[ Home | Lab | New Students | Courses | Research | Publications | Activities ]

Back to Courses and Activities of Local Interest

Back to CSC 432 Home


Project for CSC 432: Database Management Projects

This is the description of the task for the main project for CSC 432. 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.

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 schemas. 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.
    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 schema 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 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

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.

Organization

The project will be a team effort, each team consisting ideally of 4 students. Please choose your partners by the drop date for this course. There is no extra credit for forming a team of fewer than 4 people. (If there aren't enough people around to make teams of 4, teams of 3 members would be acceptable. In this case, the number of application programs required for the assignment can be scaled back.)

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

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
M Schemas 600
Conceptual schema (1.7) 160
Logical schema (1.9, 2.1, 2.3, 2.4) 320
Integrity constraints, etc. (2.2) 120
A Application 480
SQL Queries (2.5) 240
Programs (3.2) 240
U User interface 160
Functionality & Robustness (3.4) 120
Manual (3.5) 40


singh@ncsu.edu