Posted: September 18th, 2022

DBMS Assign 2

You are to complete the normalization exercise detailed in the attached document.  

When normalizing the table you may only use the given columns.  You may not create new fields or surrogate keys.

Submit your response as a Word document ( x) via the title link.  Name the file “Normalization”.

I’m Attaching some of the examples for this one can please go through it and find the solution.

Normalization Exercise

BIS

6

3

8 – Database Management for Business Systems

The following table shows a partial list of appointments for a pet grooming salon:

1265

9/10/03

9:00 AM

75

989-687-2432

Jack

9/10/03

3

9/10/03

1267

9/10/03

10:00 AM

51

989-721-3487

Winston

5

105

Nail Trimming

9/10/03

Jack

100

Bath

GroomingAppointment

ApptID

ApptDate

ApptTime

CustID

CustPhone

PetName

TechID

ServiceID

ServiceName

1

2

6

5

9/10/03

9:00 AM

75

989-687-

24

32

Jack

6

100

Bath

3

105

Nail Trimming

1266

9:30 AM

24

989-655-7448

Bailey

112

Teeth Brushing

1267

10:00 AM

51

989-721-3487

Winston

5

101

Haircut

1268

11:00 AM

63

989-248-5821

2

Field Descriptions:

ApptID – ID number of the appointment

ApptDate – date of the appointment

ApptTime – time of the appointment

CustID – ID number of the customer

CustPhone – phone number of the customer

PetName – name of the pet being groomed

TechID – ID number of the technician performing the grooming service

ServiceID – ID number of the grooming service being performed (an appointment can include multiple services)

ServiceName – name of grooming service being performed

1. Use arrows to show the functional dependencies in the table.

2. Convert the table to 3NF.

Key Terms and Normalization

1

Key Terms

A key is a column, or combination of columns, that uniquely identifies the rows in a relation.
If the key consists of two, or more, columns it is called a composite key.

Your student number and your globalID are examples of keys that the University uses to
identify you as a student.

Keys come in a variety of types, including:

 candidate key

 primary key

 foreign key

 surrogate key

A candidate key is a key that determines all of the other columns in a relation. A relation can
potentially have a number of candidate keys. Let’s consider a typical student relation:

Student (StudentNbr, FirstName, LastName, DOB, Major, Email, Phone, Address, City, State, ZIP)

In this relation, the StudentNbr and Email columns hold values that are unique to a given
student. Both of these columns could, therefore, be used to functionally determine all other
columns in the relation. As such, these columns are considered candidate keys.

A primary key is a candidate key selected as the primary means of identifying rows in a relation.
In the Student relation above, the StudentNbr and GlobalID columns are both candidate keys.
You would need to select one of the candidates to serve as the primary key. In this case,
StudentNbr would probably be selected because it is short and numeric. When writing a
relation, the primary key in underlined:

Student (StudentNbr, FirstName, LastName, DOB, Major, Email, Phone, Address, City, State, ZIP)

A foreign key is the primary key of one relation that is added to another relation to form a link
between the relations. As an example, let’s consider two relations: Branch and Book. Since
each book belongs to a specific branch, they share a relationship. We can enforce this
relationship by adding the primary key of Branch (BranchID) to Book. This makes BranchID a
foreign key in Book. When writing a relation, any foreign keys are noted with a dashed
underline:

Branch (BranchNbr, BranchName, Address, City, State, ZIP, Phone)
Book (BookNbr, Title, Author, ShelfLocation, BranchNbr)

A surrogate key is an artificial column added to a relation to serve as a primary key. Surrogate
keys are useful when there are no good candidate keys to serve as a relation’s primary key. As
an example, suppose you had an employee relation that looked like this:

Key Terms and Normalization

2

Employee (FirstName, LastName, DateHired, JobTitle)

Since many employees might have the same first name, the same last name, or both, the name
columns are not good candidates for the primary key (e.g. Robert Miller is a very common
name – I’ve known more than a few in my life). Likewise, DateHired and JobTitle are not good
candidates because multiple employees many have been hired on the same day or have the
same job title. You would, therefore, need to use multiple columns to create a candidate for
the primary key. One possibility might be FirstName, LastName, and DateHired. Since the odds
that two, or more, employees with the same first and last name would be hired on the same
day is low, this combination of columns is a workable candidate for primary key.

Employee (FirstName, LastName, DateHired, JobTitle)

Although FirstName, LastName, DateHired creates a unique combination and a workable
candidate for primary key, it is not very efficient. There is also a very small chance that two
employees with the same name could be hired on the same day (given a large enough company
– and a very common name). This would cause problems because duplicate primary keys are
not allowed. For these reasons, a surrogate key would be a better choice.

Instead of using three columns to create the primary key, an artificial column could be added
called EmployeeNbr. This column would hold a sequential number so no two employees would
ever have the same EmployeeNbr. Since the value in EmployeeNbr is unique, it can serve as the
primary key all by itself. This creates a simpler relation and makes the database more efficient.

Employee (EmployeeNbr, FirstName, LastName, DateHired, JobTitle)

Key Terms and Normalization

3

Normalization

This week’s presentation focused on the concepts of well-structured relations and functional
dependencies. We will now cover how understanding functional dependencies can lead to
well-structured relations through the process of normalization.

Normalization is the process of decomposing relations with anomalies to produce smaller, well-
structured relations. As a process, normalization has steps, or normal forms. Although there
are at least six normal forms, we will concentrate on the first three in this course.
Normalization is a progressive process so each normal form builds on the one before. Said
another way you cannot reach the third normal form without satisfying the requirements for
forms one and two first.

Normal Forms

A table is in first normal form (1NF) if it meets the requirements of a relation. As discussed in
this week’s presentation, a table must have six properties to be considered a relation:

 columns must have unique names

 all values in a column must come from the same domain

 each row must be unique

 there can be no multi-valued attributes

 the order of the rows is irrelevant

 the order of the columns is irrelevant

A relation is in second normal form (2NF) if it is in 1NF and all partial functional dependencies
have been removed. A partial functional dependency exists when a non-key attribute is
functionally dependent on part of, but not all of, the primary key. It should be noted that
partial functional dependencies are only possible when the primary key is composite.

A relation is in third normal form (3NF) if it is in 2NF and all transitive functional dependencies
have been removed. A transitive functional dependency exists when a non-key attribute is
functionally dependent on another non-key attribute.

Normalization Examples

The easiest way to understand normalization is to work through the process systematically. We
can do that with a couple of examples from this week’s presentation.

The first example involves a table that records SKU data. The table has the following columns:

 SKU – stock keeping unit, the number of the item

 SKUDescription – the description of the item

 Department – the department where the item is sold

 BuyerID – the employee who buys the item for the department

Key Terms and Normalization

4

Start the normalization process by identifying the functional dependencies. From the
presentation, we know that the table has two functional dependencies:

We can use the functional dependencies to create two new tables:

SKUData (SKU, SKUDescription, Department, BuyerID)
BuyerData (BuyerID, Department)

For both tables, we use the determinant of the functional dependency as the primary key. This
means that the primary key of the SKUData table is SKU, while the primary key of the
BuyerData table is BuyerID.

Key Terms and Normalization

5

Having created tables based on the functional dependencies, we begin checking the normal
forms of each table.

Are the tables in 1NF?

1NF requires that the tables have the six properties of a relation:

Relation Properties SKUData BuyerData
Columns must have unique names Yes Yes
Values in a column must come from the same domain Yes Yes
Each row must be unique Yes Yes
There can be no multi-valued attributes Yes Yes
The order of the rows is irrelevant Yes Yes
The order of the columns is irrelevant Yes Yes

Since both tables have the six properties of a relation, they are both in 1NF.

Are the tables in 2NF?

2NF requires that the tables be in 1NF and have no partial functional dependencies. A partial
functional dependency exists when a non-key attribute is functionally dependent on part of,
but not all of, the primary key. Partial functional dependencies are only possible when a table
has a composite primary key. Since SKUData and BuyerData do not have composite primary
keys, partial functional dependencies are not possible. Given that the tables are 1NF and there
are no partial functional dependencies, the tables are also in 2NF.

Are the tables in 3NF?

3NF requires that the tables be in 2NF and have no transitive functional dependencies. A
transitive functional dependency exists when a non-key attribute is functionally dependent on
another non-key attribute. For 3NF, let’s consider each table in turn, starting with SKUData:

SKUData (SKU, SKUDescription, Department, BuyerID)

In SKUData there are three non-key attributes: SKUDescription, Department, and BuyerID. If
there are any functional dependencies between these attributes then a transitive functional
dependency would exist and SKUData would not be in 3NF. Based on the functional
dependencies identified in the original table, we know that BuyerID determines Department.
Since these are both non-key attributes, a transitive functional dependency exists in SKUData.
To fix this problem, simply remove the attribute that is determined by the transitive functional
dependency (Department) from the table. With the Department attribute removed, SKUData is
now in 3NF:

SKUData (SKU, SKUDescription, BuyerID)

Key Terms and Normalization

6

Now let’s consider the BuyerData table:

BuyerData (BuyerID, Department)

Since BuyerData only has one non-key attribute (Deparment), there can’t be a transitive
functional dependency. This means that BuyerData is in 3NF.

The last step in the process is to make sure the 3NF tables are properly related. Tables
produced from the normalization process should always be related in some way. Since BuyerID
is the primary key in BuyerData and also appears as an attribute in SKUData, it should be noted
as a foreign key in SKUData. Use a dashed underline to indicate that BuyerID is a foreign key:

SKUData (SKU, SKUDescription, BuyerID)
BuyerData (BuyerID, Department)

Now the tables are related and in 3NF.

Key Terms and Normalization

7

The second example involves a table that records repair data. The table has the following
columns:

 RepairNbr – the number of the repair

 RepairDate – when the repair was completed

 RepairCost – the charge for the repair

 ItemNbr – the number of the item used in the repair

 Type – the type of the equipment used in the repair

 AcquisitionCost – the price that was paid to purchase the item

Start the normalization process by identifying the functional dependencies. From the
presentation, we know that the table has two functional dependencies:

We can use the functional dependencies to create two new tables:

Repair (RepairNbr, RepairDate, RepairCost, ItemNbr, Type, AcquisitionCost)
Item (ItemNbr, Type, AcquisitionCost)

For both tables, we use the determinant of the functional dependency as the primary key. This
means that the primary key of the Repair table is RepairNbr, while the primary key of the Item
table is ItemNbr.

Key Terms and Normalization

8

Having created tables based on the functional dependencies, we begin checking the normal
forms of each table.

Are the tables in 1NF?

1NF requires that the tables have the six properties of a relation:

Relation Properties Repair Item
Columns must have unique names Yes Yes
Values in a column must come from the same domain Yes Yes
Each row must be unique Yes Yes
There can be no multi-valued attributes Yes Yes
The order of the rows is irrelevant Yes Yes
The order of the columns is irrelevant Yes Yes

Since both tables have the six properties of a relation, they are both in 1NF.

Are the tables in 2NF?

2NF requires that the tables be in 1NF and have no partial functional dependencies. A partial
functional dependency exists when a non-key attribute is functionally dependent on part of,
but not all of, the primary key. Partial functional dependencies are only possible when a table
has a composite primary key. Since Repair and Item do not have composite primary keys,
partial functional dependencies are not possible. Given that the tables are 1NF and there are
no partial functional dependencies, the tables are also in 2NF.

Are the tables in 3NF?

3NF requires that the tables be in 2NF and have no transitive functional dependencies. A
transitive functional dependency exists when a non-key attribute is functionally dependent on
another non-key attribute. For 3NF, let’s consider each table in turn, starting with Repair:

Repair (RepairNbr, RepairDate, RepairCost, ItemNbr, Type, AcquisitionCost)

In Repair there are five non-key attributes: RepairDate, RepairCost, ItemNbr, Type, and
AcquisitionCost. If there are any functional dependencies between these attributes then a
transitive functional dependency would exist and Repair would not be in 3NF. Based on the
functional dependencies identified in the original table, we know that ItemNbr determines Type
and AcquisitionCost. Since these are all non-key attributes, a transitive functional dependency
exists in Repair. To fix this problem, simply remove the attributes that are determined by the
transitive functional dependency (Type and AcquisitionCost) from the table. With Type and
AcquisitionCost removed, Repair is now in 3NF:

Repair (RepairNbr, RepairDate, RepairCost, ItemNbr)

Key Terms and Normalization

9

Now let’s consider the Item table:

Item (ItemNbr, Type, AcquisitionCost)

In Item there are two non-key attributes: Type and AcquisitionCost. Based on the functional
dependencies identified in the original table, we know that Type and AcquisitionCost are not
functionally related. This means there are no transitive functional dependencies and Item is in
3NF.

The last step in the process is to make sure the 3NF tables are properly related. Tables
produced from the normalization process should always be related in some way. Since ItemNbr
is the primary key in Item and also appears as an attribute in Repair, it should be noted as a
foreign key in Repair. Use a dashed underline to indicate that ItemNbr is a foreign key:

Repair (RepairNbr, RepairDate, RepairCost, ItemNbr)
Item (ItemNbr, Type, AcquisitionCost)

Now the tables are related and in 3NF.

Normalization Practice 1
BIS 638 – Database Management for Business Systems

The Acme Company maintains a list of the parts they order from their vendors along with the
prices that each vendor charges. The following table shows a portion of this data:

PartCost

PartNbr Desc VendorNbr VendorAddress UnitCost

1234 Processor 117 Cupertino, CA $10.00

1234 Processor 623 Phoenix, AZ $8.00

5678 Memory 117 Cupertino, CA $3.00

5678 Memory 305 Austin, TX $2.00

5678 Memory 623 Phoenix, AZ $5.00

Field Descriptions:
PartNbr – number of the part in inventory
Desc – text description of the part
VendorNbr – number of the vendor who supplies the part
VendorAddress – city and state where the vendor is located
UnitCost – cost the vendor charges for the part

1. Use arrows to show the functional dependencies in the table.
2. Convert the table into 3NF relations.

Normalization Practice 1 Solution
BIS 638 – Database Management for Business Systems

The Acme Company maintains a list of the parts they order from their vendors along with the
prices that each vendor charges. The following table shows a portion of this data:

PartCost

PartNbr Desc VendorNbr VendorAddress UnitCost

1234 Processor 117 Cupertino, CA $10.00

1234 Processor 623 Phoenix, AZ $8.00

5678 Memory 117 Cupertino, CA $3.00

5678 Memory 305 Austin, TX $2.00

5678 Memory 623 Phoenix, AZ $5.00

1. Use arrows to show the functional dependencies in the table.

We’ll look for functional dependencies moving from left to right through the fields of the table.
As we do, keep in mind that a functional dependency only exists when the relationship between
the fields is one-to-one.

PartNbr determines Desc. PartNbr does not determine VendorNbr or VendorAddress because a
given part can come from multiple vendors (e.g., PartNbr 1234 comes from VendorNbr 117 and
623). PartNbr also does not determine UnitCost because a given part can have a different cost,
depending on the vendor (e.g., the UnitCost for PartNbr 1234 is either $10.00 or $8.00,
depending on the vendor).

PartNbr Desc VendorNbr VendorAddress UnitCost

1234 Processor 117 Cupertino, CA $10.00

1234 Processor 623 Phoenix, AZ $8.00

5678 Memory 117 Cupertino, CA $3.00

5678 Memory 305 Austin, TX $2.00

5678 Memory 623 Phoenix, AZ $5.00

Desc is free-form, descriptive text. As such, it should not be used as a determinant.

VendorNbr determines VendorAddress. VendorNbr does not determine PartNbr or Desc because
a given vendor can supply multiple parts (e.g., VendorNbr 117 supplies PartNbr 1234 and
PartNbr 5678). VendorNbr also does not determine UnitCost because the parts supplied by a
vendor can have different costs (e.g., VendorNbr 117 supplies PartNbr 1234 and PartNbr 5678
with UnitCosts of $10.00 and $3.00, respectively).

PartNbr Desc VendorNbr VendorAddress UnitCost

1234 Processor 117 Cupertino, CA $10.00

1234 Processor 623 Phoenix, AZ $8.00

5678 Memory 117 Cupertino, CA $3.00

5678 Memory 305 Austin, TX $2.00

5678 Memory 623 Phoenix, AZ $5.00

Vendor Address is free-form, descriptive text. As such, it should not be used as a determinant.

Since UnitCost is not determined by PartNbr or VendorNbr alone, it is determined by the two of
them together.

PartNbr Desc VendorNbr VendorAddress UnitCost

1234 Processor 117 Cupertino, CA $10.00

1234 Processor 623 Phoenix, AZ $8.00

5678 Memory 117 Cupertino, CA $3.00

5678 Memory 305 Austin, TX $2.00

5678 Memory 623 Phoenix, AZ $5.00

2. Convert the table into 3NF relations.

Using the three functional dependencies identified in Task 1, the following tables were created:

Part (PartNbr, Desc)
Vendor (VendorNbr, VendorAddress)
Cost (PartNbr, VendorNbr, UnitCost)

In each table, the determinant of the functional dependency becomes the primary key (e.g.,
PartNbr is the primary key in the Part table). Since UnitCost has two determinants (PartNbr and
Vendor), the Cost table has a composite primary key.

Having created the tables based on the functional dependencies, we now need to review them
to make sure each table is 3NF. Let’s review each table in turn.

Part (PartNbr, Desc)

 1NF – Yes. The six requirements of a relation at met.

 2NF – Yes. There are no partial functional dependencies (the primary key is not
composite so a partial functional dependency is not possible).

 3NF – Yes. There are not transitive functional dependencies.

Vendor (VendorNbr, VendorAddress)

 1NF – Yes. The six requirements of a relation at met.

 2NF – Yes. There are no partial functional dependencies (the primary key is not
composite so a partial functional dependency is not possible).

 3NF – Yes. There are not transitive functional dependencies.

Cost (PartNbr, VendorNbr, UnitCost)

 1NF – Yes. The six requirements of a relation at met.

 2NF – Yes. There are no partial functional dependencies. UnitCost is not determined by
PartNbr or VendorNbr alone.

 3NF – Yes. There are not transitive functional dependencies.

The 3NF tables are as follows:

Part (PartNbr, Desc)
Vendor (VendorNbr, VendorAddress)
Cost (PartNbr, VendorNbr, UnitCost)

Normalization Practice 2
BIS 638 – Database Management for Business Systems

The following table shows a partial list of orders from an organization’s order management
system:

OrderInfo

OrderNbr OrderDate ItemNbr ItemDesc UnitPrice OrderQty ShipperNbr ShipperName ShipperPhone

100 09/02/10 111 Widget $25 32 300 UPS 989-674-0223

100 09/02/10 333 Super Widget $70 51 300 UPS 989-674-0223

101 09/03/10 333 Super Widget $70 47 100 DHL 989-543-9878

102 09/03/10 222 Widget Plus $45 54 200 Fedex 989-322-5541

102 09/03/10 444 Mega Widget $115 34 200 Fedex 989-322-5541

103 09/04/10 111 Widget $25 41 300 UPS 989-674-0223

103 09/04/10 333 Super Widget $70 15 300 UPS 989-674-0223

103 09/04/10 444 Mega Widget $115 77 300 UPS 989-674-0223

Field Descriptions:
OrderNbr – number of the order
OrderDate – date that the order was received
ItemNbr – number of the item on the order (orders can have many items)
ItemDesc – text description of the item
UnitPrice – price charged for one item
OrderQty – number of items ordered
ShipperNbr – number of the shipping company
ShipperName – name of the shipping company
ShipperPhone – contact phone number for the shipping company

1. Use arrows to show the functional dependencies in the table.
2. Convert the table into 3NF relations.

Normalization Practice 2 Solution
BIS 638 – Database Management for Business Systems

The following table shows a partial list of orders from an organization’s order management
system:

OrderInfo

OrderNbr OrderDate ItemNbr ItemDesc UnitPrice OrderQty ShipperNbr ShipperName ShipperPhone

100 09/02/10 111 Widget $25 32 300 UPS 989-674-0223

100 09/02/10 333 Super Widget $70 51 300 UPS 989-674-0223

101 09/03/10 333 Super Widget $70 47 100 DHL 989-543-9878

102 09/03/10 222 Widget Plus $45 54 200 Fedex 989-322-5541

102 09/03/10 444 Mega Widget $115 34 200 Fedex 989-322-5541

103 09/04/10 111 Widget $25 41 300 UPS 989-674-0223

103 09/04/10 333 Super Widget $70 15 300 UPS 989-674-0223

103 09/04/10 444 Mega Widget $115 77 300 UPS 989-674-0223

1. Use arrows to show the functional dependencies in the table.

Before we begin identifying functional dependencies please remember that, as a rule, we don’t
use fields with free form text (ItemDesc), currency values (UnitPrice), or quantities (OrderQty) as
determinants. We also avoid using fields with dates (OrderDate) and phone numbers
(ShipperPhone) when possible.

With that said, let’s look for functional dependencies moving left to right, starting with
OrderNbr.

OrderNbr determines OrderDate, ShipperNbr, ShipperName, and ShipperPhone. OrderNbr does
not determine ItemNbr, ItemDesc, or UnitPrice because a given order can include multiple items
(e.g., OrderNbr 100 includes ItemNbr 111 and ItemNbr 333). OrderNbr also does not determine
OrderQty because a given order can have different quantities, depending on the item (e.g., the
OrderQty for OrderNbr 100 is either 32 or 51, depending on the ItemNbr).

OrderNbr OrderDate ItemNbr ItemDesc UnitPrice OrderQty ShipperNbr ShipperName ShipperPhone

100 09/02/10 111 Widget $25 32 300 UPS 989-674-0223

100 09/02/10 333 Super Widget $70 51 300 UPS 989-674-0223

101 09/03/10 333 Super Widget $70 47 100 DHL 989-543-9878

102 09/03/10 222 Widget Plus $45 54 200 Fedex 989-322-5541

102 09/03/10 444 Mega Widget $115 34 200 Fedex 989-322-5541

103 09/04/10 111 Widget $25 41 300 UPS 989-674-0223

103 09/04/10 333 Super Widget $70 15 300 UPS 989-674-0223

103 09/04/10 444 Mega Widget $115 77 300 UPS 989-674-0223

ItemNbr determines ItemDesc and UnitPrice. ItemNbr does not determine OrderNbr,
OrderDate, ShipperNbr, ShipperName, or ShipperPhone because a given item can be included on
multiple orders (e.g., ItemNbr 333 is included on OrderNbr 100, 101, and 103). ItemNbr also
does not determine OrderQty because a given item can have different quantities, depending on
the order (e.g., the OrderQty for ItemNbr 111 is 32 for OrderNbr 100 and 41 for OrderNbr 103).

OrderNbr OrderDate ItemNbr ItemDesc UnitPrice OrderQty ShipperNbr ShipperName ShipperPhone

100 09/02/10 111 Widget $25 32 300 UPS 989-674-0223

100 09/02/10 333 Super Widget $70 51 300 UPS 989-674-0223

101 09/03/10 333 Super Widget $70 47 100 DHL 989-543-9878

102 09/03/10 222 Widget Plus $45 54 200 Fedex 989-322-5541

102 09/03/10 444 Mega Widget $115 34 200 Fedex 989-322-5541

103 09/04/10 111 Widget $25 41 300 UPS 989-674-0223

103 09/04/10 333 Super Widget $70 15 300 UPS 989-674-0223

103 09/04/10 444 Mega Widget $115 77 300 UPS 989-674-0223

ShipperNbr determines ShipperName and ShipperPhone. ShipperNbr does not determine
OrderNbr, OrderDate, ItemNbr, ItemDesc, UnitPrice, or OrderQty because a given shipper will
handle multiple orders and multiple items (e.g., ShipperNbr 300 handles OrderNbr 100 and 103).

Please note that ShipperPhone functionally determines ShipperNbr and ShipperName which
might lead you to choose it as the determinant instead of ShipperNbr. Since a phone number
could be used by multiple people (e.g., if you call the BIS department office a couple of different
people might answer) it is safer to use ShipperNbr as the determinant.

OrderNbr OrderDate ItemNbr ItemDesc UnitPrice OrderQty ShipperNbr ShipperName ShipperPhone

100 09/02/10 111 Widget $25 32 300 UPS 989-674-0223

100 09/02/10 333 Super Widget $70 51 300 UPS 989-674-0223

101 09/03/10 333 Super Widget $70 47 100 DHL 989-543-9878

102 09/03/10 222 Widget Plus $45 54 200 Fedex 989-322-5541

102 09/03/10 444 Mega Widget $115 34 200 Fedex 989-322-5541

103 09/04/10 111 Widget $25 41 300 UPS 989-674-0223

103 09/04/10 333 Super Widget $70 15 300 UPS 989-674-0223

103 09/04/10 444 Mega Widget $115 77 300 UPS 989-674-0223

OrderQty is determined by OrderNbr and ItemNbr together (you need to know the order and the
item on the order to know how much of the item was ordered).

OrderNbr OrderDate ItemNbr ItemDesc UnitPrice OrderQty ShipperNbr ShipperName ShipperPhone

100 09/02/10 111 Widget $25 32 300 UPS 989-674-0223

100 09/02/10 333 Super Widget $70 51 300 UPS 989-674-0223

101 09/03/10 333 Super Widget $70 47 100 DHL 989-543-9878

102 09/03/10 222 Widget Plus $45 54 200 Fedex 989-322-5541

102 09/03/10 444 Mega Widget $115 34 200 Fedex 989-322-5541

103 09/04/10 111 Widget $25 41 300 UPS 989-674-0223

103 09/04/10 333 Super Widget $70 15 300 UPS 989-674-0223

103 09/04/10 444 Mega Widget $115 77 300 UPS 989-674-0223

2. Convert the table into 3NF relations.

Using the four functional dependencies identified in Task 1, the following tables were created:

Order (OrderNbr, OrderDate, ShipperNbr, ShipperName, ShipperPhone)

Item (ItemNbr, ItemDesc, UnitPrice)

Shipping (ShipperNbr, ShipperName, ShipperPhone)
Quantity (OrderNbr, ItemNbr, OrderQty)

In each table, the determinant of the functional dependency becomes the primary key (e.g.,
OrderNbr is the primary key in the Order table). Since OrderQty has two determinants
(OrderNbr and ItemNbr), the OrderQty table has a composite primary key.

Having created the tables based on the functional dependencies, we now need to review them
to make sure each table is 3NF. Let’s review each table in turn.

Order (OrderNbr, OrderDate, ShipperNbr, ShipperName, ShipperPhone)

 1NF – Yes. The six requirements of a relation at met.

 2NF – Yes. There are no partial functional dependencies (the primary key is not
composite so a partial functional dependency is not possible).

 3NF – No. There is a transitive functional dependency. A non-key attribute (ShipperNbr)
determines another non-key attribute (ShipperName and ShipperPhone). To fix the
transitive functional dependency remove ShipperName and ShipperPhone (they’re
already in the Shipping table anyway). Make sure to indicate that ShipperNbr is a
foreign key (it relates the Order table to the Shipping table).

Order (OrderNbr, OrderDate, ShipperNbr)

Item (ItemNbr, ItemDesc, UnitPrice)

 1NF – Yes. The six requirements of a relation at met.

 2NF – Yes. There are no partial functional dependencies (the primary key is not
composite so a partial functional dependency is not possible).

 3NF – Yes. There are not transitive functional dependencies.

Shipping (ShipperNbr, ShipperName, ShipperPhone)

 1NF – Yes. The six requirements of a relation at met.

 2NF – Yes. There are no partial functional dependencies (the primary key is not
composite so a partial functional dependency is not possible).

 3NF – Yes. There are not transitive functional dependencies.

Quantity (OrderNbr, ItemNbr, OrderQty)

 1NF – Yes. The six requirements of a relation at met.

 2NF – Yes. There are no partial functional dependencies. OrderQty is not determined by
OrderNbr or ItemNbr alone.

 3NF – Yes. There are not transitive functional dependencies.

The 3NF tables are as follows:

Order (OrderNbr, OrderDate, ShipperNbr)
Item (ItemNbr, ItemDesc, UnitPrice)
Shipping (ShipperNbr, ShipperName, ShipperPhone)
Quantity (OrderNbr, ItemNbr, OrderQty)

Expert paper writers are just a few clicks away

Place an order in 3 easy steps. Takes less than 5 mins.

Calculate the price of your order

You will get a personal manager and a discount.
We'll send you the first draft for approval by at
Total price:
$0.00