This project will help you apply all the concepts covered in this course for a given scenario. 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. He does sell some new albums that are released on vinyl. So far, he 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.
In this project, you will review the information given in the scenario and based on that information, create the following documents pertaining to the scenario:
Statement of Work (SOW)
Business requirements and rules
ERD
Security plan for the database
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 request 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 few people to help him out with in 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
|
Seller Phone
|
Album
|
Notes
|
Condition
|
Paid
|
|
John Raymond
|
206.555.2352
|
Rubber Soul
|
Amer. Not British. 2nd edition, good Sleeve
|
fair
|
4
|
|
Marylin Taylor
|
206.555.0945
|
Led Zeppelin IV
|
Not orig. Sleeve damaged, vinyl good
|
good
|
4.75
|
|
Jennifer Louis
|
206.555.4545
|
Gift of the flower to the Garden
|
Rare Donovan, box set, box cond poor, but vinyl excellent
|
excellent
|
12.25
|
|
Laura Hall
|
206.555.2080
|
Dark Side of the Moon
|
|
good
|
4.45
|
Album Sales
|
Date
|
5/12/2009
|
|
|
|
|
Customer
|
Album
|
Price ($)
|
Tax ($)
|
Total ($)
|
|
John Larson
|
Dylan, Blond on Blond
|
19.95
|
1.65
|
21.60
|
|
Tabitha Snyder
|
America
|
5.95
|
|
|
|
Joni Mitchell, Blue
|
6.25
|
|
|
|
Joan Baez, Ballads
|
4.20
|
1.36
|
17.76
|
|
Brad Johnson
|
McCartney, Venus and Mars
|
5.00
|
0.42
|
5.42
|
|
Maureen Carlson
|
Decemberists, The Crane Wife
|
15.50
|
|
|
|
Muddy Waters
|
7.75
|
1.9
|
25.18
|