Reldonor presentation speaker notes
The donor, donation and drive tables are examples used through all of my notes.
The donor table is used to keep name and address type information on donors to my charities.
The donation table keeps tracks of contributions made to my charaties. It keeps the idno of the donor, the drive the donation was made to, the date of the contribution and the contribution amount.
Note that the records shown here may differ from the records shown in some of my notes. The data has changed over time.
This table is about the drives. It has a drive number, a drive name, the chairperson of the drive and total donation information.
Again note that the information in this table has changed over time and may not look like the table I use in my notes.
If I know the idno, I can get information from both the donor table and the donation table.
If I know the driveno, I can get information from both the donation table and the drive table.
There is no link between the donor table and the drive table meaning they have no fields in common. If I want to know the drive name of the charities that Stephen Daniels gave to, I need to go to the donation table, find all donations that were from donor 11111 and use the driveno on these rows/records to go to the drive table and find the drivename.
Each of these relationships will be illustrated on the following slides.
In this example, each donor can only give one donation and each donation can only have one donor. Therefore we have a one to one relationship.
One to many means one on one side and many allowed on the other. In this example a donor can give many donations. One donor many donations. However looking at the reverse each donation can only be given by one donor. This makes it a one to many relationship.
Note in a one to many relationship you can allow none or you can require at least one depending on the data you are organizing.
NOTE: If each donor could give multiple donations and in addition each donation could have multiple donors, then you have a many to many relationship.
In a many to many relationship a bridge table is constructed. In this example, the bridge table is called movie_star. The bridge file holds the movie number and the star number. In the example, movie 1111 has 3 stars, therefore there are three records for movie 1111 each with a different star number.
The primary key should be something that defines a row/record for identification purposes. Frequently identification numbers of some kind are used as primary keys.
The primary key must define a record/row uniquely. In the one to many relationship, this frequently means two or more fields/columns must be combined together to make the primary key.
We know that drive number makes sense as the primary key because if we were to add another drive we know that it would be assigned its on drive number.
Note that drive name could also be said to uniquely define the row. However, it makes sense to use drive number because it is a short field and easy to type correctly. This field will be used in another table and you want to limit the possibility for mistakes.
A foreign key is a field on a table that links into the primary key or part of the primary key on another table.
If I am using the donation table and I want to know the drive name, I will use the drive number on the donation table to link into the drive number on the drive table. Then I can take the drive name from that record/row.
In the SELECT, the only fields that need to be named with both table and drive are the fields that appear in both tables. Driveno is the only field that appears in both tables so it is selected with both table name and field/column name. NOTE the format: table name.column name.
The FROM names the tables that are being used.
The WHERE establishes the link. In this example the link is based on a match between the donation.driveno and the drive.driveno. NOTE that both have table name.column name because driveno appears in both tables.
Once the link is established the data is retrieved from both tables.
Developers tend to eliminate some of these names because they aren't needed even though they make great documentation.
The driveno on the donation table is a foreign key that is being liked to the primary driveno key on the drive table. Note that at this time we are talking about the concept of primary and foreign keys only. We have not actually established keys on the Oracle table.
NOTE that I did not need to use the alias with anything except driveno because driveno is the only thing that appears in both the donation table and the drive table.
In doing my tables NOTE that I called fields by the same name. There is a driveno on the donation table and a driveno on the drive table. This is not required. I do it to help me keep track of what I have called things.
Note that the from established the tables to be used, the WHERE establishes the link and the SELECT lists the data that is to be retrieved based on these links.
When using three tables, I establish the main table that I want to process - in this case the donation table. Then I establish the links to the two tables that I am going to for information. It is important to establish links to ALL tables being used in the WHERE clause.
Another name for equijoins is simple joins or inner joins.
An equijoin means that the value of the two tables being compared must be equal.
With a non-equijoin there is not a direct join between two tables. As you can see, grade gives the grade number and schools gives a range. I am checking to what range the gradelevel on grade falls in.
Note that since there have been no contributions to drive 400 it does not appear on the donation table. It does appear on the drive table. The outer join allows me to make a connection to all driveno even when there is not a match.
Without the (+) you do not have a outer join, you have an equijoin.
Note that fst.manager and snd.idno are the same. This is the link so they should be. I listed both to clarify that this had happened.
This combines a self join and a outer join because Linda Jones does not have a manager to match against.