Task 1 (15 marks)

Draw and present a dependency diagram for the table above, including transitive dependencies. In your oral presentation, you need to explain each dependency.

Task 2 (5 marks)

If the table above is not in the first normal form, normalise it so that it is in the first normal form without creating any more tables. You may create an additional attribute or attributes. Orally present your solution and the reason why it was not in the first normal form, and what changes were made to make it in the first normal form. If the table above is in the first normal form, explain why it is in first normal form.

Task 3 (15 marks)

If the resulting table from the previous task is not in the second normal form, normalise it so that it is in the second normal form without creating any more tables. You may create an additional attribute or attributes. Orally present your solution and the reason why it was not in the second normal form, as well as the changes that were made to turn it to the second normal form. If the resulting table from the previous task is already in the second normal form, explain why it is in the second normal form.

Task 4 (30 marks)

If the resulting table from the previous task is not in the third normal form, normalise it so that it is in the third normal form. You may create additional tables. Orally present your solution and the reason why it was not in the third normal form, as well as the changes that were made to turn it to the third normal form. If the resulting table from the previous task is already in the third normal form, explain why it is in the third normal form.

Task 5 (15 marks)

Based on the resulting table from the previous task, draw and present an ERD to the audience. Please use LucidChart or an equivalent professional diagramming software application to draw the ERD.

1. Examine the following STUDENT table, which is used for a student record management system in an Australian university. Attribute name Sample value Sample value Sample value Sample value STU_NAME (Primary Rhys Pickering Mandeep Kaur Manpreet Singh Xiao Zhang Key) STU_PHONENUMBER 0406465234,03 0406475234,03 0406467334,03 0406469034, 03 9654 3316 9654 3336 9654 3318 9654 3319 STU_DOB 3/12/1997 4/8/1998 8/4/1997 5/6/2000 STU_HOMECOUNTRY Australia India India China STU_CONTINENT Oceania Asia Asia Asia STU_GENDER Male Female Male Female STU_SCHOOL Business IT and IT and Business Engineering Engineering STU_MAJOR Accounting Finance Software Development Information Systems Andrew Yue SCHOOL_HEAD SCHOOL_PHONE Suzanne Pita 9067 1835 Suzanne Pita 9067 1835 9054 1321 STU_SUBJECT Accounting Principles, Business Law, Quantitative Method Andrew Yue 9054 1321 Introduction to Microeconomics, Principles of Finance Introduction to Programming, Database Fundamentals Introduction to Programming, Introduction to Information Systems SUBJECT_LECTURER James, Andrew, June, Andrew, John Terry, Peter Fariza Wei SUBJECT_CODE BIT1001, FINC1001, ACCT1001, BLAW1002 QUAN1003 BIT1001, BIT1002 BIT1003 FINC1002 12,6 12,6 6,6 SUBJECT_CREDITPOINT 6, 6, 6 STU_SUBJECTMARK 60, 70, 80 STU_AVERAGEMARK 50, 60 70, 80 70, 70 70 55 75 70 MIS201_Assessment_2_Brief_Presentation_Module 4.1 Page 2 of 8 STU_SUBJECTGRADE P, C, D PP CD CC 1. Examine the following STUDENT table, which is used for a student record management system in an Australian university. Attribute name Sample value Sample value Sample value Sample value STU_NAME (Primary Rhys Pickering Mandeep Kaur Manpreet Singh Xiao Zhang Key) STU_PHONENUMBER 0406465234,03 0406475234,03 0406467334,03 0406469034, 03 9654 3316 9654 3336 9654 3318 9654 3319 STU_DOB 3/12/1997 4/8/1998 8/4/1997 5/6/2000 STU_HOMECOUNTRY Australia India India China STU_CONTINENT Oceania Asia Asia Asia STU_GENDER Male Female Male Female STU_SCHOOL Business IT and IT and Business Engineering Engineering STU_MAJOR Accounting Finance Software Development Information Systems Andrew Yue SCHOOL_HEAD SCHOOL_PHONE Suzanne Pita 9067 1835 Suzanne Pita 9067 1835 9054 1321 STU_SUBJECT Accounting Principles, Business Law, Quantitative Method Andrew Yue 9054 1321 Introduction to Microeconomics, Principles of Finance Introduction to Programming, Database Fundamentals Introduction to Programming, Introduction to Information Systems SUBJECT_LECTURER James, Andrew, June, Andrew, John Terry, Peter Fariza Wei SUBJECT_CODE BIT1001, FINC1001, ACCT1001, BLAW1002 QUAN1003 BIT1001, BIT1002 BIT1003 FINC1002 12,6 12,6 6,6 SUBJECT_CREDITPOINT 6, 6, 6 STU_SUBJECTMARK 60, 70, 80 STU_AVERAGEMARK 50, 60 70, 80 70, 70 70 55 75 70 MIS201_Assessment_2_Brief_Presentation_Module 4.1 Page 2 of 8 STU_SUBJECTGRADE P, C, D PP CD CC