Answer Happy

Question (C2): (7 marks) A hospital would like to set up a database recording which registered nurse is in charged by which medical doctor etc. Each registered nurse has a unique nurse ID, and each medical doctor has a unique doctor ID, and the following attributes are found for nurses, and medical doctors in the first round of design: 11 NURSE: nurseID, surname, first name, birthdate, dept-code, sex, salary-level, rank DOCTOR: doctorID, surname, first name, birthdate, dept_code, sex, grade A recent responsibility list shows the nurses that worked under each doctor in past 2 months, see below for some samples: doctorID nurseID 20140000 P123, P234, P883, P678 20172222 N234, N334, N222, N883 20182222 C243, C123, C883, C888 20163333 P111, P103 20164444 C222, C234 You are responsible to help design this database for the hospital. a) What relationship (one-to-one, one-to-many, many-to-many) exists between NURSE and DOCTOR? Explain your answer or draw an entity relationship diagram. (2 marks) b) How many tables would be needed to implement the relationship as indicated in (a)? List out the attributes in each table, indicating which field(s) form the primary key, and which field is the foreign key (if foreign key exists). (5 marks)