Database design (draft)
Code and design aided by Prof. Scharff (thanks!)
E-mail me any suggestions for improvements
To be implemented into matrix.csis.pace.edu so it can be accessible when user is online. If we have trouble doing that, then the database would be implemented locally on the computer that's being run on. (Download the Oracle9i 9.0.1 JDBC Thin Driver to use, I think. See also: How to use Oracle JDBC drivers (for Oracle9i Release 1 (9.0.1))
Assumptions:
-
All the classes and their infomation (CRN, professor, class size, credits, meeting day(s), time class begins, time class ends, enrollment) is entered into the Class table. The program will not change the class infomation except for Enrollment, which will be incremented by 1 for every student that have registered for that class.
-
All the students that are allowed to registered are already entered into the Student table. The program will not add any new student to be added to the Student database.
The SQL code of the tables and simple test cases are here (don't paste the comments when you run it)
3 tables of data: Class, Student, Registration
In table Class, it will contain 12 columns of information (note that there are only 2 meetings for each class section to reduce the number of columns and simplify the complexity of the program):
-
Crn INTEGER,
-
Class_Name CHAR (7),
-
Professor CHAR (15),
-
Class_Size INTEGER,
-
Enrollment INTEGER, (the number of students have enrolled into the class)
-
Credits INTEGER,
-
Day1 CHAR (10),
-
Time_Start1 CHAR (7),
-
Time_End1 CHAR (7),
-
Day2 CHAR (10),
-
Time_Start2 CHAR (8),
-
Time_End2 CHAR (8),
-
PRIMARY KEY (Crn)); (the primary key is CRN for use to differentiate each class section)
In table Student, it will have 6 columns of information:
-
Stud_Id INTEGER,
-
Last_Name CHAR(15),
-
First_Name CHAR(15),
-
password CHAR(10),
-
email CHAR(30),
-
Status CHAR(3), (indicates the year the student is in, i.e. freshman (fre), sophomore (sop), junior (jun), senior (sen))
-
PRIMARY KEY (Stud_Id) (the primary key is Stud_Id for use to differentiate each student)
The Registration table records the classes the student registered. It will have 2 columns of information:
-
Stud_Id INTEGER,
-
Crn INTEGER,
-
PRIMARY KEY (Stud_Id,Crn) (there are 2 primary keys: Stud_Id and Crn; they will be used to search what classes the student has registered)
Draft last updated: March 26, 2002
