Transcript Relational example using donor, contrib and drive tables

Relational example using donor,
contrib and drive tables
Please use speaker notes for
additional information!
Access - Donor
table
The primary key is
the Idno which is a
text field of 5
characters. Notice
under Indexed it was
set to Yes with No
Duplicates.
Keys - primary
PRIMARY KEY
Each table should have a primary key that identifies one and only
one record. Sometimes the primary key is one column/field,
sometimes it is a combination of column/fields. In this example it
is one column/field, the Idno.
In the donor table, it can be seen that there is a different
Idno for each record/row. In other words, the Idno
uniquely identifies a record/row.
Access - contribu table
The primary key is made
up of Idno, DriveNo,
DateCont. Note under
Indexes these are named
IdDr.
Keys - primary
In this example, the primary key is not
as easy to determine. Clearly the Idno
does not work as the primary key
because there are multiple of the same
Idno.
This means Idno needs to be combined
with something else. If we combine it
with DriveNo it will work if there is no
record/row where the Idno plus the
DriveNo are the same.
In this example it will not work because
the same person has contributed to the
same drive multiple times to the same
drive. If we combine Idno plus DriveNo
plus DateCont as the primary key we
will be okay as long as we decide that
the same donor cannot contribute to the
same drive twiceon the same date. That
is reasonable so we will go with the
combination of those three fields to
make the primary key.
Access - drive table
DriveNo is the
primary key. It is
indexed with no
duplicates.
Keys - primary
In this example, the primary key is DRIVENO.
DRIVENO uniquely defines each record/row in
the table. Another drive with the same drive
number would not make sense in this table.
The relationship or link between the
donor table and the donation table is
based on idno. In other words, the idno
appears in both tables.
The relationship or link between the
contribution table (contribu) and the drive
table is based on driveno. In other words, the
driveno appears in both tables.
NOTE: There is no
direct link between
the donor table and
the drive table.
Types of relationships
In a relational database there are the following
kinds of relationships:
•One to One
•One to Many
•Many to Many
One to One
relationship
One to One Relationship:
If the donation table only allowed one donation for
every donor then we would have a one to one
relationship.
One to Many
relationship
One to Many Relationship:
In this example, one donor can give multiple donations but each
donation only has one donor.
Many to many
relationship
Many to Many Relationship:
In this example, each movie can have many
stars and each star can have many movies,
therefore this is a many to many relationship.
Bridge table - used
with many to many.
Keys - foreign
The DriveNO on the
contribution table is called a
foreign key because it links
into the primary key on the
drive table.
Relationships
As shown, DriveNo from Drive links to DriveNo from Contribu
and Idno from Contribu links to Idno from Donor.
Relationships
I clicked on Tools and
then relationships. This
is what I was given.
I then added all three tables that
are shown before selecting
close.
Relationships
I clicked on movieno under
Movie and dragged it over
to movieno under MovStar.
The following screen
appeared.
Relationships
This shows the relationship between the three tables.
There is a link between movieno on Movie and movieno
on MovStar and another link between starno on Star and
starno on MovStar.