| 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 — front end |
| Fri Nov 15 | application design — back end |
| 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.
In this phase, you will start to focus on the application itself, specifically the front end.
Design the user interface for your application. You can focus on just the essential and important functionality you've identified. For a web application, identify the pages, sketch the user interface for pages that display, and indicate the flow of control and information passed between pages. (See the picture in the photo album handout for an example of how you might show flow of control and information passed.) For a Java application, sketch the user interface for the main window and any other windows (including dialogs) and indicate what happens with user interaction e.g. clicking on button X brings up the Y dialog.
Note that you are only designing the application, not implementing it! (Yet.)
What to hand in:
The requirements document will likely be the same as what you've handed in previously, but should be updated if you have made any changes.
How to hand in: Hardcopies of all documents.
Graded on: (final handin)
Completeness: The UI design should support the application's essential and important functionality.
Usability: The UI should allow access to the essential and important functionality; while it doesn't need to be fancy, it should be easy to understand and convenient for the user to access the application's functionality.
In this phase, you will start to focus on the application itself, specifically the back end.
Consider the database operations that your application will need to support its functionality, and design according to convenience — simplify implementation and avoid repeated implementations — and the principle of least privilege — what views and stored routines should be defined to allow the application the access needed without having to grant wider-than-needed access to the underlying tables? Identify what each view will show and the type (procedure or function), parameters, return values, and task accomplished for each stored routine. You don't need to write the view definitions or the bodies of the stored routines themselves at this point.
Identify any situations where transactions are needed, along with the appropriate isolation level for the transaction and any locking reads required.
Note that you are only designing the application, not implementing it! (Yet.)
What to hand in:
The requirements document will likely be the same as what you've handed in previously, but should be updated if you have made any changes.
How to hand in: Hardcopies of all documents.
Graded on: (final handin)
Completeness: The views and stored routines defined support the application's essential functionality as well as any additional functionality implemented without having to grant blanket access.
Correctness: The choice of views and stored routines and the use of transactions reflect an understanding of the concepts involved.
This is it, implementation of the database and application. See below for details about naming, directories, and database accounts.
Use the username_project database for the implementation of the database. If you have begun implementation of the database with a different name, ask for assistance in renaming it.
Accounts and permissions:
Use your MySQL account to connect to the database from MySQL Workbench as usual to set up tables, load data, define stored routines, try out queries, etc.
Two accounts are available for your application to use when connecting to the database:
The username_user account has the SELECT privilege for any table or view in the project database whose name starts with view_.
The username_admin account has the SELECT and EXECUTE privileges for all tables/views and stored routines, respectively, in the project database.
The passwords for these accounts are the same as your MySQL password with _user and _admin appended.
Use these accounts when your application needs to connect to the database — do not hardcode your personal MySQL account password into a web page or Java program, or require it for your application to run. Use the _user account as much as possible — define views for the queries that are needed rather than naming tables starting with view_. Use the _admin account when greater access is needed. You'll need to define stored routines for data modification rather than having your application issue INSERT, DELETE, MODIFY statements directly.
What to hand in: There is no separate handin for this phase — your code (and another documents) will be handed in with your final handin.
Graded on: (final handin)
Essential functionality: For a passing grade on the application and functionality portion of the project, you should implement your full database design (tables, views, stored routines, triggers) but you only need to implement the essential functionality of the application itself — enough provide some useful functionality, and to show mastery of the core ideas of a database application. What's implemented should be without bugs — if something doesn't work, comment it out or remove it. There should be enough data in the database to verify the application's functionality. For full credit, go beyond minimal functionality in some non-trivial way — add one or two of the features identified as "important".
Usability: The user interface should be easy to understand and the user should be able to easily accomplish the desired tasks. It does not need to be fancy — plain is fine.
Robustness: Error-checking should be performed, and error messages displayed to the user should be user-understandable.
Security: There should be adequate protection of sensitive information (such as passwords) and against SQL injection attacks. Consideration should be given to security with respect to database access — use views and stored routines to limit access, and favor using the _user database user over _admin as much as possible.
Coding standards: Code (whether web pages and PHP content or Java code) should be neatly written, follow appropriate coding and commenting conventions, and avoid excessively inefficient or awkward constructs.
Having particularly nifty features or functionality that goes beyond the required levels can result in extra credit.
What to hand in:
Your handin should include sufficient documentation for users and admins. A separate user manual is not needed if all of the information necessary for using the application is available within the application itself (e.g. through a self-explanatory user interface, a help menu, or other means). For the DB admin, there should be enough information for someone to understand the database should they want to add or modify it or the data it contains — identify what the tables, attributes, views, stored routines, triggers are for. Your database and back end design documents, potentially with some additional explanatory notes, can suffice here.
Your handin should also include sufficient documentation for testing your application. Include usernames and passwords for your application users.
Finally, a writeup should addresses your design decisions. For both the ER model and the relational model, consider when there were alternatives about how to represent or implement something — why did you make the choices you did? Did you run into difficulties, and were there things that you couldn't (or couldn't elegantly) represent? Explain.
How to hand in:
Copy your application's files to a folder project in your handin directory (/classes/cs343/handin/username, where username is your CS account username).
The database will be dumped directly from MySQL, so there is nothing additional to hand in for that.
Hand in hardcopies of your specifications document, ER model, manuals/documentation, and writeup. The database and application design will be graded based on your implementation so you don't need to hand in those designs separately.
Graded on: The project should meet the "criteria for an appropriate topic" outlined in the "Topic Proposal" section above. The individual components of the project will be graded as outlined in the "graded on (final handin)" sections for each component. There should be sufficient documentation, both for the user and for the DB administrator. The writeup should display awareness of the tradeoffs in modeling and implementation, and should provide an appropriate rationale for the design decisions made.
