CJ AutosIntroductionCJ Autos is an independent used car dealership. Most of CJ Autos’ customers are either studentsor 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 fromprivate individuals. The dealership specializes in buying and selling a limited number of makesand 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. Thesalespersons 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 thesalespersons’ experience, is a percentage of the sales price of the vehicle. The mark-upcommission is the difference between the dealer cost and the sales price. The dealer cost is theamount paid for a vehicle. In the case of trade-ins, the dealer cost is the estimate of the vehicle’swholesale value. All sales negotiated by the sales staff must be approved by Mr. Hoover. Muchof the salespersons’ commissions are based on the mark-up on the cars they sale. This helpcontrol the staff’s incentive to squeeze the profit margin in order to make a sale. The mark-upcommission is the same for all salespersons, 10%.Up to now, all records have been kept in manual form. However, the scale of operations hasexpanded to the point where it is very hard for Mrs. Ann Shaffer, the secretary, to keep the booksup to date. Recently Mr. Hoover bought a Desktop PC and sent Mrs. Shaffer to a MicrosoftAccess workshop at the local college. The PC comes with the Windows Vista operating systemand the Microsoft Access 2007 program. Mr. Hoover, now, wants to develop an Access databaseto make it easy for Mrs. Shaffer to manage the records. Mr. Hoover hires you to develop thedatabase.RequirementsThe 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. Throughyour discussion with Mr. Hoover, you determined some of the major requirements for thedatabase. Mr. Hoover said he would like the database to provide a weekly report of the vehicleinventory. He would like the report to be sorted by the make of car so he can tell by just lookingwhat proportion of the vehicle inventory each make represents. This information would help himdetermine which cars to bid on at the auto auctions he attends. Mr. Hoover also indicates that hewould like to be able to retrieve (through a query) a list of cars in stock of a particular make andmodel. This would help him respond to interested customers’ phone call. He would like the queryto show all the information about the vehicle inventory, but the query should ask him to type inthe make he is interested in before showing the data. 1/7Through your discussion with Mrs. Shaffer, you found out that she wants to be able to create aquery that show information about the sales along with the salespersons’ two types ofcommissions. She also wants another query that shows pretty much the same kind of informationalong with a calculated field that shows the total of the two types of commissions. As you discussthe specifics of the data collection process with Mrs. Shaffer, you realized data about the carsinventory 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 ispurchased. Second, additional information is recorded when a vehicle is sold. This includes thesales date, the sales price, the salesperson’s name, the purchaser’s name. Mrs. Shaffer also drewyour attention on the fact that the salespersons’ commissions’ rate needed to be record in staff’sbook (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 outany ad-hoc information needed by Mr. Hoover or the sales staff.Your AssignmentAnalyzing data needs and developing the conceptual model1) Based on the requirements and some of the information provided in the Creating thedatabase and implementing the tables’ structure section below, you should carefullyidentify at least five entities that are central to the CJ Autos’ database. You should use thenormalization techniques learned in class in order to design the database, making surethat all tables are in 3NF. For each entity/table, you should name its attributes/fields alongwith their data types and other properties. For that, you may use the template provided inAppendix B. Make sure the following rules/guidelines are applied: (1) no people’s namesor telephone numbers are used as a primary key; (2) for each entity/table, wheneverpossible and necessary, you must determine and add the fields that will establish arelationship with another table; (3) make sure that all tables are normalized (3NF); (4) thesize of the Text data fields must be limited to the minimum needed to store the data, thatmeans no 255-character long Text fields.Creating the database and implementing the tables’ structure2) Create the FirstLastAutos database (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 caseintroduction suggested. All purchase dates and sales dates must be in the year 2010. Makesure 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, andthen create the relationships between the tables. Do not forget to enforce referentialintegrity with cascade updates.4) Make sure you have set the input mask for key attributes like phone numbers in each ofyour 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 beentered by the users of the database. 2/76) Change the Model field in the