Prerequisites in Computing Science
Create a stored procedure MY_TEST to demonstrate the difference between CHAR and VARCHAR. The input pparameter is p_string. Use the body of your procedure. L_VARIABLE VARCHAR2 (10); L_FIXED CHAR (10);
BEGIN IF L_VARIABLE = L_FIXED THEN DBMS_OUTPUT.PUT_LINE (‘EQUAL’); ELSE DBMS_OUTPUT.PUT_LINE (‘NOT EQUAL’); END IF; END;
Test cases 1. exec my_test(NULL); 2. exec my_test(‘data’); 3. exec my_test(‘relational’); 4. exec my_test(‘relational database’);
Please provide in pdf: Your procedure and test results with explanations. 2. (0.5%) Map the following ERD into a relational model (one relation), map the relation into a physical
model and implement the model in Oracle 12c.
Write an SQL script to (Include your script in the pdf file with the results) • Create the EMPLOYEE table.
Add the data to reflect the following situation: TOM (address SCIENCE) supervises KEVIN, MILA, and MRIDULA since 2017-01-01. KEVIN, MILA, and MRIDULA have address CS. KING (address TRU) supervises TOM since 2000-01-01. Nobody supervises the KING
• SQL statement to list the employees (all data) and their immediate supervisors (supervisor id and supervisor name).
Part 2 (1%) During our last class, Eric Youd had a presentation on how our (grades and registration) data are stored in the real database. There is a plan to implement the automated checking for the prerequisites for course at TRU. Please help Erick with this task . Create an ERD for the courses and their prerequisites. Map the CDM into PDM and create an SQL script to create the tables. Add the data. Create a stored procedure to list the prerequisites (immediate prerequisites) for a given course (input parameter: course number). To simplify the task we will use only numbers (there are unique in this case)
Course Prerequisites in Computing Science (from Fall 2015)
1130 MATH 1700
1230
2230 2920 2130 2210 2680
MATH 1650
2160
3520 3450
4910 (Fourth Year Standing)
3410 3270
3260
3710 3610 3540
LAB SUBMISSION Create a pdf file and upload to the Blackboard.
