Database System Questions
Physical Performance In this part of the exam you will need to show your work and calculations. However, every one of your answers should finish with the format shown in the following example. Let's assume the question is, With a rectangle of width of 10 mts and a height of 6 mts, calculate the area of a rectangle: Your question showing your calculation would be: rectangle area (in squared meters) = w * l = 10 * 6 = 60 The format above shows a portion highlighted in green and one in yellow. The yellow part corresponds to the final result and every question should finish as shown there with an equals followed by the resulting number of the corresponding question with no whitespaces or anything else after. The green color part in the format is for you to write your work and calculations for possible partial credits. You don't need to show the colors in your answers, just follow the format.\ Consider a ShelterDog table that contains the following attributes: · dogID: 3 character dogID. When combined with ownerID, and stateID is the unique key for this table. · ownerID: 8 character ownerID. When combined with dogID, and stateID is the unique key for this table. · stateID: 2 character state. (e.g., Tx – Texas, NY – New York, etc). When combined with dogID, and ownerID is the unique key for this table. · shelterID: 5 character shelter identifier. · dogName: 10 character dog name · breed: 12 character breed (e.g., Pitbull, Rottweiler, Labrador, Retreiver, etc.) · age: 1 byte integer value · weight: 1 byte integer value · dateAdopted: 26 characters for dog’s date adopted in Greenwich Mean Time. · ownersName: owner’s name, max 80 characters, an average 20 characters. · email: owner’s email, max of 100 characters, an average of 36 characters · streetAddress: owner's street address, max of 150 characters, an average of 40 characters. Every character will use 2 bytes. Assumptions: · There are 3.3 million rows in the ShelterDog Table. Because there are multiple rows per owner (due to different combinations of dogID), on average, there are 10 rows for every owner; therefore, there are 330,000 owners. · Data and index blocks are 4096 bytes. · An index entry for a B+Tree contains the key (size depends on the contents) and a Row Id which is 4 bytes. · There are three variable-sized attributes (owner’s name, email, and streetAddress). Each will use a one-byte length. Near the beginning of the row, there will be an additional 2 byte offset to access the streetAddress that follows the email. · Assume there are 300,000 dogs in Texas shelters. 15% of those dogs are labradors. Questions Part 1: Space (show your work for partial credit) 1-A. How big is one data row? You must consider: · At the beginning of the row, include: • row size of 2 bytes • for each column after the first variable-sized column, include a 2 byte value representing the offset to the column. · size of each fixed-size attribute · For each variable-sized attribute, consider the average size plus one byte for the length Question 1: Show your calculation for: Average row size = Question 2: Show your calculation for: How many rows fit in a data block? You must consider: · average size of a row (from previous question) · 20 byte overhead in the data block · two bytes for each entry in the offset array per row in the data block Show your calculation, remember to round down your value Question 3: Show your calculation for: What is the number of data blocks to hold all the records? Question 4: Show your calculation for: For a unique index (called I1) on the combination of dogID and shelterID: What is the size of an I1 index entry? Question 5: Show your calculation for: How many entries fit in an index block if we assume 20 bytes of overhead per index block? Round your value down Question 6: Show your calculation for: How many leaf level index blocks are needed? Round your value up Question 7: Show your calculation for: Based on how many index entries are needed for the next higher level how many index blocks are on the second from the bottom level? Round your value up Question 8: Show your calculation for: What's the number of blocks on the 3rd from the bottom level? Round your value up. Question 9: Show your calculation for: What’s the total index blocks that are needed for the complete b-tree, including all levels? Question 10: Show your calculation for: How many levels are there for the b-tree of index l1? Part 2: Reads (show your work for partial credit) Additional assumptions: · We have another index(called I2) on the combination of state, breed, and dogName: · The index nodes for this index have 78 entries per index block. 3,300,000/78 = 42,307.69; 42,308/78 = 542.41; 543/78 = 6.96; 7/78 = 1 index block at root. · There are four index levels for I2. · Do not worry if your answer to this is different in part 1. These values are arbitrary (but done to reduce taking off for also having a mistake in the first part): · Assume an average of 28 data rows per data block (even if it is different than the original blocking factor). The number of data blocks is 3,300,000 / 28 = 117,857 data blocks · Assume three index levels in the unique index, I1, on dogID and ShelterID. Part 2 Questions: For all questions below, we want to find all Labrador dogs in Texas using the index I2. State your assumptions. We have four levels in index I2 with 3,300,000 index entries. Question 11: Show your calculation for: how many entries in index I2 will satisfy this request based on the query's criteria and assumption #5? Question 12: Show your calculation for: how many leaf-level index blocks in I2 are there satisfying this request? Question 13: Show your calculation for: How many index blocks (in total) are read to satisfy this request? Question 14: Show your calculation for: What's the percentage of data rows satisfying the search criteria? Question 15: Show your calculation for: Assuming I1 is the clustering index (i.e., I2 isn't the clustering index), how many total reads are necessary to read our rows that satisfy the criteria? Question 16: Show your calculation for: Assuming I2 is the clustering index, how many total reads are necessary to satisfy the query? Question 17: Show your calculation for how many reads are necessary to find a particular row by the combination of ownerID, stateID, and dogID using index I1? Question 18: Show your calculation for: Assuming I2 is the clustering index, how many total reads are necessary to find a particular row using I1?