CS561 Midterm Exam
Exams must be submitted as single Word or PPT documents; other formats
will not be accepted.
All questions on this exam refer to the following relational database schema
company(co_name, govt_id, c
...
CS561 Midterm Exam
Exams must be submitted as single Word or PPT documents; other formats
will not be accepted.
All questions on this exam refer to the following relational database schema
company(co_name, govt_id, ceo_ssn, hq_loc)
division(co_id, div_name, subdiv_of, dir_ssn, div_hq)
site(co_id, div_name, loc)
product(prod_id, manuf_co, manuf_div, loc, prod_descr)
person(ssn, name, address)
works_for(ssn, co_id, div_name, salary, emp_id, sup_ssn)
skill(ssn, prod_id, manuf_co)
The foreign key references can be found in the lectures.
1. a) (3 points) Construct an instance of the skill table that contains a violation of the table's key
constraint - or explain why it is impossible to construct such an instance
b) (3 points) Construct an instance of the product table that contains a violation of the table's key
constraint - or explain why it is impossible to construct such an instance
c) (4 points) Construct instances of both the skill and product tables that contain violations of all
the relevant foreign key reference constraints -- or explain why it is impossible to construct such
instances
In all cases in which there is a violation, specify in which row the violation is to be found and
why it’s a violation.
*****************************************************************************
Note: The following questions are intended to measure your learning of SQL operators that are
discussed in the course’s lectures. Do not use operators, such as JOIN and different versions of
JOIN that aren’t discussed in the lectures.
*****************************************************************************
2. (10 points) Write the following query in the pseudo-code used in the lectures:
Find every division (of any company) that has at least two subdivisions
Select div_name From divisions Where count(sub_div) >= (Select *, Count(subdiv_of) as
count1 From divisions Group by div_ name)
3. (10 points) Write the following query in unextended relational algebra:
Find every person who works for at least two different divisions of the same company
[Show More]
