Computer Science > QUESTIONS & ANSWERS > EECS 484 Database Management Systems Homework 5 (100 points) <University of Michigan - EECS 484>ALL (All)

EECS 484 Database Management Systems Homework 5 (100 points) <University of Michigan - EECS 484>ALL ANSWERS CORRECT, 100% SCORE

Document Content and Description Below

Since we are using the form of multiple-choice questions in the google form, the descriptions of questions will be slightly different in this document. Please remember to double check the descriptions when you submit your answers. This homework is to be done in teams of 2 students; individual work is permitted, but not recommended. If you work in a team, please make sure to specify your partner’s name and unique name in the google form. Both members of each team will receive the same score; however, both team members should submit the solution individually via this google form. As usual, No late days for homeworks! If you miss the due date, you get 0 points. No exceptions on this. By submitting this homework, you are agreeing to abide by the Honor Code: I have neither given nor received unauthorized aid on this assignment, nor have I concealed any violations of the Honor Code.Question 1. Left-Deep Plan (32 points) Team (tid, tname, sport, ranking) Game (tid, vid, date, result) Venue (vid, venue) Q1.1 [2 points] Given the above schema and no information on data sizes, select the plans that are valid left-deep plans for the query (avoid plans that cause a join to degenerate into a cross-product) from the given plans in the Google Form where you are filling in the answers. SELECT T.tname FROM Team T, Game G, Venue V WHERE T.tid = G.tid AND G.vid = V.vid AND T.sport = ‘football’ AND V.venue = ‘Michigan Stadium’; Q1.2 [30 points] Consider the following plan for the questions that follow later in this problem. This plan only shows the Join operators. Assume that selections and projects are applied on the edges as appropriate so that projections and selections are moved inside to reduce table sizes as much as possible prior to doing any joins. For example, between T and the join operator, you would want to do a selection on T.sport = 'football' and also a projection to remove columns that are not going to be used. You can assume that projections and selections can be done together as tuples of T are being read into the buffer. Similarly, you will want to insert selections and projection operators along other edges as needed. You are given the following information about the relations in the system catalogs. You will be answering several questions related to answering the above query using an appropriate query plan.Team Table: 600 data pages. Even distribution among 5 kinds of sports (one of them is ‘football’). Index on Team Table: Clustered B+ tree index on attribute sport. The entire tree, including the leaves, fits in memory. Venue Table: 300 data pages. Even distribution among 4 venues (one of them is ‘Michigan Stadium’). No indexes available on Venue. Game Table: 900 data pages. Every team has the same number of venues where it participated in a game as any other team. Every venue has the same number of teams that played in it as any other venue. No index available on Game.

[Show More]

Last updated: 3 years ago

Preview 1 out of 9 pages

Buy Now

Instant download