Everything explained clearly in the question

Read the question carefully and answer correctly

CC204 Database Design Assignment 1 Assignment 1 (10 marks) Consider a hospital information system with the following characteristics: A patient can either be a resident who is admitted to the hospital or an out-patient who comes to the hospital for an out-patient clinic For both types of patient we will need to hold the patient's name, telephone number, address, date of birth and the patient's family doctor (GP) For a resident patient we will need to hold the ward name in which the patient is currently residing, the admission date of the patient, and also information about any operations that the patient has had The operation information will have to include the date and time of the operation, the doctor (assume one) who carried out the operation plus the theatre where the operation took place For both GPs and hospital doctors we will need to hold the doctor's name and telephone number; in addition we will need to hold the GP's address and the hospital doctor's specialism code (for example, he or she may specialize in ENT problems relating to ear, nose and throat) - assume one per hospital doctor For out-patients we will need to hold information about the outpatients' appointments: the appointment date and time and the hospital doctor who attended to the patient Study the case given above and draw a complete entity-relationship diagram (ERD). Hint: Decomposing any many-to-many relationships, indicate constraints (eg. unique identifier, arcs etc.), make any assumptions that you need to make about optional/mandatory relationships and write description to support your design. Note: Please draw the ERD using crow-foot notation