12 Feb Theory questions/problems

Q.1 [25 points] Theory questions/problems

a. [ 5 points] Define (i) tuple (ii) domain (iii) schema of a relation (iv) state of a relation, and (v) relational database system.

b. [ 5 points] What is a referential integrity or foreign key constraint? Give an example.

c. [ 5 points] What are three UPDATE operations in a database relation? Does the integrity constraints necessary in UPDATE operation?

d. [ 5 points] List the data types that are allowed for SQL attributes.

e. [ 5 points] Explain the basic form of the SELECT statement. Show the extended version of SELECT statement for ordering Query Results.

Q.2 [25 points] Practical/lab problems

Specify the following queries in SQL on the COMPANY relational database schema shown in Figure 5.5. Show the result of each query if it is applied to the COMPANY database in Figure 5.6.

a. [ 5 points] Retrieve the names of all employees in department 5 who work more than 10 hours per week on the ProductX project.

b. [5 points] List the names of all employees who have a dependent with the same last name as themselves.

c. [ 5 points] Find the names of all employees who are directly supervised by Mr. Wong.

Part2:  Consider that the EMPLOYEE table’s constraint EMPSUPERFK as specified in Figure 6.2 is changed to read as follows: CONSTRAINT EMPSUPERFK

FOREIGN KEY (Super_ssn) REFERENCES EMPLOYEE(Ssn)

ON DELETE CASCADE ON UPDATE CASCADE,

Now, Answer the following questions:

d. [5 points] What happens when the following command is run on the database state shown in Figure 5.6?   DELETE EMPLOYEE WHERE Lname = ‘Borg’

b. [5 points] Is it better to CASCADE or SET NULL in case of EMPSUPERFK constraint

ON DELETE?

FIGURE 5.5