1 Introduction

2 Design - Requirements of your application

You will form groups of three people. In all the documents you hand in, clearly mark the names of the people in the group. Also, mention any specific contributions or work of each member. Each group can choose a non-trivial application (e.g., an enterprise involving stocks, orders, invoices, etc., hospital, insurance company, theater or playhouse reservation or anything you might find interesting, useful and perhaps original).

You need to hand in the followings:

1. ER-diagram of your application.
   The minimum requirements in terms of the overall complexity are: at least 6 entities and 5 relationships (among which at least 1 many-to-many and 2 one-to-many relationships). Describe the necessity of each entity/relationship, argue about the selection of primary keys, state clearly your assumptions about mapping cardinalities.
2. A detailed description of your application.
   The description should include the following:
   1. The main goal of your application
   2. The potential user(s), e.g. if you wish to support views explain why and what kind of views might be involved.
   3. The overall flow of information. How will the final product be like? How does a user navigate through the main functional parts of your design? What are these parts? How is the data acquisition phase going to be like in terms of user friendliness, errors etc?
   4. Most importantly give a collection of sample queries you would like your application to be able to execute. Try to think which queries will be executed more often (i.e., popular queries): you might want to 'tune-up' your design accordingly.
   5. Finally state any extensions you could have done should more time and resources were available.

3 The Relational Model

Based on your design with possible modifications and corrections, create all the necessary tables (they should be at least 10) and justify why you might choose not to create separate tables for one-to-many or one-to-one relationships (think of the complications arising from such a decision). For example, NULL values and how you expect to treat them. Finally, primary keys should be clearly stated as well as the type of all attributes involved.

4 Implementation

You are allowed to do last minute changes and to reconsider part of your initial design, but be prepared to be able to justify all these changes.
In terms of implementation, there will be an introductory session on Perl and Oracle or MySQL and PHP. You will be required to incorporate most of the following features:

1. loops (simple, nested and quantification).
2. some complex SQL queries involving aggregate operators, group-by etc.
3. user friendly interface (windows, special keys, help keys, forms)
4. parameter passing among frames
5. integrity constraints (type checking, value range checking, referential integrity, Boyce-Codd Normal Form (in applicable))
6. views (if applicable)
7. Examples of entry, exit, and select processing

• what if the user types in an erroneous character or datum?
• how easy is it for her to acquire information and to navigate through the different windows?
• what should I do to enforce privacy? How do I protect different views from undesired users (if applicable)?
• which steps or side effects are involved as a result of an insertion? deletion? update? How do I treat these cases?

You need to hand in the followings:

1. hand-in a hard copy of your code files.
2. be prepared for the presentation slides.