Types of entities in the database. Basics of databases

The term "relational" means "based on relationships". The relational database consists of entities (tables) in some respects with each other. The name happened from the English word relation-attitude.
Database design consists of two main phases: logical and physical modeling.
During logical modeling, you collect the requirements and develop a database model that does not depend on the specific DBMS (relational database management system). It looks like, as if you created the drawings of your home. You could think over and draw everything: where there will be a kitchen, bedroom, living room. But it's all on paper and in layouts.
During physical modeling, you create a model optimized for a specific application and DBMS. It is this model that is implemented in practice. If you go back to home from the previous paragraph, at this stage you will have to build anywhere - to carry logs, bricks ...

The design process of the database consists of the following steps:

collection of information;
definition of entities;
definition of attributes for each entity;
determination of links between entities;
normalization;
transformation to the physical model;
database creation.

The first 5 stages form the logical design phase, and the remaining two - the phase of physical modeling.

Logic phase

The logical phase consists of several stages. Next, they are all reviewed.

Collecting requirements

At this stage, you need to determine exactly how the database will be used and what information will be stored in it. Collect as much information as possible that the system should do and what should not.

Definition of entities

At this stage, you need to determine the essences from which the database will be.

Entity is an object in the database in which the data is stored. The essence can be something real (house, man, subject, place) or abstract (banking operation, company department, bus route). In the physical model, the entity is called a table.

Entities consist of attributes (table columns) and records (rows in the table).

Typically, databases consist of several main entities associated with a large number of subordinate entities. The main essences are called independent: they do not depend on any other entity. Subordinate entities are called dependent: in order to exist one of them, there must be a related basic table.
In essence diagrams are usually presented in the form of rectangles. The entity name is indicated inside the rectangle:

Any table has the following characteristics:

it does not have the same rows;
all columns (attributes) in the table must have different names;
elements within one column have the same type (string, number, date);
the order of rows in the table may be arbitrary.

At this stage, you need to identify all categories of information (entities) that will be stored in the database.

Definition of attributes

The attribute represents a property describing the essence. Attributes are often numbered, date or text. All data stored in the attribute must have the same type and have the same properties.
In the physical model, attributes are called speakers.
After determining entities, it is necessary to define all attributes of these entities.
In diagrams, attributes are usually listed inside the entity rectangle. In the figure you will find an example of a "home" database, only now for entities from this database, some attributes are defined.

For each attribute, the data type, their size, valid values \u200b\u200band any other rules are defined. These include the rules of the obligation of filling, variable and uniqueness.
The rule of filling is determined whether the attribute is a mandatory part of the essence. If the attribute is an optional part of the entity, it can take a null-value, otherwise - no.
You must also determine whether the attribute is variable. Some attribute values \u200b\u200bcannot change after creating a record.
And finally, you need to determine whether the attribute is unique. If so, then the attribute values \u200b\u200bcannot be repeated.

Keys

The key (Key) is called the attribute set, uniquely defining the record. Keys are divided into two classes: simple and composite.
A simple key consists only of one attribute. For example, in the database of "passports of citizens of the country", the passport number will be a simple key: after all there are no two passports with the same number.
The composite key consists of several attributes. In the same database of the "passport of citizens of the country", there may be an integral key with the following attributes:
Surname, name, patronymic, date of birth. This is as an example, since this composite key, theoretically, does not ensure the guaranteed uniqueness of the recording.
There are also several types of keys, which are described further.

Possible key

A possible key is any set of attributes, unambiguously identifying the entry in the table. A possible key may be simple or composite.
Each entity must have at least one possible key, although several such keys can be somewhat. None of the primary key attributes can make an indefinite (null) value.
Possible key is also called surrogate.

Primary keys

The primary key is a set of attributes, unambiguously identifying the entry in the table (entity). One of the possible keys becomes the primary key. In diagrams, primary keys are often depicted above the main list of attributes or are highlighted by special characters. Essence in the figure has both key and ordinary attributes.

Alternative keys

Any possible key that is not primary is called an alternative key. Entity can have several alternative keys.

External keys

The external key is the set of attributes that refer to the primary or alternative key of another entity. If the external key is not associated with the primary essence, it may contain only indefinite values. If at the same time the key is composite, then all the external key attributes must be uncertain.
In diagrams, attributes combined into external keys are designated by special characters. The figure shows two connected entities (at home and their owners) and the external keys formed by them (after all, one person can own more than one house).

Keys are logical structures, not physical objects. In relational databases, mechanisms ensure the maintenance of keys.

Determination of ties between entities

Relational databases allow us to combine information belonging to different entities.
The attitude is a situation at which one entity refers to the primary key of the second entity. Like, for example, the entity of the house and the owner on the previous figure.
Relationships are defined in the design process of the base. To do this, analyze the essences and identify logical connections that exist between them.
The type of relationship determines the number of entity entries related to the entry of another entity. Relationships are divided into three main types, which are described further.

One to one

Each first entity record corresponds to only one entry from the second entity. And each record of the second entity corresponds to only one entry from the first entity. For example, there are two entities: people and birth certificates. And one person can have only one birth certificate.

One-to-many

Each first entity record can correspond to several entries from the second entity. However, each record of the second entity corresponds to only one entry from the first entity. For example, there are two entities: ordering and position position. And in one order can be a lot of goods.

Many-co-many

Each first entity record can correspond to several entries from the second entity. However, each record of the second entity can correspond to several entries from the first entity. For example, there are two entities: the author and book. One author can write a lot of books. But the book may have several authors.
According to the criterion, the relationship is made on mandatory and optional.

Mandatory relation means that for each entry from the first essence, there must be associated records in the second entity.
Optional ratio means that the entry in the second essence may not exist to record from the first entity.

Normalization

Normalization is the process of removing redundant data from the database. Each data item should be stored in the database in one and only in one instance. There are five common forms of normalization. As a rule, the database is given to the third normal form.
In the process of normalization, certain actions are performed to remove redundant data. Normalization increases speed, accelerates sorting and constructing an index, reduces the number of indices into essence, speeds up operations inserts and updates.
The normalized database is usually of greater flexibility. When modifying queries or stored data into a normalized base, it is usually necessary to make less changes, and the changes has fewer consequences.

First normal form

To convert the essence in the first normal form, you should exclude repeated groups of values \u200b\u200band ensure that each attribute contains only one value, the lists of values \u200b\u200bare not allowed.
In other words, each attribute in essence should be stored only in one instance.
For example, in the figure, the entity of the house is not normalized. It contains several attributes for storing data on home owners (the entity of the house does not correspond to the first normal form).

To bring the entity of the house in the first normal form, you must delete repeating groups of values, i.e., remove the attributes of the owner 1-3 by placing them in a separate entity. The result (the essence of the house given to the first normal form):

Second normal form

The table in the second normal form contains only those data that relate to it. The values \u200b\u200bare not key attributes of the entity depend on the primary key. If more accurately, the attributes depend on the primary key, from the entire primary key and only from the primary key.
To match the second normal form of entity should be in the first normal form.
For example, the entity of the house in the picture is the attribute price of liter gasoline, which has nothing to do with the houses. This attribute is deleted (or you can transfer it to another entity). And also we transfer the mayor's attribute into a separate entity - this attribute depends on the city where the house is located, and not from home.
The figure shows the essence of the house in the second normal form (the essence of the house shown to the second normal form).

Third normal form

In the third normal form, attributes that do not depend on the entire key are excluded. Any essence in the third normal form is also in the second. This is the most common database form.
In the third normal form, each attribute depends on the key, from the entire key and from anything other than the key.
For example, the entity of the house owner in the picture is the attribute of the zodiac sign, which depends on the date of birth of the home owner, and not on his behalf (which is the key).
To bring the essence of the owner of the house, it is necessary to create the essence of the signs of the zodiac and transfer the zodiac sign there (the entity of the house owner, given to the third normal form):

Restrictions

Restrictions (Constrains) - These are the rules for whose compliance with the database management system. Restrictions define a plurality of values \u200b\u200bthat can be entered into a column or columns.
For example, you do not want the order amount in your very cool store would be less than 500 rubles. You simply install the column limit amount of the order.

Stored procedures

Stored Proceedures are pre-compiled procedures stored in the database. Stored procedures can be used to define business rules, with their help you can carry out more complex calculations than using only restrictions.
Stored procedures may contain the program running logic, as well as database requests. They can take parameters and return results in the form of tables or single values.
Stored procedures are similar to normal procedures or functions in any program.

NOTE
Stored procedures are in the database and are executed on the database server. As a rule, they are faster than SQL statements, because they are stored in compiled form.

Data integrity

By organizing data to the table and determining the relationship between them, we can assume that the model was created correctly reflecting the business environment. Now you need to ensure that the data entered into the database gives the correct idea of \u200b\u200bthe state of the case. In other words, it is necessary to ensure the execution of business rules and support for integrity (Integrity) databases.
For example, your company is delivered by books. You are hardly accepting an order from an unknown client, because then you will not even be able to deliver the order. Hence the business rule: orders are accepted only from customers, information about which is in the database.
The correctness of the data in relational bases is provided by a set of rules. Data integrity rules are divided into four categories.

Integrity of entities - Each entity entry must have a unique identifier and contain data. After all, you need to somehow distinguish all these records in the database.
Interibute integrity - Each attribute takes only valid values. For example, the purchase amount, definitely, cannot be less than zero.
Refine integrity - A set of rules providing logical consistency of primary and external keys when inserting, updating and removing records. Reference integrity ensures that the corresponding primary key existed for each external key. Take the previous example with the entities of the owner of the house and the house. Suppose you are Vasya Ivanov and own the house. You changed the surname to the sidors and made the appropriate changes in the essence of the owner of the house. Definitely you would like your home to continue to be listed for you under your new name, and did not belong to anyone you, Ivanov, who does not exist.
Custom integrity rules - Any integrity rules are non-relaxed to any of the listed categories.

Triggers.

Trigger - This is an analogue of the stored procedure that is called automatically when the data is changed in the table.
Triggers are a powerful mechanism for maintaining the integrity of the database. Triggers are called before or after changing the data in the table.
With the help of triggers, you can not only cancel these changes, but also change the data in any other table.
For example, you create an online forum, and you need to make the latest forum post on the list of forums. Of course, you can take a message from the entity of the forum message, but this will increase the complexity of your request and its execution time. It is easier to add a trigger to the entity of the message of the forum, which would write down the last added message in the essence of the forum, in the attribute last message. This will greatly simplify the request.

Business Rules

Business rules determine the limitations imposed on the data in accordance with the requirements of the business (those for whom you create the base). Business rules may consist of a set of steps required to perform a specific task, or they can simply be verified that control the correctness of the data entered. Business rules may include data integrity rules. Unlike other rules, their main goal is to ensure proper management of business operations.
For example, in the company "Very cool guys" can be so accepted that only white, blue and black cars are purchased for service needs.
Then the business rule for the attribute color of the car entity. Office cars will be stupid that the car can only be white, blue or black.
Most DBMS provide funds:

to specify default values;
to verify the data before entering them into the database;
to maintain connections between tables;
to ensure the uniqueness of the values;
to store stored procedures directly in the database.

All these features can be used to implement business rules in the database.

Physical model

The next step, after creating a logical model, is to build a physical model. The physical model is the practical implementation of the database. The physical model defines all the objects that you have to implement.
When switching from a logical model to physical entity, they are converted to tables, and attributes in columns.
Relationships between entities can be converted to tables or leave as external keys.
Primary keys are converted to primary keys restrictions. Possible keys - in restriction of uniqueness.

Denormalization

Denormalization - This is an intentional change in the structure of the base that violates the rules of normal forms. This is usually done in order to improve the performance of the database.
Theoretically, it is always necessary to strive for a fully normalized base, however, in practice, the full normalization of the base almost always means a drop in productivity. Excessive database normalization may result in each data extraction will have to access multiple tables. Typically, four tables should be involved in the request or less.
Standard methods of denormalization are: Combining multiple tables in one, saving the same attributes in several tables, as well as storage in the table of consolidated or calculated data.

The design process of the database consists of the following steps:

collection of information;
definition of entities;
definition of attributes for each entity;
determination of links between entities;
normalization;
transformation to the physical model;
database creation.

The first 5 stages form the logical design phase, and the remaining two - the phase of physical modeling.

Logic phase

The logical phase consists of several stages. Next, they are all reviewed.

Collecting requirements

Definition of entities

At this stage, you need to determine the essences from which the database will be.

Entities consist of attributes (table columns) and records (rows in the table).

Any table has the following characteristics:

it does not have the same rows;
all columns (attributes) in the table must have different names;
elements within one column have the same type (string, number, date);
the order of rows in the table may be arbitrary.

At this stage, you need to identify all categories of information (entities) that will be stored in the database.

Definition of attributes

Keys

Possible key

Primary keys

Alternative keys

Any possible key that is not primary is called an alternative key. Entity can have several alternative keys.

External keys

Keys are logical structures, not physical objects. In relational databases, mechanisms ensure the maintenance of keys.

Determination of ties between entities

One to one

One-to-many

Many-co-many

Mandatory relation means that for each entry from the first essence, there must be associated records in the second entity.
Optional ratio means that the entry in the second essence may not exist to record from the first entity.

Normalization

First normal form

Second normal form

Third normal form

Restrictions

Stored procedures

NOTE
Stored procedures are in the database and are executed on the database server. As a rule, they are faster than SQL statements, because they are stored in compiled form.

Data integrity

Integrity of entities - Each entity entry must have a unique identifier and contain data. After all, you need to somehow distinguish all these records in the database.
Interibute integrity - Each attribute takes only valid values. For example, the purchase amount, definitely, cannot be less than zero.
Refine integrity - A set of rules providing logical consistency of primary and external keys when inserting, updating and removing records. Reference integrity ensures that the corresponding primary key existed for each external key. Take the previous example with the entities of the owner of the house and the house. Suppose you are Vasya Ivanov and own the house. You changed the surname to the sidors and made the appropriate changes in the essence of the owner of the house. Definitely you would like your home to continue to be listed for you under your new name, and did not belong to anyone you, Ivanov, who does not exist.
Custom integrity rules - Any integrity rules are non-relaxed to any of the listed categories.

Triggers.

Business Rules

to specify default values;
to verify the data before entering them into the database;
to maintain connections between tables;
to ensure the uniqueness of the values;
to store stored procedures directly in the database.

All these features can be used to implement business rules in the database.

Physical model

Denormalization

Entity is a real or abstract object that is essential for the subject area. Entity should have the name expressed by nouns in the singular

An informal way to identify entities is the search for abstractions describing objects, processes, roles and other concepts. Formal way to identify entities - Analysis of textual descriptions of the subject area, highlighting nouns and choosing them as abstractions.

A copy of the entity is a specific representative of this entity. For example, an employee of Ivanov can be an instance of the entity.

Each entity must have the following properties:

have a unique name;

have one or more attributes that either belong to the entity or inherited through the connection;

have one or more attributes that definitely identify every instance of the entity.

Attribute attribute is a characteristic of the entity that is significant for the subject area under consideration and intended for identification, classification, quantitative characteristic or expression of the condition of the essence.

There are the following types of attributes:

simple - consists of one data element;

composite - consists of several data elements;

unambiguous - contains one value for one entity;

multivalued - contains several values \u200b\u200bfor one entity;

optional - may have an empty (indefinite) value;

the derivative is a value derived from the value of another attribute.

A unique identifier is a set of attributes whose values \u200b\u200bare unique for each entity instance. Deleting any attribute from the identifier disrupts its uniqueness. Unique identifiers are depicted in the underscore chart

Each entity can have any number of connections with other entities.

Communication between entities

Communication (Relationship) - named association between entities, meaningful for the subject area under consideration.

The degree of communication is called the number of entities involved in connection.

Communication power is the number of copies of the entity involved in connection.

Depending on the power value, the connection may have one of three types:

one-to-one (denoted 1: 1).

one-to-many (denotes 1: n).

many-to-many (denoted M: N).

One to one. This means that in such a connection of the entity with one role always corresponds to no more than one entity with another role. Since the degree of communication for each essence is 1, then they are connected by one line.

One-to-many.Essence with one role can correspond to any number of entities with another role.

Many-to-many. In this case, each of the associated entities can be represented by any number of copies.

A couple of years ago, among other things, there were online lessons on the basics of building a logical structure of the database and SQL language. I'm not doing lessons at the moment, but the records themselves remained, so I decided to post them, why did you disappear in vain? 🙂

Today we will talk about the "Essence-Communication" model, or Entity-Relationship Model.

Theory

The "Entity-Relationship Model or ER model" model is a high-level conceptual data model that has been developed to simplify the design task of the database structures.

This model is a set of concepts that describe the structure of the database in the form of a set of entities, attributes and connections. The main purpose of developing such a data model is to create a user perception of data and coordinating a large number of technical aspects related to the design of the database. It should be especially noted that the conceptual data model does not depend on a specific DBMS or hardware platform, which is used to implement the database.

The goal of the Diagram "Essence-Communication" is to create an accurate and complete display of the real object area (pro) used in the future as a source of information to build a database of automated information processing systems (databases).

This diagram or conceptual model about must meet the following requirements:

Provide an adequate display of pro;
Submit in the language that is understandable to both future users and database developers;
Contain information about the pro, sufficient for further design of the database (development of logical and physical models);
Guarantee unambiguous interpretation or interpretation of the model.

The main concepts of this model are concepts essence, attribute and communications.

Essence- These are a lot of real world objects with the same properties. The essence is characterized by an independent existence and may be an object with a physical (or real) existence or an object with a conceptual (or abstract) existence.

Entity is the main content of the phenomenon or process (transaction or request), which needs to collect information, and is a nodal point of information collection. Essence refers to a set of homogeneous objects or things. Each entity is identified by the name and list of properties (attributes). As an essence, a person can act, a place, a thing, etc., information about which must be stored in the database.

Practice

EXAMPLE. Subject area " Cinema ticket order". The cinema show movies, tickets for which you can buy on the day of the show or book them in advance. The database contains information about all filmmakers in this cinema, including old. Each film transmission has its own cost, i.e. Tickets for the same film, but at different times, may differ in price. Filmcase consists of a movie, information about which is also stored in the database.

For about " Cinema ticket order"Entities will be the following concepts:

Kinopocaz

Films

Viewer

Ticket

Book

Cost

Graphically essence in the "Essence-Communication" diagrams are presented in the form of rectangles:

ATTRIBUTE— This means by which the properties of the essence or communication are determined. The attribute is the named characteristic of the essence. The name of the attribute should be unique for a particular entity, but may be the same for different entities.

A specific set of attributes for the entity is determined by the tasks in which they are used. For example, the essence of the "Order of Tickets in the Cinema" can be described using the following set of attributes:

Kinopocaz (Film number, movie number, display date, cost number);

Film (Movie number, name, duration, brief description);

Viewer (Viewer number, full name, date of birth);

Ticket (Viewer number, vector number, ticket price);

Book (Viewer number, vector number, booking date);

Cost (Cost number, film number, cost).

Graphically, the image attribute attributes are presented in the form of callouts in which the list of attribute names is listed. For example:

In bold italics and underlining the primary keys are the attribute of the essence, which is uniquely characterizing it. Underline designate external keys - attributes, uniquely characterizing the entities to which they refer.

Communication- This is the relationship between copies of two (or more) different entities. The mechanism of connections is used to determine the relationship between entities in the pro. In addition, there are relations between the attributes of a separate entity (will be considered when building logical models).

Each connection is assigned a name that should describe its function. Communications have such characteristics as the name of communication, the figure of the cardinality, the degree of participation, the degree of communication, the existence of communication and others.

The name of communication should carry a certain meaning in order to be easier to figure out the entity relate. For example, the relationship between the entities of the viewer and the ticket can be defined as "buys."

For graphic presentation of communication in the "Essence-Communications" diagrams, a rhombus is used. Inside the rhombus, the name of the communication is determined, and the entities involved in this connection are connected with the lines.

An indicator of communication cardinality (characteristic of unambiguity) denotes the degree of interconnection of entities and describes the number of possible links for each of the participating entities:

one-to-one (1: 1);
one-to-many (1: n);
many-to-many (n: m).

The creation of the database begins with the design.

DB Design Stages:

· Study of the subject area;

· Data analysis (entities and their attributes);

· Determination of relations between entities and the definition of primary and secondary (external) keys.

In the design process, the structure of the relational database is determined (the composition of the tables, their structure and logical connections). The structure of the table is determined by the composition of the columns, the data type and size of the columns, the key keys.

To basic concepts bD Models "Essence - Communication" These include: entities, links between them and their attributes (properties).

Essence - Any specific or abstract object in the subject area under consideration. Entities are the basic types of information that is stored in the database (a table is assigned in the relational database of each entity). Entities can relate to entities: students, clients, divisions, etc. Instance of entityand the type of entity is different concepts. The concept of the nature of the entity belongs to the set of homogeneous personalities, objects or events speaking as an entire (for example, a student, client, etc.). A copy of the entity belongs, for example, to a specific personality in the set. The type of entity can be a student, and the copier - Petrov, Sidorov, etc.

Attribute - This is the property of the essence in the subject area. Its name must be unique for a particular type of entity. For example, for the entity, the student can use the following attributes: surname, name, patronymic, date and place of birth, passport details, etc. In the relational database, attributes are stored in the fields of tables.

Communication - the relationship between entities in the subject area. Communications are connections between the database parts (in the relational database is a connection between the records of the tables).

Entities are the data that is classified by type, and the contacts show how these types of data correlate one with another. If you describe some subject area in terms of entity - communication, then we get model Essence - Communication For this database.

Consider the subject area: Deanat (student performance)
The database "Deanat" should be kept data on students, groups of students, on the estimates of students on various disciplines, about teachers, about scholarships, etc. We restrict ourselves to the students about students, students of students and the estimates of students on various disciplines. We define the essences, attributes of entities and basic requirements for database features with limited data.

The main subject-significant entities of the database "Deanat" are: Students, groups of students, disciplines, academic performance.

Main objective and significant attributes of entities:
- Last Name, Name, Patronymic, Paul, Date and Place of Birth, Group of Students;
-group students - name, course, semester;
- Discipline - name, number of hours
- performance - evaluation, type of control.

Basic requirements for database functions:
-Ship the student's performance on disciplines with an indication of the total number of hours and the type of control;
-Ship the performance of students in groups and disciplines;
-Suit the disciplines studied by a group of students at a certain course or
a certain semester.

From the analysis of these subject area, it follows that each entity must be assigned the simplest two-dimensional table (relationship). Next, you need to set logical connections between the tables. Between tables, students and academic performance need to establish such communication so that each record from the tables from the table corresponds to several entries in the testability table, i.e. One - to many, since each student may have several estimates.

The logical connection between the entities of the Group - the students are defined as one - to many on the fact that there are many students in the group, and each student is part of the same group. The logical connection between the entities of the discipline - the performance is defined as one - to many, because for each discipline several estimates of various students can be delivered.

On the basis of the foregoing, the Entity Model is a connection for the database "Deanat"

The arrow is a symbol of communication: one - to many.

To create a database, you must apply one of the known DBMS, such as Access DBMS.

Also on the topic

End of Master and Margarita Summary

Biryuchi ordinary (Ligustrum Vulgare L

Proverbs about food in Spanish

From Volga to Yenisei: Barometer of living standards in the regions of the Russian Federation Rating of Russian regions in terms of living

How to get a loan for business development?