CPSC 343 | Database Theory and Practice | Fall 2024 |
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.
All deadlines are in class unless otherwise noted. Dates in bold are firm, others might shift slightly. More details on what the different components entail and what to hand in are given in the "Project Components" section below.
date | due |
---|---|
Fri Sep 13 | topic proposal |
Mon Sep 23 | specifications |
Fri Oct 4 | conceptual model |
Fri Oct 25 | database design |
Fri Nov 8 | application design — frontend |
Fri Nov 15 | application design — backend |
Fri Dec 6 | implementation (database and application) |
Wed Dec 11 10pm | final handin |
This is a significant project, and is important to work on it steadily and not leave it until the last minute. The deadlines given above are to help you stay on track, and also provide an opportunity for feedback and revision. See the late policy for the late policy as it pertains to the project — in short, yes, you can turn in stages late but significant lateness or a pattern of lateness will make it substantially harder to complete the project successfully.
Grading:
Only the final handin is graded, however, handing in each phase provides an important opportunity for feedback and revision. Approximate weights of each component in the final handin:
database: 60%
application: 40%
Specifics on grading the individual components can be found in the "Project Components" section below.
The topic is up to you; some ideas are given below but you are welcome to come up with your own. The criteria for an appropriate topic:
"Moderately complex" and "non-trivial modeling decisions" are a bit difficult to define without having covered data modeling in class yet, but as a rough guideline, "moderately complex" means there should be at least 4-5 interconnected "things" about which information is being stored. For example, the sailors database has only three things (sailors, boats, reservations) while the library and company databases each have four (books, publishers, library branches, borrowers for the library; employees, departments, projects, and dependents for the company) plus additional connections between them (library branches have books, borrowers check out books, employees work on projects, etc). (In the sailors database, reservations capture the connection between sailors and boats so there isn't anything else to include.) "Non-trivial modeling decisions" can stem from connections between three or more things (such as a borrower checking out a book from a library branch, connecting "borrower" and "book" and "library branch") or "category" and "kind of" relationships between things (such as disparate things which can function in the same role — e.g. a person, a bank, and a company all be able to own a vehicle — or different types of a thing — e.g. an actor can appear in movies and TV shows, both types of media production), though this is not an exhaustive list. A good strategy is to identify a topic with room for expansion, for example, the basic library database could be extended to include waiting lists for popular books, additional media (such as DVDs or access to streaming), ...
About the last point: The emphasis in this course is on the "database" part of "database-driven application", covering everything associated with the database itself (modeling, design, implementation) and up to and including the interaction of the frontend application with the database — not a fancy user interface, tons of functionality, or extensive handling of data that has been retrieved from the database. In other words, you'll need to plan big enough with your application to achieve sufficient complexity in the data requirements, but you only need to implement a piece of that functionality in your application — enough so that your application does something useful, but not necessarily as flexible, convenient, and powerful as you'd find in a full-featured software product.
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).
What to hand in: a description of your proposed topic with enough detail to be able to assess whether it meets the criteria for an appropriate topic (a few sentences to a paragraph or so)
How to hand in: email your topic description
Graded on: n/a
The first step in developing a software system is to figure out what it needs to do. For a database application, this means determining the functional requirements (what the users need to be able to do) and as well as the data requirements (what needs to be stored and its constraints) for your project. (We will not deal with operational requirements with this project.)
Create a requirements document containing the following:
A one- or two-sentence overview of the system. (Similar to the topic suggestions given above.)
Who will use the system? Identify the different user groups. (Note that you will need at least two different groups of users with different access needs.)
The data requirements for your system. Identify what needs to be stored, how different pieces of data are related, and constraints that apply (uniquely identify?, required value?, high level data type requirements, etc).
The functional requirements for your system. Identify the tasks that users will be able to use the system to perform, along with which users need to / can perform those tasks.
Identification of the essential and important functionality. "Essential functionality" is critical — without these features, the system would not be even minimally usable for its intended purpose. "Important functionality" makes the system significantly more useful or convenient to use; without these elements, the user could accomplish something useful, but would likely not be satisfied with the experience.
Think carefully about the essential and important functionality — you will generally be expected to implement the entire database but only the essential functionality for a passing grade and at least some of the important features for a good grade, though this depends on the scale of the project. (If there's a lot of essential functionality, implementing just that may be sufficient for a good grade.)
What to hand in: the requirements document
How to hand in: Hardcopy.
Graded on: (final handin)
Completeness of the handin: All of the required elements should be included, and should be thoughtfully considered with sufficient attention to detail.
Appropriate choices: Users have been correctly identified. Data and functional requirements are appropriate. Reasonable prioritization of the application's functionality.
Thoroughness of the analysis: All of the important aspects of the problem should have been considered.
Writing quality: Proper spelling and grammar is expected. The document should be well-organized and well-written.
Neatness: Make your documents readable! Please type the requirements document.
Based on your specifications:
Create an ER model based on the data requirements for your project. Cover the full system, not just the data required for essential or important functionality. List separately policy constraints (which should be enforced by the application rather than the database) as well as any core constraints (which should be enforced by the database) that are not captured by the diagram.
In a separate design analysis, discuss your design choices. Address cases where there are tradeoffs — when there were alternatives about how to model something, why did you make the choice you did? Also address things you couldn't express or couldn't express elegantly — explain the issue and the rationale for your choice.
Using PlantUML or another diagramming tool for your ER diagram is recommended so that it is easily editable.
What to hand in:
How to hand in: Hardcopy.
Graded on: (final handin)
Completeness of the handin: All of the data requirements should be covered, even if they only support nice or extra features.
Correctness: The ER model should correctly reflect the requirements list, not disallow things which should be allowed (including prohibiting desired interactions), and, as much as possible, not allow things which should not be allowed. Your writeup should address any necessary discrepancies.
Elegance: The ER model should represent logical arrangements of the information, reasonable design choices about how to represent various relationships and constraints, and should avoid unreasonable redundancy. The discussion of the design in your analysis is important in judging the reasonableness of your choices.
Neatness: Make your documents readable! Use PlantUML or another diagramming tool for the ER model.
Based on your specifications and ER model:
Convert your ER model to a relational schema.
Identify functional dependencies and candidate keys, and normalize the relations to BCNF (or explain why it isn't possible or desirable to do so).
Identify primary key, foreign key, NOT NULL, UNIQUE, and CHECK constraints and choose appropriate SQL data types for each attribute. Include a discussion of your design — when there were alternatives about how to represent something, why did you make the choice you did?
Are there constraints which need to be implemented using triggers? Identify each, giving the trigger event, the trigger action, and when the trigger action is executed. (You don't need to write the trigger body.)
What to hand in:
The requirements and ER diagram will likely be the same as what you handed in for the previous phase, but should be updated if you have made any changes.
How to hand in: Hardcopies of all documents.
Graded on: (final handin)
Completeness of the handin: All of the required elements should be included, and should be thoughtfully considered with sufficient attention to detail.
Correctness: The relational database schema should be normalized and should correctly reflect the requirements and the ER diagram, reasonable choices should be made for data types, and constraints should be implemented appropriately.
Elegance: The schema should demonstrate reasonable design choices about how to perform the ER-to-relational mapping. Your discussion of the design is important in judging the reasonableness of your choices.