We want to develop dbms (using MS access) for our lube oil store. ??" It tracks customers, orders, services and items.
Essentially a customer can bring his car to our store and can get either of 3 services ??" basic (oil change - $[url removed, login to view]), Full (oil change + radiator flush and fill -$[url removed, login to view]), Premium (full+ transmission $[url removed, login to view]). We also sell products like wipers, car fresheners, steering wheel covers etc. So an invoice can contain service and one or more products.
Of course customer can own more than one car, but can only bring one car at a time. Also we plan to incorporate discount program, where every 10th order placed by the customer gets $20 off on the order
1) All deliverables will be considered "work made for hire" under U.S. Copyright law. Employer will receive exclusive and complete copyrights to all work purchased. (No 3rd party components unless all copyright ramifications are explained AND AGREED TO by the employer on the site per the worker's Worker Legal Agreement).
Using Visio or other data modeling tool:
· Draw an enterprise diagram to present the business data
· Draw an enhanced entity relationship diagram to show the business unit’s database and business rules for its daily operation.
· Convert the diagrams into relations
· Normalize the relations
· Present the relations for the whole database.
Using Microsoft Access:
· Create at least five basic tables for the unit’s database. The normalized tables should be well defined with keys, referential integrity, data type, index, and relationships with other tables.
· Store actual data, or reasonable mock data that you create, into tables with at least 10 records for each table
Write queries against the database you created to answer the following questions:
A. Number of product types stocked in the business?
B. Total volume of products stocked in the business?
C. Inventory on-hand for a specific product?
D. Order history for a particular customer?
E. Invoice generated ??" depicting customer, service and or product
F. Invoice generated ??" depicting customer, service and or product with discount
Windows Vista/7; MS Access/Visio: 2007 or better