6-1 Project One: Creating A Database And Querying Data

You've got the assignment open. The requirements list is long. You're staring at a blank terminal or an empty ER diagram tool, wondering where to even start Surprisingly effective..

Sound familiar?

Most database courses drop you into "Project One" with a scenario — maybe a movie rental shop, a veterinary clinic, or a small manufacturing outfit — and expect you to design, build, and query a working relational database from scratch. Still, no scaffolding. No hand-holding. Just: here's the business rules, go normalize it.

The good news? That's why the process is repeatable. Once you internalize the steps, every project after this one gets faster. The bad news? Most students skip the thinking part and jump straight to CREATE TABLE statements. That's where the pain starts Simple as that..

Let's walk through it the way it actually works in practice — not the textbook version, the real one.

What Is Project One Really Asking For

At its core, this project tests three things: can you translate business rules into a logical design, can you implement that design in SQL, and can you write queries that answer real questions about the data.

That's it. The scenario is just window dressing.

You'll typically get a narrative: "The owner wants to track customers, rentals, inventory, and late fees. Movies have genres. In real terms, customers have membership tiers. Rentals link customers to inventory items." Your job is to extract entities, relationships, attributes, and constraints from that story Easy to understand, harder to ignore..

The deliverables usually look like this

• An ER diagram (Chen or Crow's Foot notation)
• A normalized schema (3NF minimum)
• DDL scripts to create tables, constraints, indexes
• DML scripts to populate sample data
• A set of SELECT queries demonstrating filtering, joining, aggregation, and subqueries
• Sometimes: views, stored procedures, or triggers

The rubric weights vary, but the schema and queries usually carry the most points. The diagram is easy points — don't lose them.

Why This Project Trips People Up

It's not the syntax. CREATE TABLE is straightforward. JOIN syntax is memorizable Worth keeping that in mind. No workaround needed..

• Is phone_number an attribute of Customer or a separate Contact entity?
• Should genre be a lookup table or a CHECK constraint?
• Do you store late_fee as a calculated column or materialize it?
• What happens when a movie gets deleted but rentals reference it?

These aren't syntax questions. Think about it: they're modeling questions. And modeling is where the grade lives Simple, but easy to overlook..

I've seen students build perfectly valid SQL that earns a C because the design doesn't match the business rules. Now, or genres are free-text strings ("Action", "action", "Action "). But a customer can rent the same movie twice simultaneously. Which means the data loads. The queries run. Or the rental_date has no NOT NULL constraint.

The database works. The model is broken And that's really what it comes down to..

How to Build It — Step by Step

1. Read the scenario like a detective

Don't skim. Print it. Highlight every noun that looks like a thing the business cares about. Circle every verb that connects two things.

"Customers rent movies" → Customer, Movie, Rental (relationship) "Movies belong to genres" → Genre, Movie_Genre (many-to-many) "Employees process rentals" → Employee, Rental (FK to employee)

Watch for hidden entities. "Late fees are calculated daily" — that's a Fee_Schedule or Late_Fee_Policy table waiting to happen. "Membership tiers determine max rentals" → Membership_Tier lookup table.

2. Draft your entities and attributes first — on paper

Skip the ERD tool for now. Write tables as column lists:

Customer
- customer_id (PK)
- first_name
- last_name
- email (UNIQUE)
- phone
- membership_tier_id (FK)
- created_at

Membership_Tier
- tier_id (PK)
- tier_name
- max_concurrent_rentals
- discount_pct
- monthly_fee

Do this for every entity. Even so, include PKs, FKs, NOT NULL, UNIQUE, CHECK constraints. Think about data types: VARCHAR(255) for email, DECIMAL(5,2) for fees, DATE for rental dates, TIMESTAMP for audit columns.

3. Normalize to 3NF — then denormalize on purpose

First pass: eliminate repeating groups (1NF), remove partial dependencies (2NF), remove transitive dependencies (3NF) It's one of those things that adds up..

Example of a transitive dependency violation:

Rental
- rental_id
- customer_id
- customer_email  ← depends on customer_id, not rental_id

Move customer_email to Customer. Done.

But — and this matters — sometimes you keep a denormalized column for performance or historical accuracy. rental_total on the Rental table might duplicate calculable data, but it preserves what the customer actually paid at that moment, even if pricing changes later. That's a design decision. Document it No workaround needed..

4. Draw the ER diagram after the schema is solid

Tools: draw.Day to day, io, dbdiagram. io, Lucidchart, MySQL Workbench reverse-engineer, or even hand-drawn and scanned Most people skip this — try not to..

Use Crow's Foot if your instructor prefers it. Show:

• Entity names (singular: Customer, not Customers)
• Attributes with PK/FK markers
• Relationship lines with cardinality (1:1, 1:N, M:N)
• Resolve M:N with associative entities (Movie_Genre, Rental_Inventory)

Pro tip: if your tool exports SQL, use it to generate your DDL starter script. Saves typing And that's really what it comes down to..

5. Write DDL in dependency order

Tables with no FKs first: Genre, Membership_Tier, Employee. Then tables referencing those: Customer, Movie. Because of that, then associative tables: Movie_Genre. Then transaction tables: Rental, Payment, Late_Fee Surprisingly effective..

Always include:

• ON DELETE / ON UPDATE rules (usually RESTRICT or CASCADE deliberately)
• Indexes on FK columns (some engines auto-index, some don't)
• CHECK constraints for business rules: CHECK (return_date >= rental_date)

CREATE TABLE Rental (
    rental_id      INT AUTO_INCREMENT PRIMARY KEY,
    customer_id    INT NOT NULL,
    inventory_id   INT NOT NULL,
    employee_id    INT NOT NULL,
    rental_date    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    due_date       DATE NOT NULL,
    return_date    DATETIME,
    rental_total   DECIMAL(6,2) NOT NULL CHECK (rental_total >= 0),
    CONSTRAINT fk_rental_customer
        FOREIGN KEY (customer_id) REFERENCES Customer(customer_id)
        ON DELETE RESTRICT,
    CONSTRAINT fk_rental_inventory
        FOREIGN KEY (inventory_id) REFERENCES Inventory(inventory_id)
        ON DELETE RESTRICT,
    CONSTRAINT fk_rental_employee
        FOREIGN KEY (employee_id) REFERENCES Employee(employee_id)
        ON DELETE RESTRICT,
    CONSTRAINT chk_dates
        CHECK (return_date IS NULL OR return_date >= rental_date)
);

6. Seed data that tests your constraints

Don't just insert five happy-path rows. Insert data that exercises:

• Every lookup table value
• Boundary conditions (max rentals per tier, max late fee)
• Edge cases: customer with no rentals, movie never rented, rental returned late, rental never returned

Counterintuitive, but true.

6. Seed data that tests your constraints

Don’t just insert five happy‑path rows. Insert data that exercises:

• Every lookup table value – one Genre, one Membership_Tier, എല്ലാം.
• Boundary conditions – a customer on the Gold tier renting the maximum number of copies per day, a late fee that hits the capped amount.
• Edge cases – a customer with no rentals, a movie that has never been rented, a rental returned exactly on the due date, and a rental that has never-friendly returned (NULL return_date).

-- Genres
INSERT INTO Genre (genre_id, name) VALUES (1,'Action'), (2,'Comedy'), (3,'Drama');

-- Membership tiers
INSERT INTO Membership_Tier (tier_id, name, max_rentals_per_day, late_fee_per_day) VALUES
    (1,'Bronze',2,0.50),
    (2,'Silver',4,0.30),
    (3,'Gold',8,0.10);

-- Employees
INSERT INTO Employee (employee_id, first_name, last_name, hire_date) VALUES
    (1,'Alice','Smith', '2015-03-12'),
    (2,'Bob','Johnson', '2018-07-01');

-- Customers
INSERT INTO Customer (customer_id, first_name, last_name, email, membership_tier_id) VALUES
    (1,'John','Doe','john.doe@example.com',1),
    (2,'Jane','Roe','jane.roe@example.com',3);

-- Movies
INSERT INTO Movie (movie_id, title, release_year, genre_id) VALUES
    (1,'The Great Adventure',2020,1),
    (2,'Laugh Out Loud',2019,2);

-- Inventory
INSERT INTO Inventory (inventory_id, movie_id, copy_number, status) VALUES
    (1,1,1,'available'),
    (2,1,2,'available'),
    (3,2,1,'available');

-- Associative table
INSERT INTO Movie_Genre (movie_id, genre_id) VALUES
    (1,1),(1,3),(2,2);

-- Rentals – a mix of on‑time, late, and unreturned
INSERT INTO Rental (rental_id, customer_id, inventory_id, employee_id, rental_date, due_date, return_date, rental_total) VALUES
    (1,1,1,1,'2023-08-01 10:00', '2023-08-08', '2023-08-07 15:30', 4.00),   -- on time
    (2,1,2,1,'2023-08-01 10:15', '2023-08-08', '2023-08-10 09:00', 4.00),   -- late
    (3,2,3,2,'2023-08-02 14:00', '2023-08-09', NULL, 2.50);                  -- not yet returned

Run บอล‑array of queries that try to violate each constraint: insert a rental with a return_date before the rental_date, assign a customer to a nonexistent membership_tier_id, or delete a Genre that is still referenced by a Movie. Confirm that the database rejects the operation and returns a clear error message The details matter here..

7. Keep the schema in version control

Treat your DDL as code. Store every CREATE TABLE, ALTER TABLE, and seed script in a Git repository. Use a migration tool (Flyway, Liquibase, Alembic) that records which migration has been applied to each environment.

• Developers can spin up a fresh copy with git clone && ./migrate.
• Deployments are reproducible and auditable.
• Rollback is possible if a schema change breaks the application.

Example flyway folder structure:

/migrations
  V1__create_lookup_tables.sql
  V2__create_customer_movie_tables.sql
  V3__add_foreign_keys.sql
  V4__seed_initial_data.sql

8. Test the schema with a unit‑test framework

A simple approach is to write a script that:

1. Drops all tables (or uses a transaction and rolls back).
2. Applies the latest migration.
3. Runs the seed script.
4. Executes a suite of SELECT/INSERT/UPDATE/DELETE statements that cover every constraint.

If any test fails, the CI pipeline stops and reports the exact error. In Java, JUnit + Test

containers can spin up a disposable PostgreSQL or MySQL instance for each test run; in Python, pytest combined with sqlalchemy and a test database fixture achieves the same isolation. The goal is to treat schema validation as a first‑class citizen in your CI/CD pipeline—just like unit tests for application code—so that a broken foreign key or a missing CHECK constraint never reaches production.

9. Document the “why” alongside the “what”

Constraints, indexes, and naming conventions are only as useful as the team’s shared understanding of them. Add a docs/ folder (or a README.md in the migrations directory) that explains:

• Business rules encoded in CHECK constraints (e.g., “rental_total must be ≥ 0 because negative prices are refunds, not rentals”).
• Index rationale (e.g., “composite index on Rental(customer_id, rental_date) supports the ‘customer rental history’ API endpoint”).
• Naming standards (e.g., fk_<child>_<parent>, idx_<table>_<columns>, ck_<table>_<rule>).

When a new developer asks, “Why does this column have a UNIQUE constraint?” the answer lives in the repo, not in tribal knowledge.

Conclusion

Designing a relational schema that stands the test of time is a discipline that blends data integrity, performance foresight, and operational hygiene. By:

1. Modeling the domain accurately with normalized tables and explicit relationships,
2. Enforcing rules at the engine level through primary keys, foreign keys, CHECK constraints, and NOT NULL columns,
3. Optimizing access paths with purpose‑built indexes and partitioning where warranted,
4. Version‑controlling every DDL change via migration scripts, and
5. Validating the entire contract automatically in a CI pipeline,

you create a database that not only stores data correctly but also communicates its guarantees to every consumer—application code, reporting tools, and future maintainers alike. The sample rental schema above demonstrates each of these principles in a compact, runnable form; adapt the patterns to your own domain, and you’ll spend far less time debugging “impossible” data anomalies and far more time delivering features It's one of those things that adds up. Nothing fancy..

Short version: it depends. Long version — keep reading.