DBS201 Assignment Part 1: Normalization of User Views

Due Week of Feb 21

Check with your Instructor for specific date and/or time

(8% of final mark)

Your group of up to three people is to develop a 3NF solution for the following user views. You are to identify all main entities, attributes, and relationships between these entities (shown by Foreign Keys).

Submission Requirements:

1. Submit a 3NF solution for EACH user view in this assignment .

2. You are to show each step in your solution: UNF, 1NF, 2NF, 3NF .

3. You are to clearly mark Primary and Foreign Keys as shown in the sample below:

Table1[ column1 (PK), column2, column3, column4 (FK), column5 ]

Table2[ column4 (PK) , column 6(PK), column 7, …….. ]

4. You must hand in the Submissions Form ( below ) with your assignment.

Student Assignment Submission Form

I/we declare that the attached assignment is my/our own work in accordance with

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(s) Signature Student ID(s)

______

______


______

PLAGIARIASM and CHEATING

(Refer to section 8.7, page 6 of Seneca's Academic Policy)

(or visit: http://cs.senecac.on.ca/bullets/cheating.html )

Puck's Farm is a recreational farm that is visited by school groups. There are many activities that a visitor can participate in including petting the animals and going for rides on some of the larger animals. They have also built a small amusement park on the property with rides including a very small roller coaster and a merry-go-round.

When a school decides to visit the farm, they fill out an ACTIVITIES RESERVATION FORM so that Puck's farm can verify that the selected activities are available on the date of the requested visit. If all the activities are available Puck's farm requests a deposit of $150 to confirm their reservations.

Puck's Farm: User View 1

I. ACTIVITIES RESERVATION FORM

Puck’s Farm Reservation Request Form
Reservation # 44 .
Date Of Visit 02/17/05 .
Arrival Time 10:00 a.m. .
Name of School Our Lady of Grace .
Grade Level 4 .
Teacher Name Mrs. Scott .
Teacher Phone Number (416) 441-7777 .
Class Requested Activities: - Milking the goats .
- Washing the pigs .
- Ride horses .
- Roller Coaster .
- Pet the Sheep .
- Merry-go-round .
Extra Activity if one of
six above are not available - Ride on the donkeys .
______
Student Name Student Age Student Allergies
Stanley Chan 10 Grasses
Billy Dodds 10 Grasses
Jane Foster 10 None
Pinotta Guereva 11 None
Yan Tron 10 Hay Fever
Silvia Tyson 11 Peanuts
Total Students: 6
Average Age: 10.3 years


Puck's Farm: User View 2

II. DAILY ACTIVITIES SCHEDULE

The daily schedule of activities indicates for each activity the time at which each school has been scheduled to do that activity. The user view shows part of the schedule, specifically that part of the schedule that covers activities #10 and #11 for today. The students from one particular school stay together all day. They go to each of their scheduled activities together.

Daily Schedule of Activities Report
Puck's Farm
Activity Number / Activity / Start Time / End Time / School
10 - / Roller Coaster / 9:00 / 10:00 / Scarborough Public School
10:15 / 11:15 / Etobicoke Waldorf School
11:30 / 12:30
12:30 / 1:30 / LUNCH
1:30 / 2:30 / Newmarket and District Christian Academy
2:45 / 3:45 / J.R.Bell Elementary School
4:00 / 5:00 / Eglington Public School
11 - / Washing the pigs / 9:00 / 10:00 / Etobicoke Waldorf School
10:15 / 11:15 / Newmarket and District Christian Academy
11:30 / 12:30
12:30 / 1:30 / LUNCH
1:30 / 2:30 / J.R.Bell Elementary School
2:45 / 3:45
4:00 / 5:00 / Scarborough Public School

etc...

Puck's Farm: User View 3

Puck's Farm

Customer Report

Feb 17, 2005

Customer
No / Name / Address / Phone / Contact
Name / Date of
Last Visit
401 / Mississauga Public School / 1050 Hurontario St.
Mississauga, Ont
M5W 1Y2 / (905) 878-1234 / Anne Clarke / 2004-06-05
402 / Scarborough Public School / 12345 Markham Rd
Scarborough, Ont
M8X 3T3 / (416) 691-4321 / Jackie Relton / 2004-06-14
403 / Dixie Public School / 661 Dixie Rd
Brampton, Ont
N4P 9J8 / (905) 446-7654 / Ron Pichora / 2004-07-02
404 / J.R. Bell Elementary School / 876 Ontario St.
St. Catharines, Ont
L8V 1V1 / (905) 937-9977 / Mark Nelson / 2003-11-30
405 / Toronto
Waldorf School / 7878 Bathhurst
Toronto, Ont
M8M 4R4 / (416) 876-5432 / George Osborne / 2004-06-12
407 / Eglington Public School / 204 Eglinton Way
Toronto, Ont
L3Y 2T2 / (416) 853-8686 / Ahmed Kassim / 2004-06-27

Note: This report can be used to contact the user by mail or by phone. It may also be used by the Sales Manager to see which customers have not visited for quite some time.

Puck's Farm: User View 4

The following inventory report is used to ensure that adequate stocks of the various foods are in stock. When there is more than one supplier the report helps the buyer select the one with the lowest price.

Puck's Farm
Food Inventory Report
Parts List:
PartCode / Description / Inventory / Supplier / Cost
HA200 / Hamburger – Regular / 612 / Fresh Catering and Grocery / $0.83
Windor Food Prducts / $0.82
HA210 / Hamburger – Quarter Pounder / 515 / Fresh Catering and Grocery / $1.08
Windor Food Prducts / $1.04
HA300 / Hamburger – Half Pounder / 1006 / Fresh Catering and Grocery / $1.85
Windor Food Prducts / $1.96
C101 / Chicken Strips / 507 / Fresh Catering and Grocery / $1.40
Windor Food Prducts / $1.30
C102 / Chicken Filet / 446 / Windor Food Prducts / $1.70
Black Angus Food Prducts, Inc. / $1.80
C103 / Chicken Grill / 807 / Windor Food Prducts / $1.45
Black Angus Food Prducts, Inc. / $1.56
C104 / Chicken Balls / 303 / Black Angus Food Prducts, Inc. / $.1.70
S100 / Spring Salad – Small / 104 / Black Angus Food Prducts, Inc. / $0.88
S101 / Spring Salad – Large / 50 / Black Angus Food Prducts, Inc. / $1.32
S102 / Greek Salad – Small / 89 / Black Angus Food Prducts, Inc. / $0.98
M100 / Mustard / 509 / Prism Catering and Sales, Ltd / $1.10
Pride of T.O. Sales, Ltd. / $1.20
Asta Kosher Meals, Co. / $1.55
R100 / Relish / 632 / Prism Catering and Sales, Ltd / $1.32
Pride of T.O. Sales, Ltd. / $1.41
Asta Kosher Meals, Co., Co. / $1.65
MA100 / Mayonaise / 250 / Pride of T.O. Sales, Ltd. / $1.10
Prism Catering and Sales, Ltd / $1.20


Puck's Farm: User View 5

INVOICE

PUCK'S FARM

Invoice Number: 2005-15

Customer No: 402

Customer Name: Scarborough Public School

Customer Address: 2780 Warden Avenue,

Scarborough, ON.

R4T 1Z7

This Invoice For Your Order Number: 105

QTY / Participant / Admission ($10.00) / Food Charge / Total
28 / Grade 3 Class with 28 children / $280.00 / $145.60 / 425.60
2 / 1 teacher and one other adult / $0.00 / $18.00 / 18.00
GST (7%) / 32.25
Total / $475.85

Amount Due upon receipt of Invoice.

Note: this is the bill which is sent to the customer on the day after their visit.
Puck's Farm: User View 6


LUNCH MEAL LIST

Lunch is served to the teachers and students between 12:30 and 1:30.In order to help the kitchen staff the following report is produced. It helps them to prepare the right amount of each type of meal to be ready at 12:30. The teacher who is responsible for the class assists in giving the right order to each student , then the teacher is given his or her meal.

School / Quantity / Menu Selection / Description / Supersize / Regular
Size
Eglington Public School / 9 / 2 / Classic Combo / 5 / 4
Etobicoke Waldorf School / 5 / 2 / Classic Combo / 2 / 3
J.R.Bell Elementary School / 11 / 2 / Classic Combo / 4 / 7
Newmarket and District Christian Academy / 7 / 2 / Classic Combo / 3 / 4
Scarborough Public School / 4 / 2 / Classic Combo / 1 / 3
Eglington Public School / 8 / 4 / Chicken Filet / 4 / 4
Etobicoke Waldorf School / 5 / 4 / Chicken Filet / 2 / 2
J.R.Bell Elementary School / 3 / 4 / Chicken Filet / 3 / 0
Newmarket and District Christian Academy / 6 / 4 / Chicken Filet / 3 / 3
Scarborough Public School / 5 / 4 / Chicken Filet / 1 / 4

DBS201-051 A1 V1.0 Page1 of 7