Question

ROOM_ASSIGNMENT (Session_ID, Room_Number, Room_Capacity, Start_Time, End_Time)

Session_ID -> Room_Number
Session_ID -> Start_Time
Session_ID -> End_Time
Room_Number -> Capacity
Bringing relational table ROOM_ASSIGNMENT to the 3NF would result in creation of?

Answer 1

To achieve Third Normal Form (3NF) for the ROOM_ASSIGNMENT table, a new Room table would be created with Room_Number and Room_Capacity, separating it from the session information which would remain in the Session table.

Step-by-step explanation:

Bringing the relational table ROOM_ASSIGNMENT to the Third Normal Form (3NF) would involve ensuring that all the functional dependencies are such that they are only between a key and a non-key attribute, and that there are no transitive dependencies. Given the functional dependencies provided:

• Session_ID → Room_Number
• Session_ID → Start_Time
• Session_ID → End_Time
• Room_Number → Capacity

We can notice that the attribute Room_Capacity is dependent on Room_Number, which is not a key for the ROOM_ASSIGNMENT table. Hence, to bring the table to 3NF, we must create a new table for rooms that would include Room_Number and Room_Capacity. The revised schema would have two tables:

1. Session table with attributes Session_ID, Room_Number, Start_Time, and End_Time.
2. Room table with attributes Room_Number and Room_Capacity.

This schema ensures that every non-key attribute is only dependent on a key attribute, achieving the 3NF.