Relational Database Design ISYS 464 Introduction to Relational
Relational Database Design ISYS 464
Introduction to Relational Model • Data is logically structured within relations. • Each relation is a table (file) with named columns (attributes, fields) and rows (records). – Fixed format, structured data
Properties of a Relation • Simple attribute – No composite, no multivalued attribute • Each relation must have a primary key: – Simple or composite key – May have other keys (candidate keys) – Key cannot be null – Cannot be duplicated
Integrity Constraints • Entity integrity: – Primary key cannot be null, cannot be duplicated • Referential integrity • Domain constraints • Other constraints
Relational Database Design • Entity type: Create a table that includes all simple attributes – Each component of a composite attribute becomes a simple attribute • Multi-valued attribute: Create a table for each multivalued attribute – Key + attribute • Relationship: – 1: 1, 1: M • Foreign key • Relationship table: for partial participation to avoid null values – M: M: relationship table – N-ary relationship: relationship table – Recursive relationship • Attribute of relationship • Supertype and subtype
Design the relational databased on this ERD Grade
Tables • Student: Student. ID, Student. Name, Address, Sex, Major, Faculty. ID (as a foreign key) • Account: Student. ID, Balance • Faculty: Faculty. ID, Faculty. Name, Phone • Course: Course. ID, Course. Name, Credits, Faculty. ID (as a foreign key) • Student. Course: Student. ID, Course. ID, Grade – This is called relationship table
Linking Student and Faculty If student S 1’s advisor is F 2, and S 2’s dvisor is F 1, S 3’s advisor is F 1 Student: Student. ID, Student. Name, S 1 Peter S 2 Paul S 3 Mary Faculty: Faculty. ID, F 1 F 2 F 3 Address, Sex, Major, Faculty. ID 123 st M IS F 2 abc st M FIN F 1 1 st ave F IS F 1 Faculty. Name, Chao Smith Boxer Phone 1111 1112 113
Relationship Table Example If student S 1 is taking acct 101, isys 263, S 2 is taking acct 100, isys 263, and student S 3 is taking isys 464, isys 363 Student. Course: Student. ID, S 1 S 2 S 3 Course. ID, Grade acct 101 isys 263 acct 100 isys 263 isys 464 isys 363
Figure 4 -9 Mapping a Composite Attribute a) CUSTOMER entity type with composite attribute b) CUSTOMER relation with address detail Copyright © 2019, 2016, 2013 Pearson Education, Inc. All Rights Reserved
Order Processing ERD
Tables • • • Customer: CID, Fname, Lname, Address Customer. Email: CID, Email Customer. Phone: CID, Phone Order. S: OID, Date, Salesperson, CID Product: PID, Description, Price Order. Product: OID, PID, Qty Note: Don’t name a table Order because it is a SQL keyword.
Multivalued attribute example If customer C 1 has three phones: 1111, 1112, 1113 and C 2 has 2 phones: 2111, 2112, the Customer. Phone table looks like this: CID, C 1 C 1 C 2 Phone 1111 1112 1113 2111 2112
Figure 4 -10 Mapping an Entity with a Multivalued Attribute a) EMPLOYEE entity type with multivalued attribute b) EMPLOYEE and EMPLOYEE SKILL relations Copyright © 2019, 2016, 2013 Pearson Education, Inc. All Rights Reserved
Figure 4 -17 Example of Mapping a Unary 1 colon N Relationship a) EMPLOYEE entity with unary relationship b) EMPLOYEE relation with recursive foreign key Copyright © 2019, 2016, 2013 Pearson Education, Inc. All Rights Reserved
Recursive M: M Relationship Course. Prerequisite. Table: Course. Number, Pre. Req. Number Note: Partial participation
Special consideration for 1: 1 or 1: M relationship with partial participation: Assuming only 10% of students have advisor Student: Student. ID, Student. Name, Address, Sex, Major, Faculty. ID S 1 Peter 123 1 st st M IS F 1 S 3 Pau xyz st M FIN Null S 2 May ABC St F ACCT Null S 7 Nancy …. . Null S 6 Mia …. ACCT F 2 …. Faculty: Faculty. ID, Faculty. Name, Phone F 1 Chao 1111 F 2 Smith 1112 F 3 Boxer 1113
Creat a Student. Faculty table Student: Student. ID, Student. Name, Address, Sex, Major S 1 Peter 123 1 st st M IS S 3 Pau xyz st M FIN S 2 May ABC St F ACCT S 7 Nancy …. . …… S 6 Mia …. ACCT …. Faculty: Faculty. ID, Faculty. Name, Phone F 1 Chao 1111 F 2 Smith 1112 F 3 Boxer 1113 Student. Faculty: Student. ID, Faculty. ID S 1 F 1 S 6 F 2
Mapping Ternary (and n-ary) Relationships • One relation for each entity and one for the associative entity • Relationship table has foreign keys to each entity in the relationship Copyright © 2019, 2016, 2013 Pearson Education, Inc. All Rights Reserved
Ternary Relationship. Table: Employee. ID, Serial. No, Project. ID, Assigned. Date
Figure 3 -2 Employee Supertype with Three Subtypes All employee subtypes will inherit employee number, name, address, and date hired. Each employee subtype will also have its own attributes Copyright © 2019, 2016, 2013 Pearson Education, Inc. All Rights Reserved
Tables • Employee: Emp. Number, Emp. Name, Address, Date. Hired • Hourly. Employee: Emp. Number, Hourely. Rate • Salaried. EMployee: Emp. Number, Salary, Stock. Option • Consultant: Emp. Number, Contract. Number, Billing. Rate
Quality of Database Design • Be able to link related records. • Eliminate data duplication: – Normalized database – Third normal form – No obvious duplication in the database
Example of not normalized Database A Table that Combines Two Entity Types Customer. Cart Table: CID, Cname, Addr, Cart. ID, Date Sample data: C 1, Peter, 123 Lincon Ave, Cart 1, 1/1/2020 C 1, Peter, 123 Lincon Ave, Cart 2, 1/3/2020 C 2, Paul, 25 First St. , Cart 4, 1/2/2020 C 2, Paul, 25 First St. , Cart 5, 1/7/2020 …. Note: Customer data is duplicated for each order.
Each entity in separate table and using foreign key to link: no duplication Customer Table: CID, Cname, C 1, Peter, C 2, Paul, Addr 123 Lincon Ave 25 First St. Cart Table Cart. ID, Date Cart 1, 1/1/2020 Cart 2, 1/3/2020 Cart 4, 1/2/2020 Cart 5, 1/7/2020 CID C 1 C 2
Example of not normalized Database A Table that includes a multi-value attribute Fname CID Lname Customer: CID, Fname, Lname, Email Sample data: C 1, Peter, Smith, ps@yahoo. com C 1, Peter, Smith, ps@gmail. com Email. Addr
Denormalization • Denormalization is a strategy used on a previously-normalized database to increase the read performance of a database, at the expense of losing some write performance, by adding redundant copies of data or by grouping data. • Data accessed together should be stored together.
- Slides: 27