DBS211- Lab 10 Solved

(2NF, 3NF)

Objective:

Students will learn:

·         To continue the normalization of user views from 1NF to 2NF and 3NF

·         How to identify and remove partial dependencies

·         How to identify and remove transitive dependencies


Definitions:

Definition: A relation is in 1NF if it contains no multi-valued dependencies (also known as repeating groups).

Definition: A relation is in 2NF it is in 1NF and it contains no Partial Dependencies.

Definition:  A Partial Dependency occurs when a non-key attribute(s) is dependent on (or is determined by) a part of a composite primary key.

Definition: A relation is in 3NF it is in 2NF and it contains no Transitive Dependencies.

Definition:  A Transitive Dependency occurs when a non-key  attribute (s) is dependent on (or is determined by) another non-key attribute.

 

Instructions:

Part A (Second Normal Form (2NF))

Note: A relation that has only a simple primary key cannot have any partial dependencies!

 

1.         Examine the following report:

 

                                                             Premiere Corporation

Customer Orders

Customer Number
Name
Order Number
Order Date
Sales Rep
Rep Last Name
124
Sally Adams
12489   
2016-09-02
03
Jones
12500   
2016-09-05             
256
Ann Samuels
12495   
2016-09-04
06
Smith
311
Don Charles
12491
2016-09-02
12
Diaz
315
Tom Daniels
12494
2016-09-04
06
Smith
522
Mary   Nelson
12498
2016-09-05   
12
Diaz
12504
2016-09-05
        

                                                                       

Step 1: 

Create the UNF relation by creating a relation composed of all the attributes found in the User View. Don’t forget to underline the primary key and place brackets around any multi-valued dependencies (also known as repeating groups) you may find.

 

UNF:

 

UNF:   Customer [ CK CustNo, CustName, RepNo, RepName,( OrderNo, OrderDate )]

 

____________________________________________________________________________________________

 

Step 2: 

Create the 1NF relations by resolving the multi-valued dependencies (also known as repeating groups):

 

1NF

Now you are ready to create the 2NF relations by resolving the partial dependencies from the 1NF relations.

 

Your 1NF solution should look something like this:


1NF:   Customer [ CK CustNo, CustName, RepNo, RepName ]
          Cust_Order [CK OrderNo, OrderDate, FK CustNo ]

 

 

Note: if you did not get a similar solution, please talk to your instructor about it now! It is very important to get the correct UNF and 1NF relations.

 

Step 3: 

The process for taking a relation from 1NF to 2NF involves resolving the partial dependencies. We see that from our definition of 2NF (page 1) a partial Dependency is when a non-key attribute is determined by a part of the primary key. We also read in the note (page 1) that we cannot have partial dependencies when there is a one-part Primary Key).

 

1NF:   Customer [ CK CustNo, CustName, RepNo, RepName ]
          Cust_Order [CK OrderNo, OrderDate, FK CustNo ]

 

 

Now examine the CustOrder relation.  Does it have a composite primary key ( a key made up of more than 1 field) ?  ____________

 

Identify the key attributes._________________________________

 

Identify the non-key attributes. _____________________________

 

Are any of the non-key attributes determined by ONE of the key attributes? _______________________

 

Which non-key attributes are determined by only one of the PK attributes?

________________________________________.  

 

 

We must create new relations for the partial dependencies.

 

Write the 3 possible PK’s:

 

[CustNo ,

[OrderNo ,

[CustNo, OrderNo , 

 

Place all non-key attributes on the appropriate table (hint: choose the table with the least parts.

 

Second Normal Form 2NF: 

 

CUSTOMER   [ CustNo, 

ORDER          [ OrderNo                                                       

CUSTORDER [ CustNo, OrderNo, 

 

1NF:   Customer [ CK CustNo, CustName, RepNo, RepName ]
          Cust_Order [CK OrderNo, OrderDate, FK CustNo ]

 

 

 

Part B (Third Normal Form (3NF))

 

We now have a set of 2NF relations from our User View.  Your 2NF solution should look something like this:

 

2NF:                                                                       

Customer [ CustNo, CustName,  RepNo, RepName ]

CustOrder [ CustNo, OrderNo ]

Order [ OrderNo, Orderdate ]

 

If you did not correctly identify the order relation, please ask your instructor about this process now!

 

We are now ready to identify any transitive dependencies we may have.

 

Note: A relation that has no transitive dependencies is already in 3NF!

 

1.         Examine each of the 2NF relations and determine the following:

 

Customer relation:        Key attributes ___________________________________

                                                                                                                                                                 

Non-key attributes: ________________________________

 

CustOrder relation        Key attributes ___________________________________

 

                                    Non-key attributes ___________________________________

 

 

Order relation               Key attributes ____________________________________

 

                                    Non-key attributes: ___________________________________

 

Note: if a relation contains less than 2 non-key attributes, there cannot be any transitive dependencies. Therefore the CustOrder and Order relations cannot contain any transitive dependencies! Simply copy those relations to the 3NF solution.

 

Examine non-key attributes of the Customer relation.  Do any of the non-key attributes determine any of the other non-key attributes? __________

If you answered yes, you are right.  Fill in the blanks:

 

____________________________   is determined by ________________

 

 

 

2.         We must create a new relation for the transitive dependency.  We do this by moving the non-key attributes involved in the transitive dependency to a new relation. The primary key of the new relation will be the non-key attribute that determines the other non-key attributes involved in the transitive dependency. 

 

Write the DBDL for the new relation:

 

REP [                                                                                 ]

 

3.         The last step in resolving the transitive dependency is to maintain the link (or relationship) between the relation that contained the transitive dependency (Customer) and the new relation (Rep).  We do this by placing a foreign key to the new relation (Rep)  into the relation that contained the transitive dependency (Customer). The foreign key will be the primary key of the new relation. Don’t forget to identify it with (FK)

 

 

Complete the 3NF solution:

 

3NF:                                                           

                                                                                                                                                            

CUSTORDER [ CustNo, OrderNo ]

ORDER [ OrderNo, Orderdate ]

CUSTOMER [CustNo, CustName,                     ] (fill in the foreign key)

REP [                                                   ]              


3NF:   Customer             [ CK CustNo, CustName, FK RepNo ]
          Cust_Order           [ CK OrderNo, OrderDate, FK CustNo ]
          Reps                     [ CK RepNo, RepName ]


 

Last Section:

 

Lab 10 Submission:

 

For the following User View, determine the 1, 2 and 3NF and hand in this page only to your instructor. The UNF relation has been provided.

 

Premiere Corporation

Order Detail Report

 

Order Number
Order Date
Cust Number
Cust Last Name
Part Number
Part Desc
Qnty Ordered
Quoted Price
12489  
2016-09-02  124
124
Adams
AX12
Iron
11
14.95
12491  
2016-09-02  311
311
Charles
BT04
GasGrill
3
440.00
BZ66
Washer
1
399.99
CX11
MiniBlender
1
11.98
12494  
2016-09-04  
315
Daniels
CB03  
Bike
4
279.96
12495  
2016-09-04
256
Samuels
CX11  
MiniBlender
2
23.96
12498  
2016-09-05
522
Nelson
AZ52  
Dartboard
2
12.96
BA74  
Basketbal
4
24.96
12500  
2016-09-05  
124
Adams
BT04
GasGrill
1
149.99
12504  
2016-09-05
522
Nelson
CZ81
Treadmill
2
325.98   
UNF:

Order [CK OrderNo, Orderdate, CustNo, CustLname, (PartNo, PartDesc, QtyOrd, Price)]

 

1NF:

Order [OrderNo, Orderdate, FK CustNo, CustLname]

Parts [PartNo, FK OrderNo, PartDesc, QtyOrd, Price]

 

2NF:

Order [OrderNo, Orderdate]

CustOrder [FK CustNo, FK OrderNo]

Customer [CustNo, CustLname]

Parts [PartNo, PartDesc, Price]

PartOrder [FK OrderNo, FK PartNo, QtyOrd]

 

3NF:

Order [OrderNo, Orderdate]

CustOrder [FK CustNo, FK OrderNo]

Customer [CustNo, CustLname]

PartOrder [FK OrderNo, FK PartNo, QtyOrd]

Parts [PartNo, FK PartDesc]

PricePart [PartDesc, Price]