Database Management Systems
Database Management Systems CSCI 4560
Popular in Course
Popular in ComputerScienence
This 185 page Class Notes was uploaded by Hester Ernser on Wednesday September 23, 2015. The Class Notes belongs to CSCI 4560 at Middle Tennessee State University taught by Zhijiang Dong in Fall. Since its upload, it has received 76 views. For similar materials see /class/213005/csci-4560-middle-tennessee-state-university in ComputerScienence at Middle Tennessee State University.
Reviews for Database Management Systems
Report this Material
What is Karma?
Karma is the currency of StudySoup.
You can buy or earn more Karma at anytime and redeem it for class notes, study guides, flashcards, and more!
Date Created: 09/23/15
MIDDLE TENNESSEE 57m UNIVERSIW Subclasses Database Management Systems Topic The Enhanced Entityi39Relationship Model Specialization Generalization Constraints f i 39ggza on Zh ijiang Dong Categories Example Dept of Computer Science Middle Tennessee State University Based on the slides coming with the textbook Topic The EntityRelationship Model CSCI 45416 a aw h an 3 1 U U H a utlltne wth w l CSCI 4560 El EER stands for Enhanced ER or Extended ER 3 EER Model Concepts El Includes all modeling concepts of basic ER IZI Additional concepts E subclassessuperclasses E specializationgeneralization E categories UNION types E attribute and relationship inheritance C These are fundamental to conceptual modeling El The additional EER concepts are used to model applications more completely and more accurately D EER includes some objectoriented concepts such as inheritance Topic The Enhanced EntityRelationship Model TENNESSEE MAM Llem CSCI 45416 Subclasses Immgls ie v 1 IAIL ILMI um i 630 4560 E An entity type may have additional meaningful subgroupings i of its entities EI EMPLOYEE may be grouped into El SECRETARY ENGINEER TECHNICIAN based on the EMPLOYEE s job E MANAGER EMPLOYEES who are managers I SALARIEDEMPLOYEE HOURLYEMPLOYEE based on the EMPLOYEE s method of pay El EER diagrams extend ER diagrams to represent these additional subgroupings called subclasses or subtypes MIDDLE TENNESSEE STAT UNIVERSITY SubdaSS s I Each of these subgroupings is a subset of EMPLOYEE Specialization entities Generalization Constraints ll Each is called a subclass of EMPLOYEE Specialization mes I EMPLOYEE is the superclass for each of these subclasses Categories Example I These are called superclasssubclass relationships I EMPLOYEESECRETARY I EMPLOYEETECHNICIAN I EMPLOYEEMANAGER I El I I ll39 IIIII 6 p 9 Subclasses and Superclasses Subclasses I An entity that is member of a subclass represents the same as some member of the superclass Wm W i Li3ctiu u0l il1 I The subclass member is the same entity in a distinct specific role I An entity cannot exist in the database merely by being a member of a subclass it must also be a member of the superclass I A member of the superclass can be optionally included as a member of any number of its subclasses Subclasses and Su perclasses I A salaried employee who is also an engineer belongs to the two subclasses ll ENGINEER I S39ALARIEDEMPLOYEE Subclasses Specialization Generalization Constraints Specialization Lattices I A salaried employee who IS also an engineering manager Categories belongs to the three subclasses Example I MANAGER l ENGINEER I SALARIEDEMPLOYEE II it is not necessary that every entity in a superclass be a member of some subclass El I I ll39 illtl 6 p 9 lSA Relationships Subclasses Specialization Generalization Constraints I These are also called lS A relationships Eggigiggzaim I SECRETARY lS A EMPLOYEE Categories I TECHNICIAN isA EMPLOYEE Example I El ill I III Iliil d p 9 Typel39nhezritanc39e39 Subclasses Specialization I An entityythat is member of a subclass inherits I All attributes of the entity as a member of the superclass Generalization Constraints I I All relationships of the entity as a member of the superclass Specialization Lattices Categories I Example Example I In the previous slide SECRETARY as well as TECHNICIAN and ENGINEER inherit the attributes Name SSN from EMPLOYEE I Every SECRETARY entity will have values for the inherited attributes El I I ll39 IIIII 6 p 9 Topic The Enhanced EntityRelationship Model TENNESSEE MAM Llem CSCI 45416 Speclalization MIDDLE I39ENNESSEE mifmn um I CSCI 4560 J r j Specialization is the process of defining a set of subclasses of a superclass 1 The set of subclasses is based upon some distinguishing characteristics of the entities in the superclass El SECRETARY ENGINEER TECHNICIAN is a specialization of EMPLOYEE based upon job type CI It is possible to have several specializations of the same superclass 1 Another specialization of EMPLOYEE based on method of pay is SALARIEDEMPLOYEE HOURLYEMPLOYEE MIDDLE TENNESSEE STAT UNIVERSITY Subclasses Specialization Generalization Constraints Specialization Lattices Categories Example SpecificLocal Attributes l Specific or Local attributes Attributes that apply only to entities of a particular subclass I the attribute TypingSpeed of SECRETARY I The subclass can also participate in specific relationship types I For example a relationship BELONG STO of HOURLYEM PLOYEE IIIII S p 9 nmifmnmain iEi 63014580 H 3 Both subclasses and superclasses are represented as r m regular entity types by rectangular boxes 3 Specific attributes are represented as regular attribute and 39 connected to the corresponding subclass by straight lines 1 Specialization is represented by a small circle D Superclasses are connected to the circle by lines I3 Subclasses are also connected to the circle by lines El The subset symbol C on each line connecting a subclass to the circle indicates the direction of the superclasssubclass relationship MIDDLE mm SpecializationAn Example C SCI 456139 Figure 41 EER diagram notation to represent subclasses and speciaiization Specialization SECRETARY TECHNICIANll ENGINEER MANAGER HOU RLYEMPLOYEE SALARI EDEMPLOYEE BELONGSTO TRADEUNON Three specializations of EMPLOYEE SECRETARY TECHNICIAN ENGINEER MANAGER HOURLYiEMPLOYEEl SALARIEDiEMPLOYEE PROJECT Topic The Enhanced EntityRelationship Model TENNESSEE MAM Llem CSCI 45416 Generalization MIDDLE TENNESSEE STAT UNIVERSITY Subclasses Specialization Genera at io n Constraints Specialization Lattices Categories Example Concept I Generalization is the reverse of the specialization process I Several classes with common features are generalized into a superclass ll original classes become its subclasses l Example CAR TRUCK generalized into VEHICLE I Both CAR TRUCK become subclasses of the superclass VEHICLE I We can view CAR TRUCK as a specialization of VEHICLE I Alternatively we can view VEHICLE as a generalization of CAR and TRUCK DLE GeneralizationAn Example Noofpassengers Vehiceid Vehicieid Generalization b Noiofipassengers Figure 43 Generalization a Two entity types CAR and TRUCK b Generalizing CAR and TRUCK into the superclass VEHICLE Diagi mmatjc Neta39tion Subclasses Specialization I Dia gramma tic notation are sometimes used to distinguish between generalization and specialization Generalization Constraints I Arrow pomting to the generalized superclass represents a Specialization generalization Lattices I Arrows pointing to the specialized subclasses represent a Categories speCIalization Example I We do not use this notation because it is often subjective as to which process is more appropriate for a particular situation I We advocate not drawing any arrows El Hi I ll39 illil p 9 Data Modeling I A superclass or subclass represents a collection or set or G n fa a an grouping of entities Constraints Specialization I It also represents a particular type of entity Lattices Categories l Shown in rectangles in EER diagrams as are entity types Example I I We can call an entity types and their corresponding collections classes whether they are entity types superclasses or subclasses El l I ll39 illil 6 p 9 Topic The Enhanced EntityRelationship Model TENNESSEE MAM Llem CSCI 45416 Constraints Diagrammatic Notation Subclasses Specialization Gene a39iza b I Predicatedefined or conditiondefined subclasses members of the subclass can be decided exactly by a condition Constraints Specialization Lattices Categories l Condition is a constraint that determines subclass members Example I Display a predicatedefined subclass by writing the predicate condition next to the line attaching the subclass to its superclass El I I ll39 IIIII 6 p 9 MIDDLE TENNESSE Attributede ned iiS pe39C39ial i zat ibn Subclasses Specialization Gene a39iza b I Attributedefined specialization all subclasses in a specialization have membership condition on the same attribute of the superclass Constraints Specialization Lattices Categories l Attribute is called the defining attribute of the specializatidn I Example JobType is the defining attribute of the specialization SECRETA RY TECHNICI AN ENGINEER of EMPLOYEE Example IIIII S p 9 MIDDLE MAM Llem Constraints Attributedefined SpecializationAn Example Figure 44 EER diagram notation for an attribute defined specializa on on Jobtype EMPLOYEE Jobitype 39Secretary Typingspeed ISECRETARYI ITECHNICIANI ENGINEER Technician39 Userdefined Specialization Subclasses Specialization G 2 I enera39 amquot I Userdefined speCIalization no condition determines the membership constraints Specialization Lattices I Membership in a subclass is determined by the database users by Categories applying an operation to add an entity to the subclass Example I Membership in the subclass is specified individually for each entity in the superclass by the user El Hi I ll39 IIIII p 9 Con traints CSCI 45416 Constraints I Two basic constraints can apply to a specializationgeneralization I Disioimness Constraint I Comp slsness Cons raint MIDDLE E NESSE I intneisfs Co Subclasses specialization l Disjointness Constraint Specifies that the subclasses of the G q speCIalization must be disjomt eneralization C onstfaim s I an entity can be a member of at m0st one of the subclasses of the specialization Specialization La lces l Specified by d in the circle connecting the superclass in EER diagram Categories Example I If not disjoint specialization is overlapping I that is the same entity may be a member of mOre than one subclass of the specialization l Specified by 0 in the circle connecting the superclass in EER diagram El E I ll39 iliri 3 p P Completeness Obns ai39nt Subclasses S I pec39aquotza I Total Completeness ConstraInt Generalization I I Every entIty In the superclass must be a member of some subclass In Constraints the speCIalIzatIongeneralIzatIon Specialization Lattices l Shown In EER dIagrams by a double lIne at the superclass Side Categories Example I Partial Completeness Constraint I An entity is not required to belong to any of the subclasses l Shown in EER diagrams by a single line at the superclass side El I I III IIIII 6 p 9 Speeial iziationZGeneralization Types Subclasses Specialization I Four types of specializationgeneralization I Disjoint total Generalization Constraints Specialization I DISJOInL pamal La lces I Overlapping total Categories Overapping partial Example I Generalization usually istotal because the superclass is derived from the subclasses El I I Ill IIIII 6 p 9 Constraints Disjoint Partial SpecializationAn Example Figure 44 EER diagram notation for an attribute defined specialization on Jobtype Secretary39 Typingispeed ISECRETARYI TECHNICIAN ENGINEER I Technician39 IDDLE Overlapping Total SpecializationAn Example Constraints Figure 45 EER diagram notation for an overlapping nondisjoint specialization Manufacturedate MAN U FACTU RE DPART PU RCHASEDF ART Topic The Enhanced EntityRelationship Model CSCI dSilC Specialization Lattices ii39i3i ii Lattiees V MIDDLE IENNESSEE nmifmmmin CSCI 4580 gm L d l C D D C A subclass may itself have further subclasses specified on it El forms a hierarchy or a lattice Hierarchy or single inheritance every subclass has only one superclass Lattice or multiple inheritance a subclass can be subclass of more than one superclass In a lattice or hierarchy a subclass inherits attributes not only of its direct superclass but also of all its predecessor superclasses A subclass with more than one superclass is called a shared subclass LatticeAn Example mu wx mi n Specialization Lattices EMPLOYEE SECRETARY HTECHNICIANH ENGINEER MANAGER SALARIEDEMPLOYEE ENGINEERINGiMANAGER Figure 46 A specialization iattice with shared subclass ENGINEERINGMANAGER MIDDLE TENNESSEE STATI UNIVERSITY Subclasses Specialization Generalization Constraints Specialization Lattices Categories Example I We can have I specialization hierarchies or lattices or I generalization hierarchies or lattices I It depends on how they were derived I We just use specialization to stand forthe end result of either specialization or generalization IIIII S p 399 MIDDLE TENNESSE ruct HierarchyLattice Subclasses I Specialization I Top down conceptual refinement process Generalization I In specialization start with an entity type and then define subclasses Constraints of the entity type bysuocessrve spectallzatlon S 39 lizatibn I La tceS II Bottom up conceptual synthesis process categmies I In generalization start with many entity types and generalize those Example that have common properties I In practice a combination of both processes is usually employed illil S p 9 LatticeAn Example 39 458 Specialization Lattices STUDENT GRADUATE U NDERGRADUATE ASSISTANT STUDE T DENT RESEARCHASSISTANT ITEACHINGASSISTANT Figure 47 A specialization iattice with multipie inheritance tor a UNIVERSiTV database Topic The Enhanced EntityRelationship Model CSCI 45416 Categories MIDDLE I39ENNESSEE miL l39An um i CSCI 560 Htierarchylitattice 1 All of the superclasssubclass relationships we have seen thus far have a single superclass E A shared subclass is a subclass in El more than one distinct superclasssubclass relationships IZI each relationships has a single superclass 3 shared subclass leads to multiple inheritance D In some cases we need to model a single superclasssubclass relationship with more than one superclass I Superclasses can represent different entity types ll Such a subclass is called a category or UNION TYPE El Difference between category and shared subclass C A shared subclass is a subset of the intersection of its superclasses 3 Shared subclass member must exist in all of its superclasses wwm IENNESSEE STAT UNIVERSITY Subclasses Specialization Generalization Constraints Specialization Lattices Example Ciategory39An Example I In a database for vehicle registration a vehicle owner can be a PERSON a BANK holding a lien on a vehicle or a COMPANY I A category UNION type called OWNER is created to represent a subset of the union of the three superclasses COMPANY BANK and PERSON I A category member must exist in at least one of its superclasses IIIII S p 9 MIDDLE liru zs CategoryAn Example Figure 48 Two categones union types OWNER and REGISTERED VEHICLE Categories Topic The Enhanced EntityRelationship Model TENNESSEE MAM Llem CSCI 45416 Example MIDDLE TENNESSEE STATI UNIVERSITY Subclasses Specialization Generalization Constraints Specialization Lattices Categories Example UNIVERSITY Database Pu rp39oSe39 I The database keeps track of I students and their majors transcripts registration I course offered by the university I the sponsored research projects of faculty and graduate students MIDDLE E NESSE UNIVERSITY Database quotntijty Tfype39s139 Subclasses l The database should have the following entity types I PERSON Specialization Generalization Constraints FACULTY a subclass of PERSON 55335323 l STUDENT a subclass of STUDENT A student can have one major and one minor Categories Example I GRADSTUDENT a subclass of STUDENT A graduate student may have one advisor and a thesis committee I INSTRUCTORRESEARCHER a subset of the union of FACULTY and GRADSTUDENT and includes all faculty as well as graduate students who are supported by teaching or researching El E I ll39 Illii o p P UNIVERSITY Database Subclasses I The database should have the following entity types I DEPARTMENT which have many faculty members but one and only Specialization Generalization one chair Constraints Specialization I COLLEGE which have multiple departments Lattices C t I GRANT which has only one principle investigator PI and can a egones suppOrt multiple faculty members and graduate students Example I COURSE which may have multiple sections I SECTION the sections offered during the past and currently I CURRENTSECTION the sections offered currently El E I III Illii 3 p P MIDDLE ESSEE MAM LMvtm CSCI dSilC Database Management Systems Topic Database Programming Zhijiang Dong Dept of Computer Science Middle Tennessee State University Based on the slides coming with the textbook MIDDLE TENNESSEE MAM Llem CSCI 45416 Topic Database Programming 1 pgtvgtl J A tn h mam aa 032CZQH aquot CM quot x MIDDLE I Outline CSCI 45410 I Database Programming I Embeded I OCCI I JDBC Database Programming I Objective I To access a database from an application program as opposed to interactive interfaces I An interactive interface is convenient but not sufficient I A majority of database operations are made thru application programs increasingly thru web applications Database Programming Approaches I Embedded commands I Database commands are embedded in a generalpurpose programming language I Library of database functions I Available to the host language for database calls known as an API Application Program Interface I A brand new fullfledged language I Minimizes impedance mismatch Impedance Mismatch I lncompatibilities between a host programming language and the database model I type mismatch and incompatibilities requires a new binding for each language I set vs recordatatime processing I need special iterators to loop over query results and manipulate individual values Steps in Database Programming Client program opens a connection to the database server Client program submits queries to andor updates the database Client program retrieves query result or feedback from the database When database access is no longer needed client program closes terminates the connection MIDDLE Database Programming Embedded SQL 1 aquot i f quot Try 239 39 q H n u v v t ah mifle w l L csc 4583 1 Embedded SQL 1 a method of combining the computing power of a highlevel language Emwm like CC and the database manipulation capabilities of SQL mi El It allows you to execute any SQL statement from an application program i Oracle s embedded SQL environment is called ProC El A embedded SQL such as ProC program is compiled in two steps 1 First the database vendor provided precompiler ie ProC recognizes the SQL statements embedded in the program and replaces them with appropriate calls to the functions in the SQL runtime library also provided by database vendor E The output is pure CC code with all the pure CC portions intact El Then a regular CC compiler is used to compile the code and produces the executable E The database vendor provided SQL runtime library must be linked Embedded SQL Embedded SQL I An embedded SQL statement is distinguished from the host language statements by enclosing it between i EXEC SQL BEGIN and a matching Ei xlD EEZEU or EXEC SQL END I Syntax may vary with language I Shared variables used in both languages usually prefixed with a colon in SQL Shared Variable Declaration ngedded I Variables inside DECLARE are shared and can appear while prefixed by a colon in SQL statements I Eir39JLCODE is used to communicate errorsexceptions between the database and the program I Example int loop EXEC SQL BEGlN DECLARE SECl lON varchar dname16 fname16 char ssn10 bdate1 1 int dno dnumber SQLCODE EXEC SQL END DECLARE SECl lON Mapping Between SQL Type and C Type SQL data type C variable declaration Embedded SMALLINT ShDI t SQL INTEGER Int BIGINT lung DECIMAL oat NUMERIC dDUble long double FLOAT oat REAL double DOUBLE PRECISION long double CHARACTER VARCHAR DATETIME char INTERVAL VARCHAR l BINARY BINARY VARVING Notes The varchar host variable type is recognized by the ESQLC preprocessor and converted to the char data type in C Establishing Connection CSCI 45416 Embedded SQL I Connection CONNECT TO server name AS connection name AUTHORIZATION user account info I Disconnection D ISCON N ECT connection name MIDD Execute a SQL Statement EXEC SQL select FNAME LNAME ADDRESS SALARY into fname lname address salary from EMPLOYEE where SSN ssn if SQLCODE 0 printffname else printf SSN does not exist ssn ENDEXEC Embedded SQL SQLCODE holds the value returned after the most recently attempted SQL operation I 0 after a successful operation I 1 after an error or I 100 after all requested rows have been fetched INTO clause specifies the program variables into which attribute values from the database are retrieved Retrieve Multiple Tuples Embedded SQL I Concepts I Cursor a pointer that points to a single tuple from the result of a query that retrieve multiple tuples OPEN CURSOR fetches the query result from the database and sets the cursor to a position before the first row in the query result FETCH move the cursor to the next tuple CLOSE CURSOR processing of query results has been completed Retrieve Multiple Tuples Example EXEC SQL select Dnumber into dnumber Embedded from DEPARTMENT where Dname dname SQL EXEC SQL DECLARE EMP CURSOR FOR select Ssn Fname Minit Lname Salary from EMPLOYEE where Dno dnumber FOR UPDATE OF Salary EXEC SQL OPEN EMP EXEC SQL FETCH from EMP into ssn fname minit lname salary while SQLCODE 0 printfquotEmployee name is Fname Minit Lname promptquotEnter the raise amount raise EXEC SQL UPDATE EMPLOYEE set Salary Salary raise where CURRENT OF EMP EXEC SQL FETCH from EMP into ssn fname minit lname salary EXEC SQL CLOSE EMP MIDDLE 39 1mg Dy amrc SQL Embedded I Objective Q I Composing and executing new not previously compiled SQL statements at runtime I a program accepts SQL statements from the keyboard at runtime I a pointandclick operation translates to certain SQL query I Dynamic update is relatively simple dynamic query can be complex I Composing and executing new not previously compiled SQL statements at runtime I because the type and number of retrieved attributes are unknown at compile time Dynamic SQL Example Embedded SQL EXEC SQL BEGIN DECLARE SECTION varchar sqlupdatestring256 EXEC SQL END DECLARE SECTION leirompt Enter update commandquot sqlupdatestring EXEC SQL PREPARE sqlcommand FROM sqlupdatestring EXEC SQL EXECUTE sqlcommand MIDDLE Topic Database Programming CSCI 45416 Introduction I C API to access Oracle database I Designed as small set of well encapsulated classes and interfaces for ease of use I Extensive features for relational access objectrelational access and scalability I Introduced in 9i growing customer base MIDDLE 39 Be nefits Easy to learn and use similar to JDBC in relational access I Based on Standard C and object oriented design I Higher productivity and quality in application development I Develop clientserver middletier and complex object modeling applications I Continuing enhancements by Oracle to add more features MIDDLE Features I Complete SQLPLSQL execution support I Scalability options to serve increasing number of users and requests I Seamless interface to manipulate objects of userdefined types as C class instances I Support for all Oracle data types and large objectLOB types I Database metadata access CSCI 4560 Embedded SQL OCCI JDBC OCCI Building an application Application source les use OCCI API and classes OCCI APl header les OTT generated C class headers OTT generated C class implementations C compiler OCCI header files occih occiCarmnon1 OCCI library static or dynamic 0cciContml h Linker acciData h wesiObjecLLh OCCI libram Application J 47m n m i In OCCI Control Classes CSCI 4560 Embedded SQL OCCI JDBC Create Create Execute MIDDLE Initialize Environment MAM LMvtm I Creating default Environment include 1 header file for all OCCI classesinterfaces include ltoccihgt create Environment Environment env EnvironmentcreateEnvironment use the Environment instance to create connections database access terminate Environment by calling static method I L I III IFllllllldlUL 1 IHIIIIIIIach n 036 4580 39 Connecting to Cl D C D D A user connection is represented by a Connection class instance Call the createConnection method of Environment class to create a connection Connection EnvironmentcreateConnection const string ampuserName const string amppassword const string ampconnectString Use the Connection object to access data execute SQL commands work with objects End connection by calling EnvironmentterminateConnection Advanced mechanisms like connection pooling session pooling proxy authentication also supported MIDDLE Create Connection I Creating a connection First need Environment Environment env EnvironmentcreateEnvironment n in Connection connenvgtcreateConnection scott 3rd parameter is db nameTNS alias n m tiger database access use the Connection object ogoff and terminate connection envgtterminateConnectionconn wth um i 630 4560 1 Execute DDLDML statements SELECT queries PLSQL blocks and retrieve results 3 Statement class for preparing amp executing SQLPLSQL statements getting PLSQL OUT results E ResultSet class for fetching SELECT query results 3 Uniform interface for binding and getting values of all data types III setXXX methods of Statement E getXXX methods of Statement amp ResultSet D Data type conversions automatically handled by OCCI Executing SQL Usage CSCI user I Create a Statement object with ConnectioncreateStatement I Specify SQL commandDDLDMLquery as argument to I ConnectioncreateStatementstring ampsql I StatementsetSQLstring ampsql I Statementexecutestring ampsql can be used for any SQL returns status StatementexecuteUpdatestring ampsql returns lnsertUpdateDelete count I StatementexecuteQuerystring ampsql returns ResultSet I Use setXXX methods of Statement to pass input bind values I Execute the SQL statement using one of the execute methods of Statement I For SELECT queries fetch the results using ResultSet class object Executing SQL Examples Simple DML Insert createStatement on Connection class gives a Statement instance Statement stmt conngtcreateStatement insert into DeptDeptnoDname Loc values 1 ACCOUNTS ZONE1 executeUpdate for all lNSERTUPDATEDELETE stmtgtexecute Update conngtterminateStatementstmt DML Insert with bind Statement stmt conngtcreateStatement insert into EmpEmpNoEname values1 2 1 and 2 are bind placeholders int empno 2 string empname JOHN W first parameter is bind position second is value stmtgtsetlnt1 empno stmtgtsetString2 empname stmtgtexecute Update Executing SELECT Examples I Executing Select queries and fetching results Statement stmt conngtcreateStatement select Empno Ename Sal from Emp where Hiredate gt 1 automatically converted to Date stmtgtsetString1 01JAN1987 executeQuery returns a ResultSet ResultSet rs stmtgtexecuteQuery ResultSetnext fetches rows and returns FALSE when no more rows while rsgtnext true get values using the getXXX methods of ResultSet empno rsgtgetlnt1 empname rsgtgetString2 empsalary rsgtgetFloat3 stmtgtcloseResultSetrsto free resources DML on Multiple Rows I DMLNSERTUPDATEDELETE of multiple rows in single roundtrip Statement stmt conngtcreateStatement insert into emp empno ename values 1 2 specify max iterations stmtgtsetMaXlterations10 number of rows specify maximum data size for types like string stmtgtsetMaXParamSize2 100 set values and add iterations stmtgtsetnt1 1001 stmtgtsetString2 JOHN stmtgtaddlteration stmtgtsetnt1 1002 stmtgtsetString2 JOE stmtgtaddlteration repeat iterationsdo not call addlteration after last set stmtgtexecuteUpdatewi insert 10 rows in single trip MIDDLE IENNESSEE nmifmiimsin CSCI 4580 Error iHlamdltng l E D E C OCCI uses C exception mechanism to return all errorsin Oracle clientserver or C STL Applications should have a trycatch block to handle exceptions The exception object thrown is of SQLException class if error is in Oracle SQLException is derived from standard C exception class getErrorCode and getMessage methods of SQLException return Oracle error information Error Handling Examples I Handling Oracle and C STL errors separately try ResultSet rs stmtgtexecuteQuery while rsgtnext catch SQLException amporaex OracleOCC errors int errno orangtgetErrorCode returns the ORA number string errmsg orangtgetMessage more application error handling catch exception ampex any other CSTL error cout ltlt Error ltlt exwhat ltlt endl MetaData Access I Dynamically discover the attributeseg nametypesize count of database objectseg tablesprocedurestypes and query results I lvletaData class and its getXXX methods provide extensive attribute information of database objects and query result columns I Use ConnectiongetMetaData and ResultSetgetColuanistMetaData to retrieve needed MetaData objects nwt LMvtxat MetaData Access Examples I MetaData of a schema object ConnectiongetMetaDatastring ampobject ParamType ptype ptype PTYPETABLEPTYPEVEW etc or PTYPEUNK MetaData md conngtgetMetaData EMP PTYPEUNK ATTROBJPTYPE returns PTYPETABLEPTYPEVEW int objectType mdgetlntMetaDataATTROBJPTYPE int columnCount mdgetlntMetaDataATTROBJNUMCOLS Timestamp objts mdgetTimestampMetaDataATTRTMESTAMP vectorltMetaDatagt cols mdgetVectorMetaDataATTRLSTCOLUMS each MetaData in the vector is for 1 column cout ltlt column 1 name ltlt cos0getStringMetaDataATTRNAME MAM LMvtm MetaData Access Examples I MetaData of a ResultSet Statement stmt conngtcreateStatement seect from emp ResuItSet rs rs gtexecuteQuery each element in the vectorltgt is a column vectorltMetaDatagt selectcols rs gtgetCquanistMetaData int columnCount selectcolssize for int i 0 i lt columnCount i cout column name ltltselectcosigetStringMetaDataATTRNAME cout column type ltltseectcosigetlntMetaDataATTRDATATYPE MIDDLE Topic Database Programming CSCI 45416 What s JDBC I JDBC is a standard interface for connecting to relational databases from Java I Hides database specific details from application I The JDBC Core API Package in javasql I The JDBC Optional Package API in javaxsql I Part of Java SE JZSE I Java SE 6 has JDBC 4 MIDDLE I39ENNESSEE mifle um i CSCI 580 El Defines a set of Java Interfaces which are implemented by vendorspecific JDBC Drivers D Applications use this set of Java interfaces for performing database operations portability IZI Every database server has corresponding JDBC drivers j Majority of JDBC API is located in javasql package 3 DriverManager Connection ResultSet DatabaseMetaData ResultSetMetaData PreparedStatement CallableStatement and Types 1 Other advanced functionality exists in the javaxsql package CI DataSource MIDDLE IENNESSEE nm mmsm CSCI 4580 363 WEE to SQ L 1 Register JDBC driver 2 Obtain a connection 3 Create statement object 4 Execute SQL statement 4a Process SELECT l statement or DDL statement 4b Process DML l I5 Process query results gt 6 Close connections I MIDDLE Load Database Driver CSCI 4500 I To manually load the database driver and register it with the load its class file ClassforName ltdatabase drivergt w try This loads an instance of the Oracle DB Driver The driver has to be in the classpath ClassforName oraclejdbcdriverOracleDriver catch ClassNotFoundException cnfe Systemoutprintln cnfe lEJl39 MIDDL Get a Database Connection CSCI A1560 I Dri39u39erlx lanager class is responsible for selecting the database and and creating the database connection by providing overloaded Lonnectioni methods I All connection methods require a JDBC URL to specify the connection details I JDBC URL Format jdbc subprotocoln ame driverc ependantdatabasen ame I Example jdbcoraclethinQ oracledbcsmtsueotI trl Connection conn DriverManagergetConnection jdbb ldbielilill quot ICSmTltI Pdllquot scott tiger username scott passwd tiger catch SQLException sqle Systemoutprintln sqle Create a Stateme t CSCI user I JDBC instance objects are created from the Gonnection I Use the createStatementO method which provides a context for executing an SQL statement I The same quenes connection is a Cor sci object created previously Statement statement connectioncreateStatement quotrent object can be used for many unrelated Statem nt Int face 3 went interface provides three methods to execute SQL statements I Use executeQuet ySta tng sqt for SELECT statements I Returns a 39 object for processing rows I Use eecuteUpdateStrtng sent for DML or DDL I Returns an int I Use execute8trtng for any SQL statement I Returns a boolean value Execute a DMLDDL Statement CSCI 4560 I Create an empty statement object J to execute the statement A a u c returns eitherthe row count for INSERT UPDATE or DELETE statements or O for SQL statements that return nothing Statement stmt conncreateStatement int rowcount stmtexecuteUpdate DELETE FROM WORKSON WHERE Essn 123456789 T L t rowcount stmtexecuteUpdate CREATE TABLE temp col1 NUMBER52 co2 VARCHAR230 rowcount is the number of rows affected by the the SQL statements Execute a Query CSCI user I Provide a SQL query string without semicolon as an argument to the eryQ method Statement stmt conncreateStatement ResultSet rset stmtexecuteQuery SELECT Ssn FROM EMPLOYEE ResultSetlnterface object I Maintains a cursor pointing to its current row of data I Provides methods to retrieve column values boolean next XXX getXXXint columnNumber XXX getXXXString columnName void close I Loop through retrieving information I The iterator is initialized to a position before the first row I You must call next once to move it to the first row ResultSetlnterface I When retrieving data from the iiteeuiti getXXX method I getString I getlnt I getDouble 39 use the appropriate I There is an appropriate getXXX method of each javasqTypes datatype ResultSet Example CSCI A1560 Statement stmt conncreateStatement ResultSet rset stmtexecuteQuery SELECT Ssn Dno Salary FROM EMPLOYEE while rsnext Wrong this will generate an error String valueO rsgetString0 Correct String value1 rsgetString1 int value2 rsgetlnt2 int value3 rsgetlnt Salary MIDDL Close Connection CSCI 4560 I Explicitly close a Connection Statement and ResultSet object to release resources that are no longer needed I Call their respective close methods Connection conn Statement stmt ResultSet rset stmtexecuteQuery SELECT FROM EMPLOYEE clean up rsetcose stmtcose39 conncose Handling Unknown SQL Statements I Create an empty statement object Statement stmt conncreateStatement I Use execute to execute the statement boolean isQuery stmtexecuteSQLstatement I Process the statement accordingly if isQuery was a query process results ResultSet r stmtgetResutSet else was an update or DDL process result int count stmtgetUpdateCount MIDDLE ESSEE MAM LMvtm CSCI dSllC Database Management Systems Topic The Relational Model Zhijiang Dong Dept of Computer Science Middle Tennessee State University Based on the slides coming with the textbook Topic Enhanced EntityRelationship Model to Relational Model Mapping TENNESSEE MAM Llem CSCI dSllC MIDDLE TENNESSEE mifmn um i 630 4560 Miillill i i 1 ERtoRelational Mapping Algorithm Step 1 Step 2 Step 3 Step 4 Step 5 Step 6 Step 7 Mapping of Regular Entity Types Mapping of Weak Entity Types Mapping of Binary 11 Relation Types Mapping of Binary 1N Relationship Types Mapping of Binary MN Relationship Types Mapping of Multivalued attributes Mapping of Nary Relationship Types 3 Mapping EER Model Constructs to Relations Step 8 Step 9 Options for Mapping Specialization or Generalization Mapping of Union Types Categories MIDDLE 15mm EntityRelationship Model Example The COMPANY ER model will be used to demonstrate the mapping Topic Enhanced EntityRelationship Model to Relational Model Mapping TENNESSEE Mix r mum C d 5 416 ERRM Mapping nmi LMumiu z gig2p il lM CSCI 4580 D Mapping Algorithm Bis431W wagger E For each regular strong entity type E in the ER schema create a relation R that includes all the simple attributes of E D Choose one of the key attributes of E as the primary key for R El If the chosen key of E is composite the set of simple attributes that form it will together form the primary key of R E Example El Relations EMPLOYEE DEPARTMENT and PROJECT are created in the relational schema corresponding to the regular entities in the ER diagram D Ssn Dnumber and Pnumber are the primary keys for the relations EMPLOYEE DEPARTMENT and PROJECT as shown COMPANY Relational Model After Step 1 Fname Minit Lname Bdate Address Sex ERRM Mapping MIDDLE I39ENNESSEE wlei um i CSCI 560 BREW Magma xvi 139 M n 39 ping or Wll Entity D Mapping Algorithm III For each weak entity type W in the ER schema with owner entity type E create a relation R amp include all simple attributes or simple components of composite attributes of W as attributes of R I Also include as foreign key attributes of R the primary key attributes of the relations that correspond to the owner entity types E The primary key of R is the combination of the primary keys of the owners and the partial key of the weak entity type W if any El Example I Create the relation DEPENDENT in this step to correspond to the weak entity type DEPENDENT 1 Include the primary key Ssn of the EMPLOYEE relation as a foreign key attribute of DEPEN DENT renamed to Essn E1 The primary key of the DEPENDENT relation is the combination Essn Dependentname because Dependentname is the partial key of DEPENDENT MIDDLE TENNESSEE gt WI LMl LKM v 135C 45613 ERRM Mapping COMPANY Relational Model After Step 2 Fname Minit Lname Bdate Address Sex MIDDLE TENNESSEE wlei um i 630 4560 E vl l r Mambo xvi iyl iapping Binahy 1i 1 Reiilatnfion C Three possible mapping algorithms Foreign Key approach Choose one of the relationssay S and include a foreign key in S the primary key of T It is better to choose an entity type with total participation in R in the role of S Merged relation option An alternate mapping of a 1 1 relationship type is possible by merging the two entity types and the relationship into a single relation This may be appropriate when both participations are total Crossreference or relationship relation option The third alternative is to set up a third relation R for the purpose of crossreferencing the primary keys of the two relations 8 and T representing the entity types C Example of Foreign Key Approach III 1 1 relation MANAGES is mapped by choosing the participating entity type DEPARTMENT to serve in the role of S because its participation in the MANAGES relationship type is total MIDDLE COMPANY Relational Model After Step 3 135C 45613 Fname Minit Lname Bdate Address Sex ERRM Mapping DEPENDENT I Essn I Dependent name I Sex I Bdate I Relationship l all cit i N Relationship 39r vrs ggge J 39 63014580 1 Mapping Algorithm JEE JW WWW El For each regular binary 1 N relationship type R identify the relation S that represent the participating entity type at the Nside of the relationship type 1 in I3 lnclude as foreign key in S the primary key of the relation T that represents the other entity type participating in R E Include any simple attributes of the 1N relation type as attributes of S E Example I 1N relationship types WORKSFOR CONTROLS and SUPERVISION in the ER model C For WORKSFOR we include the primary key Dnumber of the DEPARTMENT relation as foreign key in the EMPLOYEE relation and call it Dno COMPANY Relational Model After Step 4 135C 45613 Fname Minit Lname Bdate Address Sex DEPENDENT I Essn I Dependent name I Sex I Bdate I Relationship l tillatoplrm of Binary llvjilzzi li i leiiatiionship 03014560 1 Mapping Algorithm 1 For each regular binary MN relationship type R create a new relation gram 8 to represent R WWW ll lnclude as foreign key attributes in S the primary keys of the relations that represent the participating entity types their combination will 39 form the primary key of S E Also include any simple attributes of the MN relationship type or simple components of composite attributes as attributes of S 3 Example 3 The MN relationship type WORKSON from the ER diagram is mapped by creating a relation WORKSON in the relational database schema D The primary keys of the PROJECT and EMPLOYEE relations are included as foreign keys in WORKSON and renamed Pno and Essn respectively 3 Attribute Hours in WORKSON represents the HOURS attribute of the relation type The primary key of the WORKSON relation is the combination of the foreign key attributes Essn Pno COMPANY Relational Model After Step 5 135C 45613 Fname Minit Lname Bdate Address Sex DEPENDENT I Essn I Dependent name I Sex I Bdate I Relationship l v41 a gaping i yiiulittlvallued attributes JEN ii 6361 4580 El Mapping Algorithm EEqu E For each multivalued attribute A create a new relation R Magnumt 39 D This relation R wrll Include an attribute corresponding to A plus the primary key attribute Kas a foreign key in Rof the relation that represents the entity type of relationship type that has A as an attribute xvi E The primary key of R is the combination of A and K If the multivalued attribute is composite we include its simple components El Example D The relation DEPTLOCATONS is created El The attribute DLOCATION represents the multivalued attribute LOCATIONS of DEPARTMENT while DNUMBERas foreign keyrepresents the primary key of the DEPARTMENT relation 1 The primary key of R is the combination of Dnumber Dlocation COMPANY Relational Model After Step 6 CSCi 45613 Fname Minit Lname Bdate Address Sex DEPENDENT I Essn I Dependent name I Sex I Bdate I Relationship l Step 7 Mapping of Nary Relationship Types mu LM main 3935 mac I I Mapping Algorithm I For each nary relationship type R where ngt2 create a new ERRM relationship S to represent R Mapping I Include as foreign key attributes in S the primary keys of the relations that represent the participating entity types I Also include any simple attributes of the nary relationship type or simple components of composite attributes as attributes of S I Example I The relationship type SUPPY in the ER on the next slide a SUPPLIER PROJECT Mapping Nary Relationship Type SUPPLIER SNAME 39 PROJECT I PROUNAME I l PART I PARTNO l SUPPLY PROJNAME l SNAME PARTNO J QUANTITY MIDDLE Ad V9 3 NF 3919 r i3939 H 5 wmmamy m Mappmg EH m PM CSCI 560 E w m Magma Jv MIDDLE TENNESSEE MAM hmll lhi CSCI dSilC EERRM Mapping Topic Enhanced EntityRelationship Model to Relational Model Mapping MIDDLE E NESSE I N E SYATI UNIVERSlTV ERRM Mapping l Mapping Algorithm I Convert each specialization with m subclasses S1 82Sm and generalized supercl ass C where the attributes of C are ka1an and k is the primary key into relational schemas using one of the four following options Option 8A Multiple relations Superclass and subclasses Option BB Multiple relationsSubclass relations only Option 80 Single relation with one type attribute Option 8D Single relation with multiple type attributes I ll39 Illri 3 p P Step 8 Mapping Specialization or Generalization nwt LMi39txairi EERRM Mapping I Option 8A Multiple relationsSuperclass and subclasses I Create a relation L for C with attributes AttrsL ka1 an and PKL k Create a relation L for each subclass Si 1 lt i lt m with the attributes AttrsL k U attributes of Si and PKLk This option works for any specialization total or partial disjoint of overlapping IDDLE Option 8A Example If SCI 4536f Mapping EMPLOYEE Job Type Secremrw Engineer TypingSpeed SECRETARY I TECHNICIAN ENGINEER Technician MIDDLE ENNESSEE 3F x r 39 l 1n EERRM Mapping Option 8A Example 8 EMPLOYEE E l FName Mnit LName BirthDate Address JobType SECRETARY TECHNICIAN I E I TypingSpeed ENGINEER l SSN l TGrade l I E I EngType l MIDDLE TENNE EE nwt LMytxal Step 8 Mapping Specialization or Generalization EERRM Mappmg I Option SB Multiple relationsSubclass relations only I Create a relation Li for each subclass Si 1 lt i lt m with the attributes AttrL attributes of 8 U ka1 an and PKL k This option only works for a specialization whose subclasses are total every entity in the superclass must belong to at least one of the subclasses Option BB Example EERRM Mapping VEHICLE NoOfPassengers MaxSpeed MIDDLE TENNESSEE MAM Llem CSCI 45416 EERRM Mapping Option 88 Example b CAR Vehicleld I LicensePlateNo Price MaxSpeed I NoOfPassengers l TRUCK Vehicleld l LicensePlateNo Price NoOfoles l l Step 8 Mapping Specialization or Generalization EERRM Mapping I Option 80 Single relation with one type attribute I Create a single relation L with attributes AttrsL ka1an U attributes of 81 U U attributes of Sm U t and PKL k The attribute t is called a type or discriminating attribute that indicates the subclass to which each tuple belongs MIDDLE Option 80 Example If SCI 4536f EERRM Mapping EMPLOYEE Job Type Secremry Engineer TypingSpeed SECRETARY I TECHNICIAN ENGINEER Technician IDDLE Option 80 Example CBC 4 561 EERRM Mapping 0 EMPLOYEE FName Mlnit LName BinhDate Address I JobTypel TypingSpeed TGrade Step 8 Mapping Specialization or Generalization EERRM Mapping I Option 8D Single relation with multiple type attributes I Create a single relation schema L with attributes AttrsL ka1an U attributes of 81 U U attributes of Sm U 11 2 tm and PKL k Each 1 1 lt i lt m is a Boolean type attribute indicating whether a tuple belongs to the subclass S MAM Llem EERRM Mapping Option 8D Example ManufactureDate MANUFACTUREDPART SupplierName PU RCHASEDPART Option 8D Example CSCI 45416 EERRM Mapping d P HT I PanNo I Descriptxon I MFlag I DrawingNo ManmamureDate I BatchNo I PFlag I SupplierName I ListPrice I Mapping of ared SUbCI aSLSeS Multiple 5m warm I ERRM Mapping I Mapping Algorithm l A shared subclass such as STUDENTASSISTANT is a subclass of several classes indicating multiple inheritance These classes must all have the same key attribute otherwise the shared subclass would be modeled as a category We can apply any of the options discussed in Step 8 to a shared subclass subject to the restriction discussed in Step 8 of the mapping algorithm Below both 80 and 8D are used for the shared class STUD ENTASSISTANT El E I ll39 Illii 3 p P Multiple Inheritance Example mu mu Dbl 550 4r EERRM Mapping STUDENT UNDERGRADUATE STUDENT RESEARCILASSKSTANT TEACHINGJSSISTANT MIDDLE TENNESSEE 57m umvmsmr Relational AI b a Database Management Systems TopicsThe Relational Algebra and Calculus Zh ijiang Dong Relational Calculu Dept of Computer Science quot Middle Tennessee State University Query By Ex I amp 9 Based on the slides coming With the textbook Topic The Relational Algebra and Calculus Relational Algebra Relational Calculus Query By Example MIDDLE TENNESSEE STAT UNIVERSITY Outline I Relational Algebra I Unary Relational Operations I Relational AlgebraOperations From Set Theory I Binary Relational Operations I Additional Relational Operations I Examples of Queries in Relational Algebra I Relational Calculus I Tuple Relational Calculus I Domain Relational Calculus ll EXample Database Application COMPANY I Overview of the QBE language appendix D El I I ll39 IIIII 6 p 9 MIDDLE Topic The Relational Algebra and Calculus CSCI dSllC Relational Algebra MIDDLE Topic The Relational Algebra and Calculus CSCI dSllC MIDDLE quotI History I Muhammad ibn Musa alKhwarizmi 800847 CE wrote a book titled aljabr about arithmetic of variables I Bookwas translated into Latin Relational Algebra I lts title aljabr gave Algebra its name V l AlKhwarizmi called variables shay Relational I Shay is Arabic for thing Calculus r I Spanish transliterated shay as xay X was sh in Spain QueryBy I Where does the word Algorithm come from Example I Algorithm originates from alKhwarizmi I Reference PBS httpwwwpbsorgempiresislaminnoalgebrahtml El I I ll39 illil 6 p 9 MIDDLE I39E mifle Ksl l CSCI 4580 h r 391 MN Mute D l l l Relational algebra is the basic set of operations for the relational model These operations enable a user to specify basic retrieval requests or queries The result of an operation is a new relation which may have been formed from one or more input relations D This property makes the algebra closed all objects in relational algebra are relations D The new relations can be further manipulated using operations of the same algebra A sequence of relational algebra operations forms a relational algebra expression El The result of a relational algebra expression is also a relation that represents the result of a database query or retrieval request MIDDLE E NESSE Overview ll Relational Algebra consists of several groups of operations I Unary Relational Operations I SELECT symbol cr sigma I PROJECT symbol 2r pi I RENAME symbol p rho Relational AI b 39a I Relational Algebra Operations From Set Theory I UNION U INTERSECTION DIFFERENCE or MINUS 7 I 39 Reamna39 I CARTESIAN PRODUCT gtlt I Binary Relational Operations I JOIN several variations of JOIN exist Query By I DIVISION Example I Additional Relational Operations I OUTER JOINS OUTER UNION I AGGREGATE FUNCTIONS These compute summary of information for example SUM COUNT AVG MIN MAX QQO MIDDLE i i i fiif COMPANY Database Schema All examples discussed below refer to the COMPANY database shown here EM PLOYEE I Fname I Mini I Lname I I Bdate I Address I Sex I Salary I Superissn I Dno I DEPARTMENT w 4 DEPLLOCATIONS PRO ECT WORKSON I i DEPEN DENT Essn Dependentname s ex Bdate Relationship COMPANY Database State Figure 55 One passxl e database sme Em me COMPANY re amnal dambase schema DEPARYMENT Dname Ra means22 WNWquot 1395mm Headquatlevs 339555555 mum IDDLE Topic The Relational Algebra and Calculus CSCI dSllC rm lgls ie 3 39 wth um i x 03014560 Cl The SELECT operation is used to select a subset of the tuples from a relation based on a selection condition denoted by gfm39 qkmm 0ltselection conditiongtR Where pwamuhum D the symbol a sigma is used to denote the select operator El the selection condition is a Boolean conditional expression specified on the attributes of relation R El tuples that make the condition true are selected ie appear in the result of the operation D tuples that make the condition false are filtered out ie discarded from the result of the operation El SELECT operator is applied to a single relation E SELECT operator is applied to each tuple individually MIDDLE I39ENNESSEE hufle um i CSCI 4580 mm i hl mnll lwwmm C For Operation 0ltselection conditiongtR lt selection condition gt is one of the following Ult 39 gtlt V39r gtlt 39gt I lt u 39L 4 V 39 r u 39L 4 gt D arbitrarily connected by the above two forms using Boolean operators such as AND OR and NOT 3 If the domain of an attribute is ordered values the comparison operators that can be applied to the attribute are lt s gt 2 and 75 D If the domain of an attribute is unordered values the comparison operators that can be applied to the attribute are and 75 1 Some domains may allow additional types of comparison operators such as SUBSTRINGOF for character strings SELECT Examples I Select the EMPLOYEE tuples whose department number is 4 0 DNO4 EM PLOWquot E E I Select the employee tuples whose salary is greater than 30000 67SALAE gtsooooEMPLOYEE I Select the employee tuples who either work in department 4 and make over 25000 per year or work in department 5 and make over 30000 039Dno4 AND Salarygt25000 on Dno5 AND Salarygt30000E3 t lPLG EE smi mmin L SELECT Operation iPFroperties cscuseo 1 The SELECT operation daemon0 conditiongtR produces a relation S that has the same schema same attributes as R 3 SELECT a is commutative g gg w 1 0ltcondition1gt0ltcondition2gtR 0ltcondition2gt0ltcondition1gtR 3 Because of commutativity property a cascade sequence of SELECT operations may be applied in any order 1 0ltcondition1gt0ltcondition2gt0ltcondition3gtRD 0 ltcondition2gt 0 ltcondition3gt 0 ltcondition1 gt l A cascade of SELECT operations may be replaced by a single selection with a conjunction of all the conditions D 0ltcondition1gt0ltcondition2gt0ltcondition3gtRD 0 ltcondition1 gt AND ltcondition2gt AND ltcondition3gtR E The number of tuples in the result of a SELECT is less than or equal to the number of tuples in the input relation R miL l39An um i 63614560 Cl PROJECT Operation keeps certain columns attributes from a relation and discards the other columns CI PROJECT creates a vertical partitioning grgmw E The list of specified columns attributes is kept in each tuple E The other attributes in each tuple are discarded l The general form of the project operation is 7Tltattribute listgtR Where El 7139 pi is the symbol used to represent the project operation El ltattribute listgt is the desired list of attributes from relation R separated by E Example To list each employee s first and last name and salary the following is used 7TILname Fname SalaryEMP OYEE WQDTQEJ DngA v5 P 39 nmi LMHmin 630 4580 C1 The project operation removes any duplicate tuples E This is because the result of the project operation must be a set of tuples Mathematical sets do not allow duplicate elements 1mm Q ml l QWWWE E The number of tuples in the result of projection 7rlt5tgtR is always less or equal to the number of tuples in R El If the list of attributes includes a key of R then the number of tuples in the result of PROJECT is equal to the number of tuples in R E PROJECT is NOT commutative E 7Tltnst1 gt7Tlt5t2gtR 7rltljsf1gtR as long as ltlist2gt contains the attributes in ltlist1gt MIDDLE TENNESSEE armx CBC 4580 Unary Relaiional Operations Operation Example Figure 61 Results of SELECT and PROJECT operations a 6mm AND Salarygtg5000 0R 9mg AND Sarawaoooo EMPLOYEE b mm SaraWltEMPLOYEEgt is use SarayEMPL0YEE a Fname Mini Lname Bdate Address Sex Salary Superssn Dno Franklin T Wong 333445555 19551208 638 Voss Houston TX M 40000 888665555 5 Jennifer S Wallace 987654321 19410620 291 Berry Bellaire TX F 43000 888665555 4 Ramesh K Narayan 666884444 19620915 975 Fire Oak Humble TX M 38000 333445555 5 b C Lname Fname Salary Sex Salary Smith John 30000 M 80000 Wong Franklin 40000 M 40000 Zelaya Alicia 25000 F 25000 Wallace Jennifer 43000 F 43000 Narayan Ramesh 38000 M 38000 English Joyce 25000 M 25000 Jabbar Ahmad 25000 M 55000 Borg James 55000 IDDLE Topic The Relational Algebra and Calculus CSCI dSllC 6301 4580 D Two ways to apply several relational algebra operations one after the other El Relational algebra expression nesting the operations II Apply one operation at a time and create intermediate result relations C Names must be given to the relations holding the intermediate 39 results El Example To retrieve the first name last name and salary of all employees who work in department number 5 we must apply a select and a project operation El Relational algebra expression 7TFnameLnameSalaryUDNO5EMPLOYEE I Sequence of operations El DEP5EMPS lt7 00N05EMPLOYEE E RESULT lt7 anameyLnameySalayDEP5EliPS MIDDLE E NESSE 3 I The expression DEP5EMPS lt UDN05EMPLOYEE means I The selection result of the operation JUN05EMPLOYEE will be saved in a relation I The relation is called DEP5EMPS I The attributes of the DEP5EMPS are the same as the attributes in the relation EMPLOYEE Relational Algebra Relational Calculus I The expression RESULT lt anameLnam9753ayDEP5EMPS 39 means I The projection result ofthe operation Query By 7I39FnamavLnamaSaaryDEP5EMPS will be stored in a relation Example I The relation is called RESULT I The attributes of RESULT are Fname Lname and Salary El E I lll lllli 3 p P RENAME 39peratif on II In some cases We may Want to rename the attributes of a relation or the relation name or both Relational Algebra I Useful when a query requires multiple operations I Necessary in some cases see JOIN operation later I RENAME operation denoted by p rho Relational I Give a new schema to a relation R by changing Calculus I attribute names A1 A2 A3 An to B1 82 B3 Bn pB132BsBnR I relation name to S Empty pm I both the attribute names and relation names pSBtBZBs BnR El E I ll39 Illii 3 p P RENAME Operation Shor th nd Notation Relational AI b 39a I For convenience we also use a shorthand for renaming attributes in an intermediate relation I RESULT e WFnameyMamasmaryDEP5EMPS RESULT will have the same attribute names as DEP5EMPS same attributes as EMPLOYEE Relational CalCUIU I RESULTSSN lt7 7T3UPER35NDEP5EMPS RESULT will have only one attribute SSN same attributes as SUPERSSN in EMPLOYEE Query By Example El I I ll39 till 6 p 9 IDDLE Topic The Relational Algebra and Calculus CSCI dSllC Basie Relational Algebra Operations ft m Set 5m umme illelagonal I UNION Operation denoted by U I The result of ROS is a relation that includes all tuples that are either in R or in S or in both R and S I Duplicate tuples are eliminated ll INTERSECTION operation denoted by m I The result of the operation R S is a relation that includes all tuples that are in both R and S Relational QueryBy l SET DIFFERENCE operation denoted by Example I The result of R i S is a relatiOn that includes all tuples that are in R but not in S El E I ll39 Illii 3 p P 630 4560 E R1 A1 A2 An and R2B1 82 Bn are type compatible if I they have the same number of attributes and El the domains of corresponding attributes are type compatible ie r domAidomBi for i1 2 n T mw 3 Two relations may have different names and different attribute names a Type Compatibility of operands is required for the binary set operations UNION INTERSECTION and SET DIFFERENCE E The resulting relation for R1UR2 also for R1 R2 or R1 R2 has the same attribute names as the first operand relation R1 by convention it ji iiti Example 63014580 E To retrieve the social security numbers of all employees who either work in department 5 RESULTt below or directly supervise an employee who works in department 5 r RESULT2 below giggmmwsa a DEP5EMPS e 00N05EMPLOYEE m RESULTt e 7TSSNDEP5EMPS m RESULT2SSN e WSUPERSSNDEP5EMPS El RESULT e RESULTtURESULTZ j The union operation produces the tuples that are in either RESULTt or RESULT2 or both UNION Example CSCI 415416 qums3 RESUU1 RESUUQ RESUU Result of the UNION operation 35 l 33quot I 33 RESULTlt RESULI1 123456789 333445555 123456789 RESULT 333445555 888665555 333445555 666884444 666884444 453453453 453453453 888665555 MIDDLE TENNESSEE I u LNi hem Iil 4G5 UNION INTERSECTION and MINUS Example a STU DENT INSTRUCTOR Fn Ln Fname Lname 0 Fn Ln Susan Yao John Smith Susan Yao Ramesh Shah Ricardo Browne Ramesh Shah Johnny Kohler Susan Yao Johnny Kohler Barbara Jones Francis Johnson Barbara Jones Amy Ford Ramesh Shah Amy Ford Jimmy Wang Jimmy Wang Ernest Gilbert Ernest Gilbert John Smith Ricardo Browne Francis Johnson c I Fn I Ln I d Fn Ln 9 Fname Lname I Susan I Yao I Johnny Kohler John Smith I Ramesh I Shah I Barbara Jones Ricardo Browne Amy Ford Francis Johnson Jimmy Wang Ernest Gilbert Figure 64 The set operations UNION INTERSECTION and MINUS a Two unioncompatible relations b STUDENT U INSTRUCTOR c STUDENT INSTRUCTOR d STUDENT INSTRUCTOR 9 INSTRUCTOR e STUDENT Properties of UN ION INTERSECT am 5m umvmsrrv I Commutative Relational A39 b393 I Both UNION and INTERSECTION are commutative ie ROS SUR R S S R I Associative l Both UNION and INTERSECTION are assOciative ie Bursa SURUT Recent Sewer Relational Calculu QueryBy I The MINUS operation is neither commutatiVe nor Example associative ie in general R 7 S S i R 3g Heeeneeemet El I I III IIIII 6 p 9 I CARTESIAN or CROSS PRODUCT Operation I This operation is used to combine tuples from two relations in a combinatorial fashion Denoted by RA1A2 A x SB1Bg Bm Result is a relation Q with degree n m attributes QA1A2AnB1B2 Bm in that order The resulting relation state has one tuple for each combination of tuplesone from R and one from 8 Hence if R has n9 tuples denoted as W n9 and S has n5 tuples then H gtlt Swill have np ns tuples The two operands do NOT have to be type compatible El DJ I ll Illri o p 9 V MIDDLE IENNESSEE nmi LMHmin 630 4580 mtwwmmwsa mam Ni malty Reia tonal lfgerations 39 JCT 1 Generally CARTESIAN PRODUCT is not a meaningful operation E For example FEMALEEMPS lt7 USEXFEMPLOYEE EMPNAMES lt7 7TFNAMEVLNAMEVSSNFEMALEEMPS EMPDEPENDENTS lt7 EMPNAMESgtltDEPENDENT El EMPDEPENDENTS will contain every combination of EMPNAMES and DEPENDENT NO MATTER they are actually related or not C CARTESIAN PRODUCT become meaningful when followed by other operations E To keep only combinations where the DEPENDENT is related to the EMPLOYEE we add a SELECT operation FEMALEEMPS lt7 USEXFEMPLOYEE EMPNAMES lt7 WFNAMEVLNAMEVSSNFEMALEEMPS EMPDEPENDENTS 7 EMPNAMESXDEPENDENT ACTUALDEPS lt7 USSNESSNEMPDEPENDENTS RESULT H 7TFNAMELNAMEDEPENDENTNAMEACTUALDEPS RESULT will now contain the name of female employees and their dependents l CARTESIAN PRODUCT Example TENNESSEE mum u Figure 55 the CARTESIAN PRODUCT CROSS PRODUCT operahanv rsmnajmvs Mini Lname Ssn Eggs Addvass m Sa iry Supu s l no J my 9seaa7777 mewm aaztcasue smmg tx 5543m 4 a EMPNAMES V ya an 1 E n m r w ace Eng Ish muzz oeaemwe m mm V MIDDLE IENNESSEE nmi LMumin CSCI 4580 Eilwtlirgmiygyga Warm lainairy Pleiiatjional 1 N ll JOIN Operation denoted by gt4 D The sequence of CARTESIAN PRODECT followed by SELECT is used quite commonly to identify and select related tuples from two relations D JOIN combines this sequence into a single operation El This operation is very important for any relational database with more than a single relation because it allows us combine related tuples from various relations 1 Syntax D The general form of a join operation on two relations RA1A2 WA and SB1Bgme is R lgtltltjol39n conditiongt S where R and S can be any relations that result from general relational algebra expressions MIDDLE TENNESSEE MIL m min CSCI 4560 o a stairs on 314 imam a Example Suppose that we want to retrieve the name of the manager of each department D To get the manager s name we need to combine each DEPARTMENT tuple with the EMPLOYEE tuple whose SSN value matches the MGRSSN value in the department tuple D We do this by using the join operation DEPTMGR lt DEPARTMENT NMGHSSNSSN EMPLOYEE D MGRSSNSSN is the join condition 3 Combines each department record with the employee who manages the department 3 The join condition can also be specified as DEPARTMENTMGRSSN EMPLOYEESSN E D E PTM G R DEPTMGR Dname Dnumber Mgrssn Fname Minit Lname Ssn Research 5 333445555 Franklin T Wong 833445555 39 39 39 4 987654321 Jennifer S Wallace 987654321 1 888665555 James E Borg 888665555 Figure 66 Result of the JOIN operation JOIN Properties Relational Algebra I Consider the following JOIN operation R041 71427 M An Mama s 33132y5m I Result is a relation Q with degree n m attributes QA1A2AnB1Bg 7Bm in that order I The resulting relation state has one tuple for each combination of tuples r frOm R and s from S butonly if they satisfy the join condition rAisBj Relational Calculus I Hence if R has n9 tuples and S has n5 tuples then the join result will generally have less than nF n5 tuples Query By I Only related tuples based on the join condition will appear in the Example result El I I ll39 illil 6 p 9 MIDDLE TENNESSEE T h J O I N eta I Theta JOIN the general case of JOIN operation denoted by p 511 7 A27 7I 1nl Dltfheta 35115214q m m1 I The join condition is called theta I9 I Theta can be any general boolean expression on the attributes of R and S for example RA lt 85 AND RAK 85 OR HAP lt SBq I Most join conditions involve one or more equality conditions AN D ed together for example RA 85 AND HAK 85 AND HAP SBq I Theta JOIN example Nearpmz 3051th BOAT MIDDLE TENNESSEE STAT UNIVERSITY Relational Algebra Relational Calculus Query By Example I EQUIJOIN Operation EQUIJOIN The most common use ofjoin involves join conditions with equality comparisons only The only comparison operator used in EQUIJOIN is In the result of an EQUIJOIN we always have one or more pairs of attributes whose names need NOT be identical that have identical values in every tuple The JOIN seen in the previous example was an EQUIJOIN IIIII S p 9 iNiATUR KL d lllNl wwrmwu 63614580 Cl NATURAL JOIN Operation denoted by gtk it D It requires that the two join attributes or each pair of corresponding join attributes have the same name in both relations El If this is not the case a renaming operation is applied first Emwmgyga E It was created to get rid of the second super uous attribute in an 9 EQUIJOIN condition because one of each pair of attributes with identical values is superfluous 1 Example Q lt RABCD SCDE D The implicit join condition includes each pair of attributes with the same name AND ed together RCSC AND RDSD D Result keeps only one attribute of each such pair QA B C D E MIDDLE TENNESSEE mu LN mm CSCI 4560 oercttb ae iron Timmy NATURAL LJLKQHN 1 Emmet 1 To apply a natural join on the DNUMBER attributes of DEPARTMENT and DEPTLOCATIONS it is sufficient to write DEPTLOCS 4 DEPARTMENT DEPTLOCATIONS 1 Only attribute with the same name is DNUMBER D An implicit join condition is created based on this attribute DEPARTMENTDNUMBERDEPTLOCATIONSDNUMBER a PROJDEPT Pname Pnumber Plocation Dnum Dname Mgrssn Mgrstartdate ProductX 1 Bellaire 5 Research 833445555 19880522 ProductY 2 Sugarland 5 Research 333445555 19880522 Produth 3 Houston 5 Research 333445555 19880522 Computerization 10 Stafford 4 Administration 987654321 19950101 Reorganization 20 Houston 1 Headquarters 888665555 19810619 Newbenefits 30 Stafford 4 39 39 39 39 987654321 19950101 b DEPTiLOCS Dname Dnumber Mgrssn Mgrstartdate Location Headquarters 1 888665555 19810649 Houston Administration 4 987654321 19950101 Stafford Research 5 333445555 19880522 Bellaire Research 5 333445555 19880522 Sugariand Research 5 383445555 19880522 Houston Figure 67 Results of two NATURAL JOIN operations a PROJiDEPT lt PROJECT DEPT b DEPTLOCS DEPARTMENT DEPTLOCATIONS Complete Set of Relational periati o ns Relational A3999 I The set of operations including SELECT PROJECT UNION DIFFERENCE RENAME and CARTESIAN PRODUCT is called a complete set because I any other relational algebra expression can be expressed by a combination of these five operations Relational calcu39us I For example I ROS HUS 7 R i SUS i R Query By I R Ddltjoin canditiangt S Ultjain canditiongtRgtltS Example El I I ll39 Illrl 6 p 9 NWFILMHKJIH 030 4580 3 DIVISION Operation denoted by III The division operation is applied to two relations R and S denoted by RZ SX where E Z is the set of attributes of R gigggim w III X is the set of attributes of S 39 D X is a subset of Z J Let Y be the set of attributes of R that are not attributes of S ie Y Z X D The result of DIVISION is a relation TY that includes a tuple t if tuples 1 appear in R with IRY t and with IFX Is for every tuple Is in S I i D For a tuple t to appear in the result T of the DIVISION the values int must appear in R in combination with every tuple in S MIDDLE TENNESSEE nwt LMVlm DIVISION Example Figure 68 a SSNPNOS SMITHPNOS Essn Pno I Pno I 123455789 1 I 1 I 123456789 2 I 2 I 665884444 3 453453453 1 453453453 2 SSNS 333445555 2 Ssn 333445555 3 123456789 333445555 10 453453453 333445555 20 999887777 30 999887777 10 987987987 10 987987987 30 987654321 30 987554321 20 888665555 20 A B 31 b1 32 b1 33 b1 34 b1 31 b2 33 b2 32 b3 as b3 34 b3 31 b4 32 b4 33 b4 The DIVISION operation a DIvidIng SSNPNOS by SMITHPNOS b T R S T n MIDDLE TENNESSEE Iii L I min DIVISION Example Table 61 apeiattons oi Relational Algebra Operation S ELECT PROJECT THETA JOIN EOUIJCIN NATURAL JOIN UNION INTERSECTION DIFFERENCE CARTESIAN DIVISION Purpose Seicu an r from a relation R Produces a new relation with only some of the attributes of R and removes duplicate lupies Produces all combinations of tuples from R and R2 that satisfy thejoin condition Produces all the combinations ol tttples from R and R that satisfy a join condition with only equality comparisons Same as EQUIJOIN except that the join attributcs of R2 are not included in the resulting relation iftllejoin i i t be speci ed at all Produces a relatinn that includes all he mpies in R i both R and R1 It and R1 must be union compatible Produces a relation that includes all the tuples in both R and R1 I1 and R1 must he union cumpati le Produces a relation that includes all the tuples in R that are not in R2 R and R2 must be union com palihle Produces a relation that has the attributes ofR and R2 and include as triples all possible combinations ortuples From RK and R1 Produces a relation RX that includes all uples tIX in mm that appear in R in combination with every tuplt nin Rle where Z x U Y Notation n R Manny boil R N It2 0 Minnie 1 N 0 umdiliuil R1 0R RI N tltnnne i iltnn z Rimquot tannin R2 OR Riquot teiainninpan m OR Knit2 RLJR2 RR2 12422 RgtltR2 R Z R1 Y A h t m ii 63614580 5 My E Query Tree El An internal data structure to represent a query Cl Standard technique for estimating the work involved in executing the I 39 u query the generation of intermediate results and the optimization of iiii immw execution Cl Nodes stand for operations like selection projection join renaming division Cl Leaf nodes represent base relations 3 A tree gives a good visual feel of the complexity of the query and the operations involved Cl Algebraic Query Optimization consists of rewriting the query or modifying the query tree into an equivalent tree Query Tree Example CSCI 45416 7 PPnumberPDnumELnameEAddressEBdate 3 N DMgrssnESsn 2 N PDnumDDnumber 9 EMPLOYEE m c PPlocation Stafford 9 DEPARTMENT Figure 69 Query tree corresponding to the relational algebra 0 PROJECT expression for 02 IDDLE Topic The Relational Algebra and Calculus CSCI dSllC Functions and Grouping NWFLMHKMH 630 4580 El A type of request that cannot be expressed in the basic relational algebra is to specify mathematical aggregate functions on collections of values from the database E Examples of such functions include retrieving the average or total ggggg mmi salary of all employees or the total number of employee tuples B These functions are used in simple statistical queries that summarize information from the database tuples II Common functions applied to collections of numeric values include SUM AVERAGE MAXIMUM and MINIMUM 1 D The COUNT function is used for counting tuples or values DLE I Use of the Aggregate Functional operation 3 gate Function Operation retrieves the maximum salary value from the EMPLOYEE relation Q 3 Dr Va 1 MAX Salary L LO LL retrieves the minimum Salary value from the EMPLOYEE relation gIVIN Saiar amw mEE retrieves the sum of the Salary from the EMPLOYEE relation SUM Salary Ell339 50165 LU computes the count number of employees and their average salary F1 r U i 1 1 3 COUNT sew AVERAGE Salarylt93lVIFLCl L I l Note countjust counts the number of rows without removing duplicates V MIDDLE IENNESSEE nmi LMHmin CSCI 4580 mmnmgmli Gammon summits Llsin n with 5 4 El 3 The previous examples all summarized one or more attributes for a set of tuples El Maximum Salary or Count number of Ssn Grouping can be combined with Aggregate Functions 3 Example For each department retrieve the DN0 COUNT SSN and AVERAGE SALARY D A variation of aggregate operation gallows this E Grouping attribute placed to left of symbol E Aggregate functions to right of symbol 5 For examplei DNO ECOUNTSSN AVERAGE SalaryEMPLOYEE E Above operation groups employees by DNO department number and computes the count of employees and average salary per department MIDDLE grams Grouping with Aggregation Example Figure 510 The aggregate function operation a pRDno NojLemployees Averagesal Dno S COUNT Ssn AVERAGE Sam EMPLOYE E W 07703 COUNT Ssn AVERAGE Salary EMPL YEE C 3 COUNT Ssn AVERAGE Salary EMPLOYEE R a Dno Noofempoyees Averagesal b Dno Couansn Averagesalary 5 4 33250 5 4 33250 4 3 31 000 4 3 31 000 1 1 55000 1 1 55000 35125 C Averagesalary
Are you sure you want to buy this material for
You're already Subscribed!
Looks like you've already subscribed to StudySoup, you won't need to purchase another subscription to get this material. To access this material simply click 'View Full Document'