CONTINOUS ASSESSMENT COURSE CS 312 DATABASE SYSTEMProject title Design and build a database for Aka Hospital Project description Aka Hospital a leading healthcare provider has seen an incredible growth in its operations since the introduction of the database system developed by a group of students The company has seen a reduction in operational cost and improved customer service The company database has grown significantly since its inception and it now faces various performance and scalability issues Now its time to optimize and enhance the database to meet the growing demands of the company As a reference point find below the requirements of the company during the design of the existing Database System The hospital has 5 wards with a total of 80 beds available for short and longterm patients and an outpatient clinic Each ward is uniquely identified by a number for example ward 11 and also a ward name for example Otomfuo location for example E Block total number of beds and telephone extension number for each ward for example Extn 7711 When a patient visits the hospital for the first time he or she is allocated a unique patient number At this time additional details of the patient are also recorded including name first and last address telephone number date of birth gender marital status date registered with the hospital and the details of the patients nextofkin The details of a patients nextofkin are recorded which includes the nextofkins full name relationship to the patient address and telephone number For outpatients the above patient record is taken including the date and time of the appointment at the outpatient clinic The hospital has a Medical Director who has overall responsibility for the management of the hospital The Medical Director maintains control over the use of the hospital resources including staff beds and supplies in the provision of costeffective treatment for all patients The information stored for each member of staff includes a staff number name first and last full address telephone number date of birth gender position held and current salary It also includes each members qualifications which includes date of qualification type and name of institution and last employment details which includes the name of the organization position and start and finish dates The type of employment contract for each member of staff is also recorded whether the member of staff is on permanent basis or temporary contract and the type of salary payment weekly monthly When further diagnosis is expected to be carried out on a patient the patient is given an appointment Each appointment is given a unique appointment number The details of each patients appointment are recorded and include the name and staff number of the consultant performing the examination the date and time of the appointment and the examination room When a patient is prescribed medication the details are recorded This includes the patients name and number drug number and name units per day method of administration for example oral intravenous IV start and finish date The medication pharmaceutical supplies given to each patient is monitored The hospital also maintains a stock of pharmaceutical supplies for example antibiotics and painkillers The details of pharmaceutical supplies include drug number and name description dosage method of administration quantity in stock reorder level and cost per unit The drug number uniquely identifies each type of pharmaceutical supply The details of the suppliers of the surgical nonsurgical and pharmaceutical items are stored This information includes the suppliers name and number address email and telephone and fax numbers The supplier number is unique to each supplier The breakdown of the project task is given below Phase One WEEK 6 Project Score 15 marks Presentation Score 5 marks aWith the aid of a database modelling software create the following Entity Relationship Diagram ERD iConceptual ERD iiLogical ERD iiiPhysical ERD bDerive relational schema from the above case study that represents the entities and relationships Identify primary alternate and foreign keys Note use the following notation to describe your relational schema as shown in the example of a Staff relation given below staffNo fName lName address NIN sex DOB deptNo cUsing the technique of normalization normalise your schemas above to improve efficiency and reduce redundancy This may involve refining table structures indexes and relationships Phase Two WEEK 10 Project Score 15 marks Presentation Score 10 marks aUsing mysql server implement the normalized schemas Ensure that referential integrity is established between related tables bStore data into each relation The minimum tuples should be 10 for each relation cGenerate the SQL script for the database created Phase Three WEEK 15 Project Score 20 marks Presentation Score 10 marks aCreate SQL queries that are optimized f