Can you please validate if my answers are correct?
CS3743 Hwk 2 Physical Performance (30 pts) Consider a Vehinterest table that contains the following attributes: custNr 8 character customer number. When combined with vehType and vehMake, it is the unique key for Vehinterest vehType 5 character type of vehicle (e.g., TRUCK, SUV, VAN, ). When combined with custNr and vehMake, this is the unique key for this table. vehMake 12 character make of vehicle (e-g., 4Runner, Explorer, Prius, Connect). When combined with custNr and vehType, this is the unique key for this table. ageRangeCd customer's age range (e.g., 1:under 20, 2:20-29, 3:30-39, 4:40-49, 5:50-59, 6:60-69, 7.over 70), this is 1 byte lastActivityDt 4 byte date representing the last activity for this row email customer's email, max of 250 character, average of 36 characters streetAddress customer's street address, max of 150 characters. These average is 40 bytes. Assumptions: 1. There are 10,000,000 rows and 1 row = 1 customer 2. Data and index blocks are 4096 bytes. 3. An index entry for a B+Tree contains: the key (size depends on the contents) and a Row ld which is 4 bytes. 4. There are two variable-sized attributes (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. 5. Assume there are 200,000 customers in ageRangeCd=5. 10% of those customers are interested in SUVS. Questions Part 1: Space (show your work for partial credit) A. How big is one data row? You must consider: size of each fixed-size attribute For each variable-sized attribute, consider the average size plus one byte for the length. Also, each attribute after the first variable-sized attribute must have a two byte offset. Include a row size field of 2 bytes per row. Average row size = 101 bytes B. How many rows fit in a data block? You must consider: average size of a row (from part 1A) 20 byte overhead in the data block (leave out for my midterm) two bytes for each entry in the offset array per row in the data block (leave out for my midterm) Rows per data block = 39 rows per block Number of data blocks = 256,411 data blocks C. For a unique index (called 11) on the combination of custNr,vehType, and vehMake: . what is the size of an Il index entry? (refer to assumption #3) 24 bytes how many entries fit in an index block if we assume 20 bytes of overhead per index block? (on my midterm leave out overhead) 169 entries/block how many entries are in the index? (CHANGE WORDING TO HOW MANY RECORD ENTRIES ARE ON THE LEAF LEVEL OF THE INDEX (REMEMBER EACH ENTRY IS AN INDEX ENTRY)) 10,000,000 /(how many index entries we can fit in the index block) how many leaf level index blocks are needed? 59,172 leaf level index blocks how many index entries are needed for the next higher level? index blocks? o index entries on the next higher level = 351 blocks on the next level index blocks on the next higher level = 3
