Create a stored procedure that can be used to add a student to the school and a
ID: 3786828 • Letter: C
Question
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
[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
Explanation / Answer
// the below statements are used for creating a simple insert stored procedure. You can run it through an explicit call from a host language program. .... similarly create other tables....
DROP PROCEDURE IF EXISTS `students`
GO
CREATE PROCEDURE students
(
IN p_student_id INT(11) ,
IN p_salutation VARCHAR(5) ,
IN p_lastname VARCHAR(25) ,
IN p_firstname VARCHAR(25) ,
IN p_streetaddress VARCHAR(50) ,
IN p_zip VARCHAR(5) ,
IN p_phone VARCHAR(15) ,
IN p_employer VARCHAR(60) ,
IN p_registrationdate DATE ,
IN_createdby VARCHAR(25) ,
IN_createddate DATE ,
IN_modifyby VARCHAR(25) ,
IN_modifydate DATE
)
BEGIN
INSERT INTO students
(
student_id ,
salutation ,
lastname ,
firstname ,
streetaddress ,
zip ,
phone ,
employer ,
registrationdate ,
createdby ,
createddate ,
modifyby ,
modifydate
)
VALUES
(
p_student_id ,
p_salutation ,
p_lastname ,
p_firstname ,
p_streetaddress ,
p_zip ,
p_phone ,
p_employer ,
p_registrationdate
p_createdby ,
p_createddate ,
p_modifyby ,
p_modifydate
) ;
END
GO
// the below statements are used to create the table ..using mysql command.. similarly create other tables....
CREATE TABLE IF NOT EXISTS student
(
STUDENT_ID 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
PRIMARY KEY (STUDENT_ID)
) ENGINE = MYISAM
GO
//query to check if student data exists if so add to section
CREATE DEFINER=`root`@`127.0.0.1`
PROCEDURE `GetUserName`(student STUDENT_ID INT,SECTION SECTION_ID INT)
BEGIN
DECLARE SECTION_ID INT;
SELECT STUDENT_ID SECTION SECTION_ID
FROM STUDENT,SECTION
WHERE STUDENT_ID = STUDENT_ID;
IF STUDENT_ID EXISTS
THEN
INSERT INTO SECTION (SECTION_ID, COURSE_NO, SECTION_NO, START_DATE_TIME, LOCATION, INSTRUCTOR_ID, CAPACITY);
END IF;
END
//query to select the required data
mysql> CREATE PROCEDURE my_proc_OUT (OUT highest_salary INT)
-> BEGIN
-> 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
-> END$$
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.