CSE4/560 Databases and Query Languages Homework 2 Solved

CSE4/560
Databases and Query Languages
Homework 2

Total Marks 100

1.[15] What is a foreign key constraint? Why are such constraints important? What is referential integrity?

2. [25] Given the following relational schema, you have already written the SQL statements required to create the preceding relations, including appropriate versions of all primary and foreign key integrity constraints. They are again added below for your reference.

 

CREATE TABLE Works( eid INTEGER NOT NULL, did INTEGER NOT NULL, pettime INTEGER, PRIMARY KEY(eid, did),

UNIQUE (eid),

FOREIGN KEY (did) REFERENCES Dept

);

CREATE TABLE Dept( did INTEGER NOT NULL, budget REAL, managerid INTEGER,

PRIMARY KEY(did),

FOREIGN KEY (managerid) REFERENCES Emp

);

(i)     Now modify the Dept relation in SQL so that every department is guaranteed to have amanager.

(ii)    Write an SQL statement to add John Doe as an employee with eid = 101, age = 32 andsalary = 15, 000

(iii)  Suppose you have a view SeniorEmp defined as follows:

CREATE VIEW SeniorEmp (sname, sage, salary)

AS SELECT E.ename, E.age, E.salary

FROM Emp E

WHEREE.age 50;

Explain what the system will do to process the following query:

SELECT S.sname

FROM SeniorEmp S

WHERE S.salary 100,00

(iv)  Give an example of a view on Emp that could be automatically updated by updatingEmp.

(v)    Give an example of a view on Emp that would be impossible to update (automatically) and explain why your example presents the update problem that it does.

3.[10] Consider the SQL query whose answer is shown below.

 

(a)  Modify this query so that only the login column is included in the answer.

(b)  If the clause WHERE S.gpa2 is added to the original query, what is the set of tuples in the answer?

4. [60] Consider the following relations: Student(snum: integer, sname: string, major: string, level: string, age: integer)

Class(name: string, meets at: string, room: string, fid: integer)

Enrolled(snum: integer, cname: string)

Faculty(fid: integer, fname: string, deptid: integer)

Write the following queries in SQL. No duplicates should be printed in any of the answers.

(a)   Find the names of all Juniors (level = JR) who are enrolled in a class taught by I;

(b)  Find the age of the oldest student who is either a History major or enrolled in a coursetaught by I

(c)   Find the names of all classes that either meet in room R128 or have five or more studentsenrolled.

(d)  Find the names of all students who are enrolled in two classes that meet at the sametime.

(e)   Find the names of faculty members who teach in every room in which some class istaught.

(f)    Find the names of faculty members for whom the combined enrollment of the coursesthat they teach is less than five.

(g)   For each level, print the level and the average age of students for that level.

(h)  For all levels except JR, print the level and the average age of students for that level.

(i)    For each faculty member that has taught classes only in room R128, print the facultymember’s name and the total number of classes she or he has taught.

(j)    Find the names of students enrolled in the maximum number of classes.

(k)  Find the names of students not enrolled in any class.

(l)    For each age value that appears in Students, find the level value that appears most often.For example, if there are more FR level students aged 18 than SR, JR, or SO students aged 18, you should print the pair (18, FR).