Chapter 7

1. Chapter 7 The Relational Data Model, Relational Constraints & The Relational Algebra Chapter 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

4. For the relation schema President (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

5. President Chapter 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

7. Chapter 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 Year Person Name Street City Zipcode PhoneNumber Chapter 7

9. Constrains • 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 enforced Chapter 7

10. Relational 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

11. Chapter 7

12. Fig 7.6 1st 3 Tables Chapter 7

13. Fig 7.6 2nd 3 Tables Chapter 7

14. Referential 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 tuple Chapter 7

15. Chapter 7

16. Maintenance 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

17. Relational Algebra • Select : s<selection condition>(<relation name>) • Project : p<attribute list>(<relation name>) s(DNO =4 & SALARY>25000)OR(DNO=5 & SALARY.30000) (EMPLOYEE) p LNAME, FNAME, SALARY (EMPLOYEE), p SEX, SALARY (EMPLOYEE)* * Projection requires duplicate elimination. Chapter 7

18. Fig 7.8 Chapter 7

19. Relational Algebra Expressions • Query: Retrieve first name, last name, salary of employee in department number 5. (a) p FNAME,LNAME, SALARY (s DNO=5(EMPLOYEE)) (b)TEMP <-- sDNO=5 (EMPLOYEE) R(FIRSTNAME, LASTNAME, SALARY)* <-- p FNAME, LNAME,SALARY(TEMP) * Attributes renamed. Chapter 7

20. Chapter 7

21. Fig 7.11 Chapter 7

22. Fig 7.12 Chapter 7

23. Joins • 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

24. Joins (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

25. Joins (con’t) • Join: <relation name> |><|<join condition> <relation name> Query: Retrieve the name of each department manager. DEPT_MGR¬ DEPARTMENT|><|MGRSSN=SSN EMPLOYEE Chapter 7

26. Chapter 7

27. Connect_AC Chapter 7

28. Fig 7.14 Chapter 7

29. Division • If r is an instance of R(Z) and S is an instance of S(X) and XÍZ, 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 tuple in S Chapter 7

30. Fig 7.15 Chapter 7

31. Aggregate (or Set) Functions <Grouping attributes>F<function list>(<relation name>) • Valid Aggregate Functions • Count • Average • Maximum • Minimum Chapter 7

32. Fig 7.16 Chapter 7

33. ER 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. Choose one of the key attributes of E as primary key for R. If the chosen key of E is composite, the set of simple attributes that form it will together form the primary key of R. Chapter 7

34. ER to Relational Mapping(con’t) • Step 2: For each weak entity type W in the ER Schema with owner entity type E, create a relation R, and include all simple attributes (or simple components of composite attributes of W as attributes of R. In addition, include as foreign key attributes of R the primary key attribute(s) of the relation(s) that correspond to the owner entity type(s). Chapter 7

35. ER to Relational Mapping(con’t) • Step 3: For each binary 1:1 relationship type R in the ER schema, identify the relations S and T that correspond to the entity types participating in R. Choose one of the relations-S, say- and include as 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. Include the simple attributes of the 1:1 relationship type R as attributes of S. Chapter 7

36. ER to Relational Mapping(con’t) • Step 4: For each regular (non-weak) binary 1:N relationship type R, identify the relation S that represents the participating entity type at the N-side of the relationship type. Include as foreign key in S the primary key of the relations T that represents the other entity type participating in R. Include an simple attributes of the 1:n relationship type as attributes of S. Chapter 7

37. ER to Relational Mapping(con’t) • Step 5: For each binary M:N relationship type R, create a new relation S to represent R. Include as foreign key attributes in S the primary keys of the relations that represent the participating entity types; their combination will form the primary key of S. Also, include any simple attributes of the M:N relationship type as attributes of S. Chapter 7

38. ER to Relational Mapping(con’t) • Step 6: For each multivalued attribute A, create a new relation R that includes an attribute corresponding to A plus the primary key attribute K(as a foreign key in R) of the relation that represents the entity type or relationship type that has A as an attribute. The primary key of R is the combination of A and K. If a multivalued attribute is composite, we include its components. Chapter 7

39. ER to Relational Mapping(con’t) • Step 7: For each n-ary relationship type R, n>2, create a new relation S to represent R. Include as foreign key attributes in the S the primary keys of the relations that represent the participating entity types. Also include any simple attributes of the n-ary relationship types as attributes of S. The primary key for S is usually a combination of all the foreign keys that reference the relations representing the participating entity types. However, if Chapter 7

40. Step 7 (con’t) The participation constraint (min,max) of one of the entity types E participating in the R has max =1, then the primary key of S can be the single foreign key attribute that references the relation E’ corresponding to E; this is because , in this case, each entity e in E will participate in at most one relationship instance of R and hence can uniquely identify that relationship instance. Chapter 7