The concept of ER-model. The concept of entity. Attributes. Attribute types

The concept of ER-model. The concept of entity. Attributes. Attribute types


Contents


1. What problems can a developer have when designing a database? 

When designing a database and developing a software product, the most important problem is the problem of interaction between the developer and the customer. The task of the developer is to most accurately recreate the wishes of the customer when developing a database management software product. The main problem that the developer needs to solve is the correct construction of the database, or rather the schema (structure) of the database.

In addition, the developer additionally meets with other difficulties, which include:

  • search for efficient algorithms;
  • selection of appropriate data structures;
  • debugging and testing complex code;
  • design and convenience of the application interface.

In the process of developing database management software, the developer must learn the requirements of the customer in detail. The database should be designed in such a way that it is understandable, most accurately reflects the problem being solved and does not contain redundancy in the data.

To facilitate the process of developing (designing) a database, the so-called semantic data models are used. For different types of databases, the ER-model is the most well-known data model (Entity-Relationship model).

2. What is the ER-model (Entity-relationship model)? What is the need to develop an ER-model?

An ER model (Entity-relationship model or Entity-relationship diagram) is a semantic data model that is designed to simplify the database design process. From the ER-model can be generated all kinds of databases: relational, hierarchical, network, object. The basis of the ER-model are the concepts of “essence”, “relationship” and “attribute”.

For large databases, the development of an ER model makes it possible to avoid design errors that are extremely difficult to correct, especially if the database is already in use or at the testing stage. Errors in the database structure can lead to alteration of the code of the software managing this database. As a result, time, funds and human resources will be used inefficiently.

ER-model – a representation of the database in the form of intuitive graphical charts. The ER model visualizes a process that defines a certain subject area. An “entity” – “relationship” diagram is a diagram that graphically represents entities, attributes, and relationships.

The ER model is only a conceptual level of modeling. ER-model does not contain implementation details. For the same ER-model, details of its implementation may differ.

3. What is an entity in the database? Examples

An entity in a database is any object in a database that can be distinguished based on the essence of the subject area for which this database is being developed. The database developer must be able to correctly identify entities.

Example 1. The following entities can be distinguished in the bookstore database:

  • book;
  • provider;
  • placement in the bookstore.

Example 2. The following entities can be distinguished in the educational process accounting database of a certain educational institution:

  • students;
  • teachers;
  • groups;
  • disciplines that are being studied.


4. What are the different kinds of entity types? Identifying Entity Types in the ER Model

In the model “entity” – “relationship” there are two kinds of entity types:

  • weak type. This type of entity is dependent on a strong entity;
  • strong type. This is an independent type of entity that does not depend on anyone. Figure 1 shows the notation for a weak and strong entity type in the ER-model.

ER-model entity types Figure

Figure 1. Designation of strong and weak entity types

5. What are attributes for? Types of attributes. The designation of attributes on the ER-model

Each entity type has a specific set of attributes. Attributes are intended to describe a particular entity.

There are the following types of attributes:

  • simple attributes. These are attributes that can be part of composite attributes. These attributes consist of one component. For example, simple attributes include: a book code in a library or a student’s course of study at an educational institution;
  • compound attributes. These are attributes that consist of several simple attributes. For example, the residential address may contain the name of the country, town, street, house number;
  • single-valued attributes. These are attributes that contain only one single value for an entity. For example, the attribute “Number of the record book” for the entity type “Student” is single-valued, since a student can have only one number of the book (one value);
  • multi-valued attributes. These are attributes that can contain multiple values. For example, the multi-valued attribute “Phone number” for the entity “Student”, since a student can have several phone numbers (home, mobile, etc.);
  • arbitrary attributes. These are attributes whose value is based on the values of other attributes. For example, a student’s current course of study can be calculated on the basis of the difference between the current year of study and the year the student entered the school (if the student had no problems with study and he well studied the discipline “Organization of databases and knowledges”).

In the ER diagram, attributes are denoted as shown in Figure 2. As can be seen from the figure, any attribute is denoted as an ellipse with the name inside the ellipse. If the attribute is a primary key, then its name is underlined.

ER-model attributes photo

Figure 2. Representation of attributes on ER-model diagrams

6. How are the entity types and attributes of the ER model implemented in real databases and the programs they manage?

When developing database management programs, entity types and their attributes can be represented in different ways while adhering to several approaches:

  • choose a known technology as a data source (for example, Microsoft SQL Server, Oracle Database, Microsoft Access, Microsoft ODBC Data Source, etc.), which has already been researched, tested, standardized and has a power set of database management tools;
  • develop your own database format and implement its processing methods, and implement interaction with known data sources in the form of special commands like Import/Export. In this case, you will have to personally program all the routine work of maintaining and ensuring reliable operation of the database;
  • realize the combination of the two approaches above. Modern software development tools have a powerful set of libraries for processing complex sets and visualizing data in them (collections, arrays, visualization components, etc.).

If the database is implemented in known relational DBMS (for example, Microsoft Access, Microsoft SQL Server, etc.), then the types of the entities are represented by tables. Attributes from the ER model correspond to the fields in the table. One record in the database table represents one instance of the entity.

Each attribute type is implemented as follows:

  • a simple attribute or a single-valued attribute can be represented by an accessible set of basic types that exist in any programming language. For example, integer attributes are represented by the type int, integer, uint, etc.; attributes containing a fractional part can be represented by the type float, double; string attributes of type string, etc.;
  • a composite attribute is an object that includes several nested simple attributes. For example, in Microsoft Access DBMS, a composite attribute of a certain table can be formed based on a set of simple types (fields). In programming languages, the union of fields is implemented by structures or classes;
  • a multi-valued attribute can be implemented by an array or a collection of simple or compound attributes;
  • an arbitrary attribute is implemented by an additional field, which is calculated when accessing the table. Such a field is called a calculated field and is formed on the basis of other fields in the table;
  • an attribute that is a primary key can be an integer, string, or other ordinal type. In this case, the value of each table cell that corresponds to the primary key is unique. Most often, the integer type (int, integer) appears as the primary key.

If the database is implemented in a unique format, then the types of entities must be represented as classes or structures. Entity attributes are implemented as fields (internal data) of a class. Class methods implement the necessary processing of class fields (attributes). The interaction between classes is implemented using specially designed interfaces using well-known design patterns.

7. An example of a fragment of the ER-model for the type of entity “Student”

This example demonstrates a fragment of the ER-model for the type of entity “Student”.

ER-model entity Figure

Figure 3. Fragment of the ER-model for the type of entity “Student”

In the figure above, the following attributes are declared, which in the DBMS (program) can be of the following types:

  • attribute “Primary key” – is a unique integer value that is generated automatically. In DBMS, this is a counter field;
  • attribute “Year entry” is a simple attribute that can be implemented with an integer value (int, integer);
  • attribute “Phone number” is a multi-valued attribute that can be implemented as an array or collection, etc.;
  • attribute “Gradebook number” is a simple attribute that can be implemented as a string of characters, since the gradebook number can contain letters in addition to numbers;
  • attributes Country, City, Street, House Number – these are the attributes that form the composite attribute Address. All these attributes can be a string (text);
  • attributes Name and Surname are simple attributes that are part of the compound attribute of the Student Name. All these attributes can be of string type;
  • attribute Birthday is a simple attribute of type Date (DateTime);
  • attribute “Age of student” is a calculated field, which is defined as the difference between the current (system) date and the value of the Birthday attribute.


Related topics