Comp 521: Files and Databases -- Fall 2010

Problem Set #2

Issued: 9/15/2010      Due: In class 9/29/2010

Question 1.

Consider the follow database schema:

CREATE TABLE Person (
    id              INTEGER,
    FirstName       TEXT,
    FamilyName      TEXT,
    DateOfBirth     DATE,
    BirthPlace      TEXT,
    PRIMARY KEY (id)
);

CREATE_PARENT = """CREATE TABLE  ParentsChild (
    motherId        INTEGER,
    fatherId         INTEGER,
    childId           INTEGER,
    PRIMARY KEY (motherId, fatherId, childId),
    FOREIGN KEY (motherId) REFERENCES Person,
    FOREIGN KEY (fatherId) REFERENCES Person,
    FOREIGN KEY (ChildId) REFERENCES Person
);

Note that the FamilyName would hold the maiden name of any married women. Write relational algebra expressions for each of the following queries:

A) Find all people with a last name of “Hart”

B) Find the first and last names of all children who were born in the same hometown as both of their parents

C) Find all siblings of a person named “Lee Hart” (include half-brothers and half-sisters)

D) Find all aunts and uncles (both maternal and paternal) of  “Lee Hart”

• BJ: there's no reliable way to tell a person's gender.

E) Find any children with the same first name as one of their parents.

F) Devise a query to test that the sets of all mothers and all fathers are disjoint.

Question 2.

Using the same schema given in problem #1 write Domain Relation Calculus expressions for the following queries:

A) Find all mothers older than their child's father.

B) Find the younger full-siblings of a person named “Nora Cail”

C) Find any first-cousins who are parents of a child.

D) Find any person with a parent born in "Chapel Hill, NC"

E) Find all people without parents in the database

F) Find all fathers having children with more than one mother

Question 3.

Consider the follow database schema:

CREATE TABLE Students (
    pid INTEGER PRIMARY KEY,
    name TEXT
);
CREATE TABLE Courses (
    number TEXT,
    semester TEXT,
    seats INTEGER,
    PRIMARY KEY(number, semester)
);
CREATE TABLE Enrollment (
    student_name TEXT,
    course_number TEXT,
    course_semester TEXT,
    grade INTEGER,
    PRIMARY KEY(student_name, course_number, course_semester),
    FOREIGN KEY student_name REFERENCES Students(name),
    FOREIGN KEY (course_number, course_semester) REFERENCES Courses(number, semester)
);

Write relational algebra expressions for each of the following queries:

1. Find the course number for each class taken by "Robert Jackson" in "S2010", and the grade received.
2. Find the numbers of all courses taught in "F2010" which were taken by students who also took "COMP521" in "F2010".
3. Find all classes in which no student failed.
4. Find all students who have never taken a large class (where "large" means more than 50 seats)

Question 4.

For each relational algebra expression you wrote in problem 3, write an equivalent Tuple Relational Calculus expression.

Question 5.

For this progamming problem you will need to first download the following database movies.db (~500 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.

A. The list of customers born in 1990 or after with a first name that is the same as their last name.

B. The most rented movie(s).

C. The customer(s) with the most rentals.

D. How many customers have not rented a movie.

E. Customers who have rented more than 100 movies and rated every one with one star (rating = 1).

F. Customers who have rented more than 200 movies in a same day.

G. A list of customers who have rented at least 4 of the 6 movies in the Star Wars saga (ignore if they rented any supplemental materials or other Star Wars related materials).

H. All movie titles and their rating which have a unanimous rating by all customers who rented it.

I. All movies rented by two or more customers that also rented the movie "Woman in the Moon"

J. The two movies with the highest and lowest average ratings respectively.

K. In this database, in what year were the most movies released?

L. Find all pairs of customers who have rented more than 50 rentals in common and given them the same ratings

M. Find the most times that any single movie was rented by the same customer.

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