Sample Questions User View to UNF 1. The following tabular listing from ABC Prin
ID: 3807700 • Letter: S
Question
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. , Canada’s 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 [
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
Explanation / Answer
1
1NF [Part_Id (PK), Part_Name, QtyUsed, Supplier, SupplierPhone, Model Description , ModelPrice ]
2NF
PARTS [Part_Id (PK), Part_Name, QtyUsed]
SUPPLIER[Supplier_Id (PK), Supplier, SupplierPhone]
MODEL[ Model_Id (PK), Model Description , ModelPrice ]
2.
1NF
UNF [Invoice# (PK), Invoice Date, CUST#, CustName, Address, Region, SalesRep#, SalesRepName, EquipUsed#, EquipUsedDesc, ServCode, ServDesc, HourlyCharge, Hours , PaymentType ]
2NF
INVOICE[Invoice# (PK), Invoice Date]
CUSTOMER[CUST# (PK), CustName, Address]
SALESREP[SalesRep# (PK), SalesRepName,Region]
EQUIPMENT[EquipUsed# (PK), EquipUsedDesc]
SERVICE[ServCode (PK), ServDesc, HourlyCharge, Hours , PaymentType]
3.
UNF [ Reg#(PK), RegDate, CustName, Caddress, Hphone, City,PostalCode,Wphone, ( ChildName, ProgCode, ProgName,Cost ), ProcessDate, ProcByEmp# ]
1NF
UNF [ Reg#(PK), RegDate, CustName, Caddress, Hphone, Wphone, ChildName, ProgCode , ProgName,Cost , ProcessDate, ProcByEmp#]
2NF
REGISTRATION[Reg#(PK), RegDate , CustName, Caddress, Wphone, ChildName,City,PostalCode]
PROGRAMME[ProgCode (PK), ProgName,Cost ]
PROCESS[ProcByEmp# (PK),ProcessDate]
4.
UNF [Class#(PK), ClassName, Room# (Member#, MbrName, (Date, AttendY/N) ]
1NF
UNF [Class#(PK), ClassName, Room# ,Member#, MbrName ,Date, AttendY/N ]
2NF
CLASS[(Class#,Room#)(PK), ClassName ]
MEMBER[Member# (PK), MbrName,Date, AttendY/N]
5.
UNF [Item#, ItemDesc, ( SalesPerson, SuppNo, Supplier, Quantity, Units, DateExpected, OrderNumber ) ]
1NF [ Item# PK, ItemDesc, SalesPerson, SuppNo , Supplier, Quantity, Units, DateExpected, OrderNumber ]
2NF
ITEM[Item# (PK), ItemDesc]
SUPPLIER[SuppNo (PK), Supplier, Quantity, Units]
ORDER[OrderNumber (PK), DateExpected]
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.