Question

1. (24 points) Consider you have a Person table with SSN as the primary key, and an attribute age (there are other attributes, which is not important to this problem). You are also given the following: • Each tuple of the table has size of 200 bytes • Each sector (page) on the disk have 1100 bytes • No tuple is split between two pages • There are 600,000 tuples in the table • The table is store in a single file, but is split (equally) between 2 tracks on the disk. Within the same track the sector of the file is contiguous • Each seek/rotation combination takes 50 milliseconds • Transferring a single page from the disk to memory take 0.5 millisecond. • When you first access a file, you will need to do a single seek/rotation combination a. How many tuples can be stored in a single page? b. How many pages are needed to store the table? c. Consider the following SQL query: SELECT * from Person where SSN=”123456789”. Calculate the best case and worst case time taken to answer this query if i. There is no index ii. There is a clustering index on SSN (Ignore the time needed for reading the index) d. Now consider the following SQL query: SELECT * FROM Person WHERE (age > 20) and (age < 29) ORDER BY age. Assume that there is a non-clustering index on the age attribute. Assume that you want to use the index to answer the query by first accessing the index, and then read each tuple in the order that the index provided. (Assume there is NO buffer to store the location of the buffer so as to make reading easier – i.e. you really do have to access each tuple in the order of the age attribute). Calculate the (worst case) time taken to answer this query using the index if i. There are 2 tuples that satisfy this query ii. There are 5,000 tuples that satisfy this query (Once again, ignore the time needed for reading the index) Show your work in all the cases.

          1. (24 points) Consider you have a Person table with SSN as the primary key, and an attribute age
(there are other attributes, which is not important to this problem). You are also given the
following:

• Each tuple of the table has size of 200 bytes
• Each sector (page) on the disk have 1100 bytes
• No tuple is split between two pages
• There are 600,000 tuples in the table
• The table is store in a single file, but is split (equally) between 2 tracks on the disk.
Within the same track the sector of the file is contiguous
• Each seek/rotation combination takes 50 milliseconds
• Transferring a single page from the disk to memory take 0.5 millisecond.
• When you first access a file, you will need to do a single seek/rotation combination

a. How many tuples can be stored in a single page?
b. How many pages are needed to store the table?
c. Consider the following SQL query: SELECT * from Person where SSN=”123456789”.
Calculate the best case and worst case time taken to answer this query if
i. There is no index
ii. There is a clustering index on SSN (Ignore the time needed for reading the
index)

d. Now consider the following SQL query: SELECT * FROM Person WHERE (age > 20) and
(age < 29) ORDER BY age. Assume that there is a non-clustering index on the age
attribute. Assume that you want to use the index to answer the query by first accessing
the index, and then read each tuple in the order that the index provided. (Assume there
is NO buffer to store the location of the buffer so as to make reading easier – i.e. you
really do have to access each tuple in the order of the age attribute). Calculate the
(worst case) time taken to answer this query using the index if
i. There are 2 tuples that satisfy this query
ii. There are 5,000 tuples that satisfy this query

(Once again, ignore the time needed for reading the index)

Show your work in all the cases.

1. (24 points) Consider you have a Person table with SSN as the primary key, and an attribute age
(there are other attributes, which is not important to this problem). You are also given the
following:

• Each tuple of the table has size of 200 bytes
• Each sector (page) on the disk have 1100 bytes
• No tuple is split between two pages
• There are 600,000 tuples in the table
• The table is store in a single file, but is split (equally) between 2 tracks on the disk.
Within the same track the sector of the file is contiguous
• Each seek/rotation combination takes 50 milliseconds
• Transferring a single page from the disk to memory take 0.5 millisecond.
• When you first access a file, you will need to do a single seek/rotation combination

a. How many tuples can be stored in a single page?
b. How many pages are needed to store the table?
c. Consider the following SQL query: SELECT * from Person where SSN=”123456789”.
Calculate the best case and worst case time taken to answer this query if
i. There is no index
ii. There is a clustering index on SSN (Ignore the time needed for reading the
index)

d. Now consider the following SQL query: SELECT * FROM Person WHERE (age > 20) and
(age < 29) ORDER BY age. Assume that there is a non-clustering index on the age
attribute. Assume that you want to use the index to answer the query by first accessing
the index, and then read each tuple in the order that the index provided. (Assume there
is NO buffer to store the location of the buffer so as to make reading easier – i.e. you
really do have to access each tuple in the order of the age attribute). Calculate the
(worst case) time taken to answer this query using the index if
i. There are 2 tuples that satisfy this query
ii. There are 5,000 tuples that satisfy this query

(Once again, ignore the time needed for reading the index)

Show your work in all the cases.

Added by Jessica P.

Question

Please give Ace some feedback