Database Systems - Lec 4: Relational Data Modeland ER-/EER- to - Relational Mapping - Nguyen Thanh Tung

Nội dung text: Database Systems - Lec 4: Relational Data Modeland ER-/EER- to - Relational Mapping - Nguyen Thanh Tung

1. Outline ▪ Relational Data Model ▪ ER-/EER-to-Relational Mapping ▪ Reading Suggestion: • [1]: Chapters 5, 7, 12 • [2]: Chapters 15, 16 2
2. Basic Concepts ▪ The relational model of data is based on the concept of a relation ▪ A relation is a mathematical concept based on the ideas of sets ▪ The model was first proposed by Dr. E.F. Codd of IBM in 1970 in the following paper: "A Relational Model for Large Shared Data Banks," Communications of the ACM, June 1970 4
3. Basic Concepts ▪ Tuple: row/record in table ▪ Cardinality: number of tuples in a table ▪ Database schema S = {R1, R2, , Rm} 6
4. Basic Concepts ▪ A relation can be conveniently represented by a table, as the example shows ▪ The columns of the tabular relation represent attributes ▪ Each attribute has a distinct name, and is always referenced by that name, never by its position ▪ Each row of the table represents a tuple. The ordering of the tuples is immaterial and all tuples must be distinct 8
5. Basic Concepts Notes Informal Terms Formal Terms Table Relation Column Header Attribute All possible Column Domain Values Row Tuple Table Definition Schema of a Relation Populated Table State of the Relation 10
6. Relational Integrity Constraints ▪ Null value • Represents value for an attribute that is currently unknown or inapplicable for tuple • Deals with incomplete or exceptional data • Represents the absence of a value and is not the same as zero or spaces, which are values 12
7. Relational Integrity Constraints Key Constraints ▪ The CAR relation, with two candidate keys: License_Number and Engine_Serial_Number 14
8. Relational Integrity Constraints Referential Integrity ▪ A constraint involving two relations (the previous constraints involve a single relation) ▪ Used to specify a relationship among tuples in two relations: the referencing relation and the referenced relation ▪ 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 16
9. Relational Integrity Constraints Referential Integrity 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 the 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 18
10. Relational Integrity Constraints Other Types of Constraints ▪ Semantic Integrity Constraints: - based on application semantics and cannot be expressed by the model per se - E.g., “the max. no. of hours per employee for all projects he or she works on is 56 hrs per week” - A constraint specification language may have to be used to express these - SQL-99 allows triggers and ASSERTIONS to allow for some of these ▪ State/static constraints (so far) ▪ Transition/dynamic constraints: e.g., “the salary of an employee can only increase” 20
11. Update Operations on Relations ▪ INSERT a tuple ▪ DELETE a tuple ▪ MODIFY a tuple ▪ Integrity constraints should not be violated by the update operations 22
12. Update Operations on Relations ▪ Deletion: to remove an existing tuple t from a relation R. When deleting a tuple, the following constraints must not be violated: • The tuple must already exist in the database • The referential integrity constraint is not violated ▪ Modification: to change values of some attributes of an existing tuple t in a relation R 24
13. Outline ▪ Relational Data Model ▪ ER-/EER-to-Relational Mapping ▪ Reading Suggestion: • [1]: Chapters 5, 7, 12 • [2]: Chapters 15, 16 26
14. Main Phases of Database Design ▪ Three main phases • Conceptual database design • Logical database design • Physical database design ▪ Detailed discussions: see [1] (chapter 12) • Six phases 28
15. Main Phases of Database Design ▪ Conceptual database design • The process of constructing a model of the data used in an enterprise, independent of all physical considerations • Model comprises entity types, relationship types, attributes and attribute domains, primary and alternate keys, structural and integrity constraints ▪ Logical database design • The process of constructing a model of the data used in an enterprise based on a specific data model (e.g. relational), but independent of a particular DBMS and other physical considerations • ER- & EER-to-Relational Mapping • Normalization (w8) 30
16. The ERD for the COMPANY database 32
17. ER- & EER-to-Relational Mapping ▪ ER- • Step 1: Mapping of Regular Entity Types • Step 2: Mapping of Weak Entity Types • Step 3: Mapping of Binary 1:1 Relationship Types • Step 4: Mapping of Binary 1:N Relationship Types • Step 5: Mapping of Binary M:N Relationship Types • Step 6: Mapping of Multivalued attributes • Step 7: Mapping of N-ary Relationship Types ▪ EER- • Step 8: Options for Mapping Specialization or Generalization. • Step 9: Mapping of Union Types (Categories) 34
18. The ERD for the COMPANY database Strong Entity Types
19. The ERD for the COMPANY database Owner’s PK PK Weak Entity Types Partial key
20. ER-to-Relational Mapping ▪ ER- • Step 1: Mapping of Regular Entity Types • Step 2: Mapping of Weak Entity Types • Step 3: Mapping of Binary 1:1 Relationship Types • Step 4: Mapping of Binary 1:N Relationship Types • Step 5: Mapping of Binary M:N Relationship Types • Step 6: Mapping of Multivalued attributes • Step 7: Mapping of N-ary Relationship Types ▪ Transformation of binary relationships - depends on functionality of relationship and membership class of participating entity types 40
21. ER-to-Relational Mapping DEPARTMENT OFFER MODULE 1 N ▪ Assume every module must be offered by a department, then the entity type MODULE is a mandatory member of the relationship OFFER. The relation for MODULE is: MODULE(MDL-NUMBER, TITLE, TERM, , DNAME) 42
22. Result of mapping the COMPANY ER schema into a relational schema 44
23. ER -to-Relational Mapping N 1 BORROWER ON_LOAN BOOK ▪ One possible representation of the relationship: BORROWER(BNUMBER, NAME, ADDRESS, ) BOOK(ISBN, TITLE, , BNUMBER) ▪ A better alternative: BORROWER(BNUMBER, NAME, ADDRESS, ) BOOK(ISBN, TITLE, ) ON_LOAN(ISBN, BNUMBER) 46
24. Result of mapping the COMPANY ER schema into a relational schema ??? [1]: Step 4, chapter 7 48
25. The ERD for the COMPANY database M:N
26. ER-to-Relational Mapping ▪ ER- • Step 1: Mapping of Regular Entity Types • Step 2: Mapping of Weak Entity Types • Step 3: Mapping of Binary 1:1 Relationship Types • Step 4: Mapping of Binary 1:N Relationship Types • Step 5: Mapping of Binary M:N Relationship Types • Step 6: Mapping of Multivalued attributes • Step 7: Mapping of N-ary Relationship Types 52
27. ER-to-Relational Mapping ▪ Example 1: 1:1 involuted relationship, in which the memberships for both entities are optional PERSON(ID, NAME, ADDRESS, ) MARRY(HUSBAND-ID, WIFE_ID, DATE_OF_MARRIAGE) 54
28. ER-to-Relational Mapping ▪ Step 6: Mapping of Multivalued attributes • For each multivalued attribute A, create a new relation R. This relation R will include 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 the multivalued attribute is composite, we include its simple components Example: The relation DEPT_LOCATIONS is created. The attribute DLOCATION represents the multivalued attribute LOCATIONS of DEPARTMENT, while DNUMBER-as foreign key-represents the primary key of the DEPARTMENT relation. The primary key of R is the combination of {DNUMBER, DLOCATION} 56
29. Result of mapping the COMPANY ER schema into a relational schema 58
30. ER-to-Relational Mapping Ternary relationship types: The SUPPLY relationship Note: if the cardinality constraint on any of the entity types E participating in the relationship is 1, the PK should not include the FK attributes that reference the relation E’ corresponding to E (see section 4.7 [1])
31. ER- & EER-to-Relational Mapping ▪ ER- • Step 1: Mapping of Regular Entity Types • Step 2: Mapping of Weak Entity Types • Step 3: Mapping of Binary 1:1 Relationship Types • Step 4: Mapping of Binary 1:N Relationship Types • Step 5: Mapping of Binary M:N Relationship Types • Step 6: Mapping of Multivalued attributes • Step 7: Mapping of N-ary Relationship Types ▪ EER- • Step 8: Options for Mapping Specialization or Generalization. • Step 9: Mapping of Union Types (Categories) 62
32. EER-to-Relational Mapping ▪ Option 8A: Multiple relations-Superclass and subclasses Create a relation L for C with attributes Attrs(L) = {k,a1, an} and PK(L) = k. Create a relation Li for each subclass Si, 1 < i < m, with the attributesAttrs(Li) = {k} U {attributes of Si} and PK(Li)=k. This option works for any specialization (total or partial, disjoint or over- lapping). ▪ Option 8B: Multiple relations-Subclass relations only Create a relation Li for each subclass Si, 1 < i < m, with the attributes Attr(Li) = {attributes of Si} U {k,a1 ,an} and PK(Li) = 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). 64
33. Example: Option 8B Tonnage 66
34. Example: Option 8C EngType 68
35. EER-to-Relational Mapping ▪ Mapping of Shared Subclasses (Multiple Inheritance) • A shared subclass, such as STUDENT_ASSISTANT, 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 8C and 8D are used for the shared class STUDENT_ASSISTANT. 70
36. Example: Mapping of Shared Subclasses Course Major 72
37. Example: Mapping of Union Types OwnerId CYear 74
38. Q&A 76