02- lecture 2 fd
TRANSCRIPT
-
7/28/2019 02- Lecture 2 FD
1/22
COMP1212
Anomalies and Dependencies
Dr. Mabruk Ali
-
7/28/2019 02- Lecture 2 FD
2/22
Semantics of the Relation Attributes
GUIDELINE 1: Informally, each tuple in arelation should represent one entity orrelationship instance. (Applies to individual
relations and their attributes). Attributes of different entities (EMPLOYEEs, DEPARTMENTs,
PROJECTs) should not be mixed in the same relation
Only foreign keys should be used to refer to other entities
Entity and relationship attributes should be kept apart as much
as possible.
Bottom Line: Design a schema that can beexplained easily relation by relation. Thesemantics of attributes should be easy to
interpret.
-
7/28/2019 02- Lecture 2 FD
3/22
Redundant Information in Tuples and Update
Anomalies
Mixing attributes of multiple entities maycause problems
Information is stored redundantly wasting
storage Problems with update anomalies
Insertion anomalies
Deletion anomalies
Modification anomalies
-
7/28/2019 02- Lecture 2 FD
4/22
Slide 4
Employee Salary Project Budget Role
Brown 20 Alpha 2 Technician
Green 35 Gamma 15 Designer
Green 35 Epsilon 9 Designer
Hoskins 55 Epsilon 9 Manager
Hoskins 55 Gamma 15 Consultant
Moore 48 Gamma 15 Manager
Moore 48 Epsilon 9 Designer
Data redundancy
Values stored repetitively in relations(esp. poorly designed relations)
Potential for anomalous data to be
stored
This relation
associates
employees withprojects. Assume
no nulls are
allowed.
-
7/28/2019 02- Lecture 2 FD
5/22
Slide 5
Both values
updated: OK
Update anomalies
Each persons salary is repeated foreach project they are involved with.
What does this imply when we need to
increase someones salary?Employee Salary Project Budget RoleBrown 20 Alpha 2 Technician
Green Gamma 15 Designer
Green Epsilon 9 Designer
Hoskins 55 Epsilon 9 Manager
Hoskins 55 Gamma 15 Consultant
Moore Gamma 15 Manager
Moore 48 Epsilon 9 Designer
35
35
37
37
48Only one value
updated: ANOMALY
50
-
7/28/2019 02- Lecture 2 FD
6/22
Slide 6
If a project ends (i.e., is deleted), whathappens to the data for employees on
that project?
Employee Salary Project Budget Role
Green 35 Gamma 15 Designer
Green 35 Epsilon 9 Designer
Hoskins 55 Epsilon 9 Manager
Hoskins 55 Gamma 15 Consultant
Moore 48 Gamma 15 Manager
Moore 48 Epsilon 9 Designer
Brown 20 Alpha 2 TechnicianDelete project Alpha
Delete anomalies
What happens to
(Brown, 20)?ANOMALY
-
7/28/2019 02- Lecture 2 FD
7/22
Slide 7
Johnson hasnt yet
been assigned to a
project, but no nulls
allowed
Insert anomalies
What happens when we hire a newperson? (remember, no nulls allowed)
Employee Salary Project Budget RoleBrown 20 Alpha 2 Technician
Green 35 Gamma 15 Designer
Green 35 Epsilon 9 Designer
Hoskins 55 Epsilon 9 Manager
Hoskins 55 Gamma 15 Consultant
Moore 48 Gamma 15 Manager
Moore 48 Epsilon 9 Designer
Employee Salary Project Budget RoleBrown 20 Alpha 2 Technician
Green 35 Gamma 15 Designer
Green 35 Epsilon 9 Designer
Hoskins 55 Epsilon 9 Manager
Hoskins 55 Gamma 15 Consultant
Moore 48 Gamma 15 Manager
Moore 48 Epsilon 9 Designer
Johnson 36 ??? ??? ???
Where do we store
(Johnson, 36) until
then? ANOMALY
-
7/28/2019 02- Lecture 2 FD
8/22
Slide 8
Employee Salary
Brown 20
Green 35
Hoskins 55
Moore 48
Employee Project Role
Brown Alpha Technician
Green Gamma Designer
Green Epsilon Designer
Hoskins Epsilon ManagerHoskins Gamma Consultant
Moore Gamma Manager
Moore Epsilon Designer
Project Budget
Alpha 2
Gamma 15Epsilon 9
The solution: Normalisation
Breaking up the relation eliminates theworst of the redundancy
-
7/28/2019 02- Lecture 2 FD
9/22
Functional Dependencies(FD)
An important concept associated with normalization.
Functional dependency describes the relationshipbetween attributes.
For example, if A and B are attributes of relation R, B is
functionally dependent on A (denoted A B), if eachvalue of A in R is associated with exactly one value of Bin R.
An alternative way to describe the relationship between
attributes A and B is to say thatA functionally determines B.
A Called (the Determinant) B Called (the dependent)
-
7/28/2019 02- Lecture 2 FD
10/22
Characteristics of FDs
Determinants should have the minimalnumber of attributes necessary to
maintain the functional dependency with
the attribute(s) on the right hand-side.
This requirement is called full functional
dependency.
-
7/28/2019 02- Lecture 2 FD
11/22
Identifying FDs
Identifying all functionaldependencies between a set of
attributes is relatively simple if the
meaning of each attribute and therelationships between the attributes
are well understood.
This information should be providedby the enterprise in the form of
discussions with users and/or
documentation such as the usersre uirements s ecification.
-
7/28/2019 02- Lecture 2 FD
12/22
Identifying FDs (Cont)
However, if the users are unavailablefor consultation and/or the
documentation is incomplete then
depending on the databaseapplication it may be necessary for the
database designer to use their
common sense and/or experience toprovide the missing information.
-
7/28/2019 02- Lecture 2 FD
13/22
Examples of FD constraints (1)
social security number determinesemployee name
SSN -> ENAME
project number determines project nameand location
PNUMBER -> {PNAME, PLOCATION}
employee ssn and project number
determines the hours per week that theemployee works on the project
{SSN, PNUMBER} -> HOURS
-
7/28/2019 02- Lecture 2 FD
14/22
Types of functional
dependency Full
Partial
Transitive
-
7/28/2019 02- Lecture 2 FD
15/22
Full Functional Dependency
Full functional dependencyindicates that if Aand B are attributes of a relation. B is fully
functionally dependent on A, if B is functionally
dependent on A, but not on any proper subset of
A.
A functional dependency A B is a partially
dependency if there is some attribute that canbe removed from A and yet the dependency still
holds.
A B == LHS RHS
-
7/28/2019 02- Lecture 2 FD
16/22
Slide 16
Example of Full FD Example: R(Year, Course_code, Course_coordinator)
year + course_code course_coordinator
(i.e., course_coordinator determined by combination
of both a particular year and a course_code)
If we remove either Year or Course code from the
left hand side (LHS) (the determinant), the
dependency is no longer exist.
Year
Course_codeCourse_coordinator
-
7/28/2019 02- Lecture 2 FD
17/22
Slide 17
Partial functional dependency
Subset of left hand side determines right handside
extra attributes on LHS are unnecessary
Student ID
Date of
Birth
Invoice
Number
Student
NameInvoice
Date
Invoice
Total
R1(StudentId, StudentName,DateOfBirth)R2(InvoiceNumber, InvoiceDate, InvoiceTotal)
-
7/28/2019 02- Lecture 2 FD
18/22
Slide 18
Now Full functional
dependency
left hand side determines right hand side No extra attributes on LHS are unnecessary
Student IDDate of
BirthInvoice
Number
Invoice
Total
-
7/28/2019 02- Lecture 2 FD
19/22
Slide 19
Transitive dependency Transitive dependency
part number determines supplier number
supplier number determines supplier name
therefore, part number alone also determines
supplier name
Ideally should not exist within the samerelation
Part
number
Supplier
number
Supplier
name
Part
number
Supplier
name
-
7/28/2019 02- Lecture 2 FD
20/22
Transitive Dependency
It is important to recognize a transitivedependency because its existence in arelation can potentially cause updateanomalies.
Transitive dependency describes acondition where A, B, and C are attributesof a relation such that if
A B and
B C, then
C is transitively dependent on A via B
(provided that A is not functionallydependent on B or C).
-
7/28/2019 02- Lecture 2 FD
21/22
MVD & JD
Normal Forms will be discussed nextlecture.
The fourth normal form makes use of a new
kind of dependency, called a multivalueddependency (MVD); MVDs are ageneralization of FDs.
The fifth normal form makes use of anothernew kind of dependency, called ajoindependency (JD); JDs are a generalization
of MVDs, just as MVDs are a generalization
-
7/28/2019 02- Lecture 2 FD
22/22
The End
Lecture 05 - ER to Relation Mapping 22