IST210 1

Normalization Process: Exercise 2: Step 1

Step 1. Identify all the candidate keys of the relation.

(Attorney, ClientNumber, MeetingDate)

IST210 2

Normalization Process: Exercise 2: Step 2

Step 2. Identify all the functional dependencies in the relation.

• ClientNumber ClientName• When you are not certain about functional dependencies,

consider the three modification operations in Chapter 1: insert, update, and delete. • E.g., Update ClientName for ClientNumber 1000

Trivial dependency:(Attorney, ClientNumber, MeetingDate) (ClientName, Duration)

IST210 3

Normalization Process: Exercise 2: Step 3

Step 3. If any determinant is not a candidate key, the relation is not well formed.

(Attorney, ClientNumber, MeetingDate) (Attorney, ClientNumber, MeetingDate) (ClientName, Duration)

ClientNumber ClientName

IST210 4

Normalization Process: Exercise 2: Step 3

Step 3. Examine the determinants of the functional dependencies. If any determinant is not a candidate key, the relation is not well formed. In this case:

a. Place the columns of the functional dependency in a new relation of their own.

CLIENT(ClientNumber, ClientName)b. Make the determinant of the functional dependency the primary key

of the new relation.CLIENT(ClientNumber, ClientName)

c. Leave a copy of the determinant as a foreign key in the original relation.

MEETING(Attorney, ClientNumber, MeetingDate, Duration)ClientNumber: A foreign key and also part of primary key

d. Create a referential integrity constraint between the original relation and the new relation.

ClientNumber in MEETING must exist in ClientNumber in CLIENT

IST210 5

Normalization Process: Exercise 2: Step 4

Step 4. Repeat step 3 as many times as necessary until every determinant of every relation is a candidate key.

CLIENT(ClientNumber, ClientName)MEETING(Attorney, ClientNumber, MeetingDate, Duration)

ClientNumber in MEETING must exist in ClientNumber in CLIENT

Well-formed relational model design