CPSC 343 Database Theory and Practice Fall 2017


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.

Teams and Topics


Projects are to be completed in teams of two. Teams will be assigned based on student preferences and skills.



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:

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 front end 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, plan big enough to achieve sufficient complexity in the data requirements; it's OK if you only implement some of the functionality.

You are encouraged to discuss your ideas with me prior to fully developing your specifications in order to determine if they would be suitable.


Note that these ideas are given as a starting point - you still need to make sure your topic satisfies the criteria given above.

Due Dates and Grading

All deadlines are in class unless otherwise noted. More details on what the different components entail and what to hand in are given in the "Project Components" section below.

Fri Sep 8 team survey (available on Canvas under Quizzes)
Mon Sep 18
topic approval (encouraged but not required)
Mon Sep 25 project specifications (milestone)
Fri Sep 29 peer review of project specifications
Wed Oct 18 conceptual model (milestone)
Mon Oct 23 peer review of conceptual model
Fri Nov 3 database design (milestone)
Mon Nov 13 peer review of database design
Fri Dec 8 implementation (database and application) / final handin


Grading will be based primarily on the final handin - specifications, conceptual model, database design, and the functionality of the application itself will all be assessed at this point. Peer reviews provide an opportunity for feedback earlier in the process, and you are always welcome to discuss your work at office hours.

What each component is graded on is outlined below in the "Project Components" section.

It is expected that all members of a group will contribute substantially. All members of a group will normally receive the same grade.

Project Components

Topic Approval

You are encouraged to discuss project topics with me to make sure they are appropriate (criteria given above) and of an appropriate scale. Something written (such as a draft of your specifications) is helpful; this can be followed up with a meeting during office hours or just email communication.

What to hand in: draft of specifications or other description of the topic

Graded on: not graded


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.

Begin by identifying your target audience (the stakeholders). Who will be using your system? Are there different groups of people with different needs?

As much as possible, interview members of each group of stakeholders. (If that is not possible, try to put yourself in their shoes and imagine how they would use the system.) What kinds of things does each group want to use the system for? What data is necessary for those tasks? What's the structure of that data?

Also consider any relevant existing systems. Use them to shed light on what your system will need to do - how do they work? What features do they have? What are they lacking?

This data-gathering should result in a requirements document with the following sections:

Note that your focus in all of this is only what the system will do, not how. The how will come later!

What to hand in: Your requirements document with the elements described above.

Milestone graded on:

Final handin graded on:

Conceptual Data Model

In this phase, you should produce an ER model for the data involved in your project. This should be based fairly directly on your requirements analysis, though you may wish to first revise your requirements list based on feedback from the peer review. (You may also need to revise your requirements if you discover things that weren't sufficiently nailed down as you work on modeling.) Your focus should be modeling the data in a conceptually accurate way, and then expressing as many of the constraints on the data as possible within the ER framework.

You should also write up a discussion of your design - when there were alternatives about how to model something, why did you make the choice you did? Did you run into any difficulties trying to express things within the ER model? (Were there things you couldn't express or couldn't express elegantly?) Explain.

What to hand in: (a) The requirements list (data and functional) from your requirements document, updated to include any revisions made. (b) The ER diagram. (c) A list of any specifications or constraints that are not represented in the ER diagram. (d) Your writeup.

Milestone graded on:

Final handin graded on:

Database Design

In this phase, you will complete the database design process:

What to hand in: (a) The requirements list (data and functional). This will likely be the same list you handed in for the previous phase, but should be updated if you have made any changes. (b) The ER diagram. This will likely be the same diagram from the previous phase, but should be updated if you have made any changes. (c) Your database design, including the five elements listed above.

Milestone graded on:

Final handin graded on:


This is it, implementation of the database and application. Use one team member's username_project database for the implementation of the database, and, if you are creating a web application, one team member's www/cs343/project directory (create the project subdirectory) for the PHP and HTML files.

For a passing grade, you should implement the full database design (tables, views, stored routines, triggers) but you only need to implement the essential functionality of the application itself - enough to provide some useful functionality, and to show mastery of the core ideas of a database application. (For a higher-than-passing grade, you should go beyond a minimal demonstration of functionality, and should generally have all of the essential and many of the important features implemented, though the specifics depend on the scale of the project.) Implemented functionality should work correctly; remove minor features that are incomplete or buggy. The database should contain enough data to usefully test/demonstrate the system.

In addition, you should produce two manuals: one for a user using the system, and one for the DB adminstrator managing the system. 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). The DB admin's manual should provide the information necessary for someone to understand the database should someone want to add or modify it or the data it contains - identify what the tables, attributes, views, stored routines, triggers are for.

Finally, a writeup should address 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.

What to hand in: (a) The code/files for your application (see below). (b) The database (including definitions for tables, views, and stored routines). (c) The specifications document (including the introduction, overview, functional and data requirements) and the conceptual (ER) model. (d) The user's and DB administrator's manuals. (e) Your writeup.

How to hand in your project:

Graded on:

Having particularly nifty features or features that go beyond the "essential" and "important" features can result in extra credit.


In order to encourage appropriate progress and to provide an opportunity for peer review and feedback, components of the project are due at various points during the semester.

What to hand in: As identified for specific components above.

Graded on: Satisfactory progress, as identified for specific components above. Late handins are not accepted, so you will also miss out on the benefits of peer review if you miss these deadlines.

Peer Review

For each of the three specifications and design phases, your team will review the work of several other groups and provide feedback to them. This is an opportunity to gain additional experience with the different elements of the project as you will get to see how others have done things as well as having to think critically about how to handle different situations.

What to hand in: For each assigned document to review, a writeup addressing the points outlined below. Identify which document each review is for.

Graded on: Thoroughness, accuracy of critique.


Consider the following points:

Conceptual Model

Consider the following points:

Database Design

Consider the following points:

Valid HTML 4.01!