Database Systems - Chapter 4: Relational Model - Trương Quỳnh Chi

Nội dung text: Database Systems - Chapter 4: Relational Model - Trương Quỳnh Chi

1. Contents 1 Relational Data Model 2 Main Phases of Database Design 3 ER-/EER-to-Relational Mapping 2
2. Relational Data Model  Basic Concepts: relational data model, relation schema, domain, tuple, cardinality & degree, database schema, etc.  Relational Integrity Constraints  key, primary key & foreign key  entity integrity constraint  referential integrity  Update Operations on Relations 4
3. Basic Concepts  Relational data model: represents a database in the form of relations - 2-dimensional table with rows and columns of data. A database may contain one or more such tables. A relation schema is used to describe a relation  Relation schema: R(A1, A2, , An) is made up of a relation name R and a list of attributes A1, A2, . . ., An. Each attribute Ai is the name of a role played by some domain D in the relation schema R. R is called the name of this relation 6
4. Basic Concepts  Tuple: row/record in table  Cardinality: number of tuples in a table  Database schema S = {R1, R2, , Rm} 8
5. Basic Concepts Relational data model Database schema Relation schema Relation Tuple Attribute 10
6. Basic Concepts 12
7. Relational Integrity Constraints  Constraints are conditions that must hold on all valid relation instances. There are three main types of constraints:  Key constraints  Entity integrity constraints  Referential integrity constraints  But 14
8. Relational Integrity Constraints - Key Constraints  Superkey of R: A set of attributes SK of R such that no two tuples in any valid relation instance 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]  Key of R: A "minimal" superkey; that is, a superkey K such that removal of any attribute from K results in a set of attributes that is not a superkey 16
9. Relational Integrity Constraints - Key Constraints  The CAR relation, with two candidate keys: License_Number and Engine_Serial_Number 18
10. 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 20
11. Relational Integrity Constraints - Referential Integrity  The value in the foreign key column (or columns) FK of 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 22
12. 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” 24
13. Update Operations on Relations  Insertion: to insert a new tuple t into a relation R. When inserting a new tuple, it should make sure that the database constraints are not violated:  The value of an attribute should be of the correct data type (i.e. from the appropriate domain).  The value of a prime attribute (i.e. the key attribute) must not be null  The key value(s) must not be the same as that of an existing tuple in the same relation  The value of a foreign key (if any) must refer to an existing tuple in the corresponding relation  Options if the constraints are violated: Homework !! 26
14. Update Operations on Relations  In case of integrity violation, several actions can be taken:  Cancel the operation that causes the violation (REJECT option)  Perform the operation but inform the user of the violation  Trigger additional updates so the violation is corrected (CASCADE option, SET NULL option)  Execute a user-specified error-correction routine  Again, homework !! 28
15. Main Phases of Database Design  Three main phases  Conceptual database design  Logical database design  Physical database design 30
16. Overview of Database Miniworld Design Process REQUIREMENTS - COLLECTION & ANALYSIS • Create a database Data requirements ent schema in CONCEPTUAL DESIGN implementation data independ Conceptual schema model of a commercial – DBMS DBMS LOGICAL DESIGN • Data model mapping (DATA MODEL MAPPING) is often automated or Database schema semi-automated within specific – the database design PHYSICAL DESIGN tool. DBMS Internal schema 32 Database Design
17. Entity-Relationship Diagram (ERD) for COMPANY Database 34
18. Contents 1 Relational Data Model 2 Main Phases of Database Design 3 ER-/EER-to-Relational Mapping 36
19. ER-to-Relational Mapping  Step 1: Mapping of Regular (strong) Entity Types  Entity > Relation  Attribute of entity > Attribute of relation  Primary key of entity > Primary key of relation  Example: We create the relations EMPLOYEE, DEPARTMENT, and PROJECT in the relational schema corresponding to the regular entities in the ER diagram. SSN, DNUMBER, and PNUMBER are the primary keys for the relations EMPLOYEE, DEPARTMENT, and PROJECT as shown 38
20. Step 1: Mapping of Regular (strong) Entity Types 40
21. Owner’s PK PK Weak Entity Type Partial key 42
22. 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 44
23. 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) 46
24. Step 3-4: Mapping of Relationship Types (Mandatory) 48
25. 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)
26. Step 3-4: Mapping of Relationship Types (Optional) 52
27. ER-to-Relational Mapping  Transformation of recursive/involuted relationships  Relationship among different instances of the same entity  The name(s) of the prime attribute(s) needs to be changed to reflect the role each entity plays in the relationship 1 PERSON MARRY M 1 PART COMPRISE N N EMPLOYEE SUPERVISE 1 56
28. ER-to-Relational Mapping  Example 2: 1:M involuted relationship  If the relationship is mandatory or almost mandatory: EMPLOYEE(ID, ENAME, , SUPERVISOR_ID)  If the relationship is optional: EMPLOYEE(ID, ENAME, ) SUPERVISE(ID, START_DATE, , SUPERVISOR_ID)  Example 3: N:M involuted relationship PART(PNUMBER, DESCRIPTION, ) COMPRISE( MAJOR-PNUMBER, MINOR-PNUMBER, QUANTITY) 58
29. 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} 60
30. Result of mapping the COMPANY ER schema into a relational schema 62
31. ER-to-Relational Mapping Ternary relationship types: The SUPPLY relationship N N N 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 64
32. 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) 66
33. 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). 68
34. Example: Option 8B Tonnage 70
35. Example: Option 8C EngType 72
36. 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. 74
37. Example: Mapping of Shared Subclasses 76
38. Example: Mapping of Union Types 78
39. Exercise 2 82
40. 84 Exercise 4