Question

Identify whether or not the following are in 2NF and/or 3NF.

If they are in 2NF, explain why. If they are not in 3NF, explain and decompose them to 3NF.

EMPLOYEE (EMPLOYEE-No, SECTION-No)

CLASS (COURSE-No, SECTION-No, ROOM)

CLASS (COURSE-No, ROOM, CAPACITY, COURSE-NAME)

Answer 1

A table would be in 2NF iff:

• Table is in 1NF (First normal form):We dont have multiple values for single column or in single cell.
• No non-prime attribute is dependent on the proper subset of any candidate key of table.

So,all the 3 tables would be in 2NF as we have only one primary key and all other columns except primary key(primary attribute's) are dependent on any other primary key(If we are having more than one candidate key for a table in that case).

So,

1)EMPLOYEE (EMPLOYEE-No, SECTION-No):Employee-No is only the primary key and SECTION_No is dependent on it.

2)CLASS (COURSE-No, SECTION-No, ROOM):Here we should have only one primary key(Let it be COURSE-No) and no other candiate key and all other non-prime attribute are dependent on it.

3)Same as "2".

3NF:

A table design is said to be in 3NF if both the following conditions hold:

• Table must be in 2NF
• Transitive functional dependency of non-prime attribute on any super key should be removed.

An attribute that is not part of any candidate key is known as non-prime attribute.

In other words 3NF can be explained like this: A table is in 3NF if it is in 2NF and for each functional dependency X-> Y at least one of the following conditions hold:

• X is a super key of table
• Y is a prime attribute of table

An attribute that is a part of one of the candidate keys is known as prime attribute.

Transitive dependency means:

If "A" is dependent on "B" and "B" is dependent on "C".

A->B->C.

For 3NF we should have only one level of dependecies:

A->B

B->C.

In our example's:;

• 1)EMPLOYEE (EMPLOYEE-No, SECTION-No):

Here as we have only two columns so we 3NF is there.

2)CLASS (COURSE-No, SECTION-No, ROOM):

Here we Section no is dependent on Course-no and Then Room is dependent on SECTION-No,so here 3NF is not there.

TO Convert it into 3Nf:

CLASS(COURSE-No,SECTION-No)

CLASS(Section-No,ROOM)

3)CLASS (COURSE-No, ROOM, CAPACITY, COURSE-NAME):

Theory is same as"2:.

Converting it to 3Nf:

CLASS(COURSE-No,COURSE-NAME,SECTION-No)

CLASS(Section-No,ROOM)

CLASS(Room,Capacty)