Database Design Part 1 Chapter 9 1 Outline

Database Design Part 1 Chapter 9 1

Outline v. Requirements Gathering …to DB Design v. Relational Database Model The Data: From Conceptual Data Model to a Database Designing a Relational Database • Representing Relationships • Foreign Keys • Super & Sub-Classes • Maintaining Data Integrity • Referential Integrity • Normalization – in Part 2 RDBMS 2

Requirements Gathering Functionality – the user must be able to: − Enter orders − Track order status − Enter/assign/resolve complaints Data – the system must be able to capture: − Order # − Order Date − Customer Info − Billing address − Shipping address − Products, & quantity − Price, discounts 3

From Requirements Gathering to DB Design Ensure consistency with existing systems Front-end activities Back-end activities Data Administrator Database Administrator (DBA) l l l Data standards - Naming, etc. Data use - Ownership, accessibility Data quality - Integrity rules l l l Designing the database Capacity planning Performance monitoring & tuning Security Backup & Recovery Database 4

Relational Database Model …and RDBMS 5

Relational Database Model v Developed by E. F. Codd in 1970 A model where data is represented as a set of related tables or relations. Emp. ID Last. Name First. Name Birth. Date Salary Dept. CD 123456 Doe John 01/01/1995 $90, 000. 00 MIS 987654 Smith Sue 12/31/1980 $140, 000. 00 FIN 871245 Lyons Larry 06/02/1970 $120, 000. 00 MIS 389210 Jones Jen 08/19/2000 $45, 000. 00 MTH − Rules of Relations: • Each row is unique - Primary Key • Entries in cells are simple - Rule of Atomic Values • Entries in columns are from the same set of values – i. e. Domain • Order of columns may be interchanged • Order of rows may be interchanged 6

Relational Database Management System v. RDBMS It is ______ that allows us to create Relational Databases Examples include: • _____________ • _______ v. Relational Database Organizes data into Tables (relations) Database Customer Order. Line • Record – row, tuple • Field – column, attribute 7

The Data: From Conceptual Data Model to a Database Conceptual Data Model ERD Database Domain Class Diagram Entity Class Table Attribute Field Instance Object Record Primary Key Identifier Primary Key Relationship Association Foreign Key Cardinality Multiplicity ** ** Relationship rules –will learn in the DB class DB 8

Designing a Relational DB Topic Tables Primary Keys Associatives Multi-Valued Attributes Description Create TBs – 1 for each entity/domain class Select/define a primary key for each table Create new tables to represent many-to-many relationships Resolve a multi-value attribute to a new table Representing Relationships Foreign Keys Add foreign keys to establish relationships between tables Super & Sub Classes Decide on how to represent any Generalization Hierarchies Data Integrity Referential Integrity Define Referential Integrity constraints Normalization – Part 2 Evaluate schema quality and make necessary improvements 9

Representing Relationships 10

Foreign Keys – rule for a 1: M v. One to Many Relationship create foreign key in the relation that is on the “many” side of relationship only − NOTE: Going the other way would generate a multi-value attribute Copy it over PK FK Add Dept. Cd here Department dept. Cd {key} dept. Name location Employee 1 employs * Emp. ID {key} last. Name first. Name title 11

Department Dept. CD Employee Name Building Emp. ID Last. Name First. Name Title ACC Accounting FOS 111 Doe John Dir. Dev. FIN Finance FOS 222 Smith Sue Mgr DBA MTH Math SRICH 333 Lyons Larry Acctg Clerk MIS Mgmt Info Systems FOS 444 Jones Jen Sys Analyst III 12

Foreign Keys – rule for a 1: 1 v. One to One Relationship create foreign key in either relation − NOTE: IF you have one & only one on both sides, these may be merged into a single table Copy it over PK FK Add SSN here Employee Emp. ID {key} last. Name first. Name title Parking. Space 1 is assigned FK 1 ps. No {key} level Accessible? PK Add ps. No here Copy it over 13

Employee Emp. ID Parking. Space Last. Name First. Name 111 Doe John 222 Smith 333 444 Title ps. No Level Acc? Dir. Dev. 1 1 y Sue Mgr DBA 2 1 n Lyons Larry Acctg Clerk … Jones Jen Sys Analyst III 4 n 400 14

Foreign Keys – rule for a M: M v. Many to Many Relationship should’ve been eliminated on ERD!!! create new relation with PKs of related entities as: Employee Project 1. . * 0. . * Emp. Project − 1 concatenated PK, and 2 FKs Copy it over PK Copy it over FK Employee Emp. ID {key} last. Name first. Name title 1. . 1 0. . * FK Emp. Project __________ (PK) role st. Date PK Project 1. . * 1. . 1 proj. Cd {key} proj. Name desc st. Date end. Date 15

Employee Emp. ID Project Last. Name First. Name Title Proj. CD Name Start. Date 111 Doe John Dir. Dev. OE Order entry 01/01/2019 222 Smith Sue Mgr DBA IN Inv Update 10/19/2019 333 Lyons Larry Acctg Clerk MA Mobile App 02/14/2020 444 Jones Jen Sys Analyst III HR HR Rel 10 05/01/2020 Emp. Project Option A: Emp. ID Proj. CD 111 OE 111 IN 222 MA 444 OE Emp. Project Option B: Emp. ID Proj. CD Role Start. Date 111 OE Proj Mgr 1/1/2019 111 IN Sys Analyst 10/30/2019 222 MA DBA 5/15/2020 444 OE Sys Analyst 1/10/2019 16

Generalization Hierarchy: Superclasses & Subclasses Student Undergrad Student mentor SID {key} name birth. Date Grad Student thesis. Topic 17

Generalization Hierarchy: Superclasses & Subclasses v. Option A: implement as Separate Tables Student Undergrad Student mentor SID {key} name birth. Date Grad Student thesis. Topic Name Birth. Date 111 Doe 01/01/1995 222 Smith 12/31/1998 333 Lyons 06/02/2000 444 Jones 08/19/2002 Undergrad-Student Grad-Student SID Mentor SID Thesis Topic 222 Doe 111 UX Design 333 Sys Perf 444 18

Generalization Hierarchy: Superclasses & Subclasses v. Option B: implement as One Table Student Undergrad Student mentor SID {key} name birth. Date Grad Student SID Name Birth. Date 111 Doe 01/01/1995 222 Smith 12/31/1998 333 Lyons 06/02/2000 444 Jones 08/19/2002 Mentor Thesis Topic UX Design Doe Sys Perf thesis. Topic But what are we missing? 19

Super & Sub Classes v. In Summary… Separate Tables − Student (SID, name, birth. Date) − Undergrad. Student (SID, mentor) − Grad. Student (SID, thesis. Topic) Student Undergrad Student mentor SID {key} name birth. Date Grad Student thesis. Topic One Table − Student (SID, name, birth. Date, mentor, thesis. Topic, student. Type) 20

Maintaining Data Integrity 21

Sample Data Model 22

Referential Integrity v. Referential Integrity (def) an integrity constraint on the Foreign Key: 1. Prevents adding a FK value (i. e. from the ____ TB), if an associated PK value does not exist. 2. Prevents deleting a PK value (i. e. from the ____ TB), if an associated FK value exists. 3. Prevents changing a PK value (i. e. from the ____ TB), if an associated FK value exists. Can override: − Cascade Delete Overrides constraint #2 − Cascade Update Overrides constraint #3 23