Chapter 8

1. Chapter 8 Mapping a Conceptual Design into a Logical Design

2. Chapter 8 Outline • Conceptual Database Design • Relational Database Design Using ER-to-Relational Mapping • Mapping EER Model Constructs to Relations

3. What is a design methodology? • A structured approach that uses procedures, techniques, tools, and documentation aids to support and facilitate the process of design.

4. Phases of database design • Three main phases: • conceptual database design, • logical database design, • physical database design. • Conceptual database design • The process of constructing a model of the data used in an organization independent of all physical considerations.

5. Phases of database design • Logical database design • Process of constructing a model of data used in an organization based on a specific data model, but independent of a particular DBMS and other physical considerations.

6. Phases of database design • Physical database design • Process of producing a description of the implementation of the database on secondary storage; it describes the base tables, file organizations, and indexes used to achieve efficient access to the data, and any associated integrity constraints and security restrictions.

7. Critical success factors in database design • Work interactively with the users as much as possible. • Follow a structured methodology throughout the data modeling process. • Employ a data-driven approach. • Incorporate structural and integrity considerations into the data models. • Use normalization and transaction validation techniques in the methodology.

8. Critical success factors in database design • Use diagrams to represent as much of the data models as possible. • Use a database design language (DBDL). • Critical success factors in database design • Build a data dictionary to supplement the data model diagrams. • Be willing to repeat steps.

9. Overview of the database design methodology

10. Step 1: Conceptual database design (Create ER model) • Step 1.1 Identify entities • Step 1.2 Identify relationships • Step 1.3 Identify and associate attributes with entities or relationships • Step 1.4 Determine attribute domains • Step 1.5 Determine candidate, primary, and alternate key attributes

11. Step 1: Conceptual database design methodology (Create ER model) • Step 1.6 Specialize/Generalize entities (optional step) • Step 1.7 Check model for redundancy • Step 1.8 Check model supports user transactions • Step 1.9 Review conceptual database design with users

12. Extract from the data dictionary

13. Main relationships for StayHome Online Rentals

14. First draft ER diagram

15. Multiplicity constraints for the relationships

16. Adding multiplicity constraints to the ER diagram

17. Extract from the data dictionary

18. ER diagram for StayHome Online Rentals showing primary keys

19. Check ER model supports data requirements

20. ER diagram showing all entities, relationships, and attributes

21. Relational DatabaseDesign by ER- and EER-to-Relational Mapping • Design a relational database schema • Based on a conceptual schema design • Seven-step algorithm to convert the basic ER model constructs into relations • Additional steps for EER model

22. Relational Database Design Using ER-to-Relational Mapping

24. ER-to-Relational Mapping Algorithm • COMPANY database example • Assume that the mapping will create tables with simple single-valued attributes • Step 1: Mapping of Regular Entity Types • For each regular entity type, create a relation R that includes all the simple attributes of E • Called entity relations • Each tuple represents an entity instance

25. ER-to-Relational Mapping Algorithm (cont’d.) • Step 2: Mapping of Weak Entity Types • For each weak entity type, create a relation R and include all simple attributes of the entity type as attributes of R • Include primary key attribute of owner as foreign key attributes of R

26. ER-to-Relational Mapping Algorithm (cont’d.)

27. ER-to-Relational Mapping Algorithm (cont’d.) • Step 3: Mapping of Binary 1:1 Relationship Types • For each binary 1:1 relationship type R • Identify relations S and T that correspond to entity types participating in R • 3 Possible approaches: • Foreign key approach :Choose the entity type with total participation –S, say-and include in S the primary key of T, and include all simple attributes of R • Merged relation approach: Merge the 2 entities S and T into a single relation(do it when both participation are total) • Cross-reference approach: Create a 3rd relation Rfor cross-referencing the primary keys of S and T (similar to step 5)

28. 1:1 relationship mandatory both entities (a) ER diagram (b) table

29. 1:1 relationship mandatory one entity only (a) ER diagram (b) tables

30. 1:1 relationship mandatory one entity only (a) ER diagram (b) tables

31. 1:1 relationship optional both entities (a) ER diagram (b) tables

32. ER-to-Relational Mapping Algorithm (cont’d.) • Step 4: Mapping of Binary 1:N Relationship Types • For each regular binary 1:N relationship type • Identify relation that represents participating entity type at N-side of relationship type • Include primary key of other entity type as foreign key in S • Include simple attributes of 1:N relationship type as attributes of S

33. ER-to-Relational Mapping Algorithm (cont’d.) • Step 5: Mapping of Binary M:N Relationship Types • For each binary M:N relationship type • Create a new relation S • Include primary key of participating entity types as foreign key attributes in S • Include any simple attributes of M:N relationship type

34. *:* relationship (a) ER diagram (b) tables

35. ER-to-Relational Mapping Algorithm (cont’d.) • Step 6: Mapping of Multivalued Attributes • For each multivaluedattribute A • Create a new relation R • R includes an attribute corresponding to A, plus the primary Key attribute K (from the entity that has A amultivalue) as a foreign key in R • Primary key of R is the combination of A and K

36. Multi-valued attribute (a) ER diagram (b) tables

37. ER-to-Relational Mapping Algorithm (cont’d.) • Step 7: Mapping of N-ary Relationship Types • For each n-ary relationship type R • Create a new relation S to represent R • Include primary keys of participating entity types as foreign keys • Include any simple attributes as attributes

38. Complex (ternary) relationship(a) ER diagram (b) tables

39. Discussion and Summary of Mapping for ER Model Constructs

40. Mapping EER Model Constructs to Relations • Extending ER-to-relational mapping algorithm

41. Mapping of Specialization or Generalization • Step 8: Options for Mapping Specialization or Generalization (see pages 294-295) • Option 8A: Multiple relations—superclass and subclasses • For any specialization (total or partial, disjoint or overlapping) • Option 8B: Multiple relations—subclass relations only • Subclasses are total • Specialization has disjointedness constraint

42. Mapping of Specialization or Generalization (cont’d.) • Option 8C: Single relation with one type attribute • Type or discriminating attribute indicates subclass of tuple • Subclasses are disjoint • Potential for generating many NULL values if many specific attributes exist in the subclasses • Option 8D: Single relation with multiple type attributes • Subclasses are overlapping • Will also work for a disjoint specialization

44. Mapping of Shared Subclasses (Multiple Inheritance) • Apply any of the options discussed in step 8 to a shared subclass

45. Summary • Map conceptual schema design in the ER model to a relational database schema • Algorithm for ER-to-relational mapping • Illustrated by examples from the COMPANY database • Include additional steps in the algorithm for mapping constructs from EER model into relational model