Building a relational structure from an ER model

To the articles Development → Relationships of database tables and Development → Designing databases of a hawk ueasley I would like to make a small addition.

I want to describe the rules by which you can build a relational database schema. Few people need these rules, since they are used by developers on an intuitive level, but they are interesting even because they formalize the process of constructing a database schema.

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

ER model

ER- model is a diagram, the constituent elements of which are:

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

Let me give you an example:
Let it be necessary to store information about customers and their orders. Let’s build a diagram


Fig. 1

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

Relation (usually it corresponds to the table in the database data) should not be confused with the entity. An entity transforms into a relation by extracting it from the ER diagram.

Design stages

1. Conceptual design

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

2. Logical design

   
   A set of preliminary relationships is constructed with an indication of the primary key for each relationship. A list of attributes is compiled, then these attributes are distributed by relationship. All relationships must remain with the NBFI.
   
   The transition to a relational structure (building a set of relations) is performed according to the following rules:
   
   
   1. If the degree of binary communication is 1: 1 and the membership class of both entities is mandatory, then only one relationship is required. The primary key to this relationship can be the key to either of these two entities. In this case, a single occurrence of each key value in any instance of the relationship is guaranteed.
   2. If the degree of binary connection is 1: 1 and the class of one of the entities is optional, then it is necessary to build two relationships, for each entity you need to highlight one relationship. An entity key, for which the membership class is optional, is added as an attribute to the relation allocated to the entity with the required membership class.
   3. If the degree of binary communication is 1: 1 and the membership class of none of the entities is optional, then three relations are used - one for each entity - the keys of which serve as primary in the corresponding relations and one for communication. The relationship allocated for communication will have one entity key from each entity.
   4. If the degree of the binary connection is 1: M and the membership class of the M-connected entity is mandatory, then it is enough to use two relations: one for each entity, provided that the entity key serves as the primary key for the corresponding relationship. The key of a simply connected entity should be added as an attribute to the relation assigned to the M-connected entity.
   5. If the degree of the binary connection is 1: M and the membership class of the M-connected entity is optional, then it is necessary to use three relations: one for the entity and one for the connection. A link must have, among its attributes, an entity key from each entity.
   6. If the degree of binary communication is equal to M: M, then three relations are needed to store data: one per entity and one for communication. Keys of essence enter into communication. If one of the entities is degenerate, then there are two relations (i.e. two tables will suffice).
   7. In the case of a three-way communication, four relationships must be used: one per entity and one for communication. The relation generated by the connection has, among the attributes, the keys of the essence of each entity.
   
   
   We will use the rules, we will reduce the data to a table.
   
   

   Entities	Rule number	Relations
   Customer Order	4	Customer (# Customer Order (# Order, # Customer
   Employee Order	4	Employee (# Employee Order (# Order, # Employee
   Order Order Item	4	Order (#Order Order element (#Order element, #Order
   Brigade Order Item	4	Brigade (# Brigades Order element (# Element, # Brigades
   Product Order item	4	Item (# Item Order item (# Item, # Item
   Customer Order	6	Client (# Client's Order (# Order Payment (# Payment, # Client, # Order
   Team Worker	5	Brigade (# Brigade Employee (# Employee Brigade employee (# Brigade employee, # Employee, # Brigade
   Order item Operation	5	Order element (# Element Operation (# Operations Record operation (# Records, # Element, # Operations
   Order Item Material	5	Order element (# Element Material (# Material Consumption (# Records, # Element, # Material

   Tab. 1
   
   Having distributed the attributes according to the obtained relations, we get (in the list of fields in the first place is the primary key, the others marked with “#” are foreign keys):
   
   

   Brigade	(# Brigades, # Brigadier, Location)
   POSITION	(# Positions, Position, Salary)
   ORDER	(#Order, #Customer, #Employee, Placement Date, RequiredDate, Execution Date, Description)
   CLIENT	(# Customer, Name, First Name, Surname, Organization or Department, Address, Telephone Number, Email Address)
   RECORDINGS	(# Records, # Element, # Operations, # Employee, Quantity)
   PAYMENT	(#Payment, #Customer, #Order, Payment Amount, Payment Date, Notes)
   CONSUMPTION	(#Records, #RaxMat, #Element, Quantity)
   COMPOSITION	(#Item, #Order, #Product, #Gangs, Quantity)
   COLLECTIONS	(# Sotr Brigades, # Brigades, # Employee)
   EMPLOYEE	(# Employee, Passport Number, Surname, First Name, Patronymic, # Positions, Address, Home Phone, Work Phone, Date of Birth, Date of Employment, Date of Contract Termination, Photo, Notes)
   OPERATION	(# Operations, Description, Cost, Time, Equipment, Execution)
   MATERIAL	(# RaskhMat, NaimRaskhMat, Price, Density, Type, Composition)
   PRODUCT	(# Product, Brand, Name, Description of the Product, Type, Serial Number, In Stock, Price)

   Tab. 2
   

That's what we were taught to do at the university. Maybe someone will be interested. As for "whether it is necessary," I am listening to your opinions!