Create the necessary procedures & functions to Update, Insert and Delete from th

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) ;

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.