Database Project A. Requirements Analysis Problem: Create a database to help a User sell used vinyl records. Information Needs of Owner 1.) Add Inventory for sale - Needs database to store all information related to each item (Album title, Artist, Label, Medium, Genre, country released in, release date) 2.) Needs to track condition of each used item and it’s price (Cover/Sleeve condition, Vinyl/Media Condition, Price) 3.) Needs to track location of each item in the store (Bin/Box location) 4.) Needs to track shipping costs, weights and type by country to insure proper shipping costs are applied to each order 5.) Track customers and orders to know what customers have purchased in the past ??" let them know if you have similar items for sale that they may have missed. 6.) An easy to use search function which will locate what user is looking for by using Title, Artist, Genre B. Conceptual Design ??" Entity-Relationship diagram ??" see Excel Spreadsheet Relationships ??" Excel Spreadsheet C. Physical Design - Create tables and columns from entities and attributes of your ER Diagram: ??" Excel Spreadsheet SQL create table statements for all tables, keys and referential integrity: Excel Spreadsheet. D. Implementation ??" Will use Microsoft Sequel Server 2005 for the database. Use Microsoft Visual Studio 2005 for the interface. The interface would be in Visual C#.Net 2005 and include the following: 1.) A login page. a.) Admin login will take you to Products details of add/modify and delete. b.) Customer login will take you to Customer details of add/modify products in the shopping cart - used datagrids. E. For the triggers listed in the requirements I would like three: One that would delete a record in the ALBUM table after it was shipped. The second would calculate “OrderTotal?? after shipping costs were inserted in the SHIPPINGCOST table. A third would calculate OrderSubtotal based on the number of records purchased.
Attached the conceptual and physical design of database, and some SQL code which may need to be fixed