02/05/2020
COMP9315 14s2 Final Exam
COMP9315 14s2 The University of New South Wales DBMS Implementation
COMP9315 DBMS Implementation Final Exam 14s2
[Instructions] [Notes] [PostgreSQL] [C] [Q1] [Q2] [Q3] [Q4] [Q5] [Q6] [Q7] [Q8]
Question 2 (9 marks) Consider the following relation:
create table Product (
id int,
);
name char(25),
colour char(10),
cost float,
barcode char(13),
released date,
primary key(id)
Tuples from this relation are stored in a file with the following page structure:
Each page contains N tuple-slots and has an initial bit-string (N-bits in length). Bit i in the bit- string indicates the presence (1) or asbence (0) of a tuple in slot i.
Assume that
fields are stored in the tuple in the order they appear in the create table statement padding is only required before fields of type int, float or date
each of the data types int, float or date occupies 4 bytes
pages are 4KB, including both the presence bits and the tuple slots
there are 10,000 Product tuples
the load factor indicates the average percentage of filled slots per main data page (alternatively, you view it as the actual number of tuples as a percentage of the total capacity of the file)
the load factor can be > 100% (if overflow pages are used)
Based on the above, answer the following questions: a. How may bytes are needed for each tuple slot?
https://www.cse.unsw.edu.au/~cs9315/19T2/past-exams/14s2/Q02.html 1/2
02/05/2020 COMP9315 14s2 Final Exam
b. What is the maximum number of tuples that can be stored in each page?
c. If the relation is stored as a heap file, and has a load factor of 90% (i.e. on average, pages are only 9/10ths full), how many pages are needed to hold all tuples?
d. If the relation is stored as a hash file with 100 main data pages plus overflow pages, what is the load factor?
Instructions:
Type your answer to this question into the file called q2.txt Submit via: submit q2
End of Question
https://www.cse.unsw.edu.au/~cs9315/19T2/past-exams/14s2/Q02.html
2/2