Description
Question 1
Create B tree and B+ tree of degree 3 for the following sequence of keys. Show the structure in both cases after every insertion.
60, 41, 50, 76, 37, 39, 68, 49, 44
Question 2
A file has r=30,000 EMPLOYEE records of fixed-length. Consider a disk with block size B=512 bytes. A block pointer is P=6 bytes long and a record pointer is P R =7 bytes long.
Each record has the following fields: NAME (30 bytes), SSN (9 bytes), DEPARTMENTCODE (9 bytes), ADDRESS (40 bytes), PHONE (9 bytes), BIRTHDATE (8 bytes), SEX (1 byte), JOBCODE (4 bytes), SALARY (4 bytes, real number). An additional byte is used as a deletion marker
.
- Calculate the record size R in bytes.
- Calculate the blocking factor bfr and the number of file blocks b assuming an unspanned organization.
- Suppose the file is ordered by the key field SSN and we want to construct a primary index on SSN. Calculate the index blocking factor bfr i.
Question 3
Assume the following below given tables and answer the questions.
Teacher (teacher_ID, teacher_name, teacher_dept, teacher_salary)
Teaches (teacher_ID, course_id, sec_id, semester, year)
Course (course_id, course_title, dept_name, course_credits)
-
Query 1:
write a relational algebra query to find the names of all teachers in the IT department, along with the course titles of the courses that they teach. -
Query 2:
write a relational algebra query to find the names of all instructors in the IT department who have taught a course in 2020, along with the titles of the courses that they taught.
Question 4
Consider the given schema and answer the questions.
Q(a,b), R(b,c), S(b,d), T(b,e).
-
For the following SQL query, give two equivalent logical plans in relational algebra such that one is likely to be more efficient than the other. Indicate which one is likely to be more efficient. Explain. -
By using the left-deep tree plan favored by optimizers. Write a left-deep tree plan for the below given SQL query.
Use relational algebra
; be sure to use parentheses to indicate the order that the joins should be performed.
SELECTQ. a
FROMQ, R
WHEREQ.b = R.b AND R.c = 3
SELECT*
FROMQ, R, S, T
WHEREQ.b = R.b AND R.b = S.b AND S.b = T.b
