Reports

1. Project report #1 (520 points)
   1. (10 points) Describe the problem concisely (less than about 250 words). Why is a database (as opposed to, say, a simple set of files) a good idea for this task?
   2. (10 points) Describe the intended classes of users of your database system.
   3. (10 points) Identify 5 main "things" about which you will need to keep information, and the information you will need to keep.
   4. (10 points) Describe realistic situations where you might wish to apply any two of the operations. (about 50 words each).
   5. (80 points) Sketch the interfaces (i.e., forms) required for each of the operations (both input and output - in some cases the output will just be a confirmation).
   6. (80 points) Describe the views of the data corresponding to the above operations (in about 50 words each).
   7. (160 points) Construct local conceptual (ER) schemas for each view.
   8. (40 points) Document the local conceptual schemas. Highlight any design decisions - specifically, describe why you have the entities and relationships that you do.
   9. (80 points) Derive a local logical (relational) schema from each of the above local conceptual models. Normalize at least to 3NF.
   10. (40 points) Document the local logical schemas. Highlight any design decisions - specifically, explain why decided to make the relations you did and how each entity and relationship in the ER schema is captured in the logical schema.

2. Project report #2 (600 points)
   
   1. (80 points) Derive a global logical schema from the above. Normalize at least to 3NF.
   2. (120 points) Describe any design decisions for the global schema. Identify and explain all integrity constraints. Describe which attributes are allowed to be NULL, why, and what a NULL value means for each attribute on which it is allowed.
   3. (80 points) For each operation, show a transaction that would implement it. Showing a transaction simply involves specifying its input and output relations, and drawing links to connect them. The input and output relations come from the views mentioned above, but the views can sometimes have gaps. The present step, by connecting the relations through the schema ensures that any gaps have been filled. The connections can be hand-drawn on the model diagrams.
   4. (80 points) Create base relations with the right attribute domains, etc. Populate the base relations with 4-8 rows each. Show what is in each table by printing out a "SELECT * FROM table" for each table.
   5. (240 points) Write interactive SQL queries for each operation, and test via wisql (preferred) or isql. Turn in the trace for each query. Choose values for the constants so that a non-empty answer set is returned. You may need to add additional rows to do so. Explain why each SQL query is correct. This is important and part of the explanation can be the corresponding relational calculus expression. If you come up with erroneous solutions prior to the correct one, also include those as part of the explanation.

3. Project report #3 (480+120 points)
   As part of the documentation, explain who played what functional role when.
   1. Submit revised versions of the previous reports - you will get credit on the improvements, scaled by 50%. Please highlight the improved parts.
   2. (240 points) Write applications using embedded SQL, and test appropriately. Be sure to check the inputs for errors.
   3. (80 points) Document your programs. The documentation should be part of the code, but will be graded in addition. Highlight design decisions.
   4. (120 points) Demo. Graded on functionality & robustness, and ease of use of your programs. You are required to implement a graphical user interface (GUI), e.g., using HTML and CGI, or Java, or any other suitable approach.
   5. (40 points) User manual for one selected operation from each of the four tasks.

Implementation Plan

1. Understand the problem.
2. Create a conceptual schema.
3. Create a logical schema.
4. Create base relations.
5. Input these into the DBMS.
6. Design the SQL queries and updates to realize the various operations, and test them.
7. Create forms for each operation, as well as application code to exercise them appropriately.
8. Test by hand.
9. Give a demo to the TA and the instructor.

Organization

The project is recommended to be a team effort, each team consisting ideally of 4 students. Please choose your project partners by the drop date. There is no extra credit for forming a team of fewer than 4 people, but such teams are allowed. In such cases, the number of application programs required for the assignment is scaled down, but the remaining tasks are not scaled down.

The team will have the following functional roles. Every member will have to do some database design and application programming, so the roles can be rotated among the members. Each team member will write the same number of application programs. The prime and backup application programmers will do this first.

User Interface Specialist (prime: and backup:)

Responsible for designing and implementing the user interface for the project.

Software Engineer (prime: and backup:)

Responsible for designing the software architecture, ensuring code quality, and testing and documentation. Also responsible for plan.

Database Designer (prime: and backup:)

Responsible for designing the conceptual and logical schemas, based on the given informal specifications.

Application Programmer (prime: and backup:)

Responsible for designing and implementing the actual applications (operations) that use the designed database.

Grading