2. Create an ER diagram for the following problem description. (20 marks) A hospital maintains a nursing roster to ensure that there is sufficient coverage for all of their patients. Each ward has a...

2. Create an ER diagram for the following problem description. (20 marks) A hospital maintains a nursing roster to ensure that there is sufficient coverage for all of their patients. Each ward has a nurse who is the supervisor. One nurse is assigned to each ward (other than the supervisor). Normally nurses can be scheduled to work on any ward; however some wards require nurses with extra qualifications (e.g. Intensive care, maternity, chemotherapy qualifications). Therefore hospital keeps track of their qualifications and nurses are assigned to wards based on their qualifications. Each nurse has a unique identification number (NID), name, DoB, start year, and the ward ID that they are assigned to. Each hospital floor has multiple wards. A ward is identified by using a unique ID such as "E14" (East 14). Beds are numbered from 1 upwards within each ward and number of beds in each ward is recorded. For each ward we need to keep track of the following: • Ward ID (WID) • Floor ID (FL_ID) • Supervisor ID (SID) • Nurse ID (NID) • Number of beds in each ward (No_Beds) 3. Create a database schema that implements your requirements. Here you are required to use appropriate table name for each entity in the above ER diagram, enter suitable attributes for each entity and underline the Primary key of each table. (05 marks) Eg. Nurse (NID, Name, WID, qualifications,start_date,…..) Ward (WID, FL_ID, SID, NID, No_Beds) 4. Use MS Access to implement the database schema you created in question 3 above. Here you are required to use SQL commands to create tables. a. Write SQL commands you used to create tables. (05 marks) b. You are required to insert sufficient data into all of your tables so that you can test all of the SQL queries in section 5. 5. Write SQL queries for the following questions and execute the queries on the database you created in section 4, on MS Access. (5 marks x 4 =20 marks) a. Display IDs of all Wards located in Floor 4. (05 marks) b. Display number of beds in ward “E14” which is located in floor 6. (05 marks) c. Display names and IDs of nurses who are working in ward “S14”. (05 marks) d. Display names of all nurses who are working in floor 6. Note that Floor number is stored only in Ward table. You are required to join Ward table and Nurse table to answer this query. (05 marks)