There are relationships between database tables to avoid redundancy in design. That is, do not enter the same values into several objects, but create only one and use in others, adding a link to the main one in them. In this case, you must comply with the database integrity requirement.
Database Connections
As an example, consider the links in the database of a travel agency. It includes a table of hotels in Thailand - the country to which the travel agency sends guests. Hotels are located in various regions of the Kingdom.
If the company works with hotels in only a few regions, and in each of the regions there are tens or hundreds of hotels, then filling out the region field for each hotel each time is an empty job. Therefore, a separate table is created in the database containing only a list of regions, and when entering a hotel, the user simply selects the one from the drop-down list.
By analogy, a list of countries has been added for a company working with several states. Then another field is added to the list of regions - “Country”, and a directory of countries is added to the database, the names of which are then selected from the list.
Tables are one-to-many related. This means that one entry from the “Regions” table is found many times in the “Hotels” table. In addition, there are many-to-many and one-to-one views. But the latter is rarely used in practice. Below we will learn how these types are denoted in Access data schemas.
DB data schema
In the given example, “Hotels” are associated with “Regions”, and those, in turn, are associated with “Countries”. This information, written in text, does not show the connections between objects too clearly. And in our database there are only three tables, and there can be hundreds of them. Keeping in mind all the connections to the developer is difficult.
As an aid in the databases, data schemes are created that visually show all the objects and the relationships between them. In Access, data schemas are created using a special tool. For our database, it looks like this:
Rectangles denote tables with a list of fields, lines between them - links. On the communication line at the places adjoining the rectangles of the tables, the following notation is made: “1” and “∞”. They show what type of connection is applied in this regard. The “1” icon on the source table with the “∞” icon on the receiver indicates the one-to-many view. Both connections in our database are of this type.
Accordingly, two units at the two ends of the line speak of the one-to-one form, and two infinity signs - many-to-many.
Creating a data schema
To create a data scheme in Access, a tool has been added to the "Work with Databases" panel. The DBMS automatically creates a schema for the tables and relationships that exist in the database. The above diagram was created by the system independently. The user can make changes to the layout. Some of them will not affect the structure of the database, only the display of information. And some will lead to changes in the structure.
In the "Design" mode, the "Clear Layout" operation is available. When it is executed, the data schema screen in Access is cleared, and the tables and relationships are hidden. This does not mean that they disappear from the database - they simply are not reflected in the layout of the circuit.
The operation "Hide Table" will perform the same action on the selected object. It will simply disappear from the screen along with its relationship lines. To return hidden tables, the "Display Table" operation will help. The objects to be added to the layout are selected. In this connection with him are displayed automatically.
Creating relationships between tables
In our example, the relationships between the tables were already defined at creation time. Let us dwell in more detail on how to do this. As we already know, “Hotels” contains the “Region” field, the data for which are taken from the table of the same name. When adding the Region column, the type of the Substitution and Relation field is indicated.
The field creation wizard opens, in which we select the option of obtaining the value of cells from another object. At the second step of the wizard, select the object from which the values will be substituted. In our case, this is the “Regions” table. From the list of fields available for display, we need “Name” - in the table of hotels the names of the regions will be shown. If necessary, specify the sort order of the names and the width of the column.
At the last step, give a name to the new field and specify the integrity parameter. We will dwell on it in more detail below. After clicking on the “Finish” button, the Region column is added to the hotel table, the values for it are taken from the specified object.
The relationship between the tables of hotels and regions is now displayed in the diagram.
Relationship change
If a relation is not added when adding a column to an object, this is done directly in the data schema layout in Access. How to create a new relationship, we show an example. Click the "Change Communications" button. In relationship editing, to create a new relationship, click New. In the "Create" form, select the tables for the relationship and the fields that will correspond to each other.
For an already created connection, it is possible to change the parameters for combining records in queries. To do this, call the MS Access data schema dialog box “Modify Relationships” and click the “Combine” button. In the form of editing parameters, options for associations are proposed:
- In the first case, the query results display only those rows in which the fields of the "Hotels" and "Regions" tables coincide.
- In the second case, all the lines of the “Regions” and only the matching “Hotels” are combined.
- In the third, the situation is opposite to the second - all the lines of the "Hotels" are combined with the matching "Regions".
We leave automatic selection of the system - the first option.
DB data integrity
The relationships between database objects on the data schema in Access lead us to the concept of data integrity. As shown above, when creating relationships between fields of database objects, an integrity parameter is specified. If it is enabled, communications between objects are maintained and protected by the system.
We show this clearly on the example of the base of a travel company. In the "Hotels", the hotel with the name Anantara Lawana Koh Samui Resort belongs to the Samui region. Suppose we removed this area from the "Regions". Now the field refers to a record that does not exist. This is a violation of integrity.
Similarly, with the established requirement of maintaining integrity, we will not be able to select the “Chiang Mai” region in this field, because it does not exist in the table of regions.
If necessary, Access database schemas are uploaded to a report and sent to a printing device, converted to a file, or sent by e-mail.