Sample Questions User View to UNF

1. The following tabular listing from ABC Printer company is to be used to answer the following part 2 questions. The listing shows a printout of the company's two printer models and the parts and part suppliers used to make the models.

Answer all questions in the space provided:

Part_Id	Part_Name	Qty Used	Supplier	Supplier Phone	Model Description	Unit Price
PF123	Paper spool	2	ABC Plastics	416-234-2342	Laserjet Printer	$423.56
LC432	Laserject cartridge	4	Jetson Carbons	905-434-3333	Laserjet Printer	$423.56
MT123	120v power unit	1	ACME Power Supply	767-232-2221	Laserjet Printer	$423.56
PF123	Paper spool	1	ABC Plastics	416-234-2342	256 Colour Printer	$89.99
IJ345	Ink jet tray	3	Para Inks	416-323-2345	256 Colour Printer	$89.99
MT123	120v power unit	2	ACME Power Supply	767-232-2221	256 Colour Printer	$89.99

UNF [Part_Id, Part_Name, (QtyUsed, Supplier, SupplierPhone, Model Description , ModelPrice ) ]

1NF [

2NF [

[

[ 2. Solve the following user-view shown below. Be sure to list the 1NF and 2NF solution.

INVOICE #:	1355		CUSTOMER:	56 - John Adams
INVOICE DATE:	5-Jul-02		REGION:	High Park
SALES REP:	2 - Ryan Armstrong		ADDRESS:	21 Scarlett Road
				Toronto
EQUIP. USED:	20 hp John Deer tractor/ mower			M2S 4S3
	2 hp Johnson grass trimmer
	Haggmann garden-tiller
SERVICES:
SERVICE CODE	DESCRIPTION	HOURLY CHARGE	WORK DURATION (hours)	TOTAL CHARGE
LC	Lawn Cutting	$25.00	0.75	$18.75
LW	Lawn Weeding	$35.00	1.15	$40.25
LF	Lawn Fertilizing	$15.00	0.25	$3.75
TG	Tree Pruning	$45.00	0.50	$22.50
			SUBTOTAL:	$85.25
			GST (7%):	$5.97
			PST (8%):	$6.82
			TOTAL DUE:	$98.04
			PAYMENT:	Cheque

UNF [Invoice#, Invoice Date, CUST#, CustName, Address, Region, SalesRep#, SalesRepName, ( EquipUsed#, EquipUsedDesc),

(ServCode, ServDesc, HourlyCharge, Hours ) , PaymentType ]

3. (10 marks)For the following userview, list the relations in 1NF and 2NF.

Town of Vaughan Activity Registration Form

Registration # 6503 Date: January 7, 2002

Name: Tina Marvello

Address: 1453 PineTree Way City: Woodbridge Postal Code L9T 4Z3

Home Phone: 905-555-1212 Work Phone: 416-555-2121

Client 1

Name	Program Code	Program Name	Cost
Robert Marvello	2544	Aquaquest 1	$58

Client 2

Name	Program Code	Program Name	Cost
Alexandra Marvello	2544	Aquaquest1	$58

Client 3

Name	Program Code	Program Name	Cost
Alexandra Marvello	4811	Play With Clay	$78

Client 4

Name	Program Code	Program Name	Cost
Mary Wong	4811	Play WithClay	$78

Signature: Tina Marvello Total Cost: $276.00

Process Date: Jan 10/02 Processed by Emp#126

UNF [ Reg#(PK), RegDate, CustName, Caddress, Hphone, Wphone, ( ChildName, ProgCode, ProgName,Cost ), ProcessDate, ProcByEmp# ] 4. For the user view shown below:

STUDIOUS SENIORS ATTENDANCE REPORT

Class# Class Name Room Member# Name Date Attendance

1234 Ballroom Dancing 2133 123 Jane Smith Sept 12 Y

Sept 19 Y

1234 Ballroom Dancing 2133 124 Bill Smith Sept 12 Y

Sept 19 N

1234 Ballroom Dancing 2133 321 Paul Woo Sept 12 N

Sept 19 Y

1234 Ballroom Dancing 2133 439 Mary Huang Sept 12 Y

Sept 19 N

2245 Basket Weaving 2133 439 Mary Huang Sept 14 N

Sept 21 Y

3122 Italian Cooking 2134 439 Mary Huang Sept 15 Y

Sept 22 Y

3122 Italian Cooking 2134 123 Jane Smith Sept 15 Y

Sept 22 N

UNF [Class#(PK), ClassName, Room# (Member#, MbrName, (Date, AttendY/N) ]

5. MARS BARS CANADA Inc. , Canadas leading distributor of chocolate bars orders ingredients from suppliers to make their products. The following report is used by the manufacturing department to identify the expected receiving date for materials that have been ordered.

MARS BARS CANADA Inc

Ingredients Expected Date Report

Sales- Supplier Date Order

Item Description Person No Supplier Quantity Units Expected Number

------ ------- ------ ------------ -------- ------------------- ------------ ------ ------------- ------------

SG01 Sugar Grade 1 Bill 411 Redpath Sugars 1000 kg Feb 24/02 MB652

SG01 Sugar Grade 1 John 422 Jamaica Cane 2000 kg Feb 24/02 MB653

SG01 Sugar Grade 1 Joe 397 Cuban Sugars 2500 kg Mar 5/02 MB699

SG01 Sugar Grade 1 Bill 411 Redpath Sugars 1555 kg Mar 5/02 MB702

---------

Sub-Total 7055 kg

ML01 Molasses John 422 Jamaica Cane 2000 liters Feb 24/02 MB653

ML01 Molasses Roxanne 555 Natura-Sugars 2000 liters Feb 26/02 MB648

ML01 Molasses Joe 397 Cuban Sugars 2660 liters Feb 27/02 MB699

---------

Sub-Total 6660 liters

UNF [Item#, ItemDesc, ( SalesPerson, SuppNo, Supplier, Quantity, Units, DateExpected, OrderNumber ) ]

1NF [

2NF [