Create the necessary procedures & functions to Update, Insert and Delete from th
ID: 3591953 • Letter: C
Question
Create the necessary procedures & functions to Update, Insert and Delete from the four tables in your schema (COURSE, SECTION, ENROLLMENT and STUDENT).
COURSE
SECTION
ENROLLMENT
STUDENT
UPDATE
PRC_COURSE_UPDATE
PRC_SECTION_UPDATE
PRC_ENROLLMENT_UPDATE
PRC_STUDENT_UPDATE
INSERT
FN_COURSE_INSERT
FN_SECTION_INSERT
PRC_ENROLLMENT_INSERT
FN_STUDENT_INSERT
DELETE
PRC_COURSE_DELETE
PRC_SECTION_DELETE
PRC_ENROLLMENT_DELETE
PRC_STUDENT_DELETE
If you’re creating a function, the function should return the created Primary Key value to the caller.
For the ‘Delete’ procedures, make sure you’re deleting child records first! Example- if you’re deleting ‘COURSE’, make sure to delete corresponding ‘Section’ records first.
Save the objects in your schema, and extract the DDL using SQL Developer and attach your script to Sakai. Extract o nly the procedure/function code.
COURSE
SECTION
ENROLLMENT
STUDENT
UPDATE
PRC_COURSE_UPDATE
PRC_SECTION_UPDATE
PRC_ENROLLMENT_UPDATE
PRC_STUDENT_UPDATE
INSERT
FN_COURSE_INSERT
FN_SECTION_INSERT
PRC_ENROLLMENT_INSERT
FN_STUDENT_INSERT
DELETE
PRC_COURSE_DELETE
PRC_SECTION_DELETE
PRC_ENROLLMENT_DELETE
PRC_STUDENT_DELETE
Explanation / Answer
----------------------------------------------------- -- Table student -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS student ( id INT NOT NULL , name VARCHAR(100) NOT NULL , registrationId VARCHAR(45) NOT NULL , date_birth DATE NULL , date_registrationId DATE NOT NULL , PRIMARY KEY ( id ) , UNIQUE INDEX registrationId_UNIQUE ( registrationId ASC) ) ENGINE = InnoDB; -- ----------------------------------------------------- -- Table test -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS test ( id INT NOT NULL , testDate DATE NOT NULL , description VARCHAR(255) NOT NULL , PRIMARY KEY ( id ) ) ENGINE = InnoDB; -- ----------------------------------------------------- -- Table score -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS score ( student_id INT NOT NULL , test_id INT NOT NULL , score DECIMAL(15,2) NULL , PRIMARY KEY ( student_id , test_id ) , INDEX fk_student_has_test_test1 ( test_id ASC) , INDEX fk_student_has_test_student1 ( student_id ASC) , CONSTRAINT fk_student_has_test_student1 FOREIGN KEY ( student_id ) REFERENCES student ( id ) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT fk_student_has_test_test1 FOREIGN KEY ( test_id ) REFERENCES test ( id ) ON DELETE NO ACTION ON UPDATE NO ACTION) ;