Database Management Systems - 1
CS561 Midterm Exam
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
...
Database Management Systems - 1
CS561 Midterm Exam
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
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
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
4. (10 points) Write the following query in extended relational algebra:
Find the number of products manufactured by the company with govt_id 123-45-
6789
5. (10 points) Write the following query in SQL without any embedded subqueries:
Find every person who works for a company that has at least two divisions each
of which has at least two sites.
6. Consider the following query:
Find every company that employs at least one person who has no skills at any
of the products that the company manufactures.
a) (10 points) Write the query in SQL using an embedded query, but without
using any of the embedding operators (IN, NOT IN, EXISTS, NOT
EXISTS).
b) (10 points) Write the query in SQL with an embedded query that uses
either IN or NOT IN, whichever is appropriate. (You may not use EXISTS
or NOT EXISTS).
c) (10 points) Write the query in SQL using an embedded query that uses
either EXISTS or NOT EXISTS, whichever is appropriate. (You may not
use IN or NOT IN).
7. (10 points) Write the following query in SQL using both a GROUP BY and
a HAVING clause. (You may not use any of the following operators: IN,
NOT IN, EXISTS, NOT EXISTS, ALL, ANY, )
8. (10 points) Write the query of Question 7 in SQL using a GROUP BY but no
HAVING clause. (You may not use any of the following operators: IN, NOT IN,
EXISTS, NOT EXISTS, ALL, ANY, )
[Show More]
