This project contains a database of an invented musical instruments store. Advisory: the data, although invented, should not be used for malicious purposes.
The project documents are divided into the following files:
- Folder with scripts for each of the created users (these should be created from the ADMIN document).
- .mwb file of the database model.
- .xlsx file of the spreadsheet from which the data was extracted and normalized.
- ADMIN: includes the design script, inserts, and user management.
- FINANCIERO: scripts corresponding to the user who manages and records purchases, suppliers, and performs data analysis.
- VENDEDOR: scripts corresponding to the user who manages inventory, records sales, and manages customers.
A family-owned business selling musical products with a physical store wants to update their database as they are experiencing several issues with their business management. Their old way of storing sales data is neither efficient nor secure. Therefore, they decide to hire a database developer to create a database based on samples from the latest January month-end record in an Excel document. This will make it easier for them to query their purchases and sales for better tracking. The document is divided into three parts: one for storing the available stock of products, another with a monthly sample of products purchased from various suppliers, and another with customer sales.
They offer a range of musical instruments, MIDI devices, and audio equipment. Their brands include JBL (digital audio), VOX (amplifiers and accessories), Babilon (acoustic and electric guitars, electric basses, and strings), and DrumCo (drums and drumsticks).
The products are purchased at 50% of their value, based on agreements with suppliers: Drum City, Hermes Music Club, and Top Music. They are sold at 150% to achieve a 100% profit margin and cover their fixed expenses.
The company divides tasks into two roles. One family member takes on the role of a financial manager (sometimes also acting as a salesperson) responsible for tracking purchases and performing data analysis. Other family members handle product sales and stock calculations.
Additionally, the company aims to achieve the following objectives with the implementation of this database:
- Improve inventory management efficiency and accuracy: difficulties in maintaining proper inventory tracking sometimes result in stock shortages or excess inventory.
- Optimize sales management: ease of tracking sales and revenue accurately.
- Simplify purchasing from suppliers: maintain proper tracking of purchases from suppliers to better manage inventory and analyze product expenses.
- Facilitate decision-making: provide relevant information on inventory, sales, revenue, and costs for more informed and effective decision-making.
- Enhance data security and protection: implement security measures to protect the information stored in the database.