CT230 Lab 2

http://geminga.it.nuigalway.ie/~gettrick/courses/CT230/labs/lab2.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

In this lab you will define Relationships among tables and look at integrity constraints.

• Start by opening the database lab1.mdb you worked on last week. Copy it to a new database lab2.mdb (to do this just choose Save As from the File menu). Close ACCESS and reopen with lab2.mdb. Your single table called Customers should have both degree and cardinality seven, i.e. 7 rows and 7 columns, with schema
  Customers(ID, FIRSTNAME, LASTNAME, ADDRESS, CITY, STATE, ZIPCODE)

  Using Design View, change the Data Type of Customers.ID to Text.

• Now create a second table acccording to the schema
  Sales(Product_Code, Customer_ID, Amount)

  In the above schema, attributes of the primary key are underlined. The second schema Sales has a composite primary key composed of two attributes (fields) Product_Code and Customer_ID. Use design view to design the Sales table. Product_Code and Customer_ID should have data type Text, while Amount should have data type Currency.

• To set the composite primary key for the Sales table, point the cursor on the (grey) tab just to the left of the first row and drag down over the first two rows, highlighting them. Then, while keeping the cursor over these rows, press the control Ctrl key and the right mouse button at the same time. From the resulting menu select Primary Key. Both rows should now be highlighted with a small key icon.

• You need to set up a link (Relationship) between the Customer ID in the Customers table and in the Sales table: Specifically, Customers.ID which is a primary key in the Customers table is a foreign key Sales.Customer_ID in the Sales table. Select Relationships from the Tools menu in the top title bar. Highlight the tables you want to create relationships between, and click Add to add them to the Relationship window (in this case the two tables Customers and Sales). Click and drag the primary key from Customers Customers.ID and drop it on the foreign key in Sales Sales.Customer_ID. You will be presented with a box detailing properties of the Relationship: for the moment the only important one is Enforce Referential Integrity. Click this box and then click OK. In the Relationship window a link will now be displayed as a line joining the relevant keys of the two tables.

• To edit the Relationship, double click on the line linking the two tables in the Relationship window. To delete the relationship, highlight it by clicking on it once, then choose Delete from the Edit menu in the top title bar. Now experiment with the following:
  1. In the Sales.Customer_ID field of the Sales Table add a sequence of characters which does not occur as a value of Customers.ID in the Customers Table. For Sales.Product_Code write a random sequence of letters. Try to save this. Why do you get an error?
  2. In the Sales.Customer_ID field of the Sales Table add a sequence of characters which does occur as a value of Customers.ID in the Customers Table. Leave the field Sales.Product_Code empty. Try to save this. Why do you get an error?
  3. Return to the Relationships Window, double click on the relationship linking Customers and Sales Tables. This time do not enforce referential integrity, make sure the box Enforce Referential Integrity is not ticked, and click OK. Now repeat 1. above. Can you save this time?

• Now Create a new database parking.mdb with schema
  Cars(RegNum, Owner, Address)
  Officer(RegNum, Surname, Firstname)
  Offence(Code, Date, Officer, CarReg)

  All attributes should have Data Type Text except for Officer.RegNum, Offence.Officer, Offence.Code which should have Data Type Number and Offence.Date which should have Data Type Date/Time. Create Relationships defining the Primary Key Officer.RegNum as the Foreign Key Offence.Officer and the Primary Key Cars.RegNum as the Foriegn Key Offence.CarReg. Populate the Database with the data given below. Edit the data if necessary to enforce Entity Integrity and Referential Integrity on all Relationships.

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

  Officer	RegNum	Surname	Firstname
  	567	White	John
  	NULL	Burke	NULL

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

• TO BE HANDED IN
  
  1. Print out each of your new (edited) tables from ACCESS
  2. For the Offence Relation and the data given do the pair of attributes Officer,CarReg form a valid candidate key?
  3. Is Offence.Officer a Superkey in the data given above?
  4. If we choose Cars.Owner as a Primary Key is Entity Integrity violated for the Data given?