04- Relational Database Design.pdf

Relational Database Design o CSC 375: Database Management Systems o Dr. Wassim Katerji o Fall 2024  Objectives of this Lesson ✓ How to design a relational database schema based on a conceptual schema design. ✓ Learn the logical database design step of database design, which is...

Relational Database Design o CSC 375: Database Management Systems o Dr. Wassim Katerji o Fall 2024  Objectives of this Lesson ✓ How to design a relational database schema based on a conceptual schema design. ✓ Learn the logical database design step of database design, which is also known as data model mapping. ✓ Present the procedures to create a relational schema from an entity– relationship (ER) schema. Sample ER Conceptual Schema Diagram Result of Mapping the ER Schema into a Relational database  Steps of ER-to-Relational Mapping 1) Mapping of Regular Entity Types 2) Mapping of Weak Entity Types 3) Mapping of Binary 1:1 Relationship Types 4) Mapping of Binary 1:N Relationship Types 5) Mapping of Binary M:N Relationship Types 6) Mapping of Multivalued Attributes 7) Mapping of N-ary Relationship Types  Step 1: Mapping of Regular Entity Types ▪ For each regular (strong) entity type E in the ER schema, create a relation R that includes all the simple attributes of E. ▪ Include only the simple component attributes of a composite attribute. ▪ Choose one of the key attributes of E as the primary key for R. If the chosen key of E is a composite, then the set of simple attributes that form it will together form the primary key of R. ▪ If multiple keys were identified for E during the conceptual design, the information describing the attributes that form each additional key is kept to specify additional (unique) keys of relation R. ▪ Knowledge about keys is also kept for indexing purposes and other types of analyses. Step 1: Mapping of Regular Entity Types  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); this takes care of mapping the identifying relationship type of W. ▪ 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. ▪ If there is a weak entity type E2 whose owner is also a weak entity type E1, then E1 should be mapped before E2 to determine its primary key first. Step 2: Mapping of Weak Entity Types Step 3: Mapping of Binary 1:1 Relationship Types ▪ 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. ▪ There are three possible approaches: o The foreign key approach o The merged relationship approach ▪ The first approach is the most useful and should be followed unless special conditions exist  Step 3: Foreign Key Approach o Choose one of the relations—S, say—and include as a foreign key in S the primary key of T. o It is better to choose an entity type with total participation in R in the role of S. Include all the simple attributes (or simple components of composite attributes) of the 1:1 relationship type R as attributes of S. o Another possibility is to have foreign keys in both relations S and T redundantly, but this creates redundancy and incurs a penalty for consistency maintenance. o Merged relation approach: An alternative mapping of a 1:1 relationship type is to merge the two entity types and the relationship into a single relation. This is possible when both participations are total, as this would indicate that the two tables will always have the exact same number of tuples. Step 4: Mapping of Binary 1:N Relationship Types ▪ The foreign key approach: For each regular 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 relation T that represents the other entity type participating in R; we do this because each entity instance on the N-side is related to at most one entity instance on the 1-side of the relationship type. Include any simple attributes (or simple components of composite attributes) of the 1:N relationship type as attributes of S. Step 5: Mapping of Binary M:N Relationship Types ▪ 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 (or simple components of composite attributes) as attributes of S. Notice that we cannot represent an M:N relationship type by a single foreign key attribute in one of the participating relations (as we did for 1:1 or 1:N relationship types) because of the M:N cardinality ratio; we must create a separate relationship relation S. Step 5: Mapping of Binary M:N Relationship Types  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 a multivalued attribute. ▪ The primary key of R is the combination of A and K. If the multivalued attribute is composite, we include its simple components. Step 6: Mapping of Multivalued Attributes  Step 7: Mapping of N-ary Relationship Types ▪ For each n-ary relationship type R, where n > 2, create a new relationship relation 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. ▪ The primary key of S is usually a combination of all the foreign keys that reference the relations representing the participating entity types. However, if the cardinality constraints on any of the entity types E participating in R is 1, then the primary key of S should not include the foreign key attribute that references the relation E′ corresponding to E. Step 7: Mapping of N-ary Relationship Types Any Question?