Transcript guideline 1

Lecture4
Lecture4:
Informal guidelines for good relational design
Mapping ERD to Relation
Ref. Chapter3
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)
Lecture4
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.
Lecture4
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:
Lecture4
GUIDELINE 2: insertion, deletion and update
anomalies
EMP_PROJ ( Emp#, Proj#, Ename, Pname, No_hours)
1. Update Anomaly: Changing the name of project number
P1 from “Billing” to “Customer-Accounting” may cause
this update to be made for all 100 employees working on
project P1.
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.
Lecture4
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.
Lecture4
• 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.
Lecture4
GUIDELINE 4: Candidate key
11
GUIDELINE 5: Avoid Data redundancy
• Disadvantages :
1. weak maintaining of the database
2. waste memory
Lecture4
• Data redundancy is a term used about databases and
means simply that some data fields appear more than
once in the database.
12
13
Lecture4
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 (staffNo, fName, lName, position, sex, DOB)
• Client (clientNo, fName, lName, telNo, prefType, maxRent,
staffNo) Foreign Key staffNo references Staff(staffNo)
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
Lecture4
1- Mapping strong entity types
LName
Name
EmpNo
Employee
15
Employee ( EmpNo, Fname, Mname, Lname )
• 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.
Lecture4
2- mapping Multi_valued Attributes
EM ( M , EPK)
FKs : EPK references E (EPK)
16
Example of Multi-valued Attributes
street
BranchNo
city
telNo
Branch
postCo
de
• Branch( brachNo, street, city, postCode)
• BrachTel (telNo, brachNo)
FK: brachNo references Branch(branchNo)
• 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
empNo
1
Lname
M
has
DepAge
Employee ( EmpNo, Lname)
Dependents(empNo, depName, DepAge)
FK : empNo referneces Employee (EmpNo)
Lecture4
3- Mapping Weak Entities
Dependents
DepName
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
stName
stNo
Lecture4
1. Many-to-many binary relationship set
N
enroll
date
Student (stNo, stName)
Subject (scode, sName)
Enroll (stNo, scode, date)
FKs: stNO reference Student(stNo)
scode reference Subject(sCode)
SUBJECT
sName
sCode
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
DeptName
DeptNo
Lecture4
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
sName
Department (DeptNo, DeptName)
Staff (scode, sName , DeptNo , year)
FKs: DeptNO references Department(DeptNo)
sCode
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.
Lecture4
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
officeNo
• Employee( emp_name, emp_id )
• Office (officeNo, 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
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.
Lecture4
• single relation with two copies of the primary key (one needs to
be renamed), plus attributes of the relationship.
1
staffNo
staffname
staff
supervise
N
Staff ( staffNo, staffname, supervisorstaffNo)
25
6 – Mapping n-ary Relationships
Create a relation R to represent the relationship
Include the PK of the participating entities E1,E2.. En as FKs in R.
The combination of all FKs form the PK of R.
Add the relationship attributes to R
Lecture4
•
•
•
•
Supplier (Sname)
Project (projname)
Supply (Sname, PartNo, ProjName, Quantity)
fKs :
Sname references Supplier(Sname)
PartNo references Part (PartNo)
ProjName references Project (ProjName)
Part (PartNo)
26
27
Lecture4
References
Lecture4
• “Database Systems: A Practical Approach to Design,
Implementation and Management.” Thomas Connolly,
Carolyn Begg. 5th Edition, Addison-Wesley, 2009.
28