Useful Tips

Designing the database structure using the entity-relationship method


The information model of the subject area is a description of the subject area, performed without orientation to the software and hardware used in the future. Contains background information about the subject area. The step of creating an infological model is called infological design.

The purpose of infological modeling is to create an accurate and complete display of the real world, used in the future as a source of information for building a database.

The complex of tasks of this stage is to identify common information objects and the relationships between them. The results of the infological design can be expressed as an infological or conceptual model that represents the data structure. To build a conceptual model, the “Entity - Communication” modeling method or ER diagram is used.

When developing a standard organization chart, the following personnel were determined, which included: directors, administrators, sales consultants, cleaning staff, and drivers. When organizing the work of the store, an important factor is the mobile, skilled work of employees who are able to organize the customer service process as quickly and efficiently as possible.

The work of a sales assistant is a process that can be divided into the following stages:

  • search for the desired product,
  • formation of a list of goods,
  • Add customer information

The information processes of the stages are presented in the form of a table (Table 1.).

Table 1. Information processes stages

1. search for the desired product

- search for goods in the warehouse by letter-by-letter input of the name of the goods, manufacturer or price in the search field,

2. the formation of the list of goods

- the conclusion of the selected goods in a separate table,

3. paperwork of the client

- saving information to the database,

4. clearance of sale

- the choice of the quantity of goods sold,

After studying the subject area and analyzing the structure of the system, objects were identified. The list of entities and relationships are presented in tables 2 and 3.

Table 2. The list of entities of the subject area

Table 3. The list of relationships between entities

Suppliers SUPPLY Products

Products CONTAIN Types

The goods are LOCATED Shop

Shop WORK Employees

Employees place orders

Orders MAKE Customers

Based on the available data, it becomes possible to build the ER-diagram necessary for the further design of the information system (Fig. 1).

Figure 1. ER diagram.

The next design step is to create the logical structure of a relational database. Each data model information object is displayed with a corresponding relational table. The structure of the relational table is determined by the required composition of the corresponding information object, where each column (field) corresponds to one of the details of the object. The key details of the object form a unique relational table key. For each column, you specify the format and size of the data. Rows (records) of the table correspond to instances of the object and are generated when the table is loaded.

The connections between the objects of the data model are implemented by the same details - communication keys in the corresponding tables. The join key is always the unique key of the main table. A key in a subordinate table is either part of a unique key in it, or a field that is not part of the primary key. A communication key in a subordinate table is called a foreign key. In Access, you can create a data schema that visually represents the logical structure of the database. The definition of one-to-many relationships in this scheme should be consistent with the constructed data model. The appearance of the data scheme almost coincides with the graphical representation of the information-logical model. Tables 4 and 5 show the structures of the Products and Employees objects. Similarly, you can get other database tables.

Er Chart Concepts

Essence is a class of objects of the same type, information about which should be taken into account in the model.

Each entity must have a name expressed by a singular noun.

Examples of entities can be such classes of objects as "Supplier", "Employee", "Invoice".

Each entity in the model is depicted as a rectangle with the name:

Figure 1.1 - Image of the entity

Entity instance- This is a specific representative of this entity.

For example, the representative of the entity "Employee" may be "Employee Ivanov."

Entity instances must bedistinguishable, i.e. Entities must have some properties that are unique to each instance of this entity.

Entity attributeis a named characteristic, which is some property of the entity.

The name of the attribute should be expressed in the singular (possibly with characterizing adjectives).

Examples of attributes of the entity "Employee" can be such attributes as "Personnel number", "Last name", "First name", "Middle name", "Position", "Salary", etc.

Attributes are displayed within the rectangle defining the entity:

Riceparty 1.2 - Entity Attributes

Entity key- this isredundanta set of attributes whose values ​​are collectivelyuniquefor each entity instance. The redundancy lies in the fact that the removal of any attribute from the key violates its uniqueness.

An entity can have several different keys.

Key attributes are depicted in the diagram by underlining:

Figure 1.3 - Entity Key

Communicationis some association betweentwoentities. One entity can be associated with another entity or with itself.

Relationships allow one entity to find other entities associated with it.

For example, relations between entities can be expressed by the following phrases - “EMPLOYEE may have several CHILDREN”, “each EMPLOYEE must be listed in exactly one DEPARTMENT”.

Graphically, the relationship is depicted by a line connecting two entities:

Figure 1.4 - Image of links

Each connection has two ends and one or two names. The name is usually expressed in an indefinite verb form: "have", "belong", etc. Each of the items refers to its end of connection. Sometimes names are not written because of their obviousness.

Each link can have one of the followingcommunication types:

Figure 1.5 - Types of relationships

Relationship typeone to onemeans that one instance of the first entity (left) is associated with one instance of the second entity (right). The one-to-one relationship most often indicates that in fact we have only one entity, incorrectly divided into two.

Relationship typeone to manymeans that one instance of the first entity (left) is associated with several instances of the second entity (right). This is the most commonly used type of connection. The left entity (from the "one" side) is calledparent, right (from the "many" side) -subsidiary. A typical example of such a connection is shown in Fig. four.

Relationship typemany-to-manymeans that each instance of the first entity can be associated with several instances of the second entity, and each instance of the second entity can be associated with several instances of the first entity. The many-to-many relationship type istemporarytype of communication acceptable in the early stages of model development. In the future, this type of connection should be replaced by two one-to-many relationships by creating an intermediate entity.

Each link can have one of twocommunication modalities:

Figure 1.6 - Communication modality

Modality "can"means an instance of one entitymay be connectedwith one or more instances of another entity,or maybe not connectedwith not a single copy.

Modality "should"means an instance of one entitymust be associated with at least onean instance of another entity.

Communication may havedifferent modalityfrom different ends (as in Fig. 1.4).

The described graphic syntax allowsdefinitelyread diagrams using the following phrase construction scheme:

Each link can be read from left to right, and from right to left. The connection in Fig. 1.4 reads like this:

From left to right: "each employee can have several children."

From left to right: "Every child must belong to exactly one employee."

Download MySQL Workbench

The MySQL Workbench distribution is available on this page. The latest version of the program at the time of writing is Version 6.1. Before downloading, you must select one of the following platforms:

  • Microsoft Windows (MSI Installer and ZIP archive available)
  • Ubuntu linux
  • Fedora
  • Red Hat Enterprise Linux / Oracle Linux
  • Mac OS X

After choosing a platform, you are prompted to register or log in to Oracle. If you do not want, there is a link below "No thanks, just start my download" - click on it)

Beginning of work

The start screen of the program reflects the main directions of its functionality - the design of database models and their administration:

At the top of the screen is a list of connections to the MySQL servers of your projects, and a list of the latest open data models is at the bottom of the screen. Work usually starts with create data schema or loading an existing structure into MySQL Workbench. Let's get to work!

Creating and editing a data model

To add a model, click the plus sign next to the "Models" heading or select "File → New Model" (Ctrl + N):

On this screen, enter the database name, select the default encoding and, if necessary, fill out the comment field. You can start creating tables.

Adding and editing a table

The list of project databases and the list of tables within the database will be located in the tab "Physical Schemas". To create a table, double-click on "+ Add Table":

A convenient interface opens for editing the list of fields and their properties. Here we can set the field name, data type, as well as set various attributes for the fields: assign a field primary key (PK)tag it Not Null (NN), binary (BIN), unique (UQ) and others set for the field auto increment (AI) and default value.

Index Management

You can add, delete and edit table indices in the tab "Indexes" table management interface:

We enter the name of the index, select its type, then tick off the list of fields participating in this index in the necessary order. The order of the fields will correspond to the order in which the boxes were checked. In this example, I added a unique index to the field username.

Relations between tables

Setting foreign keys and linking tables is only possible for tables Innodb (this storage system is selected by default). To manage the relationships in each table there is a tab "Foreign Keys":

To add a link, open the tab "Foreign Keys"child table, enter the name of the foreign key and select parent table. Further in the middle of the tab in the graph Column select the key field from the child table, and in the column Referenced Column - the corresponding field from the parent table (the field type must match). When creating foreign keys corresponding indexes are automatically created in the child table.

In section "Foreign Key Options" configure the behavior of the foreign key when changing the corresponding field (ON UPDATE) and removing (ON DELETE) parent record:

  • RESTRICT - give an error when changing / deleting the parent record
  • Cascade - update the foreign key when the parent record is changed, delete the child record when the parent is deleted
  • SET NULL - set the value of the foreign key Null when changing / deleting a parent (unacceptable for fields that have the flag set NOT NULL!)
  • NO ACTION - do nothing, but in fact the effect is similar to RESTRICT

In the above example, I added to the child table Userprofile foreign key to link to the parent table User. When editing a field userId and deleting items from the table User similar changes will be automatically occur with related records from the table Userprofile.

Filling the table with basic data

When creating a project, you often need to add start data to the database. These can be root categories, admin users, etc. There is a tab for managing MySQL Workbench tables for this "Inserts":

As you can see from the example, if you need to apply some MySQL function to the data before writing to the database, this is done using the syntax func functionName ('data'), eg, func md5 ('password').

After entering the data, you need to save them to the local database by clicking on the button "Apply Changes".

Creating an EER chart (entity-relationship diagrams)

To represent the data schema, entities and their relationships in graphical form, MySQL Workbench has an EER diagram editor. To create a chart at the top of the database management screen, double-click on the icon "+ Add Diagram":

In its interface, you can create and edit tables, add relationships of various types between them. To add a table that already exists in the diagram to the diagram, simply drag it from the panel "Catalog Tree".

To export the data schema to a graphic file, select "File → Export"and then one of the options (PNG, SVG, PDF, PostScript File).

Import an existing data schema (from an SQL dump)

If we already have a data scheme, it can be easily imported into MySQL Workbench for further work. To import the model from the SQL file, select "File → Import → Reverse Engineer MySQL Create Script.", then select the desired SQL file and click "Execute>"

MySQL Workbench also provides import and synchronization of a data model directly with a remote server. To do this, you need to create connecting remote access to MySQL, which I will discuss in the continuation of this review.

The demo project from the article is available for download at this link. I wish you success and beautiful cockroach schemes!