5
Database
Normalization
Dependencies: Definitions
Multivalued Attributes (or repeating groups): nonkey attributes or groups of non-key attributes the
values of which are not uniquely identified by
(directly or indirectly) (not functionally dependent on)
the value of the Primary Key (or its part).
Dependencies: Definitions
Partial Dependency when an non-key
attribute is determined by a part, but not the
whole, of a COMPOSITE primary key.
Partial
Dependency
Dependencies: Definitions
Transitive Dependency when a nonkey attribute determines another non-key
Transitive
attribute.
Dependency
Normal Forms: Review
Unnormalized There are multivalued
attributes or repeating groups
1 NF No multivalued attributes or
repeating groups.
2 NF 1 NF plus no partial
dependencies
3 NF 2 NF plus no transitive
dependencies
5
Example 1: Determine NF
ISBN Title
ISBN Publisher
Publisher Address
All attributes are directly
or indirectly determined
by the primary key;
therefore, the relation is
at least in 1 NF
BOOK
ISBN
Title
Publisher
Address
Example 1: Determine NF
ISBN Title
ISBN Publisher
Publisher Address
The relation is at least in 1NF.
There is no COMPOSITE
primary key, therefore there
cant be partial dependencies.
Therefore, the relation is at
least in 2NF
BOOK
ISBN
Title
Publisher
Address
Example 1: Determine NF
ISBN Title
ISBN Publisher
Publisher Address
Publisher is a non-key attribute,
and it determines Address,
another non-key attribute.
Therefore, there is a transitive
dependency, which means that
the relation is NOT in 3 NF.
BOOK
ISBN
Title
Publisher
Address
Example 1: Determine NF
ISBN Title
ISBN Publisher
Publisher Address
We know that the relation is at
least in 2NF, and it is not in 3
NF. Therefore, we conclude
that the relation is in 2NF.
BOOK
ISBN
Title
Publisher
Address
Example 1: Determine NF
ISBN Title
ISBN Publisher
Publisher Address
In your solution you will write the
following justification:
1) No M/V attributes, therefore at
least 1NF
2) No partial dependencies,
therefore at least 2NF
3) There is a transitive dependency
(Publisher Address), therefore,
not 3NF
Conclusion: The relation is in 2NF
BOOK
ISBN
Title
Publisher
Address
10
Example 2: Determine NF
Product_ID Description
All attributes are directly or
indirectly determined by the
primary key; therefore, the relation
is at least in 1 NF
ORDER
Order_No
Product_ID
Description
11
Example 2: Determine NF
Product_ID Description
The relation is at least in 1NF.
There is a COMPOSITE Primary Key (PK)
(Order_No, Product_ID), therefore there can be
partial dependencies. Product_ID, which is a part
of PK, determines Description; hence, there is a
partial dependency. Therefore, the relation is not
2NF. No sense to check for transitive
dependencies!
ORDER
Order_No
Product_ID
Description
12
Example 2: Determine NF
Product_ID Description
We know that the relation is at least
in 1NF, and it is not in 2 NF.
Therefore, we conclude that the
relation is in 1 NF.
ORDER
Order_No
Product_ID
Description
13
Example 2: Determine NF
Product_ID Description
In your solution you will write the
following justification:
1) No M/V attributes, therefore at least
1NF
2) There is a partial dependency
(Product_ID Description), therefore
not in 2NF
Conclusion: The relation is in 1NF
ORDER
Order_No
Product_ID
Description
14
Example 3: Determine NF
Part_ID Description
Part_ID Price
Part_ID, Comp_ID No
Comp_ID and No are not
determined by the primary
key; therefore, the relation
is NOT in 1 NF. No sense
in looking at partial or
transitive dependencies.
PART
Part_ID
Descr
Price
Comp_ID
No
15
Example 3: Determine NF
Part_ID Description
Part_ID Price
Part_ID, Comp_ID No
In your solution you will write
the following justification:
1) There are M/V attributes;
therefore, not 1NF
Conclusion: The relation is not
normalized.
PART
Part_ID
Descr
Price
Comp_ID
No
16
Bringing a Relation to 1NF
17
Bringing a Relation to 1NF
Option 1: Make a determinant of the
repeating group (or the multivalued
attribute) a part of the primary key.
Composite
Primary Key
18
Bringing a Relation to 1NF
Option 2: Remove the entire repeating group from the
relation. Create another relation which would contain all
the attributes of the repeating group, plus the primary
key from the first relation. In this new relation, the
primary key from the original relation and the
determinant of the repeating group will comprise a
primary key.
19
Bringing a Relation to 1NF
STUDENT_COURSE
Stud_ID
Course
Units
101
MSI 250
101
MSI 415
125
MSI 331
20
Bringing a Relation to 2NF
Composite
Primary Key
21
Bringing a Relation to 2NF
Goal: Remove Partial Dependencies
Composite
Primary Key
Partial
Dependencies
22
Bringing a Relation to 2NF
Remove attributes that are dependent from the part
but not the whole of the primary key from the original
relation. For each partial dependency, create a new
relation, with the corresponding part of the primary
key from the original as the primary key.
23
Bringing a Relation to 2NF
STUDENT_COURSE
Stud_ID
Course_ID
101
MSI 250
101
MSI 415
125
MSI 331
COURSE
Course_ID
Units
MSI 250
3.00
MSI 415
3.00
MSI 331
3.00
24
Bringing a Relation to 3NF
Goal: Get rid of transitive dependencies.
Transitive
Dependency
25
Bringing a Relation to 3NF
Remove the attributes, which are dependent on a
non-key attribute, from the original relation. For each
transitive dependency, create a new relation with the
non-key attribute which is a determinant in the
transitive dependency as a primary key, and the
dependent non-key attribute as a dependent.
26
Bringing a Relation to 3NF
DEPARTMENT
Dept_ID Dept_Name
1
Acct
Mktg
27