Questions 1 and 2 in this part relate to the ER model given below. The ER model illustrates the entities and relationships for modelling a bank loan service. Assume the bank has two types of staff – administration officer (abbreviated to Admin) and loan officer. Clients borrow money from the bank. Examine the ER model below and answer all the questions that follow questions in this section relate to the ER model given below ER model: STAFF StaffID OfficeNo Phone StaffType StaffType d “L” “A LOANOFFICER ADMIN Qualification) PrivilegeLevel ServiceArea serve CLIENT LOAN BORROW ClientNo Name Address Annuallncome BorrowID LoanNo LoanType InterestRate Amount Data Modelling According to the ER model given above, answer either yes or no to the following questions: (1 mark) (a) Can a loan officer have more than one qualification? (b) Can a loan officer serve more than one client? (1 mark) (c) Can a bank staff member be both an administration officer and a loan officer? (1 mark) (d) s it possible for a client to have no loans? (1 mark) 5 Marks Question 2 Converting ER models Convert the ER model given in Part A into a set of relations that satisfy Third Normal Form (3NF). You do not need to show your workings. You do not need to justify that they are in 3NF at this stage. You do not need to show sample data. Just show your relations. You should write your relations in either format shown below Student (StudentID, StudentName, DateOfBirth) Enrolment (EnrolmentlD, StudentlD, DateOfEnrolment) Question 3 4 Marks Relational model and Normalisation The following table identifies the information that doctors in a hospital made treatments for patients. You may assume that the combination of Patientl D, DoctorID and TreatDate uniquely identify each row/tuple in this relation Treatment TreatDate PatientID PatientName Phil London DoctorID DoctorName TreatDesc Take panadol Brain surgery P101 DO01 Peter Peterson 12-1-2017 Tennis Bonn Phil London DO02 15-1-2017 P101 Shirley Wu Shirley Wu P102 D001 Take antibiotics 15-1-2017 Peter Peterson Amanda Lee John Lee Heart surgery Physio-therapy Blood-transfusion 18-1-2017 P102 D003 19-2-2017 Victor Smith P103 D004 P104 Sam Sydney D002 Tennis Boon 19-2-2017 (a) On the above table, if we want to delete a record related to the treatment of the patient- Victor Smith, what kind of anomaly will happen? Justify your answer (1 mark) (b) What is the highest normal form that this relation satisfies and why? (1 mark) (c) Normalise the relation into a set of relations that satisfy 3NF using the format as follows: Relation 1 (ID1, Abc, Def,…) Relation 2 (ID2, ID1, Hij, Klm,….) Foreign key (ID1) references Relation (2 marks) PART B 10 MARKS STRUCTURED QUERY LANGUAGE QUESTIONS Students are required to answer ALL questions from this part. Each question is worth two marks (2 x 5 = 10 marks) Formulate SQL queries to answer the following information requests. Use the relations Researcher, Proposal, Review, and Expert provided below. These relations describe an application scenario for a university database that records researchers submit their proposals to a committee for review and assessment. An expert in the committee may review many proposals and a proposal may be reviewed by multiple experts. More details on these relations and tables with data populated are provided as below (tables are in next pages) Relations Researcher(ResearcherlD, FirstName, LastName, Expertise) Proposal (ProposallD, Title, RequestedFunding, ResearcherlD) Foreign key (ResearcherlD) references Researcher Review(ProposallD. ExpertID, Recommendation, ReviewDate) Foreign key (ProposallD) references Proposal Foreign key (ExpertID) references Expert Expert(ExpertID, FirstName, LastName, Expertise, ExpertType) Tables Researcher ResearcherID FirstName LastName Expertise Cell theory Artificial intelligence Fibre communications 1 John Smith 2 Michael Zhang 3 Jack London 4 Joshua Power System Vehicle design Algorithm and implementation Artificial Intelligence Macmillian Secondary education Database and Expert System Mechanical design Peterson 5 David North 6 Frank Arnott 7 Kindman Ali 8 Tony 9 Andrew Hawk 10 Jeffery Park Proposal RequestedFunding ResearcherID $300,000.00 ProposallD Title 1 Large Scale parallel algorithm studies 2 Design vehicle engine 3 Mining boom impact to CQ community 4 Develop and implement new generation distributed database 5 New generation robotics implementation 6 Wheat gene study 7 Efficiency improvement using Bio-fuel for truck engine 8 Neural network approach for traffic video $550,000.00 5 $50,000.00 $700,000.00 $400,000.00 5 $900,000.00 1 $200,000.00 10 $220,000.00 7 Review ProposallD ExpertID Recommendation ReviewDate 7/07/2017 6/06/2017 5/06/2017 5/06/2017 4/06/2017 4/05/2017 1/08/2017 Rejected Accepted 6 E007 7 E002 1 E003 Rejected Accepted Accepted Accepted 2 E002 3 E006 5 E001 4 E001 Expert ExpertiD FirstName LastName ExpertType External Expertise Anthony Baden Frank George Jennifer Computer Science Mechanical Engineering External Computer Science Washington Computer Science E001 Prescott Hoffer North E002 Internal Internal E003 E004 Mechanical Engineering Internal Secondary Education Biology Harris E005 Larrisa Hanson Internal E006 Waden Johnson Internal E007 Note that: You are asked to provide a general solution to each request. If the database contents change, each of your queries should continue to answer the information requested correctly. Simple queries are preferred; if your queries are unnecessarily complex you may lose marks For the given sample data, your queries should be able to generate the same data and column names as shown in the result table for each request You are not required to sort the results in any order unless requested State any assumptions that you make to clarify your understanding of the information request Question 1 2 Marks How many proposals did each expert review? Show the expert's ID, name, expertise and the number of proposal reviewed. Order the result by expert's first name ExpertiD FirstName LastName Anthony Baden Frank NbrProposalReviewed Expertise Prescott Computer Science Mechanical Engineering |Computer Science Secondary Education E001 2 Hoffer 2 EO02 North 1 E003 Hanson E006 Larrisa 1 Johnson Biology Waden E007 1 Question 2 2 Marks Show the details of researchers who have never submitted a proposal ResearcherID FirstName LastName London Expertise Fibre communications 3 Jack 4Joshua Peterson Power System 6 Frank Algorithm and implementation Arnott Question 3 2 Marks Show details of proposals that have been reviewed but don't have any recommendation RequestedFunding ResearcherID $300,000.00 ProposallD Title 1 Large Scale parallel algorithm studies 2 Question 4 2 Marks Show the researcher's name and expertise as well as the requested funding, whose proposal has the highest amount of requested funding among all proposals. FirstName LastName| Expertise RequestedFunding Cell theory $900,000.00 Smith John 2 Marks Question 5 Show the experts' names and number of proposals reviewed by them, who reviewed more than one proposal FirstName LastName NbrProposalReviewed Anthony Prescott Hoffer 2 Baden 2 Question 1 2 Marks Briefly explain the entity integrity rule. Question 2 2 Marks Define the concept of functional dependency. Give an example of two attributes that have a functional dependency 2 Marks Question 3 Describe your understanding about the 4 features of data warehouse 2 Marks Question 4 Briefly discuss the two important goals of a distributed database: Local autonomy and Location transparency Question 5 2 Marks Define the concept of transaction in terms of database terminology. What is the atomicity of a transaction? Question 6 2 Marks Explain the difference between a database administrator and a data administrator