Скачать презентацию Lecture 05 Relational Database Design by ER- Скачать презентацию Lecture 05 Relational Database Design by ER-

fc146327f4c7d5d1ada0247217d429a2.ppt

  • Количество слайдов: 64

Lecture 05: Relational Database Design by ER- & EER-to-Relational Mapping Dr. Dang Tran Khanh Lecture 05: Relational Database Design by ER- & EER-to-Relational Mapping Dr. Dang Tran Khanh M. eng. Tran Minh Quang Department of Information Systems Faculty of Computer Science & Engineering

Outline l l l Main Phases of Database Design Conceptual Database Design Logical Database Outline l l l Main Phases of Database Design Conceptual Database Design Logical Database Design – l l ER- & EER-to-Relational Mapping Exercises Reading Suggestion: – – [1]: Chapters 7, 12 [2]: Chapters 15, 16 Dr. Dang Tran Khanh ([email protected] edu. vn), M. eng. Tran Minh Quang ([email protected] hcmut. edu. vn) 2

Main Phases of Database Design l Three main phases – – – l Conceptual Main Phases of Database Design l Three main phases – – – l Conceptual database design Logical database design Physical database design Detailed discussions: see [1] (chapter 12) – Six phases Dr. Dang Tran Khanh ([email protected] edu. vn), M. eng. Tran Minh Quang ([email protected] hcmut. edu. vn) 3

A simplified diagram to illustrate the main phases of database design Dr. Dang Tran A simplified diagram to illustrate the main phases of database design Dr. Dang Tran Khanh ([email protected] edu. vn), M. eng. Tran Minh Quang ([email protected] hcmut. edu. vn)

Main Phases of Database Design l Conceptual database design – l The process of Main Phases of Database Design l Conceptual database design – l The process of constructing a model of the data used in an enterprise, independent of all physical considerations 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 Dr. Dang Tran Khanh ([email protected] edu. vn), M. eng. Tran Minh Quang ([email protected] hcmut. edu. vn) 5

Main Phases of Database Design l Physical database design – The process of producing Main Phases of Database Design l Physical database design – The process of producing a description of the implementation of the database on secondary storage; it describes the base relations, file organizations, and indexes design used to achieve efficient access to the data, and any associated integrity constraints and security measures Dr. Dang Tran Khanh ([email protected] edu. vn), M. eng. Tran Minh Quang ([email protected] hcmut. edu. vn) 6

Conceptual Database Design Summarization l l Read [1]: chapters 3, 12 for details To Conceptual Database Design Summarization l l Read [1]: chapters 3, 12 for details To build a conceptual data model of the data requirements of the enterprise – Model comprises entity types, relationship types, attributes and attribute domains, primary and alternate keys, structural and integrity constraints Dr. Dang Tran Khanh ([email protected] edu. vn), M. eng. Tran Minh Quang ([email protected] hcmut. edu. vn) 7

Conceptual Database Design Summarization l l l l l Step 1: Identify entity types Conceptual Database Design Summarization l l l l l Step 1: Identify entity types Step 2: Identify relationship types Step 3: Identify and associate attributes with entity or relationship types Step 4: Determine attribute domains Step 5: Determine candidate, primary, and alternate key attributes Step 6: Consider use of enhanced modeling concepts (optional step) Step 7: Check model for redundancy Step 8: Validate conceptual model against user transactions Step 9: Review conceptual data model with user Dr. Dang Tran Khanh ([email protected] edu. vn), M. eng. Tran Minh Quang ([email protected] hcmut. edu. vn) 8

Conceptual Database Design Summarization l Step 1: Identify entity types – l Step 2: Conceptual Database Design Summarization l Step 1: Identify entity types – l Step 2: Identify relationship types – l To identify the important relationships that exist between the entity types Step 3: Identify and associate attributes with entity or relationship types – l To identify the required entity types To associate attributes with the appropriate entity or relationship types and document the details of each attribute Step 4: Determine attribute domains – To determine domains for the attributes in the data model and document the details of each domain Dr. Dang Tran Khanh ([email protected] edu. vn), M. eng. Tran Minh Quang ([email protected] hcmut. edu. vn) 9

Conceptual Database Design Summarization l Step 5: Determine candidate, primary, and alternate key attributes Conceptual Database Design Summarization l Step 5: Determine candidate, primary, and alternate key attributes – l Step 6: Consider use of enhanced modeling concepts (optional step) – l To identify the candidate key(s) for each entity and if there is more than one candidate key, to choose one to be the primary key and the others as alternate keys To consider the use of enhanced modeling concepts, such as specialization/generalization, categories (union types) Step 7: Check model for redundancy – To check for the presence of any redundancy in the model and to remove any that does exist Dr. Dang Tran Khanh ([email protected] edu. vn), M. eng. Tran Minh Quang ([email protected] hcmut. edu. vn) 10

Conceptual Database Design Summarization l Step 8: Validate conceptual model against user transactions – Conceptual Database Design Summarization l Step 8: Validate conceptual model against user transactions – l Step 9: Review conceptual data model with user – l To ensure that the conceptual model supports the required transactions To review the conceptual data model with the user to ensure that the model is a ‘true’ representation of the data requirements of the enterprise Check with the previous ERD exercises !! Dr. Dang Tran Khanh ([email protected] edu. vn), M. eng. Tran Minh Quang ([email protected] hcmut. edu. vn) 11

A simplified diagram to illustrate the main phases of database design Dr. Dang Tran A simplified diagram to illustrate the main phases of database design Dr. Dang Tran Khanh ([email protected] edu. vn), M. eng. Tran Minh Quang ([email protected] hcmut. edu. vn)

Logical Database Design l To translate the conceptual data model into a logical data Logical Database Design l To translate the conceptual data model into a logical data model and then to validate this model to check that it is structurally correct using normalization and supports the required transactions Dr. Dang Tran Khanh ([email protected] edu. vn), M. eng. Tran Minh Quang ([email protected] hcmut. edu. vn) 13

Logical Database Design l Logical database design for the relational model – – – Logical Database Design l Logical database design for the relational model – – – – l Step 1: Derive relations for logical data model Step 2: Validate relations using normalization Step 3: Validate relations against user transactions Step 4: Define integrity constraints Step 5: Review logical data model with user Step 6: Merge logical data models into global model (optional step) Step 7: Check for future growth ER- & EER-to-Relational Mapping Dr. Dang Tran Khanh ([email protected] edu. vn), M. eng. Tran Minh Quang ([email protected] hcmut. edu. vn) 14

The ERD for the COMPANY database Dr. Dang Tran Khanh (dtkhanh@hcmut. edu. vn), M. The ERD for the COMPANY database Dr. Dang Tran Khanh ([email protected] edu. vn), M. eng. Tran Minh Quang ([email protected] hcmut. edu. vn)

Result of mapping the COMPANY ER schema into a relational schema Dr. Dang Tran Result of mapping the COMPANY ER schema into a relational schema Dr. Dang Tran Khanh ([email protected] edu. vn), M. eng. Tran Minh Quang ([email protected] hcmut. edu. vn)

ER- & EER-to-Relational Mapping l ER– – – – l Step 1: Mapping of ER- & EER-to-Relational Mapping l ER– – – – l 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) Dr. Dang Tran Khanh ([email protected] edu. vn), M. eng. Tran Minh Quang ([email protected] hcmut. edu. vn) 17

ER-to-Relational Mapping l Step 1: Mapping of Regular (strong) Entity Types – – Entity ER-to-Relational Mapping l 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 Dr. Dang Tran Khanh ([email protected] edu. vn), M. eng. Tran Minh Quang ([email protected] hcmut. edu. vn) 18

The ERD for the COMPANY database Strong Entity Types Dr. Dang Tran Khanh (dtkhanh@hcmut. The ERD for the COMPANY database Strong Entity Types Dr. Dang Tran Khanh ([email protected] edu. vn), M. eng. Tran Minh Quang ([email protected] hcmut. edu. vn)

ER-to-Relational Mapping l Step 2: Mapping of Weak Entity Types – – – For ER-to-Relational Mapping l Step 2: Mapping of Weak Entity Types – – – 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) The primary key of R is the combination of the primary key(s) of the owner(s) and the partial key of the weak entity type W, if any Example: Create the relation DEPENDENT in this step to correspond to the weak entity type DEPENDENT. Include the primary key SSN of the EMPLOYEE relation as a foreign key attribute of DEPENDENT (renamed to ESSN) The primary key of the DEPENDENT relation is the combination {ESSN, DEPENDENT_NAME} because DEPENDENT_NAME is the partial key of DEPENDENT Note: CASCADE option as implemented Dr. Dang Tran Khanh ([email protected] edu. vn), M. eng. Tran Minh Quang ([email protected] hcmut. edu. vn) 20

The ERD for the COMPANY database Owner’s PK PK Weak Entity Types Partial key The ERD for the COMPANY database Owner’s PK PK Weak Entity Types Partial key Dr. Dang Tran Khanh ([email protected] edu. vn), M. eng. Tran Minh Quang ([email protected] hcmut. edu. vn)

Result of mapping the COMPANY ER schema into a relational schema Dr. Dang Tran Result of mapping the COMPANY ER schema into a relational schema Dr. Dang Tran Khanh ([email protected] edu. vn), M. eng. Tran Minh Quang ([email protected] hcmut. edu. vn)

ER-to-Relational Mapping l ER– – – – l Step 1: Mapping of Regular Entity ER-to-Relational Mapping l ER– – – – l 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 Dr. Dang Tran Khanh ([email protected] edu. vn), M. eng. Tran Minh Quang ([email protected] hcmut. edu. vn) 23

ER-to-Relational Mapping l Mandatory membership class – – – For two entity types E ER-to-Relational Mapping l Mandatory membership class – – – For two entity types E 1 and E 2: If E 2 is a mandatory member of an N: 1 (or 1: 1) relationship with E 1, then the relation for E 2 will include the prime attributes of E 1 as a foreign key to represent the relationship For a 1: 1 relationship: If the membership class for E 1 and E 2 are both mandatory, a foreign key can be used in either relation For an N: 1 relationship: If the membership class of E 2, which is at the N-side of the relationship, is optional (i. e. partial), then the above guideline is not applicable Dr. Dang Tran Khanh ([email protected] edu. vn), M. eng. Tran Minh Quang ([email protected] hcmut. edu. vn) 24

ER-to-Relational Mapping DEPARTMENT l 1 OFFER N MODULE Assume every module must be offered ER-to-Relational Mapping DEPARTMENT l 1 OFFER N MODULE 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) Dr. Dang Tran Khanh ([email protected] edu. vn), M. eng. Tran Minh Quang ([email protected] hcmut. edu. vn) 25

The ERD for the COMPANY database N: 1 Relationships Dr. Dang Tran Khanh (dtkhanh@hcmut. The ERD for the COMPANY database N: 1 Relationships Dr. Dang Tran Khanh ([email protected] edu. vn), M. eng. Tran Minh Quang ([email protected] hcmut. edu. vn)

Result of mapping the COMPANY ER schema into a relational schema Dr. Dang Tran Result of mapping the COMPANY ER schema into a relational schema Dr. Dang Tran Khanh ([email protected] edu. vn), M. eng. Tran Minh Quang ([email protected] hcmut. edu. vn)

ER-to-Relational Mapping l Optional membership classes – – If entity type E 2 is ER-to-Relational Mapping l Optional membership classes – – If entity type E 2 is an optional member of the N: 1 relationship with entity type E 1 (i. e. E 2 is at the N-side of the relationship), then the relationship is usually represented by a new relation containing the prime attributes of E 1 and E 2, together with any attributes of the relationship. The key of the entity type at the N-side (i. e. E 2) will become the key of the new relation If both entity types in a 1: 1 relationship have the optional membership, a new relation is created which contains the prime attributes of both entity types, together with any attributes of the relationship. The prime attribute(s) of either entity type will be the key of the new relation Dr. Dang Tran Khanh ([email protected] edu. vn), M. eng. Tran Minh Quang ([email protected] hcmut. edu. vn) 28

ER-to-Relational Mapping l One possible representation of the relationship: BORROWER(BNUMBER, NAME, ADDRESS, . . ER-to-Relational Mapping l One possible representation of the relationship: BORROWER(BNUMBER, NAME, ADDRESS, . . . ) BOOK(ISBN, TITLE, . . . , BNUMBER) l A better alternative: BORROWER(BNUMBER, NAME, ADDRESS, . . . ) BOOK(ISBN, TITLE, . . . ) ON_LOAN(ISBN, BNUMBER) Dr. Dang Tran Khanh ([email protected] edu. vn), M. eng. Tran Minh Quang ([email protected] hcmut. edu. vn) 29

The ERD for the COMPANY database 1: N (both optional) Dr. Dang Tran Khanh The ERD for the COMPANY database 1: N (both optional) Dr. Dang Tran Khanh ([email protected] edu. vn), M. eng. Tran Minh Quang ([email protected] hcmut. edu. vn)

Result of mapping the COMPANY ER schema into a relational schema ? ? ? Result of mapping the COMPANY ER schema into a relational schema ? ? ? [1]: Step 4, p. 195, chapter 7 Dr. Dang Tran Khanh ([email protected] edu. vn), M. eng. Tran Minh Quang ([email protected] hcmut. edu. vn)

ER-to-Relational Mapping l N: M binary relationships: – – l An N: M relationship ER-to-Relational Mapping l N: M binary relationships: – – l An N: M relationship is always represented by a new relation which consists of the prime attributes of both participating entity types together with any attributes of the relationship The combination of the prime attributes will form the primary key of the new relation Example: ENROL is an M: N relationship between STUDENT and MODULE. To represent the relationship, we have a new relation: ENROL(SNUMBER, MDL-NUMBER, DATE) Dr. Dang Tran Khanh ([email protected] edu. vn), M. eng. Tran Minh Quang ([email protected] hcmut. edu. vn) 32

The ERD for the COMPANY database M: N Dr. Dang Tran Khanh (dtkhanh@hcmut. edu. The ERD for the COMPANY database M: N Dr. Dang Tran Khanh ([email protected] edu. vn), M. eng. Tran Minh Quang ([email protected] hcmut. edu. vn)

Result of mapping the COMPANY ER schema into a relational schema Dr. Dang Tran Result of mapping the COMPANY ER schema into a relational schema Dr. Dang Tran Khanh ([email protected] edu. vn), M. eng. Tran Minh Quang ([email protected] hcmut. edu. vn)

ER-to-Relational Mapping l ER– – – – Step 1: Mapping of Regular Entity Types ER-to-Relational Mapping l 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 Dr. Dang Tran Khanh ([email protected] edu. vn), M. eng. Tran Minh Quang ([email protected] hcmut. edu. vn) 35

ER-to-Relational Mapping l Transformation of recursive/involuted relationships – – Relationship among different instances of ER-to-Relational Mapping l 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 Dr. Dang Tran Khanh ([email protected] edu. vn), M. eng. Tran Minh Quang ([email protected] hcmut. edu. vn)

ER-to-Relational Mapping l Example 1: 1: 1 involuted relationship, in which the memberships for ER-to-Relational Mapping l 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) Dr. Dang Tran Khanh ([email protected] edu. vn), M. eng. Tran Minh Quang ([email protected] hcmut. edu. vn) 37

ER-to-Relational Mapping l Example 2: 1: M involuted relationship. – – l If the ER-to-Relational Mapping l Example 2: 1: M involuted relationship. – – l 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) Dr. Dang Tran Khanh ([email protected] edu. vn), M. eng. Tran Minh Quang ([email protected] hcmut. edu. vn) 38

ER- & EER-to-Relational Mapping l ER– – – – l Step 1: Mapping of ER- & EER-to-Relational Mapping l ER– – – – l 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) Dr. Dang Tran Khanh ([email protected] edu. vn), M. eng. Tran Minh Quang ([email protected] hcmut. edu. vn) 39

ER-to-Relational Mapping l Step 6: Mapping of Multivalued attributes – – For each multivalued ER-to-Relational Mapping l 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 keyrepresents the primary key of the DEPARTMENT relation. The primary key of R is the combination of {DNUMBER, DLOCATION} Dr. Dang Tran Khanh ([email protected] edu. vn), M. eng. Tran Minh Quang ([email protected] hcmut. edu. vn) 40

The ERD for the COMPANY database Multivalued Attr. Dang Tran Khanh (dtkhanh@hcmut. edu. vn), The ERD for the COMPANY database Multivalued Attr. Dang Tran Khanh ([email protected] edu. vn), M. eng. Tran Minh Quang ([email protected] hcmut. edu. vn)

Result of mapping the COMPANY ER schema into a relational schema Dr. Dang Tran Result of mapping the COMPANY ER schema into a relational schema Dr. Dang Tran Khanh ([email protected] edu. vn), M. eng. Tran Minh Quang ([email protected] hcmut. edu. vn)

ER-to-Relational Mapping l Step 7: Mapping of N-ary Relationship Types – – – For ER-to-Relational Mapping l Step 7: Mapping of N-ary Relationship Types – – – For each n-ary relationship type R, where n>2, create a new relationship S to represent R Include as foreign key attributes in S the primary keys of the relations that represent the participating entity types Also include any simple attributes of the n-ary relationship type (or simple components of composite attributes) as attributes of S Example: The relationship type SUPPY in the ER below. This can be mapped to the relation SUPPLY shown in the relational schema, whose primary key is the combination of the three foreign keys {SNAME, PARTNO, PROJNAME} Dr. Dang Tran Khanh ([email protected] edu. vn), M. eng. Tran Minh Quang ([email protected] hcmut. edu. vn) 43

ER-to-Relational Mapping FIGURE 4. 11 Ternary relationship types (a) The SUPPLY relationship Note: if ER-to-Relational Mapping FIGURE 4. 11 Ternary relationship types (a) 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]) Dr. Dang Tran Khanh ([email protected] edu. vn), M. eng. Tran Minh Quang ([email protected] hcmut. edu. vn)

ER-to-Relational Mapping Summary of Mapping Constructs & Constraints Correspondence between ER and Relational Models ER-to-Relational Mapping Summary of Mapping Constructs & Constraints Correspondence between ER and Relational Models ER Model Entity type 1: 1 or 1: N relationship type M: N relationship type n-ary relationship type Simple attribute Composite attribute Multivalued attribute Value set Key attribute Relational Model “Entity” relation Foreign key (or “relationship” relation) “Relationship” relation and two foreign keys “Relationship” relation and n foreign keys Attribute Set of simple component attributes Relation and foreign key Domain Primary (or secondary) key Dr. Dang Tran Khanh ([email protected] edu. vn), M. eng. Tran Minh Quang ([email protected] hcmut. edu. vn) 45

ER- & EER-to-Relational Mapping l ER– – – – l Step 1: Mapping of ER- & EER-to-Relational Mapping l ER– – – – l 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) Dr. Dang Tran Khanh ([email protected] edu. vn), M. eng. Tran Minh Quang ([email protected] hcmut. edu. vn) 46

EER-to-Relational Mapping l Step 8: Options for Mapping Specialization or Generalization. Convert each specialization EER-to-Relational Mapping l Step 8: Options for Mapping Specialization or Generalization. Convert each specialization with m subclasses {S 1, S 2, …. , Sm} and generalized superclass C, where the attributes of C are {k, a 1, …an} and k is the (primary) key, into relational schemas using one of the four following options: Option 8 A: Multiple relations-Superclass and subclasses. Create a relation L for C with attributes Attrs(L) = {k, a 1, …an} and PK(L) = k. Create a relation Li for each subclass Si, 1 <= i <= m, with the attributes. Attrs(Li) = {k} U {attributes of Si} and PK(Li)=k. This option works for any specialization (total or partial, disjoint of overlapping). Option 8 B: 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, a 1…, 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) Dr. Dang Tran Khanh ([email protected] edu. vn), M. eng. Tran Minh Quang ([email protected] hcmut. edu. vn) 47

EER-to-Relational Mapping Option 8 C: Single relation with one type attribute Create a single EER-to-Relational Mapping Option 8 C: Single relation with one type attribute Create a single relation L with attributes Attrs(L) = {k, a 1, …an} U {attributes of S 1} U…U {attributes of Sm} U {t} and PK(L) = k. The attribute t is called a type (or discriminating) attribute that indicates the subclass to which each tuple belongs Option 8 D: Single relation with multiple type attributes Create a single relation schema L with attributes Attrs(L) = {k, a 1, …an} U {attributes of S 1} U…U {attributes of Sm} U {t 1, t 2, …, tm} and PK(L) = k. Each ti, 1 <= i <= m, is a Boolean type attribute indicating whether a tuple belongs to the subclass Si Option 8 A is preferred !! Dr. Dang Tran Khanh ([email protected] edu. vn), M. eng. Tran Minh Quang ([email protected] hcmut. edu. vn) 48

Example: Option 8 A Dr. Dang Tran Khanh (dtkhanh@hcmut. edu. vn), M. eng. Tran Example: Option 8 A Dr. Dang Tran Khanh ([email protected] edu. vn), M. eng. Tran Minh Quang ([email protected] hcmut. edu. vn)

Example: Option 8 B Tonnage Dr. Dang Tran Khanh (dtkhanh@hcmut. edu. vn), M. eng. Example: Option 8 B Tonnage Dr. Dang Tran Khanh ([email protected] edu. vn), M. eng. Tran Minh Quang ([email protected] hcmut. edu. vn)

Example: Option 8 C Eng. Type Serving as the type attribute Dr. Dang Tran Example: Option 8 C Eng. Type Serving as the type attribute Dr. Dang Tran Khanh ([email protected] edu. vn), M. eng. Tran Minh Quang ([email protected] hcmut. edu. vn)

Example: Option 8 D Boolean type Quang (quangtran@cse. hcmut. edu. vn) Dr. Dang Tran Example: Option 8 D Boolean type Quang ([email protected] hcmut. edu. vn) Dr. Dang Tran Khanh ([email protected] edu. vn), M. eng. Tran Minh attributes

EER-to-Relational Mapping of Shared Subclasses (Multiple Inheritance) – – A shared subclass, such as EER-to-Relational 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 8 C and 8 D are used for the shared class STUDENT_ASSISTANT Dr. Dang Tran Khanh ([email protected] edu. vn), M. eng. Tran Minh Quang ([email protected] hcmut. edu. vn) 53

Example: Mapping of Shared Subclasses Dr. Dang Tran Khanh (dtkhanh@hcmut. edu. vn), M. eng. Example: Mapping of Shared Subclasses Dr. Dang Tran Khanh ([email protected] edu. vn), M. eng. Tran Minh Quang ([email protected] hcmut. edu. vn)

Example: Mapping of Shared Subclasses Course Major Dr. Dang Tran Khanh (dtkhanh@hcmut. edu. vn), Example: Mapping of Shared Subclasses Course Major Dr. Dang Tran Khanh ([email protected] edu. vn), M. eng. Tran Minh Quang ([email protected] hcmut. edu. vn)

EER-to-Relational Mapping l Step 9: Mapping of Union Types (Categories). – – – For EER-to-Relational Mapping l Step 9: Mapping of Union Types (Categories). – – – For mapping a category whose defining superclasses have different keys, it is customary to specify a new key attribute, called a surrogate key, when creating a relation to correspond to the category. In the example below we can create a relation OWNER to correspond to the OWNER category and include any attributes of the category in this relation. The primary key of the OWNER relation is the surrogate key, which we called Owner. Id We also include the surrogate key attribute Owner. Id as FK in each relation corresponding to a superclass of the category in order to specify the correspondence in values between the surrogate key and the PK of each superclass Dr. Dang Tran Khanh ([email protected] edu. vn), M. eng. Tran Minh Quang ([email protected] hcmut. edu. vn) 56

Example Owner. Id CYear Dr. Dang Tran Khanh (dtkhanh@hcmut. edu. vn), M. eng. Tran Example Owner. Id CYear Dr. Dang Tran Khanh ([email protected] edu. vn), M. eng. Tran Minh Quang ([email protected] hcmut. edu. vn)

Exercises (in-class) Map the above ERD into relational schemas. Justify your choice of mapping Exercises (in-class) Map the above ERD into relational schemas. Justify your choice of mapping options. Dr. Dang Tran Khanh ([email protected] edu. vn), M. eng. Tran Minh Quang ([email protected] hcmut. edu. vn)

Summary l l l 3 Main Phases of Database Design: An Overview Conceptual Database Summary l l l 3 Main Phases of Database Design: An Overview Conceptual Database Design: A Summarization Logical Database Design – l l l ER- & EER-to-Relational Mapping Exercises & homework Reading Suggestion & homework: do not forget !! Next Lecture: (students’ presentation) – – Relational Algebra & Relational Calculus [1]: Chapter 6 Dr. Dang Tran Khanh ([email protected] edu. vn), M. eng. Tran Minh Quang ([email protected] hcmut. edu. vn) 59

Exercises l Homework: – – [1]: Exercise 7. 5, p. 204 Map all ERDs Exercises l Homework: – – [1]: Exercise 7. 5, p. 204 Map all ERDs of the previous exercises to corresponding relational database schemas Dr. Dang Tran Khanh ([email protected] edu. vn), M. eng. Tran Minh Quang ([email protected] hcmut. edu. vn) 60

Exercises l Homework: Give a database scheme of a company as customer(C-Id, name, addr, Exercises l Homework: Give a database scheme of a company as customer(C-Id, name, addr, city) – product(P-Id, name, unit, description) – buying(C-id, P-id, date, quantity, price) – selling(C-id, P-id, date, quantity, price) Answer following questions using SQL – Dr. Dang Tran Khanh ([email protected] edu. vn), M. eng. Tran Minh Quang ([email protected] hcmut. edu. vn) 61

Exercises l Homework: 1. 2. 3. 4. 5. 6. Display all customers but the Exercises l Homework: 1. 2. 3. 4. 5. 6. Display all customers but the information to be displayed are customer id and customer name only Similar to question 2 but customers to be selected are in HCM city only Display all the products sold on 10/03/2007 Display all the customers who supplied product P 01 on 10/03/2007 Similar to question 5 but the information to be displayed includes C-id, C-name, P-id, P-name. Dr. Dang Tran Khanh ([email protected] edu. vn), M. eng. Tran Minh Quang ([email protected] hcmut. edu. vn) 62

Exercises l Homework: 7. Display customers who buy the products supplied by customer whose Exercises l Homework: 7. Display customers who buy the products supplied by customer whose name is “Intel” 8. Display product which are concurrently bought and sold on 10/03/2007 9. Display customers who paid the greatest amount of money for buying product on 10/03/2007 10. Display customers who got the greatest amount of money by selling product to the company in the year 2006 Dr. Dang Tran Khanh ([email protected] edu. vn), M. eng. Tran Minh Quang ([email protected] hcmut. edu. vn) 63

Q&A Dr. Dang Tran Khanh (dtkhanh@hcmut. edu. vn), M. eng. Tran Minh Quang (quangtran@cse. Q&A Dr. Dang Tran Khanh ([email protected] edu. vn), M. eng. Tran Minh Quang ([email protected] hcmut. edu. vn) 64