Free Essay

Sukhisingh

In: Business and Management

Submitted By sukhiboparai
Words 648
Pages 3
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

Similar Documents