Unit 1

Example 2.15:

Consider the operation , where U and V are the relations from the below given figure. We
must consider all nine pairs of tuples, one from each relation, and see whether the A component from
the U-tuple is less than the D component of the V-tuple. The first tuple of U, with an A component of
1, successfully pairs with each of the tuples from V. However, the second and third tuples from U, with
A components of 6 and 9, respectively, pair successfully with only the last tuple of V. Thus, the result
has only five tuples, constructed from the five successful pairings.

Answer

A U.B U.C V.B V.C D

1 2 3 2 3 4
1 2 3 2 3 5
1 2 3 7 8 10
6 7 8 2 3 4
6 7 8 2 3 5
6 7 8 7 8 10
9 7 8 2 3 4
9 7 8 2 3 5
9 7 8 7 8 10

Example 2.16

Here is a theta-join on the same relations U and V that has a more complex condition:
Answer

Example 2.17:

Suppose we want to know, from our running Movies relation, “What are the titles and years of
movies made by Fox that are at least 100 minutes long?”

One way to compute the answer to this query is:

1. Select those Movies tuples that have length > 100.

2. Select those Movies tuples that have studioName = ’Fox’.
3. Compute the intersection of (1) and (2).
4. Project the relation from (3) onto attributes title and year.

a. Give the expression tree for the above given question.

Answer

b. Represent the same expression tree in a conventional, linear notation, with parentheses as
a relational algebraic expression.
Answer

c. Give the equivalent form of the above relational algebraic expression

--------------------------------------------------------------------------------------------------------------------------------------
Example 2.18
Take the product of two relations R and S and Rename the attribute of which is common on R and S
as X. The result of the expression
 a. Give the table upon applying the rename

Answer

b. Give the alternative form of

Answer

Example 2.19:
a. What is the alternative relational algebraic expression for the natural join of the relations U
and V without using join operator?

Answer

b. What is the alternative relational algebraic expression for the below given theta join of the
relations U and V without using join operator?
Answer

Example 2.20: Consider the relation movies and the expression tree given below. Write One possible
linear notation of the expression tree.

Answer:

Exercise 2.4.1:
Consider the database schema with four relations
Product (maker, model, type)
PC (model, speed, ram, hd, price)
Laptop (model, speed, ram, hd, screen, price)
Printer (model, color, type, price)

Write the relational algebraic expressions and expression tree for the following queries.
a) What PC models have a speed of at least 3.00?
b) Which manufacturers make laptops with a hard disk of at least 100GB?
c) Find the model number and price of all products (of any type) made by manufacturer B.
d) Find the model numbers of all color laser printers.
e) Find those manufacturers that sell Laptops, but not PC’s.
f) Find those hard-disk sizes that occur in two or more PC’s
Answer
a. What PC models have a speed of at least 3.00?
b. Which manufacturers make laptops with a hard disk of at least 100GB?

c. Find the model number and price of all products (of any type) made by manufacturer B.

d. Find the model numbers of all color laser printers.

e. Find those manufacturers that sell Laptops, but not PC’s.

f. Find those hard-disk sizes that occur in two or more PC’s

The expression tree could be drawn as per the relational algebra expression.

Example 13.15:
Consider the MovieStar schema depicted below
CREATE TABLE MovieStar( name CHAR(30) PRIMARY KEY, address VARCHAR(255), gender CHAR(l),
birthdate DATE );
Assume all fields must start at a byte that is a multiple of four.
Tuples of this relation have a header and the following four fields:
1. The first field is for name, and this field requires 30 bytes. If we assume that all fields begin at a
multiple of 4, then we allocate 32 bytes for the name.
2. The next attribute is address. A VARCHAR attribute requires a fixed- length segment of bytes,
with one more byte than the maximum length (for the string’s endmarker). Thus, we need 256
bytes for address.
3. Attribute gender is a single byte, holding either the character ’M’ or ’F’. We allocate 4 bytes, so
the next field can start at a multiple of 4.
4. Attribute birthdate is a SQL DATE value, which is a 10-byte string. We shall allocate 12 bytes to
its field, to keep subsequent records in the block aligned at multiples of 4.

The header of the record will hold:

a) A pointer to the record schema.
b) The record length.
c) A timestamp indicating when the record was created.

We shall assume each of these items is 4 bytes long.

Then draw the layout of a record for a MovieStar tuple and determine the length of the record?

Answer

The length of the record is 316 bytes.

Example 13.16: Suppose we are storing records with the above-mentioned layout. These records
are 316 bytes long. Suppose also that we use 4096-byte blocks. Determine how many records
could be accommodated in a block and how much space is wasted?
Answer:
Of 4096 bytes, say 12 will be used for a block header, leaving 4084 bytes for data. In this space we
can fit twelve records of the given 316-byte format, and 292 bytes of each block axe wasted space.

Exercise 13.5.1:
Suppose a record has the following fields in this order: A character string of length 15, an integer
of 2 bytes, a SQL date, and a SQL time (no decimal point). How many bytes does the record take
if:
a) Fields can start at any byte.
b) Fields must start at a byte that is a multiple of 4.
c) Fields must start at a byte that is a multiple of 8.

Answer:
a) 15 + 2 + 10 + 8 = 35 bytes
b) (15 + 1) + (2 + 2) + (10 + 2) + 8 = 40 bytes
c) (15 + 1) + (2 + 6) + (10 + 6) + 8 = 48bytes

Exercise 13.5.3:
Suppose a record has the following fields in this order: A character string of length 15, an integer
of 2 bytes, a SQL date, and a SQL time (no decimal point). The records also have a record header
consisting of two 4-byte pointers and a character. Calculate the record length for the three
situations regarding field alignment
How many bytes does the record take if:
a) Fields can start at any byte.
b) Fields must start at a byte that is a multiple of 4.
c) Fields must start at a byte that is a multiple of 8.

Answer:
a) 4 + 4 + 1 + 35 = 44 bytes
b) 4 + 4 + (1 + 3) + 40 = 52 bytes
c) (4 + 4) + (4 + 4) + (1 + 7) + 48 = 72bytes

Example 13.18:
Suppose we have movie-star records with name, address, gender, and birthdate. We shall assume
that the gender and birthdate are fixed-length fields, taking 4 and 12 bytes, respectively. However,
both name and address will be represented by character strings of whatever length is appropriate.
Figure 13.23 suggests what a typical movie-star record would look like. Note that no pointer to the
beginning of the name is needed; that field begins right after the fixed-length portion of the record.
other header information record length to address gender birthdate address.

Exercise 13.7.1:
A patient record consists of the following fixed-length fields: the patient’s date of birth, social-
security number, and patient ID, each 10 bytes long. It also has the following variable-length fields:
name, address, and patient history. If pointers within a record require 4 bytes, and the record
length is a 4-byte integer, how many bytes, exclusive of the space needed for the variable- length
fields, are needed for the record? You may assume that no alignment of fields is required.

Answer:

4 + (3 − 1) × 4 + 3 × 10 = 42 bytes
Meaning:
• 4: Record length.
• (3-1): The number of pointers to variable-length fields.
• 3: The number of fix-length fields.

Example 14.7:
Consider the usual Movie relation:
Movie (title, year, length, genre, studioName, producerC#)
Suppose we have secondary indexes with indirect buckets on both studioName and yeax, and we
are asked the query SELECT title FROM Movie WHERE studioName = ’Disney’ AND year = 2005;
that is, find all the Disney movies made in 2005.

Figure 14.8 shows how we can answer this query using the indexes.
Using the index on studioName, we find the pointers to all records for Disney movies, but we do
not yet bring any of those records from disk to memory. Instead, using the index on year, we find
the pointers to all the movies of 2005. We then intersect the two sets of pointers, getting exactly
the movies that were made by Disney in 2005. Finally, we retrieve from disk all data blocks holding
one or more of these movies, thus retrieving the minimum possible number of blocks.

Example 14.22:
Consider the extensible hash table
where
k = 4; the hash function produces a sequence of only four bits.
i = 1 implies that the bucket array has only two entries, one for 0 and one for 1. The bucket array
entries point to two blocks.
Then Insert 0001,1001 and 1100 into the extensible hash table
Example 14.23:
Suppose we insert a record whose key hashes to the sequence 1010 to the above fig 14.23. Since
the first bit is 1, this record belongs in the second block.
However, that block is already full, so it needs to be split.
We find that j = i = 1 in this case, so we first need to double the bucket array, as shown in Fig. 14.24.
We have also set i = 2 in this figure.

Insert records whose keys hash to 0000 and 0111.

Example 14.24:
Consider a linear hash table with n = 2.
Assume the hash function h produces 4 bits, and we represent records by the value produced by
h when applied to the search key of the record.
i (the number of bits of the hash function that currently are used),
n (the current number of buckets), and
r (the current number of records in the hash table).
The ratio r/n will be limited so that the typical bucket will need about one disk block.
Choose n, the number of buckets, so that there are no more than 1.7n records in the file; i.e., r <
1.7n.

compute h(K), where K is the key of the record, and we use the i bits at the end of bit sequence
h(K) as the bucket number, m.
If m < n, add the record in bucket m, and if m > n, add the record in bucket .
Each time we insert, we compare the current number of records r with the threshold ratio of r/n,
and if the ratio is too high, we add the next bucket to the table.

Example 14.25:
What happens when a record whose key hashes to 0101 is inserted into the linear hash table in
fig 14.26.
Since this bit sequence ends in 1, the record goes into the second bucket of Fig. 14.26. There is
room for the record, so no overflow block is created. However, since there are now 4 records in 2
buckets, we exceed the ratio 1.7, and we must therefore raise n to 3. Since |"log2 3] = 2, we should
begin to think of buckets 0 and 1 as 00 and 01, but no change to the data structure is necessary.

We add to the table the next bucket, which would have number 10. Then, we split the bucket 00,
that bucket whose number differs from the added bucket only in the first bit. When we do the
split, the record whose key hashes to 0000 stays in 00, since it ends with 00, while the record
whose key hashes to 1010 goes to 10 because it ends that way. The resulting hash table is shown
in Fig. 14.27. n=3
Adding a third bucket Next, let us suppose we add a record whose search key hashes to 0001. The
last two bits are 01, so we put it in this bucket, which currently exists. Unfortunately, the bucket’s
block is full, so we add an overflow block. The three records are distributed among the two blocks
of the bucket; we chose to keep them in numerical order of their hashed keys, but order is not
important. Since the ratio of records to buckets for the table as a whole is 5/3, and this ratio is less
than 1.7, we do not create a new bucket.
The result is seen in Fig. 14.28.

Finally, consider the insertion of a record whose search key hashes to 0111. The last two bits are
11, but bucket 11 does not yet exist. We therefore redirect this record to bucket 01, whose number
differs by having a 0 in the first bit. The new record fits in the overflow block of this bucket.
However, the ratio of the number of records to buckets has exceeded 1.7, so we must create a new
bucket, numbered 11.

Coincidentally, this bucket is the one we wanted for the new record. We split the four records in
bucket 01, with 0001 and 0101 remaining, and 0111 and 1111 going to the new bucket. Since
bucket 01 now has only two records, we can delete the overflow block. The hash table is now as
shown in Fig. 14.29. Notice that the next time we insert a record into Fig. 14.29, we shall exceed.

Unit 2
 and determine the estimated number of tuples
in T(R join S join U)

Answer

= 1000 x 2000/50 = 40,000

(R join S) join U is

Otherwise

Determine the best least cost plan

Answer
In this example, we see that the least of all costs is associated with the fourth expression:

This expression is the one we select for computing the join; its cost is 3000