Homework SourseCreate a stored procedure that can be used to add a student
Create a stored procedure that can be used to add a student to the school and a section of a course. 1. If the student already exists, then just add him to the section (do not update information like address). 2. The procedure will require the following arguments (see table definition for types): A. Salutation B. First Name C. Last Name D. Street Address (including City) E. ZIP Code F. Phone Number G. Employer Name (if any) H. Course Number I. Section Number 3. The program should error if any constraints are violated (no last name, bad zip, bad course, bad section) 4. It should rollback changes if these or any other errors occur. 5. The dates in the STUDENT and ENROLLMENT tables should be set to the day the procedure is run. 6. Don\'t forget that each table has 4 extra required fields ( CREATED_BY, CREATED_DATE, MODIFIED_BY, and MODIFIED_DATE). The current user should be in both \'BY\'s and the day the procedure is run in both \'DATE\'s. 7. To test the procedure insert yourself into course 120, section 5. 8. If the insert fails (usually due to the ZIP code not being in the ZIPCODE table, then find a legal ZIP code in that table and use it instead of your own or insert you own ZIP into the table and rerun the procedure. 9. The following SQL can be used to verify that you have successfully placed yourself in the course: SELECT S.LAST_NAME [Last Name], s.FIRST_NAME [First Name], e.ENROLL_DATE [Enrolled On] FROM ENROLLMENT e INNER JOIN SECTION c ON c.SECTION_ID = e.SECTION_ID INNER JOIN STUDENT s ON s.STUDENT_ID = e.STUDENT_ID WHERE c.COURSE_NO =120 AND c.SECTION_NO = 5 structure of the student and section tables and respectve conraints and primary keys
[STUDENT]
STUDENT_ID(PK) INT NOT NULL
SALUTATION VARCHAR(5) NULL
FIRST_NAME VARCHAR(25) NULL
LAST_NAME VARCHAR(25) NOT NULL
STREET_ADDRESS VARCHAR(50) NULL
ZIP(FK) VARCHAR(5) NOT NULL
PHONE VARCHAR(15) NULL
EMPLOYER VARCHAR(60) NULL
REGISTRATION_DATE DATE NOT NULL
[SECTION]
SECTION_ID(PK) INT NOT NULL
COURSE_NO(FK) INT NOT NULL
SECTION_NO TINYINT NOT NULL
START_DATE_TIME DATE NOT NULL
LOCATION VARCHAR(30) NULL
INSTRUCTOR_ID(FK) INT NOT NULL
CAPACITY TINYINT NULL
[ENROLLMENT]
STUDENT_ID(PK)(FK) INT NOT NULL
SECTION_ID(PK)(FK) INT NOT NULL
ENROLL_DATE DATE NOT NULL
FINAL_GRADE TINYINT NULL
[ZIPCODE]
ZIP(PK) CHAR(5) NOT NULL
CITY VARCHAR(25) NULL
STATE VARCHAR(2) NULL
[COURSE]
COURSE_NO(PK) INT NOT NULL
DESCRIPTION VARCHAR(50) NOT NULL
COST MONEY NULL
PRERQUISITE(FK) INT NULL
Solution
CREATE TABLE ZIPCODE (
ZIP VARCHAR(5) NOT NULL PRIMARY KEY,
CITY VARCHAR(25),
STATE VARCHAR(2),
CREATED_BY VARCHAR(50) NOT NULL,
CREATED_DATE DATE NOT NULL,
MODIFIED_BY VARCHAR(50),
MODIFIED_DATE DATE
);
CREATE TABLE STUDENT (
STUDENT_ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
SALUTATION VARCHAR(5),
FIRST_NAME VARCHAR(25),
LAST_NAME VARCHAR(25) NOT NULL,
STREET_ADDRESS VARCHAR(50),
ZIP VARCHAR(5) NOT NULL,
PHONE VARCHAR(15),
EMPLOYER VARCHAR(60),
REGISTRATION_DATE DATE NOT NULL,
CREATED_BY VARCHAR(50) NOT NULL,
CREATED_DATE DATE NOT NULL,
MODIFIED_BY VARCHAR(50),
MODIFIED_DATE DATE,
FOREIGN KEY (ZIP) REFERENCES ZIPCODE(ZIP)
);
CREATE TABLE COURSE (
COURSE_NO INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
DESCRIPTION VARCHAR(50) NOT NULL,
COST INT,
PRERQUISITE INT,
CREATED_BY VARCHAR(50) NOT NULL,
CREATED_DATE DATE NOT NULL,
MODIFIED_BY VARCHAR(50),
MODIFIED_DATE DATE
);
CREATE TABLE SECTION (
SECTION_ID INT NOT NULL PRIMARY KEY,
COURSE_NO INT NOT NULL,
SECTION_NO TINYINT NOT NULL,
START_DATE_TIME DATE NOT NULL,
LOCATION VARCHAR(30),
INSTRUCTOR_ID INT NOT NULL,
CAPACITY TINYINT,
CREATED_BY VARCHAR(50) NOT NULL,
CREATED_DATE DATE NOT NULL,
MODIFIED_BY VARCHAR(50),
MODIFIED_DATE DATE,
FOREIGN KEY (COURSE_NO) REFERENCES COURSE(COURSE_NO)
);
CREATE TABLE ENROLLMENT (
STUDENT_ID INT NOT NULL,
SECTION_ID INT NOT NULL,
ENROLL_DATE DATE NOT NULL,
FINAL_GRADE TINYINT ,
CREATED_BY VARCHAR(50) NOT NULL,
CREATED_DATE DATE NOT NULL,
MODIFIED_BY VARCHAR(50),
MODIFIED_DATE DATE,
PRIMARY KEY (STUDENT_ID, SECTION_ID),
FOREIGN KEY (SECTION_ID) REFERENCES SECTION(SECTION_ID)
);
insert into ZIPCODE values
(\"INDEL\", \"DELHI\", \"UP\", \"YOGESH\", current_date, null, null),
(\"INPUN\", \"PUNJAB\", \"UP\", \"YOGESH\", current_date, null, null);
insert into STUDENT values
(1,\"MR\", \"ABCD\",\"EFGH\", \"Road side street\", \"INDEL\", null, null,current_date,\"YOGESH\", current_date, null, null),
(2,\"MR\", \"XYZ\", \"PQR\", \"Another strreet\", \"INPUN\", null, null,current_date,\"YOGESH\", current_date, null, null);
insert into COURSE values
(1,\"MATHS\", null,null, \"YOGESH\", current_date, null, null),
(2,\"SCIENCE\", null,null, \"YOGESH\", current_date, null, null);
insert into SECTION values
(1,1,1,current_date,\"DELHI_LOC\", 3, null,\"YOGESH\", current_date, null, null),
(2,2,2,current_date,\"DELHI_LOC\", 4,null, \"YOGESH\", current_date, null, null);
-- Procedure to insert Student.
-- If successful, returns 1
-- else 0
CREATE PROCEDURE insertStudent(
IN SALUTATION VARCHAR(5),
IN FIRST_NAME VARCHAR(25),
IN LAST_NAME VARCHAR(25),
IN STREET_ADDRESS VARCHAR(50),
IN ZIP VARCHAR(5),
IN PHONE VARCHAR(15),
IN EMPLOYER VARCHAR(60),
IN COURSE_NO INT,
IN SECTION_NO INT,
OUT STATUS INT)
BEGIN
DECLARE studentid INT DEFAULT 0;
DECLARE sectionid INT DEFAULT 0;
Select STUDENT_ID into studentid From STUDENT
Where LOWER(STUDENT.FIRST_NAME) = LOWER(`FIRST_NAME`)
and LOWER(STUDENT.LAST_NAME) = LOWER(`LAST_NAME`);
Select MAX(SECTION_ID) into sectionid From SECTION
Where SECTION_NO = `SECTION_NO`
and COURSE_NO= `COURSE_NO`;
IF SECTION_NO IS NULL THEN
Set STATUS = 0;
END IF;
IF COURSE_NO IS NULL THEN
Set STATUS = 0;
END IF;
-- If Section id is not present, give error
if sectionid <> 0 THEN
-- If Student id is not present, insert and retrieve the student_id
if studentid = 0 THEN
INSERT into STUDENT values(null,`SALUTATION`, `FIRST_NAME`,`LAST_NAME`, `STREET_ADDRESS`, `ZIP`,`PHONE`, `EMPLOYER`, current_date, current_User, current_date, null, null);
SELECT LAST_INSERT_ID() into studentid;
end if;
-- insert into enrollment table using sectionid and studentid
INSERT into ENROLLMENT values
(studentid, sectionid, current_date, null, current_User, current_date, null, null);
Set STATUS = 1;
else
Set STATUS = 0;
end if;
END//
