Database Project Assignment
Firm Background
Sally's Hardware and Massage Emporium, LLP (SHAME) has asked you to construct a database application to manage their hardware sales and suppliers.
Five tables have been identified to manage the operation:
Customer: (Customer-ID, Customer-FName, Customer-LName, Customer-Address, Customer-City, Customer-State, Customer-Zip, Customer-Phone)
Sale: (Sale-ID, Customer-ID, Item-Code, Employee-ID, Date-of-Purchase, Quantity-Purchased, Sale-Price-Each, Payment-Type)
Inventory: (Item-Code, Item-Name, Item-QOH, Item-ROP, Item-Supplier-ID)
Supplier: (Supplier-ID, Supplier-Name, Supplier-Phone)
Staff: (Employee-ID, Employee-FName, Employee-LName,Employee-Job-Class, Employee-Commission Rate)
The business rules we know are:
1. Each sale will be for only one hardware item from our inventory.
2. Each sale is credited to only one salesperson.
3. Each sale is to only a single customer.
4. Some staff members make no sales and only staff members designated as a salesperson can make a sale. [Job Classes = Sales, Clerk, Masseuse, Stocker, etc.]
5. Each hardware item has a single supplier but some suppliers provide multiple hardware items for our inventory.
6. SHAME keeps track of potential customers before their first sale to the customer.
7. Their regular customers have made many sales over time.
8. Massage customer's names are never recorded.
9. Items are purchased and put into inventory before there are any sales for that item.
10. Commission rates vary from 8% to 12%.
11. Our masseuses do not record their sales.
12. Our store is open Monday through Saturday only so there will be no Sunday sales.
System Requirements:
1. Create the tables in Access. Make sure the lengths and data types for each attribute is appropriate for the data to be stored.
2. Link the tables in the relationships window under the database tools tab
3. Create a form to update the Customer table.
4. Populate the Customer table with at least ten records. Since management wants to have a mailing list for advertising, over half of these customers should not have any sales [see #11 & 12 below].
5. Create a form to update the Staff table.
6. Populate the Staff table with at least five records including names from your team, staff members should have various job class codes, commission rates for Employee-Job-Class = Sales should be either 8% or 10% or 12%. [Job Classes = Sales, Clerk, Masseuse, Stocker, Manager, etc.]
7. Create a form to update the Supplier table.
8. Populate the Supplier table with at least five records.
9. Create a form to update the Inventory table. This form is to have a list box from which the user can select the supplier.
10. Populate the Inventory table with at least ten records. At least three of these items are to be supplied by the same supplier
11. Create a form to update the Sale table. This form is to have three list boxes from which the user can select the customer, the item, and the sales staff member.
12. Populate the Sale table with at least ten records with multiple sales on different dates to the same customers.
13. Create a query that lists the supplier's name and the name of the itemsthey supply sorted by supplier then by item.
14. Use the query above to create a "Supplier and Parts" report grouped by supplier with both Supplier-Name and Item-Name sorted in ascending order.
15. Create a query to compute commissions. The query should include the staff member's first and last names,the item name, the quantity purchased, the sale price for each item purchased, the total of each sale, and the commission amount for each sale.
16. Create a "Commission Report" using the query above. The report is to be in stepped format, sorted by last name, first name, and item name, subtotals and report totals for the total of each sale and the commission amount.
17. Create a query to identify those inventory items that need to be reordered, that is, those items where the quantity on hand is less than the reorder point. This query should also include the amount of shortage and the supplier information, name and phone, so a reorder can be made. Make sure that there is sufficient data in the inventory table to clearly show the query works properly, that is, it only shows the parts that need to be ordered.
18. Create a "Reorder Report" using the query above. The report should be on stepped format grouped by supplier and sorted by item name with the shortage amount.
19. Create a "Main Menu" form from which all other forms and report previews are opened. All reports are to be opened in preview mode [save the trees].
20. All forms and reports are to have the same "look and feel" for the user. Form navigation should be with on screen buttons. Forms should open to a new record.