Supervisor’s social security number Super_ssn
Department number, Project number Dno, Pno
Manager’s social security number Mgr_ssn
Project name Pname, Pnumber, Plocation
Employee social security number Essn
The following questions must be answered based on the given database schema and instance. Where
explanation is required, each answer should be a SHORT passage of at most several lines.
Question 1 (1 mark): Does the database schema ensure that there must be a job associated with each employee?
Explain your answer.
Question 2 (1 mark): Can an employee work for two departments at the same time? Explain your answer.
Question 3 (1 mark): Can we allocate a project to two departments? If yes, explain your answer; if not, explain your
answer and give a solution to make it possible.
Question 4 (1 mark): A new job is allocated to Alicia in the department located at Houston. The following SQL
statements are intended to record this new change in the database instance. Will they work? Explain your answer.
INSERT INTO Works_on VALUES(Alicia, Houston);
INSERT INTO Works_on VALUES(999887777, 30);
INSERT INTO Works_on VALUES(999887777, 5);
Question 5 (1 mark): Consider the request “find all the employment details (e.g. first name, last name, hire date) of
department managers”. Can this request be completed using the given database schema? Explain your answer.
Question 6 (1 mark): Explain what the result of executing the following SQL statement on the given database
instance will be. Will they work? Explain your answer.
DELETE from Employee WHERE ssn = 333445555;
Question 7 (1 mark): Write an SQL statement to create the Works_on table including all the constraints, assuming
all the tables that Works_on table depends on already exist in the database. Your SQL statement must run in
SQLite.
Question 8 (1 mark): A new department is recently established with the name of “Property Services”. You are asked
to update the given database instance so that it also includes the new department. The new department is located
at “555 Swanston Street Melbourne VIC” and its manager is yet to be assigned. Write the SQL statement to
complete the request. Your SQL statement must run in SQLite.
Question 9 (1 mark): It was found that the data entry operator had incorrectly entered data for John B. Smith and
Ramesh K. Narayan. Their SSN were swapped. The error was discovered, and the data entry operator was
instructed to fix it. They attempted to swap SSN value in each tuple using the following SQL statement.
However, it was not successful. The DBMS returned an error message and two tuples were not updated. Explain
why they didn’t work. Write down the correct SQL UPDATE statements to carry out your proposed solution.
Question 10 (1 mark): A new project has been established, but neither a project number nor a department is
assigned yet. A new record has been entered the Project relation as follows:
Write down all the integrity constraints violated by the above operation. If the operation does not violate any
constraints, indicate as “no violations”.
Sample Solution