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

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.

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.

Topic Selection

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:

  • combining information from two or more related tables (e.g. "which branch is a given book currently checked out from?")
  • a question whose answer depends on the result of another related question or computation (e.g. "which borrowers have checked out more books than the average borrower this year?")
  • summarizing or counting across many rows (e.g. "how many books has each borrower checked out this year?")
  • combining results from two different questions into one list or comparing two such lists against each other (such as building a single "recommended reading" list that draws from both "recently returned books" and "newly acquired books" at once, or finding borrowers who show up on a "frequent visitor" list but not on a "has an overdue book" list).

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

• A classroom participation tool which supports taking attendance and in-class polling.
• 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.
• A campus intramural sports league management system, handling team rosters, game scheduling, and results/standings.
• A restaurant or campus dining ordering system with a customer loyalty/rewards program.

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).

Deliverables

Project Proposal

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.

Topic Checklist

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.

Check-in and Handin

Check-in Meeting — must be completed by 9/23

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!

Deliverables Handin — due in class 9/18

Hand in a hardcopy of your project proposal and the topic checklist.

Handin Meeting

There is no separate handin meeting for this milestone.