Sally’s Hardware And Massage Emporium LLC PA Database Projec
Sallys Hardware And Massage Emporium Llpa Database Project 2014 20
Sally’s Hardware and Massage Emporium, LLP A Database Project © 2014, 2016 & 2017 G. P. Marquis 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. 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 items they 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, with 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. 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. 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. Each table needs to have enough data to show that the queries and reports are correct.
Paper For Above instruction
Designing a Comprehensive Database System for Sally’s Hardware and Massage Emporium, LLP
The development of an effective database system for Sally’s Hardware and Massage Emporium, LLP, requires meticulous planning, implementation, and validation to align with their operational needs and business rules. This paper discusses the process of designing such a database utilizing Microsoft Access, including creating tables, establishing relationships, form development, data population, query creation, and report generation to support the company's sales, inventory, supplier management, and staff functions.
Database Tables and Their Significance
The foundation of the database comprises five core tables: Customer, Sale, Inventory, Supplier, and Staff. Each table is tailored with appropriate data types and field lengths to accommodate data such as customer personal information, sale details, inventory items, supplier contact info, and staff data. For example, the Customer table includes fields like Customer-ID, Customer-FName, Customer-LName, and contact details. Similarly, the Sale table tracks transactions with Sale-ID, Customer-ID, Item-Code, Employee-ID, Date-of-Purchase, Quantity-Purchased, Sale-Price-Each, and Payment-Type.
The Inventory table manages stock data, including Item-Code, Item-Name, Item-QOH, Item-ROP, and Item-Supplier-ID, establishing relationships with the Supplier table through Item-Supplier-ID. Staff records manage employee information such as Employee-ID, Employee-FName, Employee-LName, Employee-Job-Class, and Employee-Commission Rate, enabling role-based sales recording and commission calculation.
Implementation of Business Rules in Database Design
Implementing business rules into the database ensures data integrity and operational consistency. For instance, rules such as each sale being for a single item, credited to one salesperson, and involving one customer, are enforced through form controls and relationship constraints. The system also restricts certain staff roles like Masseuses from recording sales and manages commission rates within specified ranges (8%-12%).
Moreover, the system records potential customers prior to sales, distinguishes between regular sales, and recognizes that massage customer names are never recorded. Inventory management includes purchase before sales, with reorder points triggering reorder alerts, and report formats to highlight items needing reordering. The store's operating days influence the scheduling and date entries in sales data.
Creating and Populating Tables and Forms
Using Microsoft Access, tables are created with appropriate data types such as Text, Number, Date/Time, and Currency. Relationships are established in the Relationships window to enforce referential integrity and facilitate data consistency across tables.
Forms are designed for each table to simplify data entry and updates. For example, the Customer form allows users to add at least ten customer records, with over half not having any sales, fulfilling the marketing mailing list requirement. The Staff form includes at least five team members with varying job classes and commission rates, while the Supplier and Inventory forms are similarly structured with necessary data.
Populating Data and Using List Boxes for Selection
Data population involves entering at least ten customer records, five staffing records, five suppliers, and ten inventory items. To ensure ease of selection, forms for inventory and sales incorporate list boxes enabling users to select related records such as suppliers, customers, and staff members efficiently. For instance, the Sale form uses three list boxes to select customer, item, and staff, supporting quick and accurate data entry.
Query Development for Reporting and Analysis
Several queries are constructed to analyze data and generate reports aligned with business needs. A key query lists suppliers and their items, sorted by supplier and item, forming the basis for a "Supplier and Parts" report with grouping and sorted order. Another query calculates commissions by combining sales data with staff and item details, enabling detailed commission reports with subtotals and totals, sorted in stepped formats.
Additionally, queries identify inventory shortfalls where stock quantities fall below reorder points, including supplier details for reordering purposes. These shortfalls are presented in a "Reorder Report," grouped by supplier and sorted by item name, with shortage quantities specified.
Report Generation and User Interface
Based on the queries, reports are created to visually present data in a clear, grouped, and sorted format. The "Supplier and Parts" report enhances supplier product tracking, while the "Commission Report" details sales performance and commission earnings per employee, sorted by name and item.
The "Reorder Report" alerts management of inventory shortages, supporting procurement decisions. A centralized "Main Menu" form serves as the dashboard for navigating to all forms and reports, ensuring a consistent look and feel with on-screen navigation buttons. All forms open in new records mode, facilitating multiple simultaneous data entries.
Conclusion
Constructing a comprehensive database for Sally’s Hardware and Massage Emporium involves integrating business rules, designing normalized tables, creating intuitive forms, populating data thoroughly, and generating detailed reports. Such a system streamlines operations, enhances data accuracy, and provides valuable insights for decision-making, fulfilling the company's operational and marketing needs effectively.
References
- Hertel, M. (2019). Database Design for Mere Mortals: A Hands-On Guide. Addison-Wesley.
- Rob & Coronel. (2020). Database Systems. Cengage Learning.
- Database Systems: A Practical Approach to Design, Implementation, and Management. Pearson.