The data schema in ms access allows. Data schema creation

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

Database links

As an example, consider the links in the travel agency database. It includes a table of hotels in Thailand - the country to which the travel agency sends guests. Hotels are located in different regions of the Kingdom.

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

By analogy, added a list of countries 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 base, the names of which are then selected from the list.

Tables are one-to-many related. 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 views. But the latter is rarely used in practice. Below we will take a look at how these types are denoted in data schemas in Access.

DB data schema

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

As an aid, data schemas are created in the databases that 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 denote tables with a list of fields, the lines between them are links. On the communication line at the points of adjoining to the rectangles of the tables, the designations are made: "1" and "∞". They show what type of communication is applied in this regard. The "1" icon at the source table with the "∞" icon at the sink denotes a one-to-many view. Both links in our database are of this type.

Accordingly, two ones at the two ends of the line indicate a one-to-one form, and two infinity signs - many-to-many.

Data schema creation

To create a data schema in Access, a tool has been added to the "Work with Databases" panel. The DBMS automatically creates a schema based on the tables and relationships that exist in the database. The above diagram was 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 structural changes.

In the "Design" mode, the "Clear layout" operation is available. It clears the data schema screen in Access and hides tables and relationships. 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. The "Show Table" operation will help you to get back hidden tables. Selects the objects to be added to the layout. In this case, links with him are displayed automatically.

Creating relationships between tables

In our example, the relationships between the tables have already been defined at the time of creation. Let's dwell on how to do this in more detail. 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 the lookup and relationship field type.

The wizard for creating a field opens, in which we select the option to get 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" - the names of the regions will be shown in the hotel table. If necessary, set the sort order of the names and the column width.

On 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 the "Finish" button, the "Region" column has been added to the hotels 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.

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 will show you how to create a new link with an example. Click the Change Links button. In editing relationships to create new connection 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 relationship, it is possible to change the parameters of combining records in queries. To do this, open the MS Access data schema dialog box "Change links" and click the "Union" button. The options for joining are offered in the form for editing parameters:

• In the first case, the query results display only those rows in which the fields of the "Hotels" and "Regions" tables are the same.
• 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 rows of "Hotels" are combined with matching "Regions".

We leave the automatic system selection as the first option.

Database data integrity

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

Let's show this clearly using 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 Koh Samui region. Let's say we removed this area from Regions. The field now refers to a record that does not exist. This is a violation of integrity.

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

If needed, Access database schemas are uploaded to a report and sent to a print device, converted to a file, or emailed.

After creating the structures of the database tables, you should create a data schema. All database tables must be closed beforehand. You cannot create or modify relationships between open tables.

Data schema creation starts in a window Database with team selection Service Data Schema... After executing the specified command, two windows open: Data schema and Adding a table, in this case, the active window is Adding a table(Fig. 12), in which you can select the tables included in the data schema.

Rice. 12. Two windows: Data Schema and Add Table

After selecting the desired tables in the window Data schema corresponding tables with a list of their fields will be presented. If necessary, the size of the table windows can be changed so that the list of fields is fully visible. Then you can start defining relationships between tables.

When defining links in a data schema, it is convenient to use an information-logical model, by which it is easy to determine the main and subordinate table of each single-valued relationship, since in such a model the main objects are always placed above the subordinates. One-to-many-valued relationships are fundamental in relational databases.

To create a link of type 1: M between a pair of tables, select the key field in the main table (the name of this field in the data schema is displayed in bold), by which the link is established and, while holding down the mouse button, move the mouse pointer to the corresponding field of the subordinate table.

After completing the specified actions, a window will open Changing links(fig. 13). Moreover, in the field Relationship type automatically set to one-to-many.

Rice. 13. Window Change links

When creating a link by a composite key, you need to select all the fields included in the key of the main table, and drag them to one of the link fields in the subordinate table. At the same time, in the window Changing links needed for every compound key field in the master table - Table / Query select the corresponding field of the subordinate table named in this window - Linked table / query.

Then you can check the box. After checking this box, you can check the boxes cascading update of related fields and cascading deletion of related records. If the checkbox Ensuring data integrity is not set, then when adding or removing records and changing the values ​​of key fields, the user must himself monitor the consistency of data and the integrity of links, which is extremely difficult to perform with large amounts of data.

Maintaining coherent data integrity means that when you adjust your database, Access enforces the following conditions for related tables:

q An entry with a link key value that does not exist in the main table cannot be added to the subordinate table;

q In the master table, you cannot delete a record unless the associated records in the subordinate table have been deleted;

q You cannot change the value of a link key in a record of the master table if there are records associated with it in the subordinate table.

If there is a link between tables in the data schema with integrity settings, Access automatically monitors the integrity of that link when you add and delete records and change the values ​​of key fields. If a user attempts to violate these conditions, Access displays a corresponding message and does not allow the operation to be performed. So, for example, if only the checkbox is checked Ensuring data integrity, then you cannot delete data from the key field of the main table.

Establishing a relationship of type 1: M or 1: 1 between two tables and setting data integrity parameters for this relationship is possible only under the following conditions:

q Bound fields are of the same data type;

q Both tables are stored in one database;

q The master table is linked to the subordinate table by the primary simple or composite key of the master table.

Access does not allow you to select the Data Integrity check box for linking tables if data that does not meet the integrity requirements was previously entered into the tables.

If integrity is maintained for the selected link, then you can set the mode cascading update of related fields and the regime cascading deletion of related records.

In cascading mode of updating linked fields, when the data value in the link field of the master table changes, Access automatically changes the data values ​​in the corresponding field in the sub tables.

In the cascade deletion of related records mode, deleting a record from the master table will automatically delete all related records in the subordinate tables. When deleting records directly in the table or through a form, a warning is displayed about the possibility of deleting related records.

The resulting inter-table link is displayed in the window Data schema as a line connecting two fields of different tables. In this case, the symbol is displayed on the communication line near the main table - 1 , the subordinate table has an infinity symbol ( ¥ ) (fig. 14) .

Rice. 14. Data schema. Relationship of two tables by the key field Vendor code.

Thus, the meaning of creating relational links between tables is, on the one hand, in data protection, and on the other hand, in automating changes to several tables at once when changes in one table.

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. Database creation (establishing links between tables and filling tables)

2.4.3.2. Establishing relationships between tables in Access DBMS

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

To create links, you must close all tables and select the "Data Scheme" command from the Tools menu. The active "Add Table" dialog box will appear on the background of the inactive Data Scheme window.

Rice. 1.

In the appearing Add Tables dialog box, select the table names and click the Add button, while tables are added in the "Data Scheme" window. After all tables appear in the Data Scheme window, you need to close the Add Table window by clicking the left mouse button 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 Scheme window, drag (move) the GroupCode field from the Groups table to the corresponding field of the Students table, as a result of this operation, the "Change links" window will appear.

In the "Edit Relationships" dialog box that appears, activate the check boxes: "Ensure data integrity", "cascade update of related fields" and "cascade deletion of related records", make sure that the type of relationship is one-to-many and click Create.

Rice. 3.

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

Rice. 4.

After establishing links between tables, the Data Scheme window must be closed.

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

2.4.3.3. Populating tables

You can start filling in the tables by filling in the Students table. In the Databases window, select the required table, then click on the Open button.

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

Rice. 5.

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

Access uses a variety of methods to navigate a table. You can move from record to record using: cursor keys; buttons from the Record area located at the bottom of the table in the 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, which is performed using the navigation buttons, can take a long time, therefore, to find and replace data in the fields, you must use the Edit - Find command. A dialog box will open. In the Sample field of the search dialog box, the object you are looking for is specified and the search is performed. To replace the data in the fields, go to the Replacement tab.

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

Data schema

When creating a relational database in the Access DBMS, a data schema, which allows you to visually display the logical structure of the database: it shows tables and the relationships between them, and also ensures the use of the relationships established in the database when processing data.

In the data schema of a normalized database, which is based on one-to-one and one-to-many relationships between tables, provisioning parameters can be set for the links of these tables by a unique index of the main table or primary key coherent integrity.

In the process of maintaining the integrity of related data, the presence of a record in the subordinate table is not allowed if there is no record associated with the main table. Accordingly, in the case of initial loading of the database, when deleting, adding and updating records by the system, it is allowed to perform an operation only if it does not violate the integrity.

The relationships that are defined in the data schema are used automatically to join tables in the process of developing multi-tabular reports, queries, forms, which greatly simplifies the process of their design.

Data schema links can be established for any pair of tables with the same field that allows the table data to be joined.

Data schemas appear in the Navigation Pane of the Database Window only in Access projects that work with server databases. To display the data schema in the Access database use the command Data schema placed in the group Relationship in the tab Working with databases.

Example 1

Consider the data model of the "Supply of goods" subject area (Figure 1). The Access data schema built for this data model is shown in Figure 2.

In this diagram, the database tables with a list of their fields are displayed in the form of rectangles, and the links represent the fields by which the tables are interconnected. Key field names are highlighted for clarity and appear at the top of the complete list of fields in each table.

Data schema features

The relational database, which was created in accordance with the project of the canonical data model of the considered subject area, includes only normalized tables connected by one-to-many relationships. In such a database, there are no descriptive data, which are duplicated, their one-time input is provided, and data integrity is maintained using the system's tools.

Using relationships between tables, you can combine data from different tables, which is necessary to solve many tasks of entering, correcting and viewing data, obtaining information on queries and displaying reports. Relationships between tables are established according to the project of the logical structure of the database in question (Figure 2) and are displayed on the Access data schema.

The data schema, in addition to playing the role of a means of graphical display of the logical structure of the database, is actively used in the process of data processing. With the help of the links that are established in the data schema, the developer is relieved of the need to inform the system about the presence of any link every time. Once you have specified links in the data schema, they will be automatically used by the system. Creating a data schema provides an opportunity to simplify the design of multi-tabular reports, queries, forms, and also ensures the maintenance of the integrity of interrelated data when adjusting and entering data into tables.

MS Access DBMS allows you to build a database schema and automatically supports primary and foreign keys.

Database schema(or simply data schema) is an graphically DB. It defines and remembers the relationships between tables. This allows Access to automatically use relationships when designing forms, queries, and reports. The data schema is displayed in a special Data schema window, where tables are presented field lists, and connections - lines between fields in linked tables (Figure 3).

Rice. 3. DB Scheme Student

There are two ways to create a data schema.

The first way presupposes normalized tables, which are linked in the Data Scheme window. The creation of a data schema begins by placing in the window all the tables that must be included in the schema. Then you can start defining pairwise relationships between them.

When establishing links between a pair of tables, it is necessary to select a unique key field in the main table (PC in each table is displayed in bold), by which the link is established. Further, with the left mouse button pressed, this field is dragged to the corresponding field of the subordinate table. If a link is established by a compound key, then it is necessary to select all the fields included in the compound key of the main table and drag them to one of the link fields in the subordinate table.

After establishing a link, the Change Link dialog box opens, in which the link field of the subordinate table is defined for the key field of the main table. For each field of the composite key of the main table, a link with the field of the subordinate table must be established on a separate line... In addition, in the Edit Link window, for each link, you can set the Ensure Data Integrity option, and then set the Cascade Update Related Fields and Cascade Delete Related Records options. . At the same time, Access will automatically set the link type 1M(in the diagram it is denoted as 1:∞). If the tables contain data that does not meet the integrity requirements, the relationship 1M will not be installed, and Access displays a corresponding message in this case.

Second way- getting the data schema from one unnormalized table using the Table Analysis Wizard.

Non-normalized tables contain fields with duplicate values. For such tables, additional memory is consumed, one-time input and correction of data in fields with repeated values ​​cannot be provided, and there is a high probability of errors.

Using the Table Analysis Wizard, Access allows you to automatically or manually analyze a table and split it into multiple interconnected tables without duplicating data. For the obtained tables, the Table Analysis Wizard creates a data schema in which links are established 1M and the parameters for ensuring the integrity are determined. After the analysis procedure, normalized interrelated tables will be obtained for the original table.

However, the Table Analysis Wizard is not always able to correctly separate duplicate data into a separate table. When working with the Wizard, interactive participation of the user in correcting proposals for dividing the table is provided.

3. WORKING WITH DATA IN SINGLE TABLES. DESIGNING SIMPLE QUESTIONS

OPERATING MODES WITH SINGLE TABLES

There are four modes for working with tables in Access: Tables mode, Design mode, PivotTable mode, and PivotChart mode.

In the Tables mode, you work with data located in a single database table: viewing, editing, adding, sorting, etc.

In Design mode, the structure of the table is created or modified, that is, the names of the fields, their types, descriptions, properties and other parameters are set.

In PivotTable and PivotChart modes, it is convenient to perform data analysis by dynamically changing the way it is presented.

There is also additional mode- Preview, which allows you to see the location of the data on the sheet before printing the table.

3.2 CONCEPT OF A QUERY IN A DBMS.
BASIS FOR DEVELOPING REQUESTS

One of the main tools for data processing in a DBMS are requests... Queries allow you to select the required data from one or more related tables, perform calculations and get the results in the form of a table. Through a query, you can update data in tables, add and delete records. Sequential execution of a number of queries makes it possible to solve rather complex problems without resorting to programming.