Data Management Systems Design

1
Data Management Systems Design

• CIS 631 Lecture Notes 9
• Cyril S. Ku

2
Normal Forms
First ( 1 NF ) Atomicity Second ( 2 NF
) FD and Key Constraints Third ( 3 NF ) FD
and Key Constraints Boyce-Codd (BCNF) Stronger
3NF Fourth ( 4 NF ) Multi-Valued
Dependencies Fifth ( 5 NF ) Join
Dependencies
3
KEY
R Relation Schema A1, A2, An Ai, are
attributes, 1? i ? n. Superkey S, S ? R with
property that no two tuples t1 and t2 in any
legal relation state r of R will have t1S
t2S. A key K is a superkey with additional
property that removal of any attribute from K
will cause K not to be a superkey any more. A key
(K) is minimal. That is, if we have KA1, A2,
Ak, then K-Aj is not a key for 1? j ? k. If a
relation has more than one minimal key, each is
called a candidate key. One of the candidate keys
is arbitrarily designated to be the primary key.
The others are called secondary (alternate)
keys. Each relation schema must have a primary
key.
4
First Normal Form A repeating group is a
collection of logically related attributes that
occur multiple times within one tuple.
Repeating group caused by ? multi-valued
attribute and ? multi-valued composite
attribute (nested relations). e.g. ? DEPT(DNUM,
DNAME, DLOCATION) DLOCATION - Multi-Valued
Attribute A department (DEPT) can have more
than one location e.g. ? BENEFITS(SSN, ENAME,
B_CODE, DNAME, DSEX )
An employee can have more than one dependent
- nested relation - repeating group
5
A relation is in 1st NF if it contains no
repeating groups. Eliminate repeating groups by
creating separated relations ? DEPT
(DNUM, DNAME) DEPT_LOC (DNUM, DLOCATION)
? BENEFITS (SSN, ENAME, B_CODE) DEPENDENTS
(SSN, DNAME, DSEX) Relations are in 1NF if all
domains are simple. (All elements are atomic.)
6
Second Normal Form Full FD X Y Let A ?
X ( X - A ) Y partial FD ( X -
A ) Y A relation schema R is in 2NF if
R is in 1NF and if every nonkey (non prime)
attribute A in R is fully functionally dependent
on every key of R. A relation in 1NF will be in
2NF if any one of the following applies 1.
The PK is composed of only one attribute and no
secondary keys. 2. No nonkey
attributes exist. 3. Every nonkey attribute is
dependent on the entire set of PK
attributes.
7
Emp_Proj (SSN, Pnumber, Hours, Ename, Pname,
Plocation) fd1 SSN, Pnumber Hours
fd2 SSN Ename fd3 Pnumber
Pname, Plocation EP1(SSN, Pnumber,
Hours) EP2(SSN, Ename)
2NF EP3(Pnumber, Pname, Plocation) A relation
R is in 2NF if every nonkey (non-prime) attribute
of R is fully functionally dependent on each
relation key.
Violates 2NF
8
Third Normal Form A relation schema R is in 3NF
if, whenever a FD X A holds in R,
either (a) X is a superkey or (b) A is
a prime attribute (A is a member of a candidate
key). A relation R is in 3NF if
the non-prime attributes are mutually
independent i.e., no non-prime attribute is
functionally dependent on another non-prime
attribute.
9
E.g., Major (Snumber, Major-Dept,
College) Snumber --gt Major-Dept Snumber --gt
College Problem Major-Dept --gt College -
violates 3NF One solution SM (Snumber,
Major-Dept) MC (Major-Dept, College)
in 2NF
10
R1(Student, SSN, Class, State) Let R1 be R1(A,
B, C, D) gt A --gt B, A --gt C, A --gt D If B --gt
C, B --gt D, B --gt A, then B can be a candidate
key (A is the primary key). gt R1 is in Third
Normal Form. The dependencies of B --gt C, B --gt
D, and B --gt A -- look like there are
dependencies between nonkey attributes. But if B
is a candidate key (in this case, it is. Since B
determines all the other attributes in the
relation), then it is alright and R1 is still in
3NF. 1 to 1 relationship between A and B. No
repeating attributes problem as shown in the next
example (R2).
11
R2(Student, Major-Dept, College) Let R2 be
R2(A, B, C) gt A --gt B and A --gt C but B --gt
C gt R2 is not in 3NF since B --gt C implies that
there is dependency between nonkey
attributes. Problem repeated attributes in B
and C (whenever B is CS, C is Science since B --gt
C) Student Major-Dept College ---------------
--------------------------------------------- 101
CS Science 102 CS Science 113 English L
iberal Arts 239 Biology Science 369 English
Liberal Arts
12
Boyce-Codd Normal Form (BCNF) A relation schema
R is in BCNF if whenever a FD X A holds in
R, then X is a superkey of R. (3NF allows A
to be prime attribute if X is not a superkey.)
R( A, B, C ) fd1 AB C AB is a
superkey fd2 C A A is a prime
attribute C is not a superkey gt
not in BCNF
in 3NF
13
e.g. R ( Student, Phone , Course,
Grade ) Assume each student has one unique
non-shared phone . (Phone, Course is a
candidate key.) Student , Course
Grade Phone , Course Grade Student
Phone Phone Student
Problems (1) Phone of each student is
stored more than once hence there is
unnecessary redundancy. (2) A students phone
cannot be stored until that student takes
at least one course.
in 3NF
Not in BCNF
14
(3) If a student withdraws from all courses, all
information about this students is deleted
i.e., phone. (4) If the students phone
changes, more than one tuple must be
changed. The problems arise because there are
dependencies between prime attributes. R
(Student, Phone, Course, Grade) S
(Student, Phone) T (Student, Course,
Grade) Student Phone
Student, Course Grade
in BCNF