Concepts of Database Management, Fifth Edition

1. Concepts of Database Management, Fifth Edition Chapter 6: Database Design 2: Design Methodology

2. Objectives • Discuss the general process and goals of database design • Define user views and explain their function • Define database design language and use it to document database designs • Create an entity-relationship diagram to visually represent a database design • Present a methodology for database design at the information level and view examples illustrating this methodology Concepts of Database Management, 5th Edition

3. Objectives • Explain the physical-level design process • Discuss top-down and bottom-up approaches to database design and examine the advantages and disadvantages of both methods • Use a survey form to obtain information from users prior to beginning the database design process • Review existing documents to obtain information prior to beginning the database design Concepts of Database Management, 5th Edition

4. Objectives • Discuss special issues related to implementing one-to-one relationships and many-to-many relationships involving more than two entities • Discuss entity subtypes and their relationships to nulls • Learn how to avoid potential problems when merging third normal form relations • Examine the entity-relationship model for representing and designing databases Concepts of Database Management, 5th Edition

5. Database Design • Information-level Design – building a database that satisfies the organization’s requirements as cleanly as possible • Physical-level design – designers transform the information-level design into a design for the specific DBMS used by the organization • User Views – requirements necessary to support a particular user’s operations • Cumulative design – supports all user views encountered so far in the design process Concepts of Database Management, 5th Edition

6. Information-Level Design Methodology • Information-level Design Methodology • Represent user view as collection of tables • Normalize these tables • Identify all keys • Merge the result into design Concepts of Database Management, 5th Edition

7. Represent User View as Collection of Tables Step 1: Determine entities involved and create separate table for each type Step 2: Determine primary key for each table Step 3: Determine properties for each entities Step 4: Determine relationships among entities Concepts of Database Management, 5th Edition

8. Types of Relationships • One-to-Many Relationships – created by including the primary key of the “one” table as a foreign key in the “many” table • Many-to-Many Relationships – created with a new table whose primary key is the combination of the primary keys of the original tables • One-to-One Relationship – created with a single relationship from one record in a table to a single record in another table Concepts of Database Management, 5th Edition

9. Normalize the Tables • Represent all keys • Primary, alternate, secondary, foreign • Database Design Language (DBDL) • Mechanism for representing tables and keys DBDL Example Figure 6.1 Concepts of Database Management, 5th Edition

10. Types of Primary Keys • Three types of primary keys used in database design • Natural key (logical key or intelligent key) – consists of a column that uniquely identifies an entity • Artificial key – column created for an entity to serve solely as the primary key; visible to users • Surrogate key (synthetic key) – system-generated primary key usually hidden from users Concepts of Database Management, 5th Edition

11. DBDL Notation • Table name followed by columns in parentheses • Primary key column(s) underlined • AK identifies alternate keys • SK identifies secondary keys • FK identifies foreign keys Concepts of Database Management, 5th Edition

12. Entity-Relationship Diagrams • Diagram that visually represents database structure • Rectangle represents each entity in the E-R diagram • Primary key for each entity appears above the line in the rectangle for each entry Concepts of Database Management, 5th Edition

13. Entity-Relationship Diagrams • Other columns that comprise each entity appear below the line within each rectangle • The letters AK, SK, and FK appear in parentheses following the alternate key, secondary key, and foreign key respectively • For each foreign key, there is a line leading from the rectangle that corresponds to the table being identified to the rectangle that corresponds to the table containing the foreign key Concepts of Database Management, 5th Edition

14. Figure 6.2: Entity-Relationship Diagrams Concepts of Database Management, 5th Edition

15. Merge the Result into the Design • As soon as Steps 1 through 3 for a given user view have been completed, the results can be merged into the cumulative design • If the working view is the first user view, the cumulative design will be identical to the design for the first user • Otherwise, all the tables for this user with those that are currently in the cumulative design Concepts of Database Management, 5th Edition

16. Merge the Result into the Design (con’t) • Next, you combine tables that have the same primary key to form a new table • New table has the same primary key as those tables you have combined • New table also contains all the columns from both tables • In the case of duplicate columns, you remove all but one copy of the column Concepts of Database Management, 5th Edition

17. Figure 6.4:User View Examples View #1: Sales Rep View Rep (RepNum, LastName, FirstName, Street, City, State, Zip, Commission, Rate) Concepts of Database Management, 5th Edition

18. Figure 6.5:User View Examples View #2: Customer View Concepts of Database Management, 5th Edition

19. Entities • Independent entity - an entity that does not require a relationship to another entity for identification • Dependent entity - an entity that does require a relationship to another entity for identification Concepts of Database Management, 5th Edition

20. Physical-Level Design • Undertaken after information-level design completion • Most DBMSs support primary, candidate, secondary, and foreign keys • DB programmers must include logic to ensure the uniqueness of primary keys and enforce other conditions Concepts of Database Management, 5th Edition

21. Top-Down vs. Bottom-Up • Bottom-up • Design starts at low level • Specific user requirements drive design process • Top-down • Begins with general database that models overall enterprise • Refines the model until design is achieved Concepts of Database Management, 5th Edition

22. Survey Form • Used to collect information from users • Must contain particular elements • Entity information • Attribute information • Relationships • Functional dependencies • Processing information Concepts of Database Management, 5th Edition

23. Existing Documents • Aid in collecting user requirements • Collect information similar to that collected with survey forms • Entity information • Attribute information • Relationships • Functional dependencies • Processing information Concepts of Database Management, 5th Edition

24. Figure 6.14: Example Invoice Concepts of Database Management, 5th Edition

25. Figure 6.15: List of Possible Attributes Concepts of Database Management, 5th Edition

26. Figure 6.16:Tentative List of Functional Dependencies Concepts of Database Management, 5th Edition

27. Figure 6.17:Revised List of Functional Dependencies Concepts of Database Management, 5th Edition

28. Figure 6.18:Tentative List of Entities Concepts of Database Management, 5th Edition

29. Figure 6.19:Expanded List of Entities Concepts of Database Management, 5th Edition

30. One-to-One Relationship Considerations • Implementing a one-to-one relationship by simply including the primary key of each table as a foreign key in the other table • A problem is that there is no guarantee that the information will match Concepts of Database Management, 5th Edition

31. One-to-One Relationship Considerations (con’t) • One possible solution is to create a single table • Although workable, two features are not particularly attractive: • Combines columns of two different entities into a single table • If possible for one entity to exist without the other Concepts of Database Management, 5th Edition

32. One-to-One Relationship Considerations (con’t) • Better solution • Create two separate tables for customers and sales reps and to include the primary key of one of them as a foreign key in the other • This foreign key would also be designated as an alternate key Concepts of Database Management, 5th Edition

33. Figure 6.20:1:1 Relationship Considerations Include primary key of each table as foreign key in the other Concepts of Database Management, 5th Edition

34. Figure 6.21:1:1 Relationship Considerations Implementation when information does not match Concepts of Database Management, 5th Edition

35. Figure 6.22:1:1 Relationship Considerations Implemented in a single table Concepts of Database Management, 5th Edition

36. Figure 6.23: 1:1 Relationship Considerations 1:1 relationship implemented by including primary key of one table as foreign key (and alternative key) in the other Concepts of Database Management, 5th Edition

37. Many-to-Many Relationship Considerations • Complex issues arise when more than two entities are related in a many-to-many relationship • Many-to-many-to-many relationship – involves multiple entities • Crucial issue in making the determination between a single many-to-many-to-many relationship and two (or three) many-to-many relationships is the independence Concepts of Database Management, 5th Edition

38. Figure 6.24: M:M Relationship Considerations Sample Sales Data Concepts of Database Management, 5th Edition

39. Figure 6.25:M:M Relationship Considerations Result obtained by splitting Sales table into three tables Concepts of Database Management, 5th Edition

40. Figure 6.26:M:M Relationship Considerations Result obtained by joining three tables--2 rows are in error. Must be converted to 4NF Concepts of Database Management, 5th Edition

41. Nulls • Null - a special value • Null - actually represents the absence of a value in a field • Nulls - used when a value is either unknown or inapplicable Concepts of Database Management, 5th Edition

42. Figure 6.27: Table Split To Avoid Nulls Nulls are absence of values Concepts of Database Management, 5th Edition

43. Figure 6.28: Sample DBDL Concepts of Database Management, 5th Edition

44. Entity Subtypes • Entity subtype – table that is a subtype of another table • Incomplete category – records that do not fall into the subtype • Complete categories – all records fall into the categories Concepts of Database Management, 5th Edition

45. Figure 6.29: Entity Subtypes Concepts of Database Management, 5th Edition

46. Figure 6.32:Two Entity Subtypes—Incomplete Categories Concepts of Database Management, 5th Edition

47. Figure 6.33: Two Entity Subtypes—Complete Categories Concepts of Database Management, 5th Edition

48. Avoiding Problems with 3NF When Merging Tables • When combining third normal form tables, the result need not be in third normal form • To avoid the problem of creating a table that is not in third normal form, be cautious when representing user views • If you always attempt to determine whether determinants exist and include them in the tables, the problem is often avoided Concepts of Database Management, 5th Edition

49. Entity-Relationship Model • An approach to representing data in a database • Entities are drawn as rectangles • Relationships are drawn as diamonds with lines connecting the entities involved in relationships Concepts of Database Management, 5th Edition

50. Figure 6.34:One-to-many relationship Concepts of Database Management, 5th Edition