[an error occurred while processing this directive] [an error occurred while processing this directive]       Table Relationships
Rice University
COMP 200
Elements of Computer Science
Fall 2005
Relationships between Tables

To learn how to design a relational database we will work on a specific problem of building a database for a football league.  Below is a description of all the data we need to keep track of.

A fictitious football league (FFL) has a few teams.  The league hopes to expand and be able to add more teams in the future.  Each team has a unique name, a coach and a phone number to contact the team and the coach.  A team has many players.  A player plays for one team and has a name and an age.  In the game of football, there are many player positions, each of which has a  unique name and a specific description of what the position is.  A player can play one or more positions.  For example, a player can play both as a running back and as a wide receiver.  Obviously any position can be played by many different players.  For example, many players can be a quarterback.

The best way to store the above data is to organize them into tables and store them in a relational database.

Tables

The first order of business is to decide what the principal tables are.  At first glance, we see that we need at least three tables whose designs are listed below.

The above table designs can be easily created using MS ACCESS.

In class:  hands-on exercise to build the above table in a database called football.  Be sure to remember where you save the database.

 

One-to-Many Relationship

"A teams has many players while a player can play only for one team."  This is an example of what is called "one-to-many" relationship.  In addition to storing this relationship in the database, we want to tell the database that the team name for each of the players in the Players table can only reference a valid team name in the Teams table.  This is an example of what is called "referential integrity."  This is an EXTREMELY IMPORTANT property in relational database design.  In MS ACCESS, this is best achieved using the lookup wizard in the design of the Players table.

In class: hands-on exercise on how to use the lookup wizard to create a one-to-many relationship with referential integrity between Teams and Players.

  1. Select the Players table and open its design by clicking the Design button.  You can also right-click on the Players table and select Design View.
  2. For the Data Type of the TeamName field, click on the list box and select Lookup Wizard.  The Lookup Wizard will start a dialog window.
  3. Select "I want the lookup column to lookup  the values in a table or query."  Click Next.
  4. Select Table Teams.  Click Next.
  5. Select the TeamName field on the left window pane and click the arrow (>) button to add TeamName to the right window pane.  Click Next three times and click Finish.  The Lookup Wizard will ask you to save the design.  Click Yes.
  6. You have just created a one-to-many relationship between Teams and Players: each team in the Teams table can have many players in the Players table, while each player in the Players table can only reference one record in the Teams table.  The Teams table is called the main table, while the Players table is called the related table.
  7. To enforce referential integrity, we use the Relationships diagram.  Open the Relationship diagram window using the menu Tools/Relationships.
  8. Point at an empty area in the Relationship diagram and click the right mouse button. 
  9. Right click on the line connecting Teams and Players. 

IMPORTANT: In order to establish a one-to-many relationship between a main table and a related table, the main table must have a key field and the related must have a foreign key that matches (i.e. has the same type) with the main table's key.

Many-to-Many Relationship

In class hands-on exercise.

"A player can play one or more positions, while any position can be played by many different players."  This is an example of what is called many-to-many relationship.  To express this relationship in a relational database, we need an auxiliary table, say PlayersPositions that serves as a "hub" to which we connect the table Players and the table Positions.  First, we need to modify the design of Players by adding a key to it.

  1. Open the design of Players and insert a row at the top of the table design in order to add a key field.  In practice, since the order of each data field is immaterial, we can simply append a new field to the existing fields in the design table.
  2. New the key field PlayerID.  Leave its type as Text.
  3. Create a new table called PlayersPositions according to the following design.
     
    Field Name Data Type Description
    PlayerID Use Lookup Wizard Foreign Key from Players
    PostionCode Use Lookup Wizard Foreign key from Positions
    Description Text  

     

  4. Use the Lookup Wizard to connect PlayerID of this table to the PlayerID of the Players table.
  5. Use the Lookup Wizard to connect PositionCode of this table to the PositionCode of the Positions table.
  6. In the Relationships window, add the table PlayersPositions.  There should be a line connecting Players to PlayersPositions via the key field PlayerID of Players and a line connecting Positions via the key field PositionConde.
  7. Edit these links to make them enforce referential integrity as in step 9 of the One-to-Many relationship.

In the end, we see that a many-to-many relationship between two tables consists of a pair of one-to-many relationships between each of the tables and an additional intermediary table.

Entering Data

Now that we have the database design in place, we can start entering data.  Since in a one-to-many relationship, the related table can only reference a key field in the main table, we must enter the data in the main table BEFORE entering the related data in the related table.  In the above example, we must enter data in the following order:

  1. Data for the Teams table
  2. Data for the Players table: here you can enter the TeamName of each player by selecting the desired team name in the TeamName list box.
  3. Data for the Positions table: since the Positions table is independent of the Teams  and Players tables, you can in fact enter in the Positions table before Teams and/or Players.
  4. Data for the PlayersPositions table: here you can enter the PlayerID and the PositionCode by selecting the desired player's id and position code in the PlayerID and PositionCode list boxes respectively.

Click here to download the completely designed football database

Exercises:

  1. Design a relational database to model the following  relationships: a book has one or more authors and an author can write many books.  A book has an ISBN number, a title and a price.  An author has a name and a contact phone number.  Format the phone number in the form (area code) 3 digits - 4 digits.  Enter some sample data to illustrate the relationships.
  2. Give an example of a one-to-many relationship and design a database for it.
  3. Give an example of a many-to-many relationship and design a database for it.

© Dung X. Nguyen

Last revised 12/02/2005 10:02 AM

Maintained by the professor; see contact information on the course home page