Establishing Relationships

From NeoWiki

(Difference between revisions)
Jump to: navigation, search
Revision as of 14:46, 19 November 2007 (edit)
Valterb (Talk | contribs)
m (Creating a Unique Index - typo)
← Previous diff
Current revision (12:27, 29 March 2008) (edit) (undo)
Lorinda (Talk | contribs)
(Creating the Relationship - correct formatting of note)
 
(12 intermediate revisions not shown.)
Line 1: Line 1:
{{TBBA Header}} {{TBBA Header}}
-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.+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 to establish the relationships between the various tables.
==Types of Relationships== ==Types of Relationships==
Line 9: Line 9:
#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.
-#repeat with the '''Sightings''' +#repeat with '''Sightings'''
#close the floating window #close the floating window
-#click on the the {{prefName|FieldTrip_ID}} field in the {{Window|Field_Trips}} sub-window; drag from there to the {{prefName|FieldTrip_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|FieldTrip_ID}} field in the {{Window|Field_Trips}} sub-window; drag from there to the {{prefName|Sightings_FieldTrip_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.+#click on the disk 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== ==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 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 is 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: 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 '''AOU_Birdlist''' and '''Sightings''' tables ('''EnglishName''' relates to '''Bird_Name''')
-:The '''Locations''' and '''Field_Trips''' tables ('''LocationName''' relates to '''Field_Trip_Location''')+:The '''Locations''' and '''Field_Trips''' tables ('''Location_Name''' relates to '''Field_Trip_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 '''Field_Trips''' tables. 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 '''Field_Trips''' tables.
Line 27: Line 27:
#In the main database window, click on the {{prefName|Tables}} icon in the left hand column #In the main database window, click on the {{prefName|Tables}} icon in the left hand column
#Right-click or control-click on the {{prefName|AOU_Birdlist}} icon and choose {{Menu|Edit}} #Right-click or control-click on the {{prefName|AOU_Birdlist}} icon and choose {{Menu|Edit}}
-#Click in the cell with the '''English Name''' field name+#Click in the cell with the '''EnglishName''' field name
#Click on the {{Button|Index Design}} button on the toolbar. (It looks like a sheet of paper with a blue drafting triangle laying ontop of it.) The {{Window|Indexes}} window opens. #Click on the {{Button|Index Design}} button on the toolbar. (It looks like a sheet of paper with a blue drafting triangle laying ontop of it.) The {{Window|Indexes}} window opens.
#Click the {{Button|New Index}} icon (a sheet of paper with a yellow sunburst.) #Click the {{Button|New Index}} icon (a sheet of paper with a yellow sunburst.)
-#'''New Index''' Appears in the box on the left. Type an appropriate name (e.g. EnglishName)+#'''New Index''' appears in the box on the left. Type an appropriate name (e.g. EnglishName)
#Check the {{prefName|unique}} box in the {{Section|Index Details}} section #Check the {{prefName|unique}} box in the {{Section|Index Details}} section
#Set the {{prefName|Index Field}} to '''EnglishName''' and the order to '''Ascending''' #Set the {{prefName|Index Field}} to '''EnglishName''' and the order to '''Ascending'''
#Click the {{Button|Save Index}} icon (a sheet of paper with a "floppy" disk on it). #Click the {{Button|Save Index}} icon (a sheet of paper with a "floppy" disk on it).
#Click {{Button|Close}} #Click {{Button|Close}}
 +#Close the {{Window|Table}} window by clicking on the red button at the top left of the window.
===Creating a Unique Constraint=== ===Creating a Unique Constraint===
Line 40: Line 41:
#Make a note of the exact table and field names involved. In this case '''AOU_Birdlist''' and '''EnglishName''' #Make a note of the exact table and field 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.'' #:''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...}}.+#With the database open in 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: #Enter the following statement into the top portion of the {{Window|Execute SQL Statement}} window:
#:{{preBox|<nowiki>alter table "AOU_Birdlist" add constraint "uq_EngName" unique ("EnglishName" )</nowiki>}} #:{{preBox|<nowiki>alter table "AOU_Birdlist" add constraint "uq_EngName" unique ("EnglishName" )</nowiki>}}
Line 52: Line 53:
#Click on the icon that looks like a block of blue cells with a green plus in the corner. #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''' #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. +#:'''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. #If the '''Sightings''' Table does not appear in the relationship "map," add it as well.
#close the floating window #close the floating window
-#click on the the {{prefName|EnglishName}} field in the {{Window|AOU_Birdlist} sub-window; drag from there to the {{prefName|Bird_Name}} field in the {{Window|Sightings}} sub-window. A line appears connecting these two fields.+#click on the the {{prefName|EnglishName}} field in the {{Window|AOU_Birdlist}} sub-window; drag from there to the {{prefName|Bird_Name}} 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 disk drive symbol in the toolbar to save the changes you have made, and close the window.
==Related External Links== ==Related External Links==
[http://www.oooforum.org/forum/viewtopic.phtml?p=248053#248053 OOo forum thread on unique constraints] [http://www.oooforum.org/forum/viewtopic.phtml?p=248053#248053 OOo forum thread on unique constraints]
-{{botlangbarEN|[[Fr:Établir les Relations|Français]]}}+{{botlangbarEN|[[Fr:Établir les Relations|Français]] [[It:Stabile_Relazioni|Italiano]]}}

Current revision

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 to establish the relationships between the various tables.

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. One-to-one relationships are most frequently used when some data (e.g. salary) is confidential and access to it must be more limited than access to other data (e.g. Name and Address). For our birding database we will not need to use One-to-one relationships.

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 Sightings
  4. close the floating window
  5. click on the the FieldTrip_ID field in the Field_Trips sub-window; drag from there to the Sightings_FieldTrip_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 disk 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 is 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 Field_Trips tables (Location_Name relates to Field_Trip_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 Field_Trips tables.

Creating a Unique Index

While a Unique Index is not absolutely necessary for this process, it will help insure that we don't accidently enter duplicate information into the fields in question.

  1. In the main database window, click on the Tables icon in the left hand column
  2. Right-click or control-click on the AOU_Birdlist icon and choose Edit
  3. Click in the cell with the EnglishName field name
  4. Click on the Index Design button on the toolbar. (It looks like a sheet of paper with a blue drafting triangle laying ontop of it.) The Indexes window opens.
  5. Click the New Index icon (a sheet of paper with a yellow sunburst.)
  6. New Index appears in the box on the left. Type an appropriate name (e.g. EnglishName)
  7. Check the unique box in the Index Details section
  8. Set the Index Field to EnglishName and the order to Ascending
  9. Click the Save Index icon (a sheet of paper with a "floppy" disk on it).
  10. Click Close
  11. Close the Table window by clicking on the red button at the top left of the window.

Creating a Unique Constraint

  1. Make a note of the exact table and field 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.
  2. With the database open in the main window (the one with Tables, Queries, Forms, and Reports in the left hand column), go to the Tools menu and choose SQL....
  3. 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.
  4. Click Execute
    Note: The Status section of the window will tell you if the command was succesfully executed or if there was a problem.
  5. 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.
  4. If the Sightings Table does not appear in the relationship "map," add it as well.
  5. close the floating window
  6. click on the the EnglishName field in the AOU_Birdlist sub-window; drag from there to the Bird_Name field in the Sightings sub-window. A line appears connecting these two fields.
  7. click on the disk drive symbol in the toolbar to save the changes you have made, and close the window.

Related External Links

OOo forum thread on unique constraints


This article in other languages: Français Italiano
Personal tools