1. What is ON DELETE CASCADE ?

2. Can a child's FK field have a different name than the parent's PK field? Provide examples.

3. What is referential integrity? How referential integrity rules are enforced? Provide example.

4. What are the differences between stored procedures and functions? Provide examples.

5.

Suppose you have a table ORDERS.

ORDER_ID CUSTOMER_ID ORDER_DATE SHIP_DATE

-----------------------------------------------------------------------------

1233 789443345435 01-feb-2009 10-feb-2009

1234 789443345435 02-feb-2009 null

1235 543579392949 02-feb-2009 null

1236 237458685686 02-feb-2009 09-feb-2009

1237 383485868586 02-feb-2009 null

1238 789443345435 02-feb-2009 null

Set in ONE statement SHIP_DATE to today's date for all customer 789443345435 orders that have not been shipped.

6. What type of constraints do you know? What is the purpose of each type?

7. What type of data models do you know? Describe each type.

8.

Consider the following PL/SQL block. What will be the value of y in the inserted row?

9.

The entity integrity rule requires that ____. A. all primary keys entries are unique

B. a part of the key may be null C. foreign key values do not reference primary key values D. duplicate object values are allowed

10. The referential integrity rule requires that ____.

Question 10 options:

	every null foreign key value must reference an existing primary key value
	an attribute have a corresponding value
	every non-null foreign key value reference an existing primary key value
	you delete a row in one table whose primary key does not have a matching foreign key value in another table

11. Create table Employee with empID (PK), empFname, empLname, emp-salary.

Insert at least 4 rows in the Employee table

Create a trigger that will fire automatically in response to any change in salary before delete or insert or update on employee table.

Check what happens when you try any of the events (insert-update-delete)

Create at least two test cases for each event.

What to submit:

1. Write SQL code to create Employee table.
2. Write SQL code to insert values in tables.
3. Write Trigger code
4. Submit Test cases and trigger execution results with screen shots.

CREATE TABLE test. (x INT, Y INT) create or replace TRIGGER test trigger BEFORE INSERT ON test FOR EACH ROW BEGIN IF new. Y IS NULL THEH new. Y 111; END IF: THEN new. Y mew. Y END IF: EHD: INSERT INTO test. (x) VALUES (10) SELECT FROM test