A guide to database structure and design. Types of Logical Data Models

IN modern world Data is becoming increasingly important, and without exaggeration we can say that the world is driven by data. Therefore, now much attention is paid to the collection, storage, analysis and sale of data. The key to the successful development of a modern business is the accumulation, systematization and use, for example, of information about your customers, such as their needs, purchasing preferences, etc. Such information can help in making informed decisions regarding the practical effectiveness of promotional offers, identifying unprofitable business segments, analyzing demand for manufactured goods or services, tracking the dynamics of trade for individual items and reviewing other key factors. Databases, when used correctly, give us these advantages over our competitors.

If you are the owner of a small business and do not yet use a Customer Relationship Management System (CRM or Customer Relationship Management) in your work, which automates the generation of customer interaction strategies, then the success of your business is subject to certain risks. Remember that your competitors are not asleep!

Let's take a look at which software you can use to build a database tailored to the unique needs of your small business that will collect daily, weekly, monthly or yearly data.

Filemaker Pro 12

This database has undeservedly fallen out of the sight of database administrators and developers for a long time, but has been loved by the business community since its creation. Filemaker Pro, created by Apple, runs on both the Mac operating system and Windows system. The program is an intuitive and very easy-to-use tool for creating your own databases with support for the provision of data on the Internet, which is capable of generating reports in normal and advanced modes, and can be integrated with other database systems.

Microsoft Access 2010

For a long time, the Access database management system Microsoft package Office has been the most popular solution for most small businesses. However, now it is faced with competition from other DBMSs that are easier to use, better integrated with cloud systems, and do not require extensive knowledge in creating, maintaining databases and development software.

If you already have a database, chances are it was built using Microsoft Access. The new 2010 version looks and works better and is easier to use compared to previous versions, for example, with the widely used 2003 version. Despite the fact that this DBMS has begun to be crowded out by competitors, it still occupies a leading position in this segment of the software market.

Oracle Application Express (APEX) database for this business

APEX is a database management system built on the mega-successful Oracle database engine. APEX is available completely free if you are already an Oracle customer and provides a more advanced business application creation system than Microsoft Access or FileMaker Pro. However, using APEX is not as simple as simply entering data into tables, as is done in an Access database.

If you are already using Oracle or anticipate needing more advanced database management capabilities, such as integration with other data systems in the future, or handling very large volumes of data with fast performance, then APEX is the right choice.

Zoho Creator is a relative newcomer to the database world and offers an intuitive cloud-based database system. The Zoho developers have created a truly reliable, easy-to-use system in which you can quickly create a simple database application without much preparation. This became possible thanks to the use of data entry forms, a very good report builder, and integration with other systems, which is often necessary when you already have an existing database created in other DBMSs, or when using the databases of your partners.

When making decisions, a manager at any level is based on the information available to him about the subject of management, therefore, the effectiveness of his work directly depends on the qualitative characteristics of this information, such as adequacy, completeness, reliability, timeliness, consistency, etc.

IN modern conditions Information systems play and will play an increasingly important role in achieving the strategic goals of the organization. This leads to new requirements for information systems and their functions. Such systems are no longer just a tool that provides information processing to departments and end users within an organization. They must now produce information-based products and services that will provide the organization with a competitive advantage in the marketplace.

Information systems and information technologies used within their framework are the result of certain decisions of managers in an organization. However, in turn, systems and technologies dictate their own specific conditions for doing business and change organizations.

And no matter what consultants the manager attracts in this area, the final decisions must be made by him personally. A manager must be able to make the most of potential advantages information technologies. He must have sufficient knowledge to provide general management of the process of application and development of information technology in the organization and understand when additional resources are required in this area or the assistance of third-party specialists.

Since the invention of writing, humanity has been faced with the task of storing data. Record keeping has a long history, but despite the evolution from clay tablets to papyrus, then to parchment and finally to paper, all this time it had one thing in common - information was processed manually.

With the development of civilization, document management threatened to consume all of a specialist’s time - by the end of the 20th century, many companies had entire floors dedicated to storing documents, which, you see, is not so far from storing clay tables in Sumerian archives.

With the advent of computers, the task of document management was simplified - storing documents in electronic form turned out to be simple, cheap and convenient. A key component of this new technology there was software. It has become relatively easy to program and use computers, and it has become much easier to sort, analyze and process data. Standard packages have emerged for common business applications such as accounting, payroll, inventory, subscription management, banking, and document library management.

The reaction to the emergence of these new technologies was quite predictable: large businesses stored even more information and demanded ever faster equipment.

In the course of their activities, industrial enterprises, corporations, departmental structures, government and management bodies have accumulated large volumes of data. They contain enormous opportunities for extracting useful analytical information, on the basis of which you can identify hidden trends, build a development strategy, and find new solutions.

Obviously, providing quick access to most of the data is not that difficult. However, any of us has encountered a situation where finding the necessary document, so wisely saved last month (or year), turns out to be disproportionately labor-intensive. At this point, it becomes clear that the traditional capabilities of file systems are no longer enough for success in the modern world - the world of information technology.

Today, in order to obtain additional competitive advantages, most domestic companies need serious IT support for their business - a company transformation system based on the use of information technology and aimed at increasing the efficiency of the organization.

In the second half of the 90s, many enterprises began to realize that the data at their disposal was a valuable asset, the correct use of which could create competitive advantages. Large companies have been accumulating data about their customers, suppliers, products and services for decades. However, they realized that their data was stored in disparate systems and that this information needed to be integrated to further develop the business. The need to integrate corporate information gave rise to the creation of databases.

This is especially true now when, thanks to the rapid development of e-commerce, Internet-based firms can turn into huge enterprises within a few months, or even weeks. And, as a result, their databases will grow rapidly.

Therefore, a prudent manager should begin investing in IT support without bringing the enterprise to the decision point where his company faces a cost ceiling. The real problem facing the company's top management is organizing the accumulated data archives in a way that would make it easy to find the required information. Finding patterns, trends, anomalies and relevant information in a large database is one of the new, most exciting areas of data management.

For those who have already taken this path, it is obvious that databases can radically change the nature of the work of any organizations positioned in different subject areas, relieving managers from performing routine procedures associated with searching for information in numerous files, paper documents, reference books and standards. This is a new stage in the company’s development, leading it to the next stage of evolution, although often using revolutionary methods.

Reducing time costs is only an indirect effect of automation. the main task development of information technologies in another way - in the acquisition by an organization of fundamentally new qualities that give it significant competitive advantages. This is exactly the case that is worth a lot.

Moreover, today installing and administering databases is a much less complicated process than just a few years ago. Database design and management are largely automated. Software that allows you to solve this problem - create a database, update the information stored in it - and provide convenient access to it for viewing and searching is called a database management system (DBMS).

The database management system creates a specific environment for the user to work on the computer screen (user interface). In addition, the DBMS has certain operating modes and a command system. Information systems are created and operate on the basis of DBMS. It is worth recalling that database management systems are one of the most successful technologies in the entire computer industry. Billions of dollars are spent on database systems and applications every year. Database management systems play an exceptional role in the organization of modern industrial, instrumental and research information systems.

Typical modes of working with a DBMS are creating a database, editing a database, manipulating a database, searching in a database. To operate in each mode, there is its own system of DBMS commands. Any user work with the database is built in the form of an algorithm made up of these commands. Such algorithms can be executed in direct execution mode (the given command is immediately executed) and in automatic execution mode, i.e., in program mode.

When creating a database, the user strives to organize information according to various characteristics and quickly retrieve a sample with an arbitrary combination of characteristics. Database users can be various application programs, software systems, as well as domain experts who act as consumers or sources of data (called end users).

The main feature of a well-built DBMS is its functionality to sharply reduce labor costs for processing almost all internal and external business information of an organization. A database designed in this way allows each department to use the information entered by one user and eliminates the need for company divisions to duplicate data, which leads to a sharp reduction in labor costs. For example, information about a sold product, already at the time of release from the warehouse, becomes equally available to both the sales manager and business applications of general accounting and payroll.

Meanwhile, despite the enormous achievements associated with facilitating the installation, management and use of DBMS, especially those that run on personal computers or workstations, many still prefer to use the file system. There is a tacit assumption that the DBMS must be handled by well-trained, full-time personnel, and that most database users do not have any training in database technology. Users still find it difficult to connect to the DBMS, find the desired directory or database names where data is stored, and formulate queries and update the database. Connectivity and Access Paradigm file system still seem significantly easier to understand.

However, when thinking about the success of his business, a manager should not succumb to such sentiments. It is worth remembering that it is now easy and inexpensive to create a database. Millions of people do this, and you don't have to become a computer operator to do it. A competent IT engineer and a few training workshops for staff is all you need to turn piles of files with hard-to-access information into a modern database. Whereas, by abandoning the advantages of a DBMS for the sake of the immediate convenience of the staff and their reluctance to change the established order, the manager risks remaining the only user of cuneiform in the world who has switched to the phonetic alphabet.

· MS Access DBMS capabilities.

· Modes of working with database objects in MS Access: online mode, design mode.

· The order of constructing expressions in MS Access.

· Operations with data in a database table.

· Purpose and methods of creating various database objects: form, report, request, data access page.

· Using controls in database objects: form, report, query, data access page.

· Using a mechanism to maintain data integrity when creating relationships between tables.

· Tools for automating operations with database objects in the Microsoft Access DBMS.

· Ability to change settings and parameters of the MS Access DBMS.

Microsoft Features Access

You can use Access to do the following:

1. Design of basic IS objects - two-dimensional tables with different types data, including OLE object fields.

2. Establishing relationships between tables, supporting data integrity, cascade updating and deleting records.

3. Entering, storing, viewing, sorting, modifying and retrieving data from tables using various means information control, table indexing and logical algebra apparatus (for data filtering).

4. Creation, modification and use of derived objects of information systems (forms, queries and reports), with the help of which the following operations are performed:

· optimization of user input and viewing of data (forms);

· connecting data from different tables;

· carrying out group operations (i.e. operations on groups of records united by some characteristic), with calculations and the formation of calculated fields;

· data selection using logical algebra (queries);

· Drawing up printed reports on data contained in database tables and queries.

MS Access has extremely powerful, convenient and flexible tools for visually designing objects, and this allows the user to preliminary preparation Quite quickly create a full-fledged IS at the level of tables, forms, query queries and reports.

Microsoft Access provides the ability to open tables, queries, views, stored procedures, functions, and forms in PivotTable and PivotChart views. It is possible to save views in PivotTable and PivotChart modes as data access pages, which can then be viewed by any user on whose computer is installed Microsoft Internet Explorer 5 or later.

Microsoft Access provides powerful, intuitive ways to share Extensible Markup Language (XML) data, regardless of platform, data format, protocol, schema, or business rules. XML is not only the standard technology for transporting data on the Internet; it is quickly becoming the technology of choice for exchanging data between business applications.

Microsoft Access has greatly improved the integration of Access and SQL Server by including advanced SQL database properties in a Microsoft Access project. The use of extended properties in Microsoft Access projects makes it possible to use features such as lookup relationships, value conditions (also known as constraints), text formatting, and subtables.

Technology for working with MS Access

You can start MS Access and shut it down with any of the following: standard methods provided in the Windows environment.

The MS Access processing object is a database file with an arbitrary name and extension .MDB. This file includes the main MS Access objects: tables, forms, queries, reports, pages, macros and modules.

Database development is divided into the following main stages.

1. Determining the purpose of creating a database. The first step in developing a database is to determine its purpose and how it will be used. Consult with future database users. Together with them, formulate the questions that you and they want answered using the database. Create thumbnails of the reports you would like to receive. Gather the forms you already use to enter data. As the purpose of the database is determined, a list of necessary data will begin to form. Knowing this, you can determine what factual data should be stored in the database and what topics this data should be distributed into. The topics must correspond to tables, and the data must correspond to fields (columns) in these tables.
2. Determining the required fields in the database. Each field contains specific factual data. For example, you may need the following customer information: company name, address, city, country, and phone number. You should create a separate field for each type of information. Consider the following when creating your field layout.

· Include all required information. Break information down into minimal logical components. For example, it is convenient to split employee names into two fields - “First Name” and “Last Name”, which will make it easier to sort by last name.

· Do not create fields for data consisting of multiple elements. For example, if you create a Products field in the Suppliers table that contains a list of all products for that supplier, it will be difficult to find suppliers who supply a specific product.

· It is not recommended to include data in a table that is the result of an expression. For example, in a table that contains the fields "Price" and "Quantity", you should not create a field containing the product of the values ​​of these fields.

· Do not create fields containing similar data. For example, if you create the fields “Product!”, “Product2”, and “Product3” in the “Suppliers” table, it will be difficult to find suppliers who supply a specific product. In addition, the database structure will have to be changed if a supplier appears that offers four products. One field for products will be enough if you place this field in the “Products” table and not in the “Suppliers” table.

1. Defining the tables that the database should contain. Each table should contain information on only one topic. The list of required fields will tell you which tables are required. For example, if the Hire Date field is used, it belongs to the employee information topic, i.e. must be contained in the "Employees" table. You will also need the Customers, Products, and Orders tables.
2. Determining the tables to which the fields belong. When deciding which table each field should belong to, consider the following design principles.

· Include each field in only one table.

· Do not include a field in a table if adding it would cause the same data to appear in multiple records in that table. If a table field appears to contain a lot of duplicate data, the field is probably placed in the wrong table. For example, if you include a field containing the customer's address in the Orders table, this information will be repeated in many records if the customer makes different orders. If you place an address in the Clients table, it will appear only once. Data stored in only one table is updated only once. This is more efficient and, in addition, eliminates the possibility of duplicating records containing different information.

1. Defining fields with unique values ​​in each record. To link information stored in different tables in Microsoft Access, for example to link a customer to all of his orders, each database table must contain fields or a set of fields that uniquely identify each record. Such a field or set of fields is called primary key .
2. Defining relationships between tables. After you have broken up your information into tables and defined your primary key fields, you must choose how Microsoft Access will reunite the related information. To do this, you need to define the relationships between the Microsoft Access database tables. It is useful to examine relationships in an existing database with a well-organized structure, for example, in the Northwind training database.
3. Improving the database structure. After creating the necessary tables, fields and relationships, you need to review the database structure again and identify possible shortcomings. It is advisable to do this at this stage, while the tables are not filled with data.

Create tables in Microsoft Access, create relationships between them, and enter enough data into the tables to check the structure. To test your database relationships, see if you can create queries to retrieve the information you need. Create draft forms and reports and see if they display the data you expected. Search for unnecessary duplicate data and eliminate them.

1. Entering data and creating other database objects. If the table structures meet the requirements, then you can enter all the data. You can then create all the necessary database objects - queries, forms, reports, data access pages, macros and modules.
2. Using Microsoft Access analysis tools. There are two tools in Microsoft Access that can help you improve your Microsoft Access database structure. The Table Analysis Wizard allows you to analyze the structure of a table, suggest appropriate new structures and relationships, and split the table into new related tables if that makes sense. The Performance Analyzer examines the entire database and makes recommendations for improvement, and can also implement those recommendations.

Database creation

There are two methods you can use to create a database in Microsoft Access. The simplest way create a database - use the Database Wizard to create all the necessary tables, forms and reports. It is also possible to create an empty database and then add tables, forms, reports and other objects to it - this is the most flexible method, but it requires a separate definition of each element of the database. In both cases, the created database can be changed and expanded at any time.

To create a new database, select from the menu File team Create, then in the taskbar Creating a file select an option New database. After this, a standard filer appears on the screen, in which you should open the desired folder and set the name created file Database. For example, "group.MDB". Having created the file, Access opens an empty database window, and in this window you can carry out all operations - create and manipulate database objects. MS Access is a multi-window application, but only one database can be open at any time. Its window is the main document window in Access, and closing it means closing the corresponding .MDB file.

Rice. 1. Database window

A database window generates many child windows of objects (tables, queries, forms, etc.), and each such window can be closed autonomously - by any of the standard Windows ways. Additionally, without closing the window, you can save an object (such as a table layout) whose window is on the screen and give it a name - just as you do with files: with the command File-Save or File-Save As....

You can work with the window of any object (child window) either in operational mode(for example, enter or view data in a table), or in design view (for example, change the layout of a table).

Basic concepts of MS Access. MS Access Objects

As can be seen from Fig. 1, an Access database can have the following objects: tables, queries, forms, reports, pages. A table is a basic MS Access object. Data should be stored in tables, with each table containing the same type of information. All other database objects are derived and are created only on the basis of previously prepared tables.

A form is not an independent MS Access object: it simply helps us enter, view and modify information in a table or query. Queries and reports perform independent functions: select, group, present, and print information. Data access pages are a special type of web page designed to be viewed and worked over the Internet or intranet with data stored in a Microsoft Access or Microsoft SQL Server database. Using the page, users can enter, edit, and delete data from the database.

Every MS Access object has a name. Microsoft Access has the following restrictions on field, control, and object names (Table 1).

· the name must contain no more than 64 characters;

The name can include any combination of letters, numbers, spaces and special characters except for the period (.), exclamation point(!), superscript (") and square brackets ();

· must not begin with a space character;

· must not include control characters (with ASCII codes from O to 31);

· must not include straight quotation marks (") in the names of tables, views, and stored procedures in a Microsoft Access project.

Although spaces are acceptable within field, control, and object names, they can cause conflicts in Visual Basic programs under some circumstances.

When defining a name for a field, control, or object, it is useful to check whether the name is the same as the name of a property or other element used by Microsoft Access (for Russian names, this situation may occur if it is the same as the name of a property or user-defined function).

Table 1 Data types that fields can have in Microsoft Access

Data type	Usage	Size
Text	Text or a combination of text and numbers (such as addresses), as well as numbers that do not require calculations (such as telephone numbers, inventory numbers, or postal codes)	Up to 255 characters
Numerical	Numeric data used for mathematical calculations, with the exception of financial calculations (for which the type "Cash" should be used). To more accurately determine the type of number, use the FieldSize property	1,2,4 or 8 bytes. 16 bytes for replication codes (GUID) only
MEMO field	Long text or numbers, such as notes or descriptions	Up to 64,000 characters
Date Time	Dates and times	8 bytes
Monetary	Currency values. The currency type is used to prevent rounding during calculations. Assumes up to 15 characters in the integer part of the number and 4 in the fractional part	8 bytes
Counter	Automatic insertion of sequential (increasing by 1) or random numbers when adding a record. This field type is convenient to use for the primary key of a table. Access automatically selects whole sequence numbers (1,2,...) as values ​​for such fields. In the future, the number assigned to the record when it was created does not change (regardless of deletion, insertion of new records, etc.)	4 bytes. 1 6 bytes only for replication codes (GUID)
Logical	Fields containing only one of two possible values, such as Yes/No, True/False, On/Off	1bit
OLE Object Field	Objects (for example, Microsoft documents Word, spreadsheets Microsoft Excel, pictures, sounds, and other binary data) created in other programs that use the OLE protocol. Objects can be linked or embedded in a Microsoft Access table. To display an OLE object in a form or report, you must use an attached object frame	Up to 1 gigabyte (limited by disk space)
Hyperlink	A field that stores hyperlinks in the form of a path or URL	Up to 64,000 characters
Substitution Wizard	Creates a field that allows you to select a value from another table or from a list of values ​​using a combo box. When you select this option in the list of data types, a wizard is launched to automatically determine this field	The same size as the primary key, which is also a lookup field; usually - 4 bytes

Work with each database object is performed in a separate window, and two modes of operation are provided:

1. online mode, when information is viewed, changed or selected;

2. Design mode, when the layout, structure of an object (for example, table structure) is created or changed.

In addition, the database file includes another document that has its own window - Data Schema. In this window we create, view, change and break relationships between tables. These connections help us monitor data and create queries and reports.

In the database window, under the standard toolbar, there is a panel with the “Open”, “Design” and “Create” buttons, as well as buttons for changing the type of presentation of database objects. The left side of the window displays a list of tabs (by the number of Access objects) with roots: Table, Query, Form, Report, Pages, Macro And Module. If any tab is selected, the database window displays a list of existing objects of this type in this database. For example, if you select the tab Table, then the window displays a list of tables in the open database. To open a table, select its name in this list and click the “Open” button. To include in the database new table, you need to click the “Create” button. To correct the layout of an existing table, select its name in the list and click the “Design” button. The same operations are performed on all other Access database objects.

Set of items horizontal menu and the composition of the toolbars depend on the type and mode of the document window, which is in this moment actively. For example, a table window in online mode has buttons “Cut”, “Sort Ascending”, etc., and in design mode - buttons “Properties”, “Define Key”, etc. Working with toolbars follows the Windows standard.

Note. Fields of the Numeric, Date/Time, Currency, and Boolean types have predefined data output formats. The output format can be selected in the Field Format Properties cell. You can also create your own output formats for all data types except OLE objects.

Database development technology

Let us define the purpose of creating this database - storing information about students. As a basic database object, we will define a table in which the following data about students will be stored: personal file number, last name, first name, patronymic, date of birth, home address, class. To place them, we define table fields of the same name. Let's set the field as the table key Personal file number.

To create a database, launch MS Access and select from the menu File team Create, then in the Create File taskbar, select New Database. After that in the window New Database File open the desired folder, for example, new folder, and specify the name of the database file to be created, for example, “Group.MDB”.

Creating a table

To create a table, select the tab in the list of tabs on the left side of the database window Table. After this, the database window will display shortcuts for table creation options: in design mode, using a wizard, and by entering data. By double-clicking on the line “Creating a table in Design mode”, open the table window in Design mode, as shown in Fig. 2.

Rice. 2. Defining table field parameters in Design mode

At the top of the window is a table layout that you can create or modify, which is simply a list of fields indicating the field name, data type, and description.

In the Field Name column, enter a custom field name, and in the next column, specify the data type for this field. The data type can be selected from the drop-down list. As soon as the cursor is in the Data Type column, the Field Properties form (characteristics of this field) appears at the bottom of the window. It is a list of properties (on the left is the name of the property, on the right is the value of this property) with a hint window for each property. The list of properties changes depending on the data type that is currently displayed in the Data Type column. By clicking on a value field in the properties sheet, you can change that value (within the limits allowed for that data type). Most values ​​are accepted by the system by default; many properties can be changed independently. Some values ​​can be selected from a drop-down list.

When choosing a property value, it is essential to follow these guidelines:

· for text and numeric fields, you must specify the field size, and for text this is the allowable length of the value (for example, 20 or 40 characters), and for a number - the computer representation format (byte, integer (two bytes), long integer, etc. );

· for the field Date Time It is imperative to specify the format so that the system knows how to process the input data. For example, if you select Short date format, the system will expect you to enter exactly the date (in the Russian version - DD.MM.YYYY), and if you select Short time format, you will have to enter HH:MM (hours and minutes) in this field;

· as the value of the Value Condition property, you can specify a logical expression that should accept value True(“True”) when entering data in this field. In the following property, you can record an arbitrary error message that will be issued by the system, for example: “This field value is invalid.” In the Required field property, you can specify “Yes” (blank values ​​are not allowed) or “No” (blank values ​​are allowed);

· if the primary key of your table includes one field, in the Indexed field property for it, select: “Yes, matches are not allowed,” and then click on the “Define Key” button in the toolbar (with the key image). This will define the primary key of your table (and prevent you from entering records with a duplicate primary key value).

So, following the above guidelines, define the table fields. In the Field Name column, enter the name “Personal File No.” To determine the data type of this field, click the arrow in the Data Type column, expand the list of possible data types and select the Text option. In the Field Properties design window area, select the Field size and determine maximum amount The characters to be entered in this field are 10 characters.

Please note that as you select various field property options, a tooltip appears on the right side about the purpose of the option.

Proceeding similarly, enter the following data about other fields of the table (Table 2).

Once you have completed entering descriptions of the table fields, define the primary key. To do this, specify the field Personal file number, click the Key Field button on the Standard toolbar.

Table 2. Data about table fields

Note. It is not necessary to define a primary key field, but it is advisable. If a primary key has not been defined, Microsoft Access will ask you if you want to create a key field when you save the table.

Selecting a team Table mode on the menu View, switch the display of the created database table to table display mode. Be sure to save the table under the name Students.

Operations with data in a table

Data input. After selecting the Students table in the window, click the “Open” button. Place the cursor in the field Personal file number and enter the number value, for example, P-69. When you have finished entering the field value, press the key Tab to move to the next field. In the remaining fields of this entry, enter the remaining information in the first entry.

There are several ways to share an Access database, depending on your needs and resource availability. This article describes the available options and the benefits of each, and provides resources with additional information about working methods.

To change the database structure, you must have Access installed on your computer.

In this article

Sharing data using network folders

This is the simplest option with minimum requirements, but it provides the least functionality. In this method, the database file is stored on a shared network drive and all users use it simultaneously. Because all database objects are used simultaneously, multiple users can change data at the same time, limiting reliability and availability. Performance may also be affected because all database objects are sent across the network.

This option is suitable if the database will be used by several people at the same time and users will not need to change the database structure.

Note: This method is less secure than other database sharing methods because each user has a complete copy of the database file, which increases the risk of unauthorized access.

Sharing a Database Using a Network Folder

If there is no shared network folder, you need to configure it.

For more information about this, see the help for the operating system of the computer that will be used to share the database. If the shared folder is on network server, you may need help from your network administrator.

The Access application must be configured to open in shared mode on all users' computers. This mode is the default, but this needs to be checked: if a user opens the database in exclusive mode, other users will not be able to work with the data. Follow the steps below on each computer.

1. Launch Access and on the tab File select item Options.

   In the window Access Options select item Client Options.

Copy the database file to shared folder. Next, configure the file attributes to allow read/write access to the database file. To use the database, you must have read and write access to it.

On each user's computer, create a shortcut to the database file. In the Shortcut Properties dialog box, specify the path to the database file in the property Target, using a UNC address instead of the connected drive letter. For example, instead of the path F:\sample.accdb indicate the path \\computername\shared.accdb.

Note: Users can perform this action themselves.

Sharing a Partitioned Database

This method is useful if you don't have a SharePoint site or database server. General access shared databases can be accessed over the network or through a SharePoint site. When a database is partitioned, it is reorganized into two files: the server database, which contains the data tables, and the client database, which contains all other database objects (for example, queries, forms, reports). Each user interacts with data using a local copy of the external database.

Benefits of Database Partitioning

Increased productivity. Only data is shared over the network, not tables, queries, forms, reports, macros, or modules.

Increased Availability Database transactions, such as editing records, are faster.

Improved Security. Users access the back-end database through linked tables; It is less likely that attackers could gain unauthorized access to data through the client database.

Increased reliability If a user encounters a problem and the database closes unexpectedly, any damage to the database file is usually limited to the copy of the client database that the user has open.

Flexible development environment Each user can independently develop queries, forms, reports, and other database objects without affecting the work of other users. Additionally, you can develop and distribute a new version of the client database without disrupting access to data stored on the database backend.

If this method works for you, go to the instructions in the article Partitioning an Access database.

Share a database on a SharePoint site

If you have a server running SharePoint (especially with Access Services), there are several possible good options. SharePoint integration helps provide easier access to your database. When you publish a web database, Access Services creates a SharePoint site that contains the database. All database objects and the data itself are moved to SharePoint lists on this site.

The published database is hosted on the Internet. You can create Web forms and reports that run in a browser window, as well as standard Access objects (sometimes called client objects to distinguish them from Web objects). To use Access client objects, you must install the Access application, but all database objects that are stored on SharePoint are shared.

Note: If you have Access installed on your computer, you can use client objects from a web database, not just web database objects.

Access Services provides a platform for creating free data that can be used on the Internet. Web databases are built and published using Access 2010 and SharePoint, and you can then use the web database through a web browser.

Forms, reports, and interface macros run inside the browser.

If you are using a web database, the data is stored in SharePoint lists: All tables are converted to SharePoint lists and records become list items. This allows you to control access to your web database using SharePoint permissions.

Queries and data macros run on the server: All SQL processing is done on the server. This improves network performance because only result sets are transmitted over it.

Saving a Database to a Document Library

The database can be saved in any SharePoint document library. This method is similar to saving the database to network folder and provides a convenient way to control access to the database. When linked to SharePoint lists, only the data is shared, not the database objects. Each user gets their own copy of the database.

For example, if your SharePoint site has lists that track customer service issues and store employee data, you can create a database in Access that serves as the interface to those lists. You can create Access queries to analyze these issues and Access reports to format and publish written reports for team meetings. If users have Access installed on their computers, you can share Access queries and reports for a SharePoint list using the menu Performance. When viewing a list on a SharePoint site, users will be able to find and open queries, reports, and other Access objects from a menu Performance. If users don't have Access, they can still consume data from lists using SharePoint views.

Open the database you want to share.

On the tab File select item Save as.

Note: If you're using Access 2010, select items File > Save and publish > Save Database As > SharePoint.

In the dialog box Save to SharePoint Browse to the appropriate document library.

Check the database file name and type, change them if necessary and click the button Save.

For more information, see Publish to Access Services and Import and link data to a SharePoint list.

Share a database by linking to SharePoint lists

This method has the same benefits as using a split database and allows each user to modify their own copy of the database because sharing data is accessed through a SharePoint site. While this does not provide the benefits of publishing the database to a SharePoint site, it does provide the benefit of having the data centralized. Because the data resides in SharePoint lists, it can be shared over the network using SharePoint features.

This method includes three main steps.

Moving data to SharePoint lists.

Create links to these lists.

Database file distribution.

You can use the SharePoint Site Migration Wizard to complete the first two steps, and last action can be accomplished using any available means.

Use the Export Tables to SharePoint Wizard

On the tab Working with databases in Group Data transfer click element SharePoint.

Note: This element is only available if the database file is saved in ACCDB format.

Follow the wizard to export tables to SharePoint; specifically, specify the SharePoint site location. To cancel the process, click the button Cancel.

To view more information about the migration, on the last page of the wizard, select the checkbox Details.

This page contains information about which tables are associated with the lists, as well as backup location information and the database URL. It also displays a warning if there are problems with the migration and indicates the location of the log table where you can view more information about the problems.

When all the wizard steps are completed, click the button Ready.

If the wizard displays a warning, you should review the log table and take the necessary action. For example, you might want to unwrap some fields or convert them to other data types that are compatible with SharePoint lists.

Note: To view lists on a SharePoint site, click the Quick Launch button Lists or select item View all site contents. You may need to refresh the page in your web browser. To display lists in the Quick Launch pane on your SharePoint site or change other settings (such as turning on version tracking), you can change the list settings on your SharePoint site. For more information, see Help for your SharePoint site.

Sharing a Database Using a Server

You can share a database using an Access application and a database server (such as SQL Server). This method provides many advantages, but it requires additional software - a database server.

This method is similar to database sharing because the tables are stored online and each user has a local copy of the Microsoft Access database file that contains links to tables, queries, forms, reports, and other database objects. This option is used if the database server is available and all users have Access installed. The benefits of this method depend on the database server software used, but general case these include user accounts and selective access to data, excellent data availability, and convenient built-in data management tools. Moreover, most database server applications work fine with earlier versions of Access, so it is not necessary for all users to be running the same version. Only tables are shared.

Benefits of Sharing a Database Using a Database Server

High performance and scalability In many cases, a database server provides better performance than a single Access database file. Many database server products also provide support for very large databases of approximately 500 in size per interval (2 GB) for an Access database file (two gigabytes). Database server products typically work very efficiently by processing queries in parallel (using multiple native threads in a single process to process user queries) and also minimize additional memory requirements when adding new users.

Increased Availability Most database server products allow you to create backups database while it is being used. This way, users don't have to forcefully close the database to Reserve copy data. Moreover, the database server typically handles concurrent editing and record locking very efficiently.

Increased security It is impossible to completely protect a database. However, database server products offer strong security that can help you protect your data from unauthorized use. Most database server products offer account-based security, allowing you to specify who can see which tables. Even if the interface is incorrectly gained, unauthorized use of data is prohibited by account-based security.

Automatic recovery options In the event of a system failure (for example, a crash operating system or power failure) some database server products have mechanisms automatic recovery, which restore a database to its latest consistency state within minutes, without the need for a DBA. participate.

Processing on the server Using Access in a client-server configuration helps reduce network traffic by processing database queries on the server before sending the results to the client. Server processing is usually more efficient, especially when working with large data sets.

Basic steps for using Access with a database server

Factors to consider when choosing a method

Method requirements	Database partitioning	Network folder	SharePoint site	Database server
Necessity of availability database server software
The need for SharePoint
Necessity availability Access Services on SharePoint Server			Depends on the scenario: Linking to lists and saving to a document library does not require Access Services; Publishing as a web database or web application requires Access Services.
Data Availability		Suitable for small groups if data changes little	The best. Suitable for offline use cases.	The best
Safety	Depends on additional measures	Least secure way	The best	The best
Flexibility	Flexible way. New database functions can be easily developed without disruption. Users can change the structure in their own copy.	Less flexible way. Development can be done using an offline copy of the database, which is then replaced. There is no possibility for users to individually change the database structure.	Flexible way. Use SharePoint permissions to control access and change design. Allows some database objects, such as forms, to be used in a browser-based manner.	Flexible way. New database functions can be easily developed without disruption. Users can change the structure of objects in their own copy.

1. The concept of a DBMS.

2. Relational databases.

3. Types of databases.

4. Types of database structures

The need to store data in the form of certain structures, that is, to organize information about certain objects of the surrounding world, has always been tangible for humanity. In this case, an object is understood as either an object or a more abstract concept (for example, the process of producing something).

Adding an object to the database is only half the battle. It still needs to be characterized somehow, to associate a certain meaning with it. And here we need to introduce the concept of “given”. A given is a certain indicator that characterizes an object and gives it a certain meaning. Moreover, it is not necessary that the object be defined by one data - there can be many of them. Imagine that you are dealing with a hacker structure. Hacking is an object. But the data is already hacker trends, experience of illegal activities, the number of exploits written and hacked machines, etc. In other words, data is the characteristics of a certain object. This is what interests the client most when accessing the still-to-be-future database.

Creating a multi-megabyte file with tons of information (which, by the way, may well be redundant) is not a solution to the problem. A person loves comfort, therefore, in order, for example, to get information on a major hacker, the client will only be required to provide the hacker’s nickname, and then comprehensive information about the cybercriminal will become a weapon of justice. Organizing such a system is very difficult; more than a dozen years passed before individual files became worthy databases (a database in an ini file is also stylish - Dr.). Now everything has become much easier thanks to the existence of structured files - databases and various models data organization.

Actually, a model is the basis on which a particular database is based. In one model or another, connections between data, types of input data, methods of storage, management, etc. are defined. Linking data with application programs provided through a DBMS or using database management systems.

So, a DBMS is a set of language and software tools designed for creating, maintaining and sharing a database with many users. In other words, with the help of a DBMS, anyone (if they have certain rights, of course) will be able to access the database and get from there the information they are interested in.

Relational databases.

This or that DBMS depends on the model that forms the basis of the database. Nowadays, two models have become the most common: relational (relationship model) and object-oriented (object model).

Let's start with the relational model. Back in 1969, American mathematician Dr. E.F. Codd (E.F. Codd) analyzed the situation that had developed by that time using databases and came to the conclusion that things were bad. All models available at that time had significant shortcomings: data redundancy, processing complexity and lack of security for storing information, etc. After painful reflection, Codd decided to create his own model - a relational one. For those who have been maliciously skipping English, let me remind you that relation is translated as “attitude” or simply “table”. The brilliant doctor simply implemented data storage in tabular form, that is, he organized such “storages” in the form of logical structures (physical storage methods can be any). Thus, Codd was able to achieve clarity in the presentation of information and ease of processing. Thanks to the achievement of this genius, in order to generate a data table, it became enough to execute a certain logical query that obeys the laws of Boolean algebra. Among the data manipulation operators, there are at least three operations: extracting rows (SELECT), extracting columns (PROJECT) and joining tables (JOIN). As a result of these actions we get a table. And a simple conclusion from all this: the result of any operation in the relational model is an object of the same kind as the object on which the action was performed.

This is the main property of the described model.

In addition to the basic knowledge, we will need the basic definitions that apply to this model: data type, attribute, tuple, relation, and primary key.

Type d data– a definition that corresponds to the concept of type in programming languages. In other words, for the relational model we can note such basic types as “integers”, “strings”, “characters”, “floating point numbers”, “date” and “money” (where would we be without money these days :)) .

Attribute is a column in a table containing data. For example, if the screen contains information about hacker trends, exploits, and experience, then all of these columns are attributes.

Cortege– a row in a table with data. Thus, comprehensive information on a specific hacker is a tuple.

Attitude– the table as a whole. The description of the data types used in the table is called the relation header, and everything else (the data itself) is called the relation body.

Primary To sunroof– a minimum set of attributes (columns) that will determine the unique uniqueness of each tuple (row) in a relation (table). When creating a database, you should be very careful about specifying the primary key - in our example, the hacker's nickname will not be enough (what if someone wants to take the nickname of their idol? :)). It happens that for authentication an additional field is introduced with a sequence number, which will be uniquely different for each line. But no one forbids choosing two or three attributes for the primary key: whatever you want, as long as this action is logically justified (such a series of attributes will be called a composite primary key).

To achieve effective database management, data connectivity must be ensured. Simply put, you need to be able to connect two or more tables in the database (if they exist there, of course). For this purpose, the so-called “foreign key” was invented, which is an attribute (or set of attributes) in one table that is of the same type as the primary key of another. But you must also comply with the condition that each value in a column of one table must match some value in another. The essence of this definition is after my explanation of possible data connections.

In DBMS theory, there are three types of relationships: one-to-one, one-to-many and many-to-many. I will tell you in detail about each type.

1. One-to-o bottom. This type of relationship is used when the primary key of one table refers to the key of another. To make it clearer, I’ll give an example: let’s say we have three tables in a hacker database. The first is information about the hacker: date of birth, gender (girls can be hackers too ;)) and ICQ. The second is hacker trends (type of trend, its complexity and initial investment). Well, the third is the type of Internet access (technology, access speed, security assessment). All these tables cannot be combined into one, since as a result of the lack of connection between data on Internet access and hacker trends (and not only about them), we will get confusion. And when the connection is implemented in the form of three different tables (using a primary key - a sequence number), both high processing speed and data orderliness are ensured.

2. One- To O- m legs. The most typical connection. Implemented when copying the primary key of one table to another. In this case, in the second table this key is called external. Unclear? Then I’ll turn to an example again. Let's take two tables - with information about the hacker (the "Hackers" table) and about the relationship with the characteristics of the exploits that he wrote (the "Exploits" table). Essentially, they are connected by a one-to-many mechanism. Indeed, each hacker can be the author of several exploits (this often happens), but each exploit can be written by one and only one author (even when working together in hack groups, one person is working on a particular exploit). Here, the hacker's nickname is used as a foreign key in the "Exploits" table, and the name of the exploit is used as the primary key. At the same time, the foreign key “hacker nickname” is the primary key in the “Hackers” table, and was introduced here intentionally to connect the two tables and organize the search for the necessary information. By the way, the “Exploits” relation will not necessarily consist of only one attribute - you can add characteristics of the operating systems to which the exploit is applicable, the number of targets, type (local or remote), etc.

3. Many-To O-m legs. The essence of this type of relationship is that a key in one table is associated with a key in another and vice versa. Things are very bad with this type in the relational model. More precisely, there is no way to directly implement this connection at all. To get around this drawback, a classic solution is used: an intermediate relation is added, which will be related in a one-to-many way to both the first and second tables. Again a clear example. We have two relationships: information about hackers and data about servers that were once hacked. If you think about it, we have the following structure: several servers can be hacked by one attacker (this often happens in life), and several hackers can settle on one server (simultaneously or sequentially) if the admin does not patch the bug in time. To implement a similar scheme in a relational database, we will add an intermediate relation of two fields: the hacker's nickname and the server address. So this auxiliary table will have a one-to-many relationship with both the first and second relationship. Of course, this will increase data redundancy, so experts recommend avoiding such connections.

Each database model has its own control language. For the relational model, this language is SQL (Structured Query Language, or structured query language). The creators of this language sought to bring their brainchild as close as possible to the human (English) language and at the same time fill it with logical meaning.

The SQL language greatly facilitates the work of those who constantly deal with relational DBMSs. Strictly speaking, without this structured language, many unfortunate people would have to write a program, for example, in C. Imagine: in order to fully work with a table, you first need to create this object, then program procedures for accessing it (extracting and adding rows). To get rid of such hemorrhoids, the DBMS developers took care of creating the SQL language.

All SQL queries are very similar to the logical conditions of Boolean algebra (those who haven’t skipped math will understand me :)). You will see this for yourself if you look at the sidebar with the main commands of the language.

As already mentioned, there are other types besides relational. In particular, object-oriented ones. Naturally, for such databases a different query language will be used.

In most object-oriented databases there is a simple GUI, allowing the user to access objects in a navigation style. At the same time, the principle of encapsulation is ignored: no one will forbid you to see the insides of objects directly. But, as experts say, the navigation style in OODB is in some sense a “step back” compared to query languages ​​in relational DBMSs. And painful searches best language requests to the OODB are still ongoing.

The main database access languages ​​are still based on simple SQL syntax and have some kind of extension that applies to objects. Examples of such languages ​​are ORION, Iris and O2 Reloop.

As you can see, the database market is famous for more than just the relational model. Nowadays, developers are trying to expand their software products various innovations, adding object-oriented add-ons to the existing relational DBMS core. In addition to this, the SQL query language is also modified. SQL3 already has specific methods for working with OODB, but their implementation still leaves much to be desired.

For the needs of an ordinary person (that is, you), relational DBMSs, which are used everywhere, are quite sufficient. This is the popularly beloved MySQL, and the less beloved Access, and MSSQL. There are a lot of similar control systems, decide and choose the one that suits your heart best. And, as always, this unique SPECIAL issue will help you make this difficult choice;).

Types of databases.

What types of databases are there? In most cases, programmers' solutions are limited to two types: local and client-server. In the first case, you get an all-in-one shampoo. In the second, we separate the data and the client application and get two layers.

However, there has been a distinction of the third level for quite a long time, and it is the three-level model that everyone bypasses, fearing its complexity. In this article we will look at each model separately with all their advantages and disadvantages.

LOCAL BASE

The simplest database is local. In this case, the database and the program are located on the same computer. The connection to the database file occurs through a special driver or directly. The driver can only process simple queries of the 1992 SQL standard and provide data to the program or save changes to a table. All other manipulations can only be performed by the program. This way, the logic, data, and application work as one unit and cannot be separated.

The brightest and most common representatives of this kind of databases are Dbase (files with the extension .dbf), Paradox (extension .db) and Access (extension .mdb). The Dbase and Paradox formats are not even databases, but tables, because only one data table can be stored in one file. Indexes that speed up searching and perform sorting are located in separate files. Thus, a single database can consist of many files, and this sometimes leads to certain problems when delivering the application to the end user.

Access files are a hybrid of tables and databases. Here, all tables and indexes are stored in one file, which is much more convenient to manage. In addition, the Access database management environment is the most convenient and available in any MS office package. Otherwise, MS Access has the same shortcomings as other representatives of this class.

The main drawback of local databases, as the humorist M. Zadornov says, is “they are stupid.” Yes Yes. The quality and speed of access directly depends on the driver. Most of them did not have SQL query optimizers or any caching. The hardware capabilities were used minimally, so on large databases queries are executed extremely slowly.

Dbase and Paradox tables were designed too long ago and their weakest link is their indexes. These tables have no transactions and no corresponding log. After adding new entry, if the driver did not have time to process changes in the indexes and an error occurred (the light went out or a freeze occurred), then the index collapses and you have to use special utilities or reform the indexes to restore it. IN Access databases I didn’t have such problems, because the indexes are better protected in them.

What is a broken index? An index is a column in which all row values ​​are necessarily unique. Most often, a simple counter is used for these purposes. Let's say the user added a record and the counter assigned the value 195 to it, but the counter value itself did not change. When adding the next entry, the counter again tries to give us the number 195, but since such an entry already exists, an error occurs. This is an index violation, and the treatment for it is quite simple (but tedious) - to reform the index.

NETWORK DATABASE

Why are local databases called local? Yes, because only one user works with the data and because the database and the program are located on the same computer. For small projects this is fine, but for large volumes of data a single operator will not be able to handle the task and will require several people to work on the shared data.

Network databases were designed to solve such problems. In principle, these are the same local databases, only they are posted on network drive server (this could be simple file server or a computer with balls), and several clients access one database over the network.

Let's see how the database is accessed. The program and driver are on the client, and the data is on the server or simply on remote computer. How does the program receive data? The client sends the driver an SQL query that must be executed, but the data is located remotely! To process the request, the entire necessary table (in the case of Access, the entire database, because everything is in one file) is downloaded to the client’s computer, where the driver processes the data.

I would beat the one who came up with such technology, because this is a real mockery of the system. Can you imagine what will happen if you need to run a query on a 1 GB database with a 34 Kb/s telephone connection? It's the same as forcing

extract oil through a milkshake straw.

But some Russian companies (I won’t point fingers) provided us with network solutions based on dbf files in the field of accounting, office work and economics. This is already a mockery. I was asked several times to restore dead databases of a warehouse program after the tools built into the program could not cope with the task.

But the worst behavior began to be indices. For Paradox tables, if they were on a shared Win95 disk, I had to repair the indexes at least once a week. When I moved the database files to a network drive on a NetWare 3.11 server (this was around 1998), the problems with indexing problems immediately disappeared (probably because this is really a server, and not clumsy Windows 9x).

At network connection multi-use was incomplete. Changes made by one user were not visible to others; it was necessary to restart the program or reconnect, because it was at the moment of connection that the program sucked all the data

CLIENT-SERVER

Having failed with network databases, they finally decided to divide the monotonous model into two levels - the application and the database. Now a database is not just a table with data, but an entire engine whose tasks include not only storing data, but also processing queries.

In client-server technology, the driver has already changed its purpose, and now it only needs to know how to connect to the server and send it a request. The rest is left to the server. This technology greatly reduces traffic, especially with good programming. Let's say the user wants to see all the data in which the name of a certain column contains words starting with the letter "A". The client just needs to send the server the following text:

FROM Table name

WHERE Column LIKE ‘A%’

The database server, having received a request, parses it and comes up with an optimal execution plan, in this case, searching for the required rows.

Having received the necessary data, the server returns only this and nothing else. Thus, the client can request the necessary data from the server at any time and there will be no need to drive the entire database over the network. With a well-built application and optimal queries, the client will be able to work with a database of any size, even through a 56 Kbps modem. Not bad? The main thing is to request only what you need, and in small pieces.

CLIENT-SERVER FEATURES

Client-server database capabilities vary by manufacturer. The simplest options are provided by databases such as MySQL. In them, the server has a built-in request processing engine and basic capabilities for ensuring security and distribution of rights.

In more reputable client-server databases (MS SQL Server, Oracle, etc.)

There are the following additional features:

1. views – we will discuss them in more detail in the security article;

2. triggers – functions that can be called on certain events (insertion, change and deletion of data); these functions can carry out some logic to ensure data integrity;

3. replication – merging databases (let’s say a company has two offices and each of them has its own database; by setting up replication, both databases can automatically merge into one at the main office or exchange changes on a schedule);

stored procedures and functions that are executed on the server at the slightest request of the client and can contain entire routines with logic that will perform any actions; To write such programs, not just the SQL language is used, but its extension - Transact-SQL (for MS databases) and PL/SQL (for Oracle, etc.).

The list of features depends on the specific database, its sophistication, and may be more or less.

INDICES ON THE SERVER

Due to the presence of transaction management in server databases, you can forget about problems with indexes. Let's say a user added a record. At this moment, a transaction (implicit) begins, during which all necessary actions to save data are performed. If something goes wrong and the saving does not go through to completion, all changes are rolled back and nothing in the server’s operation is disrupted.

Transactions can also be explicit if the programmer himself indicates where the beginning and end are, and if several operations of changing or adding data can be performed in them. In this case, the server, if an error occurs in the specified block, will roll back any changes to all operations made during the execution of the explicit transaction.

In local databases, indexes are stored linearly. It's like a column of ordered data, and for rows it's the same as putting all the words in alphabetical order. Of course, such an index simplifies the search. When scanning by index occurs and when the program sees that more words have already been found than specified in the search condition, scanning can stop and will not have to scan the entire database. For example, let's look for the word "Lampshade". It will be somewhere at the beginning, and to find it, you only need to scan the beginning of the table, no further than all the words starting with the letter A. Due to the fact that the data is ordered, we can be sure that all other words will start with the letters B, C, etc.

In the case of a server database, indexes are most often (depending on the database and index type) stored a little differently - in the form of a tree. How many words must be checked to find the word "anchor" in a database using a linear index? In fact, almost everything. When storing an index in a tree - no more than for the word "Lampshade". To explain the tree index, let's consider a classic problem (in reality everything is a little more complicated, but the idea is the same). The alphabet is stored at the very top of the tree. The program finds the letter A and goes down one level. Here she finds all the words starting with the letters A, B and moves even lower. And so on until the right word is found

Thus, even if the desired word is at the very end, searching for it will not take much longer than searching for a word from the beginning of the table.

THIRD LEVEL

Many programmers I know are only able to work with a two-tier model, that is, with client-server applications. Not because they don’t know anything else, but because they simply don’t see the advantages of the three-level model and don’t want to suffer with unnecessary problems, but in the future, when supporting programs, three levels, in theory, can save them from unnecessary anal diseases .

I worked in one company (let’s not poke a pitchfork at it), which had several offices across Russia, and in each of them there was a fleet of 20-30 computers. In the Moscow office this figure exceeded a hundred. Corporate programs were updated every two weeks (changes, additions, etc. were made). During the updates, poor admins worked on Saturdays to patch the software on each machine and make sure it was functional. How to solve this problem?

The simplest thing is to use a three-tier system: client, logic server (nerds like to say “business logic”) and application server. In such a system, all logic is collected in the application server. If something has changed in the database or in the data processing logic, just update it, and all clients will work in a new way without any patches.

The advantage of such a system is that there is no need to keep drivers for accessing any databases on client machines. Clients only need to know where the application server is located, be able to connect to it and display the data correctly.

Let's imagine a classic problem - the appearance new version databases or transition to a database of a qualitatively new level. Well, we already lack the capabilities of MySQL, we wanted to get all the power of Oracle. To do this, the database server is reinstalled, the application server is changed to connect to the new database - and the clients are ready to work. There is no need to update them!

But the most interesting thing is that the client program can be anything. You can write scripts that allow you to work with the application server directly from the browser. In this case, users on any platform (Windows, Linux, etc.) will be able to work with the database.

Types of database structures

A database (DB) is an electronic storage of any information that has its own specific, most convenient and functional structure. To create databases and work with them, various DBMSs (database management systems) are used. Databases differ in their structure: pre-relational (on inverted lists, hierarchical systems and network DBMS), relational and post-relational (for example, object).

In the modern world, we need tools that would allow us to store, organize and process large amounts of information that are difficult to work with in Excel or Word. Such repositories are used to develop information websites, online stores and accounting add-ons. The main tools that implement this approach are MS SQL and MySQL. Product from Microsoft Office is a simplified version in functional terms and more understandable for inexperienced users. Let's take a step-by-step look at creating a database in Access 2007.

Description of MS Access

Microsoft Access 2007 is a database management system (DBMS) that implements a full-fledged graphical user interface, the principle of creating entities and relationships between them, as well as the structural query language SQL. The only disadvantage of this DBMS is the inability to work on an industrial scale. It is not designed to store huge amounts of data. Therefore, MS Access 2007 is used for small projects and for personal, non-commercial purposes.

But before showing step-by-step how to create a database, you need to familiarize yourself with the basic concepts of database theory.

Definitions of basic concepts

Without basic knowledge about the controls and objects used when creating and configuring a database, it is impossible to successfully understand the principle and features of setting up a subject area. So now I'll try in simple language explain the essence of all important elements. So, let's begin:

1. A subject area is a set of created tables in a database that are interconnected using primary and secondary keys.
2. An entity is a separate database table.
3. Attribute – the title of a separate column in the table.
4. A tuple is a string that takes the value of all attributes.
5. A primary key is a unique value (id) that is assigned to each tuple.
6. The secondary key of table "B" is a unique value from table "A" that is used in table "B".
7. An SQL query is a special expression that performs a specific action with the database: adding, editing, deleting fields, creating selections.

Now that we have a general idea of ​​what we will be working with, we can begin creating the database.

Creating a database

For clarity of the whole theory, we will create a training database “Students-Exams”, which will contain 2 tables: “Students” and “Exams”. The main key will be the “Record Number” field, because this parameter is unique for each student. The remaining fields are for more complete information about students.

So do the following:

That's it, now all that remains is to create, fill and link tables. Continue to the next point.

Creating and populating tables

After successfully creating the database, an empty table will appear on the screen. To form its structure and fill it out, do the following:

Advice! For fine tuning data format, go to the “Table Mode” tab on the ribbon and pay attention to the “Formatting and Data Type” block. There you can customize the format of the displayed data.

Creating and editing data schemas

Before you start linking two entities, by analogy with the previous paragraph, you need to create and fill out the “Exams” table. It has the following attributes: “Record number”, “Exam1”, “Exam2”, “Exam3”.

To execute queries we need to link our tables. In other words, this is a kind of dependency that is implemented using key fields. To do this you need:

The constructor should automatically create the relationship, depending on the context. If this does not happen, then:

Executing queries

What should we do if we need students who study only in Moscow? Yes, there are only 6 people in our database, but what if there are 6000 of them? Without additional tools it will be difficult to find out.

It is in this situation that SQL queries come to our aid, helping to extract only the necessary information.

Types of requests

SQL syntax implements the CRUD principle (abbreviated from the English create, read, update, delete - “create, read, update, delete”). Those. with queries you can implement all these functions.

For sampling

In this case, the “read” principle comes into play. For example, we need to find all students who study in Kharkov. To do this you need:

What should we do if we are interested in students from Kharkov who have more than 1000 scholarships? Then our query will look like this:

SELECT * FROM Students WHERE Address = “Kharkov” AND Scholarship > 1000;

and the resulting table will look like this:

To create an entity

In addition to adding a table using the built-in constructor, sometimes you may need to perform this operation using SQL query. In most cases, this is needed during laboratory or course work as part of a university course, because in real life there is no need for this. Unless, of course, you are engaged in professional application development. So, to create a request you need:

1. Go to the “Creation” tab.
2. Click the “Query Builder” button in the “Other” block.
3. In the new window, click on the SQL button, then enter the command in the text field:

CREATE TABLE Teachers
(Teacher Code INT PRIMARY KEY,
Last name CHAR(20),
Name CHAR(15),
Middle name CHAR (15),
Gender CHAR (1),
Date of birth DATE,
main_subject CHAR(200));

where "CREATE TABLE" means creating the "Teachers" table, and "CHAR", "DATE" and "INT" are the data types for the corresponding values.

Attention! Each request must have a “;” at the end. Without it, running the script will result in an error.

To add, delete, edit

Everything is much simpler here. Go to the Create a Request field again and enter the following commands:

Creating a Form

With a huge number of fields in the table, filling the database becomes difficult. You may accidentally omit a value, enter an incorrect one, or enter a different type. In this situation, forms come to the rescue, with the help of which you can quickly fill out entities, and the likelihood of making a mistake is minimized. This will require the following steps:

We have already covered all the basic functions of MS Access 2007. There is one last important component left – report generation.

Generating a report

A report is a special MS Access function that allows you to format and prepare data from a database for printing. This is mainly used for creating delivery notes, accounting reports and other office documentation.

If you have never encountered such a function, it is recommended to use the built-in “Report Wizard”. To do this, do the following:

1. Go to the "Creation" tab.
2. Click on the “Report Wizard” button in the “Reports” block.
   
   
3. Select the table of interest and the fields you need to print.
   
   
4. Add the required grouping level.
   
   
5. Select the sort type for each field.