Designing a Prototype Database for ElectronicRecycler

Overview of ElectronicRecycler's Business Operations

After College, our first IT assignment is to help a used CD, DVD and video games store called ElectronicRecycler. The store does the following:

A) rent used CDs, DVDs or video games,

B) sell (sale) used CDs, DVDs or video games to customers and

C) buy (purchase) used CDs, DVDs or video games from customers looking to get rid of their stuff.

eed to keep track of the following information:

Customers, with a customer_id, first name, last name, address, zip code, phone, email, interests.

Each Purchase (store buys the items from a customer), which has a purchase_id, date, amount, and of course all the items purchased, by which employee and from which customer.

Each Sale (store sells the items to a customer), which has a sale_id, date, amount, all the items sold, to which customer, and by which employee.

Each Rental (store rents the items to a customer), which has a rental_id, date, what items in the inventory were rented, which employee did the rental, when they are due, the rental amount required from customer and a paid? field.

A list of employees of the store (employee_id, name, address, phone, email, rank, salary). 

An inventory list, which has the type of medium (CD, DVD or video-game), the title. If it’s a CD, then the name of the artist, and the type of music, as well as the music label. If it’s a DVD, then the movie genre, the director, the hero, the heroine, the year of the movie. If it’s a video-game then the type of platform (example, playstation 2, xbox), the genre and the age rating.  Individual copies of each title need to be tracked, and linked to business transactions (sales, rentals, purchases).

We got the above description after talking to the CEO & management of ElectronicRecycler. Our next step is to use these requirements in order to build the first prototype of the database to support ElectronicRecycler.

We need to do the following for our project:

Milestone 1: Draw an ER diagram, as per conventions in class. (on paper) (30%)

Milestone 2: Correct milestone 1 & create a table schema ( on paper). (10%)

Milestone 3: Correct milestone 2, then write a SQL script file that creates the backend (create, drop and insert) on a MYSQL DBMS. (30%)

Milestone 4: Correct milestone3, then create front end Access screens including a main menu, to insert/update/delete data in all the tables in the MYSQL database. Use drop-downs for the foreign keys in the forms.  (30%)