Assignment #4

ITEC1010  3.0 A :Fall 2002

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.

MS Access

Scenario:

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.
Cid L_name F_name Address Orders
'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:

  1. Sales
  2. Customers
  3. Tours

Table Design:

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.

The Data:

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.

Queries:

  1. Design a query that will show all tours ( ID and Tour Title) that have a price less than $2000.00.
  2. Design a query that will show all tours ordered by customers in the GTA (Area codes 416 and 905) Query should return Tour ID, Tour Title, Tour price, Customer ID, Last name, and Area code.
  3. Design a query listing all customers related to invoices with zero deposit. Query should return  Customer ID, Last name, Area code, Deposit, Invoice number, and Tour ID.
  4. Design a query that will show all tours ordered by customers in the GTA who have ordered tours with a price over $2000.00 and have made 0 deposit. The query should return Tour ID, Tour Title, Tour price, Customer ID, Last name, and Area code. This query must be based on the third query

Forms:

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.

Reports:

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 ]

  1. [ 5 ]   A typed and neatly formatted title page, showing your name, course, section, student number, and date.
  2. [ 15 ]  A print-out of the structure of the Tour_Sales table. 
  3. [ 15 ]  A print-out of the three tables in datasheet view. These display the data in the tables as it was entered.
  4. [ 40 ]  A print-out of all queries (datasheet view). Underneath each query, hand write the criteria used under the appropriate field. *** NOTE: IF THIS CRITERIA IS NOT INCLUDED, THIS PART OF THE ASSIGNMENT WILL RECEIVE ZERO. ***
  5. [ 15 ]   A print-out of the form
  6. [ 10 ]   A print-out of the report.