## CS525 – Advanced Database Organization Quiz

### Instructions

• You have to upload the assignment to the course blackboard.
• This is an individual and not a group assignment. Fraud will result in 0 points
• For your convenience the number of points for each part and questions are shown in parenthesis.
• There are 2 parts in this quiz
1. Result Size Estimations
2. I/O Cost Estimation

### Part 1 Result Size Estimations (Total: 25 Points) 高级数据库代考

Consider a table student with attributes CWID, name, major, credits, a table course with title, instructor, credits, and a table registered with attributes student and course. registered.student is a foreign key to CWID. Attribute course of relation registered is a foreign key to attribute title of relation course. Given are the following statistics:

T(student) = 40,000                  T(course) = 1,000                        T(registered) = 20,000

V(student,CWID) = 40,000         V(course,title) = 1,000                  V(registered, student) = 5,000

V(student,name) = 39,600          V(course,instructor) = 500            V(registered, course) = 100

V(student,major) = 50                  V(course,credits) = 59

V(student,credits) = 59

Assume the range [0,90] for the credits.

#### Question 1.1 Estimate Result Size (5 Points)

Estimate the number of result tuples for the query q = σinstructor=Bob(course) using the fifirst assumption presented in class (values used in queries are uniformly distributed within the active domain).

#### Question 1.2 Estimate Result Size (5 Points) 高级数据库代考

Estimate the number of result tuples for the query q = σmajor=CSmajor=EE(students) using the fifirst assumption presented in class.

#### Question 1.3 Estimate Result Size (7 Points)

Estimate the number of result tuples for the query q = σcredits39credits44(course) using the fifirst assumption presented in class.

#### Question 1.4 Estimate Result Size (8 Points)

Estimate the number of result tuples for the query q below using the fifirst assumption presented in class. ### Part 2 I/O Cost Estimations (Total: 30 Points) 高级数据库代考

#### Question 2.1 External Sorting (5 Points)

You have a block of size of 128KB and 2GB memory. Compute the maximum size of fifile that can be sorted by 3-Pass Multiway Sort.

#### Question 2.2 I/O Cost Estimation (25 Points) 