Relational Donor

Speaker Notes:

Slide 1:

This presentation is about a relational database that consists of three tables: donor, contribu and drive. The three tables are in third normal form and can be used together in queries etc.

The database was done using Access 97.

Slide 2:

This is the donor table. Note that the Idno is the key. Other fields are Name, StAdr, City, State, Zip, YrFirst and Contact. All of this data relates directly to the Idno key.

Please note that on the Internet I have put a version of this database created in Access 2000. I called the table Donor2000 and I put a D in front of each of the field names. DIdno, DName etc. Just to differentiate!

Slide 3:

The primary key should be something that uniquely defines a record for identification purposes. Frequently it is some kind of identification number.

Slide 4:

Since one donor can make multiple donations the Idno can not be used as a key.

Since one donor can make multiple donations to the same Drive the Idno plus the DriveNo cannot be used as a key. (Note that the DriveNo alone cannot be a key because multiple donations are made to the same drive).

To make a unique key I decided that the combination of Idno + DriveNo + DateCont made a unique key. Once this decision is made, it is clear that a person cannot give to the same drive twice on the same date. This seems reasonable. If it is not, then we have to reconsider the construction of the primary key.

Remember that the data on the table cannot relate to an individual part of the key, it must relate to the whole key. In this case the only non key field is AmtCont and it relates directly to the combined key. The amount was contributed by a person, to a drive on a particular date.

Please note that when I changed this to the 2000 version I called the table Donation2000 instead of Contribu.

Slide 5:

Frequently multiple fields must be combined to make a unique primary key.

Slide 6:

This is the Drive table and the primary key is DriveNo since there will be only 1 record per DriveNo in this table. The data on the table all relate directly to DriveNo.

Slide 7:

DriveNo uniquely defines a record. Drive name does as well and maybe even Drive Chair. However, usually you pick an identification number as opposed to words or names for the primary key - they are shorter and are less likely to be entered differently. For example Children's Drive and Childrens Drive are not the same and William Souza and Bill Souza are not the same.

Slide 8:

If I know the idno, I can get information from both the donor table and the contribution table (contribu).

If I know the driveno, I can get information from both the contribution table (contribu) 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 John Doe gave to, I need to go to the contribu table, find all donations/contributions that were from donor 11111 and use the driveno on these rows/records to go to the drive table and find the drivename.

Slide 9:

Each of these relationships will be illustrated on the following slides.

Slide 10:

In this example each donor has one contribution and each contribution is from one donor. Therefore we have a one to one relationship.

Slide 11:

Each donor can give one or multiple contributions/ donations where each donation is by a single donor - this creates a one to many relationship. On the donor side there is one and on the contribution side there is none or one or many.

Note that if each contribution could have have been given by multiple donors you would have a many to many relationship.

Slide 12:

In a many to many relationship, a bridge table is used. In this example the bridge table is called MovStar and it holds the movieno and the starno - these numbers comprise the primary key. Note that you can also carry data related to both parts of the primary key in this table. For example, you could carry the salary the star was paid for the movie. A bridge file is mainly the two keys to the two tables in the many to many relationship. But, the concept can be expanded to create a table that has a bridge relationship and includes data - again, the data must be related to both elements of the primary key (movieno and starno).

Note that there are two stars for movie 111, therefore there are two records each with a different star name. There is only one star for movie 222 so there is only one record in the bridge file. Movie 333 has four stars so there are four records in the bridge file, each with a different star number.

Note that the key must be made up of both movieno and starno since there are duplicates of movieno and duplicates of starno.

Slide 13:

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 contribution table and I want to know the drive name, I will use the drive number on the contribution table to link into the drive number on the drive table. Then I can take the drive name from that record/row.

Slide 14:

Select tools and relationships to see the relationships that have been established for a database.

Slide 15:

This shows a query with all three tables being used. Note the fields/columns that come from each table is shown below the field name on the query.

Slide 16:

We will now run through the steps of establishing relationships using the MovieStar database seen previously.

Slide 17:

I clicked on okay to accept this and repeated the drag of starno on Star to starno on MovStar.

It should be notes that Access will try to set up the relationships for you when you do a query if you use matching names.

Slide 18:

You can go back into the relationship table, click on the line that shows the relationship, press delete and remove the relationship. You will be warned about continuing. To rebuild the relationship, you repeat the click and drag illustrated on the previous slides.