Recitation #2

1. 

It is supposed that through time, 107 billion individual human beings
have lived. Let us assume that each of these people have an 8 byte id,
a 3 byte encoding of their date of birth, a 3 byte encoding of their
date of death, an 8 byte foreign key to their mother and an 8 byte
foreign key to their father. Then we have 10 bytes to encode the
characteristics of the persons life (their outlook, tastes, cause of
death, basic life trajectory, etc.). Assume that disk blocks are 8K
and with 64-bit addresses. Further assume that B+tree pointers are
64-bits and main memory pages/blocks are also 8K.

a. For person data, what is the blocking factor for a B+ tree leaf
node with forward and backward pointer to sequential blocks.

b. How many disk blocks do we need to represent every person who ever
lived?

c. What is the order of an internal B+tree node?

d. Assuming that we cluster on person ids with completely filled leaf
blocks. What is the maximum height B+tree for an index built over id
as the search key.

e.. For d. above, what is the minimum height tree?

2. 

The system part will require you to install postgresql on your laptops and then tune
brainz to make query answering as fast as possible. You may work with one other partner on this system exercise.

First get the data here: brainz.zip
NOTE: Don't use the admin interface. Instead \i brainz.sql from the command line.

1. Give the name of all artists or groups who have released an album called "The Wall".
2. Give the name of all artists or groups who have released an album containing the word "love"
3. Give the name of all the artists or groups who have not released an album containing the word "Babylon".
4. Give all the names that are shared among several (more than one) different artists. For example I suspect that the there is more than one artist named 'John Smith'.
5. Give the names all all tracks that have been released by 'Pink Floyd'
6. Give a list of the names of all tracks by artists who released albums in the '90's
7. Names of artists that died at age 27 between 1965 and 1995
8. Count of artists that died at age 27 between 1965 and 1995?
9. Count of artists that died at age 28 between 1965 and 1995?

If you are unable to have super-user access to a lap-top, let me know
and I will try to arrange something. Whatever you do, DO NOT run this
system exercise on the school's database server. You will certainly
cause a lot of unintended consequences if you (even try to) do so.

What to hand in:

Give a papers that include you answers to problem 1. For problem 2, print out all SQL used (including index declarations and similar), and a copy of the explain analyze parse tree for each answered query.