Database Management Systems - 1 CS561 Midterm Exam

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 impos sible 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 . (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, rel op ALL, rel op ANY, rel op SOME) 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, rel op ALL, rel op ANY, rel op SOME)