For each of the following relations, indicate the normal form for that relation. If the relation is not in third normal form, decompose it into 3NF relations. Functional dependencies (other than those implied by the primary key) are shown where appropriate.
a. CLASS(CourseNo, SectionNo)
b. CLASS(CourseNo, SectionNo, Room)
c. CLASS(CourseNo, SectionNo, Room, Capacity) [FD: Room → Capacity]
d. CLASS(CourseNo, SectionNo, CourseName, Room, Capacity) [FD: CourseNo → CourseName; FD: Room → Capacity]
Normal forms of the relations
a)
Relation:
CLASS(CourseNo, SectionNo)
The above relation is in 3NF.
Explanation:
• The non-primary key attribute SectionNo does not depend on other non-primary key attribute CourseNo.
• So, the relation does not contain any transitive dependency.
• It shows the form of dependency that is functional.
b)
Relation:
CLASS(CourseNo, SectionNo, Room)
The above relation is in 3NF.
Explanation:
• The non-primary key attribute SectionNo does not depend on other non-primary key attribute Room.
• So, the relation does not contain any transitive dependency.
• It shows the form of dependency that is functional.
c)
Relation:
CLASS (CourseNo, SectionNo, Room, Capacity)
[FD: Room -> Capacity]
The above relation is in 2NF.
Explanation:
• All the non-key attributes Capacity, SectionNo and Room are functionally dependent on primary key attribute CourseNo.
Decompose 2NF to 3NF:
• To get 3NF from 2NF, a new relation should be created from the 2NF relation.
• The relation is converted in third normal form by moving non-key attribute.
Explanation:
• Here, Capacity is the non-key attribute; it is moved to form a new relation along Room attribute.
CLASS (CourseNo, SectionNo, Room)
Room (Room, Capacity)
• The attribute Capacity is functionally dependent on Room.
• Therefore, the normal form of the relation is 3NF.
• It does not have a transitive dependency.
Relation:
CLASS (CourseNo, SectionNo, CourseName, Room, Capacity)
[FD: CourseNo -> CourseName; FD: Room -> Capacity]
The above relation is in 1NF.
Explanation:
• The table does not contain any replicate fields or groups of fields, hence it is 1NF.
Decompose 2NF to 3NF:
• To get 3NF from 1NF, a new relation should be created from the 1NF relation.
• The relation is converted in third normal form by moving non-key attribute.
Explanation:
• It is converted in third normal form by moving non-key attribute.
Course (CourseNo, CourseName)
Class (CourseNo, SectionNo, Room)
Room (Room, Capacity)
• A relation does not have any repeating attributes.
• Two new relations are created from the relation.
• CourseNo and CourseName are functionally dependent.
• Capacity attribute is non-key and it is dependent functionally.
• The non-primary key attribute SectionNo does not depend on other non-primary key attribute CourseNo.
• The non-primary key attribute SectionNo does not depend on other non-primary key attribute Room.
• Hence, there is no transitive dependency in the relations.
• Therefore, the normal form of the relation is 3NF.