Lab 4 - Database(SQL) Review

1. Problem Statement

Consider the following application: we need to create a simple retail management system for a small store. There are many aspects of such a system, however, in this lab we just focus on a tiny part of it: inventory control and coupon redemption.

A store needs to keep track of its inventory for every product. For every product, it records the following information: UPC number (Universal Product Code), product name, price, quantity in stock, manufacturer. For each manufacturer, the store records the physical address and telephone number. A product must be produced by a single manufacturer, but a manufacturer may product many products.

Coupons are distributed from the manufacturer of a product to end users. When an end user purchases the corresponding product, the amount is deducted from the bill. The store periodically sends coupons to redemption centers, which reimburse the store. In a redemption center, there are representatives each of which is responsible for coupons from several manufacturers. Assume that a manufacturer only has one representative, but one representative may be responsible for different manufacturers.

A store records the information of each coupon: UPC number, value, quantity redeemed, redemption representative. For each representative, the store records address of the redemption center and telephone number of the representative.

Each coupon should be linked to the products to which it is intended. A coupon might be linked to more than one product (for instance, $1 off any size Duracell Battery), and there might be different coupons available for a product.

2. Database Schema

Now let us develop the schema for this application. Clearly there are 3 parts as stated above: product information, coupon information and connection between them. Consider the following table for product information (primary key is underlined).


Is this table enough to capture the relation described above? If not, how to fix it.

The coupon information part is very similar to the product information. Please develop the schema for it.

How to connect product information and coupon information?

3. Query

1. In ascending order, list the number of products sold from each manufacturer.

2. List the price of the least expensive toothpaste product. (Assume that for each toothpaste product, the word "toothpaste" appears in its name)

3. List the addresses and telephone numbers of manufacturers of products with more than 100 in stock.

4. List the redemption representatives of products with more than 100 in stock.

5. For each manufacturer, list the number of toothpaste products that are linked with at least one coupon. Zeros should be included in the answer.

CS105 Labs