2. 2. CONSTRUCTION OF THE “OBJECT - PROPERTY - RELATIONSHIP” MODEL

To describe ILM, both analytical (descriptive) type languages ​​and graphic tools hereinafter applied graphic method mapping the “object-property-relationship” model. IN subject area in the process of its examination and analysis, classes of objects are identified. Object class called a collection 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: students, teachers, audiences, etc. Objects can be real, as mentioned above, or they can be abstract, such as objects, which students study.

When reflected in information system each object is represented by its identifier, which distinguishes one object of a class from another, and each class of objects is represented by the name of that class. Thus, for objects of the “SUBJECTS STUDYED” class, the identifier of each object will be “SUBJECT NAME”. The ID 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, but their values, naturally, may differ. For example, for objects of the “STUDENT” class, such a set of properties describing objects of the class could be “YEAR OF BIRTH”, “GENDER”, etc.

When describing a subject area, it is necessary to depict each of the existing classes of objects and the set of properties fixed for objects of this class.

We will use the following notation to display objects and their properties (Fig. 2. 3).

Property

Rice. 2.3 Designation of objects and their properties

Each class of objects in the info logical model a unique name is assigned.

When constructing an information model, it is advisable to give a verbal interpretation of each entity, especially if an ambiguous interpretation of the concept is possible.

Rice. 2.4 Image of the object-property relationship

When describing a subject area, it is necessary to reflect the connections between the object and the properties that characterize it. This is depicted simply as a line connecting the designation of an 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's call these properties single. For other properties, it is possible for one object to have several values ​​at the same time. Let, for example, when describing an “EMPLOYEE”, fix as his property the “Foreign LANGUAGE” that he speaks. Since an employee may know several foreign languages, then we will call such a property plural. When depicting the connection 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's call these properties static, and those properties whose value can change over time will be called dynamic.

Another characteristic of the relationship between an object and its property is whether this property is present in all objects of a given class or absent in some objects. For example, for individual employees the property “ACADEMIC DEGREE” may exist, but other objects of this class may not have the specified property. Let's call these properties conditional.

When depicting the connection between a conditional property and an object, we will use dotted line, and to denote dynamic and static properties we will use the letters D and S above the corresponding line.

Sometimes in an information model it is useful to introduce the concept “composite property”. Examples of such properties are “ADDRESS”, consisting of “CITY”, “STREET”, “HOUSE” and “APARTMENT”, and “BIRTH DATE”, consisting of “DAY”, “MONTH” and “YEAR”. In the ILM, to denote a composite property, we use a square, from which lines emanate, connecting it with the designations of its constituent elements (Fig. 2. 4).

The infological model displays not individual instances of objects, but classes of objects. When the designation of an object is depicted in the ILM, it is clear that we're talking about about a class of objects that have the described properties. Therefore, in most cases, it is possible not to explicitly introduce a designation for a class of objects into the information model. An explicit representation of a class of objects is necessary only if the software for a given class of objects records not only the 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 “ENTERPRISE EMPLOYEES” not only the age of each employee is recorded, but also the average age of all employees, then the infological model must 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 captures connections between objects of different classes. There are connections of the type “one to one” (1: 1), “one to many” (1: M), “many to many” (M: M). Sometimes these types of connections are called the degree of connection.

In addition to the degree of connection in the information model, to characterize the connection between different entities, it is necessary to indicate the so-called “membership class”, which shows whether an object of a given class may not be related to any object of another class. An entity's membership class must be either required or optional.

Let's explain what was said on specific examples. As mentioned above, the infological model is not built for a single object, but displays classes of objects and connections 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 written “Entity”, and relationship is “Relationship”). However, sometimes, in addition to ER-type diagrams, ER-instance diagrams are used.

Suppose that the information model displays the relationship between two classes of objects: “EMPLOYEE” and “Foreign LANGUAGE”.

Let's assume that the domain is a factory where some employees know a foreign language, but none of them speaks more than one language. Naturally, there are many languages ​​that none of the employees speak, and also that some of the employees speak the same foreign language (Fig. 2. 6).

s1. .ya1

s2. .ya2

s3. .ya3

s4. .ya4

s5. .ya5

s6. .ya6

s7. .ya7

Rice. 2.6 Diagram of ER - instances

In this case, the diagram of ER instances will have the form shown in Fig. 2.6, and the ER-type diagram is as in Fig. 2. 7.

Rice. 2. 7. Diagram of E - R types

Let us further assume that the subject area is an institute, and the “PERSON” object represents applicants entering this institute. Each of the applicants must be proficient in some foreign language, but no one speaks more than one language (Fig. 2. 8). In this case, the diagram of ER instances will have the form shown in Fig. 2.8, and the ER-type diagram is as in Fig. 2. 9.

Personality Language

l1 i1

l2 i2

l3 i3

l4 i4

l5 i5

l6 i6

l7 i7

In both the first and second cases considered, there is a relationship M between the entities: 1. In the diagram, this is shown 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 the data entities.

The difference in the situations under consideration is that in the first case, the membership class is optional for both entities, and in the second case, for the “PERSONALITY” entity, the membership class is mandatory. In the diagram (Fig. 2. 9) this is displayed by a point in the rectangle corresponding to the object “PERSONALITY”.

Let the subject area be the same as in the previous case, but there are situations where some applicants know several foreign languages. In this case, the connection between objects will be of type M: M.

For such a subject area, the diagram of ER instances will have the form shown in Fig. 2.10, and the ER-type diagram is as in Fig. 2.11.

Personality Language

l1 i1

l2 i2

l3 i3

l4 i4

l5 i5

l6 i6

l7 i7

Let us assume that the subject area is a certain linguistic institute, in which each and every employee necessarily knows several foreign languages, and for each of the languages ​​known to science in this institute there is at least one specialist who speaks it.

In this case, the relationship between the entities will be M:M and the membership class of both entities is mandatory.

(An example could be given, but the point is clear.)

Above we looked at 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 considered 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 consideration, an object may be considered simple, and in another, the same object may be considered complex. For example, the “chair” object in the material assets accounting subsystem will be considered as a simple object, but for an enterprise that produces chairs, it will be a composite object (including “legs”, “back”, “seat”, etc.).

There are several varieties complex objects: Composite objects, generic objects and aggregated objects.

Composite object corresponds to the mapping of the “whole-part” relationship. Examples of composite objects are NODES-PARTS, CLASS-STUDENTS, etc.

To display composite objects in an information model, no special symbols are usually used. The relationship between a 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, “PARTS” and “NODES” are connected by a relationship of type M: M, and “GROUP” and “STUDENTS” are connected by a relationship of 1: M.

Generic object reflects the presence of a “genus-type” relationship between objects of the subject area. For example, the objects STUDENT, SCHOOLBOY, GRADUATE, TECHNICAL SCHOOL STUDENT form the generalized object STUDENTS. The objects that make up a generalized object are called its categories.

Both “generic” objects and “specific” objects can have a certain set of properties. Moreover, the so-called inheritance of properties is observed, i.e., a “species” object has all the properties that a “generic” object has, plus properties inherent only to objects of this type.

Determining genus-species relationships means classifying objects of a subject area according to certain characteristics. Subclasses can be distinguished in the information model in explicit and implicit form. In the first case, when displaying graphically, enter special designation for a subclass. In Fig. 2. 14 shows a fragment of the infological model, reflecting the generalized object “PERSONALITY” for higher educational institution. There are several categories for him: TEACHER, STUDENT, GRADUATE. A triangle was used to indicate a subclass in the diagram.

Naturally, the classification can be multi-level. Thus, in the example under consideration, the generalized object “PERSONALITY” can be divided into two subclasses: EMPLOYEE and STUDENT. EMPLOYEES, in turn, can be classified into FACULTY, TEACHING STAFF, ADMINISTRATION, etc.

Personality

Rice. 2.14 Generalized object image

The classes of objects identified in the subject area can be either intersecting or non-intersecting. To display this information in an infological model, you can use an intersection graph, the vertices of which correspond to 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 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 “DELIVERY” combines the objects “SUPPLIER”, which supplies products, “CONSUMER”, which receives these products, as well as the supplied “PRODUCTS” itself. A unique object is “DELIVERY DATE”. An aggregated object, like a simple object, can have properties that characterize it. In the example under consideration, such a property could be the size of the delivery.

Aggregated objects are usually called verbal nouns (for example, supply-supply, release - release, sell-sale, etc.).

Rice. 2.16 Image of the aggregated object

To display an aggregated object in the information model, we will use the following conventions:

We will depict the aggregated object itself as a diamond, next to which the name of the corresponding object is indicated. This rhombus must be connected to symbols those objects that form this aggregated object. The properties of an aggregated object are displayed in the same way as for a simple object. On rice. Figure 2.16 shows the aggregated object “DELIVERY OF PRODUCTS”.

Structural elements of the database

In the description of a data object, it is necessary to distinguish 2 components: structure and instance.

Structure– a list of object attributes and characteristics of the attributes.

Instance– a set of attribute values.

The structure changes extremely rarely. The instance is subject to change.

When stored on a computer, a database corresponds to a group of files and folders, and a file corresponds to a set of objects. Each object has a corresponding entry in the file. Each attribute has a corresponding field in the record.

To describe an attribute, use the following characteristics:

1. name, for example, nContract, cStudent;

2. type, for example, symbolic, numeric;

3. length, for example, 15 bytes;

4. precision, for numerical data.

5. description, comment;

6. image format on screen and paper;

7. hint;

8. input format;

9. initial value;

10. range of values.

Key is a means of organizing objects in a set. The key contains key expression, composed of object attributes. Ascending value key expression objects are presented for viewing and processing.

You can specify multiple keys for one set. For example, for the Employees set, you can set the key according to the alphabet of last names, the employees will be presented in alphabetical order.

The key is called primary, if one value of its expression selects 0 or 1 object from the set. For example, for recruiting employees, the “By personnel number” key is primary, since either no or only one employee is allocated based on one value of the personnel number.

The key is called secondary, if one value of its expression identifies 0 or more objects from the set. For example, the key for recruiting employees, the “By last name alphabetically” key is secondary, since among the employees there may be namesakes.

According to the axiom of difference, every set has a primary key. In extreme cases, its expression includes all the attributes of the object in the set.

A good practice is to introduce an artificial attribute for the data object, “Sequence number in the set,” which is automatically assigned and unique. The key for this attribute is called surrogate.

Note that the concepts of primary and secondary key do not depend on the number and values ​​of objects in the set. Primary and secondary keys are for empty sets.

Let there be n sets of objects E 1, E 2, ..., E n.

Communication is called a set of sequences of objects (е i 1, е i 2, …, е i n), where е i 1 О Е 1, е i 2 О Е 2, …, е i n О Е n.

With the help of connections, sets of objects are combined into a single information structure.

There are three types of connections between two sets of objects (n=2):

1. one to one (1:1);

2. one to many (1:M);

3. many to many (M:N).

"one to one", if for each object from the first set you can specify 0 or 1 object from the second set and for each object from the second set you can specify 0 or 1 object from the first set.

Examples of 1:1 relationships are connections between:

· students and grade books,

between states and currencies,

between officers and service weapons,

· between citizens and foreign passports. Each student either does not have a grade book, or has only one.

For each record, either the student is not specified, or there is only one.

The connection between two sets E 1 and E 2 is of the type "one to many" 0 or more 0 or 1 object from the first set.

Examples of 1:M connections are connections between

· banks and deposits,

· deposits and contributions,

between groups and students,

between departments and employees,

between statements and statement lines,

· between clients and requests.

Each bank either has no deposits (the bank has not yet opened) or may have many deposits. For each deposit, either the bank is not specified, or there is only one.

The connection between two sets E 1 and E 2 is of the type "many to many", if for each object from the first set you can specify 0 or more objects from the second set and for each object from the second set you can specify 0 or more objects from the first set.

Examples of M:N relationships are relationships between

· products and countries,

between students and disciplines,

between employees and projects,

between orders and products,

between stores and customers.

Each product may come from many countries or none at all. Each country may supply many products and not supply any.

Graphically, connections are represented by arrows (Fig. 4.5).

In real DBMSs, only one type of communication is implemented - one to many.

The 1:1 relationship is obtained from the 1:M relationship by limiting it.

To implement the M:N connection, it is introduced new set objects and two 1:M connections are used.

For example, the relationship between countries and products of type M:N is obtained using the “supplies” data set (Fig. 4.6).

0

(Lecture 7)

Design and creation of a database Analysis of the subject area. Identification of classes of objects and relationships A subject area is defined if the objects existing in it, their properties and relationships between them are known)

An object in the conceptual approach is something about which information should be accumulated in an information system.

One of important stages domain analysis is the identification and description of classes of objects (entities) and the relationships between them. The description can be obtained in any form, but for the convenience of the design process it is formalized in the form of tables.

Object classes

How to identify classes of objects in a subject area?

An object class (entity type, entity) is a significant thing about which an enterprise must store information.

Signs of a class of objects existing in the subject area:

a) something important about which the company needs to store information;

c) named concept;

d) noun;

e) there is a class of objects if there is a real significant object;

Having identified an object class, you need to give it a name. It must be unique.

The terms used in the enterprise are chosen as the name.

A name is invented when all other possibilities have been exhausted, since invented names can lead to misunderstandings and duplication.

The name must be agreed with the customer.

A name can consist of more than one word (words that clarify the name - adjectives, etc.). Often the same thing is called by the same name, then it is necessary to choose one main name, and describe the rest as synonyms.

When identifying a class of objects, a group of things is identified, consisting of individual elements(objects). An object class is a class or category of things. For example, the object class "DEPARTMENT" consists of specific objects " Educational and methodological department", "Chief Mechanic's Department".

Stages of identifying and modeling a class of objects:

a) research of each noun identified during the analysis of the subject area at the enterprise and identification of its significance;

b) identifying whether there is information about this noun that needs to be stored for this enterprise;

c) assigning a name to a class of objects in the singular;

d) checking whether one object of an object class can be distinguished from another;

e) description of a class of objects to verify that everyone (developers, customers) puts the same meaning into this term;

Object Class Properties

For each class of objects, its properties (entity attributes) are determined. A property is a specific piece of information.

A property describes a class of objects. This is a qualitative or quantitative description of a class of objects.

The property might look like this:

Descriptive words, phrases;

Prepositional constructions (salary amount for each employee);

Possessive nouns and pronouns (others, end date, sign of obsolescence).

Each property is given a name. Names should be clear and unambiguous.

What information about the object class should be stored;

What information about the object class should be displayed or printed;

Is this property really needed?

Customers often forget about their specific needs - they think that the field will appear on the screen or report itself, and do not see the need to mention it.

When studying the documentation existing at the enterprise, it is necessary to pay attention to outdated requirements previous systems, For example, old uniform output document - party membership, nationality.

It is also necessary to mark derived and aggregated data; for each class of objects only the original properties are recorded.

Derived and aggregated properties are described separately and are formed, as a rule, by the program based on the values ​​of the original properties. The need to store such properties is quite rare.

There are a number of requirements for a property name. Names should be clear and unambiguous, for example the name of the property "quantity" can lead to confusion - returned, delivered? It is necessary to choose more specific names: “delivery size”, “order quantity”, etc. If a name consists of more than one word, they are separated by spaces.

The most common example is the "date" property. Unless specifically stated what the date is, it may be interpreted as date of birth, date of hire.

If it is necessary to store both, add specific property, for example, in addition to the hiring date, it is also necessary to store the date of election by competition. This can be identified at later stages of domain analysis.

The property identified during the analysis of the subject area must be broken down into the smallest components that make sense. The level of division depends on the needs of the enterprise.

Classic examples: address, lumber block dimensions (height, length, width) can be stored as a single property, but it is more useful to store as separate properties.

The difference between a class of objects (entity) and a property (attribute) is given in Table 4.

Table 4 - Differences between object class and property

Having defined a property, you need to make sure that for each specific object the property can have one single value.

If more than one value is found for a property, this indicates a missing property in the object class or a candidate property. new class objects.

If you find a property that has its own properties, then it is not a property, but a class of objects. For example, the class of objects “OVERALLS” has been identified, which has the properties “number”, “name”, “color”. If, during further analysis and study of the relevant document in the subject area, that color must have an article in addition to the name, then “color” is no longer a property, but a class of objects “COLOR” with the corresponding properties “name”, “article”.

For each property, it is necessary to determine its optionality.

Def.: Property optionality - determining whether the value of a property of an object is mandatory when storing information about a specific object in the database.

A required property value must exist and be known for every object of the object class in question.

Optional property values ​​may not be known (or may not exist) for an object at the time it is created.

For example, the value of the “work start date” property is always known for a working employee, but the value of the “work end date” property may be unknown at a given point in time if the employee has an open-ended contract.

For each property, the following are also identified in the subject area and described:

Format (type, maximum length, average length ( regular size), place of decimal point, unit of measurement;

Valid values ​​(range, list of values, multiple ranges, default values);

When characteristics of properties are identified, domains can also be identified.

A domain (from a domain perspective) is a set of validation rules from a business perspective, constraints that apply to more than one property.

From the point of view of a relational database, a domain is an admissible set of values ​​on which it can be one or more attributes of one or more relational relationships are defined.

Using a domain, you can set: a range of values; list of specific values; several ranges; Mathematical equation; default value, etc. These rules are described in the database once and are applied to different properties. The most famous domain (yes, no).

There is the following technology for working with properties, containing steps:

Identification of a candidate property;

Associating a property with an object class;

Naming a property;

Defining the property format;

Determining whether a property is optional;

Determining the logical restrictions of a property imposed by the subject area (entry of a value into a range, etc.);

Checking that this is really a property and not a class of objects;

If necessary, create a domain.

All instances (objects) of the identified class of objects must be uniquely determined and identified. If an object cannot be uniquely identified in an object class, then it may not be an object class at all.

Defining unique identifiers

For each class of objects, unique identifiers must be identified.

A unique identifier is a property, collection of properties, or combination of properties and relationships used to uniquely identify an object in a class of objects.

A property that is part of a unique identifier must be optional.

There can be any number of unique identifiers in an object class. And the number of components (properties and connections) included in the unique identifier can be whatever you want.

A unique identifier can be defined at any stage of domain analysis, but to begin describing and designing an object class, it is necessary that each object class have unique identifiers.

Comment:

When choosing a method for identifying objects of a class of objects, it is necessary to model not the technological needs of the system being developed, but the business needs.

When using a numeric code as a unique identifier, you must ensure that the subject area has a corresponding document in which such a code is displayed.

For example, the properties “employee personnel number”, “department code” are already defined by the existing system at the enterprise accounting, the “position code” property is presented in the industry position classifier, etc.

Comment:

At the database design stage (hereinafter, when constructing the DLM DB - surrogate keys), a unique identifier can be generated technically, but during the analysis of the subject area, unique identifiers used by the enterprise are used.

If there are several unique identifiers, it is necessary to determine the main one. This is how an identifier is made that is more often used in business, for example, a “personnel number”. Or any unique identifier that has the shortest length and numeric type.

The most a large number of An object class such as “INDIVIDUALS/PERSON” has unique identifiers. Each object in such a class of objects can be uniquely identified by the following properties: “number”, “passport series”, “TIN”, “number driver's license", "token number". For the object class “POSITION” the following unique identifiers can be identified: “code”, “name”, “ short name».

It should be noted that the more classes of objects are identified during the analysis of the subject area, the more normalized the structure will then be relational base data.

Almost any noun in a domain is eligible to be defined as an object class, since almost every noun has,

at least a set of three properties: the name of the object, the short name of the object, the numerical equivalent of the name of the object (code, number, cipher).

You can see the classes of objects by studying in detail the information flows in the enterprise that are subject to automation.

Information flows are represented by documents.

Any document is a candidate for an object class. The document has a header, which, as a rule, indicates the name of the document and the date of its formation.

The document has an informative part, which contains qualitative and quantitative indicators.

At the bottom of the document are the names and positions of the persons signing the document.

The document may also contain the name, address and other details of the company issuing the document.

Thus, when studying the document, you can see and highlight the following classes of objects: “ENTERPRISE/LEGAL ENTITY” or

“STRUCTURAL UNIT OF THE ENTERPRISE”; "TYPE OF STRUCTURAL UNIT"; "ADDRESS"; "LOCALITY"; “TYPE OF SETTLEMENT”; "STREET"; “STREET TYPE” (street, avenue, lane, driveway, etc.); "DOCUMENT"; "DOCUMENT POSITION"; "INDIVIDUAL"; "JOB TITLE"; “RECORD OF THE WORK OF AN INDIVIDUALS” (start date, end date); “PRODUCT/SERVICE”; “Object” (accounting).

What is the DOCUMENT POSITION object class? Any document usually has several positions (order positions, price list positions, invoice positions, account card entries, etc.). Thus, the 1:M type relationship existing in the subject area is visible: “each DOCUMENT must have one or more POSITIONS”; With reverse side the connection reads - “each DOCUMENT POSITION must refer to the same DOCUMENT.” In addition, each document position has its own properties - a number, some quantitative indicators (number of accounting units, price per unit, and others).

For each subject area, you can see a list of object classes that is mandatory for all subject areas. Each subject area, in the broad sense of the word, reflects the work of an enterprise or organization - manufacturing enterprise, educational or medical institution, trade organization, warehouse, rental point, home economic sphere, and so on. The name (full or short) of the enterprise or organization appears in various output documents and reports. Thus, in the subject area there is an object class ENTERPRISE or STRUCTURAL UNIT OF ENTERPRISE. In addition, it is often necessary to keep records of the address and telephone number of this enterprise. Mandatory in the subject area

present individuals holding certain positions, recording with their signatures the accounting (income or expense) of any object. Moreover, in order to solve problems of data analysis and then make appropriate management decisions, it is of interest for the subject area to store knowledge about the history of accounting for the state of a particular object. And another category of object classes required for each subject area are documents, on the basis of which all processes take place in a given subject area.

All the results of the analysis of the subject area in the process of identifying classes of objects and their properties can be summarized in the form of a formalized description, a table. An example of such a description is given in Table 5.

Table 5 - Formalized description of the subject area. Object classes, properties.

Property	Unique identificator	Physical characteristics of the property (type, length)	Property optionality (Yes/No)	Logical property restrictions (value range, uppercase, lower case for symbolic properties, etc.)	Processes for property values
tab.number				> 0
				> 0	Vv, Pr, Ob
				Perv. capital letter	Vv, Pr, Ob
date of birth				DD.MM.YYYY	Vv, Pr, Ob
JOB TITLE

The following abbreviations are used in the table: U - unique identifier, P - candidate primary key (main unique identifier), G - data generation, Vv - data entry, Pr - data viewing, Ob - data updating.

Relationships between object classes

Since everything in this world is connected, a parallel step in the analysis of the subject area, along with identifying classes of objects and their properties, is the step of identifying connections and associations that arise between classes of objects. Relationships represent information needs and business rules in an enterprise; their definition can be expressed as follows:

A named, meaningful association between two classes of objects.

The relationship that one thing has to another.

When considering communication, you need to think of it as two-way, bidirectional.

Each connection has certain characteristics.

Communication option number. This is a business rule that specifies whether a relationship must exist for every object in an object class (mandatory relationship) or not (optional relationship).

For example, at the enterprise it was revealed next rule: "each

a position may correspond to a specific category of position.” At some point in time, a document appears at the enterprise about the creation new category, but there are no posts yet that reference this category. But on the other hand, there is a rule: “each position in an enterprise must be assigned to one and only one position.” Thus, it can be seen that two different associations have been identified between the two classes of objects (“POSITION CATEGORY” and “POSITION”).

Power (maximum cardinal number). This is a business rule indicating how many such connections exist - one and only one, or many. If a link is found that has a cardinality of zero, that link is optional.

We are considering binary relationships (may be different)

Each side of the connection has a name. This is a description of the rules of business.

For example: “corresponds to”, “applies to”.

Names often come in pairs: “based on” - “is the basis for”; “purchased from” - “supplied”; “responsible for” - “is under responsibility.”

The name has great importance, it shows how well the relationships of information are understood.

Once you see a connection, you need to make sure it makes sense. To do this, it must be pronounced as a normal sentence in both directions (any connection is two-way), using the rule for pronunciation of the connection (Table 6).

Table 6 - Communication reading rule

Example of reading a connection: “each INDIVIDUALS may have zero, one or more EMPLOYMENT BOOK ENTRIES”; “Each EMPLOYMENT BOOK ENTRY must relate to one and only one INDIVIDUALS.”

Let's take a closer look existing types(power) of connections.

1. One-to-many relationship (1:M). This is the most common type of communication, having a power of one or more in one direction and one and only one in

friend. The object classes that are on the “one” side of this relationship are called the main or parent. An object class that is on the "many" side - a child or child.

In most cases, subordinate object classes are optional, but master object classes are required. That is, an object of the main class of objects can exist without a subordinate object, but a subordinate cannot exist without the main one.

From a database point of view, this means that first an object of the main object class is created in the database, and then objects of the subordinate class. If the 1:M relationship is optional on both sides, objects can be created arbitrarily. 1:M relationships, required on both sides, are very rare and mean that objects of two classes of objects cannot exist without each other.

Example of connection 1:M: “each STRUCTURAL UNIT OF THE ENTERPRISE may correspond to zero, one or more EMPLOYMENT BOOK ENTRIES.” On the reverse side: “Each EMPLOYMENT BOOK ENTRY must relate to one and only one STRUCTURAL UNIT OF THE ENTERPRISE.”

2. Many-to-many relationship (M:M or M:N). This is also a very common type of communication, especially on initial stages domain analysis. This connection has a capacity of one or more in both directions. An example of such a connection: “in each STRUCTURAL UNIT of an ENTERPRISE many INDIVIDUALS can work.” On the reverse side: “every

AN INDIVIDUAL can work in many “STRUCTURAL UNITS OF THE ENTERPRISE”.

Most M:M relationships are optional in both directions, that is, an object of one class of objects can exist without being bound to an object of another class of objects; any instance can appear first. M:M relationships that are required on both sides are very rare - objects of both object classes must be created at the same time.

It should be noted that in any subject area there are no “many_to_many” connections; at each moment in time everything is determined uniquely. The appearance of such a connection in project documentation shows that the subject area has not been thoroughly examined. The M:M relationship can be “broken” by any document or document position. Such a class of objects that breaks the M:M connection is called the “entity of intersection.” You just need to see and find this class of objects. For the above example of the M:M relationship, this class of objects is “WORK BOOK RECORD”. If we have identified it, then the connections in the subject area already sound like this: “each STRUCTURAL UNIT OF THE ENTERPRISE can correspond to zero, one or more EMPLOYMENT BOOK ENTRIES.” On the reverse side: “each EMPLOYMENT BOOK ENTRY must relate to one and only one STRUCTURAL UNIT OF THE ENTERPRISE.” And one more connection: “to every INDIVIDUALS,

working at the enterprise may correspond to zero, one or more EMPLOYMENT BOOK ENTRIES.”

3. One-to-one communication (1:1). A rare connection, usually from a business point of view this means that these are not two classes of objects, but one. This connection can have power of one and only one in both directions. If such a connection is discovered, the information flows should be examined again and it may turn out that the two identified classes of objects actually constitute one.

Example of a 1:1 relationship: “each BICYCLE can only be used by one CLUB MEMBER.” On the reverse side: “each CLUB MEMBER can only ride one BICYCLE”

1:1 connections required on both ends, where both objects must appear at the same time, are very rare.

After identifying any relationship between classes of objects, it is necessary (for each side):

Set availability;

Select a name;

Determine power;

Define optionality;

Check by reading.

It should be noted that any number of connections can be identified between two classes of objects. For example, 2 connections can be identified between the object classes “INDIVIDUALS” and “ADDRESS”: one fixing the registration address, the other - the residential address.

4. Recursive communication. Relationship between objects of the same object class. Such a connection can have all the properties inherent in any other connection.

Example: Each NODE can act as a parent to one or more NODES. Each NODE can be subordinate to one and only one NODE.

The results of identifying connections can be summarized using the following table:

Table - Formalized description of the subject area. Relationships between object classes

The following abbreviations are used in the table: KO - object class; D.b. -must be, M.b. - May be.

Identified connections are verified by reading. It must be remembered that every connection is two-way!

Unformalized description of the subject area

During the analysis process, it is also necessary to record business rules or semantic (semantic) statements that limit the subject area within the framework of the problem being solved. These are not the functions of the enterprise, as such, but immutable facts that the developed automated information system must always obey.

Examples of semantic statements:

- “Employees who have reached the age of 16 are hired”;

- “Any employee cannot be responsible for more than ten real estate properties for rent or sale at the same time”;

- “Any employee has no right to sell or lease his own real estate”;

- “Special discounts do not apply to cars less than one year old”;

- « total amount discounts cannot exceed 40% of the net amount shown on the invoice.”

The identified semantic statements are recorded in natural language and must be further reflected in the database. Usually, similar rules are implemented using database objects such as triggers, procedures, views (views).

Download lecture: You do not have access to download files from our server.

Types of relationships between domain objects

Relationships based on multiplicity can be of four types - “one-to-one”, “one-to-many”, “many-to-many”, “many-to-one”.

A one-to-one (1:1) relationship exists when one instance of one object is associated with a single instance of another. The relationship is unique from left to right as well as from right to left.

leads

Enterprise Director

A one-to-many (1:M) relationship exists when one instance of a first object is associated with one (or more) instances of a second object, but each instance of the second object is associated with only one instance of the first. The relationship is unique from right to left.

Comprises

City District

A many-to-many (M:M) relationship exists when one instance of the first object is associated with one or big amount instances of the second and each instance of the second with one or many instances of the first

Student (last name, grade book no. Faculty) Subject (name, number of hours)

A many-to-one relationship (M:1) is similar to a one-to-many relationship. The relationship is unique only from left to right.

Student's last name (M:1) Group number

Conceptual model. A model of objects with attributes describing them and relationships between them is called conceptual model. This model represents objects and their relationships without specifying how they are physically stored.

It is presented graphically in the form of a special diagram proposed by the American database specialist Charles Bachman. In Bachmann diagrams, objects are represented by the vertices of a certain mathematical graph, and connections are represented by arcs of the graph. Let's consider, for example, the Procurement Data Model (see Fig. 48).

Rice. 48 Design example conceptual model

The model consists of three objects: Supplier, Order, Product. The relationship Existing between the objects Supplier and Order has the power of one-to-many, since each order is made to one supplier, but several orders can be made to a given supplier. The relationship between the Order and Product objects has many-to-many power, since an order contains several products and a product can appear in several orders.

One-to-one connections

One-to-one connections occur 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 easily be combined into one, the structure of which is formed by combining the details of both original objects, and any of the alternative keys can be selected as a key attribute, i.e. keys of source objects. Graphic image one - unambiguous connections are group - headman, company - bank account, etc.

Fig. 1 Graphic representation of one-to-one relationships between objects

One-to-multi-valued relationships (1:M)

One-to-multi-valued relationships (1:M)– these are connections where an instance of one object (A) can correspond to several instances of another object (B), and each instance of a second object (B) can correspond to only one instance of the first object (A).

Fig. 2 Graphic representation of one-multi-valued connections between objects.

In such a connection, object A is the main object, and object B is the subordinate, i.e. there is a hierarchical subordination of object B to object A. An example of one-valued relationships is departments - employees, department - teacher, student group, 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 a second object (B), and vice versa, each instance of a second object (B) can also correspond to several instances of the first object (A).

Fig.3 Conversion of M:N type connection through a link object

A link object must have an identifier formed from the identifiers of the original objects Ka and Kb.
An example of multi-valued relationships is the connection between suppliers and goods, if one supplier supplies different names of goods, and a product of the same name is supplied by several suppliers.

Defining relationships between information objects

Let's consider the definition of connections between information objects and the type of relationships by which they are characterized for the subject area Educational process.

Relationships between GROUP - STUDENT objects are characterized by single-valued relationships (1:M), since one group includes many students, and one student is included in only one group. Communication between them is carried out by the group number, which is a 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 DEPARTMENT TEACHER, which are also in one-to-one relationships. The connection between them is carried out using the unique key of the main object DEPARTMENT - the department code, which is descriptive in the subordinate object TEACHER.

Table 1. Objects reference information about students, groups and subjects

Table 2. Grouping of details by document information objects List of teachers of the department

The table uses the following notations for the key: P – simple, U – unique.

In each group, classes are held in different subjects during the semester (object STUDY). On the other hand, each activity is specific for each group. Therefore, there is a one-to-many relationship between objects SUBJECT - STUDY.

There are many classes available for each subject. various groups different teachers. On the other hand, each lesson is conducted on a specific subject, which defines a one-to-many relationship between SUBJECT - STUDY objects. One-to-many relationships between objects are defined similarly TEACHER – STUDY.
The STUDY object actually plays the role of a connective object in multi-valued object relationships.

Fig.4 Multi-valued connections of information objects

Fig.5 Information-logical model of the subject area Educational process

The PROGRESS object contains data on the performance (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 relates to one specific student. This means that the object SUCCESS is subordinate and is in a one-to-one relationship with the object STUDENT. The object SUCCESS is also subordinate and is in a one-to-one relationship with the object STUDY. The object PERFORMANCE performs the role of an object – a link of many-multi-valued relations between the objects STUDENT and STUDY. Many-multi-valued relationships between these objects are determined by the fact that one student corresponds to many classes represented by the STUDY object, and one lesson is carried out with many students.

Table 3 lists all one-valued connections between objects, indicates the keys by which connections should be established, and identifies the main and subordinate information objects in these connections.

Table 3 Information object connections

Information-logical model of the subject area Educational process

The information-logical model is presented in canonical form and the objects in it are arranged by levels. The level of other objects is determined most the long way to the object from the zero level. This placement of objects gives an idea of ​​their hierarchical subordination, makes the model more visual and facilitates the understanding of single-valued relationships between objects.

Logical structure of a relational database

Logical structure of a relational database Access data is an adequate reflection of the resulting information-logical model, which does not require additional transformations. Each data model information object is represented by 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 details. The key details of the object form a unique key of the relational table. For each column, the type, data size, and other properties are specified. Table rows (records) correspond to object instances and are formed when tables are loaded.

Relationships between objects of the data model are implemented by the same details - communication keys in the corresponding tables. In this case, the connection key is always the unique key of the main table. The relationship key in a 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 relationship key in the subtable is called foreign key. Can be created in Access data schema, which clearly displays the logical structure of the database. The definition of single-valued relationships in this scheme must be carried out in accordance with the constructed data model. Appearance data schemes are almost identical to graphical representation information-logical model. For the data model built in the example considered, the logical structure of the database in the form of an Access data diagram is shown in Fig. 2.7.

In this diagram, rectangles display database tables with a complete list of their fields, and relationships show which fields are used to interrelate the tables. Key field names are on the left side full list fields of each table.