Pt2520 Project Description: Vince’s Vinyl Scenario
Pt2520 Project Description Project Vince’s Vinyl Scenario Vince Roberts
Vince Roberts runs a vintage record shop in the University district. His shop sells 45s, LPs, and old 76 RPM records. Most of his stock is used. He buys used vinyl from customers or finds them at yard sales and discount stores. Vince records the conditions of the vinyl and covers when he buys.
Purchases are rated mint, good, fair or poor, depending on their condition. Vince doesn’t buy poor-quality albums unless they are rare. Records that Vince doesn’t want to buy can be left behind in the shop to go in the 5/$1 bin. He does sell some new albums that are released on vinyl. Most customers get discounts on their purchases.
Until now, Vince has kept most of his inventory either in his head or in a spiral notebook he keeps behind the sale counter. But his inventory and his business have grown to where that is far from sufficient. Vince is looking for someone to build him a database that will track the extent and condition of his album inventory. He wants to keep better track of all purchases of albums and their sales. He also wants to determine the value of his inventory.
The database Vince wants should be able to track where, from whom, and for how much he purchased his stock. Vince also needs to track his sales. He is often not sure how much money he has spent and how much he has made. In addition, Vince would like to allow his customers to make special requests and notify them when their requested item comes in. The database should be able to track customer requests.
The database will provide reports on total purchases and total costs for various periods of time. It will also generate an email list for notifying customers of new sales, new albums of interest, and other promotions. Currently, Vince works alone but he is planning to hire a few people to help him out with his shop. Vince has very few security concerns for his database. However, he feels that if he starts collecting customer information, that data should be secured.
Vince doesn’t have complex records but here are a few samples:
- Album Purchases:
- Date: 5/14/2009
- Seller Name: John Raymond
- Seller Phone: 206.555.2352
- Album Notes: Rubber Soul Amer. Not British. 2nd edition, good
- Condition: Sleeve fair
- Price Paid: 4
- Album Sales:
- Date: 5/12/2009
- Customer: John Larson
- Album: Dylan, Blond on Blond
- Price: 19.60
- Tax: (not specified)
Paper For Above instruction
This paper presents a comprehensive analysis and design approach for developing a relational database system tailored to Vince Roberts' vintage record shop. It encompasses the gathering of business requirements, defining business rules, creating an Entity Relationship Diagram (ERD), and establishing security measures. The goal is to facilitate inventory management, sales tracking, customer relations, and security, aligning with the shop's evolving needs.
Introduction
Vince Roberts' record shop specializes in used and new vinyl records, including 45s, LPs, and 76 RPM discs. The shop's growth necessitates a structured database that can handle inventory, sales, customer requests, and enhance operational efficiency. Developing this database involves understanding the business processes, gathering requirements, defining rules, designing the schema, and ensuring security measures are in place.
Gathering Business Requirements
The core requirements for the database include tracking inventory details such as purchase date, seller information, album condition, and pricing. It must record sales details like customer information, album details, sales date, price, and applicable taxes. Additionally, the system should manage customer requests, notify customers of new stock, and generate periodic sales and purchase reports. The security aspect involves protecting customer data and restricting unauthorized access.
Stakeholders involved include the shop owner (Vince), customers, employees (future), and possibly suppliers. The system must fulfill their various information needs, notably inventory tracking for Vince, customer management for staff, and secure data handling.
Identified Issues with the Current System
- Manual record-keeping leads to data loss and inefficiency.
- Difficulty in generating comprehensive reports for sales and inventory.
- Lack of real-time inventory updates and visibility.
- Inability to effectively manage customer requests and notifications.
- Security vulnerabilities in handling customer information.
Business Rules
- Only albums rated mint, good, or fair are purchased unless rare.
- Records with poor condition are only bought if they are rare.
- Customers receive discounts based on membership or promotion.
- Customer requests must be tracked and used to notify customers when items arrive.
- All purchase and sale transactions are recorded with date, amount, and involved parties.
- Inventory value is calculated based on purchase price and condition adjustments.
Entity Relationship Diagram (ERD) Design
Based on requirements, the primary entities include Albums, Purchases, Sales, Customers, Sellers, and Requests. Each album has attributes such as AlbumID, Title, Artist, ReleaseYear, Condition, Notes, and Price. Purchases and Sales are linked to albums and to their respective entities (Sellers and Customers). Requests are related to customers and albums.
Normalization of the ERD follows the first three normal forms to eliminate redundancy and dependency issues. For example, seller information is stored separately from purchase records, and album details are stored in a dedicated entity.
Security Plan
The security plan involves implementing role-based access controls (RBAC) in MS SQL Server. Users are assigned roles such as Administrator, Staff, and Customer, with varying privileges. Authentication mechanisms include login credentials, possibly integrated with Windows Authentication.
Authorization governs access to data, ensuring customers cannot view or modify purchase data, while staff can manage inventory and sales. Views are created to restrict data exposure, and stored procedures manage data operations securely.
Threats include unauthorized access, data breaches, and internal misuse. The plan includes regular security audits, encrypted connections, and secure login policies.
Preliminary disaster management involves regular backups, recovery procedures, and access control policies to minimize data loss during incidents.
Conclusion
The designed database system will streamline Vince Roberts' inventory and sales processes, enhance customer management, and ensure data security. Proper normalization and security protocols will support growth while safeguarding sensitive information, thereby contributing to the long-term success of the shop.
References
- Relational Database Design and Implementation. Pearson Education.