UMBC              Spring 2002
CMSC 661        Database Management Systems


Database Design Project

Purpose

Design, implement, document, and demonstrate a database system using the Oracle installation at UMBC. Your system will support activities pertaining to student registration system. You are encouraged to use additional features and enhancements if any.

System Requirements

System Users: Faculty, Students
System Functions: To allow students and faculty(as appropriate) to
1. Authenticate themselves as users of the system
2. Register for courses (for an upcoming semester)
3. Provide and maintain information about students and courses
4. Obtain reports on student status, course schedule
5. Enter final grades for students at the end of a semester

Information contained in the system:
1. Authentication information for each type of users -- name, Id number, password, and user type.
2. Academic record of each student
    -- all course information (when the student completed the course and the grade received)
    -- courses that the student is currently enrolled in
    -- courses that the student has registered in the next semester.
3. Course related information
    -- name, number, department
    -- when offered (you may want to distinguish a course that is offered currently or in the next semester by calling it a class)
    -- prerequisites
    -- if offered currently or next semester, section number, instructor, location(classroom identifier), meeting time, maximum enrollment, the number of enrolled or registered students, credit hours
 

Constraints (not limited to the list shown below):
1. If a student is listed as enrolled or registered in a course, that course must be offered in the current semester or the next semester.
2. Number of students enrolled or registered in a course must be less than the maximum enrollment.
3. An instructor cannot be assigned to two courses taught at the same time in the same semester.
4. Two courses cannot be taught in the same room at the same time in a given semester.
5. A student cannot be enrolled or registered in two courses taught at the same time.
6. A student cannot be registered for more than 20 credit hours.
7. A student registering for a course must have a "C" or better grade in courses that are listed as its prerequisites.

Deliverables:

Phase I  (Due April 2, 2002 or sooner)

1. An E-R diagram for the design of your system (including all additional assumptions you made in developing that design)
2. A relational schema
3. Appropriate views for each group

Phase II (Due April 16, 2002 or sooner)

1. revisions to the design, if any
2. SQL statements to create the relational schema
3. SQL statements to create the constraints (primary, foreign, unique, check, not null)
4. Pseudo code for at least one function using PL/SQL (for example, computing the GPA of a students taking into account all completed courses)
5. System data -- be sure to include sufficient number of faculty, students, courses, classes to support meaningful queries

Phase III (Due May 9, No late submission accepted)

Submit a final report consisting of
1. Final design document
2. Sample output for each system function
3. Source code
4. A contribution log for each team member

In addition, a 10-15 minute demo of the system to the TA or Dr. Mundur is required. All team members are required to attend the demo and explain their contribution towards the completion of the project.