Database management, Spring 2005

Revision of relational algebra and the use of metadata.

Exercise 1

• The database has the tables

  Person(Id, Firstname, Lastname) 
  Dead(Id, DateOfDeath) 
  Marriage(HusbandId, WeddingDate, WifeId)
  Divorced(HusbandId, WeddingDate, DateOfDivorce)
  

  • Give the query "Names of Melanie Griffith's husbands and the dates they where wedded with Melanie Griffith" in SQL and as a relational algebra expression.
  • Give the query "Names of Melanie Griffith's dead husbands" in SQL and as a relational algebra expression.
• Find out how direct access (random access) files are handled in Java programming language. Find out or construct a small example.
• Find out how you can detect the structure (columns, primary key, and foreign keys) of your table using Oracle catalog. Give the SQL queries. The contents of the catalog is described, for example, in 'Oracle 8 Server, Release 8.0.5 / Oracle8 reference' manual in http://www-db.helsinki.fi/oracledocuments. You may try your queries with bodbacka's database test using Oracle's demo account scott/tiger. Instructions for using this database may be found in the home page of the 'Database project' course.
• Find out how you can detect the structure of your tables in a database independent way using JDBC.