Faculty of Science and Technology
ITECH1006/5006
Database Management Systems
Database Management Systems
Tutorial Week 3
Tasks
1. Given the following table, convert the table into normalised data structure showing all attributes and identifying primary keys. Show your normalisation process from the un-normalised form to the third normal form.
2. Given the following table, convert the above table into normalized data structures showing all attributes and identifying primary keys. Show your normalisation process from the un-normalised form to the third normal form.
Customer
Number
148
148
282
356
356
408
608
608
608
Customer
Name
AI’s
Appliance
and Sport
AI’s
Appliance and Sport
Brookings
Direct
Ferguson’s
Ferguson’s
The
Everything
Shop
Johnson’s
Department
Store
Johnson’s
Department
Store
Johnson’s
Department
Store
CRICOS Provider No. 00103D
Order
Number
21608
Order Date
Part
Description
Iron
Number
Ordered
11
Quoted
Price
$21.95
Warehouse
10/20/2007
Part
Number
AT94
3
Rep
Number
20
21619
10/23/2007
DR93
Gas Range
1
$495.00
2
20
21614
10/21/2007
KT03
Dishwasher
2
$595.00
3
35
21610
21610
21613
10/20/2007
10/20/2007
10/21/2007
DR93
DW11
KL62
Gas Range
Washer
Dryer
1
1
4
$495.00
$399.99
$329.95
2
3
1
65
65
35
21617
10/21/2007
BV06
Howe Gym
2
$794.95
2
65
21617
10/21/2007
CD52
Microware
Oven
4
$150.00
1
65
21623
10/21/2007
KV29
Threadmill
2
$1,290.00
2
65
Page 1 of 3
3. Given the sales data in the following table, normalise the table to the third normal form by considering the business rules stated in each of the cases stated below.
Date
Product
Price
Client
Phone
Address
11 Jan
Widget
100
Nurk Inc.
666-999
11 Bush Ave
12 Jan
Gizmo
120
Klutz & Co
131-313
13 Luck Rd
12 Jan
Widget
100
Bloggs Ltd
123-456
12 High St
13 Jan
Widget
100
Klutz Coy.
131-323
13 Luck Rd
14 Jan
Gizmo
120
F. Nurk Inc.
666-999
11 Bushy Ave
Case 1:
Each client makes no more than one order per day
No two clients have the same name
Each order consists of a single product
Case 2:
Assuming that in any single day,
Clients sometimes make several orders
Each order is for a different product
Case 3:
Assuming that that clients sometimes can order the same product several times in a single day.
4. Given the following table and the following business documentation:
Each employee belongs to one department only.
An employee is assigned to at least one project and a project must have at least one employee. a) Convert the table into normalized data structures showing all attributes and identifying primary keys.
b) Present your normalized relations as an ER diagram.
Employee No: 01267
Employee Name: Graham Clark
Employee Address: 12 Smith Street Richmond VIC 3456
Hire Date: 02/03/1980
Salary: 30,000
Project(s) assigned to:
Project no:
Project Start Date:
Marketing project
123
12/01/2002
Payroll project
175
13/06/2002
Leave: 03/03/1999 - 17/03/1999
15/12/1999 - 29/12/1999
Department No: 05
Department Name:
Department Location: MEL
Computing
CRICOS Provider No. 00103D
Page 2 of 3
5. For the following relations, determine what normal form each is in:
a) ORDER_LINE(OrderNumber, ItemNumber, ItemDescription, Price, Qty)
b) STORE_MERCHANDISE (SKU, PromotionID, Seller, Style, Price)
Given that the following dependencies exist:
SKU, PromotionID determines Seller, Style, Price
SKU
determines
Seller, Style
c) Convert the following relation to 3NF; the repeating data within the relation has been identified using {}:
PATIENT(HouseholdNum, HouseholdName, Street, City, State, PostCode, Balance,
PatientNum, PatientName, {ServiceCode, Description, Fee, Date})
The following dependencies within the data:
PatientNum -> HouseholdNum, HouseholdName, Street, City, State, PostCode, Balance,
PatientNum, PatientName
HouseholdNum -> HouseholdName, Street, City, State, PostCode, Balance
ServiceCode -> Description, Fee
PatientNum, ServiceCode -> Date
CRICOS Provider No. 00103D
Page 3 of 3 