Lecture 4 Informal guidelines for good relational design

Lecture 4: Informal guidelines for good relational design Mapping ERD to Relation Ref. Chapter 3 1 Prepared by L. Nouf Almujally

Outlines • Guideline 1: • Semantics of the Relation Attributes • Guideline 2: • insertion, deletion and update anomalies • Guideline 3: • Null Values in Tuples • Guideline 4: • primary key • Guideline 5: • Avoid Data redundancy • Mapping ERD to Relations

Real World Domain Conceptual model (ERD) Relational Data Model Create schema (DDL) Load Data (DML) Lecture 4 The Process of Database Design 3

Relational Model Terminology • A relation is a table with columns and rows. • Attribute is a named column of a relation. • Tuple is a row of a relation. • Alternative Terminology for Relational Model: 4

5

• Design a schema that can be explained easily relation by relation. • Each cell of the relation should contains exactly single value • Each Attribute has a distinct name • Only foreign keys should be used to refer to other entities • Each tuple is distinct. There are no duplicate tuples • The order of attributes and tuples have no significance. Lecture 4 GUIDELINE 1: Semantics of the Relation Attributes and tuples 6

• Design a schema that does not suffer from the insertion, deletion and update anomalies. • Attributes of different entities (EMPLOYEEs, DEPARTMENTs, PROJECTs) should not be mixed in the same relation • Example Consider the relation: Lecture 4 GUIDELINE 2: insertion, deletion and update anomalies EMP_PROJ ( Emp#, Proj#, Ename, Pname, No_hours) 1. Update Anomaly: Changing the name of project number P 1 from “Billing” to “Customer-Accounting” may cause this update to be made for all 100 employees working on project P 1. 7

2. Insert Anomaly: Cannot insert a project unless an employee is assigned to. Inversely - Cannot insert an employee unless an he/she is assigned to a project. Lecture 4 Example of an anomaly 3. Delete Anomaly: When a project is deleted, it will result in deleting all the employees who work on that project. Alternately, if an employee is the sole employee on a project, deleting that employee would result in deleting the corresponding project. 8

EXAMPLE OF AN UPDATE ANOMALY Tbl_Staff_Branch Tbl_Staff Tbl_Branch

GUIDELINE 3: Null Values in Tuples • NULL values: • Unknown value: a particular person has a date of birth but it is unknown, so it is represented by NULL in the database. • Unavailable value: a person has a home phone but does not want it to be listed, so it is represented as NULL in the database. • Not applicable attribute: an attribute College Degree would be NULL for a person who has no college degrees. Lecture 4 • Relations should be designed such that their tuples will have as few NULL values as possible • Attributes that are NULL frequently could be placed in separate relations (with the primary key) 10

1. The candidate key must be unique within its domain. 2. The candidate key cannot hold NULL values (NULL is not zero. Zero is a number. NULL is 'nonexistent value'). 3. The candidate key can never change. It must hold the same value for a given occurrence of an entity for the lifetime of that entity. Lecture 4 GUIDELINE 4: Candidate key 11

GUIDELINE 5: Avoid Data redundancy • Disadvantages : 1. weak maintaining of the database 2. waste memory Lecture 4 • Data redundancy is a term used about databases and means simply that some data fields appear more than once in the database. 12

13 Lecture 4

Derive relations for logical data model • To implement the database in relational DBMS, ERD must be translated to tables 1. 2. 3. 4. Specify the name of the relation. A list of the relation’s simple attributes enclosed in brackets. Identify the primary key and foreign key(s) of the relation. Specify the identification of a foreign key, the relation containing the referenced • For example: • Staff (staff. No, f. Name, l. Name, position, sex, DOB) • Client (client. No, f. Name, l. Name, tel. No, pref. Type, max. Rent, staff. No) Foreign Key staff. No references Staff(staff. No) 14

• A (strong) entity set reduces to a table with the same attributes and PK. • If composite attributes exist, only their component simple attributes are needed. • Derived attributes are usually omitted. FName MName Lecture 4 1 - Mapping strong entity types LName Emp. No Employee ( Emp. No, Fname, Mname, Lname ) 15

• A multivalued attribute M of an entity E is represented by a separate table EM 1. Includes the multivalued attribute M in EM 2. Includes the PK of E as FK in EM 3. The PK of EM is the combination of the PK of E and the multivalued attribute M. Lecture 4 2 - mapping Multi_valued Attributes EM ( M , EPK) FKs : EPK references E (EPK) 16

Example of Multi-valued Attributes street Branch. No city tel. No Branch post. Co de • Branch( brach. No, street, city, post. Code) • Brach. Tel (tel. No, brach. No) FK: brach. No references Branch(branch. No)

• A weak entity set becomes a table that includes its key and the primary key of the owner entity as FK. • The combination of the two keys form the PK of the weak entity. Example: Employee emp. No 1 Lname M has Dep. Age Employee ( Emp. No, Lname) Dependents(emp. No, dep. Name, Dep. Age) FK : emp. No referneces Employee (Emp. No) Lecture 4 3 - Mapping Weak Entities Dependents Dep. Name 18

4 - mapping Binary Relationships ü create a new relation with columns for the PKs of the two participating entity sets, and attributes of the relationship. ü The PK of the new relatoin consists of the PKs of the two entities. ü The PKs of the two entities also serve as foreign keys referencing the entities. M STUDENT st. Name st. No Lecture 4 1. Many-to-many binary relationship set N enroll date Student (st. No, st. Name) Subject (scode, s. Name) Enroll (st. No, scode, date) FKs: st. NO reference Student(st. No) scode reference Subject(s. Code) SUBJECT s. Name s. Code 19

4 - mapping Binary Relationships ü add the PK of the one side to the many side. It also serves as a FK of the many side. ü Add the attributes of the relationship to the many-side. 1 Department Dept. Name Dept. No Lecture 4 2. One-to-many binary relationship sets Instead of using a separate table for the relationship, just modify the tables for the two entities: N Has year staff s. Name Department (Dept. No, Dept. Name) Staff (scode, s. Name , Dept. No , year) FKs: Dept. NO references Department(Dept. No) s. Code 20

4 - mapping Binary Relationships • mandatory participation on both sides • add the PK attributes of one side, and attributes of the relationship, to the other side. Lecture 4 3. One-to-one relationship sets • mandatory on one side • add the PK attributes of the optional side, and attributes of the relationship, to the mandatory side. • Optional on both sides • choose one side and add its PK, and attributes of the relationship, to the other side. 21

1: 1 relationship -Mandatory on both sides 1 employee Emp_name Emp_id 1 has year office. No • Employee( emp_name, emp_id ) • Office (office. No, office_Loc, emp_id, year) FKs: emp_id references employee (Emp_id) Office_Loc

1: 1 relationship - Mandatory on one sides 1 employee 1 has year Emp_name Emp_id spouse Spouse_id • Employee( emp_name, emp_id ) • Spouse(spouse_id, spouse_name, emp_id, year) FKs: emp_id references employee (Emp_id) Spoude_name

1: 1 relationship - Optional on both sides 1 employee Emp_name Emp_id 1 use year Car_No • Employee( emp_name, emp_id ) • Car (Car_No, Car_name, emp_id, year) FKs: emp_id reference employee (Emp_id) Car_name

5 – Mapping Unary Relationships • Follow rules for 1: 1 binary relationship. Lecture 4 • single relation with two copies of the primary key (one needs to be renamed), plus attributes of the relationship. 1 staff. No staffname staff supervise N Staff ( staff. No, staffname, supervisorstaff. No) 25

6 – Mapping n-ary Relationships Create a relation R to represent the relationship Include the PK of the participating entities E 1, E 2. . En as FKs in R. The combination of all FKs form the PK of R. Add the relationship attributes to R Lecture 4 • • Supplier (Sname) Project (projname) Supply (Sname, Part. No, Proj. Name, Quantity) f. Ks : Sname references Supplier(Sname) Part. No references Part (Part. No) Proj. Name references Project (Proj. Name) Part (Part. No) 26

27 Lecture 4

References Lecture 4 • “Database Systems: A Practical Approach to Design, Implementation and Management. ” Thomas Connolly, Carolyn Begg. 5 th Edition, Addison-Wesley, 2009. 28