Database Project

For this project, you will use Microsoft Access to create a business database and application. There are four options of varying difficulty. The more challenging options are potentially worth more points. Which option you choose is entirely up to you.

To complete the assignment, you will have to create tables, queries, reports, and forms. You will also be expected to populate the database with a sufficient amount of sample data to prove that your database meets the specifications with respect to reporting and data entry.

When developing the system, you should come as close as you can to meeting the specifications. Due to technical or time limitations, it is not always possible to figure out how to exactly meet every last requirement a business might have. It is usually still worthwhile to have a system come close and provide most of the capability desired.


Submitting the Assignment

You must turn in your assignment via EMAIL. Since Access files can be large, I recommend that you ZIP the file before EMAILing it.

Name both the MDB and ZIP files using your last name, first initial, and the option number. For example, if your name were Hugh Jasse and you were submitting the first option, the ZIP file should be named "jasseh_1.ZIP".

Please use this address: rfhomework@katz.pitt.edu

In the subject: put the following information:

  1. Your Name
  2. The Assignment
  3. The time your section meets
  4. Which option you have chosen

Submissions without that information will be returned.


Assignment Options

Options 1, 2, and 3 are all based on the same business: Katz Klassic Kars sells antique, exotic, and other collectible used automobiles. No new cars are sold; they sell high-performance collectibles such as Porsche, Ferrari, Lamborghini, luxury collectibles such as Rolls Royce, Bentley, Mercedes, American classics from the 50's such as 55 - 57 Chevies & Thunderbirds, and muscle cars from the 60's such as GTO, Road Runner, and Z28. Cars are displayed in a showroom where they are sold by a small sales force. Katz Klassic Kars also maintains a service department to service and repair the vehicles they sell. The service department also services vehicles of this type that were not sold at the dealership.

For more information, check out the BAA that was done by Katz Klassic Kars in anticipation of these system development efforts.

Important Restriction for this Project:

Microsoft Access has database creation wizards which can be used to rapidly generate database applications of different types. These include Order Entry, Asset Tracking, Resource Scheduling, Inventory, Contact Management, etc.

You are not to use a database creation wizard to start your database. There are 2 main reasons for this. First, the purpose of this project is for YOU to design the system. Second, the databases generated do not particularly well meet the requirements of Katz Klassic Cars. You may also not use a databse creation wizard as a starting point for Option #4.

You may (and are encouraged) to use the report, form, and query wizards.

[Option 1] [Option 2] [Option 3] [Option 4]

Option 1. Mail, EMail, and Telephone Contacts

This option can earn you up to 93 points of the 100 that are available (which would be equivalent to a "A")

Create a database and application to manage a mailing/telephone/email list. It should be able to store and retrieve records to generate mailing lists, telephone call lists, fax lists, or EMail contact lists.

Specifications:

  1. Table(s) -- 60 points

    This project can be implemented in a single table. Although using more than one table is possible, it would add complexity and cause you to need more queries. You will need to store names, addresses, various phone numbers, email addresses, etc. Make sure to give yourself sufficient flexibility to deal with various name, address and phone number styles and formats.

  2. Queries and Reports (20 points)

  3. Form(s) (10 points)

    Data maintenance form to allow records to be added, edited, and deleted.

  4. Menu/Switchboard (3 points)

    Provide access to all system features

Back to Top

Option 2. Sales Commissions

This option can earn you up to 95 points of the 100 that are available (which would be equivalent to an "A").

Create a database and application to store and retrieve the information necessary to calculate sales commissions for the sales force at Katz Klassic Kars. It should have the flexibility to individually assign a different commission rate for each sales person (perhaps senior sales people would earn a slightly higher rate than those with less time on the job). The commission should be calculated as a percentage of the profit of each sale.

You will need to store information about each sale, such as the date, the item sold, the dealer's cost for the item sold, and the selling price. You will also need to store information about each sales person, including at a minimum their name and their commission rate.

Please note that the system should calculate the commissions, not the user.

Specifications:

  1. Table(s) -- 70 points

    This database will require at least two tables. Using more than two would add complexity, but could allow you to provide additional features beyond the minimum required here.

  2. Queries and Reports -- 10 points

  3. Forms -- 12 points

    Data maintenance forms to allow records to be added, edited, and deleted for sales people, sales, and any other data you are maintaining.

  4. Menu/Switchboard (3 points)

    Provide access to all system features

Back to Top

Option 3. Service Department

This is the option that could earn for you 97 (or possibly even all) of the 100 points that are available (which would be equivalent to an "A plus"). Please note that this option is considerably more complex than the others.

Create a database and application that Katz Klassic Kars could use to enhance business in the service department of the dealership. The types of information that should be available would be:

The system should include forms to allow all data in the system to be maintained.

Specifications:

  1. Table(s) -- 70 points

    You will need multiple tables for this application. They will include, but not be limited to:

  2. Queries and Reports -- 14 points

    You will have to use different selection and grouping approaches for the reports. Some of this can be done in queries, some in the reports themselves. Use your best judgement with respect to which layouts would be most useful to management. Bear in mind that it can be acceptable to generate certain "reports" as forms; make sure the data cannot be edited if a form is to be "view-only".

  3. Forms -- 10 points

  4. Menu/Switchboard (3 points)

    Provide access to all system features

  5. 3 points are held "in reserve" to reward truly exceptional ("A+") work.
Back to Top

Option 4. You Suggest the Topic

This option can earn you up to 97 points (or even all) of the 100 that are available (which would be equivalent to a "A+")

Create a database and application that does something really cool, totally awesome, or possibly even useful. You must get my approval on a topic for this option. Based on what you suggest, we will agree on the maximum possible points and the breakdown.

Point maximums:

  1. Table(s) -- 70 points

    As needed to support your requirements.

  2. Queries and Reports (14 points)

    As needed to support your requirements.

  3. Form(s) (10 points)

    Data maintenance form to allow records to be added, edited, and deleted.

  4. Menu/Switchboard (3 points)

    Provide access to all system features

Back to Top