Project 10.3
University Student Database

You will implement a University Registration System (similar to GoSolar at GSU) in Java using JDBC. The database schema is available at hw5.sql.

There are 3 kinds of database users:

  1. Registrar Staff: These users will have the ability to load the database tables, make changes to courses, sections, fee details etc.
  2. Department Staff: These users will have the ability to authorize students into sections, overflow students into sections, add assistantship information to the system, generate class lists etc.
  3. Student: These users will be able to register for classes, see their schedules, see fee detail, see transcripts etc.

The following real-world constraints need to be enforced by your Java program:

  1. Undergraduate students are not allowed to register for graduate courses numbered 6000 and above.
  2. Students should not be allowed to register for a class which is FULL unless they have an overflow.
  3. Students should not be allowed to register for a class which is listed as AUTHORIZATION ONLY unless they have an authorization.
  4. Undergraduate students are not allowed to register for more than 20 hours in a semester and the limit for graduate students is 15.
  5. Students cannot register for two classes that overlap in meeting time.
The Java application will be a terminal-based program that has the following interactions with the users. Based on the username, the program should determine the type of user and provide the appropriate menu.

Department Staff Menu: 

[raj@toto project]$ java GoLunar OracleId
Oracle Password:xxxxxx
Semester (e.g. FA2003,SP2003,SU2003): SP2003
Username: 1000
Password: 
 
**********************************************************************
***                                                                ***
***    Welcome to the GoLunar - Online Registration System         ***
***        Venette Rice - Department Staff                         ***
***                                                                ***
**********************************************************************

                         1. Authorize Student into Section

                         2. Overflow Student into Section

                         3. Add Assistantship on System

                         4. Generate Class List

                         q. Quit


Type in your option: 1
CRN:10101
SID:1111
Student John Davison authorized into CRN 10101, CSC 2010.
OR
No need to authorize - This section does not need authorization.



**********************************************************************
***                                                                ***
***    Welcome to the GoLunar - Online Registration System         ***
***        Venette Rice - Department Staff                         ***
***                                                                ***
**********************************************************************

                         1. Authorize Student into Section

                         2. Overflow Student into Section

                         3. Add Assistantship on System

                         4. Generate Class List

                         q. Quit


Type in your option: 2
CRN:10101
SID:1111
No need to overflow - Space still available in this section.
OR
Student John Davison overflowed into CRN 10101, CSC 2010.

**********************************************************************
***                                                                ***
***    Welcome to the GoLunar - Online Registration System         ***
***        Venette Rice - Department Staff                         ***
***                                                                ***
**********************************************************************

                         1. Authorize Student into Section

                         2. Overflow Student into Section

                         3. Add Assistantship on System

                         4. Generate Class List

                         q. Quit


Type in your option: 3
Student Id: 3333
Ashish Bagai (3333) has been added to the Assistantship List.


**********************************************************************
***                                                                ***
***    Welcome to the GoLunar - Online Registration System         ***
***        Venette Rice - Department Staff                         ***
***                                                                ***
**********************************************************************

                         1. Authorize Student into Section

                         2. Overflow Student into Section

                         3. Add Assistantship on System

                         4. Generate Class List

                         q. Quit


Type in your option: 4
CRN: 10101

CSC 2010, Introduction to Computer Science
SP 2003
Instructor: Bhola

SID           LNAME           FNAME
---------------------------------------------
1111          Davison         John
2222          Oram            Jacob
...
...

Student Menu: 

[raj@toto project]$ java GoLunar OracleId
Oracle Password:xxxxxx
Semester (e.g. FA2003,SP2003,SU2003): SP2003
Username: 1111
Password: 
 
**********************************************************************
***                                                                ***
***    Welcome to the GoLunar - Online Registration System         ***
***        John Davison - Student                                  ***
***                                                                ***
**********************************************************************

                         1. Add a Section

                         2. Drop a Section

                         3. See Schedule for a Term

                         4. See Fee detail

                         5. See Transcript

                         q. Quit


Type in your option: 1
CRN: 10101
CSC2010, Introduction to Computer Science ADDED.
OR
Appropriate Error Message.


**********************************************************************
***                                                                ***
***    Welcome to the GoLunar - Online Registration System         ***
***        John Davison - Student                                  ***
***                                                                ***
**********************************************************************

                         1. Add a Section

                         2. Drop a Section

                         3. See Schedule for a Term

                         4. See Fee detail

                         5. See Transcript

                         q. Quit


Type in your option: 2
CRN: 10101
CSC2010, Introduction to Computer Science DROPPED.
OR
Appropriate Error Message.



**********************************************************************
***                                                                ***
***    Welcome to the GoLunar - Online Registration System         ***
***        John Davison - Student                                  ***
***                                                                ***
**********************************************************************

                         1. Add a Section

                         2. Drop a Section

                         3. See Schedule for a term

                         4. See Fee detail

                         5. See Transcript

                         q. Quit


Type in your option: 3
Term: FA2002

CRN   Course  Title                            Days Time        Room Instructor
-------------------------------------------------------------------------------
10101 CSC2010 Introduction to Computer Science MWF  09.00-09.50 105G Bhola
...
...



**********************************************************************
***                                                                ***
***    Welcome to the GoLunar - Online Registration System         ***
***        John Davison - Student                                  ***
***                                                                ***
**********************************************************************

                         1. Add a Section

                         2. Drop a Section

                         3. See Schedule for a term

                         4. See Fee detail

                         5. See Transcript

                         q. Quit


Type in your option: 4
Term: sp2003

Spring 2003

  Tuition - InState 
      (12 hours)     1,500.00
  Technology Fee        75.00
  Health Fee            30.00
  Activity Fee          65.00
  Transportation Fee    25.00
                     --------
                     1,695.00
                     --------

**********************************************************************
***                                                                ***
***    Welcome to the GoLunar - Online Registration System         ***
***        John Davison - Student                                  ***
***                                                                ***
**********************************************************************

                         1. Add a Section

                         2. Drop a Section

                         3. See Schedule for a term

                         4. See Fee detail

                         5. See Transcript

                         q. Quit


Type in your option: 5

Summer 2002
  CSC  1010 10101 Computers and Applications          3 A 12.00
  POLS 1200 10701 Intro Political Sci                 3 C  6.00
       Semester GPA: 3.00   GPA: 3.00

Fall 2002
  CSC  2010 10101 Introduction to Computer Science    3 A 12.00
  CSc  2310 Introduction to Programming in Java       3 A 12.00
  Math 2211 Calculus I                                5 B 15.00
       Semester GPA: 3.54    GPA: 3.35
...
...

Registrar Staff Menu: 

[raj@toto project]$ java GoLunar OracleId
Oracle Password:xxxxxx
Semester (e.g. FA2003,SP2003,SU2003): SP2003
Username: 2000
Password: 
 
**********************************************************************
***                                                                ***
***    Welcome to the GoLunar - Online Registration System         ***
***        Alison Payne - Registrar Staff                          ***
***                                                                ***
**********************************************************************

                         1. Load Sections from File

                         2. Load Grades from File

                         3. Increase Section Cap

                         4. Display Term Schedule

                         5. Display Student Transcript

                         6. Display Student Schedule and Fee Detail

                         q. Quit


Type in your option: 1
File Name: sections.dat
Sections Loaded


**********************************************************************
***                                                                ***
***    Welcome to the GoLunar - Online Registration System         ***
***        Alison Payne - Registrar Staff                          ***
***                                                                ***
**********************************************************************

                         1. Load Sections from File

                         2. Load Grades from File

                         3. Increase Section Cap

                         4. Display Term Schedule

                         5. Display Student Transcript

                         6. Display Student Schedule and Fee Detail

                         q. Quit


Type in your option: 2
File Name: grades.dat
Grades Loaded



**********************************************************************
***                                                                ***
***    Welcome to the GoLunar - Online Registration System         ***
***        Alison Payne - Registrar Staff                          ***
***                                                                ***
**********************************************************************

                         1. Load Sections from File

                         2. Load Grades from File

                         3. Increase Section Cap

                         4. Display Term Schedule

                         5. Display Student Transcript

                         6. Display Student Schedule and Fee Detail

                         q. Quit


Type in your option: 3
CRN: 10101
Old Capacity is 35 
New Capacity: 45
Cap Updated for CRN 10101.


**********************************************************************
***                                                                ***
***    Welcome to the GoLunar - Online Registration System         ***
***        Alison Payne - Registrar Staff                          ***
***                                                                ***
**********************************************************************

                         1. Load Sections from File

                         2. Load Grades from File

                         3. Increase Section Cap

                         4. Display Term Schedule

                         5. Display Student Transcript

                         6. Display Student Schedule and Fee Detail

                         q. Quit


Type in your option: 4
CRN   Course  Sec  Days  Time        Room  Cap Cur Avail Instructor Auth 
------------------------------------------------------------------------
10101 CSC2010 1    MWF   09.00-09.50 105G  35   3   32   Bhola      N
10102 CSC2010 2    MWF   10.00-10.50 105CS 40   5   35   Henry      N
...
...

Options 5 is similar to student option except here the system should
accept student id as input and display that 
student's transcript.

Option 6 is similar to student options except here the system should
accept student id (in addition to term) as input and display that student's
term schedule and fee detail for the particular term.
Sample files for loading data in the Registrar's options are available in
  1. sections.dat
  2. grades.dat

This project will be completed in two parts (HW 5 and HW 6). In HW 5, you are required to complete the following options:

  1. Registrar Staff Options 1, 2, 4, and 5
  2. Student Option 5
The remaining will be completed in Homework 6.