Use the next two tables:

CREATE TABLE patient(

patientID INTEGER PRIMARY KEY,

fname CHAR(25),

lname CHAR(25),

gender CHAR(1),

phone INTEGER

);

CREATE TABLE account(

accountID INTEGER PRIMARY KEY,

insuranceCo CHAR(25),

balance FLOAT

);

CREATE TABLE bill(

patientID INTEGER,

accountID INTEGER,

value FLOAT,

visitDate DATE,

PRIMARY KEY(patientID, accountID, visitDate)

);

7. Write a foreign key constraint to be added to the BILL tables creation statement, that uses accountID to relate

the BILL table to ACCOUNT table.

8. Write a foreign key constraint to be added to the BILL tables creation statement, such that all bills associated with a patient are deleted when that patient is deleted.

9. Consider the following schema: Emps (empID, SSN, name, managerID). This relation gives a set of employees ID

(assumed unique), their social security number (unique), the name of the employee (not necessarily unique) and

the ID of the manager of the employee. Assume every employee has a unique manager, and that there are no

duplicate tuples in this relation.

We wish to assert that no one can manage more than 10 employees. Here are two possible SQL assertions:

I. CREATE ASSERTION I CHECK(NOT EXISTS(

SELECT mgrID

FROM Emps

GROUP BY mgrID

HAVING COUNT(*) > 10

));

II. CREATE ASSERTION II CHECK(10 >=

(SELECT MAX(cnt) FROM (SELECT mgrID, COUNT(*) AS cnt FROM Emps GROUP BY mgrID));

Which, if any, of the two queries above will correctly (in SQL) get the desired set of employee IDs?

(a) Both I and II

(b) I only

(c) II only

(d) Neither I nor II