1 / 14

TOPIC 3 (part 1)

TOPIC 3 (part 1). Relational Database Design (Top Down). Dr. WENNY RAHAYU. Readings : Chpts 3 & 7, Elmasri & Navathe. -- From Data Modeling ( eg. ER Model to Relational Logical Model for implementation. TOP DOWN DESIGN. -- Normalization of Relations. BOTTOM UP DESIGN.

lbales
Download Presentation

TOPIC 3 (part 1)

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. TOPIC 3 (part 1) Relational Database Design (Top Down) Dr. WENNY RAHAYU • Readings : Chpts 3 & 7, Elmasri & Navathe

  2. -- From Data Modeling ( eg. ER Model to Relational Logical Model for implementation. TOP DOWN DESIGN -- Normalization of Relations BOTTOM UP DESIGN Relational Database Design Two Approaches in Relational Database Design Today’s lecture looks at top-down design, bottom-up design will be described the next lecture Topic 3 : Relational Database Design

  3. Relational Database Design Consider the following ER-Diagram manages empNo deptNo name deptName 1 (1, 1) 1 (0, 1) address Department Employee M (1, 1) degree M (1, m) works for 1 (1, m) 1 (0, m) has works on projNo M (1, 1) M (1, m) projName Dependent Project dependentName M (0, m) dateOfBirth partNo supplies partName M (1, m) M (1, m) Supplier Part quantity supNo supName date Topic 3 : Relational Database Design

  4. Relational Database Design (1) Transformation of E-R Model into Relational Logical Model ( Top Down design ) -- E-R to Relational Mapping Algorithm will be explained here step by step. STEP 1:For each entity in the ER model, create a relation ( i.e a table that includes all the simple attributes). Make sure to identify the primary key for the relation (i.e the PK of the entity). Note: if there is a specialization/generalization relationship in your EER (to be discussed in Topic 3- part 2, you need to transform the ‘superclass’ entity only within this Step 1). Taking the ER-Diagram on the previous slide as an example: EMPLOYEE (empNo, name, address) DEPARTMENT(deptNo, deptName) PROJECT(projNo, projTitle) SUPPLIER(supNo, supName) PART(partNo, partName) Topic 3 : Relational Database Design

  5. empNo name dependentName address dateOfBirth degree has 1 (0, m) M (1, 1) Dependent Employee Relational Database Design Transformation of E-R Model into Relational Logical Model ( Top Down design ) STEP 2:For each weak entity in the ER model, create a relation which includes all the simple attributes. The primary key of the relation is the combination of the primary key/s of the ‘owner’ and the the key of the weak entity itself. DEPENDENT( empNo, dependentName, dateOfBirth) Topic 3 : Relational Database Design

  6. empNo deptNo name deptName manages address 1 (1, 1) 1 (0, 1) Department Employee degree Relational Database Design Transformation of E-R Model into Relational Logical Model ( Top Down design ) STEP 3:For each binary 1 TO 1 Relationship identify the two relations that correspond to the entities participating in the relationship. Choose one of the Relation ( usually the one with total participation) and include as foreign key the primary key of the the other relation. DEPARTMENT( deptNo, deptName, mngrEmpNo) Topic 3 : Relational Database Design

  7. empNo deptNo name deptName works for address M (1, 1) 1 (1, m) Employee Department degree Relational Database Design Transformation of E-R Model into Relational Logical Model ( Top Down design ) STEP4:For each binary 1 TO N Relationship identify the relations that represent the participating entity at the N (i.e many) side of the relationship. Include as foreign key in the relation that holds the N side, the primary key of the other entity (that holds the 1 side) EMPLOYEE (empNo, name, address, deptNo) Topic 3 : Relational Database Design

  8. empNo projNo name projName works on M (1, m) address M (1, m) Employee Project degree Relational Database Design Transformation of E-R Model into Relational Logical Model ( Top Down design ) STEP 5:For each binary M:N Relationship create a new relation to represent the relationship. The primary key of the new relation is the combination of the primary keys of the two connected entities. WORKS ON (empNo, projNo) Topic 3 : Relational Database Design

  9. empNo name address Employee degree Relational Database Design Transformation of E-R Model into Relational Logical Model ( Top Down design ) STEP 6:For each multivalued attribute, create a new relation that includes the multivalued attribute and the primary key of the entity where the multivalued attribute is attached. EMPLOYEE (empNo, name, address) EDEGREE(empNo, degree) Topic 3 : Relational Database Design

  10. projNo projName Project supNo partNo M (0, m) supName partName supplies M (1, m) M (1, m) Part Supplier quantity date Relational Database Design Transformation of E-R Model into Relational Logical Model ( Top Down design ) STEP 7:For each n-ary ( > 2 ) Relationship create a new relation to represent the relationship. The primary key of the new relation is the combination of the primary keys of the participating entities that hold the N (many) side. In most cases of an n-ary relationship all the participating entities hold a many side. SUPPLIES (supNo , projNo, partNo , date, quantity) Topic 3: Relational Database Design

  11. Relational Database Design Final Tables PROJECT(projNo, projTitle) SUPPLIER(supNo, supName) PART(partNo, partName) DEPENDENT( empNo, dependentName, dateOfBirth) DEPARTMENT( deptNo, deptName, mngrEmpNo) EMPLOYEE (empNo, name, address, deptNo) WORKS ON (empNo, projNo) EDEGREE(empNo, degree) SUPPLIES (supNo , projNo, partNo , date, quantity) Topic 3 : Relational Database Design

  12. Owner# OwnerName OwnerAddress Maintenance# Date Type Cost ACReceipt# DateofPayment Tenant# TypeOfAccount ContactAddress AmountPaid Phone# FamilyName FirstName Relational Database Design OWNER Perform the complete transformation steps for the E-R Model described in Topic4. 1 (1, N) own Building# Address N (1, 1) Value incur (1, 1) (0, N) MAINTENANCE PROPERTY 1 N LeasePeriod (0, N) 1 N (0,N) Bond for rent (1, 1) ACCOUNT N (1, 1) N M (1, N) pay TENANT (1, N) 1 1 Topic 3 : Relational Database Design

  13. Relational Database Design Perform the complete transformation steps for the E-R Model of the ‘Real Estate Agency’ described in Topic4. SOLUTION: (to be discussed in the lecture) Topic 3 : Relational Database Design

  14. Next Lecture … Topic 3 (part 2) – EER Modeling and transformation

More Related