Comp 521: Files and Databases -- Fall 2012

Problem Set #2

Issued: 9/12/2012      Due: In class 9/25/2012

Question 1.

Consider the follow database schema:

CREATE TABLE Invoice (
    InvNum INTEGER PRIMARY KEY,
    CusNum INTEGER,
    SalesDate DATE,
    FOREIGN KEY (CusNum) REFERENCES Customer (CusNum)
);

CREATE TABLE InvoiceLine (
    InvNum INTEGER,
    ProductCode INTEGER,
    NumSold INTEGER,
    PRIMARY KEY (InvNum, ProductCode),
    FOREIGN KEY (InvNum) REFERENCES Invoice (InvNum),
    FOREIGN KEY (ProductCode) REFERENCES Inventory (ProductCode)
);

CREATE TABLE Customer (
    CusNum INTEGER PRIMARY KEY,
    First TEXT,
    Last TEXT,
    Phone TEXT,
    Address TEXT,
    City TEXT,
    State TEXT
);

CREATE TABLE Inventory (
    ProductCode INTEGER PRIMARY KEY,
    Description TEXT,
    Manufacturer TEXT,
    InStock INTEGER,
    Price REAL,
    UNIQUE (Manufacturer, Description)
);

Write SQL queries to generate the following result sets:

1. A list of all customer names (First and Last) with invoices dated in the 2011 calendar year. (Note: In SQL you can do comparsions between Dates specified in ISO format, where January 1, 2011 is specified as '2011-01-01' and December 31, 2011 is specified as '2011-12-31').
2. A list of all customer names (First and Last) who have ordered one or more screwdrivers. (You can assume that 'screwdriver' appears somewhere in the Description field of an Inventory record).
3. List all products that are only available from a single source (only one manufacturer)
4. List those products that can be supplied by every manufacturer.
5. A list of customer numbers and product codes for those items that a customer has ordered on two or more ocassions.
6. A list of customer names and the number of orders they have made.
7. A list of all invoice numbers and their total price.
8. A list of sales volume attributable to each customer, with the customer's name and the total dollar amount.
9. A list of the sales volume, in units sold, for each inventory item.
10. A list of the sales volume of all invoices according the customer's home state, with the state and dollar amount.
11. List all items where the quantities invoiced surpass the current inventory.

Question 2.

For this progamming problem you will need to first download the following database movies.db (~800 Mbytes) with the schema:

CREATE TABLE Customers (
    cardNo  INTEGER PRIMARY KEY,
    first   TEXT,
    last    TEXT,
    sex     CHAR,
    dob     DATE
);

CREATE TABLE Movies (
    movieId INTEGER PRIMARY KEY,
    title TEXT,
    year INTEGER
);

CREATE TABLE Rentals (
    cardNo  INTEGER,
    movieId INTEGER,
    date    DATE,
    rating  INTEGER,
    PRIMARY KEY(cardNo, movieID, date),
    FOREIGN KEY (cardNo) REFERENCES Customers,
    FOREIGN KEY (movieId) REFERENCES Movies
);

Write a single python program that prints answers to the following queries. You should attempt to answer each using a single embedded SQL query.

1. List the titles of all movies that include "Star Wars" in their title.
2. List the full names of all customers with first names of "PAUL" or "ROBERT" who rented "The Sting"
3. List of customers who rented "2001: A Space Odyssey" in the first 7 days of 2001.
4. Which customer or customers rented the most movies?
5. Want is the most rented movie?
6. Which customers saw both "King Kong" and "Godzilla" and ranked "King Kong" higher?
7. Which customer rated the most movies as "5"?
8. Which movie has the most ratings of "1"?
9. List all first names, last names, and movie titles of customers with first name "ALICE" who rented a movie with "Alice" in the title.
10. What is the most common Rental rating?

Turn in a listing of your program and a print out of its output (label the output with its corresponding part).

Question 3.

Reconsider the the schema defined in Question 1. Use table constraints, assertions, or triggers to enforce the following integrity constraints.

1. Before an invoice is deleted all of its invoice lines should first be removed.
2. You cannot insert or update an invoice line which would result in the total of all NumSold attributes for the specified product would exceed the current inventory for that item.