Establishing Relationships

From NeoWiki

(Difference between revisions)
Jump to: navigation, search
Revision as of 00:23, 12 September 2007 (edit)
Lorinda (Talk | contribs)
(Creating the Relationships - fix typo)
← Previous diff
Revision as of 15:43, 12 September 2007 (edit) (undo)
Lorinda (Talk | contribs)
(Creating the Relationships - add section on unique constraints, etc.)
Next diff →
Line 6: Line 6:
Relationships can be one-to-one or one-to-many. In a one-to-one relationship, one record in one table relates to one and only one record in another table. In a one-to-many relationship, one record in one table relates to more than one record in another table. In our database, the relationship between Field Trips and Sightings is one to many; on any given field trip, the birder would (hopefully!) see many birds. Note that at least one of the fields you are relating must be a primary key field. Relationships can be one-to-one or one-to-many. In a one-to-one relationship, one record in one table relates to one and only one record in another table. In a one-to-many relationship, one record in one table relates to more than one record in another table. In our database, the relationship between Field Trips and Sightings is one to many; on any given field trip, the birder would (hopefully!) see many birds. Note that at least one of the fields you are relating must be a primary key field.
-==Creating the Relationships==+==Creating Relationships When One Field is a Primary Key==
#With the database open, go to the {{menu|Tools}} menu and choose {{menu|Relationships}} #With the database open, go to the {{menu|Tools}} menu and choose {{menu|Relationships}}
#In the floating window, click on the '''Field Trips''' and then click the {{button|Add}} button. A small window named {{window|Field Trips}} appears in the larger {{window|Relation Design}} window. #In the floating window, click on the '''Field Trips''' and then click the {{button|Add}} button. A small window named {{window|Field Trips}} appears in the larger {{window|Relation Design}} window.
Line 12: Line 12:
#close the floating window #close the floating window
#click on the the {{prefName|ID}} field in the {{Window|Field Trips}} sub-window; drag from there to the {{prefName|Field Trips ID}} field in the {{Window|Sightings}} sub-window. A line appears connecting these two fields, with a '''1''' the {{window|Field Trips}} end and an '''n''' next to the {{Window|Sightings}} window. This identifies a one-to-many relationship. #click on the the {{prefName|ID}} field in the {{Window|Field Trips}} sub-window; drag from there to the {{prefName|Field Trips ID}} field in the {{Window|Sightings}} sub-window. A line appears connecting these two fields, with a '''1''' the {{window|Field Trips}} end and an '''n''' next to the {{Window|Sightings}} window. This identifies a one-to-many relationship.
 +#click on the hard drive symbol in the toolbar to save the changes you have made, and close the window.
 +
 +==Creating Relationships When Neither Field is a Primary Key==
 +In Base, whenever you create a relationship between tables, at least one of the fields being used must be unique. That is, in at least one of the tables, no two records can have identical entries in the field being used to create the relationship. This is automatically the case if one of the fields in a Primary Key. But if you want to relate tables using something other than the primary key, you must first induce a unique constraint on one of the fields you want to use.
 +
 +In the case of our birding database, data entry will be much easier if we do this with two of our relationships, specifically for the relationships between:
 +:The '''AOU Birdlist''' and '''Sightings''' tables ('''EnglishName''' relates to '''Bird Name''')
 +:The '''Locations''' and '''Sightings''' tables ('''LocationName''' relates to '''Location''')
 +The example below follows the procedure for relating the '''AOU Birdlist''' and '''Sightings''' tables. The same procedure will need to be used to link the '''Locations''' and '''Sightings''' tables.
 +
 +===Creating a Unique Constraint===
 +#Make a note of the exact field and table names involved. In this case '''AOU Birdlist''' and '''EnglishName'''
 +:''Note: There must not be any spaces in the field name. While Base will not allow you to create field names with spaces in them when using the wizard or Design view, Base will retain spaces in field names if you import a spreadsheet into Base as a table. When importing the AOU Birdlist, the field name was '''English Name''' (with a space). I had to edit the table and delete the space before the SQL command would work.''
 +#While the database open to the main window (the one with Tables, Queries, Forms, and Reports in the left hand column), go to the {{Menu|Tools}} menu and choose {{Menu|SQL...}}.
 +#Enter the following statement into the top portion of the {{Window|Execute SQL Statement}} window:
 +:<pre>alter table "AOU Birdlist" add constraint "uq_EngName" unique ("EnglishName" )</pre>
 +::uq_EngName is the name of the constraint that is created by this command.
 +#Click {{Button|Execute}}
 +:''Note: The {{Section|Status}}'' section of the window will tell you if the command was succesfully executed or if there was a problem.
 +#Close the {{Window|Execute SQL Statement}} window
 +
 +===Creating the Relationship===
 +#With the database open, go to the {{menu|Tools}} menu and choose {{menu|Relationships}}
 +#Click on the icon that looks like a block of blue cells with a green plus in the corner.
 +#In the {{Window|Add Tables}} window that appears, click on '''AOU Birdlist'''
 +:'''Note: If you have not created any relationships yet, the {{Window|Add Tables}} window will open automatically.
 +#If the '''Sightings''' Table does not appear in the relationship "map," add it as well.
 +#close the floating window
 +#click on the the {{prefName|EnglishNAme}} field in the {{Window|AOU Birdlist} sub-window; drag from there to the {{prefName|BirdName}} field in the {{Window|Sightings}} sub-window. A line appears connecting these two fields.
#click on the hard drive symbol in the toolbar to save the changes you have made, and close the window. #click on the hard drive symbol in the toolbar to save the changes you have made, and close the window.

Revision as of 15:43, 12 September 2007

This article is part of the To Base and Back Again tutorial series.

Part of the power of Base is that it is a relational Database, allowing you to relate different tables of data. In order to make use of this power, we need establish the relationships between the various tables. In our case, not all the tables need to be related. The tables we "termed" covenience tables in the first article of this series, do not need to be related by an official process, as they will be used primarily for combo boxes and/or list boxes. The essential tables do need to be related to each other.

Contents

Types of Relationships

Relationships can be one-to-one or one-to-many. In a one-to-one relationship, one record in one table relates to one and only one record in another table. In a one-to-many relationship, one record in one table relates to more than one record in another table. In our database, the relationship between Field Trips and Sightings is one to many; on any given field trip, the birder would (hopefully!) see many birds. Note that at least one of the fields you are relating must be a primary key field.

Creating Relationships When One Field is a Primary Key

  1. With the database open, go to the Tools menu and choose Relationships
  2. In the floating window, click on the Field Trips and then click the Add button. A small window named Field Trips appears in the larger Relation Design window.
  3. repeat with the Sightings
  4. close the floating window
  5. click on the the ID field in the Field Trips sub-window; drag from there to the Field Trips ID field in the Sightings sub-window. A line appears connecting these two fields, with a 1 the Field Trips end and an n next to the Sightings window. This identifies a one-to-many relationship.
  6. click on the hard drive symbol in the toolbar to save the changes you have made, and close the window.

Creating Relationships When Neither Field is a Primary Key

In Base, whenever you create a relationship between tables, at least one of the fields being used must be unique. That is, in at least one of the tables, no two records can have identical entries in the field being used to create the relationship. This is automatically the case if one of the fields in a Primary Key. But if you want to relate tables using something other than the primary key, you must first induce a unique constraint on one of the fields you want to use.

In the case of our birding database, data entry will be much easier if we do this with two of our relationships, specifically for the relationships between:

The AOU Birdlist and Sightings tables (EnglishName relates to Bird Name)
The Locations and Sightings tables (LocationName relates to Location)

The example below follows the procedure for relating the AOU Birdlist and Sightings tables. The same procedure will need to be used to link the Locations and Sightings tables.

Creating a Unique Constraint

  1. Make a note of the exact field and table names involved. In this case AOU Birdlist and EnglishName
Note: There must not be any spaces in the field name. While Base will not allow you to create field names with spaces in them when using the wizard or Design view, Base will retain spaces in field names if you import a spreadsheet into Base as a table. When importing the AOU Birdlist, the field name was English Name (with a space). I had to edit the table and delete the space before the SQL command would work.
  1. While the database open to the main window (the one with Tables, Queries, Forms, and Reports in the left hand column), go to the Tools menu and choose SQL....
  2. Enter the following statement into the top portion of the Execute SQL Statement window:
alter table "AOU Birdlist" add constraint "uq_EngName" unique ("EnglishName" )
uq_EngName is the name of the constraint that is created by this command.
  1. Click Execute
Note: The Status section of the window will tell you if the command was succesfully executed or if there was a problem.
  1. Close the Execute SQL Statement window

Creating the Relationship

  1. With the database open, go to the Tools menu and choose Relationships
  2. Click on the icon that looks like a block of blue cells with a green plus in the corner.
  3. In the Add Tables window that appears, click on AOU Birdlist
Note: If you have not created any relationships yet, the Add Tables window will open automatically.
  1. If the Sightings Table does not appear in the relationship "map," add it as well.
  2. close the floating window
  3. click on the the EnglishNAme field in the {{Window|AOU Birdlist} sub-window; drag from there to the BirdName field in the Sightings sub-window. A line appears connecting these two fields.
  4. click on the hard drive symbol in the toolbar to save the changes you have made, and close the window.
Personal tools