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.

   Oracles's dictionary table user_tables gives information about tables. Table user_tab_columns list the columns. The following query retrieves column information for all users' tables.

   	select t.table_name, column_name, data_type, data_length, 
         nullable, column_id 
       from user_tables t, user_tab_columns c 
   	where t.table_name=c.table_name 
       order by c.table_name, column_id 

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

   JDBC API provides the class DatabaseMetaData for metadata. The method getMetaData() of class Connection retrieves the metadata associated to the current connection. To get the tablenames DatabaseMetaData provides the method getTables(String catalog, String schemaPattern, String tableNamePattern, String [] types). The pattrers are masks uses in mapping strings with SQL's like operation. The tables of owner SCOTT are retrieved with method call getTables(null,"SCOTT","%",{"TABLE"}). getTables produces a ResultSet object as its return value. Each row of the result contain the columns TABLE_CAT, TABLE_SCHEM, TABLE_NAME, TABLE_TYPE and REMARKS. Method getColumns gives information about columns. In this case the resulting rows contain columns COLUMN_NAME and TYPE_NAME

   Petri Helin has mase a test program, to apply these metrhods. This program builds the create table definitions for Scott's tables.

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.

   Oracle's table user_constraint contains information about integrity constraints. If the constraint is foreign key constraint, the type of the constraint is 'R'. Two rows of table user_constraints are needed in representing a foreign key: one for the origin and one for the target. Column r_constraint_name in the origin row identifies the constraint. The columns that belong to the foreign key are defined in table user_cons_columns. The following query contains an example of how to use these tables.

      select c.constraint_name, c.table_name, r.table_name 
        referenced_table, cc.position, cc.column_name, c.delete_rule 
      from user_constraints c, user_constraints r, user_cons_columns cc 
      where c.constraint_type='R' and 
        c.status='ENABLED' and c.r_constraint_name=r.constraint_name 
        and c.constraint_name=cc.constraint_name 
      order by c.constraint_name, c.table_name, cc.position 

4. A disk unit has 10 disks (20 surfaces). Each disk surface 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? 4000

      The capacity of a cylinder = track capacity * number of tracks in cylinder = 500*1KB*20= 10MB

   2. How many cylinders there are in this disk unit? 4000
   3. What's the capacity of the entire disk unit? number of cylinders* cylinder capacity = 40GB

5. Consider the disk unit of task 4.
   1. What is the average access time for a random disk block?

      Block access time = seek time + rotation delay + transfer time = 12ms + 1/2*1/60 s + 1/500*1/60s=
      12ms+1/120 s+1/30000s=
      12ms+8.3ms+0.03 ms=20.33ms

   2. How long does it take, in average, to read 1000 blocks in random sequence?
      1000*20.33ms=20.33s
   3. How long does it take to read 1000 sequential blocks?
      12ms+8.3ms+1000/500 * 1/60s =20.3ms+33.3ms= 53.3 ms
   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?
      We need:
      100 rotations + seek time + rotation delay + 4* (single track passage time + potential rotation delay)
      these are less than
      100*1/60s+5*(12ms+8.3ms) = 100*16.7ms+ 5* 20.3ms= 1667.ms + 101.5ms =1768.6 ms
      A random access takes 20.33 ms thus 1768.6 ms will be exceeded when X>=87.

6. How do the following improvements affect the factors of the access time:
   1. doubling of the rotational speed,
      both the transfer time and the rotation delau are reduced to the half of the original ones.
   2. doubling of the storage density (capacity of the track)
      the transfer time will reduce to the half of the original one.
   3. placing two read/write heads in each arm.
      the seek time will reduce but notas much as to the half of the original.
   4. doubling the size of a block
      Block transfer takes twice the brevious time. We need to access only half of the number of blocks tos retrieve a file. This reduces the time for randomly processing many blocks.