CT230 Lab 3

http://geminga.it.nuigalway.ie/~gettrick/courses/CT230/labs/lab3.html

In this lab you will work with Queries to create new Relations from old. Queries are questions about the data in the database - essentially the Relational Algebra Operators covered in lectures. The lab is based on the database

Cars RegNum Owner Address
92-KE-191 H. Saville Maynooth
99-TS-430 P. Kelly Clonmel

Officer RegNum Surname Firstname
567 White John
513 Burke NULL

Offence Code Date Officer CarReg
18 03/10/97 513 99-TS-430
19 04/04/98 NULL 99-TS-430
20 NULL 513 NULL

which is also available at

http://geminga.it.nuigalway.ie/~gettrick/courses/CT230/labs/cars.mdb
  1. Download the above database from the web address listed, or type it in as given above. There are two Relationships: The Primary Key Officer.RegNum is the Foreign Key Offence.Officer and the Primary Key Cars.RegNum is the Foriegn Key Offence.CarReg.

  2. Open the database with name cars.mdb. Click on the Queries tab (next to the Tables tab) and then click on New. Open the Query under Design View. Highlight the table offence and click on Add. Then click on Close. To perform a query involving a particular attribute, double click on the attribute name in the table (or drag it down to the Field: box towards the bottom of the Design View). If you wish to enter a selection condition, type it in the Criteria: box below Field:.

  3. As an example, double click on Code from the offence table. In the Criteria: box enter 18. Now go to the top title bar in ACCESS and from the View menu select Datasheet View. This will give the result of your query, simply

    Code
    18

    By entering 18 in the Criteria: box, it is understood that Code=18 is the condition. This matches the first tuple in the Offence table.

  4. From the View menu select Design View once again. Now double click on all the remaining attributes in offence to add them to the Field: part of the query: There should now be four columns: Code, Date, Officer, CarReg. From View select Datasheet View. You should see a table (relation) resembling

    Code Date Officer CarReg
    18 3/10/97 513 99-TS-430

    What we have performed here is effectively a select operation on the offence relation with condition C as Code=18, i.e. sCode=18(offence).

  5. Return to Query Design View and in the Criteria: box under Code replace 18 by >18. Go again to Datasheet View to get the table

    Code Date Officer CarReg
    19 4/4/98 99-TS-430
    20 513

  6. Experiment with different settings in the Criteria: box. Note that when more than one criteria is entered for different fields, there is a logical AND put between them. Edit your last query inserting criteria offence.Code > 18 AND offence.Date <> 5/4/98. What does the resulting Datasheet View tell you about NULL values of attributes that are involved in Criteria:?

  7. In the View menu from the top title bar you will see a third view called SQL View. Select this and look at the resulting code. This is Standard Query Language.

  8. TO BE HANDED IN
    1. Using SQL View, write down the code for each of the queries in (3.) (4.) (5.) and (6.) above.
    2. Suppose we form a query on the offence table choosing the two attributes/fields officer and CarReg, and leaving the Criteria boxes empty. Print out the resulting table, and write this query in terms of the projection operator p.
    3. From the cars.mdb database answer the following questions:
      (a) Write down the dates (if any) on which Officers whose Code is not 513 reported Offences.
      (b) Write down the CarReg numbers (if any) of cars that either committed an Offence that was reported by Officer 513 or did not commit an Offence on the third of October, 1997.
      (c) Write down the Surnames (if any) of Officers who did not report an Offence on the fourth of April, 1998.
© NUI, Galway