XYZ – Super Sales Database Design and Implementation

Problem to Solve

ERD

Database Entity Relationship Diagram

XYZ – Super Sales (XYZSS) is the brain child of Fern Seher. She has organised all the financing for the set up of a new website which aims to be an online version of a TV sales programs. She hired three presenters, Peter, Paul and Marie. They will be starting next Summer.

The website will sell products only when a presenter is online. The presenters are on air for half an hour, and they can reduce prices in steps of 10 percentiles, from the original retail price down to the wholesale purchase price.  Each item (one instance of a product) has a item number. Its wholesale purchase price, its original retail price and the discount given by the presenters (along with the number of minutes into the half-hour sales session that the discount is offered) are recorded.

Every unsold item is sold at the last discounted price given by the presenters plus delivery costs on ebay. The ebay account will not be under the name XYZ – Super Sales in order to avoid association. If after 6 months an item is still in one of the warehouses it has to be sold on ebay for whatever price it achieves.  This can be below the original wholesale purchase price. This is done in order to clear shelf space.

The business needs to record customer information. Fern would also like to see how many items were bought by the customers. The business does not need to keep records of the ebay customers. This is all done by ebay anyway.

Fern has rented three warehouses, which are actually just garages. They are fitted with shelves. Each shelve is divided into shelve spaces. Each shelve space can only hold one item at one time. Fern also hired Frank to look after the warehouses and the shipping. He will be starting in March. He is going to record any changes on a stock control system written by Fern’s husband Michael. He will also set up the website.

Michael could have set up the database too, but he thinks he should give you a chance to design and implement a working database.

He needs to be able to see that all queries work. For this purpose the database needs to be populated. The data in the database should simulate that the business has already been running for some time.

 

Reports List

On the following page there is a list of the reports that the database will be required to produce.

If anything, it probably contains as much useful information as the scenario given above.

1.          All details of a customer.

2.          The amount of money made on ebay in one month.

3.          An item with its original purchase price, its original retail price and its final sale price.

4.          A list of shelf spaces (one month ago) with items in them, which warehouse they are in and on which shelf this shelf space is.

5.          The number of items which had to be sold on ebay for the purchase price.

6.          The number of items which had to be sold on ebay for the presenters’ discounted price.

7.          One product and how it is discounted during a presenters sales session.  i.e. 12mins   -10%; 24mins   -30%. Please keep in mind that the total cannot exceed 100.

8.          Items that are still in the warehouses after 6 months.

9.          The number of products sold by all presenters.

10.         A list of products with how many of each have been sold, in ascending order.

11.         The most profitable presenter of the month. This is calculated by adding profit margins for all items sold by the presenter.

12.         A list of presenter names and the number of items each one has sold.

13.         The presenter who reaches the 50% discount mark first on average.

14.         The number of customers who bought a particular product.


Solution