Databases

Problem Set 1

Attempt seriously by Friday, February 22, before class

Submit by Tuesday, February 26, at start of class

Instructions


  1. For each of the following, indicate whether the statement is true, false, or unspecified according to the UU schema. Explain why, but keep your explanations very brief. (For example, you might write, "True because of the foreign key constraint between blah and blah.")

    1. Different students may have the same first and last name.
    2. Every student must have a don.
    3. Students can have more than one don.
    4. No two members of the faculty can have the same last name, even if they have different first names.
    5. Sometimes two or more professors may share the same phone number.
    6. Professors may teach more than one course, though some slackers may not teach any.
    7. Some courses taught in different departments may have the same title.
    8. No two distinct courses taught by the same professor may have the same title.
  2. Write SQL queries to answer the following questions:

    1. What courses are being offered by instructors who are also dons?
    2. What are the id numbers of students who have the same last name as their don?
    3. How many students are signed up for both Biology and Economics courses?
    4. What are the identification numbers of students who either have the first name Ada or are registered for a class that allows the enrollment to exceed 100?
  3. Write SQL queries that correspond to these descriptions:

    1. List the first names of pairs of students who have the same last name, but different first names.
    2. List the first and last names of instructors who are dons but are not teaching any courses. The list should be in order of first name (with ties broken by last name).
    3. List the first and last names of students who have the same first name as an instructor for at least one of the courses for which they have registered.
    4. For each student enrolled in at least one course, list their first and last names, ID#s, and the number of courses for which they are enrolled in descending order of the the number of courses with ties broken by increasing ID#.
    5. List, in alphabetical order without duplicates, the last names of teachers such that the maximum capacity of one (or more) of their courses is at least twice that of another of their courses.
    6. Find the most common first name among all students and faculty. Include ties, that is, if Jim and Mike are tied as the most common names, list both, in alphabetical order.
  4. Consider this sequence of relational algebra expressions:

    1. Translate it into a single equivalent SQL query. (Your translation should not be too literal, but should capture the same meaning.)

    2. In a clear and concise sentence, explain what the query achieves. (For example, "It finds all the professors who teach cosmology courses." Example of what not to write: "It selects the foo attributes from the bar and buzz tables.")

  5. Consider this SQL query:

    SELECT id, first_name, last_name
    FROM   Students NATURAL JOIN Registry NATURAL JOIN Professors
    WHERE  department = 'Economics'

    Explain what you think is the intended goal of the query, why the query will fail to achieve that goal, and offer an SQL query that will achieve that goal.

  6. In a clear and concise sentence, explain what the following query achieves:

    SELECT first_name, last_name
    FROM Students
    WHERE 
      (SELECT COUNT(*)
       FROM Registry
       WHERE student_id = id) IN
      (SELECT MAX(Temp.n) 
       FROM (SELECT student_id, COUNT(*) AS n
             FROM Registry
             GROUP BY student_id) AS Temp)
  7. Compute the average capacity of a UU course, assuming that the capacity is fifteen if it is not explicitly specified in the database.

  8. Read about SQL conditional expressions and the CASE operator. Given what you learn, rewrite your solution to the previous problem more compactly.


  9. Consider this relational algebra equation:

  1. What does it mean? (Informally, in English, not in terms of SQL nor in terms of sets.)

  2. Give a counterexample showing that this equation does not always hold for bags.

  3. Does it hold for sets? If so, prove it. If not, provide a counterexample.


UU schema

CREATE TABLE professors (
    last_name CHARACTER VARYING(50) NOT NULL,
    first_name CHARACTER VARYING(50),
    department CHARACTER VARYING(50),
    phone INTEGER,
    PRIMARY KEY (last_name)
);

CREATE TABLE courses (
    title CHARACTER VARYING(50) NOT NULL,
    teacher CHARACTER VARYING(50) NOT NULL,
    cap INTEGER,
    PRIMARY KEY (title, teacher),
    FOREIGN KEY (teacher) REFERENCES professors(last_name)
);

CREATE TABLE students (
    id INTEGER NOT NULL,
    first_name CHARACTER VARYING(50) NOT NULL,
    last_name CHARACTER VARYING(50) NOT NULL,
    years_left INTEGER DEFAULT 4,
    don CHARACTER VARYING(50),
    PRIMARY KEY (id),
    FOREIGN KEY (don) REFERENCES professors(last_name)
);

CREATE TABLE registry (
    student_id INTEGER NOT NULL,
    title CHARACTER VARYING(50) NOT NULL,
    teacher CHARACTER VARYING(50) NOT NULL,
    PRIMARY KEY (student_id, title, teacher),
    FOREIGN KEY (student_id) REFERENCES students(id),
    FOREIGN KEY (title, teacher) REFERENCES courses(title, teacher)
);