Relation Schema Solution
a) First normal form (1NF): -
Relational Schema: -
Dependency Diagram: -
Tables: -
This table in first normal form, because no repeating groups are there. But this table contains partial and transitive dependency.
|
INV_NUM (PK) |
PROD_NUM (PK) |
SALE_DATE |
PROD_LABEL |
VEND_CODE |
VEND_NAME |
QUANT_SOLD |
PROD_PRICE |
|
211347 |
AA-E3422QW |
15-jan-2016 |
Rotary sander |
211 |
NeverFail, Inc. |
1 |
$49.95 |
|
211347 |
QD-300932X |
15-jan-2016 |
0.25-in.drill bit |
211 |
NeverFail, Inc. |
8 |
$3.45 |
|
211347 |
RU-995748G |
15-jan-2016 |
Band saw |
309 |
BeGood, Inc. |
1 |
$39.99 |
|
211348 |
AA-E3422QW |
15-jan-2016 |
Rotary sander |
211 |
NeverFail Inc. |
2 |
$49.95 |
|
211349 |
GH-778345P |
16-jan-2016 |
Power drill |
157 |
ToughGo Inc. |
1 |
$87.75 |
b) Second Normal form (2NF): -
Relational Schema: -
Dependency Diagram: -
Tables: -
I remove the partial dependency from the given table. So that now this table in second normal form and also contains transitive dependency.
INVOICE
INV_NUM (PK) | SALE_DATE |
211347 | 15-jan-2016 |
|
211348 |
15-jan-2016 |
|
211349 |
16-jan-2016 |
LINE
|
INV_NUM (PK, FK) |
PROD_NUM (PK, FK) |
QUANT_SOLD |
|
211347 |
AA-E3422QW |
1 |
|
211347 |
QD-300932X |
8 |
|
211347 |
RU-995748G |
1 |
|
211348 |
AA-E3422QW |
2 |
|
211349 |
GH-778345P |
1 |
PRODUCT_VENDER
|
PROD_NUM (PK) |
PROD_LABEL |
VEND_CODE |
VEND_NAME |
PROD_PRICE |
|
AA-E3422QW |
Rotary sander |
211 |
NeverFail, Inc. |
$49.95 |
|
QD-300932X |
0.25-in.drill bit |
211 |
NeverFail, Inc. |
$3.45 |
|
RU-995748G |
Band saw |
309 |
BeGood, Inc. |
$39.99 |
|
GH-778345P |
Power drill |
157 |
ToughGo Inc. |
$87.75 |
c) Third normal form (3NF): -
Relational Schema: -
Dependency Diagram: -
Tables: -
I remove the transitive dependency. So that these tables in third normal form.
INVOICE
|
INV_NUM (PK) |
SALE_DATE |
|
211347 |
15-jan-2016 |
|
211348 |
15-jan-2016 |
|
211349 |
16-jan-2016 |
LINE
|
INV_NUM (PK, FK) |
PROD_NUM (PK, FK) |
QUANT_SOLD |
|
211347 |
AA-E3422QW |
1 |
|
211347 |
QD-300932X |
8 |
|
211347 |
RU-995748G |
1 |
|
211348 |
AA-E3422QW |
2 |
|
211349 |
GH-778345P |
1 |
PRODUCT
|
PROD_NUM (PK) |
PROD_LABEL |
VEND_CODE (FK) |
PROD_PRICE |
|
AA-E3422QW |
Rotary sander |
211 |
$49.95 |
|
QD-300932X |
0.25-in.drill bit |
211 |
$3.45 |
|
RU-995748G |
Band saw |
309 |
$39.99 |
|
GH-778345P |
Power drill |
157 |
$87.75 |
VENDOR
|
VEND_CODE (PK) |
VEND_NAME |
|
211 |
NeverFail, Inc. |
|
309 |
BeGood, Inc. |
|
157 |
ToughGo Inc. |
d) Entity Relationship Diagram (ERD): -
