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.
- Project report #1 (520 points)
- (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?
- (10 points) Describe the intended classes of users of your
- (10 points) Identify 5 main "things" about which you will need to
keep information, and the information you will need to keep.
- (10 points) Describe realistic situations where you might wish to
apply any two of the operations. (about 50 words each).
- (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).
- (80 points) Describe the views of the data corresponding to the
above operations (in about 50 words each).
- (160 points) Construct local conceptual (ER) schemas for each
- (40 points) Document the local conceptual schemas. Highlight any
design decisions - specifically, describe why you have the entities
and relationships that you do.
- (80 points) Derive a local logical (relational) schema from each
of the above local conceptual models. Normalize at least to 3NF.
- (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.
- Project report #2 (520 points)
- (80 points) Derive a global logical schema from the above.
Normalize at least to 3NF.
- (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.
- (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
- (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.
- Project report #3 (560 points)
As part of the documentation, explain who played what functional role
- Submit revised versions of the previous reports - you will get
credit on the improvements, scaled by 50%. Please highlight the
- (320 points) Write applications using embedded SQL, and test
appropriately. Be sure to check the inputs for errors.
- (80 points) Document your programs. The documentation should be
part of the code, but will be graded in addition. Highlight design
- (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.
- (40 points) User manual for one selected operation from each
of the four tasks.
The way this project is envisioned you will carry out the following
- Understand the problem.
- Create a conceptual schema.
- Create a logical schema.
- Create base relations.
- Input these into the DBMS.
- Design the SQL queries and updates to realize the various
operations, and test them.
- Create forms for each operation, as well as application code to
exercise them appropriately.
- Test by hand.
- 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:
- First, you have to use either Java Servlets or JSP to support your
- If you plan to show your demo on your own server (which could be your
laptop), you can use any DBMS. I would recommend that you choose one of the
major commercial DBMSs such as Oracle and Sybase.
- If you plan to show your demo on one of my research machines, you have to
use one particular DBMS on one particular OS. I will decide which DBMS and
OS to use after I talk to the people who would like to do so.
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
- 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
|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 |