CT230 Lab 4

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

• if any of this lab is unclear, raise your hand or go up to ask the TA
• your lab report (hardcopy/printout) should be written during the lab and given to the TA at the end of the lab for grading [make sure your ID & name are on the cover]
• if there is a problem with your computer, try rebooting. if this does not work, go to another computer, and send an e-mail to joe.oconnell@nuigalway.ie to report the problem

This lab will use ACCESS to write simple SQL code for Queries. We will work with the same database as last week at

• 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 cars and click on Add. Then click on Close. We will now do a SELECT on the table cars. In Design View, double click on Address so it gets added to the Field: box below. Now from View in the menu on the top title bar, choose SQL View. You should see

  SELECT cars.Address
  FROM cars;

  Now from the Query menu at the top choose Run. You should get the following table:

  Address

  Maynooth

  Clonmel

• Save the previous Query. Now re-open it and choose SQL View. Edit the code to get

  SELECT *
  FROM cars;

  and Run this code as before. Since * is the wildcard operator, we get all the fields from the cars table, i.e. the complete table. Now open this Query under Design View to see the correspondence.

• Under SQL View type

  SELECT *
  FROM cars,officer;

  and Run this Query. Note that the output is the same as the Cartesian Product cars X vans since we are selecting all attributes from both tables.

• TO BE HANDED IN
  Develop BOTH Queries in SQL View and the result (table) from running the Query corresponding to the following:
  1. List the First Names of all Officers who registered an Offence with Code 18.
  2. List the Owners and Addresses of Cars that committed Offences that were NOT registered by the Officer with surname White.
  3. List the Owners of all cars that committed offences on the fourth of April, 1998.

• TO BE HANDED IN
  Write down SQL code that carries out the same operation as the following Relational Algebra Expressions:
  1. s_{Address='Clonmel'}(Cars).
  2. p_RegNum,Owner s_{Address='Clonmel'}(Cars).
  3. s_{Officer.Surname='Burke' OR Officer.Firstname='John'} p _{Officer.Surname,Officer.Firstname,Cars.Owner}(Cars X Officer).