Revise schema to fourth normal form

Question

I have made the following table:

  SCHEDULE
  -id_movie
  -id_room
  -date
  -hour

(Id_room,date,hour) form the primary key. At a specific date,at a specific hour I have only a movie in a specific room. The problem is that this table is not in the fourth normal form (4NF). Can you give me some advice? I think it is in BCNF.

The table has a multivalued dependence: id_room —>> hour.

I need to get this table to 4NF.

Answer 1

From wikipedia, 4th Normal Form

A Table is in 4NF if and only if, for every one of its non-trivial multivalued dependencies X->>Y, X is a superkey — that is, X is either a candidate key or a superset thereof.

You say that the relation has a multivalued dependence: id_room->hour.

But id_room is neither a candidate key nor a superset.

So, in that case, the relation is not in 4NF.

---

To make the schema 4NF, you can split into two relations:

  SCHEDULE_DATE_MOVIE
  -id_movie  
  -id_room   *
  -date      *

  SCHEDULE_HOUR
  -hour
  -id_room   *