[SOLVED] Comp 5120/6120 database systems i final exam

Problem 1 Concepts (20 points)
(1) Current hard disks typically allow at most one disk head to read or write at any one
time.
True False
(2) Updates of sorted files are much faster than updates of indexed files.
True False
(3) A search key of an index is the same as a candidate key (minimal set of fields that
uniquely identify a record in a relation).
True False
(4) For data entry k* in index, alternative 1 (data record with key value k) implies clustered
index and in practice, clustered index also implies alternative 1.
True False
(5) A RAID Level 5 system has the best performance of all RAID levels with redundancy
for small and large read and large write requests.
True False
(6) We can create several clustered indexes on a file without replicating data records.
True False
(7) It is the responsibility of the buffer manager to pin and unpin a page.
True False
(8) For ISAM (Indexed Sequential Access Method) tree indexes, any sequence of inserts
or deletes of a set of data entries will result in the same tree.
True False
(9) Based on the assumptions of our textbook, in order to insert a record into a heap file
with an unclustered hash index, we have to finish the whole process at a cost of 2D (D
– the average time to read or write a disk page).
True False
(10) In a bulk loading B+ tree, all the leaf nodes are stored sequentially and linked with
pointers.
True False
Page 3 of 9
Problem 2 SQL (20 points)
Consider the following relations:
Student(snum: integer, sname: string, major: string, level: string, age: integer)
Class(cname: string, meets_at: string, room: string, fid: integer)
Enrolled(snum: integer, cname: string)
Faculty(fid: integer, fname: string, deptid: integer)
The meaning of these relations is straightforward; for example, Enrolled has one record
per student-class pair such that the student is enrolled in the class.
Write the following queries in SQL. No duplicatesshould be printed in any of the answers.
1. For each level, print the level and the average age of students for that level.
2. Find the names of all Seniors (level = SR) who are enrolled in a class taught by John
Doe.
Page 4 of 9
3. Find the names of all classes that either meet in room Shelby 1120 or have three or
more students enrolled.
4. Find the names of faculty members for whom the combined enrollment of the courses
that they teach is less than ten.
Page 5 of 9
Problem 3 B+ Tree (25 points)
Consider the following B+ tree indexes of order d = 2.
1. Show the tree that would result from deleting a data entry with key 25 from the
following B+ tree (follow the algorithm in the textbook). (11 pt)
Page 6 of 9
2. Show the tree that would result from inserting a data entry with key 25 into the
following B+ tree (follow the algorithm in the textbook). (14 pt)
Page 7 of 9
Problem 4 Storage and Indexing (35 points, 7 points each)
1. Briefly explain why we need the two techniques, data striping and redundancy, in
RAID (Redundant Arrays of Independent Disks) systems.
2. What is the role of the buffer manager in a DBMS? Explain what the buffer manager
must do to process a read request for a page.
Page 8 of 9
3. Explain the index-only evaluation technique with an SQL query sentence as an
example.
4. Explain the difference between an ISAM (Indexed Sequential Access Method) tree and
a B+ tree.
Page 9 of 9
5. What is the difference between a clustered index and an unclustered index? If an index
contains data records as data entries, can it be unclustered?