Seinfeld Relational Database

A relational database schema modeling the cast, characters, and episodes of the TV show Seinfeld across all 9 seasons — built to practice schema design, normalization, and writing non-trivial SQL queries.

---

What's Here

• Schema (seinfeld-schema.sql) — a normalized schema separating real-world people from show data: Person (with Actor and Director as specializations), Character, Season, Episode, Episode_Notes, and a Portrays junction table linking actors to the characters they played in specific episodes.
• Data (seinfeld-data.sql) — populated with all 172 broadcast episodes and 809 actor-character-episode records.
• Queries (seinfeld-queries.sql) — example queries demonstrating the schema, including a CTE-based query that finds a character's first appearance.
• Indexes (seinfeld-indexes.sql) — indexes on the schema.

Not yet implemented — triggers and views are planned but not yet written; see TODO.md for the current roadmap, including known query bugs being tracked.

---

Example Query

Finding a character's first appearance, using a CTE and a MIN() subquery:

WITH PCES AS (
  SELECT *
  FROM Portrays
    JOIN `Character` USING(character_id)
    JOIN Episode USING(episode_id)
    JOIN Season USING(season_id)
)
SELECT season_no, episode_no, episode_name
FROM PCES
WHERE referred_to_as = 'Uncle Leo'
AND aired_on = (
  SELECT MIN(aired_on) FROM PCES WHERE referred_to_as = 'Uncle Leo'
);

+-----------+------------+-----------------+
| season_no | episode_no | episode_name    |
+-----------+------------+-----------------+
|         2 |          2 | The Pony Remark |
+-----------+------------+-----------------+

---

Database Engine

Written primarily for MySQL. Alternative syntax for SQLite, PostgreSQL, and SQL Server is included as inline comments within the same schema file (e.g. -- BEGIN TRANSACTION; -- SQLite / SQL Server) rather than as separate branches, to avoid maintaining divergent copies of the schema.

---

Running Locally

MySQL:

mysql -u root -p -e "DROP DATABASE IF EXISTS Seinfeld; CREATE DATABASE Seinfeld;"
mysql -u root -p Seinfeld < seinfeld-schema.sql
mysql -u root -p Seinfeld < seinfeld-data.sql

SQLite:

sqlite3 Seinfeld.db < seinfeld-schema.sql
sqlite3 Seinfeld.db < seinfeld-data.sql

SQLite does not enforce foreign keys by default — run PRAGMA foreign_keys = ON; at the start of your session if you want referential integrity enforced.

For other engines, see the inline comments in seinfeld-schema.sql for the necessary adjustments (transaction syntax, identifier quoting).

---

Project Structure

seinfeld/
├── LICENSE
├── README.md
├── seinfeld-schema.sql    # Table definitions
├── seinfeld-data.sql      # Episode and cast data
├── seinfeld-indexes.sql   # Indexes
├── seinfeld-queries.sql   # Example queries
└── TODO.md                # Roadmap and known issues