The purpose of this assignment:To assess your ability to:
• Apply active database concepts such as stored procedures and triggers in a relational database.
This assignment assumes previous access to Oracle using the Oracle SQL Developer application and assignment 6-1 & 7-1 have been completed. I also attached BaseTable.sqlfor you to start on this exercise in case you had issue in previous assignments.
Assignment Details:
• Assume that you have created the following tables in your database at previous labs:
DEPARTMENT(DEPT_ID, DEPT_NAME)
TEAM(TEAM_ID, TEAM_NAME, DEPT_ID)
APPLICATION(APP_ID, APP_NAME, TEAM_ID, DB_ID,SERVER_ID)
SERVER(SERVER_ID,SERVER_NAME,HOST)
DATABASE(DB_ID,DB_NAME,SERVER_ID)
OUTAGE(OUTAGE_ID, APP_ID, OUTAGE_START_TIME, OUTAGE_END_TIME, OUTAGE_CAUSS, DESCRIPTION)
• Assume that the DEPARTMENT and TEAM tables include the following information.
DEPARTMENT table TEAM tabe
1. Run "BaseTable.sql" to drop and recreate the tables, and insert data into the base tables if you do not have the tables and data ready as stated above.
Here is the output file if everything runs correctly:
2. Create, execute and test a stored procedure:
a. Create a stored procedure called Get_team_namethat takes DEPT_ID as a parameter and return the team name from TEAM table. Copy the stored procedure into Word document.
i. Remember to end the stored procedure with a slash on a line by itself (after the END; in the procedure itself).
ii. Test steps:Copy each step SQL script and output into Word document.
1. Run SELECT * from TEAM WHERE DEPT_ID=101;
2. Execute your stored procedure: exec Get_team_name(101);
b. (Option) Create a stored procedure called remove_deptthat takes DEPT_ID as a parameter and delete a department with that dept_id from DEPARTMENT table. Copy the stored procedure into Word document.
i. Remember to end the stored procedure with a slash on a line by itself (after the END; in the procedure itself).
ii. Teststeps: Do the following steps to test your stored procedure. Copy each step SQL script and output into Word document.
1. Run the following two SQL commands:
a. SELECT * from DEPARTMENT WHERE DEPT_ID=101;
b. SELECT * from TEAM WHERE DEPT_ID=101;
2. Execute your procedure to delete DEPT_ID = 101;
3. Run select in step i again.
a. SELECT * from DEPARTMENT WHERE DEPT=101;
b. SELECT * from TEAM WHERE DEPT=101;
4. Answer this question: Did the corresponding records with dept_id = 101 in table TEAM get deleted automatically? Can you explain why?
5. Issue a ROLLBACK command to undo all changes.
6. Rerun select in step i :
a. SELECT * from DEPARTMENT WHERE DEPT_ID=101;
b. SELECT * from TEAM WHERE DEPT_ID=101;
7. Answer the question: Do the records related todept_id = 101 exist in DEPARTMENT and TEAM tables? Explain why?
3. Creating and Testing SQL Triggers. Save the SQL code of the trigger in a separate .sql script file called trig_dept_onUpdate.sql . Then, include each SQL command and its corresponding output in your Word document.
a. Create a trigger named trig_dept_onUpdatethat will automatically setdept_idto the new one in TEAM table when a corresponding department is modified. For example, if dept_id = 103is changed to 204 in DEPARTMENT table, then the dept_id of 103 inteam table will be set to 204 in TEAM table.
b. Test Steps:Copy each step SQL script and output into Word document.
i. Run the following two SQL commands:
1. SELECT * from DEPARTMENT WHERE DEPT_ID=103;
2. SELECT * from TEAM WHERE DEPT_ID=103;
ii. Run trig_dept_onUpdate trigger to update the dept_id to 204 where dept_id = 103.
iii. Run the same following SQL commands:
1. SELECT * from DEPARTMENT WHERE DEPT_ID=103;
2. SELECT * from TEAM WHERE DEPT_ID=103;
3. SELECT * from DEPARTMENT WHERE DEPT_ID=204;
4. SELECT * from TEAM WHERE DEPT_ID=204;
i. Remember, your trigger should only make changes to the particular records where DEPT_ID=103 that is affected by the UPDATE statement. (i.e., you may also want to check the values to the other records in the TEAM table to guarantee they have not changed with this update, etc.)
ii. Remember to end each trigger with a slash on a line by itself (after the END; in the trigger itself).
4. Your assignment should include:
a. stored procedureGet_team_name or remove_deptscript with output
b. triggertrig_dept_onUpdatescript with output
c. Testing steps with output result
Note: Please copy both SQL statement and output result in every step into Word document with step number (for example: Step#1, Step #2, etc). Points will be deducted if any of them is not missed. For each step, the output should follow each SQL statement.
Note: Common Lab Expectations - These apply to all SQL Labs for this class.
1) Always include set echo on;SQL command at the beginning of every SQL script (.sql) file you create or before inputting any SQL commands directly into the worksheet area in SQL Developer (along with the set serveroutput on;command right after it).
Example scripts and the corresponding output
Ch07_Artist_ORA_Script_Example.sql
Ch07_Artist_ORA_Script_Example_Output.lst
2) Include commit; command for INSERT/UPDATE/DELETE to ensure the changes is stored permanently in the database.
a. When writing your SQL query commands and gathering the output from them (to be put into a Word document), you must be sure to include the step #, the SQL command(s), and it corresponding output(s), in that order in the Word document. Refer to Example1.doc. (https://download.franklin.edu/COMP/281/Example1.doc
b. Use the drop table cascade constraints; command BEFORE creating any table in your SQL (.sql) file or in the worksheet area so that this file can be rerun when necessary. You will get table does not exist the first time. Ignore it.
c. Insert will append the data into existing table. If you want to remove the data in the table and then insert the new data, use delete from ; command BEFORE doing any inserts into tables in your .sql file or in the worksheet area.
d. Use the comment feature in SQL Developer in your .sql script files and in your worksheet view to include any comments. These are input into your file by surrounding your comments with a /* ... */. An example is: /* this is a comment */ Also see it used in the .sql file hyperlink in #1 above. These comments should be used to designate which commands and output apply to which steps.
More resources and helpful hints:
Resources on Triggers and procedures:
https://www-db.stanford.edu/~ullman/fcdb/oracle/or-triggers.html
https://tucano.tucanowebdesign.com/oracle/tutorial5.html
https://www.pgrocer.net/Cis50/procedures.html
https://www.sqlteam.com/article/an-introduction-to-triggers-part-i
https://dba.fyicenter.com/faq/sql_server_2/ALTER_TRIGGER_Modifying_Existing_Triggers.html
https://download.oracle.com/docs/cd/B19306_01/server.102/b14220/triggers.htm
Viewing Defined Triggers
To view a list of all defined triggers, use: select trigger_name from user_triggers;
For more details on a particular trigger:
select trigger_type, triggering_event, table_name, referencing_names, trigger_body
from user_triggers
where trigger_name = '';
Viewing Defined Procedures
To view a list of all defined procedures, use:
select object_name, procedure_name from user_procedures;
For more details on a particular procedure:
select * from user_procedures where procedure_name = '';
Displaying Trigger Definition Errors
If you get a message: "Warning: Trigger created with compilation errors." you can see the error messages by typing:
show errors trigger ;
Alternatively, you can type, SHO ERR (short for SHOW ERRORS) to see the most recent compilation error. Note that the reported line numbers where the errors occur may not be accurate.
To see a list of the fields available to you, do a descuser_triggers; and/or a descuser_procedures;