Question

Answer each of the following questions.

The questions are based on the following relational schema:

Emp(*eid: integer¬, ename: string, age: integer, salary: decimal, doj: date)

Works(*eid: integer, *did: integer, no_of_hours: integer)

Dept(*did: integer, dname: string, budget: real, managerid: integer)

a) Give an example of a foreign key constraint that involves the Dept relation.

b) What are the options for enforcing this constraint when a user attempts to delete a Dept tuple?

c) Define the Dept relation in SQL so that every department is guaranteed to have a manager.

d) Define a view in SQL that holds the employees whose salary is greater than the average salary of their department.

e) Alter the Emp table so that it can hold the city in which the employee resides.

Answer 1

a) In relation Dept, the column managerid is a foreign key constraint which references the primary column *eid of relation Emp. Also, the primary key *did of relation Dept is used as a foreign key in Works relation.

b) If a foreign key constraint is defined on relation Dept, then if an user tries to delete a row from Dept, it will throw an error if there are entries present in the Works table with the to be deleted did value.

In this case, we can define the foreign key constraint with ON DELETE CASCADE.

Cascade deletes from parent to child, so when the parent (referenced) record is deleted, any child (referencing) records are also deleted.

This means that if a tuple is deleted from Dept relation, all the referencing records in relation Works are also deleted.

c) Please find the CREATE TABLE command for Dept relation.

CREATE TABLE Dept (

did INT NOT NULL,

dname VARCHAR(50),

budget REAL,

managerid INT NOT NULL,

PRIMARY KEY(did)

);

d) CREATE VIEW EMP_SALARY AS

SELECT * FROM Emp WHERE salary > avg(salary);

e) ALTER TABLE Emp ADD city VARCHAR(50);