Database management, Fall 2004

Exercise 1 (4.11.2004)

• 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.
• As an example associated with task 2, write Java code (essential parts) for class AddressBook. AddressBook records names and their associated email addresses in a direct access file. A record may be of fixed or varying length. AddressBook should provide methods for adding a new address, changing an address, retrieving an address based on its sequence number and retrieving an address based on the associated name.
• 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.