Skip to content

Exam 1 Study Guide

Our first exam is Tuesday, October 13. We will spend the first 10–15 minutes for Q&A, since the exam comes right after fall break. The exam itself is written to take about 50 minutes. You may not use a computer, phone, or other device.

The following supplementary pages will be provided:

  • The core schema of our project, as a diagram. Some of the questions will use these tables.
  • A small table not from the project that references the core schema, and some sample data.
  • An EER diagram (like the one in HW3, but smaller), and a key to the EER crow's foot notation.

Exam Outline

This is what we are planning. The question order and point values are not 100% guaranteed.

Question Topic Points Homework Labs Readings Class Days
1. Basic SQL 15 pts HW1 SQLite Week 1
Week 2		 Sep 01
Sep 03
2. Aggregation 15 pts HW1 Python DB-API Week 2 Sep 03
Sep 08
3. CRUD and Constraints 20 pts HW2
GP2		 SQLite vs. PostgreSQL Week 3
Week 5		 Sep 10
Sep 29
4. SQL in Python 15 pts HW2 Python DB-API Week 3
Week 6		 Sep 08
Sep 10
5. EER Diagrams 15 pts GP1 Abstract ER
Reading EER		 Week 4 Sep 15
Sep 17
6. Relational Mapping 20 pts HW3
GP2		 EER Mapping Week 5 Sep 22
Sep 24

Not on the exam:

  • GitHub, pull requests, code reviews, etc.
  • SSH tunnels, pgAdmin, virtual environments
  • Drawing an EER diagram (you will read one, not draw one)
  • Generating data with Faker, and connecting with psycopg

Practice Questions

These questions are like the ones on the exam, but they use data you have already seen. Try each one on paper before you open the sample answers.

Q1 and Q2: SQL

Exercise

Write the following queries using the World database from HW1.

  1. List the countries in South America whose capital city has more than one million people.
    • Schema: Country, Capital, Population
    • Order: Population (descending)
  2. For each continent with more than 30 countries, show the number of countries and the total population.
    • Schema: Continent, NumCountries, TotalPopulation
    • Order: NumCountries (descending)
  3. List the countries in Europe that have three or more official languages.
    • Schema: Name, NumOfficial
    • Order: NumOfficial (descending), Name
Answer 
-- 1. Eight rows, starting with Lima (6,464,693)
SELECT co.Name AS Country, ci.Name AS Capital, ci.Population
FROM country AS co
  JOIN city AS ci ON ci.ID = co.Capital
WHERE co.Continent = 'South America'
  AND ci.Population > 1000000
ORDER BY ci.Population DESC;

-- 2. Four rows: Africa (58), Asia (51), Europe (46), North America (37)
SELECT Continent, COUNT(*) AS NumCountries, SUM(Population) AS TotalPopulation
FROM country
GROUP BY Continent
HAVING COUNT(*) > 30
ORDER BY NumCountries DESC;

-- 3. Three rows: Switzerland (4), Belgium (3), Luxembourg (3)
SELECT co.Name, COUNT(*) AS NumOfficial
FROM country AS co
  JOIN countrylanguage AS cl ON cl.CountryCode = co.Code
WHERE co.Continent = 'Europe'
  AND cl.IsOfficial = 'T'
GROUP BY co.Code, co.Name
HAVING COUNT(*) >= 3
ORDER BY NumOfficial DESC, co.Name;

In query 1, notice which columns the join connects: a country's Capital is a city's ID, not a CountryCode. In queries 2 and 3, a condition on a single row goes in WHERE, and a condition on a group goes in HAVING.

Exercise

Now practice writing SQL for the core schema.

  1. For each catalog year, show how many students are bound to it, and how many of those students have an expected graduation term.
    • Schema: catalog_year, num_students, num_with_grad_term
    • Order: catalog_year
  2. List the active students who expect to graduate in a spring term.
    • Schema: last_name, first_name, year
    • Order: year, last_name, first_name
Answer 
-- 1.
SELECT catalog_year, COUNT(*) AS num_students,
       COUNT(expected_grad_term) AS num_with_grad_term
FROM student
GROUP BY catalog_year
ORDER BY catalog_year;

-- 2.
SELECT p.last_name, p.first_name, t.year
FROM person AS p
  JOIN student AS s ON s.person_id = p.person_id
  JOIN term AS t ON t.term_code = s.expected_grad_term
WHERE s.status = 'active'
  AND t.season = 'Spring'
ORDER BY t.year, p.last_name, p.first_name;

In query 1, COUNT(*) counts rows, and COUNT(column) counts the values in that column that are not null. In query 2, a student's person_id is also a person_id in person, so that is the join column; the names live in person, not student. Students whose expected_grad_term is null match no term, so the join leaves them out.

Q3: CRUD and Constraints

The hotel tables from the Faker lab are defined in create_sampleHotelDB.sql. Suppose the tables contain only these rows:

hotel_id name street city country year
1 Valley View Inn 12 Port Rd Harrisonburg USA 1998
2 Blue Ridge Lodge 400 Skyline Dr Luray USA 1935
hotel_id sort phone label
1 1 540-555-0101 front desk
1 2 540-555-0102 reservations

Exercise

For each task, write one SQL statement. Then say whether PostgreSQL would accept or reject it, and why. Each statement runs on the original data above, as if it were the only statement.

  1. Add a front desk phone number, 540-555-0199, for hotel 3.
  2. Add a hotel named Bluestone Suites, at 1 Duke Dr in Harrisonburg, USA, opening in 2030.
  3. Change the sort for all of hotel 1's reservations from 2 to 1, so that hotel 1 is listed first.
  4. Delete the Blue Ridge Lodge.
  5. Delete the Valley View Inn.

Follow-up question: Why is the primary key of hotel_phone the pair (hotel_id, sort) and not hotel_id alone?

Answer 
-- 1.
INSERT INTO hotel_phone VALUES (3, 1, '540-555-0199', 'front desk');

-- 2.
INSERT INTO hotel (name, street, city, country, year)
VALUES ('Bluestone Suites', '1 Duke Dr', 'Harrisonburg', 'USA', 2030);

-- 3.
UPDATE hotel_phone SET sort = 1 WHERE hotel_id = 1 AND sort = 2;

-- 4.
DELETE FROM hotel WHERE hotel_id = 2;

-- 5.
DELETE FROM hotel WHERE hotel_id = 1;
  1. Rejected: foreign key violation, since there is no hotel 3.
  2. Rejected: CHECK violation, since year must be between 1800 and 2026. Also note that the statement leaves out hotel_id. The column is GENERATED ALWAYS AS IDENTITY, so PostgreSQL rejects any value you give it.
  3. Rejected: primary key violation, since hotel 1 already has a phone with sort 1.
  4. Accepted, because no phone references hotel 2.
  5. Rejected: foreign key violation, since two phones still reference hotel 1.

Follow-up: One row is one phone number of one hotel, and a hotel can have several. With hotel_id alone as the key, each hotel could store only one phone. This is the same reasoning as a multivalued attribute in HW3, and as the key questions in GP2.

Q4: SQL in Python

Suppose the hotel tables were in a SQLite database, with the same data and constraints as Q3. Consider a Python module like HW2:

import sqlite3


def connect(path):
    global con, cur
    con = sqlite3.connect(path)
    cur = con.cursor()


def phones(hotel_id):
    cur.execute("SELECT phone, label FROM hotel_phone "
                "WHERE hotel_id = ? ORDER BY sort", (hotel_id,))
    return cur.fetchall()


def find(city):
    cur.execute("SELECT hotel_id, name FROM hotel WHERE city = '" + city + "'")
    return cur.fetchone()


def add_phone(hotel_id, sort, phone, label):
    cur.execute("INSERT INTO hotel_phone VALUES (?, ?, ?, ?)",
                (hotel_id, sort, phone, label))
    con.commit()

Exercise

  1. What do phones(1), phones(2), find("Luray"), and find("Richmond") return?
  2. One function has a security flaw. Name it, give an argument that takes advantage of it, and rewrite the execute call.
  3. Does add_phone(1, 2, "540-555-0103", "fax") raise an exception? Is the database different afterward?
  4. Does add_phone(3, 1, "540-555-0199", "front desk") raise an exception? Is the database different afterward? What one line would change the answer?
Answer
  1. cur.fetchall() returns a list of tuples, and cur.fetchone() returns a single tuple.
    • phones(1) → [('540-555-0101', 'front desk'), ('540-555-0102', 'reservations')]
    • phones(2) → [], since fetchall() returns a list even when there are no rows
    • find("Luray") → (2, 'Blue Ridge Lodge')
    • find("Richmond") → None, since fetchone() returns None when there are no rows
  2. SQL injection in find, which pastes its argument into the SQL text. The argument x' OR 'a' = 'a makes the condition true for every row, so find returns a hotel that is not in that city. The fix is a parameterized query: cur.execute("SELECT hotel_id, name FROM hotel WHERE city = ?", (city,)). Notice the comma in (city,), which makes it a tuple.
  3. It raises sqlite3.IntegrityError, since hotel 1 already has a phone with sort 2 (the primary key). Nothing changes.
  4. No exception, and the row is saved, even though there is no hotel 3! However, connect never runs PRAGMA foreign_keys = ON, and Python's sqlite3 does not enforce foreign keys without it. With that line in connect, as in HW2, the call raises sqlite3.IntegrityError and nothing changes. (We won't ask you this kind of trick question on the exam.)

Q5: EER Diagrams

Recall the Vet Clinic EER model from HW3.

Vet Clinic EER Diagram

Exercise

Explain each answer from the notation in the diagram.

  1. What are the minimum and maximum number of SERVICEs performed at one APPOINTMENT?
  2. How many PETGROOMERs can hold an APPOINTMENT?
  3. Can an EMPLOYEE be both a PETGROOMER and a VETERINARIAN? Can an EMPLOYEE be neither?
  4. Can a PET_PERSON be neither a PET_OWNER nor a PERMITTED_PERSON? Can a PET_PERSON be both?
  5. Can one ANIMAL have two owners?
  6. How is a PRESCRIPTION uniquely identified?
Answer
  1. At least 1 and at most N, from the one-or-many symbol next to SERVICE on performed_at.
  2. Zero or one, from the zero-or-one symbol next to PETGROOMER on holds.
  3. No: the d means the specialization is disjoint.
    Yes: the single line from EMPLOYEE to the circle means participation is partial.
  4. No: the double line from PET_PERSON to the circle means participation is total.
    Yes: the O means the specialization is overlapping.
  5. No. The exactly-one symbol next to PET_OWNER on owns means each animal has exactly one owner.
  6. PRESCRIPTION is a weak entity with two identifying relationships, prescribes and is_given.
    It is identified by the veterinarian's EID, the animal's ID, and its partial key, DatePrescribed.

Q6: Relational Mapping

Use the abstract model from the Reading EER Diagrams lab. This practice question maps the whole model; the exam will ask you to map only part of one.

EER model with X, Y, Z, F, R, W

Exercise

Draw a relational database diagram that implements this model.

  • Draw one box per table, with the table's name at the top and one column per line.
  • You do not need to show data types (none will be provided in the EER diagram).
  • Write PK after each primary key column name. Write FK after each foreign key column name.
  • Draw a line from each foreign key column to the primary key it references (and no other lines).
  • Draw two symbols at each end of the line, as in the EER notation key:
    • At the FK end, draw o< (zero or many) or o| (zero or one) if the relationship is one-to-one.
    • At the PK end, draw || (exactly one) or o| (zero or one) if the foreign key can be null.
Answer

Relational diagram with eight tables

  • Specialization: the hierarchy is disjoint and total, so every X is exactly one of Y or Z. That would allow tables for Y and Z only, with no table for X. But F refers to X, and a foreign key cannot point at two tables, so X keeps a table of its own. Y and Z each use X's key as both their primary key and a foreign key, so their lines to x are one-to-one: o| at y and z, and || at x.
  • jumps over: one X jumps over many Fs, and an F is jumped over by at most one X. The 1 side's key goes in the N side, so f gets xid, which may be null. That is why the line from f ends with o| at x, rather than ||.
  • Color: a multivalued attribute becomes a table keyed by the owner's key plus the value.
  • visits: many-to-many, so it becomes a table whose key is both foreign keys.
  • owns: W is a weak entity, so its key is its owner's key plus its partial key.