B. Consider the following two relations: Relation r Relations R Р 10 В a b 5...
Question 1 (5 marks) Consider two relations called Item and Orderltem. Imagine that relation Item has 160,000 tuples and Orderltem has 200,000 tuples. Both relations store 100 tuples per a page. Consider the following SQL statement SELECT * FROM Item INNER JOIN OrderItem ON Item.ItemID-OrderItem. ItemID; We wish to evaluate an equijoin between Orderltem and Item, with an equality condition Item.ltemID Orderltem.ItemID. There are 802 buffer pages available in memory for this operation. Both relations are stored as (unsorted) heap...
10. [12 Points) Properties of relations Consider the relation R defined on R by «Ry x2 - y2 = x - y (a) Show that R is reflexive. (b) Show that R is symmetric. (c) Show that R is transitive. (d) You have thus verified that R is an equivalence relation. What is the equivalence class of 3? (e) More generally, what is the equivalence class of an element x? Use the listing method. (f) Instead of proving the three...
Consider relation R = (A,B,C,D,E) for the following: 2. Is R in 2NF with the following functional dependencies? If not, normalize it. [5 points] A→BC -AD → E в с 3. Are the relations from the answer of question 2 in 3NF? If not normalize it. 5 points]
Consider the natural join of the relation R(A,B) and S(A,C) on attribute A. Neither relations have any indexes built on them. Assume that R and S have 80,000 and 20,000 blocks, respectively. The cost of a join is the number of its block I/Os accesses. If the algorithms need to sort the relations, they must use two-pass multi-way merge sort. QUESTION: Assume that there are 10 blocks available in the main memory. What is the fastest join algorithm for computing...
Consider the schema R = (A, B, C, D, E) and let the following set F of functional dependencies holdforR: F = {A -> BC, CD -> E, C -> A, B -> D,} 1) Prove or disprove ADE is in the closure of F. A proof can be made by using inference rules IR1 through IR3. A disproof should be done by showing a relational instance (counter example) that refutes the rule. 2) What are the candidate keys of...
Consider the relation R(A,B,C,D) with FDs A -> B, C -> D, AD -> C, BC -> A. Check for both BCNF and 3NF status. Which of the following is the most accurate summary of the results?R is in BCNF and 3NF. No normalization is necessary.R is in 3NF but not in BCNF. We should try normalizing to BCNF, but this results in information being lost. As such, we stay with the original schema.R is in BCNF but not in 3NF....
Consider a relation R with ve attributes A, B, C, D, and E. You are given the following functional dependencies: A->B, BC->E, and ED->A. (a) List all keys for R. (10 points) (b) Is R in BCNF? If it is, explain why. If is not, decompose it into a collection of BCNF relations. (20 points) (c) Is R in 3NF? If it is, explain why. If it is not, convert it into a collection of 3NF relations. (20 points)
Language: SQL - Normalization and Functional Dependencies Part 4 Normalization and Functional Dependencies Consider the following relation R(A, B, C, D)and functional dependencies F that hold over this relation. F=D → C, A B,A-C Question 4.1 (3 Points) Determine all candidate keys of R Question 4.2 (4 Points) Compute the attribute cover of X-(C, B) according to F Question 43 (5 Points) Compute the canonical cover of F.Show each step of the generation according to the algorithm shown in class....
**************PLEASE COMPLETE PART F) ONLY************** Consider the following relational database schema (primary keys are underlined) and SQL query: Hotel (hotelNo, hotelName, city) Room (roomNo, hotelNo, type, price) Booking (hotelNo, guestNo, dateFrom, dateTo, roomNo) Guest (guestNo, guestName, guestAddress) SELECT g.guestNo, g.guestName FROM Room r, Booking b, Hotel h, Guest g WHERE h.hotelNo = b.hotelNo AND g.guestNo = b.guestNo AND h.hotelNo = r.hotelNo AND h.hotelName = "Ritz" AND dateFrom >= "Jan 01, 2001" AND dateTo <= "Dec 31, 2001"; (A) state what...
**************PLEASE COMPLETE PART E) ONLY************** Consider the following relational database schema (primary keys are underlined) and SQL query: Hotel (hotelNo, hotelName, city) Room (roomNo, hotelNo, type, price) Booking (hotelNo, guestNo, dateFrom, dateTo, roomNo) Guest (guestNo, guestName, guestAddress) SELECT g.guestNo, g.guestName FROM Room r, Booking b, Hotel h, Guest g WHERE h.hotelNo = b.hotelNo AND g.guestNo = b.guestNo AND h.hotelNo = r.hotelNo AND h.hotelName = "Ritz" AND dateFrom >= "Jan 01, 2001" AND dateTo <= "Dec 31, 2001"; (A) state what...