2023 Problem I 30 points 1 Prove that any relation schema with two attributes is in | Assignment Collections

Problem I: (30 points) 1- Prove that any relation schema with two attributes is in BCNF. (5 points) 2- R(A, B, C) is a relation in BCNF. A is one of the keys of R. R may have other keys. Prove that R is in 4NF. (10 points) (Hint: Assume that R is not in 4NF. What could the MDs that violate 4NF be? For each such MD, use what you know about the relation to show that this MD cannot violate 4NF.) 3- Consider the universal relation R = {A, B, C, D, E, F, G, H, I} and the set of functional dependencies F = { {A, B} -> {C}, {A} -> {D, E}, {B} -> {F}, {F} ->{G, H}, {D} -> {I, J} }. What is the key for R? Decompose R into 2NF, then 3NF relations. (15 points) Problem II: (30 points) In this problem, we will consider a movie database with three tables: MovieDirector(title, director, year) MovieCast(title, actor, salary) MovieReview(title, reviewer, score) All attributes are strings except year, salary, and score, which are integers. A- Write the following queries in SQL (2 points each). To save you some writing, you may use the abbreviated schema MD(T,D,Y), MC(T,A,S), and MR(T,R,S). Write your queries so that the final answers will never include duplicates, but add the keyword DISTINCT only in cases where duplicates would otherwise be produced. (a) Using subqueries in WHERE, find the directors who have also acted. Sort their names alphabetically. (b) Without using subqueries in WHERE, find the actors who have never directed. (c) Find the titles of all movies in the 1960’s starring both Dustin Hoffman and Anne Bancroft. (d) Without using EXCEPT, find the actors who starred in every movie directed by Kurosawa. (e) Using SQL aggregates, find the titles of the movies with at least two reviews. (f) Same as (e), but use subqueries in WHERE instead of aggregates. (g) Same as (e), but do not use subqueries in WHERE or aggregates. (h) Using SQL aggregates, find the titles of the movies with exactly one review. (i)…