1. What is ON DELETE CASCADE ? 2. Can a child\'s FK field have a different name
ID: 3797855 • Letter: 1
Question
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 ____.
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:
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 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.Explanation / Answer
1. What is ON DELETE CASCADE ?
Ans. Suppose you have a two tables say EMPLOYEE and DEPARTMENT.
EMPLOYEE has the following columns > Emp_id (Primary key), Emp_First_Name, Emp_Last_Name
DEPARTMENT has the following columns > Dept_id(Primary Key), Dept_name, Emp_id (Foreign key which refers to EMPLOYEE table)
EMPLOYEE table has the following data
Emp_id
Emp_First_Name
Emp_Last_Name
001
John
Smith
002
Reuben
Morgan
DEPARTMENT
Dept_id
Dept_name
Emp_id
11
ABC
002
22
PQR
001
Now when you try to delete the employee with Emp_id=001 (DELETE from EMPLOYEE where Emp_id=1) this will not work as Emp_id column is a foreign key in DEPARTMENT table. If you want to delete the record with Emp_id=001 even though it is referred in DEPARTMENT table, then you have to use ON DELETE CASCADE statement. This statement will delete the records from the child table and later from the parent table also.
In this example while creating the table mention the ON DELETE CASCADE statement as shown below.
EMPLOYEE table: CREATE statement (This will be the parent table)
CREATE TABLE EMPLOYEE (Emp_id numeric(10) not null, Emp_First_Name varchar(100), Emp_Last_Name varchar(100), CONSTRAINT pk_emp_id PRIMARY KEY(Emp_id));
DEPARTMENT table: CREATE statement (This will the child table as Emp_id is referred from EMPLOYEE parent table)
CREATE TABLE DEPARTMENT (Dept_id numeric(10) not null, Dept_name varchar(100), Emp_id numeric(10) not null, CONSTRAINT fk_emp_id FOREIGN KEY (Emp_id) REFERENCES EMPLOYEE(Emp_id) ON DELETE CASCADE );
So in the above table if any records is deleted from EMPLOYEE table (parent table) then the respective records are also deleted in the DEPARTMENT table (child table). If ON DELETE CASCADE is not used then you will encounter with FOREIGN KEY CONSTRAINT error.
2. Can a child's FK field have a different name than the parent's PK field
Ans: Yes. The child's FK field can have different name than the parent's PK field. The name does not really matters here. The only advantage of have same name in the both parent and child table is, you can save some time of typing and make use of USING keyword while joining the two table. Below the example.
SELECT e.Emp_id,e.Emp_First_Name,e.Emp_Last_Name,d.Dept_name from EMPLOYEE e JOIN DEPARTMENT d USING (Emp_id);
If you do not use same name then you will have to use below statement (say DEPARTMENT table had employee id field name as EMPLOYEE_ID)
SELECT e.Emp_id,e.Emp_First_Name,e.Emp_Last_Name,d.Dept_name from EMPLOYEE e JOIN DEPARTMENT d where e.Emp_id=d.EMPLOYEE_ID;
3. What is referential integrity? How referential integrity rules are enforced? Provide example.
Ans: Referential integrity is one of the concept in DBMS. It represents relationship between multiple tables. Take the above example of EMPLOYEE and DEPARTMENT tables.
Suppose we have not made Emp_id as a foreign key in DEPARTMENT table. Now employee Reuben is not longer a employee and you want to remove that record from EMPLOYEE and DEPARTMENT tables. If there is no FK constraint then you have to delete the record from EMPLOYEE table and then DEPARTMENT table and other related tables if they exists. This becomes quite a pain. In this you might miss out removing records related to Reuben from some table nad end up with some junk data in the tables.
If we enforce referential integrity then as explained in Question 1, you can use ON DELETE CASCADE for FK fields while creating the tables. This would reduce the man power and DELETE all the records related to Reuben in the child table also.
4. What are the differences between stored procedures and functions? Provide examples.
Ans: Objects which are pre-compiled (which are compiled for first time) and the compiled format is stored are called as Stored Procedures. These are gets executes whenever stored procedures are called.
Objects which are compiled and executed every time when it is called are known as Functions.
Differences:
1. Stored Procedures can return zero or n values (it is optional) whereas Functions must nad should return a values.
2. Stored Procedures allows SELECT and any DML (like INSERT/UPDATE/DELETE) statement in it whereas Functions allows SELECT statements only.
3. Try-catch block can be used in Stored Procedures to handle exception whereas try-catch block cannot be used in Functions.
4. Stored Procedures cannot be embedded in the SELECT statements but Functions can be used in SELECT statements.
5. Procedures cannot be called from functions but functions can be called from Stored Procedures.
5. Set in ONE statement SHIP_DATE to today's date for all customer 789443345435 orders that have not been shipped.
Ans: UPDATE ORDERS SET SHIP_DATE=systimestamp where CUSTOMER_ID=789443345435;
(OR)
UPDATE ORDERS SET SHIP_DATE = GETDATE() where CUSTOMER_ID=789443345435;
6. What type of constraints do you know? What is the purpose of each type?
Ans: There are many kinds of constraints. Below are some of them.
1. UNIQUE: Each row of the column have unique records. But NULL records are allowed.
2. DEFAULT: This specifies a default value for a column in the table.
3. NOT NULL: This specifies that the column cannot hold NULL values
4. PRIMARY KEY: Combination of NOT NULL and UNIQUE constraint. Each row should be unique and NULL records are not allowed. This helps to find a particular records easily and also quickly.
5. FOREIGN KEY: It ensures the referential integrity concept. It ensures related data in one table matches with records in another table.
6. CHECK: It makes sure that the value in the field meets a specific condition.
7. What type of data models do you know? Describe each type.
Ans: There are three types of data models
1. Conceptual
2. Logical
3. Physical
8. Consider the following PL/SQL block. What will be the value of y in the inserted row?
Ans: The value of 'y' will be 111.
9. The entity integrity rule requires that
Ans: A. all primary keys entries are unique
10. The referential integrity rule requires that
Ans: C. every non-null foreign key value reference an existing primary key value
Emp_id
Emp_First_Name
Emp_Last_Name
001
John
Smith
002
Reuben
Morgan
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.