Question

Any help appreciated!

Question 1 (5 points). Answer each of the following questions briefly. The questions are based on the following relational schema:

Emp( eid: integer, ename: string, age: integer, sala1l1: real)

Works( eid: integer, did: integer, pet_time: integer)

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

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

2. Write the SQL statements required to create the preceding relations, including appropriate

versions of all primary and foreign key integrity constraints.

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

4. Write an SQL statement to add John Doe as an employee with eid = 101, age = 32 and salary = 15,000.

5. Write an SQL statement to give every employee a 10 percent raise.

6. Write an SQL statement to delete the Toy department.

Question 2 (5 points). Consider the following schema:

Suppliers( sid: integer, sname: string, address: string)

Parts(pid: integer, pname: string, color: string)

Catalog( sid: integer, pid: integer, cost: real)

The Catalog relation lists the prices charged for parts by Suppliers. Write the following

queries in SQL:

1. Find the pnames of parts for which there is some supplier.

2. Find the snames of suppliers who supply every part.

3. Find the snames of suppliers who supply every red part.

4. Find the pnames of parts supplied by Acme Widget Suppliers and no one else.

5. Find the sids of suppliers who supply only red parts.

6. Find the sids of suppliers who supply a red part and a green part.

7. Find the sids of suppliers who supply a red part or a green part.

Question 3 (5 points). The following relations keep track of airline flight information:

Write the following queries in SQL:

For each pilot who is certified for more than three aircraft, find the eid and the maximum cruisingrange of the aircraft for which she or he is certified.

Find the names of pilots whose salary is less than the price of the cheapest route from Los Angeles to Honolulu.

Find the aids of all aircraft that can be used on routes from Los Angeles to Chicago.

Print the names of employees who are certified only on aircrafts with cruising range longer than 1000 miles.

Answer 1

Question 1

1. These foreign key constaints are necessary:
   Works : FOREIGN KEY did REFERENCES Dept(did)
   Dept: FOREIGN KEY managerid REFERENCES Emp(eid)
2. CREATE TABLE Emp ( eid INT, ename VARCHAR, age INT, salary REAL, PRIMARY KEY (eid) );
   CREATE TABLE Dept ( did INT PRIMARY KEY, dname VARCHAR, budget REAL, managerid INT, FOREIGN KEY (managerid) REFERENCES Emp(eid) );
   CREATE TABLE Works ( eid INT, did INT, pet_time INT, PRIMARY KEY (eid, did), FOREIGN KEY (eid) REFERENCES Emp(eid) ON DELETE CASCADE, FOREIGN KEY (did) REFERENCES Dept(did) ON DELETE CASCADE);
3. managerId INT NOT NULL
4. INSERT INTO Emp VALUES(101,'John Doe', 32, 15000.0);