高级数据库代考 Advanced Database Organization Quiz

CS525 – Advanced Database Organization Quiz

高级数据库代考 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.

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=CS∨major=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 = σcredits≥39∧credits≤44(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)