Project for CSC 742: Database Management Systems

This is the description of the task for the main project for CSC 742. The details may change over time, so please visit the online version again. The project will be based on the case study presented in the narrative. Your main task is to develop an information system. In order to do this systematically, you will need to perform at least the subtasks described in terms of project reports.

New Each group should keep an online version of each report after report submission (preferably the day after submission). It should be either pdf or html file. You should put the URL to the report on the report title page. Failing to provide this information will cost you 20 points.


Each report must have a title page, which include course number, course title, semester information, instructor name,  the names of the group members, separately listed four functional roles with prime and backup (see organization), URL to the online version of the report, and submission date. Project reports must be generated by a word processor; hand-written reports are not accepted. Failing to meet these requirements will cost you 20 points.

  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 (520 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) 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.
    4. (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 (560 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. (320 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

The way this project is envisioned you will carry out the following steps.
  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.

DBMS for Your Implementation

By default, you should do your homework and course project using the Sybase server provided by the Computer Science department. Other DBMSs are allowed for the course project provided that the following conditions are met: 


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


The programming project will be an implemented system and described in a report. The report will include an ER schema, a logical (i.e., relational) schema and rationale, physical design and rationale, description of the queries and transactions (embedded SQL and program logic). The grading for the project will be totaled over three modules as follows. Some of these categories correspond to specific items in project reports (as listed). The others are spread over several items.
Section Category Max Points
G General 360
Problem description (1.1-1.6) 200
Documentation (1.8, 1.10, 3.3) 160
S Schemas 520
Conceptual schema (1.7) 160
Logical schema (1.9, 2.1, 2.3) 240
Integrity constraints, etc. (2.2) 120
A Application 560
SQL Queries (2.4) 240
Programs (3.2) 320
U User interface 160
Functionality & Robustness (3.4) 120
Manual (3.5) 40
T Total 1600