(Answered): Task 3 Use Normalisation to validate your ERD and derive the relational schema [20 marks] (a) Use t ...

Task 3 Use Normalisation to validate your ERD and derive the relational schema [20 marks] (a) Use the technique of normalisation to validate your logical ERD data model. Demonstrate that each of your relations is in third normal form (3NF) by displaying the functional dependencies between attributes in each relation. Note, if any of your relations are not in 3NF, this may indicate that your ER model is structurally incorrect or that you have introduced errors in the process of deriving relations from your model. [10 marks) (b) To further demonstrate your knowledge of normalisation, assume that a proposed (badly structured) relation for the Faculty Database has the structure shown below. Identify the functional dependencies represented in this relation and demonstrate the process of normalising this relation into 3NF relations. [10 marks) StudentID Student Iname 005021 McLeod 004647 Smith F001 Student sex PaperTitle Paper performance flat No address Iname startDate Andrew M DB Design 13/07/2020 Pass 7 Symonds Street, Auckland Anna F Program 15/07/2020 Pass F001 7 Symonds Street, Auckland Design Michael M Networking 17/07/2020 Fail FOO2 24 Vulcane Lane, Auckland Anna F DB Design 13/07/2020 Pass F003 24 Vulcane Lane, Auckland Andrew M Networking 17/07/2020 Pass F001 7 Symonds Street, Auckland Anna F Networking 17/07/2020 Fail F001 7 Symonds Street, Auckland 014670 Owen 016765 Smith 005021 McLeod 004647 Smith INFS601 Case Study for Database Project Assignment A major faculty in the AsiaPacific University (APU) has approached you for the design and implementation of a relational database system that will provide information on the courses (programmes) it offers, the academic departments that run the courses, the academic staff and the enrolled students. The system will be used mainly by the students and the academic staff. The requirement collection and analysis phase of the database design process provided the following data requirements for the Faculty Database system. Each department runs a number of courses. The faculty provides a set of papers (e.g. Programming I, Logical Database Design) used in different courses (e.g. Bachelor of Science, Bachelor of Information Technology). Each course uses a number of papers but not every paper is used. A course is assigned a unique course code and a paper is identified by a unique paper code. A naper can be used in more than course, and it can be studied by many students. In addition to the paper code, each paper unique title, start date, end date, paper resources (e.g. textbooks), and assessment scheme (i.e. assessment and exam marks percentages) are also stored. Each course is managed by a member of academic staff, and each paper is coordinated by a member of academic staff also. The database should also store each course unique title, and duration (in years). A student can enrol in one course at a time. Once enrolled a student is assigned a unique student identification number. To complete a course, each student must undertake and pass all the required papers in the course that the student is enrolled in. This requires that the database store the performance (pass or fail) of each student in every paper. Additional data stored on each student includes student name (first and last), address (town, street, and post code), date of birth, sex, and student financial loan (Yes or No). For emergency purposes the database stores the last name, first name, address (not composite), phone, and relationship of each student next-of-kin. None of the next-of-kin's Each department is led by a member of academic staff (e.g. Head of the Department). The database should record the date the staff member started the role as Head of the department. Each department has a name, phone number, fax number, and location (e.g. Z Block). Each department employs many members of academic staff. A member of academic staff can be the coordinator of at most one course but can be the coordinator of more than one paper. A member of academic staff may not be assigned any of the above-mentioned roles (coordinator, course leader, Head of the Department manager). All members of academic staff teach papers. Every member of academic staff may teach zero, one or more papers, and a paper may be taught by more than one member of academic staff. The database should record the number of hours per week a member of academic staff spends teaching each paper. Each member of academic staff is identified by a unique staff number. Additional data stored on each member of academic staff includes name (first and last), phone extension number, office number, sex, salary, position (lecturer, or senior lecturer, or Professor, etc.), qualifications, and address (not composite). A member of academic staff works for one department only.