Using MySQL Workbench Data Modeler, create three (3) models from the tables belo
ID: 3727842 • Letter: U
Question
Using MySQL Workbench Data Modeler, create three (3) models from the tables below. Show Table 1 in 3rd Normal Form. Show Table 2 in 3rd Normal Form. Merge the 3rd Normal Forms of Table 1 and Table 2 into a single 3rd Normal Form model. Note that you only can model table and column names and data types. The data shown is for your reference to determine functional, partial, and transitive dependencies.
Table 1 Department Produce Produce Produce ProductCode AisleNumber Price UnitofMeasure 4081 4027 4108 0.90 lea 1.99 lb Butcher Butcher Butcher 331100 331105 332110 1.50 lb 2.40 lb 5.00 lb Freezer Freezer Freezer Freezer 411100 521101 866503 866504 1.00 lea 1.00 lea 5.00 ea 5.00 ea Table 2 Cost ier Product Marku Price DeptCode 108 -tomatoes, 21- Very Veg8ie 1.99 PR 0.35 PR 0.90 PR 2.00 PR 1.50 BU 1.89 Iplum 4081 bananas 027-grapefruit S 851 -cele 31100 chicken 7596 32- Fab Fruits 32-Fab Fruits 32- Fab Fruits 08-Meats R Us 0.45 1.00 0.50 0.60 2.50 0.25 0.25 2.50 300%) $ 400%) $ 100%) $ 400%) $ 400%) $ 100%) $ 100%) $ 331105-lean ground beef 32110-boneless chicken breasts 11100- orange 08- Meats R Us 2.40 BU 5.00 BU 1.00 FR 1.00 FR 5.00 FR 08- Meats R Us 10-Jerry's Juice 10-Jerry's Juice 521101 - apple juice s 5 Icey Creams 5 Icey Creams 866503-vanilla ice cream 866504-chocolate ice cream 2.50 5.00 FRExplanation / Answer
A table is said to be in 3NF if it
Given table 1 can be broken into 3 tables to bring it to the 3NF. The 3 divided tables are Product, Department and ProductDepartment.
Table 1 in 3 NF form.
Table- Product
ProductCode
Price
UnitOfMeasure
4081
$0.35
lb
4027
$0.90
ea
Table- Department
DeptCode
Department
AisleNumber
1
Produce
1
2
Butcher
5
Table- DeptProduct
ProductCode
DeptCode
4081
1
4027
1
Given table 2 can be divided into 4 table to bring it into 3NF. The tables are- Product, Supplier, Department, ProductDetails.
Table 2 in 3NF
Table – Supplier
SupplierCode
Supplier
21
Very Veggie
32
Fab fruits
Table – Product
ProductCode
Product
Cost
Markup
Price
4108
Tomatoes, plum
$1.89
5%
$1.99
4081
bananas
$0.20
75%
$0.35
Table- Department
DeptCode
Department
PR
Produce
BU
Butcher
Table- ProductDetail
DeptCode
ProductCode
SupplierCode
PR
4108
21
PR
4081
32
The normal forms of the given 2 tables can be merged to bring the combined table into 3NF.
The table information is given below.
Merged 3rd Normal Form
Table- Product
ProductCode
Product
Cost
Markup
Price
UnitOfMeasure
4108
Tomatoes, plum
$1.89
5%
$1.99
lb
4081
bananas
$0.20
75%
$0.35
lb
Table – Department
DeptCode
Department
AisleNumber
PR
Produce
1
BU
Butcher
5
Table- Supplier
SupplierCode
Supplier
21
Very Veggie
32
Fab fruits
Table- ProductInfo
DeptCode
ProductCode
SupplierCode
PR
4108
21
PR
4081
32
ProductCode
Price
UnitOfMeasure
4081
$0.35
lb
4027
$0.90
ea
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.