Relationships are a simple way of connecting to tables that are associated with each other in some way, i.e. a field which is identical in both tables. Having a field in one table which is identical in another is called having a ‘foreign key’ we use a foreign key so that one field can be present in multiple tables at the same time (shown via small asterisk next to the field) NOTE* The foreign key has to be the primary key of one table and simply appear within another table. In every table there is one field which is the most important field in the table; this is called the ‘primary key’. This is normally shown via a small gold key at the side of the table when it is in design view.
So we now know what a foreign key and primary key are, but how do you link tables together using these keys? Well I will show you. The process of linking two or more tables together is easy, when you have finished typing in all of the fields and information that you want within the tables that you are going to link together, you then click on the database tools option and then click relationships, as shown below:
There are many types of relationships when involving databases, however there is three that are most commonly used, These have been listed below with examples:
One instance of an entity is related to at most one instance of another entity.
For instance A, there are one or more instances of entity B or none at all, whereas for an instance of entity B, there is only one instance of entity A.
For an instance of entity A, there are zero, one, or more instances of the associated entity B; and for an instance of entity B there are zero, one, or more instances of entity A.
• Ref: From Week 20 lecture – Database development. Yang He, Accessed 20/03/11