Access data schema. Database schema in access

Topic 2.3. Presentation software and office programming basics

Topic 2.4. Database management systems and expert systems

2.4.11. Training database with the main button form "Training_students" - Download

DBMS and expert systems

2.4. Database management systems and expert systems

2.4.3. Creating a database (establishing relationships between tables and populating tables)

2.4.3.2. Establishing relationships between tables in the Access DBMS

After creating the table structure (Students, Student Groups, Disciplines, Academic Progress) for the entities of the "Dean's Office" database, it is necessary to establish connections between the tables. Relationships between tables in the database are used when generating queries, developing forms, and creating reports.

To create relationships, you must close all tables and select the "Data Schema" command from the Tools menu; the active "Add Table" dialog box will appear against the background of the inactive Data Schema window.

Rice. 1.

In the Add Tables dialog box that appears, select the names of the tables and click the Add button, and the tables will be added in the “Data Schema” window. After all tables appear in the Data Schema window, you must close the Add Table window by left-clicking on the Close button.

Rice. 2.

The next step is to establish relationships between tables in the Data Schema window. To do this, in the Data Schema window, you need to drag (move) the Group Code field from the Groups table to the corresponding field in the Students table; as a result of this operation, the “Change Links” window will appear.

In the "Edit Relationships" dialog box that appears, you need to activate the checkboxes: "Ensure data integrity", "cascade update of related fields" and "cascade delete of related records", make sure that the relationship type is set to one-to-many and click the Create button.

Rice. 3.

In the Data Schema window, a one-to-many relationship appears between the Student Groups and Students tables. In a similar way, you need to link the Student Code fields in the Students and Achievement tables, and then the Discipline Code fields in the Achievement and Discipline tables. As a result, we get the Data Schema shown in the figure.

Rice. 4.

After establishing relationships between tables, the Data Schema window must be closed.

Next, you need to fill out all the tables. It is advisable to start filling out tables from the Student Groups table, since the Group Code field of the Students table is used as a substitution column for filling out the corresponding field in the Students table.

2.4.3.3. Filling tables

You can start filling out the tables by filling out the Students table. In the Database window, select the desired table, then click on the Open button.

The structure of the database table will appear on the screen in table mode. The new table consists of one empty row.

Rice. 5.

Filling is done according to records, i.e. information is entered for the entire line. The counter field is filled in automatically. After the first entry is entered, the empty entry is moved to the end of the table. The transition to the next field is carried out by pressing the Tab key. Rice. 9.

The Access application uses various methods moving around the table. You can move from record to record using: cursor keys; buttons from the Record area located at the bottom of the table in table mode; commands Edit - Go.. To move from field to field (from left to right), use the Tab and Enter keys, and in the opposite direction Shift+Tab.

Searching for data in a large table using the navigation buttons can take a long time, so you must use the Edit - Find command to find and replace data in fields. A dialogue window will open. In the Sample field of the search dialog box, the object you are looking for is specified and the search is carried out. To replace data in fields, you must go to the Replacement tab.

After creating the structure of the tables, filling them out and establishing relationships between the tables, you can begin building queries.

A relationship in Access helps you combine data from two different tables. Each relationship of fields consists in two tables with corresponding data. For example, you might have a ProductID field in a Products table and in an OrderDetails table. Each record in the OrderDetails table has a ProductID that corresponds to a record in the Products table with the same ProductID.

When you use related tables in a query, the relationship lets Access determine which records from each table to combine in the result set. A relationship can also help prevent missing data, by keeping deleted data from getting out of synch, and this is called referential integrity.

Before you work with relationships, make sure you understand the background concepts. For more information, see Guide to table relationships and Get started with table relationships.

In this article

Overview

In an Access database, you create a table relationship using one of the following methods:

In the Relationships window, add the tables that you want to relate, and then drag the field to relate them from one table to the other table.

Drag a field on to a table datasheet from the Field List pane.

When you create a relationship between tables, the common fields are not required to have the same names, although it is often the case that they do. The common fields must have the same data type. If the primary key field is an AutoNumber field, however, the foreign key field can also be a Number field if the FieldSize property of both fields is the same. For example, you can match an AutoNumber field and a Number field if the FieldSize property of both fields is Long Integer. When both common fields are Number fields, they must have the same FieldSize property setting.

Create a table relationship by using the Relationships window

Create a table relationship by using the Field List pane to add a field

You can add a field to an existing table that is open in Datasheet view by dragging it from the Field List pane. The Field List pane shows fields available in related tables and also fields available in other tables in the database.

Field List pane and the table to which you dragged the field. This relationship, created by Access, does not enforce referential integrity by default. To enforce referential integrity, you must edit the relationship. See the section for more information.

Open a table in Datasheet view

In the Navigation Pane, double-click the table.

Open the Field List pane

The Field List pane shows all of the other tables in your database, grouped into categories. When you work with a table in Datasheet view, Access displays fields in either of two categories in the Field List pane: Fields available in related tables and . The first category lists all of the tables that have a relationship with the table with which you are currently working. The second category lists all of the tables with which your table does not have a relationship.

In the Field List pane, when you click the plus sign ( + ) next to a table name, you see a list of all the fields available in that table. To add a field to your table, drag the field that you want from the Field List pane to the table in Datasheet view.

Add a field and create a relationship from the Field List pane

With the table open in Datasheet view, press ALT+F8. The Field List pane appears.

Under Fields available in other tables, click the plus sign ( + ) next to a table name to display the list of fields in that table.

Drag the field that you want from the Field List pane to the table that is open in Datasheet view.

When the insertion line appears, drop the field into position.

The Lookup Wizard starts.

Follow the instructions to complete the Lookup Wizard.

The field appears in the table in Datasheet view.

When you drag a field from an "other" (unrelated) table and then complete the Lookup Wizard, a new one-to-many relationship is automatically created between the table in the Field List and the table to which you dragged the field. This relationship, created by Access, does not enforce referential integrity by default. To enforce referential integrity, you must edit the relationship. See the section for more information.

Edit a relationship

You change a relationship by selecting it in the Relationships window and then editing it.

Carefully position the cursor so that it points to the relationship line, and then click the line to select it.

The relationship line appears thicker when it is selected.

With the relationship line selected, double-click it.

On the Design tab, in the Tools group, click Edit Relationships.

The Edit Relationships dialog box appears.

Open the Edit Relationships dialog box

Set the join type

When you define a table relationship, the facts about the relationship inform your query designs. For example, if you define a relationship between two tables, and you then create a query that employs the two tables, Access automatically selects the default matching fields based upon the fields specified in the relationship. You can override these initial default values ​​in your query, but the values ​​supplied by the relationship will often prove to be the correct ones. Because matching and bringing together data from more than one table is something you will do frequently in all but the most simple databases, setting defaults by creating relationships can be time saving and beneficial.

A multiple table query combines information from more than one table by matching the values ​​in common fields. The operation that does the matching and combining is called a join. For instance, suppose you want to display customer orders. You create a query that joins the Customers table and the Orders table on the Customer ID field. The query result contains customer information and order information for only those rows where a corresponding match was found.

One of the values ​​you can specify for each relationship is the join type. The join type tells Access which records to include in a query result. For example, consider again a query that joins the Customers table and the Orders table on the common fields that represents the Customer ID. Using the default join type (called an inner join), the query returns only the Customer rows and the Order rows where the common fields (also called the joined fields) are equal.

However, suppose you want to include all Customers - even those who have not yet placed any orders. To accomplish this, you have to change the join type from an inner join to what is known as a left outer join. A left outer join returns all of the rows in the table on the left side of the relationship and only those that match in the table on the right. A right outer join returns all of the rows on the right and only those that match on the left.

Note: In this case, "left" and "right" refer to the position of the tables in the Edit Relationships dialog box, not the Relationships window.

You should think about the result you will most often want from a query that joins the tables in this relationship, and then set the join type accordingly.

Set the join type

In the Edit Relationships dialog box, click Join Type.

The Join Properties dialog box appears.

Click your choice, and then click OK.

The following table (using the Customers and Orders tables) shows the three choices that are displayed in the Join Properties dialog box, the type of join they use, and whether all rows or matching rows are returned for each table.

When you choose option 2 or option 3, an arrow is shown on the relationship line. This arrow points to the side of the relationship that shows only matching rows.

Make changes in the Join Properties dialog box

Enforce referential integrity

The purpose of referential integrity is to prevent orphan records – records that reference other records that no longer exist. You enforce referential integrity by enabling it for a table relationship. Once enforced, Access rejects any operation that would violate referential integrity for that table relationship. This means that Access will reject both updates that change the target of a reference and deletions that remove the target of a reference. To have Access propagate referential updates and deletions so that all related rows are changed accordingly, see the section.

Turn referential integrity on or off

On the Database Tools tab, in the Relationships group, click Relationships.

On the Design tab, in the Relationships group, click All Relationships.

Hidden check box in the table"s Properties Navigation Options dialog box.


Double-click the relationship line. The Edit Relationships dialog box appears.

Select or clear .

OK.

If you enforce referential integrity, the following rules apply:

You cannot enter a value in the foreign key field of a related table if that value doesn't exist in the primary key field of the primary table - doing so creates orphan records.

You cannot delete a record from a primary table if matching records exist in a related table. For example, you cannot delete an employee record from the Employees table if there are orders assigned to that employee in the Orders table. You can, however, choose to delete a primary record and all related records in one operation by selecting the check box.

You cannot change a primary key value in the primary table if doing so would create orphan records. For example, you cannot change an order number in the Orders table if there are line items assigned to that order in the Order Details table. You can, however, choose to update a primary record and all related records in one operation by selecting the Cascade Update Related Fields check box.

Notes: If you have difficulty enabling referential integrity, note that the following conditions are required to enforce referential integrity:

• The common field from the primary table must be a primary key or have a unique index.

  The common fields must have the same data type. The one exception is that an AutoNumber field can be related to a Number field that has a FieldSize property setting of Long Integer.

  Both tables exist in the same Access database. Referential integrity cannot be enforced on linked tables. However, if the source tables are in Access format, you can open the database in which they are stored and enable referential integrity in that database.

Set the cascade options

You might encounter a situation where you have a valid need to change the value on the "one" side of a relationship. In such a case, you need Access to automatically update all of the affected rows as part of a single operation. That way the update is completed in full so that your database is not left in an inconsistent state - with some rows updated and some not. Access helps you avoid this problem by supporting the Cascade Update Related Fields option. When you enforce referential integrity and choose the Cascade Update Related Fields option, and you then update a primary key, Access automatically updates all fields that reference the primary key.

You might also need to delete a row and all related records - for instance, a Shipper record and all related orders for that shipper. For this reason, Access supports the Cascade Delete Related Records option. When you enforce referential integrity and select the Cascade Delete Related Records check box, Access automatically deletes all records that reference the primary key when you delete the record that contains the primary key.

Turn cascade update and/or cascade delete on or off

On the Database Tools tab, in the Relationships group, click Relationships.

On the Design tab, in the Relationships group, click All Relationships.

All tables with relationships are displayed, showing relationship lines. Note that hidden tables (tables for which the Hidden check box in the table"s Properties dialog box is selected) and their relationships will not be shown unless Show Hidden Objects is selected in the Navigation Options dialog box.


Click the relationship line for the relationship that you want to change. The relationship line appears thicker when it is selected.

Double-click the relationship line.

The Edit Relationships dialog box appears.


Select the Enforce Referential Integrity check box.

Select either the or the Cascade Delete Related Records check box, or select both.

Make any additional changes to the relationship, and then click OK.

Note: If the primary key is an AutoNumber field, selecting the Cascade Update Related Fields check box will have no effect, because you cannot change the value in an AutoNumber field.

Delete a table relationship

Important: When you remove a relationship, you also remove referential integrity support for that relationship, if it is enabled. As a result, Access will no longer automatically prevent the creation of orphan records on the "many" side of a relationship.

To remove a table relationship, you must delete the relationship line in the Relationships window. Position the cursor so that it points to the relationship line, and then click the line. The relationship line appears thicker when it is selected. With the relationship line selected, press DELETE.

On the Database Tools tab, in the Relationships group, click Relationships.

On the Design tab, in the Relationships group, click All Relationships.

All tables with relationships are displayed, showing relationship lines. Note that hidden tables (tables for which the Hidden check box in the table"s Properties dialog box is selected) and their relationships will not be shown unless Show Hidden Objects is selected in the Navigation Options dialog box.


Click the relationship line for the relationship that you want to delete. The relationship line appears thicker when it is selected.

Press the DELETE key.

Access might display the message Are you sure you want to permanently delete the selected relationship from your database?. If this confirmation message appears, click Yes.

Note: If either of the tables employed in the table relationship are in use - perhaps by another person or process, or in an open database object, such as a form - you will not be able to delete the relationship. You must close any open objects that employ these tables before you try to remove the relationship.

Create, edit, or delete a relationship in an Access web app

There are important differences when working with relationships in an Access web app.

Create a relationship

The Relationships window isn't available in an Access web app. Instead of creating a relationship in an Access web app, you create a lookup field that gets values ​​from a related field in another table. For example, let's say you have an Employees table and you want to add a lookup to a Regions table so you can show which region each employee works in.

Note: The field that your lookup will use as the source for values ​​must already exist before you create your lookup field.

Here’s how you create a lookup field in an Access web app:

Edit a relationship

The Relationships window isn't available in an Access web app. You use a field in one table as the source (lookup field) for values ​​in the related field in another table.

Delete a relationship

The Relationships window isn't available in an Access web app. You use a field in one table as the source (lookup field) for values ​​in the related field in another table. In order to delete a relationship between two tables in an Access web app, you need to delete the lookup field and its associated data.

Create or modify a relationship in an Access 2010 web database

To create a relationship in an Access 2010 web database, you use the Lookup Wizard. The Relationships window is not available in a web database. You use a field in one table as the source for values ​​in the related field in another table.

Note: The field that you use as the source for values ​​must already exist before you can use the Lookup Wizard.

Create a relationship in an Access 2010 web database by using the Lookup Wizard

Open the table that should get its values ​​from the other table.

To the right of the last field, click Click to Add, and then click Lookup & Relationship.

Note: You might need to scroll horizontally to see Click to Add.

On the first screen of the Lookup Wizard, select I want the lookup field to get the values ​​from another table, and then click Next.

Select the table that has the source values, and then click Next.

In the Available Fields box, double-click the field that has the source values, and then click Next.

Optionally, specify a sort order for the lookup field. Click Next.

Optionally, adjust the width of the lookup field – a good idea if the values ​​are long. Click Next.

Enter a name for the new field. If you want to make sure that the values ​​in the two tables always match, select the Enable Data Integrity check box, and then click one of the following:

• Cascade Delete– this option means that if you delete a record from one table, corresponding records in the other table are also deleted.

  Restrict Delete– this option means that if you attempt to delete a record from one table but there is a corresponding record in the other table, the delete operation is not allowed.

  Note: Don't select Allow Multiple Values in the Lookup Wizard when you are using the wizard to create a relationship.

Modify a relationship in an Access 2010 web database

Open the table that gets its values ​​from another table.

Select the field that gets its values ​​from another table.

On the Fields tab, in the Properties group, click Modify Lookups.

Follow the steps in the wizard to make the changes that you want. You can change the following:

• The field that is the source of values

  The sort order of the values

  The width of the field, and whether to hide the key column

  The label for the lookup field

  Whether data integrity is enabled

  If data integrity is enabled, whether deletes are cascaded or restricted

After creating all the database tables, all that remains is to establish connections between them. To do this you need:

1. Click on the “Tables” tab of the database window.

2. On the menu Service select item Data schema...

3. Out the window Data Schema add all linked tables. To do this, open the menu Connections and select item Add table... or click on the button in the toolbar. In the window that appears Adding a table highlight the name of the desired table with the mouse and click on the button Add (Fig. 1).

Rice. 1. Adding a table to the window Data Schema .

After this, the layout of the corresponding table will appear in the window Data Schema. Having finished adding all the necessary tables to the window Scheme data you need to close the window Adding a table button Close .

4. Table layouts in the window Data Schema use the mouse to position as desired using the drag and drop method. Adjust the size of table layouts and stretch them with the mouse.

5. To establish a relationship between two tables:

A) Click on the primary key of the table located on side “1” of the relationship.

b) Click left button mouse and, without releasing it, move the primary key field to the subordinate table (when moving, the mouse pointer changes its appearance).

V) Release the left mouse button over the foreign key of the subtable.

6. In the window that appears Changing connections The names of the fields by which the tables are linked will be displayed. It is necessary to mark the item Ensuring data integrity . In this case, Access will make it impossible to write to a subtable a value of a common field that is not in the main table (Fig. 2) .

Rice. 2. Establishing a connection between tables.

After data integrity is established, two additional options Cascade update of related fields And Cascade deletion of related fields . The first option allows you to update any value of a key field in the main table and automatically change it in all subordinate tables. The second option works in a similar way - deleting records from subordinate tables that match the deleted key of the main table. After checking all these points, you need to click on the create button.

In the window Data Schema A connection is established between the tables in the form of a marked line. To delete a connection, select it with the mouse (the line becomes bold) and press the button Delete on the keyboard.

7. Repeat steps p. 5-6 to establish all connections.

8. Close window Data Schema , answering “ Yes » when the system prompts you to save the change in the data schema layout.

For the example under consideration, we will follow the procedure described above to establish all the relationships in the data model. The data diagram constructed in this case will exactly correspond to Fig. 3.

Comment. When building a data schema, tables must be empty.

Rice. 3. Data diagram of the example under consideration.

Lecture 6

Searching for information in database tables

Sorting information in tables

For ease of viewing, you can sort the records in the table in a specific sequence, for example, in the table Tires records can be sorted in descending order of their radius. Sorting buttons on the toolbar (menu command chains Posts - Sort - Sort Ascending or Sort in descending order) Allows you to sort columns in ascending or descending order. Before you click the sort button, you must select the fields to use for sorting. To select a field, simply place the cursor in any of its entries. After that, click on the sort button and the data will be displayed in sorted order.

Rice. 1. Sorting the table Tire across the field Radius

In Table view, you can select two or more adjacent columns at once, and then sort by them. By default, Access starts sorting records from the leftmost selected column. In this case, the table records will be sorted first by the leftmost selected column, then (for identical values ​​in the first sorted column) - by the second, etc. If you need to restore the order in which records are displayed, use the command chain Posts - Remove filter .

Filters

When working with a table in online mode, you can set a filter, i.e. ask logical expression, which will allow you to display only those records for which this expression is true.

There are three types of data filtering in Access:

Regular filter-used to select the desired values ​​from the list without viewing all records in the table or form, or when specifying several selection conditions at the same time (the selection criterion is formed using a logical operator Or ).

To perform data filtering, you need to open the table, and then in the menu in the menu Posts let's activate the team Filter , and in the submenu that opens, the command Change filter . Then, in the filter creation window, the information selection criterion is indicated in the required field (Fig. 2).

A relational database, created in accordance with the design of the domain's canonical data model, consists of normalized tables connected by one-to-multiple relationships. Such a database ensures that there is no duplication of descriptive data, that it is entered once, and that data integrity is maintained by the system. Relationships between tables allow you to combine data from different tables, which is necessary for solving most tasks of entering, viewing and adjusting data, obtaining information from queries and outputting reports.

Relationships between tables are established in accordance with the project logical structure database and are stored in the Access data schema. Data schema in Access is not only a tool graphic display logical structure of the database, it is actively used by the system in the process of data processing. Creating a data schema allows you to simplify the design of multi-table forms, queries, reports, and also ensure that the integrity of interrelated data is maintained when entering and adjusting data in tables.

Creating a Data Schema

Creating a data schema begins with executing the command Data Schema(Relationships) in the group Relationship(Relationships) on the ribbon tab Working with databases(Database Tools). As a result of executing this command, a data diagram window and a dialog box open Adding a table(Show Table), in which you select the tables included in the diagram (see Fig. 3.48). Dialog box Adding a table will open automatically if no relationship is already defined in the database. If the window does not open, on the tape Working with connections | Constructor(Relationship Tools | Design) in the group Connections(Relationships) click button Show table(Show Table).

Including tables in your data schema

In the window Adding a table(Show Table) (Fig. 3.48) displays all the tables and queries contained in the database. Select a tab Tables(Tables) and using the button Add(Add) place it in the window Data Schema(Relationships) all previously created database tables Item delivery displayed in the window Adding a table(Show Table). Then press the button Close(Close). As a result, in the window Data Schema(Relationships) database tables will be represented by windows with lists of their fields and keys highlighted in bold (see Fig. 3.52).

Creating relationships between data schema tables

When creating relationships in a data schema, a design of the logical structure of a relational database is used, which shows all single-to-multiple table relationships. Relationships are realized by adding common fields called communication key. In case of one-to-multivalued relationships between tables, the key of the relationship is the key of the main table (simple or composite). In a subordinate table, it may be part of a unique key or not be part of the table key at all. Single-to-multiple relationships are fundamental in relational databases. One-to-one relationships are used, as a rule, when it is necessary to distribute large number fields defined by the same key in different tables that have different maintenance regulations.

Creating relationships using a simple key

Let's establish a connection between the BUYER and CONTRACT tables, which are in a one-to-many relationship. When establishing connections between a pair of tables that are in a relationship of type 1: M, we select in the main table BUYER the key field CODE_POK, by which the connection is established. Next, with the mouse button pressed, drag it into the corresponding field of the CONTRACT subtable.

Because the relationship field is a unique key in the master relationship table but not a key in the child relationship table, the data schema in Access identifies a one-to-many relationship between the records in those tables. The One-To-Many value will appear in the window Changing connections(Edit Relationships) in line Relationship type(Relationship Type) (Fig. 3.49).

COMMENT
If the link field is a unique key in both tables being linked, the data schema in Access identifies the relationship " one-to-one". If some unique index is used to link tables instead of the key field of the main table, the system also states the relationship of the tables as 1: M or 1: 1.

Defining relationships using a composite key

Let's define the relationships between the SHIPPING INvoice tables, which are connected by the composite key NOM_INCLUSION + CODE_SC. To do this, in the main table INVOICE, select both of these fields by pressing the key , and drag them into the subordinate table SHIPMENT.

In the window Changing connections(Edit Relationships) (Fig. 3.50) for each field of the composite key of the main table INVOICE, named Table/query(Table/Query), select the corresponding field of the subordinate table SHIPPING, named Related table/query(Related Table/Query).

Cascading update and deletion of related records

If integrity is maintained for the selected relationship, you can set the cascading deletion mode for related records and the cascading update mode for related fields. Such parameters make it possible in the main table, respectively, to delete records and change the value in a key field, since with these parameters the system will automatically make the necessary changes in the subordinate tables, ensuring that the integrity properties of the database are preserved.

In the cascade mode for deleting related records, when you delete a record from the main table, all related records in the subordinate tables will be automatically deleted. When you delete a record from the main table, the deletion of subordinate records at all levels is performed if this mode is specified at each level.

In cascading update mode for related fields, when you change the value of a key field in a record in the main table, Access will automatically update the values ​​in the corresponding field in the subordinate records.

Install in window Changing connections(Edit Relationships) (see Figure 3.49) checkboxes cascading update of related fields(Cascade Update Related Fields) and cascade deletion of related records(Cascade Delete Related Records) is possible only after setting the data integrity parameter.

Once relationships are created, table images can be moved within the workspace of the data diagram window. Moving and resizing windows with lists of table fields in the data diagram window are carried out using standard Windows methods.

Note that if cascading deletes are not enabled, it is not possible to delete a record in the master table if there are related records in the child table.
Watch the video:

Here is the main thing we wanted to talk about on the topic “Data Schema in Access”.

There are connections between database tables that allow you to avoid redundancy in design. That is, do not enter the same values ​​into several objects, but create only one and use it in others, adding a link to the main one. In this case, it is necessary to comply with the requirement of database integrity.

Database Links

As an example, consider the connections 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 a company works with hotels in only a few regions, and in each region there are tens or hundreds of hotels, then filling out the region field for each hotel every time is a waste of time. 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 desired one from the drop-down list.

By analogy, a list of countries has been added for a company working with several countries. Then another field is added to the list of regions - “Country”, and a directory of countries is entered into the database, the names of which are then selected from the list.

Tables are related in a one-to-many manner. This means that one record from the Regions table appears many times in the Hotels table. In addition, there are many-to-many and one-to-one types. But the latter is extremely rarely used in practice. Below we will look at how these types are designated in data diagrams in Access.

Database schema

In the example above, “Hotels” are associated with “Regions”, and these, in turn, are associated with “Countries”. This information, written in text, does not clearly show the connections between objects. And in our database there are only three tables, but there can be hundreds of them. It is difficult for a developer to keep all the connections in his head.

As an auxiliary tool, data diagrams are created in databases that clearly visually show all objects and the relationships between them. In Access, data schemas are created using a special tool. For our database it looks like this:

Rectangles indicate tables with a list of fields, lines between them indicate connections. On the communication line, in the places where the table rectangles adjoin, the following designations are made: “1” and “∞”. They show what type of connection is applied in this regard. A “1” icon at the source table with a “∞” icon at the destination table indicates a one-to-many view. Both connections in our database are of this type.

Accordingly, two units at the two ends of the line indicate a “one-to-one” view, and two infinity signs indicate a “many-to-many” view.

Creating a Data Schema

To create a data schema in Access, a tool has been added to the “Working with Databases” panel. The DBMS automatically creates a schema based on the tables and relationships that exist in the database. The above diagram is created by the system itself. 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 structure.

In Design mode, the “Clear Layout” operation is available. When executed, the data schema screen in Access is cleared and tables and relationships are hidden. This does not mean that they disappear from the database - they are simply not reflected in the circuit layout.

The “Hide Table” operation will perform the same action on the selected object. He will simply disappear from the screen along with his relationship lines. The “Unhide Table” operation will help bring back hidden tables. Select the objects to add to the layout. In this case, connections with it are displayed automatically.

Creating relationships between tables

In our example, the relationships between the tables were already defined at creation time. Let's take a closer look at how to do this. As we already know, “Hotels” contains the “Region” field, the data for which is taken from the table of the same name. When you add a Region column, you specify a Lookup and Relation field type.

The Field Creation Wizard opens, in which we select the option of obtaining cell values ​​from another object. At the second step of the wizard, we 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” - the names of the regions will be shown in the hotel table. If necessary, set the sorting order of titles and the column width.

At the last step, we give the new field a name 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 hotel and region tables is now shown in the diagram.

Changing Relationships

If a relationship is not added when you add a column to an object, this is done directly in the data schema layout in Access. We'll show you how to create a new connection with an example. Click the "Edit Links" button. In editing relationships, to create a new relationship, click New. In the “Creation” form, select the tables for the connection 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 “Change Links” and click the “Merge” button. The form for editing parameters offers options for associations:

• In the first case, the query results display only those rows in which the fields of the “Hotels” and “Regions” tables match.
• In the second case, all rows of “Regions” and only matching “Hotels” are combined.
• In the third, the situation is the opposite of the second - all the lines of “Hotels” are combined with the matching “Regions”.

We leave automatic system selection - the first option.

DB data integrity

The connections between database objects in the data diagram in Access bring us to the concept of data integrity. As shown above, when creating connections between fields of database objects, the integrity parameter is specified. If enabled, relationships between objects are maintained and protected by the system.

Let us demonstrate this clearly using the example of a travel company’s database. In “Hotels”, a hotel with the name Anantara Lawana Koh Samui Resort belongs to the Koh Samui region. Let's say we deleted this area from the "Regions". The field now refers to a record that doesn't exist. This is a violation of integrity.

Similarly, with the integrity requirement set, we will not be able to select the Chiang Mai region in this field because it does not exist in the region table.

When needed, Access database schemas are downloaded into a report and sent to a printer, converted to a file, or sent by email.