Overview

In this assignment you are going to use PL/SQL (Oracle's procedural extension to SQL) to write five procedures to process data

Step 1: Setup

The schema for the database is as follows (the DDLs are in the file tables.sql https://www.mediafire.com/file/ly89b79d5c0ef3t/TABLES.sql/file):

1. STUDENT(*snum: integer, sname: string, major: string, deptid: integer, slevel: string, age: integer)

2. CLASS(*cname: string, meets_at: date, room: string, fid: integer)

3. ENROLLED(snum:integer, cname: string)

4. ACULTY(*fid: integer, fname: string, deptid: integer)

5.DEPARTMENT (*deptid: integer, dname: string, location:string)

Asterisk * denotes Primary Key

Create the tables with all the key and referential integrity constraints necessary to model the application. Make sure that your field & table names correspond to those listed above. Then populate your database by executing the file data.sql (https://www.mediafire.com/file/wb2id1bhlbde3hh/data.sql/file) provided.

Step 2: PL/SQL

Create a file named procedures.docx. Your file should contain code to create and run five procedures: pro_department_report, pro_student_stats, pro_faculty_stats, pro_histogram, pro_enroll. The description of each procedure is provided below.

Your file should look something like this:

/* create the procedure */

create or replace procedure pro_department_report as

/* declarations */ begin

/* code */ end;

/

/* actually run the procedure provide a screen shot of your output*/

begin

pro_department_report; end;

/

Step 3: Procedures

1.

Generate a report that contains statistics about students. Print out the number of classes that each student is taking; omit students taking no classes. Sort by student name.

Hint: Use a cursor and a sql select statement that has count(*), also use a table for the student table.

Sample output:

Student Name # Classes

--------------- ----------

Bob 3

Joe 2

...

2.

pro_faculty_stats: Generate a report about the total number of students each faculty teaches. Sort results by faculty name. The number of students for each faculty should be marked with an X under the corresponding bin.

You should create 4 equal-sized bins for the number of students based on the minimum and maximum number of students any faculty teaches. For example, if the minimum number of students any faculty teaches is 0 and the maximum is 8, the bins created should be those in the sample output below. If the difference between the minimum and maximum number of students is not divisible by 4, your output should include an extra bin for the remainder of the numbers. For example, if the minimum is 0 and maximum is 7, the categories would be {0}, {>0, <=1}, {>1, <=2}, {>2, <=3}, {>3, <=4}, {>4}. If the minimum is 2 and maximum is 12, the categories

would be {2}, {>2, <=4}, {>4, <=6}, {>6, <=8}, {>8, <=10}, {>10}. This means your output will have either 5 or 6 categories for the number of students. You can assume that the difference between the minimum and maximum number of students will always be greater than or equal to 4.

Note that the number of students for each faculty should be calculated as the total number of students (NOT DISTINCT) in the classes that faculty is teaching. The maximum number of characters in a faculty name will not exceed 12 and the total number of students for each faculty will not have more than two digits, so you can format your output accordingly. Make sure the Xs in your output align with the bins of students corresponding to each faculty.

Hint: Use a cursor and a select statement, with count(sname). Use rpad(lpad(to_char(something),15), 15) to align output.

Sample output:
Faculty name	# Students:	0	>0, <= 2	>2, <=4	>4, <=6	>6, <=8
------------------		-----	-----------	----------	----------	-----------
Alice		X
Bob						X
Joe				X

3.

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 the sample output below, it's 19).

Hint: Use a cursor and a select statement, with count(age), as well as select statement to get min, max & median age.

Sample output:

Age | number of students

17 | 1

18 | 10

19 | 5 <-- median

20 | 2

21 | 8

22 | 3

23 | 0

24 | 1

4.

pro_enroll: Write a procedure to enroll a student in a class. The input parameters will be as follows: sname_in, cname_in. You can assume that student names are unique (i.e. there is a single snum for every sname in the Student table). You can also assume that the given sname_in and cname_in already exist in the database. The result will be a new enrollment record added to the database.

Execute this procedure to insert the enrollments (M.Lee, CS448) and (A.Smith, ENG320) to the database. Do a select * from Enrolled before and after this procedure is run (i.e. include a query that retrieves the whole content of the Enrolled table before and after the statements for executing this procedure to insert data).

Hint: Get the snum from the STUDENT table and insert it into the ENROLLED table.