5

Dependencies: Definitions
 Multivalued Attributes (or repeating groups): non-
key 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).

STUDENT

Stud_ID Name Course_ID Units

101 Lennon MSI 250 3.00
101 Lennon MSI 415 3.00
125 Johnson MSI 331 3.00

1
 5
Dependencies: Definitions
 Partial Dependency – when an non-key
attribute is determined by a part, but not the
whole, of a COMPOSITE primary key.

CUSTOMER Partial Dependency

Cust_ID Name Order_ID

101 AT&T 1234
101 AT&T 156
125 Cisco 1250

2
 5
Dependencies: Definitions

 Transitive Dependency – when a non-

key attribute determines another non-key
attribute. Transitive
Dependency

EMPLOYEE

Emp_ID F_Name L_Name Dept_ID Dept_Name

111 Mary Jones 1 Acct
122 Sarah Smith 2 Mktg

3
 5
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
4
 5
Example 1: Determine NF
All attributes are directly
 ISBN  Title or indirectly determined
 ISBN  Publisher by the primary key;
therefore, the relation is
 Publisher  Address at least in 1 NF

BOOK

ISBN Title Publisher Address

International Standard Book Number (ISBN) is a 13-digit

number that uniquely identifies books

5
 5
Example 1: Determine NF
 ISBN  Title
The relation is at least in 1NF.
 ISBN  Publisher There is no COMPOSITE primary
 Publisher  Address key, therefore there can’t be
partial dependencies. Therefore,
the relation is at least in 2NF
BOOK

ISBN Title Publisher Address

6
 5
Example 1: Determine NF
Publisher is a non-key attribute,
 ISBN  Title and it determines Address,
 ISBN  Publisher another non-key attribute.
Therefore, there is a transitive
 Publisher  Address dependency, which means that
the relation is NOT in 3 NF.
BOOK

ISBN Title Publisher Address

7
 5
Example 1: Determine NF
We know that the relation is at
 ISBN  Title least in 2NF, and it is not in 3
 ISBN  Publisher NF. Therefore, we conclude
that the relation is in 2NF.
 Publisher  Address

BOOK

ISBN Title Publisher Address

8
 5
Example 1: Determine NF
 ISBN  Title In your solution you will write the
 ISBN  Publisher following justification:
1) No M/V attributes, therefore at
 Publisher  Address 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

9
 5
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

10
 5
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

11
 5
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

12
 5
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

13
 5
Example 3: Determine NF
 Part_ID  Description
Comp_ID and No are not
 Part_ID  Price determined by the primary
 Part_ID, Comp_ID  No 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

14
 5
Example 3: Determine NF
In your solution you will write
 Part_ID  Description the following justification:
 Part_ID  Price 1) There are M/V attributes;
therefore, not 1NF
 Part_ID, Comp_ID  No Conclusion: The relation is not
normalized.

PART

Part_ID Descr Price Comp_ID No

15
 5
Bringing a Relation to 1NF

STUDENT

Stud_ID Name Course_ID Units

101 Lennon MSI 250 3.00
101 Lennon MSI 415 3.00
125 Johnson MSI 331 3.00

16
 5
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

STUDENT

Stud_ID Name Course_ID Units

101 Lennon MSI 250 3.00
101 Lennon MSI 415 3.00
125 Johnson MSI 331 3.00

17
 5
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.
STUDENT

Stud_ID Name Course_ID Units

101 Lennon MSI 250 3.00
101 Lennon MSI 415 3.00
125 Johnson MSI 331 3.00

18
 5
Bringing a Relation to 1NF
STUDENT

Stud_ID Name
101 Lennon
125 Jonson

STUDENT_COURSE

Stud_ID Course Units

101 MSI 250 3
101 MSI 415 3
125 MSI 331 3

19
 5
Bringing a Relation to 2NF

Composite
Primary Key

STUDENT

Stud_ID Name Course_ID Units

101 Lennon MSI 250 3.00
101 Lennon MSI 415 3.00
125 Johnson MSI 331 3.00

20
 5
Bringing a Relation to 2NF
 Goal: Remove Partial Dependencies
Composite Partial Dependencies
Primary Key

STUDENT

Stud_ID Name Course_ID Units

101 Lennon MSI 250 3.00
101 Lennon MSI 415 3.00
125 Johnson MSI 331 3.00

21
 5
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.
STUDENT

Stud_ID Name Course_ID Units

101 Lennon MSI 250 3.00
101 Lennon MSI 415 3.00
125 Johnson MSI 331 3.00

22
 5
Bringing a Relation to 2NF
CUSTOMER
STUDENT_COURSE
Stud_ID Name Course_ID Units
101 Lennon MSI 250 3.00
101 Lennon MSI 415 3.00 Stud_ID Course_ID
125 Johnson MSI 331 3.00
101 MSI 250
101 MSI 415
125 MSI 331

STUDENT COURSE

Stud_ID Name Course_ID Units

101 Lennon MSI 250 3.00
101 Lennon MSI 415 3.00
125 Johnson MSI 331 3.00
23
 5
Bringing a Relation to 3NF

 Goal: Get rid of transitive dependencies.

Transitive
Dependency
EMPLOYEE

Emp_ID F_Name L_Name Dept_ID Dept_Name

111 Mary Jones 1 Acct
122 Sarah Smith 2 Mktg

24
 5
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.

EMPLOYEE

Emp_ID F_Name L_Name Dept_ID Dept_Name

111 Mary Jones 1 Acct
122 Sarah Smith 2 Mktg

25
 5
Bringing a Relation to 3NF
EMPLOYEE

Emp_ID F_Name L_Name Dept_ID Dept_Name

111 Mary Jones 1 Acct
122 Sarah Smith 2 Mktg

EMPLOYEE

Emp_ID F_Name L_Name Dept_ID

111 Mary Jones 1
122 Sarah Smith 2

DEPARTMENT

Dept_ID Dept_Name
1 Acct
2 Mktg

26