DBS201 Asst 3 (10% of final mark)
Normalization
Due Date: Tuesday April 12, 2011 - 23:59
Hand in your printed solution of this assignment. Email submissions will not be accepted.Late assignments incur a penalty of 20% per “working day”. The assignment is to be done in groups of 2 or 3 persons. An assignment done by a single person will incur a penalty of 20%.All group members must sign the assignment submission form found on page 2. Please indicate the work done by each person.
1) Assignments will be marked on
a)Correctness - All 3NF relations have been identified with correct attributes, Primary Keys and all Foreign Keys.
b)Meaningfulness - use of meaningful relation (table) and attribute names .
c)Relations are shown in alphabetic order when requested (i.e. after step 2.5 for each user view and after step 7).
d)All tables must be named according to their Primary Key.
TO HAND-IN
2) For each user view:
2.1) Write the UNF relation including the Primary Key and Multi-Valued Dependencies.
2.2) Show the 1st Normal Formafter converting the UNF to 1st Normal Form.
2.3) Show the 2nd Normal Formafter converting the 1st Normal Form to 2nd Normal Form.
2.4) Show the 3rd Normal Formafter converting the 2nd Normal Form to 3rdNormal Form..
2.5) Name the relations (tables) and show the recommended tables from this User View..
NOTE: When showing the results of step 2.5, the tables must be shown in alphabetic order by table name!
3)Simplify primary keys wherever possible and show any relations that changed.
Note: Usually no simplification is possible. This step is used to reduce a two-part PK down to a single field PK or a three-part PK down to a two-part PK whenever possible).
4) Merge all 3rd Normal Form relations from step 3.
5) If any of the merged relations is in 2nd Normal Form, convert it to 3rd Normal Form.
6)Identify all Foreign Keys
7) Explode multi-field attributes into many fields and list your final list of tables that you recommend for the SENEMAX database.
NOTE: When showing the results of step 7, the tables must be shown in alphabetic order by table name!
Student Assignment Submission Form
I/we declare that the attached assignment is my/our own work in accordance with the Seneca Academic Policy. No part of this assignment has been copied manually or electronically from any other source (including web sites) or distributed to other students
.
Name
1) Name:Student ID:
------
Work Done:
------
------
2) Name:Student ID:
------
Work Done:
------
------
3) Name:Student ID:
------
Work Done:
------
------
PLAGIARIASM and CHEATING
(Refer to section 8.7, page 6 of Seneca's Academic Policy)
(or visit: )
SENEMAX Computer Associates operates a store in York Lanes that provides technical computer support on a drop-in basis to customers who may be students, faculty or the public.There are management employees as well as programmer/analysts. Programmer analysts work under the direction of management employees.
A monthly schedule identifies the managers and programmer/analysts scheduled to work each day. Each day one manager would be scheduled to supervise programmer/analysts in the morning session and a different manager would normally be scheduled to supervise the afternoon session. If a manager is sick or cannot do his shift, another manager will replace him. Each day up to 5 programmer/analysts would be scheduled to work in the store: 1 at the cash desk, 2 at the hardware desk and 2 at the software desk. A programmer/analyst may be assigned to work at different desks during the month. If a programmer/analyst is sick or cannot do his shift, the remaining employees for that day will cover all five desks.
A customer may purchase products sold at the store.
A customer may leave one or more pieces of equipment to have one or more services performed. A work order is prepared for each piece of equipment left for repair.
The services provided include: hardware or software installation, virus removal, software upgrade, or technical support.
After the equipment has been serviced and/or the customer has selected their purchases the sales desk prepares an invoice listing each work-order completed and all services performed as well as any products purchased. Payment in cash or by MasterCard is accepted.
USERVIEW 1 (2 examples): Sample Invoices
SENEMAX COMPUTER ASSOCIATES INVOICEDATE:May 30 2011CLIENT:Paul Smith INVOICE#: 423
SERVICESSERVICE TYPE DESC CHARGE
Work Order 472: 10 – Install SW PackageApplication 30.00
13 – Upgrade OSOperating System 30.00
SERVICE SUBTOTAL60.00
PRODUCT QTY UNIT COST CHARGE
PRODUCT SUBTOTAL:0.00
INVOICE SUBTOTAL:60.00
HST:7.80
INVOICE TOTAL:67.80
PREPARED BY:Paula Corelli, #121
SENEMAX COMPUTER ASSOCIATES INVOICE
DATE: May 30, 2011CLIENT: May Huang INVOICE#: 425
SERVICES SERVICE TYPE DESC CHARGE
Work Order 474: 12 – Install OSOperating System40.00
20 – Upgrade SW PackageApplication 15.00
Work Order 475: 21 – Install Hard DriveHardware 50.00
SERVICE SUBTOTAL105.00
PRODUCTS QTY UNIT COST CHARGE
145: Logitech Performance Mouse MX 1 89.99 89.99
146: Dell V313w Printer WiFi,Print,Copy,Scan2 139.99 279.98
PRODUCT SUBTOTAL:418.07
INVOICE SUBTOTAL:523.07
HST: 68.00
INVOICE TOTAL:591.07
PREPARED BY:Amy Smith, #136
USERVIEW 2:
SENEMAXINVENTORY REPORT
PREPARED BY:Amy Smith DATE:May 30, 2011
APPROVED BY:Paul Woo DATE:May 30, 2011
PROD # / PRODUCT NAME / UNIT COST / SUPPLIER / REORDER POINT / REORDER QUANTITY / QUANTITY ON HAND100 / Kingston 8GB SD High Capacity Memory Card / 15.99 / 107-Kingston / 3 / 5 / 6
122 / Iomega 500GB eGo SuperSpeed USB 3.0 Portable Hard Drive / 79.99 / 108-Iomega / 3 / 5 / 3
144 / Logitech M305 Wireless Mouse with Nano Receiver / 29.99 / 756-Logitech / 5 / 10 / 6
145 / Logitech Performance Mouse MX / 89.99 / 756-Logitech / 5 / 5 / 5
146 / Dell V313w Printer WiFi,Print,Copy,Scan / 139.99 / 422-Dell / 5 / 5 / 4
234 / Dell V715w Printer
WiFi,Print,Copy,Scan / 179.99 / 422-Dell / 3 / 5 / 4
235 / Western Digital 2 TB My Book Essential Edition External Hrd Drive USB 3.0 / 129.99 / 501-Western Digital / 5 / 10 / 8
250 / D-Link DIR-655 Wireless Xtreme N Gigabyte Router / 89.99 / 502- D-LINK NETWORKS / 3 / 5 / 2
251 / Dell ST232OL 23” Full HD Widescreen Monitor / 169.99 / 422-Dell / 2 / 3 / 3
USERVIEW 3:
SENEMAX SERVICE CHARGES
SERVICE CODE / SERVICE DESCRIPTION / SERVICE TYPE / LABOUR CHARGE / TIME ESTIMATE (min)10 / Install SW Package / APP / 30.00 / 30
11 / Remove a Virus / VIR / 40.00 / 45
12 / Install OS / OPS / 40.00 / 60
13 / Upgrade OS / OPS / 30.00 / 45
20 / Upgrade SW Package / APP / 15.00 / 30
21 / Install Hard Drive / HRW / 50.00 / 60
23 / Add Memory / HRW / 25.00 / 30
24 / Install CD Drive / HRW / 50.00 / 60
USERVIEW 4:
SENEMAX EMPLOYEE LIST
ID / NAME / HOME PHONE / CELL PHONE / S.I.N. NUMBER / WEEK DAY / PAY RATE120 / Cindy Lee / 905-338-1234 / 221903202 / W / 12.50
121 / Paula Corelli / 416-458-4562 / 232544301 / W / 10.00
122 / Amy Smith / 905-338-1234 / 416-332-4354 / 234111991 / F / 12.50
123 / Paul Huang / 416-932-4533 / 354222991 / W / 12.50
124 / Maria Wong / 905-345-5366 / 905-654-3552 / 243524532 / M / 12.50
125 / Susan Wong / 905-345-5366 / 246543534 / M / 10.00
126 / Phil Ramirez / 416-435-6599 / 232543555 / T / 12.50
127 / Paul Woo / 416-283-4221 / 415-252-8834 / 354222991 / 24.50
143 / Mary Feria / 416-342-1079 / 415-252-2703 / 256182342 / 30.00
…. / … / …. / …. / …. / ….
… / … / … / … / … / … / …
144 / Paul Smith / 416-766-4511 / 054432435 / M / 12.50
145 / Maria Wong / 905-322-5488 / 065432432 / T / 10.00
USERVIEW 5:
SENEMAX TIME RECORDS
FOR WEEK OF: MAY 2-6, 2011
Junior / Hardware Desk -Junior / Software
Desk Junior / Hardware
Desk Senior / Software
Desk -Senior / Approved By
Monday May2
Worked
Worked / AM: #143 .... Mary Feria
x2133………..- 4 hrs
PM: #142
Kelly Ma x2132………. -
4 hrs / Mark
Fields
#132
8 hrs / Susan
Wong
#125
8 hrs / Li
Huang
#134
8 hrs / Maria
Wong
#124
8 hrs / Paul
Smith
#144
0 hrs / #142
Kelly Ma
Tuesday May3
Worked
…Worked / AM: #142
Kelly Ma
x2132………. -
4 hrs
PM: #141
LydiaBell
x2133………. -
4 hrs / Maria
Wong
#145
8 hrs / Vicky
Lee
#137
8 hrs / Wendy
Brill
#185
6 hrs / Phil
Ramirez
#126
8 hrs / Lindsay
Roth
#131
8 hrs / #141
LydiaBell
WedMay4
…Worked
Worked / AM: #141
LydiaBell
x2133 ……….-
4 hrs
PM: #127
Paul Woo
x2144
4 hrs / Paula
Corelli
#121
8 hrs / Sue
Cohen
#128
4 hrs / Paul
Huang
#123
8 hrs / Cindy
Lee
#120
8 hrs / Chris
White
#127
8 hrs / #127
Paul Woo
ThursdayMay5
Worked
s Worked / AM: #127
Paul Woo
x2144
4 hrs
PM: #127
Paul Woo
x2144
4 hrs / Amy
Smith
#136
8 hrs / Meg
McRae
#130
8 hrs / George
Bell
#129
8 hrs / Rachel
Lee
#133
8 hrs / Anna
Gransky
#135
0 hrs / #127
Paul Woo
Friday
May6
Worked
Worked / AM: #143
Mary Feria
x2133
4 hrs
PM: #130
Kelley Stewart
x2144
4 hrs / Angela
Simpson
#138
4 hrs / Ann
Brown
#139
0 hrs / Meg
Ryan
#140
8 hrs / Amy
Smith
#122
8 hrs / Ray
Jones
#143
4 hrs / #130
Kelley Stewart
USERVIEW 6 (EXAMPLE #1 OF 2):
Hint: Apart from Cust#, carry all Customer information in one attribute called CDetails.
SENEMAX COMPUTER ASSOCIATES WORK ORDERPREPARED BY: Amy Smith DATE:May 26, 2011WORK ORDER: 474
CUSTOMER: May HuangCUST#: C2048 INVOICE#: 424
ADDRESS: 124 Main St, Markham Email:
Home Phone: 416-432-1233Cell Phone: 416-432-8144
EQUIPMENT MAKE: eMachines Model: T4150Serial #: H32435412443
SERVICES TO BE PERFORMEDDATE ASSIGNED STATUS
Install Windows 2010 Advanced ServerMay 26 Complete
Upgrade Microsoft Office May 27 Complete
ESTIMATED COMPLETION DATE:May 30, 2011
INSPECTION DATE: May 27, 2011 INSPECTED BY: Paul Woo
PICKUP NOTIFICATION DATE:May 27, 2011
USERVIEW 6: SAMPLE 2 OF 2
SENEMAX WORK ORDERPREPARED BY: Amy SmithDATE: May 26, 2011WORK ORDER: 475
CUSTOMER: May HuangCUST#: C2048INVOICE #: 424
ADDRESS: 124 Main St, MarkhamEmail:
Home Phone: 416-432-1233Cell Phone: 416-432-8144
EQUIPMENT MAKE: HP Model: x3350Serial #: T43435454545
SERVICES TO BE PERFORMED DATE ASSIGNED STATUS
Install Hard DriveMay 28Complete
ESTIMATED COMPLETION DATE:May 30, 2011
INSPECTION DATE: May30, 2011 INSPECTED BY: K Stewart
PICKUP NOTIFICATION DATE:May 30, 2011
USERVIEW 7 (2 EXAMPLES):
SENEMAX COMPUTER ASSOCIATES RECEIPTDATE: May 30, 2011FOR INVOICE#: 425RECEIPT#: 301
CUSTOMER: May HuangCUST#: C2048
ADDRESS: 124 Main St, MarkhamEmail:
Home Phone: 416-432-1233Cell Phone: 416-432-8144
PAYMENT AMOUNT:$591.07 PAYMENT METHOD: Cash
ACCOUNT NUMBER: EXPIRY DATE:
RECEIVED BY: Amy Smith, # 136
SENEMAX COMPUTER ASSOCIATES RECEIPT
DATE: May 30, 2011FOR INVOICE#: 423RECEIPT#: 302
CUSTOMER: May HuangCUST#: C2048
ADDRESS: 124 Main St, MarkhamEmail:
Home Phone: 416-432-1233Cell Phone: 416-432-8144
PAYMENT AMOUNT:$67.80 PAYMENT METHOD: MasterCard
ACCOUNT NUMBER:4095 3456 2347 1902 EXPIRY DATE:11/13
RECEIVED BY: Amy Smith, # 136