Concepts of Database Management, Fifth Edition

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

2. 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

3. 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

4. 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

5. 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

6. 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

7. 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

8. 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

9. 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

10. 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

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

12. 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

13. 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

14. 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

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

16. 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