| 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 |
| milestone 3: implementation | |
| milestone 4: data integrity and security hardening | |
| 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.