CS348: Project #3


Overview

You are going to use PL/SQL (Oracle's procedural extension to SQL) to write a few functions and procedures to process data. The result of the work should be a file named your_login.sql which contains all the PL/SQL statements you used to handle the data processing tasks listed below. Your grade depends on how you use the PL/SQL statements and the result of running the resulting .sql file.

Due date: March 26th  at 11:59pm.


Step 1: Setup

You will use the same schema as you did for Project #2. Do not forget to create the PKs and FKs to test your code. This step is necessary to build the PL/SQL procedures and functions but it will NOT be included in your your_login.sql.

Your task:

1. Create the 5 tables.

2. Execute the file (data.sql) to insert the test data into the tables you created in the previous step.

 

Top


Step 2: PL/SQL

Overview


Setup

Refer to Step 1.

Important: The first line of your .sql file should be the following:

set serveroutput on size 32000
... otherwise don't come complaining to me when dbms_output isn't printing anything out. :)


Procedure: pro_department_report

Generate a report that lists, for each department, the faculty members in that department. The output should be modeled as follows. Sort by the department name; also sort faculty members by their name.

Department: Computer Sciences
Total faculty members: 3
-------------
1. Alice
2. Bob
3. Joe

Department: ECE
Total faculty members: 2
-------------
1. Joyce
2. Sam

Department: Management
Total faculty members: 0
-------------
...

Procedure: pro_faculty_stats

Generate a report that contains statistics about faculty. Print out the student to faculty ratio (integer-truncate the students for the faculty ratio). Print out the average number of classes per faculty (truncate to 1 decimal place). Then, print out the number of students that each faculty is teaching; omit faculty with no students. Sort by faculty name.

Student to faculty ratio:  20:1
Average number of classes per faculty:  1.3

Faculty Name        # Students
---------------     ----------
Bob                 15
Joe                 30
...

Procedure: pro_student_stats

Generate a report about the number of classes a student is in. The report should be as follows:

                   # classes
Student Name     0   1   2  >=3
------------    --- --- --- ---
Joe              X
Mary                     X
Random               X
...

Procedure: pro_histogram

Generate a histogram for the ages of students. Include all discrete integer values in the range min(age) .. max(age). Mark the statistical median on the graph (in this case, it's 19). [Aside: Do you really know the definition of median? What if the size of your input set is even? Be careful on this one.] An example report is as follows:

age
17   | 1    x
18   | 10   xxxxxxxxxx
19   | 5    xxxxx <-- median
20   | 2    xx
21   | 8    xxxxxxxx
22   | 3    xxx
23   | 0
24   | 1    x

Procedure: pro_insert_class

Write a procedure to insert a new class, given a faculty member's name. The input will be as follows: fname_in, cname_in, meets_at_in, room_in. The result will be a new class added. Assume there are no duplicate fname's in the faculty table. Execute this procedure to add the class CS100, meeting at 530 in A001, being taught by S.Boling. Do a select * from class before and after this procedure is run.

Your task:

Create the file your_login.sql and include here the answers for the 5 questions of this section. Your file will look something like this:

/* create the procedure */
create or replace procedure report_name as
/* declarations */
begin
    /* code */
end;
 
/
 
/* actually run the procedure */
begin
    report_name;
end;
 
/
 
...

Important:

1. The first line of your .sql file should be the following:

set serveroutput on size 32000
... otherwise don't come complaining to me when dbms_output isn't printing anything out. :)

 
2. Include in your your_login.sql a query that retrieves the whole contect of the table STUDENT before and after the changes (before question 5 and after question 6). 
 
 
 
3. for pro_histogram, only mark the median if it comes out to an integer value. 
 
 
 
4. for pro_insert_class, the behavior for providing a non-existent faculty name is
undefined.  We won't test it. 
 
 
 
5. for pro_faculty_stats, if a student a faculty member is teaching two classes and a
student is enrolled in both, only count that student once in the stats
 
 

Top


Step 3: Submitting your work

Please create a README file that contains identifying information.

To turn in your project, create a temporary directory. Copy the files you created for this project into that directory. Cd to that directory. Then execute the following command:

 
    $ turnin -c cs348 -p proj3 .

Top


Resourses

You can find additional information about creating stored procedures/functions in PL/SQL in the following websites.

 
1. http://www-db.stanford.edu/~ullman/fcdb/oracle/or-plsql.html
2. http://www.unix.org.ua/orelly/oracle/prog2/index.htm
 
 

Top


Back