I need help with this database Assignment:

Procedures

1. pro_department_report: Generate a report that lists, for each department, the students in that department. For each department, you should first print the department name on a line followed by the number of students in that department on the next line and a numbered list of student names in that department. The output should be modeled as follows: Sort by the department name (ascending and sort by student name (ascending) for each department.
2. pro_student_stats: 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.
3. 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 X's in your output align with the bins of students corresponding to each faculty.

1. 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.
2. pro_enroll: Write 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 DBO.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 select * from DBO.Enrolled before and after this procedure is run (i.e. include a query that retrieves the whole content of the DBO.Enrolled table before and after the statements for executing this procedure to insert data).

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

Anonymous Block

Use the following anonymous blocks to test your procedures and trigger:

Exec dbo.pro_department_report;

Exec dbo.PRO_ENROLL ('S.Jack', 'ENG320');

Exec dbo.pro_faculty_stats;

Exec dbo.pro_histogram;

Exec dbo.pro_student_stats;

Trigger

The trigger after insert in the pro_enroll procedure (insert into DBO.ENROLLED) and will print out SNAME, FNAME, meets_at, room FROM dbo.STUDENT S, dbo.ENROLLED, dbo.FACULTY, dbo.CLASS tables.

Note: The example in the pdf document examples of solution does not represent the correct solution for the trigger.

project.sql:

create database project;

go

use [project];

go

CREATE TABLE dbo.STUDENT

(snum int,

sname nvarchar(12),

MAJOR nvarchar(12),

DEPT_ID int,

slevel nvarchar(12),

AGE int,

CONSTRAINT STUDENT_SNUM_pk PRIMARY KEY (SNUM));

--

CREATE TABLE dbo.CLASS

(CNAME nvarchar(12),

MEETS_AT time,

ROOM nvarchar(10),

FID int,

CONSTRAINT CLASS_CNAME_pk PRIMARY KEY (CNAME));

--

CREATE TABLE dbo.ENROLLED

(SNUM int,

CNAME nvarchar(12));

--

CREATE TABLE dbo.FACULTY

(FID int,

FNAME nvarchar(40),

DEPT_ID int,

CONSTRAINT FACULTY_FID_pk PRIMARY KEY (FID));

--

CREATE TABLE dbo.DEPARTMENT

(DEPTid int,

DNAME nvarchar(100),

LOCATION nvarchar(100),

CONSTRAINT DEPARTMENT_DEPTID_pk PRIMARY KEY (DEPTID));

insert into dbo.STUDENT values (0418,'S.Jack','Math',2,'SO',17);

insert into dbo.STUDENT values (0671,'A.Smith','English',2,'FR',20);

insert into dbo.STUDENT values (1234,'T.Banks','ME',3,'SR',19);

insert into dbo.STUDENT values (3726,'M.Lee','Physics',4,'SO',21);

insert into dbo.STUDENT values (4829,'J.Bale','Chemistry',5,'JR',22);

insert into dbo.STUDENT values (5765,'L.Lim','CS',1,'SR',19);

insert into dbo.STUDENT values (0019,'D.Sharon','History', 4,'FR',20);

insert into dbo.STUDENT values (7357,'G.Johnson','Math', 4,'JR',19);

insert into dbo.STUDENT values (8016,'E.Cho','History',2,'JR',19);

insert into dbo.DEPARTMENT values (1,'Computer Sciences','West Lafayette');

insert into dbo.DEPARTMENT values (2,'Management','West Lafayette');

insert into dbo.DEPARTMENT values (3,'Medical Education','Purdue Calumet');

insert into dbo.DEPARTMENT values (4,'Education','Purdue North Central');

insert into dbo.DEPARTMENT values (5,'Pharmacal Sciences','Indianapolis');

insert into dbo.FACULTY values (101,'S.Layton',4);

insert into dbo.FACULTY values (102,'B.Jungles',1);

insert into dbo.FACULTY values (103,'N.Guzaldo',3);

insert into dbo.FACULTY values (104,'S.Boling',2);

insert into dbo.FACULTY values (105,'G.Mason',1);

insert into dbo.FACULTY values (106,'S.Zwink',2);

insert into dbo.FACULTY values (107,'Y.Walton',1);

insert into dbo.FACULTY values (108,'I.Teach',2);

insert into dbo.FACULTY values (109,'C.Jason',3);

insert into dbo.CLASS values ('ENG400',cast('08:30' as time),'U003',107);

insert into dbo.CLASS values ('ENG320', cast('09:30' as time),'R128',107);

insert into dbo.CLASS values ('COM100', cast('11:30' as time),'L108',107);

insert into dbo.CLASS values ('ME308', cast('10:30' as time),'R128',109);

insert into dbo.CLASS values ('CS448', cast('09:30' as time),'U003',106);

insert into dbo.CLASS values ('HIS210', cast('01:30' as time),'L108',104);

insert into dbo.CLASS values ('MATH275', cast('02:30' as time),'L108',108);

insert into dbo.CLASS values ('STAT110', cast('04:30' as time),'R128',108);

insert into dbo.CLASS values ('PHYS100', cast('04:30' as time),'U003',105);

insert into dbo.ENROLLED values (1234,'ENG400');

insert into dbo.ENROLLED values (8016,'ENG400');

insert into dbo.ENROLLED values (0418,'CS448');

insert into dbo.ENROLLED values (1234,'COM100');

insert into dbo.ENROLLED values (0671,'ENG400');

insert into dbo.ENROLLED values (1234,'HIS210');

insert into dbo.ENROLLED values (3726,'MATH275');

insert into dbo.ENROLLED values (5765,'PHYS100');