Chapter 7Slide 2Slide 3Slide 4PresidentSlide 6Slide 7Slide 8ConstrainsRelational Database SchemaSlide 11Fig 7.6 1st 3 TablesFig 7.6 2nd 3 TablesReferential Integrity ConstraintsSlide 15Maintenance OperationsRelational AlgebraFig 7.8Relational Algebra ExpressionsSlide 20Fig 7.11Fig 7.12JoinsJoins (con’t)Slide 25Slide 26Connect_ACFig 7.14DivisionFig 7.15Aggregate (or Set) FunctionsFig 7.16ER to Relational MappingER to Relational Mapping(con’t)Slide 35Slide 36Slide 37Slide 38Slide 39Step 7 (con’t)Chapter 7 1Chapter 7The Relational Data Model, Relational Constraints & The Relational AlgebraChapter 7 2•A relation schema R, denoted R(A1, …, An), consists of a relation name R and a list of attributes A1,…,An eg. Course (CourseName, CourseNumber, InstructorName, StartTime, EndTime, Days)•Each attribute Ai is the name of a role played by domain dom (Ai). The number n of attributes is the degree of the relation schema.•A data type is specified for each dom(Ai), eg., char, int, money, ...Chapter 7 3•A relation (or relation instance) r of the relation schema R(A1,…An) is a set r={t1,...,tn} where each ti is a n-tuple of the form <r1,…,rn> s.t. each value ri  dom(Ai) or ri is a special null value.•The value of attribute Ai for tuple tj is denoted tj(Ai).•Informally, a relation is a table of tuples.Chapter 7 4For the relation schemaPresident (Name, YearStart, YearEnd)a relation is{<Clinton,’92,NULL >,<Bush,’88,’92>}** The intension of schema is to assert facts from the miniworld - equivalently, it defines a “predicate” for relation tuples to satisfy. The tuples in a relation are unordered, and need not be a complete set satisfying the schema predicate; however, no duplicates.Chapter 7 5PresidentName YearStart YearEndBush ‘88 ‘92Carter ‘76 ‘80Regan ‘80 ‘88Clinton ‘92 NULLChapter 7 6•A subset S of attributes of a relation schema R, s.t. NO two tuples in any relation instance r of R can have the same combination of values for attributes in S, is called a superkey. Thus, the values of the attributes in S uniquely identify a tuple. •A superkey K, with the property that removing any attribute from K leaves a subset that is not a superkey, is called a key: it is a minimal superkey.Chapter 7 7Car License Number EngineSerialNumberMake Model YearTexas ABC-739 A69352 Ford Mustang90Florida TVP-347 B43696 Olds Cutlass 93New York MPO-222 X83554 Olds Delta 89Person Name StreetAddress City ZipcodeJ. Smith 52 N. Cramer St. Milwaukee 53211P. Tan 162 Lake Dr. Red Bank 07703N. Ram 22 State St Ann Arbor 48105Chapter 7 8•A relation schema may have more than one key. Each such key is called a candidate key. One candidate key is designated the primary key and is used to identify tuples in a relation. Attributes in a primary key are shown underlined in a relation schema. Car LicenseNumber EngineSerial Number Make Model YearPerson Name Street City Zipcode PhoneNumberChapter 7 9Constrains•Domain Constraint: value of each attribute A must be an atomic value from dom(A). This means that the DBMS does type checking.•Key Constraint: No two tuples can have the same combination of values for attributes in the primary key. This is also DBMS enforcedChapter 7 10Relational Database Schema•A relational database schema is a set S = { R1, . . . , Rn} of relational schemas.•A relation database instance I of S is a set I = {r1, . . ., rn} of relational instances, s.t. each ri is an instance of Ri.Chapter 7 11Chapter 7 12Fig 7.6 1st 3 TablesChapter 7 13Fig 7.6 2nd 3 TablesChapter 7 14Referential Integrity Constraints•The arrows on the next slide represent referential integrity constraints. The value of the attribute at the tail of the arrow (called foreign key) must, for each tuple, be either null or equal to the value of the attribute at the head of the arrow for some tupleChapter 7 15Chapter 7 16Maintenance Operations•Insert: adds new tuples to a relation.•Delete: removes tuples from a relation.•Update: changes values of attributes of existing tuples in a relation.Chapter 7 17Relational Algebra•Select : <selection condition> (<relation name>)•Project : <attribute list> (<relation name>)(DNO =4 & SALARY>25000)OR(DNO=5 & SALARY.30000) (EMPLOYEE)LNAME, FNAME, SALARY (EMPLOYEE), SEX, SALARY (EMPLOYEE)** Projection requires duplicate elimination.Chapter 7 18Fig 7.8Chapter 7 19Relational Algebra Expressions•Query: Retrieve first name, last name, salary of employee in department number 5.(a) FNAME,LNAME, SALARY( DNO=5(EMPLOYEE))(b)TEMP <-- DNO=5 (EMPLOYEE)R(FIRSTNAME, LASTNAME, SALARY)* <-- FNAME, LNAME,SALARY(TEMP)* Attributes renamed.Chapter 7 20Chapter 7 21Fig 7.11Chapter 7 22Fig 7.12Chapter 7 23Joins•A Join is like a Cartesian Product except that it has a join condition in which an attribute(s) of one table are compared to those of another table.Chapter 7 24Joins (con’t)•There are three types of joins.– Equijoin Join. Where the attributes are equal and the result has both of them in it.– Natural Join. Where the attributes are both equal and have the same name. Result has that name only once.–Theta Join. The General Condition where a comparison is made between selected attributes of two or more tables.Chapter 7 25Joins (con’t)•Join: <relation name> |><|<join condition> <relation name>Query: Retrieve the name of each department manager.DEPT_MGRDEPARTMENT|><|MGRSSN=SSN EMPLOYEEChapter 7 26AB Flight Departs Arrives21 9:40 12:1030 12:30 15:3718 15:18 19:05BC Flight Departs Arrives11 9:18 11:10104 14:45 16:056 18:06 20:08Chapter 7 27Connect_ACABFlightABDepartsABArrivesBCFlightBCDeparts21 9:40 12:10 104 14:4521 9:40 12:10 6 18:0630 12:30 15:37 6 18:06Chapter 7 28Fig 7.14Chapter 7 29Division•If r is an instance of R(Z) and S is an instance of S(X) and Xthen rs is an instance of T(Y), where Y= Z-X and s.t. a tuple is in rs iff its values appear in r in combination with every tup le in SChapter 7 30Fig 7.15Chapter 7 31Aggregate (or Set) Functions<Grouping attributes>F<function list>(<relation name>)•Valid Aggregate Functions–Count–Average–Maximum–MinimumChapter 7 32Fig 7.16Chapter 7 33ER to Relational Mapping•Step 1: For each regular entity type E in the ER schema, create a relation R that includes all the simple attributes of E. Include on the simple component attributes of a composite attribute.