Problem II - (20 points) Use the process of normalization to transform the table
ID: 3834341 • Letter: P
Question
Problem II - (20 points)
Use the process of normalization to transform the table below into 3NF. Show the tables in 1NF and 2NF that are created in the process.
Owner ID
Owner Name
Owner Address
Owner Automobiles(s)
100
Smith
Salt Lake City
Ford, Honda
101
Jones
Phoenix
Ford
102
Lee
Denver
Toyota
104
Smith
Salt Lake City
Chevrolet
Problem III - (15 points)
Draw an entity-relationship diagram for the following situation: A commercial bakery makes many different products. These products include breads, desserts, specialty cakes, and many other baked goods. Ingredients such as flour, spices, and milk are purchased from vendors. Sometimes an ingredient is purchased from a single vendor, and other times an ingredient is purchased from many vendors. The bakery has commercial customers, such as schools and restaurants, that regularly place orders for baked goods. Each baked good has a specialist that oversees the setup of the bakery operation and inspects the finished product.
Problem IV - (20 points)
Interglobal Paper Company has asked for your help in comparing its present computer system with a new one its board of directors would like to see implemented. Proposed system and present system costs are as follows:
Year Proposed System Costs Present System Costs
Year 1
Equipment Lease $20,000 $21,500
Salaries 30,000 50,000
Overhead 4,000 13,000
Development 30,000 —
Year 2
Equipment Lease $20,000 $10,500
Salaries 23,000 55,000
Overhead 4,400 3,300
Development 12,000 —
Year 3
Equipment Lease $20,000 $10,500
Salaries 36,000 60,000
Overhead 4,900 3,600
Development — —
Year 4
Equipment Lease $20,000 $10,500
Salaries 39,000 66,000
Overhead 5,500 4,000
Development — —
a. Using break-even analysis, determine the year in which Interglobal Paper will break even.
b. Graph the costs and show the break-even point.
Owner ID
Owner Name
Owner Address
Owner Automobiles(s)
100
Smith
Salt Lake City
Ford, Honda
101
Jones
Phoenix
Ford
102
Lee
Denver
Toyota
104
Smith
Salt Lake City
Chevrolet
Explanation / Answer
Given table is:
Owner ID
Owner Name
Owner Address
Owner Automobiles(s)
100
Smith
Salt Lake City
Ford, Honda
101
Jones
Phoenix
Ford
102
Lee
Denver
Toyota
104
Smith
Salt Lake City
Chevrolet
Database Normalisation is a process of organizing the data in the database. It is a systematic approach that is used to decompos tables to eliminate data redundancy and , insertion , update & deletion anomalies
1NF:
As per First Normal Form, in a table no two Rows of data must contain repeating group of information. That means each set of column must have a unique value.
BY considering given table owner ID 100 contains two Automobiles.
Owner ID
Owner Name
Owner Address
Owner Automobiles(s)
100
Smith
Salt Lake City
Ford
100
Smith
Salt Lake City
Honda
101
Jones
Phoenix
Ford
102
Lee
Denver
Toyota
104
Smith
Salt Lake City
Chevrolet
2NF:
· Table should be in First normal form.
· No non-prime attribute is dependent on the proper subset of any candidate key of table.
By converting into 2NF the resulting table is as follows.
Owner ID
Owner Name
Owner Address
100
Smith
Salt Lake City
100
Smith
Salt Lake City
101
Jones
Phoenix
102
Lee
Denver
104
Smith
Salt Lake City
Owner ID
Owner Automobiles(s)
100
Ford
100
Honda
101
Ford
102
Toyota
104
Chevrolet
3NF:
· able must be in 2NF
· If there are any transitive functional dependencies of non-prime attribute on any super key, then they should be removed.
The above table satisfying 3NF also.
Owner ID
Owner Name
Owner Address
100
Smith
Salt Lake City
100
Smith
Salt Lake City
101
Jones
Phoenix
102
Lee
Denver
104
Smith
Salt Lake City
Owner ID
Owner Automobiles(s)
100
Ford
100
Honda
101
Ford
102
Toyota
104
Chevrolet
Owner ID
Owner Name
Owner Address
Owner Automobiles(s)
100
Smith
Salt Lake City
Ford, Honda
101
Jones
Phoenix
Ford
102
Lee
Denver
Toyota
104
Smith
Salt Lake City
Chevrolet
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.