CIS 310 Assignment Two Instructions

CIS 310 Assignment Two Instructions - CIS 310 –...

Info iconThis preview shows page 1. Sign up to view the full content.

View Full Document Right Arrow Icon
This is the end of the preview. Sign up to access the rest of the document.

Unformatted text preview: CIS 310 – Assignment 2 Database Design and Implementation Project Papa Joe’s Pizzeria Dr. Manson; Winter 2010 DUE DATE: Tuesday, March 1, 2010 by 5 p.m. Academic Integrity Note: this is an individual assignment. Collaboration with another student is not allowed. Any sharing of work or factual data between students constitutes academic dishonesty as defined in the University Catalog. All work must be original for this project in this quarter. You may not use work from other classes or courses to fulfill this assignment. Project Prerequisites: Prior to starting to work on Assignment 2, students must complete the following tasks: 1) Review Access 2007 online tutorials (in Blackboard under Assignment Two) to learn the essential features and techniques of Access 2007. Project Objectives: This project is designed to give you a better understanding of how data can be organized into a relational database and then used to provide answers to management queries and to extract information for reports. The software we will be using for Assignment 2 is Microsoft Access 2007, which is a part of the Microsoft Office 2007 Suite. Part One: Preparing for Assignment 2. Prior to starting on Assignment 2, students must review Access 2007 tutorials to learn the essential features of MS Access 2007. Note to students: You should not proceed to Task 2 unless they have completed Task 1. Part Two: Create an empty database file. Launch MS Access 2007 Create a new (blank) database file and name the file XYPizzeria (where “X” is the first initial of your first name, and “Y” is the first initial of your last name). [for example: a database file for Alexander Smith will be named ASPizzeria). Save the database file to your hard (or flash) drive. Note that when Access saves the file, it adds an extension .aacdb. Thus, the full name for your database file will be – XYPizzeria.aacdb (where “X” and “Y” initials are as described above). 1 Part Three: Creating a Database for Papa Joe's Pizzeria Papa Joe's Pizzeria has been storing information about their customers and drivers in Excel spreadsheets. It has become very cumbersome for them to manage. They currently have no way of identifying returning customers or gathering statistical data to use when sending out promotional mailers. Papa Joe's has asked you to help them design an Access database solution that would store their data efficiently. The following are some general guidelines about the type of database that Papa Joe's would like: 1.The database should have two tables, named: CustomerData and DriverData. 2.Use the data from the PapaJoeCustomerInfo.xlsx file for the customer data table. Include information about the customer's name, address, city, state, zip code, phone number, date of order, price of order, and driver number in the table. 3.Use the data from the PapaJoeDriverInfo.xlsx file for the driver data table. Include information about the driver's number, name, and license plate number in the table. 4.Do not assign a primary key in the customer data table because there could be multiple records existing for each customer. 5.In the driver data table, assign the driver number as the primary key since it would be unique for each driver. Part Four: Creating a Query for Papa Joe's Pizzeria Now that the management for Papa Joe's is familiar with the database that you created for them in the projects for Chapter 1, they would like to be able to perform the following tasks: 1. A query of a portion of their customer information in ascending order by city. 2. A query of a portion of their customer information in descending order by the date of order. To complete this project, you will need to create two new queries and then view the results of the queries. In each query, include the following information about the customers: name, address, city, state, zip code, phone number, and date of order. Part Five: Calculating Statistics for Papa Joe's Pizzeria Using Grouping Papa Joe's would like to find out the average amount that customer in each city has spent for their orders. To do this, create a query that groups the records based on the City field. Finally, find the average of the PriceOfOrder field. Part Six: Updating the Structure of the Papa Joe's Pizzeria Database Upon further analysis of the Papa Joe's database, management decides that they would like to include more information in the Drivers table. They feel that this would be the ideal location to store 2 information, such as the address and license number of the drivers. They also decide that they would like a field added to track the date of the last time that the driver's motor vehicle record was verified. This needs to be done because of a new corporate policy that indicates each driver needs to have their driving record checked at least once a year to make sure there are no moving violations. To complete this project, you will need to download the data files and do the following: 1.Add a DriverLicenseNumber field to the DriverData table 2.Add an Address field to the DriverData table 3.Add a City field to the DriverData table 4.Add a State field to the DriverData table 5.Add a Zipcode field to the DriverData table 6.Add a PhoneNumber field to the DriverData table 7.Add a MVRCheckDate field to the DriverData table 8.Using the PapaJoeDriverInfo.xlsx file, enter the appropriate data for each of the above fields. Part 7: Reports Task7. Create two reports for Papa Joe’s Pizzeria using Report Wizard feature of Access 2007. Task 7A. Report 1  ­ Customer information in ascending order by city. Task 7B. Report 2  ­ Customer information in descending order by the date of order. Each report should include the following information about the customers: name, address, city, state, zip code, phone number, and date of order. Each reports should include a appropriate title and column headers. Each report should be professionally formatted. Assignment 2 Deliverables: You will turn in one file and a printout of reports for Assignment 2. The file will be an electronic, working copy of your database that meets the criteria specified in Parts 1 ­6 (see above). The file will be in Access 2007 format (.aacdb) and will contain all Access objects in their final form for this assignment. The file will be submitted through Blackboard. The reports will be turned in with a clear cover binder and include a cover sheet with your name, class, quarter, section and assignment description. 3 ...
View Full Document

Ask a homework question - tutors are online