The purpose of this question is to allow learner to apply the knowledge of creating relational schema and ER/EER diagram, based on a given scenario. a. b. A family tree represents the structure of a family. Show how the information of a family tree can be represented by means of a relational database, possibly starting with a simplified structure in which only the male line or only the female line is represented. Figure 1 shows an example of a family tree. Ishak b. Abdul Rahman & Zuria bt Alwi Suhaimi b. Ishak Abdul Rahman b. Ahmad & Maimunah bt. All Nadia bt. Ishak Jamilah bt. Abdul Rahman & Sanusi b. Abdullah Junaidah bt. Ishak Figure 1 Jamaludin b. Abdul Rahman & Suhaila bt Mohd Mohd. Zubair b Jamaludin Liana b Jamaludin [10 marks] Figure 2 illustrates an ill structured and incomplete ER diagram. Several entities shown in Figure 2 have attributes that are really pointers to other entities and which could be replaced with relationships. A person may have up to three companies as employers. Each person has an ID. Cars may be owned by persons, companies, or banks. Car owner ID is the ID of the person, company, or bank who owns the car. A car loan may be involved in the purchase of the car. Thoroughly analyze the diagram and the previous brief description. Construct a complete ER/EER model together with relationships and multiplicities/integrity constraints. Identify the suitable primary key for each entity. You may need to add one or more entities of your own. (Hint. Consider using the generalization/specialization relationship concept.) Person name age employer 1_ID employer2_ID employer3_ID personID address Car ownerlD vehicleID ownerType model year Figure 2 Car Loan vehiclelD customerType customerlD accountNo bankID interestRate currentBalance Bank name bankID Company name companyID [10 marks] QUESTION 2 The purpose of this question is to assess learner's understanding on normalization, and to test the skills in writing SQL statement. a. Consider the relation shown in Figure 3, which represents information on the products of a carpentry firm and their components. The following are given: the type of component of a product (attribute Type), the quantity of the component necessary for a certain product (attribute Quantity), the unit price of the component of a certain product (attribute PriceOfC), the supplier of the component (attribute Supplier) and the total price of the single product (attribute PriceOfP). Product Component Bookcase Wood Bookcase Screw Bookcase Glass Wood Screw Seat Wood Seat Seat Seat Desk Desk Table Handle Wood Type Walnut B212 Crystal Oak B212 B412 Walnut H621 Walnut Quantity PriceOfC Supplier PriceOfP 10.00 Salleh 0.10 Bakar 5.00 Johan 15.00 Salleh 0.10 Bakar 5 200 3 5 250 150 10 10 4 0.30 Bakar 8.00 Kuntum 20.00 Bakar 10.00 Salleh Figure 3 i) Identify the functional dependencies and the key(s) for this relation. ii) With reference to the relation in Figure 3, consider the following update operations: insertion of a new product; deletion of a product; addition of a component in a product; modification of the price of a product. 400 400 400 300 300 300 250 250 200 [5 marks] Discuss the types of anomaly that can be caused by these operations. [5 marks] iii) Based upon the functional dependencies and keys specified in (a), normalized the relation of Figure 3 into BCNF. [10 marks] b. Employee (empID, name, address, DOB, deptNo) Department (deptNo, deptName, manager EmpID) Projects (proj No, projName, deptNo) Works-On (empID, projNo, hoursWorked) where Employee contains employee details and empID is the key. Department a. b. C. d. The following tables form part of a database held in a Relational Database Management System:- e. Write the SQL statement to: f. Projects and Works-on contains department details and dept No is the key. managerEmpID identifies the employee who is the manager of the department. There is only one manager for each department. contains details of the projects in each department and the key is projNo (no two departments can run the same project). contains details of the hours worked by employees on each project, and empID/projNo form the key. List all employees in alphabetical order of name and DOB. [2 marks] Produce a list of the names and addresses of all employees who work for the "IT" department. [2 marks] Assuming that the retirement age is 65, produce a complete list of all managers who are due to retire this year, in alphabetical order of name. Find out how many employees are managed by "Muhammad Farhan". [3 marks] [3 marks] Produce a report of the total hours worked by each employee, arranged in order of department number and within department, alphabetically by employee name. [4 marks] For each project on which more than two employees worked; list the project number, project name and the number of employees who work on that project. 5 [4 marks] g. A new project with number PR999 under the supervision of empID E100 and with total hours work of 40 has been approved. Write the SQL statement to illustrate this changes. [2 marks] [TOTAL: 60 MARKS]