Database organization models. Database structural elements
The core of any database is the data model. Data model - a set of data structures and processing operations .
The DBMS is based on the use of hierarchical, network or relational model, on a combination of these models or some subset of them.
Hierarchical data model.
The basic concepts of a hierarchical structure include: level, element, connection. Knot is a collection of data attributes that describe an object. In a hierarchical tree diagram, nodes are represented as vertices in the graph. Each node at a lower level is connected to only one node at a higher level. A hierarchical tree has only one vertex (the root of the tree), which is not subordinate to any other vertex and is located at the top (first) level (see Fig. 5).
Rice. 5. Hierarchical data model
Each database record has only one (hierarchical) path from the root record. For example, for record C4 the path goes through records A and B3.
An example of a hierarchical structure. Each student studies in a certain (only one) group, which belongs to a certain (only one) faculty (see Fig. 6).
Rice. 6. Example of hierarchical data organization
Network model data
In a network structure, each element can be connected to any other element (see Figure 7).
Rice. 7. Network data model
An example of a network structure. A database containing information about students participating in research projects (SRW). It is possible for one student to participate in several research projects, as well as for several students to participate in the development of one research project (see Fig. 8).
Rice. 8. Example network organization data
Relational data model
These models are characterized by a simple data structure, user-friendly presentation, and the ability to use the formal apparatus of relational algebra.
The relational model focuses on organizing data in the form of two-dimensional tables. Each relational table (relation) is a two-dimensional array and has the following properties:
· each table element is one data element;
· all columns in the table are homogeneous, i.e. all elements in a column have the same type (numeric, character, etc.) and length;
Each column has a unique name;
· there are no identical rows in the table;
· the order of rows and columns can be arbitrary.
Example. A relational table can be used to present information about students studying at a university.
A field whose each value uniquely identifies the corresponding record is called with a simple key (key field). If records are uniquely identified by the values of several fields, then such a database table has composite key.
To link two relational tables, you must include the key of the first table as part of the key of the second table (the keys may coincide); otherwise you need to enter into the structure of the first table external key- the key of the second table.
The same data can be grouped into tables different ways. The grouping of attributes in tables should be rational, i.e. minimizing duplication of data and simplifying the procedure for processing it.
Normalization of relations - a formal apparatus of restrictions on the formation of relationships (tables), which eliminates duplication, ensures the consistency of those stored in the database, and reduces labor costs for maintaining (entering, adjusting) the database.
There are five normal forms of relationships. These forms are intended to reduce the redundancy of information from the first to fifth normal forms. Therefore, each subsequent normal form must satisfy the requirements of the previous form and some additional conditions. In practical database design, the fourth and fifth forms are, as a rule, not used.
Let's consider the normalization procedure using the example of designing a multi-table database. Sales containing the following information:
· Information about buyers.
· Order date and quantity of goods ordered.
· Order completion date and quantity of goods sold.
· Characteristics of the product sold (name, cost, brand).
Table 2. Table structure Sales
Table Sales can be considered as a single-table database. The main problem is that it contains a significant amount of repetitive information. This data structure is the reason following problems problems that arise when working with the database:
· You have to spend a significant amount of time entering repetitive data. For example, for all orders placed by one buyer, you will have to enter the same buyer information each time.
· When a customer's address or telephone number changes, all records containing information about that customer's orders must be adjusted.
· The presence of duplicate information will lead to an unjustified increase in the size of the database. As a result, the speed of query execution will decrease. In addition, repetitive data is used irrationally disk space computer.
· Any emergency situations will require significant time to obtain the required information.
For a logical representation of the relationships between database objects, an information-logical (infological) model is used.
There are three types of informationological database models:
· hierarchical;
· network;
· relational.
Hierarchical model data is a tree structure, where each element (object) corresponds to only one connection with an element (object) of a higher level. An example of a hierarchical model would be Windows registry, demonstrating the placement of files and folders of different levels of nesting on computer disks, as well as a family tree.
The advantages of the hierarchical model are simplicity and speed. A request to such a database is processed quickly, since the search for data occurs along one of the branches of the tree, moving down from parent objects to child objects or vice versa (search up the tree takes longer to process).
If the data structure involves more complex relationships than the usual hierarchy, then other models are used to organize information.
Network model data allows, in order to combine related information, to provide connections between some elements and any others, not necessarily parent ones. This model is similar to the hierarchical one and is an improved version of it.
IN network model data, each element can have more than one element generating it, and the graphical representation of the model resembles a network. It allows the complexity of the “tree” without limiting the number of connections included in its vertex.
A feature of hierarchical and network databases is that a rigid structure of records and sets of relationships are specified in advance, even at the design stage, and changing the structure of the database requires restructuring the entire database. In addition, since the logic of the data retrieval procedure depends on the physical organization of the data, this model is application dependent. In other words, if the data structure needs to change, the application may also need to change.
Network databases are considered tools of programmers. So, for example, to get an answer to the query: “What product is most often ordered by company X?”, you need to write some program code to navigate the database. The implementation of user requests may take a long time, and by the time the requested information appears, it will no longer be relevant.
Relational model is quite universal, it significantly simplifies the database structure and makes working with it easier. IN relational all data in the database available to the user, are organized in tables. Each table has its own unique name, corresponding to the nature of its contents. Table columns called fields, describe certain attributes of information, for example: last name, first name, gender, age, telephone number, social status of respondents. The rows of the relational table contain records and store information about one instance of a data object represented in the table, such as data about one person. There should not be identical records in the table.
The main requirement for relational database data is that the values of the fields (table columns) be elementary and indivisible information units (that is, to record an address you will need not one, but several fields containing indivisible information - street, house number, apartment number). This makes it possible to use the mathematical apparatus of relational algebra to process information. The most popular relational DBMSs are Access, FoxPro, dBase, Oracle, etc.
A relational database usually contains several tables with various information. The database developer installs relationships between individual tables. When creating connections use key fields.
Once connections are established, it becomes possible to create queries, forms, and reports that contain data from several interconnected tables.
All data available to the user in a relational database is organized in the form of relation tables, which are a two-dimensional array, where each table has its own unique name, corresponding to the nature of its contents.
Currently, most DBMSs use a tabular (relational) data model.
Advantages of the relational model:
· Simplicity and accessibility for the end user, since the only information structure is a visual table.
· Complete data independence. When changing the database structure, significant changes in the application program are not required.
Disadvantages of the relational model:
· The subject area cannot always be represented as a set of tables.
· Low query processing speed compared to other models, as well as requiring more external memory.
An example of a simple relational database is the “Respondents” table, where one row (record) is information about one of the participants in a telephone survey.
 |
Plan
Database (DB)
DBMS
Data model
Hierarchical database model
Network Database Model
Table row is a record that contains information about a single table object (one student).
The structure of the entries is the same; The collection of data elements that make up a record is called a field. The record information is in the fields. A table field is a table column.
Identical records in the table are not allowed, since in all field records they are given unique names; the Access DBMS last name allows you to:
The field must be of the same type across all records in the column (either text data, numeric data, etc.).
The relational database model, as a rule, contains several tables, the connection between which is carried out using a special field - key.
Examples of relational DBMSs: dBASE, FoxBase, FoxPro and Access.
MS Access application is a database management system that is included in Microsoft package Office and is designed to work for personal computer or on a network running the Windows operating system.
The Access DBMS database is a relational database that consists of interconnected two-dimensional tables.
Access DBMS makes it possible to:
· Design table database objects;
· Establish connections between tables;
· Enter, store, view, sort, change table data using the algebra of logic and indexing;
· Create and use database objects.
Access DBMS objects:
Database- a file that contains various data storage objects.
Tables) - organization of data storage in the form two-dimensional array. It is the main object of the database. The rest are derived from the table.
Forms- objects for displaying data from a table on the screen in a form convenient for viewing and processing.
Requests- objects for selecting and filtering table data according to certain criteria.
Report- generating a data document from a table for printing.
Macros- description of actions in the form of a sequence of commands and their automatic execution.
Modules- programs for Visual Basic, which are developed by the user to implement non-standard procedures.
Overview of the relational data model. Entity-relationship model. The concept of relationship, attribute, key, connection. Classification of connections with multiplicity and completeness. Rules for building a data model subject area.
Entity-relationship model (ER model)(English: Entity-relationship model or entity-relationship diagram) - a data model that allows you to describe conceptual diagrams using generalized block designs. The ER model is a data metamodel, that is, a means of describing data models.
The ER model is convenient for design information systems, databases, computer application architectures and other systems (models). With the help of such a model, essential elements (nodes, blocks) of the model are identified and connections between them are established.
There are a number of models for representing knowledge. One of the most convenient tools for a unified data representation, independent of the software that implements it, is the entity-relationship model ( entity - relationship model, ER - model).
The entity-relationship model is based on some important semantic information about the real world and is intended to represent data logically. It defines the meanings of data in the context of their relationships with other data. Important for us is the fact that from the “entity-relationship” model all existing models data (hierarchical, network, relational, object), so it is the most general. Any fragment of a subject area can be represented as a set of entities, between which there are a number of connections.
The ER model is one of the simplest visual models. It allows you to comprehend the structure of an object in “large strokes”, in general outline. This general description structure is called an ER diagram or ontology of the selected subject area (area of interest).
Typical examples of using the ER data model IDEF1x (ICAM DEFinition Language) and dimensional modeling.
Relational Database Relationship.
Relational database relationships are divided into two classes: object and relational. An entity relationship stores data objects (entity instances). In an object relation, one (or more) of the attributes that uniquely identify an object. Such key attribute called a (single or multiple) relationship key or primary attribute. The key is usually in the first column. The remaining attributes are functionally dependent on this key. A key can include multiple attributes ( complex key). In an object relation, attributes must not be duplicated. This is the main limitation in a relational database to maintain data integrity. A linked relation stores the keys of two or more object relations, that is, the keys are used to establish connections between the objects of the relations. A connected relationship can have other attributes that are functionally dependent on this relationship. Keys in linked relationships are called foreign keys because they are the primary keys of other relationships.
The conditions and restrictions that are imposed on relational database relationships at the tabular presentation level can be formulated as follows:
· there cannot be identical primary keys, that is, all rows (records) must be unique;
· all lines must have the same standard structure;
· table column names must be different, and column values must be of the same type;
· column values must be atomic, i.e. cannot be components of other relationships;
· The integrity of foreign keys must be maintained;
· the order of placing rows in the table is not significant - it only affects the speed of access to the desired row.
Support is provided for the following types of relationships between records: one to many; many to one, many to many.
Main stages of working with databases:
Table design.
After creating a new data bank using the File/New Database directive (Create new bank) or opening an existing bank using File/Open Database, a data bank window appears on the screen within the Access window.
In the File menu, select the New directive, and in the submenu, select the Table option.
Assigning field names
Each specification line defines the characteristics of one field of the record. The Field Name column specifies the field name. It can be up to 64 characters long and can contain Cyrillic, spaces and special characters, with the exception of periods, exclamation point and angle brackets. A natural limitation is the prohibition of having two fields with the same names in one table.
Installation type of this field
The data type is entered in the Data Type column and can be selected from a list of available types.
Text. Text fields contain text that cannot exceed 255 characters. The actual field length is set using the Field Size parameter.
Memo. Memo fields contain text up to 32,000 characters long. Fields of this data type cannot be indexed.
Number. Numeric fields contain arbitrary numeric values. Range acceptable values determined by the Field Size parameter.
Date/Time. Date/Time fields contain date and time values ranging from 100 to 9999.
Currency Money fields can store numbers up to 15 places to the left of the decimal point and four decimal places(usually two are enough) to the right of the comma.
Counter. The counter field contains a number that is automatically incremented by Access by 1 when a table is added. new block data.
Yes/No. These fields store the values Yes or No. Fields of this type cannot be indexed.
OLE Object. OLE fields contain objects, such as an Excel table or Microsoft Draw graphic, processed by an OLE server. The field size can be up to 128 MB.
Determining the field size. For numeric fields, the Field Size parameter can have one of the following values:
Byte. Stores numbers from 0 to 255 (integers only). Occupies 1 byte.
Integer. Stores numbers from -32768 to 32767 (integers only). Occupies 2 bytes.
Long Integer. Stores numbers from -2147483648 to 2147483647 (integers only). Occupies 4 bytes.
Single. Stores numbers with six-digit precision from 3.402823E38 to 3.402823E38. Occupies 4 bytes.
Double. Stores numbers with ten-digit precision from -1.79769313486232E308 to 1.79769313486232E308. Occupies 8 bytes (standard setting).
Defining Field Parameters
The characteristics of each field are determined by a number of parameters. These parameters regulate the methods of processing, storing and displaying data.
Field Size(Field size). Installs maximum length text field or the way numbers are represented in a Number field.
Format(Format). Determines how data is presented. Along with certain formats, it is allowed to use own formats user.
Decimal Places(Decimal places). Sets the number of places to the right of the decimal point.
Caption(Inscription). Defines the label that will be used as the field name in a form or report. If no value is specified for this parameter, the field name will be used as the label by default.
Default Value(Default value). Sets the value that will be automatically entered into the field when generating a data block.
Validation Rule(Restrictions on administration). A rule that limits what data can be entered into a field.
Validation Text(Report of violation). When you try to enter data into a field that does not satisfy the rule formulated in the Validation Rule.
Indexed(Indexed field). Indexing sign.
Adding and removing fields
Changes can be made to the finished specification. In particular, you can change the parameters of individual fields, add fields to the record in the right places and remove unnecessary ones. But at the same time, you should try to make all the corrections to the specification before starting to fill the data bank, because an attempt to change the parameters of the fields of the filled database may cause loss or distortion of data.
1. If you delete a field that contains data, a warning message will appear asking if the user really wants to delete, click the Cancel button.
2. From the Edit menu, select the Undo Delete directive. However, you can cancel the deletion operation and restore the table to its original state only if, after deletion, no other changes were made to the structure or contents of the bank. Access guarantees undo capability, but only for the last operation performed.
3. Close the table window and click command button No in the dialog box asking if you want to save changes. However, in this case, all other changes made during this session of working with the table will be ignored.
Setting the primary key
Once all the fields have been defined, you should select at least one field to use as the primary key. Primary key declarations prevent the introduction of duplicate data blocks because the table field used as the primary key contains a unique identifier for each data block. This field cannot contain the same value in two different records.
The primary key can only be defined in table design mode. Label the field that should become the primary key field and call the Set Primagu Key directive from the Edit menu. The marked field is immediately indicated by a key icon in the selector column (this is a sign that the field is declared a primary key) and is indexed accordingly.
If the table you are creating does not have a primary key declared when you exit design mode, Access will ask you whether to include a primary key field in the table. If the user answers positively (Yes), then Access will create a special field called ID in which it will be entered for each block of data.
The concept of a table, field, record. The main stages of working with databases in a database management system environment. Mapping the entity-relationship model of the database. Field properties, data types. Entering data into tables. Sorting, searching and filtering data.
Table is a set of named fields that describe the properties of objects.
The table provides data reflection in the form of rows and columns. The column contains characteristics of objects; string - a set of characteristics about one instance of an object. A record is a row in a database table
Field- a table column designed to store the values of a certain property (parameter) of an object.
Record- table row. One record contains data about separate object, which is described in databases.
The Access DBMS allows you to create database objects that will contain information from various tables. To do this, you need to establish a relationship between the tables. When creating a relationship, records in these tables will be merged (linked). In this case, they use conditional terms and talk about a base and dependent table. Both tables must have fields that have the same values. Then the connection between the tables will be this pair of fields (one in the base table, the second in the dependent table). Related fields can have different names, but the value type of these fields must be the same.
Database design consists of conceptual, logical and physical stages. Each stage uses its own data model.
There are several methods for constructing a conceptual database model. One of the most common methods is based on a model, which is based on representing the subject area in the form of two types of objects - entities and relationships.
An entity is a domain object that is a set of elements. Examples of entities are students, objects, clubs. Each entity element is a concrete instance. Entities are represented in the database as a table. The entity name is the table name, the characteristics are the table column names, and the instances are the table rows.
There is a concept of the degree of connection between entities related to the relationship.
The degree of a relationship determines how many instances of one entity can be associated with instances of another entity belonging to that relationship.
At the logical design stage, entities and relationships are transformed into a logical data model built according to the laws of logic. As we already mentioned in the first lesson, there are several logical data models. Among them are relational, hierarchical and network. The most widely used model today is the relational model. In English, “relation” is an attitude, hence the name of the model.
The relationship is represented as a table consisting of rows and columns. Each column of a relationship is called a field, and each row is called a record. Names of fields - attributes. Unlike a regular table, the main property of a relationship is that it should not contain identical records. This is due to the fact that a relation reflects the name of a specific set of objects, and each entry represents an element of this set. Of course, the elements of the set must be different.
Attributes (attribute groups) ensure the uniqueness (unrepeatability) of each row, which is called the relation key. There can be several keys in a relation.
There are several methods for constructing a conceptual database model. One of the most common methods is based on the ER model. This model is based on representing the subject area in the form of two types of objects - entities and relationships.
An entity is a domain object that is a set of elements. Examples of entities are students, objects, clubs. Each element of an entity is a specific instance, for example, a student Sidorov or the subject “mathematics”. As a rule, entities are expressed by nouns. Entities are represented in the database as a table. The entity name is the table name, the characteristics are the table column names, and the instances are the table rows. In table shows how to understand the basic terms of the entity.
Entity STUDENT is the name of the entity.
We are used to the fact that any information can be placed in a table. However, entity tables differ from regular tables in that they cannot have two identical rows.
For example, let the entity STUDENT have the characteristics LAST NAME, FIRST NAME, PATRONICAL NAME, DATE OF BIRTH, HOME ADDRESS. We will write it down in this form: STUDENT (LAST NAME, FIRST NAME, PATRONICAL NAME, DATE OF BIRTH, HOME ADDRESS). Examples of instances of this entity are (Sidorov, Petr, Vasilyevich, 02/01/1985, Tsvetochnaya St. 33), (Ivanova, Olga, Borisovna 05/12/1986, Pobedy Avenue, 231, apt. 3).
Relationships reflect the relationships between entities that are important for the database being designed. These are connections - LEARNING (student in class), PRESENTING (teacher subject for class in office), etc. As a rule, connections are expressed by verbs.
The relationship between entities can be depicted as lines between specific instances. The following illustrates the VISIT relationship between the STUDENT and CIRCLE entities. If an entity can be represented as a table, then to represent the relationships you need to create additional tables that contain information about the data being connected.
Access DBMS objects:
A table is an organization for storing data in the form of a two-dimensional array. It is the main object of the database. The rest are derived from the table.
Form - helps create a user interface, it is used to enter, change or display data.
Queries are objects for selecting and filtering table data according to certain criteria.
Report - document generation.
Macros are a description of actions in the form of a sequence of commands and their automatic execution.
Modules are programs in Visual Basic that are developed by the user to implement non-standard procedures.
Creating tables.
Tables are objects that directly store data.
You can create a table by selecting the DB window on the Table tab and using the Designer or Wizard. But there are other ways (see table).
To fill out a table, you need to switch to the table fill mode by opening it.
Filling out tables.
Tables consist of fields and records. Fields are columns, and records are rows. Making an entry in a table means filling out a row. To create a table, you need to define its fields, the data types of those fields, and sometimes some additional properties of those fields. Not all data takes up the same amount of space on a computer. To store them compactly, it is necessary to clearly define their type.
Data types.
In Access tables, you can specify data types.
| FORM | USED FOR DISPLAY |
| Text | Short alphanumeric values, such as last name or address. |
| Number | Numeric values, such as distance. Note that there is a separate data type for currency units. |
| Currency unit | Monetary values. |
| Not really | Yes and No values and fields containing only one of the two values. |
| Date and time | Date and time values for years from 100 to 9999. |
| Rich text | Text or a combination of text and numbers that can be formatted using color and font controls. |
| Calculated field | Calculation results. Calculations must use other fields from the same table. The expression builder is used to create calculations. |
| Attachments | Files attached to database records spreadsheets, documents, charts and other types of files supported, similar to attachments in email messages. |
| Hyperlinks | Text or a combination of text and numbers that is stored as text and used as a hyperlink address. |
| Note | Long pieces of text. The Note field is often used to store detailed description product. |
| Substitution | A list of values from a table or query, or a set of values specified when the field was created. You can create a lookup field using the Lookup Wizard. The data type in the lookup field is text or numeric, depending on what options you selected in the wizard. |
Input and editing.
Data entry and editing occurs by switching between Table View and Design modes.
Despite the fact that for data entry, especially into databases Access data with multiple users, it is better to use forms, data can be entered and changed directly in the table.
The type of data a user can enter into a table depends on the following aspects.
By default, fields in tables contain a specific type of data, such as text or numbers. You should enter the data type that the corresponding field receives.
Otherwise an error message is displayed.
If an input mask is applied to a field, a format consisting of constant characters (such as parentheses, periods, or hyphens) and special characters masks indicating in which positions, in what quantity and what type of data can be entered, it may be necessary to enter data in a certain format.
With the exception of attachments and multi-valued lists, most fields can only accept one type of data. If you don't know whether a field can contain attachments, review its properties. If the field is a multi-valued list, a check box appears next to each list item.
Concept SQL language.
The language support for conducting transactions is, as a rule, the SQL language. Relational calculus languages are based on classical predicate calculus. They provide the user with a set of rules for writing database queries. Such a request contains only information about the desired result. Based on the request, the database management system automatically, by forming new relationships, generates the desired result. Relational calculus languages are non-procedural. The first relational calculus language ALFA was developed by E.F. Codd himself.
Currently, the SQL (Structured Query Language) language has become widespread. The SQL language was developed by IBM in the mid-70s, and then approved and supported by many companies as a standard language for managing relational databases. This speech was developed based on the language standard used in the dBase database management system. The International Federation for Information Processing (AFIP) and the International Organization for Standardization (ISO) are forming and clarifying standards for further development of the SQL language. The speech is focused on carrying out operations with data that is presented in the form of a logically interconnected set of tables. The main difference from the original dBase language is that SQL is designed for table operations, while dBase is record-oriented.
Functions of the SQL language.
Using the concept of operations focused on tabular representation of data made it possible to create a compact SQL language with a small set of commands. This approach makes it quite easy to define, display and update information in the database, simplifying programming complex queries. A feature of SQL language commands is that they are more focused on the final result of data processing than on the procedure for this processing. The system determines the optimal path to output the data. SQL is non-procedural language. Full set SQL language commands include about 30 commands.
An SQL table is a collection of rows and columns, in which table rows correspond to records, and columns correspond to fields. In addition to regular tables, the SQL language allows you to create a special type of table - a selection. A sample is a subset of rows and columns from one or more tables. A sample is often called a virtual table, since it does not actually contain data, but only allows them to be reproduced. The data in the sample reflects real changes in the corresponding tables and vice versa, a change in data in updated samples leads to a change in this data in the primary tables.
Effective use SQL language commands are implemented through the use and creation special information, which allows you to reference each table and selection. This information is contained in files called table catalogs, which are created during database creation. Every SQL command ends with “;”. Every SQL command, called a clause, begins with a verb that specifies the name of the underlying operation. Many teams include keywords and proposals that clarify the execution of basic operations. In addition, the SQL command must include the data that will be processed and (or) the operations that need to be performed on this data.
The SQL language operates with the concept of databases containing all the information that is necessary for processing data in an application program. Full database SQL data includes the following components:
· tables - basic data structures in databases;
fetch - a type of virtual table that provides I/O certain lines and columns from one or more tables;
· synonyms - alternative names of tables and selections;
· index files that are attached to tables to provide quick data retrieval and maintain database integrity;
· catalogs - a set of tables in each database that describe the databases and their contents.
Development of the SQL language.
The first SQL language standard appeared in 1989 (SQL-89) and was supported by almost all commercial relational systems database management. He had general character and allowed for a broad interpretation. The advantages of SQL-89 can be considered the standardization of the syntax and semantics of selection and data manipulation operators, as well as the fixation of means for limiting the integrity of the database. However, this version lacks sections such as database schema manipulation and dynamic SQL.
The incompleteness of the requirements of SQL -89 led to the creation in 1992 of the next version of the SQL language -92, which covered a wider range of functions: manipulation of the database structure, transaction and session management, dynamic SQL. The standard version has three levels: basic, intermediate and complete. Only latest versions database management systems provide compatibility with the full standard. Work on improving this language does not stop. Improvements will be made, first of all, in the direction of enabling the trigger mechanism, defining arbitrary type data.
Plan
1. The concept of a data model, database. Concept and purpose of database management systems.
2. Overview of the relational data model. Entity-relationship model. The concept of relationship, attribute, key, connection. Classification of connections with multiplicity and completeness. Rules for constructing a domain data model.
3. The concept of table, field, record. The main stages of working with databases in a database management system environment. Mapping the entity-relationship model of the database. Field properties, data types. Entering data into tables. Sorting, searching and filtering data.
4. The concept of a query to a relational database. The concept of the SQL query language.
5. Create tables, forms, queries and reports using wizards.
6. Data exchange between the DBMS and other programs designed for document processing. Database sharing.
The concept of a data model, database. Concept and purpose of database management systems.
Database (DB) is a structured collection of interrelated data of a certain subject area (real objects, processes, phenomena, etc.).
Examples: database on the availability of medications; DB in the aircraft, train schedule system or transport ticket sales DB; Database of documents of school students, file cabinet of the personnel department or libraries, etc.
Appearance computer equipment increased the efficiency of working with databases. Data access and management occurs in a special environment software package- database management systems (DBMS).
DBMS is a program that is used to store, process and search information in databases.
The organization of data in the internal sphere is characterized by two levels - logical and physical. Physical organization data defines the method of placing data directly on machine media. Logical organization of data on machine media depends on software, organizing and maintaining data in the internal sphere. The method of logical organization of data is determined by the type of data structures used and the type of model that is supported by software.
Data model is a set of interconnected data structures and operations on these structures. To place the same information in the internal sphere, different structures and data models can be used. This depends on the user, on the hardware and software, and is determined by the complexity of automated tasks and the amount of information.
There are such data models: hierarchical, relational, post-relational, multidimensional, object-oriented.
Based on the structure of organizing information in a database, the following database models are distinguished: hierarchical, network and relational.
Hierarchical database model. This model is a structure of data that is ordered from general to specific; resembles a “tree” (graph), therefore it has the same parameters: level, node, connection. The model works on this principle: several nodes lower level connected via a link to one node top level.
Hierarchical database model has the following properties: several lower-level nodes are connected to only one higher-level node; a hierarchy tree has only one vertex, which is not subject to another; each node has its own name, there is only one route from the top of the tree (root node) to any node in the structure.
Network Database Model. In general it looks like a hierarchical one. It has the same constituent structures, but differs in the nature of the relationship between them. Between the elements of the structure is arbitrary, not limited quantity elements-connection.
Relational database model. (The origin of the name is from the Latin word relatio - relationship). The model is built on the relationships between the components of the structure. Represents one table or a collection of interconnected two-dimensional tables.
The relational model is created on the basis of a two-dimensional table.
Table row is a record that contains
Hierarchical data model
There is an ordering of the elements in the record, one element is considered the main one, the rest are subordinate. The data in the record is ordered in a certain sequence, like the steps of a ladder, and the search for data can only be carried out by sequential descent from step to step. Searching for any data element in such a system can be quite labor-intensive due to the need to sequentially go through several previous hierarchical steps.
A hierarchical database is formed by a directory of files stored on disk; The directory tree, available for viewing in Total Commander, is a clear demonstration of the structure of such a database and the search for the desired element in it. The same database is a family tree.
Network Data Model
It is distinguished by great flexibility, since it is possible to establish horizontal connections in addition to vertical hierarchical connections. This makes it easier to find the required data elements, since it is no longer necessary to go through all the existing steps.
The network database is actually The World Wide Web global computer network Internet. Hyperlinks link hundreds of millions of documents together into a single network database.
Relational data model
In a relational database, a record is a row of a rectangular table. The elements of a record form the columns of this table (fields). All elements in a column have the same type (numeric, character), and each column has a unique name. There are no identical rows in the table.
The advantages of such databases are clarity and clarity of data organization, speed of searching for the necessary information.
An example of a relational database is a scholarship assignment sheet, in which the entry is a row with data about a specific student, and the names of the fields (columns) indicate what data about each student should be recorded in the table cells.
Any type can be reduced to relational.
Data types
The data type defines the set of values that a given field can take on in different records.
Main data types in modern databases:
date Time;
monetary;
logical;
numerical;
text;
Keys
Superkey - these are one or more table fields that uniquely identify each row in the table
Potential (possible) key this is a superkey key that contains the minimum set of fields necessary to uniquely identify each row in the table.
Primary Key – This potential the key chosen to uniquely identify each row in the table; Usually the candidate key that is easiest to enter is chosen, usually a numeric one.
Key field tables in the Access DBMS are the primary key of the table.
Types of relational relationships
one to one;
Each primary key value in the main table corresponds to one or more records in the child table.
This type of relationship is not used very often because most of the information related in this way can be placed in a single table. A one-to-one relationship can be used to partition tables that contain many fields, to separate part of the table for security reasons, and to store information related to a subset of records in the main table.
one-to-many;
Each primary key value in the main table has one, more, or no records in the child table.
A one-to-many relationship is the most commonly used type of relationship between tables.
many-to-many.
In a many-to-many relationship, one record in table A can have multiple records in table B, and one record in table B can have multiple records in table A. A many-to-many relationship is two one-to-one relationships. -many" with the third table.
Organization of inter-table relationships
one to one - tables are linked by their primary keys ( primary keys both tables are set to be the same);
one-to-many– the main table (one) is linked by a primary key to a child table (many) by a foreign key (this is the primary key of the main table inserted into the child table)
many-to-many – To organize such a relationship between two tables, a third (intermediate) table is created into which the primary keys of the first two tables are inserted. The first and third, as well as the second and third tables are connected to each other, a one-to-many type of relationship.
Example of database organization
Data Integrity Conditions
The integrity condition is used to ensure that the records in the subordinate table correspond to the records in the main table, i.e. You cannot delete data from a key field of the main table.
The operations cascading update and cascading deletion of related fields allow editing and deleting data in the key field of the main table, but are accompanied by automatic changes in the related table.
The structure aspect determines What logically represents a database, the manipulation aspect determines the methods transition between states databases (that is, methods modifications data) and methods extraction data from the database, the integrity aspect determines the means of descriptions correct states Database.
A data model is an abstract, self-contained, logical definition of objects, operators and other elements that together make up the abstract data access machine with which the user interacts. These objects allow you to model the structure of data, and operators - the behavior of data.
In literature, articles and in everyday speech, the term “data model” is sometimes used in the sense of “database schema” (“database model”). This use is incorrect, as pointed out by many authoritative experts, including K. J. Date, M. R. Kogalovsky, S. D. Kuznetsov. There is a data model theory, or modeling tool, while the database model (database schema) is simulation result. According to K. Data, the relationship between these concepts is similar to the relationship between a programming language and a specific program in this language.
M. R. Kogalovsky explains the evolution of the meaning of the term as follows. Initially the concept data models used as a synonym data structures in a specific database. In the process of developing the theory of database systems, the term “data model” acquired new meaning. There was a need for a term that would denote a tool, and not the result of modeling, and would thus embody a variety of different databases of a certain class. In the second half of the 1970s, many publications devoted to mentioned problems, for these purposes the same term “data model” began to be used. Currently, in the scientific literature, the term “data model” is interpreted in the vast majority of cases in an instrumental sense (as a modeling tool).
Nevertheless, long time the term "data model" was used without a formal definition. One of the first specialists who quite formally defined this concept was E. Codd. In the article "Data Models in Database Management", he defined a data model as a combination of three components:
see also
- Metamodeling
- Article Metamodeling on Wikibooks
Notes
Literature
- Date K.J. Introduction to Database Systems. - 8th ed. - M.: “Williams”, 2006. - 1328 p. - ISBN 0-321-19784-4
- Kogalovsky M. R. Advanced technologies of information systems. - M.: DMK Press; IT Company, 2003. - 288 p. - ISBN 5-279-02276-4
- Kogalovsky M. R. Encyclopedia of Database Technologies. - M.: Finance and Statistics, 2002. - 800 p. - ISBN 5-279-02276-4
- Tsikritzis D., Lochowski F. Data models = D. Tsichritzis, F. Lochovsky. Data Models. Prentice Hall, 1982. - M.: Finance and Statistics, 1985. - 344 p.
Wikimedia Foundation. 2010.
See what a “Data Model” is in other dictionaries:
data model- A set of rules for generating data structures in a database, operations on them, as well as integrity constraints that determine permissible connections and data values, and the sequence of their changes. Note To specify the data model, use... ...
Data model- – a method of representing information model data in a computing environment. [GOST 2.053 2006] Term heading: Technologies Encyclopedia headings: Abrasive equipment, Abrasives, Highways, Automotive equipment... Encyclopedia of terms, definitions and explanations of building materials
data model- 3.1.7 data model (DM): Graphical and/or lexical representation of data that establishes their properties, structures and relationships. [ISO/IEC TR 11404 3:1996, definition 3.2.11] Source...
DATA MODEL- according to GOST 2.053–2006 ESKD “Electronic structure of a product”, – a method of presenting information model data in a computing environment... Records management and archiving in terms and definitions
multidimensional data model- A data model that operates on multidimensional data representations in the form of data cubes. Such data models began to be widely used in the mid-90s due to the development OLAP technologies. Operational capabilities multidimensional data models... ... Technical Translator's Guide
World Customs Organization data model- Data model and dataset developed at the World Customs Organization based on the United Nations Trade Data Elements Directory (UNTDED) [Trade Facilitation: English-Russian Glossary of Terms (Revised Second Edition)… … Technical Translator's Guide
Hierarchical data model is a representation of a database in the form of a tree (hierarchical) structure consisting of objects (data) of various levels. There are connections between objects, each object can include several objects... ... Wikipedia
- (RMD) logic model data, applied theory of database construction, which is an application to data processing problems of such branches of mathematics as set theory and first-order logic. The relational data model is used to build... ... Wikipedia
This term has other meanings, see ER. Entity relationship model (ERM) is a data model that allows you to describe conceptual diagrams of a subject area. The ER model is used when... ... Wikipedia
GOST R ISO/IEC 19778-1-2011: Information technology. Training, education and training. Collaboration technology. Shared workspace. Part 1: Shared Workspace Data Model- Terminology GOST R ISO/IEC 19778 1 2011: Information technology. Training, education and training. Collaboration technology. Shared workspace. Part 1: Shared Workspace Data Model original document: 5.4.9 AE CE ID... Dictionary-reference book of terms of normative and technical documentation
Books
- Model of electron gas and theory of generalized charges for describing interatomic forces and adsorption, A. M. Dolgonosov. This book discusses four key topics atomic and molecular physics, quantum and physical chemistry: description of atomic electron gas and the following conclusion of the main...