Övning om fysiska databaser (Databaser, fortsättningskurs)

Expressed with the usual notation:

Subscriber(Snr, Name, Address, Category)
Call(Cnr, Caller, Called, Start, End, Duration)

Queries like these will be used fairly often:

1. Anne has the address 11 Any Rd. What's her number?

   select snr
   from subscriber
   where name = 'Anne' and address = '11 Any Rd'
   

2. What is the name and address of the subscriber that made call number 2001?

   select name, address
   from subscriber, call
   where subscriber.snr = call.caller
   and call.cnr = 2001
   

3. What is the sum of call durations for subscriber 17?

   select sum(duration)
   from call
   where caller = 17
   

1. Assume that we have no indexes and no specific sorting order. How long will the queries above take to execute? Assume a good query optimizer. Make realistic assumptions about block size, block access times, etc.
2. Chose suitable indexes, i. e., which columns you will add indexes for.
3. Assume that the database uses (suitably large) hash tables for all its indexes. How long will the queries take to execute? Assume a good query optimizer.
4. Assume that the database uses B+ trees for all its indexes. How long will the queries take to execute? Assume a good query optimizer.
5. The telecom company grows, and now has one million subscribers, who have made ten million calls. How will this affect the times in question 1, 3 and 4?
6. Can you do something else to speed up the queries?

[Solutions]