Relationships and Advanced Query Concepts Using Multiple

TablesPlease use speaker notes for

additional information!

RelationshipsRelationships

To ensure the integrity of the data, the developer has two powerful tools:

1) Entity integrity which means that the primary key must be unique and that can be no null values in the key.

2) Referential integrity which means any foreign keys established must link to a valid primary key. Referential integrity is an option that the developer can choose to enforce or ignore. Lets say you have an employee table and a dept table. The employee table contains a foreign key of dept which links to the dept table where dept is the primary key. You cannot add an employee to the employee table and assign them to a dept that does not exist in the dept table. If you want a new dept, you must go to the dept table and create the new dept. Then you can add an employee and assign to that dept. To put this into rule form: the primary key must exist in the parent table before you can use a foreign key of that value in the child table. Also you cannot delete a row/record from the parent table that will leave orphans in the child table. Looking at my example above, once you have used the dept in the employee table, you cannot delete the dept from the dept table without first deleting all employees assigned to that dept from the employee table.

In this example, I first clicked the relationship button in the toolbar and then in the show tables, I selected the 3 tables shown.

I dragged the DDriveNo from the Drive2000 table to the Donation2000 table to establish the relationship and the edit relationship box was shown. If I want to enforce referential integrity, I will click the box.

Referential Integrity

Referential Integrity When the relationships button is clicked, relationships

appears on the tool bar. You can use this to show or edit etc.

Select tools and then select relationships to get to this screen.

Referential Integrity

Referential Integrity

If you click on Join Type, the options shown below are given. This means you can do an equi-join or inner join which joins only those records from two tables that are equal or you can allow records from one table to match another as explained.

RelationshipRelationship

I have now established a relationship between the drive number on the Drive2000 table and the drive number on the Donation2000 table.

The one side of the one to many relationship is shown with a 1.

The many side of the one to many relationship is shown with a 8

This slide shows the additional relationship between idno on the donor2000 table and idno on the Donation2000 table.

Again notice the 1 to many relationship.

IndexIndex

Especially for large databases it is beneficial to index the foreign key.

In this example, DDriveNo on the Donation 2000 table has been indexed. Because there are many occurrences of DDriveNo it is important to use Yes(Duplicates OK).

Donor2000Donor2000

Shows the entries in Donation2000 that relate to the record in Donor2000.

Drive2000Drive2000

Shows the related records in Donation2000.

DonationDonation

Because I established referential integrity I cannot add a record on the child side (Donation2000) with a drive number that does not exist on the parent side (Drive2000).

In this example, I tried to add a record with drive 127. Drive 127 does not exist in Drive2000 son an error message is displayed and the record is not allowed.

3 table query3 table query

SQLSQL

SELECT Donor2000.DIdno, Donor2000.DName, Donation2000.DDriveNo, Donation2000.DDateCont, Donation2000.DAmtCont, Drive2000.DDriveNameFROM Drive2000 INNER JOIN (Donor2000 INNER JOIN Donation2000 ON Donor2000.DIdno = Donation2000.DIdno) ON Drive2000.DDriveNo = Donation2000.DDriveNo;

SELECT Donor2000.DIdno, DName, Donation2000.DDriveNo, DDateCont, DAmtCont, DDriveNameFROM Drive2000, Donation2000, Donor2000WHERE Donor2000.DIdno = Donation2000.DIdno AND Drive2000.DDriveNo = Donation2000.DDriveNo;

The first SQL was created by Access, the second is my code.

Three tables

donation >= 500

Three tables

donation >= 500

This query takes data from three tables. The table is in ascending order by Didno and only those records with DAmtCont >= 500 are shown.

SELECT Donation2000.DIdno, Donor2000.DName, Donation2000.DDriveNo, Drive2000.DDriveName, Donation2000.DAmtContFROM Drive2000 INNER JOIN (Donor2000 INNER JOIN Donation2000 ON Donor2000.DIdno = Donation2000.DIdno) ON Drive2000.DDriveNo = Donation2000.DDriveNoWHERE (((Donation2000.DAmtCont)>=500))ORDER BY Donation2000.DIdno;

SELECT Donation2000.DIdno, DName, Donation2000.DDriveNo, DDriveName, DAmtContFROM Donation2000, Drive2000, Donor2000WHERE Donation2000.DIdno=Donor2000.DIdno AND Donation2000.DDriveNo=Drive2000.DDriveNoAND DAmtCont>=500ORDER BY Donation2000.DIdno;

SQL for three tables - donation >= 500SQL for three tables - donation >= 500

The SQL code above was generated by Access, the code below was written by me. The results are the same.

Parameter QueryParameter Query

I keyed in the >= and then square brackets surrounding the prompt for data. When you run, the prompt asks for the number to test.

SELECT Donor2000.DIdno, Donor2000.DName, Donation2000.DDriveNo, Drive2000.DDriveName, Donation2000.DAmtContFROM Drive2000 INNER JOIN (Donor2000 INNER JOIN Donation2000 ON Donor2000.DIdno = Donation2000.DIdno) ON Drive2000.DDriveNo = Donation2000.DDriveNoWHERE (((Donation2000.DAmtCont)>=[Enter number to test]));

SELECT Donor2000.DIdno, DName, Donation2000.DDriveNo,DDriveName, Donation2000.DAmtContFROM Donor2000, Donation2000, Drive2000WHERE Donor2000.DIdno = Donation2000.DIdno AND Drive2000.DDriveNo = Donation2000.DDriveNo AND DAmtCont>=[Enter number to test];

SQL from parameter querySQL from parameter query

The SQL at the top was generated by Access for the parameter query.

The SQL below is my code.

Parameter - betweenParameter - between

In this test you can see that Low amount was entered as 500. When prompted for High amount, I entered 1000.

SELECT Donor2000.DIdno, Donor2000.DName, Donation2000.DIdno, Drive2000.DDriveName, Donation2000.DAmtContFROM Drive2000 INNER JOIN (Donor2000 INNER JOIN Donation2000 ON Donor2000.DIdno = Donation2000.DIdno) ON Drive2000.DDriveNo = Donation2000.DDriveNoWHERE (((Donation2000.DAmtCont) Between [Low amount] And [High amount]));

SELECT Donor2000.DIdno, DName, Donation2000.DIdno, DDriveName, DAmtContFROM Donor2000, Donation2000, Drive2000 WHERE Donor2000.DIdno = Donation2000.DIdno AND Drive2000.DDriveNo = Donation2000.DDriveNo ANDDAmtCont Between [Low amount] And [High amount];

Code above generated by Access.

Code below written by me.

SQL ParametersSQL Parameters

CalcCalc

SELECT Donor2000.DIdno, Donor2000.DName, Donation2000.DDriveNo, Drive2000.DDriveName, Donation2000.DAmtCont, [Donation2000]![DAmtCont]*1.1 AS GoalFROM Drive2000 INNER JOIN (Donor2000 INNER JOIN Donation2000 ON Donor2000.DIdno = Donation2000.DIdno) ON Drive2000.DDriveNo = Donation2000.DDriveNo;

CalculationCalculation

SELECT Donor2000.DIdno, DName, Donation2000.DDriveNo, DDriveName, DAmtCont,DAmtCont*1.1 AS GoalFROM Donor2000, Donation2000, Drive2000WHERE Donor2000.DIdno = Donation2000.DIdno AND Drive2000.DDriveNo = Donation2000.DDriveNo;

Generated code above.

My code below.

SELECT TOP 25 PERCENT Donor2000.DIdno, Donor2000.DName, Donation2000.DDriveNo, Donation2000.DDateCont, Donation2000.DAmtCont, Drive2000.DDriveNameFROM Drive2000, Donation2000, Donor2000WHERE (((Donor2000.DIdno)=[Donation2000].[DIdno]) AND ((Drive2000.DDriveNo)=[Donation2000].[DDriveNo]));

High/Top ValuesHigh/Top Values

This code shows the use of the top or highest values button. Note that I selected 25% which now appears in the SQL code and that only the top 25% of the records are showing - there is a total of 10 records.

SELECT TOP 10 PERCENT Donor2000.DIdno, Donor2000.DName, Donation2000.DDriveNo, Donation2000.DDateCont, Donation2000.DAmtCont, Drive2000.DDriveNameFROM Drive2000, Donation2000, Donor2000WHERE (((Donor2000.DIdno)=[Donation2000].[DIdno]) AND ((Drive2000.DDriveNo)=[Donation2000].[DDriveNo]))ORDER BY Donation2000.DAmtCont DESC;

Top 10% contribution

Top 10% contribution

High values

High values

SELECT TOP 25 PERCENT Donor2000.DIdno, Donor2000.DName, Donation2000.DDriveNo, Donation2000.DDateCont, Donation2000.DAmtCont, Drive2000.DDriveNameFROM Drive2000, Donation2000, Donor2000WHERE (((Donor2000.DIdno)=[Donation2000].[DIdno]) AND ((Drive2000.DDriveNo)=[Donation2000].[DDriveNo]))ORDER BY Donation2000.DAmtCont;