Question 1

As a result of the COVID-19 pandemic, hospitals in Galford City are currently overwhelmed with patients. As a measure to improve work processes within the hospital, a new centre was created to cater for patients who have contracted the disease but are not in a critical state. These patients will be catered for by retired doctors, nurses and social care workers. The centre has different units and each unit has an id, name, manager and unit type. Each staff member has an id, name, date of birth, salary, position, qualification, retirement date and re-employment date and can be linked to a unit type. Patients have a patient id, name, address, gender and existing health condition and can be linked to a unit based on their health status of either vulnerable or non-vulnerable. Staff members can be assigned to more than one patient and can be managed by only one manager. One manager can manage one unit in the centre. One manager can also manage many staff members. Managers have a name, id, salary and date of birth. Given the scenario described above

1. Develop the conceptual design. (15 marks)

2. Develop the logical design. (15 marks)

3. Develop the physical design. (15 marks)

4. Ensure that the design is normalised to the Third Normal Form. Please show the steps (10 marks)

5. Implement the design by creating the tables for this design using SQL. Ensure you have enforced the constraints and other business rules required. (15 marks)