The concept of a data model, database. Concept and purpose of database management systems. Basic database construction models
The core of any database is the data model. Data model represents a great variety of data structures, integrity constraints and data manipulation operations. Using a data model, objects of the subject area and the relationships between them can be represented. Data model is a set of data structures and their processing operations. Modern DBMS is based on the use hierarchical, network, relational and object-oriented data models, combinations of these models, or some subset of them.
Let's look at three main types of data models : hierarchical, network, relational And object-oriented.
Hierarchical data model. A hierarchical structure represents a set of elements connected to each other according to certain rules. Objects connected by hierarchical relationships form a directed graph (inverted tree). The basic concepts of a hierarchical structure include: level, element (node), connection. The hierarchical model organizes data in a tree structure. Knot is a collection of data attributes that describe an object. In a hierarchical tree diagram, nodes look like vertices of a graph. Each node at a lower level is connected to only one node, which is at a higher level. A hierarchical tree has only one vertex (the root of the tree), which is not subordinate to any other vertex. Dependent (subordinate) nodes are located at the second, third and other levels. The number of trees in the database is determined by the number of root records.
Network data model.
Network model means representing data in the form of an arbitrary graph. The advantage of network and hierarchical data models is the possibility of their efficient implementation in terms of memory costs and efficiency. The disadvantage of the network data model is the high complexity and rigidity of the database schema built on its basis.
Relational data model. The concept of relational is associated with the developments of the famous American specialist in the field of database systems E.F. Codda. These models are characterized by a simple data structure, a user-friendly form of presentation in the form of tables, and the ability to use the apparatus of relational algebra and relational computing for data processing.
In the language of mathematics, a relation is defined this way. Let it be given n sets D1,D2, ...,Dn. Then R is a relation over these sets if R is a set of ordered sets of the form
So, the relational model is focused on organizing data in the form of two-dimensional tables, any of which has the following properties:
Each table element is one data element;
All columns in the tables are homogeneous, that is, all elements in the column have the same type (character, numeric, etc.);
Each column has a unique name;
There are no identical rows in the tables.
Tables have rows that correspond to records (or tuples), and columns that correspond to relationship attributes (domains, fields).
The following terms are equivalent:
attitude, table, file (for localDB);
motorcade,line, record;
attribute, column, field.
Object-oriented databases combine two data models, relational and network, and are used to create large databases with complex data structures.
A relational database is a set of relationships that contain all the necessary information and are united by various connections.
DB is considered normalized , if the following conditions are met:
Each table has a master key;
All fields in each table depend only on the master key;
There are no groups of duplicate values in the tables.
To successfully work with multi-table databases, as a rule, it is necessary to establish connections between them. In this case, the terms “base table” (main) and “subordinate table” are used. The relationship between tables is obtained through two fields, one of which is in the base table, and the second in the subordinate table. These fields may have a value that is repeated. If the value in the related field of the base table record and in the field of the subordinate table are the same, then these records are called related.
There are four types of relationships between tables : one to one , one to many, many to one, many to many .
Attitude one to one means that every entry in one corresponds to the table only one records in another table.
One to many relationship means that one a record from the first table can be linked with more than one a record from another table.
Main table is a table that contains primary key and forms part one in a relationship one to many.
External key is a field containing the same type of information in the table from the side a lot of.
Practical work
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.
The MS Access application is a database management system that is part of the Microsoft Office suite and is designed to work on a 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 of a 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 in Visual Basic that 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 constructing a domain data model.
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 designing 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 all existing data models (hierarchical, network, relational, object) can be generated from the “entity-relationship” model, 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 terms. This general description of the 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 a key attribute is 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 several 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 unimportant - 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 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 marks and angle brackets. A natural limitation is the prohibition of having two fields with the same names in one table.
Setting the 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. The range of valid values is determined by the Field Size parameter.
Date/Time. Date/Time fields contain date and time values ranging from 100 to 9999.
Currency Currency fields can store numbers up to 15 decimal places to the left of the decimal point and four decimal places (usually two is enough) to the right of the decimal point.
Counter. The counter field contains a number that is automatically incremented by Access by 1 when a new block of data is added to the table.
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). Sets the maximum length of a text field or how numbers are represented in a Number field.
Format(Format). Determines how data is presented. Along with certain formats, the use of user's own formats is allowed.
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(Administration restrictions). 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 the 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 the No command button when prompted 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 has been 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 a 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 problem domain 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 related to 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 | Attachments to database records, spreadsheet files, documents, charts, and other supported file types, 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 a detailed description of a 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.
Although forms are best for entering data, especially in Access databases with multiple users, you can enter and edit data directly in a 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 mask characters that indicate where, in what quantity, and what type of data can be entered, you may need to enter data in a specific 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 of the 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 fairly easy to define, display, and update information in the database, simplifying the programming of 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. The complete set of SQL commands includes 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 of SQL commands is achieved through the use and creation of specific information that 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 commands contain keywords and clauses 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. A complete SQL database includes the following components:
· tables - basic data structures in databases;
· selects - a type of virtual table that provides input/output of specific rows 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 database management systems. It was of a general nature and allowed for 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 the latest versions of 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 and defining a custom data type.
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.
The advent of computer technology has increased the efficiency of working with databases. Data access and management occurs in the environment of a special software package - a database management system (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 of data defines the method of placing data directly on machine media. Logical organization of data on machine media depends on software, organization and maintenance of 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 the following principle: several lower-level nodes are connected via communication with one higher-level node.
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. There is an arbitrary, unlimited number of elements-connection between the elements of the structure.
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
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 is the Windows registry, which shows the placement of files and folders of different levels of nesting on computer drives, 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 In 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 through the database. The implementation of user requests may be delayed, 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 In the database, all data available to the user is organized in the form of 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 a relational database 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 different 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.
 |
This is a database based on a tree structure. According to the principle of construction, it is somewhat similar to the file system of a computer. Using such a model has its advantages and disadvantages, which will be discussed in this article, along with detailed examples.
Types of databases
As you know, there are four types of database construction:
- Relational - tabular DBMS, where information is presented in the form of rows and columns. This principle is used to build buildings in Access, for example.
- Object-oriented ones are closely related to the way in which one works with objects), and this is their main advantage, but given their low productivity, they are still significantly inferior in prevalence to relational ones.
- Hybrid - DBMS that contains two of the above types at once.
- Hierarchical ones are the object of attention of this article. characterized by a tree-like structure.
The most famous example of a hierarchical database is a product created by IBM called the Information Management System, abbreviated as IMS. The first version of IMS was released in the last twentieth century, in the year sixty-eight. It is still used to store and control data today.
The principle of constructing a hierarchical model
The hierarchical data model is built according to the following principle:
- for each node of the tree structure a certain segment is assigned;
- A segment refers to data fields with a name assigned to each field and arranged in one linear tuple;
- another match: one input and several output segments for each source field;
- for each structural element there is one and only one place in the hierarchy system;
- the tree structure starts from the root element;
- Each slave node has only one ancestor, but each parent node can have multiple children.
Applying a Hierarchical Data Structure
A hierarchical database is a storage that is applicable for those systems that initially have a tree structure. For them, choosing such modeling is logical.
An example of a hierarchical database with initially systematized degrees is a military unit, in which, as we know, ranks are clearly defined. They can also be complex mechanisms, consisting of particles that become increasingly simpler towards the bottom of the hierarchy. To model such systems and bring them to the form of the database under consideration, there is no need for decomposition. However, this situation does not always arise.
Additionally, there is a tendency for a downward query to be simpler than an upward query.
Basic operations on databases built on a hierarchical model
The structure of the hierarchical database allows you to successfully and almost seamlessly (depending on skills and abilities) perform the following operations (the most basic ones are presented, the list can always be expanded with small additions):
- search the database for a particular element;
- transition through the database - from tree to tree;
- moving along a tree - from branch to branch;
- accordingly, the transition along the branches is element-by-element;
- working with records: inserting a new one and/or deleting the current one, copying, cutting, etc.
Generalized description of the structure
The term “tree-like” to describe the structure is mentioned in this article more than once. It's time to tell where it came from. This is because a hierarchical database is a database that uses the “tree” data type. Let's take a closer look at what it is.
This is a compound type: each element (node) contains several subsequent ones (one or more). And it all starts with one root element. The point is that each of the pieces of the “tree” type is a subtype, also a “tree”. Many, many branched, and still ordered structures.
Elementary types can be simple or compound, but essentially they are always records. But in a simple record there is one, and in a compound record there is a whole set of them.
The hierarchical model is characterized by the principle of descendants, when each previous segment is the ancestor of the next one. In addition, a descendant of a parent type is a subordinate type, while records equivalent to one another are considered twins.
Filling the database
The main data of a hierarchical database are the values (numbers or symbols) that are stored in records. Such a database is usually traversed from bottom to top and from left to right.
Advantages
A hierarchical database is a database that has a root folder and gradually branches downwards. Considering that such a structure is very similar to a file system, such databases are successfully used to perform various operations on computer data. The result: a rational distribution of her memory, as well as very decent indicators of the time spent on work.
The hierarchical model is ideal for using it to organize information.
Flaws
However, the same features of the DBMS under consideration, which became their main advantages, also determine their disadvantages. For example, the bulkiness and complexity of logical connections - it will be difficult for an experienced specialist to understand when working with a previously unknown database, and a simple user will get completely lost in it. This complexity of understanding leads to the fact that not many DBMSs are actually built on a hierarchical model. An example of a hierarchical database is, in addition to the already described product of IBM, Oka and MIRIS (made in Russia), as well as Data Edge and Team-UP (from foreign corporations).
Examples
A hierarchical database is a variety of different levels on which relationships are built. Schematically, it looks like an inverted graph. An example of a hierarchical database is any government administrative agency. Take, for example, school.
At the very top level there will be the “leader” of the administration - the director. The head teachers are subordinate to him, and the head teachers have teachers who manage parallel classes. Each parallel has a certain number of them, and each class has a certain number of students.
The same principle can be used to describe the management of a corporation. The head of the company or even the board of directors is at the very top. Next - an increasing number of divisions, each of which has its own structure. There are also common features: the head of each department, his assistant, his secretary, the office employees themselves, and so on.
Application in computers
There may be more serious applications. A striking example of a hierarchical database is a file system. The familiar “Explorer” is built in the very core of the Windows operating system according to exactly this scheme, just like many other file managers.
Network Databases
Exist:
- relational;
- hierarchical;
- network databases.
Why did we think about classification again? Because, unlike a relational database, a network database has similar features to a hierarchical one.
Time to remember in databases. There are one-to-one, one-to-many, and many-to-many relationships. We are interested in the latter. In a network database, it manifests itself as follows: one successor node can have several ancestors at once. The property of having multiple descendants is also preserved. We can say that hierarchical databases and network databases are themselves an example of such inheritance. The ancestor in this case is precisely the hierarchical database, since the principle of constructing the structure in network databases remains the same.
Hierarchy and relationality
The name "relational" comes from the English word "relationship". As mentioned at the beginning of the article, they are often expressed in tables. But in the previous paragraph we indicated that a hierarchical database can also organize connections, does this mean that between these two types there is some kind of thin thread uniting them?
Yes. In addition to the fact that both the first and second types still relate to databases, in addition to this feature there is one more common property. For example, a hierarchical database (and a network database along with it) can be expressed in a table. The point here is not in what form the information should be presented to the end user (this is already a question of interface usability), but on what principle the information was structured. Thus, a clear division into departments with their heads, divisions, and so on will still be expressed in the hierarchy, but for convenience it is listed in a table.
As noted, the infological model maps the real world into some human-understandable concepts that are completely independent of the parameters of the data storage environment. There are many approaches to building such models: graph models, semantic networks, entity-relationship model, etc. The most popular of these has proven to be the entity-relationship model, which will be discussed in Chapter 2.
The information model must be mapped into a computer-oriented datalogical model that is “understandable” by the DBMS. In the process of developing the theory and practical use of databases, as well as computer technology, DBMSs were created that supported various datalogical models.
First, hierarchical datalogical models began to be used. Simplicity of organization, the presence of predetermined connections between entities, and similarity to physical data models made it possible to achieve acceptable performance of hierarchical DBMSs on slow computers with very limited amounts of memory. But, if the data did not have a tree structure, then a lot of difficulties arose when building a hierarchical model and the desire to achieve the desired performance.
Network models were also created for low-resource computers. These are quite complex structures consisting of “sets” - named two-level trees. “Sets” are connected using “link records”, forming chains, etc. When developing network models, many “little tricks” were invented that made it possible to increase the performance of the DBMS, but significantly complicated the latter. An application programmer must know a lot of terms, study several internal DBMS languages, and have a detailed understanding of the logical structure of the database to navigate among various instances, sets, records, etc. One of the developers of the UNIX operating system said, “The network base is the surest way to lose data.”
The complexity of the practical use of hierarchical and network DBMSs forced us to look for other ways to present data. At the end of the 60s, DBMSs based on inverted files appeared, characterized by ease of organization and the presence of very convenient data manipulation languages. However, such DBMSs have a number of restrictions on the number of files for storing data, the number of connections between them, the length of the record and the number of its fields.
The most common models today are relational models, which will be discussed in detail in Chapter 3.
The physical organization of data has a major impact on the operational characteristics of the database. DBMS developers are trying to create the most productive physical data models, offering users one or another tool for customizing the model for a specific database. The variety of ways to adjust the physical models of modern industrial DBMSs does not allow us to consider them in this section.
Database organization models
1. Hierarchical approach to organizing databases. Hierarchical databases have the form of trees with arc-links and nodes-data elements. The hierarchical structure implied inequality between data - some were strictly subordinate to others. Such structures, of course, clearly satisfy the requirements of many, but not all, real-life problems.
2. Network data model. In network databases, along with vertical connections, horizontal connections are also implemented. However, many disadvantages of the hierarchical system have been inherited, and the main one is the need to clearly define data connections at the physical level and just as clearly follow this structure of connections when querying the database.
3. Relational model. The relational model emerged from the desire to make the database as flexible as possible. This model provided a simple and effective mechanism for maintaining data relationships.
Firstly, all data in the model is presented in the form of tables and only tables. The relational model is the only one that ensures uniformity of data presentation. Both entities and the connections of these very entities are represented in the model in exactly the same way - tables . True, this approach complicates the understanding of the meaning of the information stored in the database, and, as a result, the manipulation of this information.
Allows you to avoid the difficulties of manipulation second element models – a relationally complete language (note that language is an integral part of any data model, without it the model does not exist). The completeness of a language when applied to a relational model means that it must perform any operation of relational algebra or relational calculus (the completeness of the latter has been proven mathematically by E.F. Codd). Moreover, the language must describe any query in terms of operations on tables, not on their rows. One such language is SQL.
Third element relational model requires the relational model to maintain some integrity constraints. One such constraint states that each row in a table must have a unique identifier called primary key . The second limitation is imposed on the integrity of links between tables. It states that table attributes that reference the primary keys of other tables must have one of those primary key values.
4. Object-oriented model. New areas of computing technology, such as scientific research, computer-aided design, and institutional automation, have required databases to be able to store and process new objects—text, audio, video, and documents. The main difficulties of object-oriented data modeling stem from the fact that such a developed mathematical apparatus on which a general object-oriented data model could be based does not exist. This is largely why there is still no basic object-oriented model. On the other hand, some authors argue that a general object-oriented data model in the classical sense cannot be defined because the classical concept of a data model is unsuitable for the object-oriented paradigm. Despite the advantages of object-oriented systems - implementation of complex data types, communication with programming languages, etc. – in the near future, the superiority of relational DBMSs is guaranteed.
5.3.3 Data models and conceptual modeling
It was already mentioned above that a schema is created using some data definition language. In fact, it is created based on the data definition language of the specific target DBMS, which is a relatively low-level language; with its help, it is difficult to describe the data requirements so that the created diagram is understandable to users of various categories. To achieve such an understanding, it is necessary to create a description of the schema at some higher level, which we will call a data model. In this case, by a data model we will understand an integrated set of concepts for describing data, connections between them and restrictions imposed on data within a certain subject area.
A model is a representation of objects and events in a subject area, as well as the relationships that exist between them. A data model can be thought of as a combination of the following three components.
· Structural part, i.e. a set of rules by which a database can be built.
· The control part, which determines the types of permissible operations with data (this includes operations of updating and retrieving data, as well as operations of changing the database structure).
· A set of data integrity constraints that guarantee the correctness of the data used.
The purpose of building a data model is to present data in an understandable way. If such a representation is possible, then the data model can be easily applied when designing a database. To represent the ANSI-SPARC architecture, the following three related data models can be defined:
· an external data model that displays views of each user type existing in the organization;
· a conceptual data model that displays a logical (or generalized) view of the data, independent of the type of DBMS selected;
· an internal data model that displays the conceptual schema in a specific way that is understandable to the selected target DBMS.
Quite a number of data models have been proposed and published in the literature. They are divided into three categories: object-based data models, record-based data models, and physical data models. The first two are used to describe data at the conceptual and external levels, and the last one at the internal level.
Object data models. When building object data models, concepts such as entities, attributes and relationships are used. An entity is a separate element (employee, product, concept or event) of a subject area that must be represented in the database. An attribute is a property that describes some aspect of an object and whose value should be captured, and a relationship is an associative relationship between entities. Some of the most common types of data object models are listed below.
- Entity-Relationship model or ER model.
- Semantic model.
- Functional model.
- Object-oriented model.
Currently, the ER model has become one of the main methods for conceptual database design. The object-oriented model extends the definition of an entity to include not only the attributes that describe the state of the object, but also the actions that are associated with it, i.e. his behavior. In this case, the object is said to encapsulate state and behavior.
Record-based data models. In a record-based model, a database consists of several fixed-format records that can be of different types. Each record type defines a fixed number of fields, each of which has a fixed length. There are three main types of logical record-based data models: relational data model, network data model, and hierarchical data model.
