4
. You are required to build a Microsoft Access Database to meet the requirements of the scenario below.
ESC (Elite Sports Cars) is the name of a car dealer/garage specialising in the sale of expensive second-hand sports cars. Most days it has approximately 20 vehicles for sale (usually priced £10,000 upwards). Most of the cars have either been purchased from an auction or have been acquired from the general public.
The owner/manager is Ms. Lucy Speed. She is assisted by a full-time salesman, Andy Trueman and a part-time salesman Johnny Green. The basic salaries of all three (including Lucy) are quite low as they are expected to supplement it by earning bonuses on successful sales. Each bonus is calculated at 10% of the profit achieved on the sale.
Currently, all records of the cars are held in spreadsheet files on a PC. For each car is held:
a) Type.
b) Colour.
c) Registration Number.
d) Date purchased by ESC.
e) Price Paid.
f) Date Sold.
g) Selling Price.
h) Salesman responsible for the sale (i.e Lucy, Andy or Johnny).
i) Name and Address of purchaser.
j) Any Other Notes.
Lucy feels the time has come for a more sophisticated information system to help her run the business. As a first step she has decided (with some outside advice) that a simple Microsoft Access database should be developed that will consist of three linked tables (Customer, Vehicle and Salesman). The Vehicle table will contain all the data-fields listed above except that:
a) The “name and address of purchaser” will be replaced by the relevant customer-id – which will provide a link to the customer table.
b) The “Salesman responsible” will be replaced by the relevant salesman-id – which will provide a link to the salesman table.
The Customer table will consist of:
a) Customer-Id.
b) Customer Name.
c) Address.
d) Telephone No.
e) Any customer notes.
The Salesman table will consist of:
a) Salesman-Id.
b) Salesman Name.
Lucy has identified three essential reports/forms:
a) A list of all cars currently for sale.
b) A form displaying the amount of bonus earned by each sales person since a certain date (this date may be keyed in by a user).
c) A list of all past sales (again since a certain date).
Additionally, further forms are needed to help with the maintenance of all 3 tables.
END OF SCENARIO.
Thank you...
