| CPSC 343 | Database Theory and Practice | Fall 2026 |
A significant component of this course is a project in which you will design and implement a database-driven application, bringing together concepts and material from many parts of the course. You will be responsible for designing and implementing both the database and the application.
The project is divided into five milestones, each covering a distinct part of the overall design and implementation process. Milestones which have been assigned are listed below; see the schedule page for tentative due dates for upcoming milestones.
| date | due |
|---|---|
| Fri 9/18 | milestone 1: topic and requirements |
| Fri 10/9 | milestone 2: modeling and design |
| Wed 11/4 | milestone 3: implementation |
| milestone 4: data integrity and security | |
| milestone 5: performance audit and final handin |
This is a significant project, and is important to work on it steadily and not leave it until the last minute. Furthermore, the cumulative nature of the project, with little in the way of extra time between phases of the project, means that a late milestone isn't just a late handin — it's also a late start on everything that follows. This means that late milestones are largely not accepted — budget your time so you can complete each milestone on time, and hand in what you have when a milestone is due even if it isn't complete. A few grace days are available for the occasional "just need a day or two more!" crunch; see the late policy for details.
The domain and functionality of the application is up to you; some ideas are given below but you are welcome to come up with your own. The only two real requirements are that the topic is sufficiently distinct from the examples discussed in class and it is able to support everything the course will ask you to build. The criteria below are a minimum bar, not a full checklist — if you can't say "yes" (even tentatively) to one of them, your topic likely won't be able to encompass everything it will need to. You also don't need every detail worked out now, just a plausible reason your topic could satisfy each criterion. Don't worry if you aren't sure about some of them — the purpose of the check-in meeting for this milestone is to help with exactly that.
At least 5-6 distinct kinds of interconnected "things", such as employees, departments, projects, and dependents for a company (four kinds of things) or books, authors, publishers, library branches, and borrowers for a library (five kinds of things).
Individual attributes, or a small group of attributes, that are naturally repeated or derivable across rows, such as a publisher's address showing up on every book they publish or a book's total number of copies being derivable by counting the number of copies each library branch holds.
Relationships with different cardinalities, such as a dependent being associated with only one employee while an employee can have any number of dependents (one-to-many), or a book having many borrowers over time and a borrower borrowing many books (many-to-many).
At least two kinds of application users with different kinds of access needs, such as a regular employee and an HR adminstrator, or a borrower and a librarian.
User tasks which involve inserting, retrieving, updating, and deleting data in the database, such as registering a new borrower, looking up which books are currently checked out, updating a due date, and removing a borrower's record.
User tasks supported by a variety of query techniques, not just simple lookups. Think about tasks in your domain that would need things like the following:
User tasks which involve touching multiple tables as part of one logical operation, such as checking out a book (which might need to create a new loan record, udpate the book's available-copy count, and record a hold as fulfilled) or hiring a new employee (which might need to create an employee record and assign them to a department's project).
Data integrity rules that the database itself should enforce, such as never letting a loan record reference a book that doesn't exist, not allowing a book to be checked out if no copies are available, or automatically adjusting a book's available-copy count whenever a loan is recorded or returned.
Tables which could plausibly grow to hundreds or thousands of rows — some tables may naturally stay small in a real deployment but others should be able to grow large. For example, a regional library system is not likely to have more than a few dozen branches but a library's book collection and loan history could realistically have thousands of entries.
Room to grow. As new material is covered in the course, applications of those concepts will need to be incorporated into your project. Can you imagine expanding the scope and adding new kinds of interconnected things, such as a library adding book clubs that borrowers can register for or a company tracking its employees' compliance training completions? Is there potential for different "flavors" of things that share some characteristics but also have their own distinct details, such as borrowers being able to check out different kinds of media (books, audiobooks, DVDs, etc.), or a company having both regular employees and contractors? Might new data integrity rules emerge as the domain gets more complex? "Room to grow" is the most important criterion for an appropriate topic, but also the least specific — what matters is that there's real potential for incorporating new elements, not that you can already name what they'll be.
Some possible ideas:
Note that these ideas are given as a starting point — you still need to make sure your topic satisfies the criteria given above (without also being too big).
Your project proposal needs to contain two things:
A brief description of your topic — give a sentence or two big-picture description, similar to what is in the list of possible ideas above.
A list of 10-12 use cases or user tasks which describe the functionality of your application. Label each with the type of user that would perform that task. For example, for the library, several tasks are check out a book [librarian], check a returned book back in [librarian], browse the entire collection [library patron], search books written by a particular author [library patron], and view details about a particular book [library patron]. You don't need an exhaustive list of everything a large application might allow a user to do, but be sure to identify tasks that meet the appropriate topic criteria and try to choose a cohesive set of tasks that would provide a useful, if potentially limited, chunk of functionality.
In addition to the project proposal, briefly outline how your topic meets each of the 10 criteria listed above. You don't need full details or big explanations, just enough to see that your topic is suitable. If you aren't sure how or if your project might meet some of the criteria, come to office hours to discuss it — that's the purpose of the check-in meeting for this milestone.
A 15-minute check-in meeting is required to discuss your project topic and the checklist. Stop by office hours or make an appointment. Check-in meetings must be completed by 9/23 (several days after the milestone is due). Sooner is better so you can move on to milestone 2!
Hand in a hardcopy of your project proposal and the topic checklist.
There is no separate handin meeting for this milestone.
This is where the project really begins, with modeling the data requirements and designing the database itself.
You outlined a set of use cases (user tasks) in your project proposal, now formalize the requirements for your application: create a requirements document with the brief description of the topic and the list of use cases from your project proposal. The rest of the design and implementation of your project will be based on this document.
Create an ER/EER model that captures the data requirements implied by your use cases — what data is needed to support the functionality identified? Use the ER and EER features appropriate for your domain, but also make sure you include the elements listed below. If any of these are not naturally supported by your original set of use cases, add a few new use cases to demonstrate those structures — don't just add things to the ER model that aren't supported by your requirements document. Clearly mark any additions or revisions from what was in your original project proposal.
Your ER diagram must contain:
Using PlantUML (preferred) or another diagramming tool for your ER diagram is recommended so that it is easily editable.
Also create two lists of things present in the data requirements but which are not captured by the ER diagram:
Structural constraints which should be enforced by the database but which aren't expressible in the ER notation (such as that a book's due date must be after the checkout date or that a particular value is required).
Policy constraints which should be enforced by the application (such as that a borrower may not check out more than five books at a time). These aren't the database's responsibility, but it is worth recording them now so you don't lose track of them.
Convert your ER/EER model to a relational schema following the mapping process discussed in class.
For each relation, identify a primary key along with any foreign key(s) and appropriate NOT NULL, UNIQUE, and CHECK constraints. (If a key wasn't already identified in the ER model, derive candidate key(s) from the functional dependencies and choose one as the primary key.) In a separate list, include any structural constraints not represented in the relational schema. (You don't need to repeat the policy constraints — those are still outside the scope of the database itself.)
Identify the functional dependencies that hold in your domain — go back to the entity sets and elements from the ER model to avoid being misled by how a table happens to be organized.
Then go relation by relation: for each relation, identify the functional dependencies that hold for that relation and determine its candidate key(s). (Use this to identify an appropriate primary key for any relation that didn't already have one from the ER model and to verify that the primary key is appropriate otherwise.) Then normalize any relation that isn't already in BCNF, or explain why it isn't possible or desirable to do so. For any relation you do decompose, briefly name the anomaly that the original structure was vulnerable to.
Your schema may not need any further decomposition — in fact, that's likely if you followed good ER design practices in the first place. You do still need to go through the process of identifying the functional dependencies and demonstrating why each schema is in BCNF — there's nothing to fix, but you need to show that instead of simply claiming that everything is fine.
Discuss the choices you made in all three steps above — ER modeling, relational mapping, and normalization — wherever there was a decision to be made. (You do not need to address cases where there was really only one way to go.) In particular, be sure to address:
Places in your ER model where there was a choice about how to represent something and why you made the choice you did.
Challenges with creating the ER model — things in your requirements you couldn't express or couldn't express elegantly in ER notation and how you handled it.
Places in your relational mapping where there was more than one reasonable way to represent an ER construct relationally and why you chose the approach you did.
Your functional dependency and normalization analysis — the dependencies you identified, the normal form each relation satisfies, and either your decomposition (if one was needed) or your reasoning for why none was — along with a check that any decomposition you did perform is lossless-join and dependency-preserving.
This document is the main place where your reasoning is visible and it is an important piece of your handin — a correct-looking schema with a thin or missing design analysis doesn't give much evidence that you understand why it's correct. Focus on places where there was a legitimate choice of alternatives — you don't need to document where there really was only one reasobnable way to go.
Aim for approximately 2-4 pages.
the requirements document — your overview paragraph and use cases from the project proposal, updated to reflect any use cases added or revised
the ER/EER diagram along with the two lists of constraints not represented in the diagram and policy constraints
the relational schema, with primary key, foreign key, NOT NULL, UNIQUE, and CHECK constraints, along with a list of any constraints not captured in the schema
your design analysis and process writeup, covering your ER and ER-to-relational mapping choices and your functional dependency normalization analysis (2-4 pages)
A 15-minute check-in meeting is required to discuss your ER model before you proceed with the ER-to-relational mapping and normalization steps. Sooner is better so that you have time to act on any feedback and still complete the last two steps on time.
Hand in hardcopies of the deliverables listed above.
Handin meetings will be scheduled for Monday 10/12 and Tuesday 10/13. A signup list will be made available a few days in advance.
This is where your design becomes a working system: you will build the database from your milestone 2 schema, load it with enough data to be able to test your application's functionality, and build the back end of your application as a REST API (business logic and data access, no user interface) that supports your use cases. This is the largest milestone of the project and the one where most of the code gets written, so plan your time accordingly — don't leave it until the last week. Authentication and authorization are not part of this milestone; they come next, in milestone 4. For now your endpoints can be open to anyone, though it's worth keeping in mind which ones will eventually need to know who is calling.
Work in layers and get something running end to end early. A good order is: create the database, build one use case's path all the way through (data access, business logic, endpoint, test), and then expand to the rest of your use cases. Getting one complete vertical slice working will give you the framework and proof-of-concept for the rest.
Implement your relational schema in MySQL. Implement the full schema even if you don't implement any use cases that use some of the relations. You can use MySQL Workbench to define the tables or write the CREATE TABLE statements from scratch, but note that you will need to hand in the CREATE TABLE statements. You should:
create the tables in a valid order (and list the CREATE TABLE statements in that order)
use appropriate MySQL data types for every column
declare the primary key, foreign key, NOT NULL, UNIQUE, CHECK, and DEFAULT constraints from your design
choose ON DELETE and ON UPDATE behavior for each foreign key deliberately, based on what should happen in your domain rather than simply accepting the default
If you made changes to your ER diagram after milestone 2 or if implementing the schema reveals problems with your milestone 2 design, make the necessary changes in all places — keep the design documents and the database consistent with each other — and keep a short dated change log listing what you changed, when, and why. Changing your design as you learn more is normal and expected; changes that are visible and explained are good process, while a final schema that doesn't match what you handed in earlier without explanation is not.
Populate your database with sample data using MySQL Workbench's interface to enter rows by hand, by writing INSERT statements, or (recommended) by creating CSV files and importing them using LOAD DATA INFILE. (See homework 10 for examples of CSV files.) You don't need a ton of data, but add enough so that your queries return meaningful, non-trivial results and include some interesting cases (e.g. related rows in several tables, NULLs where they're allowed, and ties or duplicates where they'd occur in real data) so that you can fully check your queries. You only need to populate tables used by endpoints that you are implementing.
Use the username_project database in MySQL, where username is replaced by your username. You will need to connect to MySQL using your account (sent out via email) rather than guest in order to access the database.
Remember that you can run SHOW CREATE TABLE table in a query tab in MySQL Workbench to get the CREATE TABLE statement for an existing table.
Design and implement a REST API that supports the use cases from your requirements document. Follow the REST conventions discussed in class: resources named with nouns, the HTTP method conveying the action, and appropriate status codes. Your API should:
have at least 12 endpoints in total (it is recommended, though not required, that you pick a set of endpoints that support a complete useful subset of your application's functionality)
support full (or nearly full) create, read, update, and delete operations for at least 2–3 of your entities
include at least one endpoint with a path parameter that identifies a specific resource (such as /books/{id})
include at least one endpoint with a query parameter that filters or searches a collection (such as /books?author=Adams)
include at least one nested resource that reflects a relationship in your data (such as /members/{id}/loans for the loans belonging to a particular member)
have at least three endpoints backed by queries that join two or more tables, at least two backed by a subquery, at least two backed by aggregation, at least one that uses a set operation, and at least one that sorts and/or limits its results
include at least one operation that modifies data in multiple tables as part of one logical action (such as checking out a book or placing an order)
A given endpoint can satisfy more than one requirement e.g. one that involves a join and sorts its results counts toward the three join endpoints, the one sorting endpoint, and the 12 total endpoints.
Include a concise API document listing each endpoint: the HTTP method and path, a one-line description, which use case it supports, what it expects in the request (path and query parameters and the JSON body, if any, with field names and types), the JSON structure it returns on success, and the error status codes it can produce. For example:
POST /members/7/loans — check out a book (use case 4)
Request body: { "bookId": 42 }
Success: 201 Created, { "loanId": 118, "bookId": 42, "dueOn": "2026-11-18" }
Errors: 400 missing bookId; 404 no such member or book; 409 book already checked out
Note that authentication and authorization is not part of this milestone! You don't need login or logout endpoints and every route is public.
Build your back end in Java using JDBC to talk to MySQL and the lightweight web framework and JSON library introduced in class. You may reuse code from posted class examples (slides or Java files) and from the provided code and your own work on the photo album homework, as-is or with modifications. You may not copy or use code, even with modifications, from other sources (including AI and other students) — see the course Academic Integrity and AI policies.
Organize your code into the three layers discussed in class:
Routes parse the request, call the business logic, and produce the response. No SQL belongs here.
Services (business logic) validate input, enforce the policy constraints you identified in milestone 2, and coordinate multi-step operations. No HTTP details and no SQL belong here.
Data access (DAO) classes hold all of your SQL and JDBC code. Use PreparedStatement with parameters for every query — never build SQL by concatenating input — and close your connections, statements, and result sets properly (try-with-resources). Map result rows to Java objects, taking care with NULLs, dates, and decimal values.
Organize your code into packages as in the photo album homework.
Your application should also handle errors deliberately rather than letting things fail on their own. At a minimum, make sure that:
a request for something that doesn't exist returns a 404
invalid or incomplete input (missing required fields, values of the wrong type or out of range, malformed JSON) is caught and returns a 400 with a helpful message
requests that conflict with the data (such as a duplicate value in a UNIQUE column, or a reference to a row that doesn't exist) return an appropriate 4xx code, determined from the database error rather than by guessing from message text
unexpected failures return a 500
all errors use a consistent JSON format and never expose stack traces, SQL, or table names to the client (log those on the server instead)
Use a centralized error handler as discussed in class and implemented in the photo album homework.
Test your API as you build it using curl or, for GET requests, a web browser. Writing a script that exercises your endpoints (including the error cases) as done in the photo album homework is highly recommended — it's the best way to catch a problem you introduced while fixing something else.
Coming soon!
the requirements document from milestone 2, updated as needed to reflect any use cases added or revised
the ER/EER diagram along with the two lists of constraints not represented in the diagram and policy constraints, updated as needed if there were changes after milestone 2 and/or during implementation
the relational schema, with primary key, foreign key, NOT NULL, UNIQUE, and CHECK constraints, along with a list of any constraints not captured in the schema, updated as needed if there were changes after milestone 2 and/or during implementation
the change log documenting changes to your requirements document, ER/EER diagram, and/or relational schema
the database definition (CREATE TABLE statements, in a valid order)
the API document listing your endpoints
your code, including any test script(s)
your implemented and populated database
A 15-minute check-in meeting is required to discuss your API design and progress implementing the first use case and to clear up any sticking points regarding implementation. Bring a draft of your API document listing your endpoints and have your code available (bring a laptop with your code running or have copied your code into a m3-checkin folder in the handin directory before the meeting). Sooner is better for the meeting!
Hand in hardcopies of the deliverable documents — the requirements document, ER/EER diagram, relational schema, change log, database definition, and API document. Hand in your code to the handin folder. The database will be checked in MySQL.
Handin folder details coming soon!
Handin meetings will be scheduled for Thursday 11/5 and Friday 11/6. A signup list will be made available a few days in advance.
The handin meeting will discuss your database implementation, your API design, and your code. Make sure that you have handed in all of the deliverables by the due date.