Courses, channels and lessons¶

Normalization · Part III

Problem¶

One relation holds courses, offered in several years and split into channels, with their textbook, professor, room and weekly lessons:

with the functional dependencies

• IDCorso → Titolo
• IDCorso, Anno → CodLibro
• IDCorso, Anno, Canale → IDProf, Aula
• IDCorso, Anno, Canale, Giorno → Ora
• CodLibro → Libro
• IDProf → Prof

Find the key, draw a conceptual schema without adding attributes, and decompose the relation into BCNF, showing the tables with their data.

Solution¶

1 · Key. Four attributes never appear on the right of an arrow: IDCorso, Anno, Canale, Giorno. They must all be in the key, and together they determine everything else (through the dependencies, every other attribute is reached). The key is (IDCorso, Anno, Canale, Giorno).

2 · Conceptual schema.

Each dependency becomes a level of a hierarchy of weak entities: a course has editions (identified by course and year, with one textbook), an edition has channels (identified by edition and channel, with a professor and a room), a channel has lessons (identified by channel and day, with a time).

3 · BCNF decomposition. One table per dependency, with the left side as the key. The original sheet gives the table structure with empty rows; the data below are the projections of the example.

Corso

Edizione

Libro

Canale

Professore

Lezione

Checked on SQLite: each dependency holds on the example data, and the natural join of the six tables gives back exactly the eight original rows, so the decomposition loses no information.

The key, the schema and the decomposition are correct: each table has a key equal to the left side of one dependency, so none of them violates BCNF, and the E-R hierarchy corresponds to the tables one by one.

Takeaways¶

• Attributes that are never determined by others must be in the key: start from them.
• Dependencies with growing left sides (course → course + year → + channel → + day) are a hierarchy of weak entities, each identified by its parent plus one attribute.
• After decomposing, check that the join gives back the original rows.