Lab 11 Microsoft Access

Linking tables (creating relationships)

Right now, the database consists of three small tables: tablePerformers, tableVenues and tableEvents.

If you think about it, it makes sense for these three types of information to be separated.

Performers have certain characteristics like their name, the type of music they perform, the members of the group, the city they're from, the songs they perform, etc.

Venues have certain characteristics like their address, the number of seats, the cost of rental.

Events have certain characteristics like the date and the price. They also have characteristics that overlap with the other types of information - they have performers and they have a venue.

In the next series of steps, you will be linking these tables to create relationships between them:

RELATIONAL DATABASES

When you link tables in a database to create relationships between them, you have a relational database. Relational databases can be very efficient ways to store and retrieve information.

Again, feel free to try to either try to figure out how to do this (how to create relationships between the tables) on your own, or follow the steps below then jump to step 8.

1. Close all of your tables by clicking on the x to the right of the tabs:

2. Click on the Database Tools tab and click on the Relationships icon:

3. You'll get a popup box titled Show Table with a list of your tables. Select a table name and click the Add button (if you click on one twice accidentally, it will be added twice, but you can select it and delete it later). Do this for all 3 tables.

4. Close the Show Table popup

Next, you're going to create two relationships:

One between the PerformerID field in tableEvents and the ID field in tablePerformers
One between the VenueID field in tableEvents and the ID field in tableVenues

5. Click on PerformerID, hold the mouse key and drag it to the ID field in tablePerformers

You’ll get this:

6. Click on the Enforce Referential Integrity box, and then click the Create button. You should see this:

The line shows that there is a relationship between these two fields (and therefore between these two tables). The infinity sign shows that there can be many records in tableEvents that correspond to a single record in tablePerformers (in other words, a performer may perform at many events). This is called a many-to-one relationship.

RELATIONSHIP TYPES

many-to-one relationship
Another example would be the relationship between individuals and their place of birth. Every individual has one place of birth, but a given place can be the birthplace of many individuals:



one-to-one relationship
The relationship between Presidents and Countries would generally be a one-to-one relationship because each Presidents presides over one country and each country has, at most, one President:



many-to-many relationship
The relationship between a group of authors and a collection of books is a many-to-many relationship since one author can write more than one book and one book can have more than one author. To create a many-to-many relationship in a database, you put a third table in between:



7. Now click VenueID and drag it to ID in tableVenues. Again, check the Enforce Referential Integrity box and click the Create button to get this:

Now that the relationships are in place, we'll fill in tableEvents. There will be three events:
1. A concert on May 24, 2008 at the ShowBox Theater featuring Ladytron
2. A concert on May 29, 2008 at the Paramount Theater featuring Aventura
3. A concert on June 8, 2008 at the Paramount Theater featuring Return to Forever

8. Open tableEvents and start by typing in the three concert dates:

Next you’ll fill in PerformerID and VenueID values.

9. The performer at the May 24, 2008 concert is Ladytron, so open tablePerformer and find the ID is for Ladytron.

(in this screenshot, the ID for Ladytron is 6 (but yours might be different):

10. Return to tableEvents and enter that number in the PerformerID field.

11. Follow the same steps to fill in the values for the other PerformerID fields, then open tableVenue to find the correct values for the VenueID fields and fill those in as well.

Go to the Catalyst quiz and answer question #8.

Now our database is fully functional and we set up to pull information from it