A relationship is an association that is established between columns (fields) in two tables.
Three types of relationships can be distinguished:
 One-to-one relationship: when one record in a table can have only one matching record the another table and vice versa. This type of relationship is used normally when we want to store information that only have some records in a table. For example we have a table of Employees, for each employee we store general data as name, address, ...and for those employees (not many) that have work in foreign countries, we want the total amount of years worked outside, and the total amount of years spend outside. So we can store this information in another table and relate the two tables with an ono-to-ono relationship.
It is not a commonly used relationship type.
One-to-many relationship: When a record in a table can have only one matching record in the other table, and a record from the second table can have many matching records in the first table.
E.g: we have two tables, one with the data of different countries, and another with data of clients. A client live in one country but in one country can live many clients, in this relationship, Clients table is the Related table.
It is the most common type of relationship.
Many-to-many relationships: When a record in a table can have many matching records in a second table and when a record in the second table can have many matching records in the first table.A many-to-many relationship is only possible by defining an intermediate table between the two other tables.
E.g: We have orders and products, one order can have many products and one product can appear on many orders, so we must have a third table Order details to relate products with orders. In this table the primary key consist
In this unit we will see how to relate tables and the different types of relationships that can exist between two tables in a database.
If you do not know what a relationship is in a database, here  we will introduce you to some basic concepts to understand relationships better. |
Creating the first relationship.
|  |
To create relationships in Access2003 firstly we need to position ourself in the Relationships window, we can opt for:
 in Database window, drop down Tools menu, and select Relationships... option.
or,
Click on the  button on the toolbar.
|
 The Show table dialogue box will appear:
Click on one of the tables required in the relationship and click on the Add button; or double-click the name of the table.
Repeat the previous step to add the second table, and so on.
Finally click on the Close button to finish adding tables.
|
Now the Relationships window will appear with the tables added before.
To create the relationship:
Drag the field of the principal table to the equivalent field in the related table. In our case drag Number (in Customers table) to Customer (in Invoices table).
Normally you drag the primary key of the primary table.
To relate tables with two or more fields, first select the fields mantaining CTRL key down, and then drag them.
|
The Edit relationships dialogue box appears next:
At the top should be the names of the related tables (Customers and Invoices) and below this the names of the related fields (Number and Customer). Observe! they always have to be the same kinds of fields containing the same types of information.
Observe at the bottom the Relationship type assigned depends on the charateristics of the related fields (in our case One-to-Many)
Activate the Enforce Referential integrity by clicking on it.
If desired, the boxes Cascade Update Related Fields and Cascade Delete Related Records can be activated.
If you want to know more about referential integrity and Cascade procedures clic here  .
To terminate, click on the Create button.
The relationship is created and will appear in the Relationships window.
Referential integrity is a system of rules that relational database systems use in order to ensure that data stored in related tables are valid.
In Access 2003, we can activate these rules by checking the Enforce Referential integrity box when relationship is created o modified.
If Referential Integrity is checked, Access will not allow us to insert a record in the related table if there is no matching record in the primary table.
E.g: We have a residents table and a Cities table, in the Residents table I have a City field which indicates in which city the resident lives in, the two tables should be related by the City field. In this kind of relation (one-to-many) the Cities table is the primary table and the Residents table the related table (a city has many residents, an a resident lives in one city). By checking the Enforce Referential Integrity box, we will not be allowed to insert a resident with a city that does not exist in the Cities table.
Referential integrity has two associated actions:
Cascade update the related fields: If checked, when a value is changed in the related field in the primary table, the values in its related records in the related table will be automatically changed.
E.g: If we change the name of a city in Cities table, then automatically in the Residents table all the residents from this city will change to the new value.
Cascade delete related records: If checked, when a record is deleted from the primary table, all of the related records in the related table will also be deleted. E.g: If we delete a city in the Cities table, all the residents from this city are automatically deleted from the Residents table.
If options are not selected, Acess does not allow us to change the name of a city or eliminate a city if it has any residents assigned.
|
|
|
No comments:
Post a Comment