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...
-
A company uses a process cost accounting system and the weighted average method for inventory costs. The following information is available regarding direct labour for the current year: Goods in...
-
If a subordinate came to you and said that they felt the company's new diversity hiring initiative was unfair and would compromise their well-deserved opportunities for advancement, how would you...
-
This problem helps you develop skill in recording transactions by using a companys actual account titles. Refer to the Amazon.com financial statements in Appendix A. Assume that Amazon.com completed...
-
During 2011, Company P sold $50,000 of goods to subsidiary Company S at a profit of $12,000. One-fourth of the goods remain unsold at year-end. What specific adjustments are needed on the...
-
Create trigger CHECK_MEMBERSHIP_UPDATE ON MEMBERSHIP BEFORE UPDATE on MEMBERSHIP for each row DECLARE MembershipID varchar(20); FirstName varchar(20); LastName varchar(20); MembershipLevel...
-
Journalize all these transactions into a general ledger. Transactions May 1 Purchased merchandise from Keys Wholesale on account for $735.30, Invoice No 91 Dated May 1, terms 2/10, n30 May 2...
-
The use of lysozyme during the preparation of cheeses and wines is effective in reducing the numbers of a) fungi. b) viruses. c) bacteria. d) prions. e) all microbes.
-
A machine costs $1,000 and has a 3-year life. The estimated salvage value at the end of three years is $100. The project is expected to generate after tax-cash flows of $600 per year. If the required...
-
At the close of 2 0 2 3 Smith Company had $ 7 2 6 , 4 2 2 in retained earnings and reported $ 6 5 , 2 0 1 in net loss. In 2 0 2 2 the company paid $ 1 4 4 , 9 8 8 in dividends and had $ 9 5 6 , 2 1 2...
-
Fanning Training Services ( FTS ) provides instruction on the use of computer software for the employees of its corporate clients. It offers courses in the clients offices on the clients equipment....
-
Discuss pleadings (complaints and reply) and discuss the importance of these documents in litigation process and discuss ethical issues when paralegals prepare legal documents for litigation.
-
You have been given the task of Auditing your Company s client files. How would you check whether each of the Disclosure Documents has been recorded and stored in accordance with the organisation s...
-
Name four characteristics of the natural environment, with at least one each from the air, ground, and space environments, which could affect the propagation of an electromagnetic signal from the...
-
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.
-
List the modifications of the standard audit report that normally do not result in a qualification, a disclaimer, or an adverse opinion.
-
When more than one auditor is involved in an audit of a company's financial statements, what two decisions about reporting must the principal auditor make?
-
What disclosure is made in the principal auditors' report if they decide to assume responsibility for other auditors' work? If they decide not to assume responsibility for other auditors' work?
Study smarter with the SolutionInn App