In this assignment, please examine the data table below and transform it into 3NF. Please complete...
Fantastic news! We've Found the answer you've been seeking!
Question:
Transcribed Image Text:
In this assignment, please examine the data table below and transform it into 3NF. Please complete this assignment in attached Answer Sheet. Imagine the following scenario: The SHIPMENTS table for a car part company captures the data about car shipments Each shipment has a unique Shipment ID and a Shipment Date Each shipment is shipped on one truck Each truck has a unique Truck ID, and a Truck Type Each shipment can ship multiple products Each product has a unique Product ID and a Product Type Each quantity of a product shipped in a shipment is recorded in a table in the column Quantity SHIPMENTS TABLE ShipmentID ShipmentDate TruckID TruckType ProductID ProductType Quantity 111 1-Jan T-111 Semi-Trailer A Tire 180 111 1-Jan T-111 Semi-Trailer B Battery 120 222 2-Jan T-222 Van Truck C SparkPlug 10,000 333 3-Jan T-333 Semi-Trailer D Wipers 5,000 333 3-Jan T-333 Semi-Trailer A Tire 200 444 3-Jan T-222 Van Truck C SparkPlug 25,000 555 3-Jan T-111 Semi-Trailer B Battery 180 a) Does the table above meet the definition of 1NF? What is the primary key? b) Depict full key functional dependencies, partial functional dependencies (if any), and transitive functional dependencies (if any) in the SHIPMENTS table c) Normalize the SHIPMENTS Table to 2NF and show the resulting tables (with functional dependencies) d) Normalize the SHIPMENTS Table to 3NF and show the resulting tables (with functional dependencies) e) Draw a relational schema for the resulting tables, after the tables are in 3NF *Note: For questions b-d, you can use WORD to draw the tables with functional dependencies For question e, please use ERD Plus to draw a relational schema, and export and copy the image into your answer sheet. In this assignment, please examine the data table below and transform it into 3NF. Please complete this assignment in attached Answer Sheet. Imagine the following scenario: The SHIPMENTS table for a car part company captures the data about car shipments Each shipment has a unique Shipment ID and a Shipment Date Each shipment is shipped on one truck Each truck has a unique Truck ID, and a Truck Type Each shipment can ship multiple products Each product has a unique Product ID and a Product Type Each quantity of a product shipped in a shipment is recorded in a table in the column Quantity SHIPMENTS TABLE ShipmentID ShipmentDate TruckID TruckType ProductID ProductType Quantity 111 1-Jan T-111 Semi-Trailer A Tire 180 111 1-Jan T-111 Semi-Trailer B Battery 120 222 2-Jan T-222 Van Truck C SparkPlug 10,000 333 3-Jan T-333 Semi-Trailer D Wipers 5,000 333 3-Jan T-333 Semi-Trailer A Tire 200 444 3-Jan T-222 Van Truck C SparkPlug 25,000 555 3-Jan T-111 Semi-Trailer B Battery 180 a) Does the table above meet the definition of 1NF? What is the primary key? b) Depict full key functional dependencies, partial functional dependencies (if any), and transitive functional dependencies (if any) in the SHIPMENTS table c) Normalize the SHIPMENTS Table to 2NF and show the resulting tables (with functional dependencies) d) Normalize the SHIPMENTS Table to 3NF and show the resulting tables (with functional dependencies) e) Draw a relational schema for the resulting tables, after the tables are in 3NF *Note: For questions b-d, you can use WORD to draw the tables with functional dependencies For question e, please use ERD Plus to draw a relational schema, and export and copy the image into your answer sheet.
Expert Answer:
Related Book For
Principles of Information Systems
ISBN: 978-0324665284
9th edition
Authors: Ralph M. Stair, George W. Reynolds
Posted Date:
Students also viewed these computer network questions
-
Use traditional ABC analysis (using purchasing value as the benchmark) to identify the top 20% suppliers. Please show calculations used in the answer. FarmCo is a mid-sized manufacturer of farm...
-
Q1. You have identified a market opportunity for home media players that would cater for older members of the population. Many older people have difficulty in understanding the operating principles...
-
4. (8 points) Fill in with =, >, >=,
-
Dolphins emit clicks of sound for communication and echolocation. A marine biologist is monitoring a dolphin swimming in seawater where the speed of sound is 1522 m/s. When the dolphin is swimming...
-
Greish Inc. is preparing its annual budgets for the year ending December 31, 2012. Accounting assistants have provided the following data: An accounting assistant has prepared the detailed...
-
The following reaction takes place in a system consisting of \(3 \mathrm{~mol} \mathrm{CH}_{4}, 5 \mathrm{~mol} \mathrm{H}_{2} \mathrm{O}\), \(1 \mathrm{~mol} \mathrm{CO}\) and \(4 \mathrm{~mol}...
-
A manufacturer of MP3 players surveyed one hundred retail stores in each of the firms sales regions. An analyst noticed that in the South Atlantic region the average retail price was $165 (mean) and...
-
Describe the core principles and objectives of COBIT 2019.Summarize the Policy Development Guide's recommendations for organizing a policy hierarchy and selecting policy topics.t of the lab, you will...
-
Carranza a nonexempt salesperson with Verent Enterprises earns a base annual salary of $ 3 0 , 5 0 0 with a standard 4 0 hour workweek. In addition a 3 percent commission on all sales during the pay...
-
Havana Hats makes the world's best hats. Information for the last eight months follows: Number of Hats Month January Produced Total Cost 1,800 $ 9,000 February 4,890 15,648 March 2,300 10,350 April...
-
Port Company purchased 30,000 of the 100,000 outstanding shares of Sund Company common stock on January 1, 20X2, for $180,000. The purchase price was equal to the book value of the shares purchased....
-
On January 1 of its first month of business, Juan in a Million, Inc., paid $48,000 for four months rent beginning in January. How much will be reported as Rent Expense on its income statement for the...
-
Besides the effect on the tax rates that will apply, filing status also has an impact on the amount of the standard deduction that is allowed. A taxpayer who is unmarried or separated from their...
-
Dave borrowed $600 on January 1, 2022. The bank charged him a $8.00 service charge, and interest was $47.20. If Dave paid the $600 in 12 equal monthly payments, what was the APR? Note: Do not round...
-
Suppose you want to analyze personal consumption expenditures by using income. However, you also believe that personal consumption expenditures might vary by gender (female-male) and marital status...
-
Define deferred revenue. Why is it a liability?
-
Identify the basic components of an expert system and describe the role of each.
-
What does it mean to define the functions of a Web site? What are some of the possible functions?
-
Form a team and debate other teams from your class on the following topic: "Are expert systems superior to human beings when it comes to making objective decisions?" Develop several points supporting...
-
A tapered circular bar is rigidly fixed at both the ends as shown in Fig. 13.45. If the temperature is raised by \(50^{\circ} \mathrm{C}\), calculate the stress in the bar. Take \(E=202...
-
A trapezoidal flat steel plate of thickness \(10 \mathrm{~mm}\) tapers uniformly from a width \(150 \mathrm{~mm}\) to \(100 \mathrm{~mm}\) in a length of \(500 \mathrm{~mm}\). Calculate the extension...
-
On the principal planes, the maximum stress is the (a) normal stress (b) shear stress (c) both normal and shear stress (d) either normal or shear stress
Study smarter with the SolutionInn App