MyDoc is a medical center that has been offering health services in Australia for the last decade. Till now, they have been using a spreadsheet to store their patient and appointment data. Now, they have decided to use a centralized relational database to store their data. Let’s assume that you have been hired to design their database. Given the following business rule, you need to design the database of MyDoc. Business Rules: • Details such as a unique ID, name, and address need to be stored for every doctor. There are two types of doctors: GPs and Surgeons. For a GP, the Years of Experience are recorded while the area of expertise is stored for the surgeon. • For every patient, the details such as a unique patient ID, name, and address are recorded. • A patient can make multiple appointments. For every appointment, a unique appointment ID, time, and reason is recorded. There are two types of appointments: Consultation and Surgery. Duration is stored for consultation while type is stored for surgery. • Surgery appointments are done by Surgeons while Consultation appointments are done by GPs. When a surgeon does the surgery appointment, a surgery note is recorded. • An invoice is created for every appointment. Details such as a unique invoice number, date, and amount are recorded for every invoice. • A single payment is made for every invoice. Split payments are not allowed. For every payment, a unique payment code, amount paid, and date of payment are stored in the databases. HOW MANY RELATIONSHIPS ARE THERE?

Details such as a unique ID, name, and address need to be stored for every doctor. There are two types of doctors: GPs and Surgeons. For a GP, the Years of Experience are recorded while the area of expertise is stored for the surgeon. • For every patient, the details such as a unique patient ID, name, and address are recorded. • A patient can make multiple appointments. For every appointment, a unique appointment ID, time, and reason is recorded. There are two types of appointments: Consultation and Surgery. Duration is stored for consultation while type is stored for surgery. • Surgery appointments are done by Surgeons while Consultation appointments are done by GPs. When a surgeon does the surgery appointment, a surgery note is recorded. • An invoice is created for every appointment. Details such as a unique invoice number, date, and amount are recorded for every invoice. • A single payment is made for every invoice. Split payments are not allowed. For every payment, a unique payment code, amount paid, and date of payment are stored in the databases.

Assuming Mr. Moi only wants a database system that would keep records of medicine{names ofmedicine, amount in grams in stock}, purchase{medicine bought, amount in grams bought, whoattended to customer, when it was sold, contact and name of customer that bought the medicine}a. Draw a normalized ER diagram with only the most important data from question 1 bearing in _mind the information that Mr. Moi wants.

Considering the tables of the Hospital database given below, identify candidate keys, primarykeys, and foreign keys. Also, give an example of derived attributes for patient and doctor.patients (patient-id, name, Date of Birth, insurance, date-admitted, date-checked-out)doctors (doctor-id, NIC, name, date of birth, specialization)test (testid, test_name, date, time, result)doctor-patient (patient-id, doctor-id)test-log (testid, patient-id)performed-by (testid, doctor-id)

Which are entities of a patient data base in a doctor's office which has one doctor?*1 pointpatient, doctorpatient, visitdoctor, sicknesspatient, office

not a relational database?