DB Lecture 8 Relational Database Design PDF

11/27/2024 Lecture 8 Relational Database Design by ER- to-Relational Mapping © 2023 by Prof. Manal Abdel-Kader Abdel-Fattah 8.1 1 Lecture objectives 1. Design a relational database schema based on a conceptual schema design 2. Se...

11/27/2024 Lecture 8 Relational Database Design by ER- to-Relational Mapping © 2023 by Prof. Manal Abdel-Kader Abdel-Fattah 8.1 1 Lecture objectives 1. Design a relational database schema based on a conceptual schema design 2. Seven-step algorithm to convert the basic ER model constructs into relations © 2023 by Prof. Manal Abdel-Kader Abdel-Fattah 8.2 2 1  11/27/2024 ER Bank Schema © 2023 by Prof. Manal Abdel-Kader Abdel-Fattah 8.3 3 Step 1: Regular Entities Regular entity types become relations ❖ include all simple attributes ❖ include only components of compound attributes ❖ keys become primary keys ❖ if multiple keys (candidates) select a primary key CUSTOMER(Ssn, Name, Addr, Phone) © 2023 by Prof. Manal Abdel-Kader Abdel-Fattah 8.4 4 2  11/27/2024 Step 1: Regular Entities BANK(Code, Name, Addr) ACCOUNT(Acct_no, Type, Balance) LOAN(Loan_no, Type, Amount) © 2023 by Prof. Manal Abdel-Kader Abdel-Fattah 8.5 5 Step 2: Weak Entities Weak entity types become relations ❖ include all simple attributes ❖ include only components of compound attributes ❖ create a primary key from partial key and key of owning entity type (through identifying relationship) © 2023 by Prof. Manal Abdel-Kader Abdel-Fattah 8.6 6 3  11/27/2024 Step 2: Weak Entities Weak entity types become relations BANK_BRANCH(Bank_code, Branch_No, Addr) BANK(Code, Name, Addr) © 2023 by Prof. Manal Abdel-Kader Abdel-Fattah 8.7 7 Step 3: Binary 1:1 Relationships ❖ Approach 1: Foreign Key ❖ Chose one of the related entity types to hold the relationship (chose one with total participation, if possible) ❖ move all relationship attributes to this relation ❖ Approach 2: Merged Relation ❖ combine the relations for the related entities into a single relation ❖ use only when both participations are total © 2023 by Prof. Manal Abdel-Kader Abdel-Fattah 8.8 8 4  11/27/2024 Step 3: Binary 1:1 Relationships Approach 1: Foreign Key EMPLOYEE(Ssn, Name, …) DEPARTMENT(Name, Number, Mgr, Mgr_start_date) © 2023 by Prof. Manal Abdel-Kader Abdel-Fattah 8.9 9 Step 3: Binary 1:1 Relationships Approach 2: Merged Relation AJB(x, y, p, q, r) OR AJB(x, y, p, q, r) © 2023 by Prof. Manal Abdel-Kader Abdel-Fattah 8.10 10 5  11/27/2024 Step 4: Binary 1:N Relationships 1:N Relationships become foreign key at N side ❖ any relationship attributes also go to N side LOAN(Loan_no, Type, Amount, Bank, Branch) BANK_BRANCH(Bank_code, Branch_No, Addr) © 2023 by Prof. Manal Abdel-Kader Abdel-Fattah 8.11 11 Step 4: Binary 1:N Relationships ACCOUNT(Acct_no, Type, Balance, Bank, Branch) BANK_BRANCH(Bank_code, Branch_No, Addr) © 2023 by Prof. Manal Abdel-Kader Abdel-Fattah 8.12 12 6  11/27/2024 Step 5: Binary M:N Relationships M:N Relationships must become a new relation ❖ contains PKs to both related entities ❖ include any simple attributes of M:N relationship type CUSTOMER(Ssn, Name, Addr, Phone) A_C(Acct, Cust) ACCOUNT(Acct_no, Type, Balance, Bank, Branch) © 2023 by Prof. Manal Abdel-Kader Abdel-Fattah 8.13 13 Step 5: Binary M:N Relationships CUSTOMER(Ssn, Name, Addr, Phone) L_C(LoanID, Cust) Loan(Loan_no, amount,Type, Bank, Branch) © 2023 by Prof. Manal Abdel-Kader Abdel-Fattah 8.14 14 7  11/27/2024 Step 6: Multivalued Attributes For each multivalued attribute ❖ Create a new relation ❖ Primary key of R is the combination of A and K ❖ If the multivalued attribute is composite, include its simple components DEPARTMENT(Name, Number, Mgr, Mgr_start_date) DEPT_LOCATIONS(Dno, Location) © 2023 by Prof. Manal Abdel-Kader Abdel-Fattah 8.15 15 Step 7: N-ary Relationships Non-Binary Relationships become new relations ❖ For each n-ary relationship type R, create a new relation S to represent R ❖ Include primary keys of participating entity types ❖ Include any simple attributes as attributes SUPPLIER(SName) PROJECT(Proj_name) PART(Part_no) SUPPLY(SName, PName, Part, Quantity) © 2023 by Prof. Manal Abdel-Kader Abdel-Fattah 8.16 16 8  11/27/2024 Completed Bank Schema CUSTOMER(Ssn, Name, Addr, Phone) BANK(Code, Name, Addr) ACCOUNT(Acct_no, Type, Balance, Bank, Branch) LOAN(Loan_no, Type, Amount, Bank, Branch) BANK_BRANCH(Bank_code, Branch_No, Addr) A_C(Acct, Cust) L_C(LoanID, Cust) BANK_BRANCH(Bank_code) refers to BANK LOAN(Bank,Branch) refers to BANK_BRANCH ACCOUNT(Bank,Branch) refers to BANK_BRANCH A_C(Acct) refers to ACCOUNT A_C(Cust) refers to CUSTOMER L_C(LoanID) refers to LOAN L_C(Cust) refers to CUSTOMER © 2023 by Prof. Manal Abdel-Kader Abdel-Fattah 8.17 17 EXERCISES 1. Create relational schema from the following: © 2023 by Prof. Manal Abdel-Kader Abdel-Fattah 8.18 18 9  11/27/2024 EXERCISES 1. Create relational schema from the following: © 2023 by Prof. Manal Abdel-Kader Abdel-Fattah 8.19 19 EXERCISES 1. Create relational schema from the following: © 2023 by Prof. Manal Abdel-Kader Abdel-Fattah 8.20 20 10  11/27/2024 2. Create relational schema from the following: © 2023 by Prof. Manal Abdel-Kader Abdel-Fattah 8.21 21 Thank you © 2023 by Prof. Manal Abdel-Kader Abdel-Fattah 8.22 22 11