Due Date: Full credit
4:00 pm Monday 2 December 2002
Partial credit 4:00 pm Friday 06 December.
The assignment is worth 25% of your Assignment Mark.
You may work in a study group (maximum 3 people) and turn in one copy of the assignment for the group, with all names (and student numbers) of people in the group on the assignment.
The "Go Everywhere!" Travel agency needs to reorganize data about business operations and customers. You are asked to design and implement a database to track tour sales only (not the rest of the business).
Part I: The agencynow stores the data in one Tour_Sales table. There is a row per each customer holding personal information and all orders the customer has made, as follows.
|'123'||Smith||John||6000 Bathurst St, apt#1020, Toronto, ON, H2F1J2, 416-123-4567||October 1, 2001 Pelican Bay - 2 tours
September 15, 2002 Paris - 1 tour
|'124'||Green||Kathy||245 Pharmacy Ave, Toronto, ON, M1L3G9, 416-756-5500||August 31, 2002 Milan - 1 tour|
Reorganize table Tour_Sales into first normal form. Create the table satisfying 1NF conditions using the Table Design view in MS Access. Print the table structure using Tools > Analyze > Documenter. Using the Options choice, ensure that ONLY the option "Names, Data Types, and Sizes" is selected: then click OK and print the resulting page.
Part II: Using MS Access, you should build three tables:
The table Customers should include:
The table Tours should include:
The table Sales should include:
Note: The assumption is that in each invoice a customer orders a single tour, but both a customer and a tour can appear many times (in different invoices). When ordering a tour a customer may pay a deposit.
Make sure that all fields in your tables contain atomic
data and have appropriate types and lengths. Based on data description assign
validation rules and default values if applicable.
In all the above tables, the number or the ID is the primary key. Using Tools, Relationships, add all three tables to the Relationships window, and establish appropriate referential integrity constraints.
Enter the data for the Tours. There should be at least 15 tours in this table. The prices on these tours are to range from $500.00 to $5000.00, some less than $2000.00 and some more than $2000.00. At least one should cost exactly $2000.00.
Enter the data for the Customers. Use at least 20 customers, at least 15 of whom should each buy one tour. Customers should come from at least 3 area codes (Toronto (416), Outer Toronto (905) and one other.) There are to be at least two sets of between 2 and 5 customers who share the same last name, but at least five customers must have unique last names.
Enter the data for the Sales. Enter at least 20 invoices, representing orders by at least 15 customers. At least three customers should have made no purchase, and at least three should have bought more than two tours. For the "Deposit", just create some reasonable amount. Approximately one quarter of the invoices should have 0 deposit, and some of them should be with tour prices over $2000.00. All formats should be represented, and at least two tours must be bought by more than one customer.
Design a form that will present Customers ( Customer ID, First and Last names), and will display the tours (Tour ID, Tour Title, and price) ordered on a subform.
Design a report that presents all the transactions grouped by Customers. Show customer name, address, tour title, price and the date of order. The report should be arranged alphabetically by last name, then by first name within the last name group. Spend some time formatting this to make it attractive. This is a document that would be sent outside the company and it should therefore look professional.
To Hand In: [ marks shown in square brackets ]