2. 2. CONSTRUCTION OF THE "OBJECT - PROPERTY - RELATION" MODEL
To describe ILM, both analytical (descriptive) languages and graphic tools further applied graphical way displaying the "object-property-relation" model. V subject area in the process of its examination and analysis, classes of objects are distinguished. Object class call a set of objects that have the same set of properties. For example, if we consider a university as a subject area, then the following classes of objects can be distinguished in it: students, teachers, auditoriums, etc. Objects can be real, as mentioned above, or they can be abstract, such as subjects, which students study.
When reflected in the information system, each object is represented by its identifier, which distinguishes one object of the class from another, and each class of objects is represented by the name of this class. So, for objects of the class “STUDYED SUBJECTS”, the identifier of each object will be “NAME OF THE SUBJECT”. The identifier must be unique.
Each object has a specific set of properties. For objects of the same class, the set of these properties is the same, and their values, of course, may differ. For example, for objects of the "STUDENT" class, such a set of properties describing class objects can be "YEAR OF BIRTH", "GENDER", etc.
When describing the subject area, it is necessary to depict each of the existing classes of objects and a set of properties fixed for objects of this class.
We will use the following designations to display objects and their properties (Fig. & 2. 3).
Property
Rice. 2.3 Designation of objects and their properties
For each class of objects in info logical model a unique name is assigned.
When building an infological model, it is desirable to give a verbal interpretation of each entity, especially if an ambiguous interpretation of the concept is possible.
 |
Rice. 2.4 Image of an "object-property" relationship
When describing the subject area, it is necessary to reflect the connections between the object and the properties that characterize it. This is simply depicted as a line connecting the designation of the object and its properties.
The relationship between an object and its property can be different. An object can have only one value for a property. For example, each person can only have one date of birth. Let us call such properties single. For other properties, it is possible that several values exist for one object at the same time. Suppose, for example, when describing an "EMPLOYEE", the "FOREIGN LANGUAGE" which he owns is fixed as his property. Since an employee may know several foreign languages, then such a property will be called plural. When depicting the relationship between an object and its properties, we will use a single arrow for single properties, and a double arrow for multiple properties.
In addition, some properties are permanent, their value cannot change over time. Let us call such properties static, and those properties, the value of which can change over time, will be called dynamic.
Another characteristic of the relationship between an object and its property is the sign of whether this property is present in all objects of a given class or absent in some objects. For example, for some employees the property “ACADEMIC DEGREE” may exist, and other objects of this class may not have the specified property. Let us call such properties conditional.
When depicting the connection of a conditional property with an object, we will use dotted line, and for the designation of dynamic and static properties we will use the letters D and S above the corresponding line.
Sometimes in an infological model it is useful to introduce the concept "Composite property". Examples of such properties are “ADDRESS”, consisting of “CITY”, “STREET”, “HOUSE” and “APARTMENT”, and “DATE OF BIRTH”, consisting of “NUMBER”, “MONTH” and “YEAR”. We use in ILM to designate a composite property a square, from which the lines that connect it with the designations of its constituent elements originate (Fig. 2. 4).
The infological model does not display individual instances of objects, but classes of objects. When the designation of an object is shown in the ILM, it is clear that it comes about a class of objects with the described properties. Therefore, in most cases, it is possible not to explicitly introduce a designation for a class of objects into an infological model. An explicit image of a class of objects is necessary only if the software for a given class of objects records not only characteristics related to individual objects of this class, but also some integral characteristics related to the entire class as a whole. For example, if for the object class "EMPLOYEES" is recorded not only the age of each employee, but also the average age of all employees, then in the infological model it is necessary to reflect not only the "EMPLOYEE" object, but also the "EMPLOYEES" object class. To display a class of objects, you can use some specific designation or the same one that is used for objects (Fig. 2. 5).
 |
Rice. 2.5 Image of a class of objects and integral characteristics of the class.
In addition to the connection between an object and its properties, the infological model records connections between objects of different classes. There are links of the type “one to one” (1: 1), “one to many” (1: M), “many to many” (M: M). These types of relationships are sometimes referred to as the degree of relationship.
In addition to the degree of connection in the infological model, to characterize the connection between different entities, it is necessary to indicate the so-called "Class of belonging", which shows whether there can be no connection between an object of this class with any object of another class. Entity class must be either required or optional.
Let us explain what was said on specific examples... As mentioned above, the infological model is not built for a separate object, but displays the classes of objects and the relationships between them. The corresponding diagram that displays this is called an ER-type diagram (this name is due to the fact that in English the word "entity" is spelled "Entity", and the relationship is "Relationship"). However, sometimes, in addition to ER-type diagrams, ER-instance diagrams are used.
Suppose that the infological model displays a relationship between two classes of objects: "EMPLOYEE" and "FOREIGN LANGUAGE".
Suppose that the subject area is a factory where some of the employees know a foreign language, but none of them is fluent in more than one language. Naturally, there are many languages that none of the employees speaks, and also that some of the employees speak the same foreign language (Fig. 2. 6).
c1. .ya1
c2. .ya2
c3. .я3
c4. .ya4
c5. .ya5
c6. .ya6
c7. .ya7
Rice. 2.6 ER diagram - instances
In this case, the ER-instances diagram will look like the one shown in Fig. 2. 6, and the diagram of ER-types is as in fig. 2.7.
Rice. 2. 7. Diagram of E - R types
Let us further assume that the subject area is an institution, and the “PERSONALITY” object reflects the applicants entering this institution. Each of the applicants must necessarily speak a foreign language, but no one speaks more than one language (Fig. 2. 8). In this case, the ER-instances diagram will look like the one shown in Fig. 2. 8, and the diagram of ER-types is as in fig. 2.9.
Personality Language
l1 i1
l2 i2
l3 i3
l4 z4
l5 i5
l6 i6
l7 i7
Both in the first and in the second considered case, the relationship M is observed between the entities: 1. On the diagram, this is displayed from the side of the "PERSONALITY" object by a double arrow, and from the side of the "FOREIGN LANGUAGE" object - by a single arrow on the line depicting the relationship between data entities.
The difference in the considered situations is that in the first case, the belonging class is optional for both entities, and in the second, for the "PERSONALITY" entity, the belonging class is mandatory. On the diagram (Fig. 2. 9) this is shown by a point in the rectangle corresponding to the "PERSONALITY" object.
Let the subject area be the same as in the previous case, but there are situations when some applicants know several foreign languages. In this case, the link between the objects will be of type M: M.
For such a domain, the ER-instances diagram will look like the one shown in Fig. 2. 10, and the diagram of ER-types is as in fig. 2.11.
Personality Language
l1 i1
l2 i2
l3 i3
l4 z4
l5 i5
l6 i6
l7 i7
 |
Suppose that the subject area is some linguistic institute, in which each and: employees necessarily know several foreign languages, and for each of the languages known to science in this institute there is at least one specialist who knows it.
In this case, the relationship between the objects will be M: M, and the class of ownership of both entities is mandatory.
(An example could be given, but the point is clear).
Above, we considered objects without delving into their complexity. In fact, there are several types of objects.
First of all, these are simple and complex objects. The object is called simple, if it is seen as indivisible. Difficult an object is a union of other objects, simple or complex, also displayed in the information system. The concept of “simple” and “complex” object is relative. In one view, an object can be considered simple, and in another, the same object can be considered complex. For example, the object “chair” in the subsystem of material assets accounting will be considered as a simple object, but for an enterprise producing chairs, it will be a composite object (including “legs”, “back”, “seat”, etc.).
There are several varieties. complex objects: compound objects, generalized objects, and aggregated objects.
Compound object corresponds to the mapping of the whole-part relationship. Examples of composite objects are NODES-DETAILS, CLASS-STUDENTS, and so on.
Infological models usually do not use any special conventions to display complex objects. The relationship between the composite and its constituent objects is displayed in the same way as described above. Moreover, the nature of the connection can also be different: for example, “DETAILS” and “KNOTS” are interconnected by a relation of the type M: M, and “GROUP” and “STUDENTS” are connected by a relation of 1: M.
Generalized object reflects the presence of the relationship "genus-species" between objects of the subject area. For example, the objects STUDENT, SCHOOLBOY, GRADUATE, STUDENT STUDENTS form a generalized object STUDENTS. The objects that make up a generalized object are called its categories.
Both a “generic” object and “specific” objects can have a certain set of properties. Moreover, the so-called inheritance of properties is observed, that is, a “specific” object has all the properties that a “generic” object possesses, plus properties inherent only to objects of this type.
Determination of genus - specific relationships means the classification of objects of the subject area according to one or another characteristic. Subclasses can be distinguished in an infological model in an explicit and implicit form. In the first case, with a graphic image, special designation for the subclass. In fig. 2.14 depicts a fragment of the infological model, reflecting the generalized object "PERSONALITY" for higher educational institution... There are several categories for him: TEACHER, STUDENT, STUDENT. A triangle was used to indicate the subclass in the circuit.
Naturally, the classification can be multilevel. So, in the considered example, the generalized object "PERSONALITY" can be divided into two subclasses: EMPLOYEE and STUDENT. EMPLOYEES, in turn, can be classified into TEACHING STAFF, ADMINISTRATION, etc.
Personality
 |
Rice. 2.14 Generic object image
The classes of objects selected in the domain can be either overlapping or non-overlapping. To display this information in the infological model, you can use the intersection graph, the vertices of which correspond to the classes (subclasses) of objects, and the edges connect a pair of vertices only if the corresponding classes of objects are intersecting. You can use a weighted graph to display the degree of intersection. In this case, the weight of a vertex will denote the cardinality of the corresponding set of objects, and the weight of an edge will denote the cardinality of the set that is the intersection of the sets connected by this edge (Fig. 2.15).
Rice. 2.15 Intersection graph
The intersection graph contains Additional information about the subject area and does not belong to the class of ER-models.
Aggregated objects usually correspond to some process in which other objects are “involved”. For example, the aggregated object “SUPPLY” combines the objects “SUPPLIER”, which supplies products, “CONSUMER”, which receives these products, as well as the supplied “PRODUCTS”. The original object is the “DELIVERY DATE”. An aggregated object can, like a simple object, have properties that characterize it. In the example under consideration, such a property can be the delivery size.
Aggregated objects are usually called verbal nouns (for example, supply-supply, release - release, sell-sell, etc.).
 |
Rice. 2.16 Aggregated Object Image
To display an aggregated object in an infological model, we will use the following conventions:
the aggregated object itself will be represented by a rhombus, next to which the name of the corresponding object is indicated. This diamond must be associated with conventions those objects that form this aggregated object. The properties of the aggregated object are displayed in the same way as for simple object. On the rice. 2.16 shows the aggregated object "PRODUCT SUPPLY".
1. Basic concepts and terms to the topic
"INFORMATION MODEL - THE BASIS OF CONSTRUCTION
DATABASE MANAGEMENT SYSTEMS.
Every civilization has to deal with information processing. As the economy develops and the population grows, so does the volume of interconnected data required to solve business and administrative problems.
@ The model of collection, storage, processing and use of interrelated data for the purpose of the most optimal management of information flows and solving the assigned tasks in a given subject area is called an information system. ... Such a system is primarily designed to facilitate human labor, but for this it must correspond as best as possible to a very complex model of the real world.
@ Core information system are the data stored in it ... In any enterprise, the data of different departments, as a rule, overlap, that is, they are used in several departments or are generally shared. For example, management purposes often require information across the entire enterprise. Parts ordering is not possible without availability of information on stocks. The data stored in the information system should be easily accessible in the form in which they are needed for a specific production activities enterprises. In this case, the way of data storage is not essential. Today at the enterprise we can meet the data processing system traditional type, in which the employee manually places the data in the binder, and next to it - modern system with the use of the fastest computers, the most sophisticated equipment and software. Despite the striking dissimilarity, both of these systems are required to provide reliable information at a specific time, for a specific person, at a specific location and at a limited cost.
To understand the process of building an information system, you need to know a number of terms that are used to describe and present data.
@ Subject area called part real system of interest for this study.
When designing automated information systems, the subject area is displayed by data models of several levels. The number of levels used depends on the complexity of the system, but in any case includes the logical and physical levels. A subject area can refer to any type of organization (for example, a bank, university, hospital, or factory).
It is necessary to distinguish between the complete subject area (large manufacturing enterprise, warehouse, department store, etc.) and the organizational unit of this subject area. An organizational unit, in turn, can represent its own subject area (for example, a workshop for the production of bodies of an automobile plant or a data processing department of a computer manufacturing enterprise). In this case, workshops and departments themselves may correspond to certain subject areas.
The information required to describe the subject area depends on the actual model and may include information about personnel, wages, goods, invoices, invoices, sales reports, laboratory tests, financial transactions, medical records, that is, information about people, places, objects , events and concepts.
@ Object is an element of the information system, information about which we store. In relational database theory, an object is called an entity.
The object can be real(for example, a person, an object or locality) and abstract(for example, an event, a customer account, or a course being taken by students). So, in the field of car sales, examples of objects are CAR MODEL, CLIENT and ACCOUNT. In a warehouse it is a SUPPLIER, GOODS, SENDING, etc. Each object has a certain set of properties that are stored in the information system. When processing data, you often have to deal with a collection of homogeneous objects, such as employees, and record information about the same properties for each of them.
@ Object class call a set of objects that have the same set of properties.
Thus, for objects of the same class, the set of properties will be the same, although the values of these properties for each object may, of course, be different. For example, the object class MODEL properties for each object can of course be different. For example, the CAR MODEL object class will have the same set of properties that describe the characteristics of cars, and each model will have different meanings these characteristics.
Objects and their properties are real-world concepts. In the world of information that exists in the programmer's view, one speaks of the attributes of objects.
@ Attribute is an informational display of object properties. Each object is characterized by a number of basic attributes.
For example, a car model is characterized by body type, engine displacement, number of cylinders, power, dimensions, name, etc. A customer of a car store has attributes such as last name, first name, patronymic, address, and possibly an identification number. Each attribute in the model must have a unique name - an identifier. An attribute in the implementation of an information model on any information carrier is often called data item, data field or just a field.
Rice. 1.1. Three areas of data presentation.
@ table is some regular structure consisting of a finite set of records of the same type. In some sources, a table is called a relationship.
We will try to avoid the latter term, since with the development of relational theory, “relationship” along with the term “relationship” often began to call relationships between tables. Each record of one table consists of a finite (and the same!) Number of fields, and the specific field of each record of one table can only contain data of one type.
@ Data values represent the actual data contained in each data item.
The “MODEL NAME” data item can have values such as “Voyager” 96 3.8 Grand ”,“ Continental 4.6 ”or“ Crown Victoria 4.6. ”Depending on how the data items describe the object, their values can be quantitative, qualitative or descriptive Information about a certain subject area can be represented using several objects, each of which is described by several data items. data.
@ A single set of values accepted by data items is called an instance of an object... Objects communicate with each other in a certain way.
@ The corresponding model of objects with their constituent data elements and relationships is called conceptual model subject area. The conceptual model gives an idea of the data flow in the domain.
Some data elements have a property that is important for building an information model. If we know the value that such a data item of an object takes, we can identify the values that other data items of the same object take. For example, knowing the unique model number of the car - 7, we can determine that this is “Voyager“ 96 ”and that the engine displacement for this model is“ 3778 ”.
@ Key element data is called an element by which you can determine the values of other data elements.
Two or more data items can uniquely identify an object. In this case, they are called “candidates” for key data items. The question is , which of the candidates to use for accessing the object is decided by the user or the system designer. Choose key data elements carefully because right choice contributes to the creation of the right conceptual model data.
@ Primary key is an attribute (or group of attributes) that uniquely identifies each row in a table.
Primary key concept is extremely important in connection with the concept of database integrity, which we will discuss in detail at the end of this section.
@ Alternate key is an attribute (or group of attributes) that does not match the primary key and that uniquely identifies an instance of an object.
For example, for the "EMPLOYEE" object, which has the attributes "EMPLOYEE ID", "SURNAME", "FIRST NAME" and "SURNAME", the attribute group "SURNAME", "FIRST NAME", "SATURNESS" can be an alternative key in relation to the attribute " EMPLOYEE ID ”(assuming that the company does not have full namesakes).
@ External key is a table attribute that is the primary key of another table.
For example, the "MODEL NUMBER" attribute of a VEHICLE object can be a foreign key to a "MODEL" object.
@ Data recording is a collection of values of related data items.
In fig. 1.2. such items are the unique key and model name, displacement, number of cylinders and engine power. For example, one of the entries - “7 Voyager'96 3.8 Grand 3778 6 164.0”. This string represents the values that the data items of the MODEL object take. Records are stored on some medium, which can be human brain, sheet of paper, computer memory, external storage device, etc.
|
MODEL |
|||
|
UNIQUE MODEL KEY |
Model name |
Working volume (cubic cm) |
Power (hp) |
|
GMC Jimmy 4.3 |
|||
|
7 |
Voyager'96 3.8 Grand |
3778 |
164,0 |
|
Stealth 3.0 |
|||
|
348 Spider 3.4 |
|||
Figure 1.2. Data records of the MODEL object.
Each record of one table consists of a finite (and the same!) Number of fields, and the specific field of each record of one table can only contain data of one type
@ Data type characterizes the type of stored data.
The concept of a data type in the information model is completely adequate to the concept of a data type in programming languages. Usually, modern DBMSs allow storing character, numeric data, bit strings, specialized numeric data (for example, amounts in monetary units), as well as data of a special format (date, time, time interval, etc.). In any case, when choosing a data type, one should take into account the capabilities of the DBMS with which it will be implemented. physical model information system.
@ Connection is a functional relationship between entities.
If there is a relationship between some entities, then the facts from one entity are referred to or in some way related to the facts from another entity. Maintaining consistency functional dependencies between entities is called referential integrity. Since the relationships are contained “inside” the relational model, the implementation of referential integrity can be performed both by the application and by the DBMS itself (using declarative referential integrity mechanisms and triggers).
Links can be represented by five main characteristics:
Link type (identifying, non-identifying)
Parent entity;
Child (dependent) entity;
Communication power (cordiality);
Null values are allowed.
The relationship is called identifying if the child entity instance is identified (uniquely identified) through its relationship to the parent entity. The attributes that make up the primary key of the parent entity are also included in the primary key of the child entity. Child entity with an identifying relationship always dependent.
The relationship is called non-identifying if the child entity instance is identified differently than through a relationship with the parent entity. The attributes that make up the primary key of the parent entity are also included in the non-key attributes of the child entity.
Communication power is the ratio of the number of instances of the parent entity to the corresponding number of instances of the child entity. For any relationship other than non-specific, this relationship is written as 1: n.
@ Stored procedures is an application (program) that combines queries and procedural logic (assignment statements, logical branching, etc.) and is stored in a database.
Stored procedures allow you to contain, together with the database, rather complex programs that perform a large amount of work without transferring data over the network and interacting with the client. Typically, programs written in stored procedures are associated with data processing. Thus, the database can be functionally independent level an application that can interact with other tiers to receive queries or update data.
@ Rules allow you to invoke execution given actions when changing or adding data to the database (DB) and thereby control the truth of the data placed in it.
Typically, an action is a call to a specific procedure or function. Rules can be associated with a field or record and, accordingly, be triggered when data in a specific field or table record changes. You cannot use rules when deleting data. Unlike restrictions, which are only a means of controlling simple conditions the correctness of data entry, the rules allow you to check and maintain arbitrarily complex relationships between data elements in the database.
@ Referential integrity is ensuring that the foreign key value of the child entity instance matches the primary key values in the parent entity.
Referential integrity can be monitored for all operations that modify data.
@ Normalization of relations is the process of constructing the optimal structure of tables and relationships in a relational database.
In the normalization process, data items are grouped into tables that represent objects and their relationships. Normalization theory is based on the fact that a certain set of tables has better properties in the inclusion, modification and deletion of data than all other sets of tables with which the same data can be represented. The introduction of normalization of relations in the development of an information model ensures the minimum amount of physical, that is, recorded on any medium, database and its maximum performance, which directly affects the quality of the information system. Information model normalization is performed in several stages (1st, 2nd and 3rd normal forms).
@ Data dictionary is a centralized repository of information about objects, their constituent data elements, relationships between objects, their sources, values, use and presentation formats.
@ Ensuring integrity database is called a system of measures aimed at maintaining the correctness of the data in the database at any given time.
The costs of checking and maintaining the accuracy of data can represent a significant portion of the total operational costs. For example, in transport companies, to control the correctness of data entry from travel documents, the parallel input of the same data by several operators is practiced. It is believed that the probability of committing the same error in this case will be extremely small and a simple comparison of the results of the input of different operators will help to obtain error-free data. In the DBMS, data integrity is ensured by the set special offers called integrity constraints.
@ Integrity constraints is a set of defined rules that establish the validity of data and the relationships between them.
System automated processing data is based on the use of a specific data model or information model. The data model reflects the relationships between objects.
2. The sequence of creating an information model
The information model creation process begins by defining the conceptual requirements of a number of users (Figure 2.1). Conceptual requirements can also be defined for some tasks (applications) that are not planned to be implemented in the near future. This may slightly increase the complexity of the work, however, it will help to fully take into account all the nuances of the functionality required for the system being developed, and will reduce the likelihood of its alteration in the future. Individual user requirements are integrated into a single “generic view”. The latter is called the conceptual model.
@ Conceptual model represents objects and their relationships without specifying how they are physically stored.
Thus, the conceptual model is essentially a domain model. When designing a conceptual model, all efforts of the developer should be directed mainly towards structuring the data and identifying the relationships between them without considering the implementation features and processing efficiency issues. The design of the conceptual model is based on the analysis of the data processing tasks being solved in this enterprise. The conceptual model includes descriptions of objects and their interrelationships that are of interest in the considered subject area and that are identified as a result of data analysis. This refers to the data used both in already developed application programs and in those that will only be implemented.
The conceptual model is then translated into a data model compatible with the selected DBMS. It is possible that the relationships between objects reflected in the conceptual model will subsequently turn out to be unrealizable by means of the selected DBMS. This will require a change in the conceptual model. The version of the conceptual model that can be provided by a particular DBMS is called the logical model.
@ Logical model reflects the logical connections between data items regardless of their content and storage medium.
The logical data model can be relational, hierarchical, or networked ... Users are allocated subsets of this logical model, called external models (in some sources, they are also called subschemes), reflecting their understanding of the domain. External model corresponds to the views that users get based on the logical model, while conceptual requirements reflect the perceptions that users initially desired and that formed the basis for the development of the conceptual model. The logical model is displayed in physical memory such as a disc, tape, or some other storage medium.
@ Physical model that defines data placement, access methods, and indexing techniques is called the internal model of the system.
From the point of view of applied programming, data independence is determined not by the programming technique, but by its discipline. For example, in order to avoid recompiling the application in any system change, it is recommended not to define constants (constant data values) in the program. The best solution is to pass values as parameters to the program.
All the actual requirements of the subject area and the corresponding "hidden" requirements at the design stage should be reflected in the conceptual model. Of course, every possible use and modification of the database cannot be envisaged. But in most subject areas, basic data such as objects and their relationships are relatively stable. Only change information requirements, that is, ways of using data to obtain information.
The degree of data independence is determined by the careful design of the database. Comprehensive analysis of domain objects and their relationships minimizes the impact of changing data requirements in one program on other programs. This is all-encompassing data independence.
3. Relationships in the model
A relationship expresses a display or relationship between two sets of data. There are relationships of the type " one to one», « one to many" and “Many to many". In the considered problem of automating the management of the work of a car dealership, if a client makes an order for the purchase of a car for the first time, the initial registration of his data and information about the order is carried out. If the client re-orders, only this order is registered. Regardless of how many times a given customer has made orders, he has a unique identification number (unique customer key). Information about each client includes the name of the client, address, telephone, fax, last name, first name, patronymic, attribute legal entity and a note. Thus, the attributes of the CLIENT object are "UNIQUE CLIENT KEY", "CLIENT NAME", "CLIENT ADDRESS", etc. The next object of interest to us is the CAR MODEL. This object has the attributes "UNIQUE MODEL KEY", "MODEL NAME", etc. The third object under consideration is the ORDER. Its attributes are "ORDER NUMBER", "CLIENT KEY" and "MODEL KEY". And the fourth object under consideration is the SELLER. Its attributes are "UNIQUE SELLER'S KEY", "SELLER'S NAME", "SURNAME" and "PATRONALY".
One-to-one relationship (between two types of objects)
Let us mentally return to the days of the planned-distribution economy. Let's say one customer can make only one order at a given time. In this case, a relationship is established between the CLIENT and ORDER objects. one to one", Denoted by single arrows, as shown in fig. 2.2, a.
Rice. 2.2. The relationship between two objects: a) "one to one"; b) "one to many"; c) "many to many"
Rice. 2.3. The relationship between data in a one-to-one relationship.
One-to-many relationship (between two types of objects).
At a certain point in time, one client can become the owner of several car models, while several clients cannot be the owners of the same car. A one-to-many relationship can be denoted by using a single arrow pointing towards one and a double arrow pointing towards many, as shown in Figure 4-2. 2.2, b.
In this case, several records of the second object (child or subordinate) will correspond to one data record of the first object (it is often called parent or main). One-to-many relationships are very common in relational database design. The dictionary is often used as a parent object, and unique keys for accessing the dictionary records are stored in the child. In our example, as such a reference, you can imagine the CLIENT object, which stores information about all customers. When accessing a record for a specific customer, we have a list of all purchases that he made and information about which is stored in the CAR MODEL object, as shown in Fig. 2.4. If there are some records in the child object for which there are no corresponding records in the CLIENT object, then we will not see them. In this case, the object is said to contain lost (lonely) records. This is not permissible, and in the future you will learn how to avoid such situations.
Rice. 2.4. Relationship between data in a one-to-many relationship.
If we look at the records of the CAR MODEL object, then in the CLIENT object we can get data about the customer who bought this car (see fig. 2.4). Please note that we will not receive customer details for lost records.
Many-to-many relationship (between two types of objects).
In this example, each salesperson can serve multiple customers. On the other hand, by purchasing cars at different times, each customer may well be served by different sellers. There is a many-to-many relationship between the CLIENT and SELLER objects. This relationship is indicated by double arrows, as shown in Fig. 2.2, c.
In fig. 2.5 shows a diagram according to which the data will be interconnected in this case. By looking at the data in the CLIENT object, we will be able to find out which sellers served a specific customer. However, in the object SELLER, in this case, we will have to create several records for each seller. Each line will correspond to each customer service by the seller. With this approach, we will face serious problems. For example, we cannot enter a unique key for each seller in the SELLER object, since inevitably one seller will serve several customers, and in this case we will have several records for the same seller.
Rice. 2.5. Relationship between data in a many-to-many relationship
According to relational database theory, storing a many-to-many relationship requires three objects: one for each entity and one for storing the relationships between them (an intermediate object). The intermediate object will contain the identifiers of the related objects, as shown in Fig. 2.6.
Rice. 2.6. Displaying the relationship between data in a many-to-many relationship using an intermediate object
The relationships between objects are part of the conceptual model and must be displayed in the database. Along with the relationships between objects, there are relationships between the attributes of an object. It also distinguishes between one-to-one, one-to-many, and many-to-many relationships.
One-to-one relationship (between two attributes)
We assume that the key (number) of the customer is his unique identifier, that is, it does not change with subsequent orders from this client. If, along with the customer's number, another unique identifier is stored in the database (for example, a passport number), then there is a one-to-one relationship between these two unique identifiers. In fig. 2.7, a this relationship is indicated by single arrows.
One-to-many relationship (between two attributes)
The customer's name and customer number coexist. Clients with the same names there may be many, but they all have different numbers. Each client is assigned a unique number. This means that there is only one name for a given customer number. The relationship "one to many" is indicated by a single arrow in the direction of "one" and a double arrow in the direction of "many" (Figure 2.7, b).
Many-to-many relationship (between two attributes)
Several customers with the same name could be served by multiple vendors. Several sellers with the same name may have received orders from multiple customers. There is a many-to-many relationship between the customer name and salesperson name attributes. We denote this relationship with double arrows (Fig. 2.7, in).
a) 
b) 
v) 
Rice. 2.7. Relationships between two attributes:
a) the relationship "one to one"; b) one-to-many relationship» c) the relationship "many to many»
Types of data models
Hierarchical and network data models began to be used in database management systems in the early 60s. In the early 70s, it was proposed relational model data. These three models differ mainly in the way they represent relationships between objects.
The hierarchical data model is built on the principle of a hierarchy of object types, that is, one type of object is the main one, and the rest, located on lower levels hierarchy - subordinates (Fig. 2.8). A one-to-many relationship is established between the main and subordinate objects. In other words, there are several subordinate object types for a given main object type. At the same time, for each instance of the main object, there can be several instances of subordinate object types. Thus, relationships between objects are similar to relationships in a family tree with one exception: there can be only one original (main) object type for each spawned (subordinate o) object type. On the rice. 2.8 nodes and branches form a hierarchical tree structure. A node is a collection of attributes that describe an object. The highest node in the hierarchy is called the root (this main type object). The root node is at the first level. Dependent nodes (subordinate object types) are at the second, third, etc. levels.
Rice. 2.8. Hierarchical data model schema.
V network model These concepts of master and subordinate objects are somewhat expanded. Any object can be both master and subordinate (in the network model, the master object is denoted by the term “owner of the collection”, and the subordinate by the term “member of the collection”). The same object can be both the owner and the member of the collection at the same time. This means that each object can participate in any number of relationships. A diagram of the network model is shown in Figure 2.9.
Figure 2.9. Network data model diagram.
In the relational data model, objects and the relationships between them are represented using tables, as shown in Fig. 2.10. Relationships are also considered as objects. Each table represents one object and consists of rows and columns. In a relational database, each table must have a primary key ( key element) Is a field or combination of fields that uniquely identifies each row in the table. Due to its simplicity and naturalness of presentation, the relational model is most widely used in DBMS for personal computers.
Rice. 2.10. Relational data model schema.
When developing a database, the subject area is first explored (for example, "University"). The main objects are highlighted in it. They can be real ("Student") or abstract ("Discipline"). Each object is characterized by a set of properties - object attributes (data fields)... For each object, the attributes are filled with specific values. Attributes can be simple and key.
Key attribute (key)- it individual elements data by which all other data elements can be determined ("Gradebook number"). The key can be simple or compound ("Surname", "First name", "Patronymic").
After defining the main objects of the subject area using their key attributes, links are established between these objects:
a) 1: 1 ("one to one") - each instance of object A corresponds to only one instance of object B and vice versa (Figure 17).
Figure 17 - One-to-one relationship
b) 1: M ("one-to-many") - each instance of object A can correspond to 0, 1 or several instances of object B, but each instance of object B corresponds to only 1 instance of object A (Figure 18).
Figure 18 - One-to-many relationship
c) M: M ("many to many") - each instance of object A corresponds to 0, 1 or several instances of object B and vice versa (Figure 19).
Figure 19 - Many-to-many relationship
Highlighted main objects of the subject area with established links between them are infological model .
Relationship
A subject area object can be represented in the form of a table-relation - a table of a special kind, in which:
Each line contains information about one instance of an object (relation line - cortege);
· All columns are homogeneous, that is, all elements in a column have the same type and length, have a name and contain information about a separate attribute of an object;
· Each item represents one item of data about the object;
· All rows and columns are unique (no repetitions);
· There are no empty cells in the tables.
Databases based on relationship tables are called relational (relation). A set of relations (tables) is used in a database to store information about objects in the real world and to model connections between them. For example, to store the "student" object, use the relation STUDENT, in which the properties of the object are located in the columns of the table, which are the attributes of the object (table 8):
Table 8 - Ratio STUDENT
The list of relationship attribute names is called relationship scheme... Relationship diagram STUDENT can be written like this: STUDENT = (Surname, Age, Group ).
A relational database is a set of interrelated relationships. Each relation (table) in the computer is represented in the form of a file of records.
Eight different operations of set theory and relational algebra (union, selection, projection, intersection, addition, multiplication, difference, division) can be performed on tables - relations. As a result, many new (calculated) tables - relations (reports, selections, queries, etc.) can be obtained from the introduced (basic) relations.
Due to the fact that information in databases is presented in two forms - stored information (original, entered tables) and calculated information (tables obtained on the basis of the original ones), you can significantly save memory and speed up the processing of this information.
To create a simple and reliable database, you need to normalize relationships. Normalization of relations– step by step process decomposition of relations into smaller and simpler ones. Despite the increase in the number of relations at the same time, data access operations are significantly accelerated due to improved correctness, elimination of duplication and ensuring the consistency of data in the database.
There are several normal forms:
1st normal form. The relationship is considered to be in the first normal form if all its attributes are indivisible (simple). For example, the relation shown in Figure 20 below is not normalized because it contains a complex attribute Sport... To bring this relation to a normalized form, you need to get rid of this complex attribute.
Figure 20 - Reduction to the first normal form
In the resulting relationship, the key is composite, consisting of attributes Surname and Kind of sport.
2nd normal form. A relationship is considered to be in second normal form if all of its attributes depend on the composite key as a whole, and not on its parts. Therefore, if a relation is in first normal form and has a simple rather than a composite key, then it is automatically in both first and second normal forms.
For example, regarding STATEMENT(Figure 21), which has a composite key " Student, Discipline ", attribute Lecturer depends only on Disciplines rather than from the whole key. This relationship can be normalized by "breaking" it into two relationships PROGRESS and TEACHER:
STATEMENT = (Student, Discipline, Lecturer, Assessment)
PERFORMANCE = (Student, Discipline, Grade) TEACHER = (Discipline, Lecturer)
Figure 21 - Reduction to second normal form
3rd normal form. A relationship is considered to be in third normal form if dependencies between non-key attributes (transitive dependencies) are removed. For example, regarding SUBJECT = (Name, Lecturer, Chair, Phone) is not a key attribute Telephone depends on not key attribute Department.
To eliminate the transitive dependence, it is necessary to "split" the original relation into two DISCIPLINE = (Name, Lecturer, Department) and DEPARTMENT DATA = (Department, Telephone).
Further simplification of the tables is due to further restriction types of dependencies between relationship attributes.
After the normalization of relations and the establishment of connections between them, an infological model of the subject area is formed. Below (in Figure 22) is an example of an infological model of a company that draws up transactions with customers through its employees-managers:
Figure 22 - Model of the firm
Based on the infological model, a data model is developed that describes the logical structure of the database on data description language (DL), - datalogical model (DM).
The data model is used to bind the DM to the storage medium physical layer – physical model (FM). At this stage of the physical design of the database, the choice of the type of medium is carried out, the format of the stored records is developed, and the methods for accessing the data are designed.
DBMS
After that, it is already possible to form (fill) the database and directly work with it. Working with databases is reduced to performing the following operations:
1) recording (filling the database);
2) viewing;
3) editing (adding, deleting, correcting);
4) sample (requests, reports).
These operations of accumulation and data manipulation are performed by special program – database management system (DBMS).
According to the technology for solving problems performed by the DBMS, databases can be divided into two types:
Centralized database (stored entirely on the OVC of one computer system and, if the system is part of a network, then access to this database of other systems is possible);
Distributed database (consists of several, sometimes overlapping or duplicating each other, databases stored on the OVC of different network nodes).
The DBMS provides access to database data in two ways:
Local access(assumes that the DBMS processes the database, which is stored on the OVC of the same computer);
Remote access(this is a call to the database, which is stored on one of the network nodes).
Remote access can be performed using file-server or client-server technology. The file server technology assumes the allocation of one of the computing systems called a server for storing the database. All other computers on the network (clients) play the role of workstations that copy the required part of the centralized database into their memory, where processing takes place. Client-server technology assumes that a server dedicated to storing a centralized database additionally processes requests from client workstations. The client sends a request to the server. The server sends the data to the client, which is the result of a search in the database at its request.
Database management system - a set of software and language tools.
Software provide the organization of input, processing and storage of data, as well as ensure the interaction of all parts of the system during its operation (setup, testing, restoration).
Language tools provide user interaction with the database. These include:
- data manipulation languages(YAMD) - languages of queries to the database, which is a system of commands for working with data (selection, query, insert, delete, etc.);
- data definition languages(YOD) - languages designed to create a database schema (descriptions of data types, database structure, interaction and relationships between elements).
Figure 22 - Scheme of user interaction with the database
Modern DBMS – application program, which is designed to facilitate the work of an unskilled user with the database. He works with it in natural language without knowing the data manipulation language and data definition language (Figure 22). One of the examples of such a DBMS is the well-known product Microsoft- Access DBMS.
One - unambiguous connections
One - unambiguous connections take place when each instance of the first object (A) corresponds to only one instance of the second object (B) and vice versa, each instance of the second object (B) corresponds to only one instance of the first object (A). It should be noted that such objects can be easily combined into one, the structure of which is formed by combining the attributes of both original objects, and any of the alternative keys can be selected as the key attribute, i.e. the keys of the original objects. Graphic image one - unambiguous connections are the group - the headman, the company - the current account in the tank, etc.
Fig. 1 Graphic representation of one-to-one relations of objects
One - multivalued relationships (1: M)
One - multivalued relationships (1: M)- these are such relationships when an instance of one object (A) can correspond to several instances of another object (B), and each instance of the second object (B) can correspond only to one instance of the first object (A).
Fig.2 Graphical representation of a single - multi-valued connection of object relations.
In this connection, object A is the main object, and object B is subordinate, i.e. there is a hierarchical subordination of the object In object A. An example of one - multivalued relations are subdivisions - employees, a department - a teacher, a group of students, etc.
Many - multi-valued relationships (M: N)
Many - multi-valued relationships (M: N)- this is when, each instance of one object (A) can correspond to several instances of the second object (B) and vice versa, each instance of the second object (B) can also correspond to several instances of the first object (A).
Fig. 3 Conversion of a link of the M: N type through an object - a link
The bind object must have an identifier formed from the identifiers of the original objects Ka and KB.
An example of many - multivalued relationships is the relationship suppliers - goods, if one supplier supplies different names of goods, and the goods of the same name are supplied by several suppliers.
Defining links between information objects
Let us consider the definition of links between information objects and the type of relations by which they are characterized, for the subject area of the Educational process.
Relationships between objects GROUP - STUDENT are characterized by one - multivalued relations (1: M), since one group includes many students, and one student is included in only one group. The connection between them is carried out by the group number, which is the unique identifier of the main object GROUP is included in the composite identifier of the STUDENT object (see Table 1)
Similarly, a connection is established between the objects CHAIR TEACHER, which are also in one - polysemantic relations. The connection between them is carried out according to the unique key of the main object CHAIRMAN - the code of the department, which in the subordinate object TEACHER is descriptive.
Table 1. Objects reference information about students, groups and subjects
Table 2. Grouping of requisites by information objects of the document List of teachers of the department
In the table, the designations for the key are adopted: P - simple, U - unique.
In each group, during the semester, classes are held in different subjects (object STUDY). On the other hand, each activity is specific to each group. Therefore, there is a one-to-many relationship between the SUBJECT-STUDY objects.
For each subject, there are many classes in different groups by different teachers. On the other hand, each lesson is focused on a specific subject, which defines a one-to-many relationship between SUBJECT-STUDY objects. Similarly, one-to-many relationships between objects are defined. TEACHER - STUDY.
The STUDY object actually plays the role of a bundle object in multivalued object relations.
Fig. 4 Multi - multi-valued connections of information objects
Fig. 5 Information - logical model of the subject area Educational process
The PROGRESS object contains data on the progress (grade) of a specific student in a specific lesson. Therefore, it is associated with the STUDENT object and the STUDY object. One student has grades for several classes, but each grade always refers to one specific student. This means that the object PROGRESS is subordinate and is in a one-to-many relationship with the STUDNT object. The object is SUCCESS, as well as is subordinate and is in a one - multi-valued relationship with the object STUDY. The SUCCESS object plays the role of an object - a bundle of multivalued relations between the STUDENT and STUDY objects. Many - polysemantic relationships between these objects are determined by the fact that one student corresponds to many activities displayed by the STUDY object, and one lesson is conducted with many students.
Table 3 lists all one - multivalued links between objects, indicates the keys by which links should be established, and define the main and subordinate information objects in these links.
Table 3 Information Object Relationships
Information - logical model of the subject area Educational process
The information-logical model is presented in a canonical form and the objects in it are arranged by levels. The level of other objects is determined by the most long way to the object from the zero level. Such an arrangement of objects gives an idea of their hierarchical subordination, makes the model more visual and makes it easier to understand one - multivalued relations between objects.
Logical structure relational base data
The logical structure of the relational database Access data is an adequate display of the obtained information-logical model, which does not require additional transformations... Each data model information object is mapped to a corresponding relational table. The structure of a relational table is determined by the requisite composition of the corresponding information object, where each column (field) corresponds to one of the object attributes. The key attributes of an object form a unique key for a relational table. For each column, the type, data size, and other properties are specified. The rows (records) of the table correspond to the object instances and are formed when the tables are loaded.
Links between data model objects are implemented by the same attributes - communication keys in the corresponding tables. In this case, the link key is always the unique key of the main table. The link key in the subordinate table is either some part of the unique key in it, or a field that is not part of the primary key (for example, the department code in the TEACHER table). The link key in the subordinate table is called foreign key... Access can create data schema that graphically displays the logical structure of the database. The definition of single - multi-valued links in this scheme should be carried out in accordance with the constructed data model. Appearance data schema is almost the same as graphical representation informational - logical model. For the data model built in the above example, the logical structure of the database in the form of an Access data schema is shown in Figure 2.7.
In this diagram, the rectangles represent the database tables with a complete list of their fields, and the links show which fields are used to interconnect the tables. Key field names are on the left side complete list fields of each table.
| Lesson planning and lesson materials | 8 classes | Planning lessons for the academic year | Tabular Models
Lesson 12
Tabular Models
Tabular Models
 |
 |
|
Issues under study:
Tables of the "object-property" type.
- A table of the "object-object" type.
- Binary matrices.
Object-property tables
Another common form of information model is rectangular table consisting of rows and columns. The use of tables is so common that no additional explanation is usually required to understand them.
As an example, consider table 2.1.
When compiling a table, only the information that interests the user is included in it. For example, in addition to the information about books that are included in table 2.1, there are others: publisher, number of pages, cost. However, for the compiler of Table 2.1, there was enough information about the author, title and year of publication of the book (columns "Author", "Title", "Year") and information allowing to find a book on the shelves of bookshelves (column "Shelf"). It is assumed that all shelves are numbered and, in addition, each book has its own inventory number (column “Number”).
Table 2.1 - it is an information model of the book fund of a home library.
The table may reflect a certain process taking place over time (Table 2.2).
The readings, which are listed in table 2.2, were taken within five days at the same time of the day. Looking at the table, it is easy to compare different days in terms of temperature, humidity, etc. This table can be seen as information model the process of changing the state of the weather.
Tables 2.1 and 2.2 refer to the most commonly used table type. They are called "object-property" tables..
One line of such a table contains information about one object (a book in the library or the state of the weather at 12-00 on a given day). Columns - individual characteristics (properties) of objects.
Of course, the rows and columns in Tables 2.1 and 2.2 can be swapped by rotating them 90 °. Sometimes they do it. Then the rows will correspond to the properties, and the columns will correspond to the objects. But most often tables are built so that there are more rows than columns. As a rule, there are more objects than properties.
