The following questions will be based on data shown in Table 1.0, which was imported from an Excel spreadsheet. This represents college course information that is offered. Each course is stored in a separate file. The management of data is complex and creates data redundancy. Therefore, to avoid data redundancy, the structure needs to be normalized.
Table 1.0: Sample of college course information
Course Code Course Name Tutor Name Tutor ID Student ID Student Name DOB Gender Last Attendance Date
ICT450 Database Design and Development Professor Albus Dumbledore 306612 2016123456 Luke Skywalker 11.10.96 F 31.01.18
2016534363 Kylo Ren 12.12.96 F 15.01.18
2016788924 Thor 27.06.96 F 31.01.18
2016329874 Loki 31.08.96 F 17.01.18
2016324793 Clint Barton 23.05.96 F 23.01.18
2016479625 General Hux 17.02.96 M 15.01.18
2016328796 Leia Organa 15.04.96 M 31.01.18
a) State the normal form of the above Table 1.0. Explain your answer with reasons. (3 marks)
b) Given that information, draw its dependency diagram and identify all transitive and partial dependencies. (7 marks)
c) Normalize the table until third normal form (3NF). Show all your work. (10 marks)