pl/sql help for school project

From: dinyah <dinyah_at_bigfoot.com>
Date: Tue, 20 Oct 1998 22:33:36 -0400
Message-ID: <362D4800.684A7E15@bigfoot.com>

Students(SSN, Name, GPA, Dept_name)
Courses(Course_no, Title, Hours, class_size, Limit) Enrollments(SSN, Course_no)

with the following definitions:

      create table students
       (SSN char(9) not null,
        name varchar2(30),
        GPA number(3,2),
        dept_name varchar2(20),
        primary key (SSN));

      create table courses
       (course_no char(7) not null,
        title varchar2(30) not null,
        hours number,
        class_size  number,
        limit number,
        primary key (course_no));

      create table enrollments
       (SSN char(9) not null,
        course_no char(7) not null,
        primary key (SSN, course_no),
        foreign key (SSN) references students,
        foreign key (course_no) references courses);

In table courses, hours is the credit hours of a course; class_size is the
number of students currently taking the course; limit is the maximum number
of students allowed for the course. It is assumed that there is only one

session for each course.

You should populate the above tables with appropriate tuples to test your
program.

Project Requirements.

Your PL/SQL package should be able to perform the following tasks:

 Display the tuples in each table. As an example, you can use a procedure, say show_students, in your package to disply all students
in the students table.

 Insert a student in the students table. The information of the student to be inserted is provided as parameters to your procedure. If
a student with the given SSN is already in the students table, report ``The
student already exists!" and the student will not be added.

 For a given student (with SSN provided), list the SSN and name of the student as well as all courses (show course number and title)
the student has registered. If the student is not in the students table,

report ``The SSN is invalid.".

 Register a student to a course. The SSN of the student and the course number of the courses are provided. If the student is not in

the students table, report ``The SSN is invalid.". If the course is not in
the courses table, report ``The course number is invalid.". If the registration of the student into a course would cause ``class_size > limit'',
reject the registration and report ``The course is closed.". For all other
cases, the registration should be performed. If the total number of credit
hours of the courses registered by the student (including the course just
added) exceeds 18, report ``The student is overloaded.". You should make

sure that all data are consistent after each registration. For example, after you registered a student to a course, the class size of the course

should be updated accordingly.

 Drop a student from a course. The SSN of the student and the course number of the courses are provided. If the student is not in

the students table, report ``The SSN is invalid.". If the course is not in the courses table, report ``The course number is invalid.". If the student is not registered for the course, report ``The student is not registered for the course.". If the course is the last course for the student, report ``This student takes no courses.''. If the student is the last student in the course, report ``The course now has no students.".
Again, you should make sure that all data are consistent after the change.

 Implement an interactive and menu-driven interface in the bingsuns environment. This task is not required and no help will be
available from the instructor. The following sample program may be of help
to you if you choose to do the bonus task.

// Save the program into a file test.C and use // g++ -o test test.C to generate the executable file test

#include <stdlib.h>
#include <stdio.h>
#include <string.h>
#include <fstream.h>
#include <iostream.h>

int main()
{

  int flag;

  while ( 1 )

     {
      system("clear");
      cout << "Please choose the following:" << endl;
      cout << "1: Checking the available accounts." << endl;
      cout << "0: Exit;" << endl;
      cin >> flag;
      if (flag == 0)
          break;

      system("sqlplus -s userid/password < query.sql");
      cout << "Choose 1 for continue." << endl;
      cout << "Choose 0 for quit." << endl;
      cin >> flag;
      if (flag == 0 ) break;

    }
  return EXIT_SUCCESS;
} Received on Tue Oct 20 1998 - 21:33:36 CDT