Assignment #5: Database Design
Description:
The goal is to practice database design using normalization.
Work the Morgan Importing Problem described as described below. No database development is required for this
assignment, just the table design. The assignment involves concepts covered in chapter 4 and its slides. Pay
attention to the following guidelines for Assessing Table Structure from Figure 4-1 of Chapter 4.
• Count rows and examine columns
• Examine data values and interview users to determine:
Multivalued dependencies
Functional dependencies
Candidate keys
Primary keys
Foreign keys
• Assess validity of assumed referential integrity constraints
Assessing Table Structure
Morgan Importing Problem:
Phillip Morgan, the owner of Morgan Importing, makes periodic buying trips to various countries. During
the trips, he keeps notes about the items he purchases and basic data about their shipments. He hired a
college student as an intern, and she transformed his notes into the spreadsheets in Figure 4-9. This is
just sample data. Phillip has purchased hundreds of items over the years, and they have been shipped in
dozens of different shipments.
Phillip wants to enter the information age, thus he has decided to develop a database of his inventory.
He wants to keep track of the items he has purchased, their shipments, and eventually customers and
sales. To get started, he has asked you to create a database for the data in Figure 4-9.
A
B
E
1 ShipmentNumber Shipper
Phone
Contact From
Departure
G
Arrival
H
2
49100300 Wordwide
800-123-4567 Jose
Philippines
Contents
QE dining set, large bureau,
5/5/2009 6/17/1999 porcelain lamps
InsuredValue
$27,500
3
488955 Intenational
84899440 Wordwide
399400 Intenational
800-123-8898 Marilyn
800-123-4567 Jose
800-123-8898 Marilyn
Singapore
6/2/2009
Miscellaneous linen, large masks,
14 setting Willow design china
Woven goods, antique leather
$7,500
Peru
7/3/2009 7/28/2009 chairs
Singaporeee
Large bureau, brass lamps, willow
8/5/2009 9/11/2009 design serving dishes
$18,000
44
10
56789uIERTE
Item
Date
11
Willow Serving Dishes
Large bureau
12
Brass lamps
13
QE Dining Set
City
Store
Salesperson
7/15/2009 Singapore Jade Antiques Swee Lai
7/17/2009 Singapore Eastern Sales Jeremey
7/20/2009 Singapore Jade Antiques Mr. James
4/7/2009 Manila E. Treasures Gracielle
Price
$4,500
$9,500
$1,200
$14,300
14
15
Figure 4-9 – Spreadsheets from Morgan Importing To complete this assignment, you must complete following instructions and provide detailed answers for each item:
A
Follow the "Assessing Table Structure" procedure shown. List multivalued dependencies,
functional dependencies, candidate keys, primary keys, and foreign keys. State your
assumptions.
B
List questions you would ask Phillip to verify your assumptions.
C
Create tables as necessary to eliminate multivalued dependencies, if any.
D
E
F
G
The relationship between shipment and item data could be inferred by matching values in the
From cells to values in the City cells. Describe two problems with that strategy.
Describe a change to this spreadsheet that does express the shipment/item relationship.
Assume that Phillip wishes to create an updateable database from this data. Design tables you
think are appropriate. State all referential integrity constraints.
Assume that Phillip wishes to create a read-only database from this data. Design tables you think
are appropriate. State all referential integrity constraints.
H
Do these data have the multivalue, multicolumn problem? If so, how will you deal with it?
Do these data have the inconsistent data problem? If so, how will you deal with it?
J
Do these data have a null value data problem? If so, how will you deal with it?
K
Do these data have the general-purpose remarks problem? If so, how will you deal with it?
For part A, make sure you state your assumptions.
For part B, list at least 6 questions to verify your assumptions stated in part A.
For part C, describe the tables using the textbook format (the PK is underlined and the FK is italicized). For
example, SKU_DATA (SKU, SKU_Description, Department, Buyer)
For part D, focus on the FK usage and the issue of data consistency when addressing this part.
For Parts F and G,, describe the tables using the textbook format (underlined PK and italicized FK) with referential
integrity statements. For example,
SKU_DATA (SKU, SKU_Description, Department, Buyer)
BUYER (BuverName, Department)
Where SKU_DATA.Buyer must exist in BUYER.BuyerName
Include all of your answers for all parts in one MS Word document. See other useful hints document in module 5./n Hints for Assignment #5
The MORGAN IMPORTING PROJECT
Part A:
Morgan keeps a table of data about the stores from which he purchases. The stores are located in different
countries and have different specialties. Consider one relation for the stores, keeping info about the store name,
country, owner, specialty, etc.
Parts B and C:
Let's all use the following assumptions to come up with closely similar design:
Does Store Name → City
Does City → Store Name
Are City Names Unique
NO, names may repeat in different cities within one country,
and between countries.
NO, There may be more than one store in each city.
YES. Although city names in general are not unique, for a
small import-export business they probably will be.
Given these assumptions, transform the store table (from part A) into a set of tables that are in both 4NF and
BCNF. Indicate the primary keys, candidate keys, foreign keys, and referential integrity constraint statements.
Part D:
Consider a relation for the shipment. Examine the spreadsheet for needed columns and think of other possible
columns such as: Vendor Name, Vendor Fax, Departure Date, Arrival Date, Country of origin, Destination,
Shipment Cost, Insurance Value, Insurer, and others. Here, you need to identify functional dependencies and
multivalued dependencies.
Next, try to transform the SHIPMENT relation (from Part C of the Morgan Importing question at the end of Chapter
3) into a set of tables that are in both 4NF and BCNF. Indicate the primary keys, candidate keys, foreign keys, and
referential integrity constraint statements.
Show your tables using Schemas, which is to say use the form:
TABLENAME(Field1, Field2, Field3, etc.)
And make sure that Primary Keys are underlined and Foreign Keys are italicized.
State any Referential Integrity Constraints between the tables.
