University of Helsinki
Database Management,  Practice Session 1, Spring 2003

1. Write the SQL queries to find out what tables you own in an Oracle database, and what are the structures of these tables. The describtions of the data dictionary tables needed for this task may be found, for example, in 'Oracle 8 Server, Release 8.0.5 / Oracle8 reference' manual in http://www-db.helsinki.fi/oracledocuments.

2. Find out how to solve the problem of task 1 using JDBC and any dbms.

3. Find out how the foreign key is described in the Oracle data dictionary You may use manual or web search. Write an SQL query to find out the foreign keys in a given table.

4. A disk unit has 10 disks (20 surfaces). Each disk has 4000 tracks. Tracks have an equal capacity. The size of a disk block is 1KB. Each track stores 500 blocks. The rotation speed is 3600 rpm. The average seek time is 12 ms.
   1. What's the capacity of a cylinder?
   2. How many cylinders there are in this disk unit?
   3. What's the capacity of the entire disk unit?

5. Consider the disk unit of task 4.
   1. What is average access time for a random isk block?
   2. How long does it take, in average, to read 1000 blocks in random sequence?
   3. How long does it take to read 1000 sequential blocks?
   4. A file of 50000 blocks is stored in the optimal way on the disk unit. For what values of X it is more efficient read the entire file than to read X records one by one in a random order?

6. How do the following improvements affect the factors of the access time:
   1. doubling of the rotational speed,
   2. doubling of the storage density (capacity of the track)
   3. placing two read/write heads in each arm
   4. doubling the size of a block