Goals

• Learn to use Oracle.
• Design a database application from first principles.
• Apply theoretical concepts done in class.
• Raise problems and understand limitations of current technologies.

What to do

• Decide who you want to work with. I suggest 5 teams of 3, but if that doesn't work, we can have smaller (but not larger) teams. One-member team not encouraged.
• Get together with your team mates for a brainstorming session and decide what kind of database you want to implement. The initial specifications can be open ended, but as you refine the project, I expect the following:
  • A one-page informal description in English of your project: functionality, intended group of users, whether you think it is a realistic or just a "toy" database, projected user interface.
  • An entity-relationship chart documenting the process of defining the structure of the database.
  • A list of constraints for maintaining the referential integrity of the database, as well as all possible functional dependencies you could come up with. A short argument of why the set of tables you have finally decided upon satisfy basic requirements for robust relational database design.
  • Implementation in SQL, including: data entry (forms or other type of interface) and reports, security (user categories and specific interfaces for all of them, views and appropriate permissions granted to all of them), tables, constraints, queries. The final project should be as close as possible to a stand-alone application.
  • A final presentation, with "real" data and examples, organized like a product presentation (for the prospective users). A written documentation, including all the material refered to above, describing the implementation, from specifications to Oracle.
• A timetable: when you plan to complete each phase of your project. This should include: teaching yourself Oracle, meeting with the team members for discussing specifications and making decisions on the structure, implementation and who does what. Deadlines for written reports (intermediate phases are recommended), final report, final presentation preparation.

When is due

• Team composition: Tuesday, Oct. 26.
• Timetable and first draft of the database proposal: Th Oct 28. To be presented in class, briefly (5 minutes).

Constraints

• No less than 4 tables.
• Users in several imaginable categories, from end-user to data-entry typist, to administrative assistant with several privileges but not all to manager with read privileges on all the data, to database manager and other occasional users.
• At least 3-4 views.
• Data entry problem solved efficiently. "Real" data entered for testing.
• At least as many queries, and of similar level of complexity, as on your midterm in-class exam.
• All queries tested and the correctness demonstrated on meaningful examples. I will not accept queries that return (on your data) empty answers, or do not cover several possible imaginable cases.
• Keys, integrity constraints, dependencies - analyzed and implementation decisions discussed.
• Decent user interface (forms, reports).
• As close to a stand-alone application as possible.

Caveat

Due