Project

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.

Due Dates and Grading

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.

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%

  • conceptual model: 20%
  • database and backend design: 20%
  • database and backend implementation: 20%

• application: 40%

  • specifications: 5%
  • frontend design: 5%
  • application implementation and functionality: 30%

Specifics on grading the individual components can be found in the "Project Components" section below.

Project Components

Topic Proposal

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:

• It involves a database to store information.
• The data requirements are moderately complex and modeling the data involves some non-trivial decisions.
• There are at least two kinds of users with different kinds of access needs. (such as a regular user and an administrator)
• A web-based or Java application front end is appropriate.
• Useful functionality can be achieved without an elaborate front end.

"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:

• A classroom participation tool which supports taking attendance and in-class polling.
• An appointments system for students to make appointments with faculty members, especially for advising sessions.
• A content management system like Canvas. (Or a subset of such a system — Canvas is quite large.)
• A system for classroom assignments and reservations. The system should contain information about every classroom's characteristics (IT has this information online) as well as managing reservations.
• A book-swap system, sort of like a mini-eBay for matching buyers and sellers of used books on campus.
• An online voting system for student voting in campus elections.
• A social networking platform.
• A race management system, handling registration and results.
• Consult with a faculty member about creating a database to make information already collected (e.g. scientific data) available on the Web.

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:

• the requirements document
• the ER diagram and a list of any specifications or constraints that are not represented in the ER diagram
• your design analysis

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:

• your database design (the normalized relational schema, with the constraints and triggers identified)
• writeup discussing the design choices made
• the requirements (data and functional)
• the ER diagram

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.