The purpose of this question is to allow learner to apply the knowledge of creating relational...
Fantastic news! We've Found the answer you've been seeking!
Question:
Transcribed Image Text:
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] 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]
Expert Answer:
Answer rating: 100% (QA)
Question 1 a A family tree can be represented by a relational database in a number of ways One way is to create a table for each generation of the family with each row representing a person in that ge... View the full answer
Related Book For
Data Modeling and Database Design
ISBN: 978-1285085258
2nd edition
Authors: Narayan S. Umanath, Richard W. Scammel
Posted Date:
Students also viewed these programming questions
-
Case Study: Quick Fix Dental Practice Technology requirements Application must be built using Visual Studio 2019 or Visual Studio 2017, professional or enterprise. The community edition is not...
-
The purpose of this assignment is to identify and apply Operations and Supply Chain Management concepts/tools to solve operational problems and improve operational performance. To this purpose, you...
-
Write the entities and attributes for the ERD diagram entities must be 10 or more and attributes must be At least 6 This assignment is part of our Database management system course, and the purpose...
-
A companys retained earnings on December 31, 2018 was $2,190,000 and its shareholders equity was $8,760,000. During 2019 the company reported the following: Net income $225,000 A sale of treasury...
-
Investors have shown great interest in the stock of Rollover Beds Corporation, because the company has been growing at an average annual rate equal to 25 percent. Jason Jackson decided to evaluate...
-
Black cars are for-hire vehicles that provide ground transportation by prearrangement with customers. Black-car drivers rent or purchase their franchises directly from franchisors. The franchise...
-
Ajax Savings Bank has certificate of deposit notes with a face value of $\$ 10,000$ with a $10 \%$ interest rate compounded daily. What is the current market price of the $\mathrm{CD}$ if the term of...
-
Niner Bikes, as discussed in the chapter opener, uses a costing system with standard costs for direct materials, direct labor, and overhead costs. Two comments frequently are mentioned in relation to...
-
1) Discuss the concepts of specific risk and market risk in investment 2) Explain how portfolio diversification reduces investment risk 3) Create a hypothetical example of a 3-stock portfolio, select...
-
Cardinal Company is considering a five-year project that would require a $2,975,000 investment in equipment with a useful life of five years and no salvage value . The company's discount rate is 14%....
-
a) Write instructions to perform the following operations: [0.5] (i) Move the content of Accumulator to register 7(R7). (ii) Move the contents of RAM memory location 55H to Port 1 (iii) Receive...
-
What is a pipeline bubble and why might a branch instruction introduce one or more bubbles? [4 marks] (b) Explain, with the aid of an example, how conditional instructions may be used to reduce the...
-
Consider the following perfect-substitutes utility function U(x, x)=X+X2. When p=$2, P2=$8 and consumer income is $22, what is the slope of the Engel curve for x? Round your answer to the nearest...
-
Your newest venture idea is a Hyperloop service between Washington DC and Baltimore. The Hyperloop trains will offer customers a choice between coach and first-class tickets. For the venture to...
-
Raspberry Jam Strawberry Jam Raspberry Pints Strawberry Quarts Unit sales over the past year were downloaded from Lina's database and are in the Excel sheet "Sales", as are the prices. Additionally,...
-
QUESTION 1(20 Marks) SUNNY EXPRESS TRAIN which you are working for has tasked you to write a negative letter declining a customer's request for a refund. Using the following template write a negative...
-
Do some online research on Sixth Sense Technology to discover why it has not become a widely known or used technology.
-
Arlington Merchants reported the following on its income statement for the fiscal years ending December 31, 2016 and 2015. 2016 2015 Sales $4,857,500 $4,752,900 Cost of goods sold 3,258,950 3,207,000...
-
Why can total participation of an entity type in a relationship type also be referred to as existence dependency of that entity type in that relationship type?
-
The series of tasks in this exercise is based on the following ER diagram and its associated logical schema. a. Write appropriate CREATE TABLE statements for the logical schema. Be sure to define all...
-
The weak relationship type shown in Figure 5.32 requires further decomposition preparatory to mapping to a logical schema. Develop the final form of the Design-Specific ERD.
-
In problem 49, when does the estate's taxable year end? problem 49, John Henry died on May 1, 2018. When does his final 1040 have to be filed?
-
John Henry died on May 1, 2018. When does his final 1040 have to be filed?
-
John and Mary Hughes file their income tax return on a calendar year basis. John dies on May 15, 2018. Mary remarries on July 4, 2018. Can Mary file a joint return with John for the taxable year 2018?
Study smarter with the SolutionInn App