Q2. Normalisation (10 marks)

A little league netball organises a competition for under-16 teams. Each team is graded according to their age bracket. Grade A is for the 15-16 years old and grade B for the 13-14 years old. Matches are organised for both grades and a match can have teams from different grades competing. At the end of the competition, the winner is determined for each grade. The competition runs for seven weeks and consists of eight teams. Each round, there are four matches scheduled on Saturday during the competition weeks. The following schedule shows some samples of the information about the matches.

Represent this form in UNF. In creating your representation, you should note the following:

• The league have indicated that Teams are identified by a team_id and Umpires are identified by an umpire_id, these attributes must be added to your UNF
• Each Team has a Team name and the grade they play in (A or B)  as indicated on this form.
• Round numbers are not reused, i.e. the round number is unique
• A given team can only play once in any round (as illustrated above)

Continue the normalisation to third normal form (3NF).

Clearly write the relations in each step from the unnormalised form (UNF) to the third normal form (3NF).

Clearly indicate primary keys on all relations from 1NF onwards by underlining the primary key attribute/s. Show the dependencies (partial dependency at 1NF, transitive dependency at 2NF, and full dependency at 3NF) via dependency diagrams, e.g., a_id → a_name, a_desc.

Include all candidate keys at the 1NF stage.

Do not add new attributes during the normalisation.