Building a relational structure from the ER model

To the articles Development → Relationships of the Database Tables and Development → Designing the Database of a Ubeasley habravchanin I would like to make a small addition.

I want to describe the rules by which you can build a relational database schema. These rules are probably very few people need, because they are used by developers on an intuitive level, but they are interesting even by the fact that they formalize the process of building a database schema.

These rules apply to the ER-model, that is, the model "entity-relationship".

ER model

')
ER- model is a scheme, the constituent elements of which are:

• The entity is a real or imaginary object, information about which must be stored in a database. In an ER model diagram, an entity is depicted as a rectangle containing the name of the entity.
• Relationship - an association displayed graphically in a diagram between two (most often) entities, or between the same entity (recursive relationship). The link is depicted as a rhombus on which two ends stand out, one for each entity. For each side of this relationship are established:
  1. Degree of communication - how many instances of this entity is associated
  2. Obligation of communication - whether the entity must necessarily participate in communication.

I will give an example:
Let it be necessary to store information about customers and their orders. Build a chart


Pic.1

Note that on the side of the entity “ORDER” the link is indicated by an additional rectangle - this is a designation that each instance of the entity “ORDER” corresponds to an instance of the entity “CUSTOMER” (for the client, the presence of the order is not necessary). The degree “M” means that for each instance of the “CUSTOMER” entity there can be several instances of the “ORDER” entity (but not vice versa, since for each order there is always only one customer - we put the degree “1”)

A relationship (usually it corresponds to a table in a database) should not be confused with an entity. The entity goes into the relationship by selecting it from the ER-diagram.

Design Stages

1. Conceptual design

   
   An ER diagram is constructed, which includes all entities and relationships. We get a conceptual (infological) model. It should be understood that such a model may far not correspond to the relational structure of the projected database.
   
   Suppose you need to build a database in which you will need to store complete information about orders, customers, employees. For each order there is a list of elements of this order (several items), each of which is associated with a list of consumed materials and operations.
   
   I got the following diagram.
   
   
   Fig. 2
   
   

2. Logical design

   
   A set of preliminary relationships is built with the primary key for each relationship. A list of attributes is compiled, then these attributes are distributed by relationship. It is necessary that all relationships remain in the NFBK.
   
   The transition to a relational structure (building a set of relationships) is performed according to the following rules:
   
   
   1. If the degree of binary relationship is 1: 1 and the class of ownership of both entities is required, then only one relationship is required. The primary key of this relationship can be the key of any of these two entities. In this case, a single occurrence of each key value is guaranteed in any instance of the relation.
   2. If the degree of a binary connection is 1: 1 and the class of one of the entities is optional, then the construction of two relations is necessary, for each entity it is necessary to single out one relationship. The key of an entity for which the class of membership is optional is added as an attribute to the relationship allocated to an entity with a mandatory class of membership.
   3. If the degree of a binary connection is 1: 1 and the class of ownership of any of the entities is not optional, then three relationships are used — one for each entity — the keys of which serve as primary in the respective relations and one for communication. The relationship allocated to the relationship will have one entity key from each entity.
   4. If the degree of a binary connection is 1: M and the membership class of an M-connected entity is required, then it is sufficient to use two relationships: one for each entity, provided that the entity key serves as the primary key for the corresponding relationship. The key of the simply connected entity must be added as an attribute to the relation relegated to the M-connected entity.
   5. If the degree of a binary connection is 1: M and the class of membership of an M-connected entity is optional, then three relationships are necessary: ​​one for the entity and one for the relationship. A link must have among its attributes an entity key from each entity.
   6. If the degree of a binary connection is equal to M: M, then three relationships are required for data storage: one per entity and one for communication. Entity keys are in communication. If one of the entities is degenerate, then - two relationships (i.e. two tables will suffice).
   7. In the case of three-way communication, it is necessary to use four relationships: one per entity and one for communication. The relationship generated by the relationship has among the attributes of the essence keys from each entity.
   
   
   We use the rules, we summarize the data in the table.
   
   

   Entities	Rule number	Relations
   Customer Order	four	Customer (# Customer Order (# Order, # Client
   Employee Order	four	Employee (# Employee Order (Order # Employee
   Order Order item	four	Order (Order # Order item (Order Item #, Order #
   Brigade Order item	four	Brigade (# Brigade Order Item (#Element, #Brigade
   Product Order item	four	Product (Products Order item (# Items, # Items
   Customer Order	6	Customer (# Customer Order (Order # Payment (# Payment, # Customer, # Order
   Brigade Employee	five	Brigade (# Brigade Employee (# Employee Brigade Officer (# Brigade Officer, # Employee, # Brigade
   Order item Operation	five	Order item (# of item Operation (# Operations Record operation (# Records, # Element, # Operations
   Order item Material	five	Order item (# of item Material (# of Material Expense (# Entries, # Elements, # Materials

   Tab. one
   
   Distributing the attributes of the relations obtained, we obtain (in the list of fields in the first place - the primary key, the others, marked with "#", are foreign keys):
   
   

   Team	(# Brigade, # Foreman, Location)
   POSITION	(# Position, Position, Salary)
   ORDER	(#Order, #Customer, #Customer ,Designation Date, Required Date ,Datement Date, Description)
   CUSTOMER	(# Of the Customer, Name, First Name, Last Name, Organization, Or Department, Address, Telephone Number, E-Mail Address)
   RECORD OPERATIONS	(# Records, # Element, # Operations, # Employee, Number)
   PAYMENT	(# Payment, #Customer, #Order, Payment Amount, DateOpayment, Notes)
   CONSUMPTION	(# Records, # PassMat, # Elements, Quantity)
   COMPOSITION	(# Of item, # of order, # of goods, # of brigade, number)
   BATHROOMS	(#StarBrigades, #Gangs, # Employee)
   EMPLOYEE	(# Employee, Passport Number, Last Name, First Name, Patronymic, # Posts, Address, Home Phone, Work Phone, Date of Birth, Date Border, Date End of Agreement, Photo, Notes)
   OPERATION	(#Operations, Description, Cost, Time, Equipment, Execution)
   MATERIAL	(#Increase, NaimRassMat, Price, Density, Type, Composition)
   PRODUCT	(# Goods, Brand, Name, Description of the Goods, Type, Serial Number, In Stock, Price)

   Tab. 2
   

So we were taught to do at the university. Maybe it will be interesting to someone. About “do I need this,” I listen to your opinions!