Database Design 4: Deriving Tables From Data Models

Database Design 4: Deriving Tables From Data Models CS 320

Review: Relationships in Tables Created by shared key fields

Types of Database Key Fields Primary keys Foreign keys Composite keys

Review: Primary Key • Uniquely identify a record • Must be unique with respect to all other records in the table • You can create surrogate keys to serve as primary keys

Foreign Key Field that is a primary key in another table Used to create a relationship

Composite Key Primary key comprised of 2 or more fields

Review: Levels of data models • Conceptual: describes WHAT data the system contains • Logical: describes HOW the database will be structured, regardless of the DBMS • Physical: describes HOW the system will be implemented using a specific DBMS

Goal of Logical DB Design: Database Integrity • Entity integrity • Every record must have a unique primary key • The primary key value cannot be NULL • Referential integrity • Every foreign key must be the primary key of its parent table • Every foreign key value must exist in its parent table

Examples of Integrity Problems ?

Steps for Transforming an ER Model into a Sound Logical Database Design • Create a table to represent every entity • Entity PK is table PK • Represent each ER model relationship using foreign keys

Representing 1:M Relationships • Put the PK of the “1” side into the “M” table ACME_EMPLOYEE(Emp_ID, Emp_Last_Name, Emp_Office, Dept_ID) ACME_DEPARTMENT(Dept_ID, Dept_Name, Dept_Office_Loc)

Why can’t you put the PK of the “M” side into the “1” table? • A department has multiple employees… Approach 1: delimit multiple values using commas? Result: Non-relational tables (cell contains multiple values) • Approach 2: Repeat "1" side records for each "M" record? • Result: Redundant data!

Representing M:M Relationships • Create a linking table • Also called a bridge table • PK is a composite key comprised of PK’s of both entities in the M:M relationship • These fields are part of the PK, and are also FK’s

Representing M:M Relationships

What do you name the linking table? ACME_EMPLOYEE_PROJECT Typically, a compound word comprised of the two participating entities

What about composite M:M relationships? • If the relationship has an attribute, place the attribute in the linking table

Deriving Tables for 1:1 Relationships • In general, move one entity's non-key attributes into the other entity's table ACME_EMPLOYEE Emp_ID Emp_Last_Name Emp_Office Emp_Spouse_First Emp_Spouse_Last

Deriving Tables for 1:1 Relationships • How do you decide which entity to keep? • The "strong" entity • Typically in a 1:1 relationship, one entity's instance doesn't exist without the other's

Unary Relationships ACME_EMPLOYEE Employee_ID Employee_First_Name Employee_Last_Name Employee_DOB SupervisorID (FK) UWEC_STUDENT_ROOMS_WITH Student1_ID (FK) Student2_ID (FK) Treat just like any other 1:1, 1:M, or M:M relationship, except modify key names to avoid duplicate field names

Ternary Relationships UWEC_STUDENT_ ADVISOR_PROGRAM Student_ID (FK) Advisor_ID (FK) Program_ID (FK) Create a 3-way linking table

Generalization/Specialization Create tables for the supertype and each of the subtypes Put PK of supertype into each subtype table as both a PK and FK

Generalization/Specialization UWEC_Person Person_ID Person_First_Name Person_Last_Name Person_MI Person_DOB Supertype Subtypes UWEC_STUDENT Person_ID (FK) Student_Major_Program Student_Minor_Program UWEC_ INSTRUCTOR Person_ID (FK) Instructor_Title Instructor_Office Instructor_Salary UWEC_Staff Person_ID (FK) Staff_Rank Staff_Hourly_Rate

Generalization/Specialization Data Table Example UWEC_STUDENT UWEC_PERSON UWEC_INSTRUCTOR UWEC_STAFF

Test Yourself: In the CANDY_PURCHASE table, PURCH_ID is an example of a: CANDY_PURCHASE A primary key A foreign key Part of a composite key Both b and c None of the above

Test Yourself: In the CANDY_PURCHASE table, PURCH_ID is an example of: CANDY_PURCHASE A primary key A foreign key Part of a composite key Both b and c None of the above

Test Yourself: In the CANDY_PURCHASE table, PROD_ID is an example of a: CANDY_PURCHASE Primary key Foreign key Part of a composite key Both b and c None of the above

Test Yourself: How many database tables would you create for the following ER model? 3 4 5 None of the above

Your Turn #1 • Derive the tables for the following ER model. Format the table structures using the format TABLE_NAME(Field1, Field2, …). Underline primary key fields, and label foreign keys using the designation (FK).

Your Turn #1 Solution ALS_CUSTOMER(Customer_ID, Customer_Last_Name, Customer_First_Name, Customer_MI, Customer_Address, Customer _City, Customer_State, Customer_Zip, Customer_Phone) ALS_CAR(Car_VIN, Car_Make, Car_Model, Car_Year, Customer_ID (FK)) ALS_WORK_ORDER (WO_ID, WO_Date, Car_VIN (FK)) ALS_SERVICE (Service_ID, Service_Name, Service_Desc, Service_Charge) ALS_WORK_ORDER_SERVICE (Service_ID (FK), WO_ID (FK))

Your Turn #2 • Derive the tables for the following ER model. Format the table structures using the format TABLE_NAME(Field1, Field2, …). Label all primary keys by underlining them, and label foreign keys using the designation (FK).

Your Turn #2 Solution • UWEC_PERSON(Person_ID, Person_First_Name, Person_Last_Name, Person_MI, Person_Email) • UWEC_STUDENT(Person_ID (FK), Stu_Campus_Address, Stu_Campus_City, Stu_Campus_State, Stu_Campus_Zip, Stu_Campus_Phone, Stu_Perm_Address, Stu_Perm_City, Stu_Perm_State, Stu_Perm_Zip, Stu_Perm_Phone) • UWEC_INSTRUCTOR(Person_ID (FK), Instructor_Title, Instructor_Office, Instructor_Office_Phone) • UWEC_COURSE (Course_ID , Course_Dept, Course_Name, Course_Desc) • UWEC_COURSE_SECTION (Section_ID, Section_Term, Section_Day, Section_Time, Section_Location, Person_ID (FK), Course_ID (FK)) • UWEC_STUDENT_COURSE_SECTION(Person_ID (FK), Section_ID (FK), Enrollment_Grade) • UWEC_ADVISOR (Student_Person_ID (FK), Instructor_Person_ID (FK))

Database Design 4: Deriving Tables From Data Models