SECTION A QUESTION A1. which is shown as Figure 1 below (7 marks) Consider the Excelsior University domain, a very high-level (and partial) data model for (a) (3 marks) As depicted in Figure 1, a student needs to be enrolled in at least one unit i Provide the tables of Student, Unit and Enrols with necessary attributes respec- tively ii Identify primary and foreign keys in each table. iii Provide a set of relational schemas for this model. Identify primary and foreign keys in the tables (b) (2 marks) (i) Explain what Entity Integrity is, and why it should be enforced in relational models. Use Figure 1 to provide the context for your discussion (ii) A student enrolled in ISYS224 wants it to be counted both towards her current undergraduate program, as well as a planned postgraduate program in future Is that consistent with the data-model provided in Figure 1? Please provide a brief explanation of your answer (c) (2 marks) According to policy change at Excelsior University, all new contracts for academic staff must specify whether the staff is full-time permanent or part-time temporary. Modify the data model in Figure 1 in light of this new requirement. In your modified data model, use any of Mandatory, Optional, And and Or to represent the new relationship you added Manages. Has 1 *| AcadStaff staffID PK stafiName Facult facNo PK) facName deptNo (PK deptName date Joined Monitors Offers Student Enrols Unit Teaches stulD PK uCode (PK Name Name semester Figure 1: A high-level partial data model of Excelsior University SECTION A QUESTION A1. which is shown as Figure 1 below (7 marks) Consider the Excelsior University domain, a very high-level (and partial) data model for (a) (3 marks) As depicted in Figure 1, a student needs to be enrolled in at least one unit i Provide the tables of Student, Unit and Enrols with necessary attributes respec- tively ii Identify primary and foreign keys in each table. iii Provide a set of relational schemas for this model. Identify primary and foreign keys in the tables (b) (2 marks) (i) Explain what Entity Integrity is, and why it should be enforced in relational models. Use Figure 1 to provide the context for your discussion (ii) A student enrolled in ISYS224 wants it to be counted both towards her current undergraduate program, as well as a planned postgraduate program in future Is that consistent with the data-model provided in Figure 1? Please provide a brief explanation of your answer (c) (2 marks) According to policy change at Excelsior University, all new contracts for academic staff must specify whether the staff is full-time permanent or part-time temporary. Modify the data model in Figure 1 in light of this new requirement. In your modified data model, use any of Mandatory, Optional, And and Or to represent the new relationship you added Manages. Has 1 *| AcadStaff staffID PK stafiName Facult facNo PK) facName deptNo (PK deptName date Joined Monitors Offers Student Enrols Unit Teaches stulD PK uCode (PK Name Name semester Figure 1: A high-level partial data model of Excelsior University
