MIS 7003, MBA MIS Core Course

MIS 7003, MBA MIS Core Course

The University of Tulsa

Professor: Akhilesh Bajaj

Database Project

Due: Mon April 8, 2013 by start of class.

Counts for 100 points

One project per Team of 2 max

------START Project Description------

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.

We need 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. Multiple copies of each title need to be tracked, and linked to business transactions (sales, rentals, purchases).

------END Description------

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:

1. Draw a DSD diagram, as per conventions in class. (on paper) (25%) Please keep track of how long it takes your team to do this (including all discussions) and report this well.

2. Create a table schema ( on paper). (10%)

Please keep track of how long it takes you for phase 2 (including all discussions) and report this well.

3. Create tables with RELATIONSHIPS in access, and populate with some sample data (at least 4 rows per table). We should make sure the data are consistent across tables. (25%)

4. Create front end access forms for each table (20%)

5. Sample access queries for the database (20%)

a. Query 1: The sales for the last one month (fields: customer_id, name, address, amount. One row per Customer in the report Order by Customer_id.)

b. Query 2: Purchase report: The purchases of the last month (fields:employee_id, emp_name, amount. One row per employee. Order by employee_id))

c. Query 3 3: Total number of sales for the last one year, by movie genre. (fields: movie genre, dollar amount).

The goal in the project is to have fun and use our creativity, to come up with the best database application we can, using what we have learnt in class!!! Please touch base with the Professor as we go through each phase in our project. The access queries require the use of the Σ tool on the access query toolbar. Again, please touch base with the Professor for more details on that.