AnnouncementsSlide 2ConstraintsSlide 4Key ConstraintsKey Constraints (continued)Slide 7CAR table with two candidate keys – LicenseNumber chosen as Primary KeyMultiple RelationsRelational Database SchemaSlide 11Referential Integrity Constraints involve Two RelationsReferential IntegrityReferential Integrity (or foreign key) ConstraintSlide 15Other Types of ConstraintsSlide 17Update Operations on Relations Must not Violate ConstraintsUpdate Operations on RelationsPossible violations for each operationSlide 21Slide 22In-Class ExerciseRelational Algebra Chapter 6Relational AlgebraRelational Algebra queries are composed of a set of operatorsRelational Algebra expressionRelational Algebra OverviewSELECTUnary Relational Operations: SELECTSlide 31Unary Relational Operations: SELECT (contd.)PROJECTUnary Relational Operations: PROJECTUnary Relational Operations: PROJECT (cont.)Unary Relational Operations: PROJECT (contd.)Set Theory Operators: UNION, INTERSECTION & MINUSRelational Algebra Operations from Set Theory: UNIONSlide 39Example of the result of a UNION operationRelational Algebra Operations from Set TheoryRelational Algebra Operations from Set Theory: INTERSECTIONRelational Algebra Operations from Set Theory: SET DIFFERENCE (cont.)Example to illustrate the result of UNION, INTERSECT, and DIFFERENCERelational Algebra Operations from Set Theory: CARTESIAN PRODUCTRelational Algebra Operations from Set Theory: CARTESIAN PRODUCT (cont.)Slide 47Slide 48Binary Relational Operations: JOINBinary Relational Operations: JOIN (cont.)Example of applying the JOIN operationSome properties of JOINSlide 53Binary Relational Operations: EQUIJOINBinary Relational Operations: NATURAL JOIN OperationBinary Relational Operations NATURAL JOIN (contd.)Example of NATURAL JOIN operationComplete Set of Relational OperationsBinary Relational Operations: DIVISIONExample of DIVISIONRecap of Relational Algebra OperationsAnnouncements•Today–Finish RDM (Chapter 5), begin relational algebra•Reading–Sections 6.0-6.5•Program 2–Due Friday•Exam–Tuesday Oct 16, in class–Closed book–Will cover material through Thursday’s lectureClass bufMgr{... // Call DB object to allocate a run of new pages and // find a frame in the buffer pool for the first page // and pin it. If buffer is full, ask DB to deallocate // all these pages and return error Status newPage(int& firstPageId, Page*& firstpage,int howmany=1); // Check if this page is in buffer pool, otherwise // find a frame for this page, read in and pin it. // Also write out the old page if it's dirty before reading // if emptyPage==TRUE, then actually no read is done to bring // the page in. Status pinPage(int PageId_in_a_DB, Page*& page, int emptyPage=0, const char *filename=NULL);...}//// in your code// PageId pid; Page *pPageTmp SecondaryIndexHeaderPage *pHeaderPage; MINIBASE_BM->newPage( pid, pPageTmp ); pHeaderPage = (SecondaryIndexHeaderPage*)pPageTmp;ConstraintsConstraints•A key aspect of RDM is the ability to impose constraints on the database state•A constraint on a single relation places restrictions on valid relation states–Examples: •two students can’t have same student ID number–Example of key constraint•Student name cannot be NULL –Domain constraints (implicit)Key Constraints•Superkey of R: –Is a set of attributes SK of R with the following condition:•No two tuples in any valid relation state r(R) will have the same value for SK•That is, for any distinct tuples t1 and t2 in r(R), t1[SK]  t2[SK]•This condition must hold in any valid state r(R)•Key of R:–A "minimal" superkey–That is, a key is a superkey K such that removal of any attribute from K results in a set of attributes that is not a superkey (does not possess the superkey uniqueness property)Key Constraints (continued)•Example: Consider the CAR relation schema:–CAR(State, Reg#, SerialNo, Make, Model, Year)–CAR has two keys:•Key1 = {State, Reg#}•Key2 = {SerialNo}–Both are also superkeys of CAR–{SerialNo, Make} is a superkey but not a key.•In general:–Any key is a superkey (but not vice versa)–Any set of attributes that includes a key is a superkey–A minimal superkey is also a keyKey Constraints (continued)•If a relation has several candidate keys, one is chosen arbitrarily to be the primary key. –The primary key attributes are underlined.•Example: Consider the CAR relation schema:–CAR(State, Reg#, SerialNo, Make, Model, Year)–We chose SerialNo as the primary key•The primary key value is used to uniquely identify each tuple in a relation–Provides the tuple identity•Also used to reference the tuple from another tuple–General rule: Choose as primary key the smallest of the candidate keys (in terms of size)–Not always applicable – choice is sometimes subjectiveCAR table with two candidate keys – LicenseNumber chosen as Primary KeyMultiple Relations•Typically, a RDB has many relationsRelational Database Schema•Relational Database Schema:–A set S of relation schemas that belong to the same database.–S is the name of the whole database schema–S = {R1, R2, ..., Rn}–R1, R2, …, Rn are the names of the individual relation schemas within the database SCOMPANY Database SchemaReferential Integrity Constraints involve Two Relations•Example: DEPT_LOCATION.Dnumber must refer to an existing tuple in DEPARTMENT•Operationalized through concept of foreign keyReferential Integrity•Tuples in the referencing relation R1 have attributes FK (called foreign key attributes) that reference the primary key attributes PK of the referenced relation R2.–A tuple t1 in R1 is said to reference a tuple t2 in R2 if t1[FK] = t2[PK].•A referential integrity constraint can be displayed in a relational database schema as a directed arc from R1.FK to R2.Referential Integrity (or foreign key) Constraint•Statement of the constraint–The value in the foreign key column (or columns) FK of the the referencing relation R1 can be either:•(1) a value of an existing primary key value of a corresponding primary key PK in the referenced relation R2, or•(2) a null.•In case (2), the FK in R1 should not be a part of its own primary key.Referential Integrity Constraints for COMPANY databaseOther Types of Constraints•Semantic Integrity Constraints:–based on application semantics and cannot be expressed by the model per se–Example: “the max. no. of hours per employee for all projects he or she works