CJ Autos
Introduction
CJ Autos is an independent used car dealership. Most of CJ Autos’ customers are either students or households looking for a second car. The dealership purchases vehicles at auto auctions, through agreement with several new car dealerships, and as trade-ins or direct purchases from private individuals. The dealership specializes in buying and selling a limited number of makes and models. Appendix A provides a list of those makes and models.
In addition to CJ Hoover, the owner, CJ Autos has a staff of 10 salespersons and a secretary. The salespersons are paid a small salary, but the large part of their income comes from commissions. There are two types of commissions. The sales commission, which varies according to the salespersons’ experience, is a percentage of the sales price of the vehicle. The mark-up commission is the difference between the dealer cost and the sales price. The dealer cost is the amount paid for a vehicle. In the case of trade-ins, the dealer cost is the estimate of the vehicle’s wholesale value. All sales negotiated by the sales staff must be approved by Mr. Hoover. Much of the salespersons’ commissions are based on the mark-up on the cars they sale. This help control the staff’s incentive to squeeze the profit margin in order to make a sale. The mark-up commission is the same for all salespersons, 10%.
Up to now, all records have been kept in manual form. However, the scale of operations has expanded to the point where it is very hard for Mrs. Ann Shaffer, the secretary, to keep the books up to date. Recently Mr. Hoover bought a Desktop PC and sent Mrs. Shaffer to a Microsoft Access workshop at the local college. The PC comes with the Windows Vista operating system and the Microsoft Access 2007 program. Mr. Hoover, now, wants to develop an Access database to make it easy for Mrs. Shaffer to manage the records. Mr. Hoover hires you to develop the database.
Requirements
The database must keep records about the staff, as well as information about the cars inventory, the sales, and the customers who purchased cars from CJ Autos among other things. Through your discussion with Mr. Hoover, you determined some of the major requirements for the database. Mr. Hoover said he would like the database to provide a weekly report of the vehicle inventory. He would like the report to be sorted by the make of car so he can tell by just looking what proportion of the vehicle inventory each make represents. This information would help him determine which cars to bid on at the auto auctions he attends. Mr. Hoover also indicates that he would like to be able to retrieve (through a query) a list of cars in stock of a particular make and model. This would help him respond to interested customers’ phone call. He would like the query to show all the information about the vehicle inventory, but the query should ask him to type in the make he is interested in before showing the data.
Through your discussion with Mrs. Shaffer, you found out that she wants to be able to create a query that show information about the sales along with the salespersons’ two types of commissions. She also wants another query that shows pretty much the same kind of information along with a calculated field that shows the total of the two types of commissions. As you discuss the specifics of the data collection process with Mrs. Shaffer, you realized data about the cars inventory are recorded at two distinct times. First, descriptive information (Inventory Number, make, model, year of the vehicle, purchase date, dealer cost) is recorded at the time the vehicle is purchased. Second, additional information is recorded when a vehicle is sold. This includes the sales date, the sales price, the salesperson’s name, the purchaser’s name. Mrs. Shaffer also drew your attention on the fact that the salespersons’ commissions’ rate needed to be record in staff’s book (i.e. the staff table).
Mrs. Shaffer will be the main user of the database. Once the initial database is created by you, she will be the one who will update the tables, generate reports, and run the queries to pull out any ad-hoc information needed by Mr. Hoover or the sales staff.
Your Assignment
Analyzing data needs and developing the conceptual model
1)Based on the requirements and some of the information provided in the Creating the database and implementing the tables’ structure section below, you should carefully identify at least five entities that are central to the CJ Autos’ database. You should use the normalization techniques learned in class in order to design the database, making sure that all tables are in 3NF. For each entity/table, you should name its attributes/fields along with their data types and other properties. For that, you may use the template provided in Appendix B. Make sure the following rules/guidelines are applied: (1) no people’s names or telephone numbers are used as a primary key; (2) for each entity/table, whenever possible and necessary, you must determine and add the fields that will establish a relationship with another table; (3) make sure that all tables are normalized (3NF); (4) the size of the Text data fields must be limited to the minimum needed to store the data, that means no 255-character long Text fields.
Creating the database and implementing the tables’ structure
2)Create the FirstLastAutosdatabase (where First and Last are your first and last names)with the five (or more) tables that you have identified. Add at least 20 records per table, except for the table about the staff which should have as much records as the case introduction suggested. All purchase dates and sales dates must be in the year 2010. Make sure that you have John Teller and Ann Ketler among the salespersons.
3)Make sure that the tables include the foreign keys needed to establish relationships, and then create the relationships between the tables. Do not forget to enforce referential integrity with cascade updates.
4)Make sure you have set the input mask for key attributes like phone numbers in each of your tables.
5)You must set asset a validation rule for the make of the cars along with a validation text. The rule should be set in a way that only the make of cars sold by CJ Autos could be entered by the users of the database.
6)Change the Model field in the table about sales to make it a Lookup field that looks up cars’ models in a table about Makes & Models. If you do not have such a table, you need to create one. Note: If you got a message about deleting a relationship in order for you to be able to make the change, you can open the relationship window, and delete the appropriate relationship
Creating the forms
7)Create a simple data entry form (without subform) for each of the tables. It is up to you to choose the layout and the style, but you must use the same layout and the same style for the forms. You must insert the CJ Autos logo shown on the top of the first page of this assignment to the forms. Note that the same logo is available in the Notes’ section of the course web site under the name CJAuto.jpg. The image should appear on the left top side of the forms. You must also use the Label tool to add your first and last names to each of the forms. You should use large enough font size for easy reading.
8)Create a form including a main form with a subform for entering data about the sales and the staff members who closed the sales’ deals (i.e. the salespersons). It is up to you to choose the layout and the style, but you must use the same layout and the same style as the simple forms. You must insert the CJ Autos logo shown on the top of the first page of this assignment to the form. Note that the same logo is available in the Notes’ section of the course web site under the name CJAutos.jpg. The image should appear on the left top side of the form. You must also use the Label tool to add your first and last names to each of the forms. You should use large enough font size for easy reading.
Queries and reports
9)Create the report needed by Mr. Hoover as described in the Requirements section. You must use the same layout and the same style for this and any otherreport you create.The report must have calculated field(s) -sum and average at least. Call the report Weekly Inventory, and make sure the report shows the company’s logo on the top-left side.
10) Create another report based on two tables. Again, you must use the same layout and the same style for this and any otherreport you create. The report must show key information about the car inventory along with the identification numbers and names of the salespersons that closed the sales’ deals. The report must be sorted by the make of the cars. The report must have calculated field(s) -sum and average at least. Name the report Cars Sales, and make sure the report shows the company’s logo on the top-left side.
11)Create another report that involves three tables from your database. Again, you must use the same layout and the same style for this and any otherreport you create. Name the report Three-Table Report, and make sure the report shows the company’s logo on the top-left side.
12)Create the query needed by Mr. Hoover as described in the Requirements section. Name the query Cars in Stock.
13)Create the two queries needed by Mrs. Shaffer as described in the Requirements section.Name the queries Shaffer Sales and Shaffer Calculated.
14)Create a query that shows information (like inventory #, make, model, and year)about cars sold by John Teller or Ann Ketler between January 1, 2010 and November 30, 2010. Name the query Teller Ketler Sales. Hint: There is more than one table involved.
15)Create a query that will show all sales data along with the dealer cost and the profit margins. There are more than on tables involved, and you may need to add a calculated field.Name the query Sales Details.
Project Submission:
The assignment submission period is from 12/1 to 12/8. Email me () the following as attachment:
-the database file, that is,your FirstLastAutos database (where First and Last are your first and last names)
The assignment must be emailed no later than Wednesday12/8/2010 at 5:00 PM. DO NOT SEND YOUR ASSIGNMENT BEFORE 12/1/2010.
Make sure that you have a copy of your database file on your flash drive.
Appendix A: Makes and models sold by CJ Autos
Make / Model / Body type / Engine / TransmissionFord / Econoline
Escape
Escape-i
Explorer
Expedition
Expedition-L
F-Series / Van
SUV
SUV
SUV
SUV
SUV
Truck / V6
V6
V6
V6
V8
V8
V8 / Automatic
Automatic
Manual
Automatic
Automatic
Manual
Automatic
GMC / Sierra
Envoy
Acadia / Pickup
SUV
SUV / V8
V6
V6 / Automatic
Automatic
Automatic
Chevrolet / Camaro
Malibu
Malibu-e
Monte Carlo / Sedan
Sedan
Sedan
Sedan / V6
V4
V6
V6 / Automatic
Automatic
Automatic
Automatic
Pontiac / Grandam
Grand Prix
Grand Prix-2
Bonneville / Sedan
Sedan
Sedan
Sedan / V4
V4
V6
V4 / Automatic
Automatic
Automatic
Automatic
Honda / Accord
Accord-v
Civic
CRV / Sedan
Sedan
Sedan
SUV / V4
V6
V4
V4 / Automatic
Automatic
Automatic
Manual
Toyota / Camry
Camry-L
Corolla / Sedan
Sedan
Sedan / V4
V6
V4 / Automatic
Automatic
Automatic
AccessProjectF10.doc 1/7
Appendix B: Entities’ structure Student Name:
Entity name:Attribute / Data type / Field size / Format / Input mask / Default value / Validation rule
Note: The entity identifier (or primary key) must be underlined.
Entity name:Attribute / Data type / Field size / Format / Input mask / Default value / Validation rule
Note: The entity identifier (or primary key) must be underlined.
Entity name:Attribute / Data type / Field size / Format / Input mask / Default value / Validation rule
Entity name:
Attribute name / Data type / Field size / Format / Input mask / Default value / Validation rule
Note: The entity identifier (or primary key) must be underlined.
Entity name:Attribute name / Data type / Field size / Format / Input mask / Default value / Validation rule
Note: The entity identifier (or primary key) must be underlined.
AccessProjectF10.doc 1/7