Recitation #2

*** No more problems will be given. We will however be providing further information on how we want answers turned in. ***

1.

It is supposed that through time, 107 billion individual human beingshave 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 theirdate of death, an 8 byte foreign key to their mother and an 8 byteforeign key to their father. Then we have 10 bytes to encode thecharacteristics of the persons life (their outlook, tastes, cause ofdeath, basic life trajectory, etc.). Assume that disk blocks are 8Kand with 64-bit addresses. Further assume that B+tree pointers are64-bits and main memory pages/blocks are also 8K.

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

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

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

d. Assuming that we cluster on person ids with completely filled leafblocks. What is the maximum height B+tree for an index built over idas 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 tunebrainz 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.zipNOTE: Don't use the admin interface. Instead \i brainz.sql from the command line.

* Give the name of all artists or groups who have released an album called "The Wall".
* Give the name of all artists or groups who have released an album containing the word "love"
* Give the name of all the artists or groups who have not released an album containing the word "Babylon".
* Give all the names that are shared among several (more than one) different artists. For example I suspect Give a list of the names of all tracks by artists who released albums in the '90'sthat the there is more than one artist named 'John Smith'.
* Give the names all all tracks that have been released by 'Pink Floyd'
* Give a list of all the (names of the)all tracks released by 'Bananarama'by artists who released albums in the '90's.
* Names of artists that died at age 27 between 1965 and 1995
* Count of artists that died at age 27 between 1965 and 1995?
* 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 knowand I will try to arrange something. Whatever you do, DO NOT run thissystem exercise on the school's database server. You will certainlycause a lot of unintended consequences if you (even try to) do so.

¶ ¶ ¶