QUESTION 1 Discuss the importance of data models. (2 marks) QUESTION 2 Given a PILOT entity with the following attributes: PILOT_NUMBER. PILOT_ADDRESS. PILOT QUALIFICATION, PILOT DATE OF BIRTH PILOT_AGE and PILOT_ICNUMBER Based on the list of attributes given above, identity Composite attribute (1 mark) Single-valued attribute (1 mark) Derived attribute (1 mark) Multivalued attribute (1 mark) QUESTION 3 Given below is a database that records data about a music track Table name: ARTIST Artisti ArtistName 20010 Ernie Zakrie 20011 Siti Nurhaliza 20012 Naim Daniel 20013 Iman Troye ArtstLabour FMC Music Siti Nurhaliza Productions World Peace Entertaiment XO House Years Active 2009 1995 2018 2018 Table name: SONG Songib Song Title 601 Teman 600 Gundah 599 Aku Bidadari Syurgamu Artist 20013 20010 20011 1) For each table, identify the primary key and foreign key(s). If a table does not have a foreign key, write None. (2 marks) Primary key Foreign key Table name ARTIST SONG ii) Does the SONG table exhibit referential integrity? Justify your answer. marks) QUESTION 4 The following entity consists of one multivalued attribute. Identify the multivalued attribute and solve the problem caused by the multivalued attribute into a more proper database design (4 marks) EMPLOYEE PK EmpiD EmpName EmpAddress EmpSkill QUESTION 5 After completing a course in database management, you have been hired as an intern by Sohar Community Hospital. You are assigned with a team to develop a database system for the hospital's software system project - a Hospital Management System. The system also considers the coronavirus Covid-19 pandemic situation and wants to have a special monitoring for Covid-19 patients. After conducting a series of interviews with a number of hospital administrators and employees, the following are the system descriptions: A doctor must treat a minimum of one patient, while, a patient may be treated by one or more doctors. Every time a doctor treats a patient, the treatment date, details, and charges are recorded. Doctor details that need to be stored are doctor ID name, contact number, date hired and license number Patient is a person who is either admitted to the hospital, or not. With regards of the current situations, special consideration to the Covid-19 pandemic must be recorded. Each patient has the following details: patient ID, name, address, birth date, gender, and contact number A Covid patient must undergo one or more Covid Tests. Covid Test is a laboratory test given only to Covid patients. Each Covid Test has a test ID, date, description, and result that need to be recorded whenever a Covid patient undergoes a Covid Test A patient is assigned to one bed if he/she needs to be admitted. If not, a patient is not assigned a bed. A bed may or may not be assigned to any patient. A bed has a bed number, type and location. Draw an Entity Relationship Diagram (ERD) for the above scenario. Identify all entities, attributes, relationships, connectivity, primary key(s) and foreign key(s). (10 marks)
