Question 1: Normalisation (20 marks) The following question is based upon the PROJECTS relation below that lists details of team projects in a unit similar to ICT333 IT Project. You can assume that...

Question 1: Normalisation (20 marks)


The following question is based upon the PROJECTS relation below that lists details of team projects in a unit similar to ICT333 IT Project.  You can assume that the data is representative. The unit runs once a year. Team names are always unique, but projects can be repeated (if the team one year didn’t do a very good job, or the client needs it extending).



Year:     The year the project is run
StudentNo:      Unique student number

StudentName:      The full name of the student

Team:      A name the team chooses for themselves

FirstMajor:      The first (or only) major of the student

Project:      A descriptive name of the project

BroadArea:     The general IT area the project is in
Client:      Name of the person/organisation who is client for the project

Supervisor:      Name of the staff member who supervises the team

Grade:     The final grade awarded to the student. It is possible for members of a team to receive different grades.



You have been asked to design a relational database based on this design. You know that there are problems with the current design and that it will need to be modified in order to work effectively.


Answer the following questions. Each question is worth 5 marks


a.    Explain the problems with the existing design, in terms of the potential modification anomalies that it might exhibit.

b.    What normal form is the relation currently in? Explain your reasoning.

c.    Convert the relation to a set of relations in at least Third Normal Form (3NF). You only need to show the schema, not the data.

d.    Explain how your new design addresses the problems you identified in (a) and preserves all the information in the original design.