Elements of the entity-relationship model
Modeling the database structure using the normalization algorithm described in previous chapters has serious disadvantages:
Initially placing all attributes in one relation is a very unnatural operation. Intuitively, the developer immediately designs several relationships in accordance with the detected entities. Even if you commit violence against yourself and create one or more relationships, including all the expected attributes, the meaning of the resulting relationship is completely unclear.
It is not possible to immediately determine the complete list of attributes. Users have a habit of calling different names the same things or vice versa, calling different things by the same names.
To carry out the normalization procedure, it is necessary to isolate the dependencies of the attributes, which is also very difficult, because necessary explicitly list all dependencies, even those that are obvious.
In actual database structure design, a different method is used - the so-called semantic modeling . Semantic modeling is the modeling of data structure based on the meaning of that data. Various options are used as a semantic modeling tool entity-relationship diagrams (ER - Entity-Relationship ).
The first version of the entity-relationship model was proposed in 1976 by Peter Ping-Sheng Chen. Subsequently, many authors developed their own versions of similar models (Martin notation, IDEF1X notation, Barker notation, etc.). In addition, different software tools that implement the same notation may differ in their capabilities. In fact, all variants of entity-relationship diagrams are based on the same idea - a drawing is always clearer than a text description. All such diagrams use a graphical representation of domain entities, their properties (attributes), and relationships between entities.
We will describe working with ER diagrams close to Barker's notation as fairly easy to understand the basic ideas. This chapter is more of an illustration of semantic modeling techniques than a full introduction to the field.
Basic concepts of ER diagrams
Definition 1. Essence is a class of objects of the same type, information about which must be taken into account in the model.
Each entity must have a name expressed by a singular noun.
Examples of entities can be such object classes as “Supplier”, “Employee”, “Invoice”.
Each entity in the model is depicted as a rectangle with a name:
Rice. 1
Definition 2. Entity instance is a specific representative of a given entity.
For example, a representative of the “Employee” entity can be “Employee Ivanov”.
Entity instances must be distinguishable, i.e. entities must have some properties that are unique to each instance of that entity.
Definition 3. Entity attribute is a named characteristic that is some property of an entity.
The name of the attribute must be expressed as a singular noun (possibly with characterizing adjectives).
Examples of attributes of the “Employee” entity can be such attributes as “Personnel number”, “Last name”, “First name”, “Patronymic”, “Position”, “Salary”, etc.
Attributes are depicted within a rectangle defining the entity:
Rice. 2
Definition 4. Entity Key - This non-redundant a set of attributes whose collective values are unique for each entity instance. Non-redundancy is that removing any attribute from a key breaks its uniqueness.
An entity can have several different keys.
Key attributes are depicted on the diagram with an underline (or a key sign is drawn next to the key attribute):
Rice. 3
Definition 5. Connection - this is some kind of association between two entities. One entity can be connected to another entity or to itself.
Relationships allow one entity to find other entities related to it.
For example, connections between entities can be expressed by the following phrases - “An EMPLOYEE can have several CHILDREN”, “Each EMPLOYEE must be enrolled in exactly one DEPARTMENT”.
Graphically, the relationship is depicted by a line connecting two entities:
Rice. 4
Each link has two ends and one or two names. The name is usually expressed in an indefinite verbal form: “to have”, “to belong”, etc. Each name refers to its own end of the connection. Sometimes names are not written because they are obvious.
Each link can have one of the following types of communication :
Rice. 5
Communication type one to one means that one instance of the first entity (left) is associated with one instance of the second entity (right). A one-to-one relationship most often indicates that we actually have only one entity, incorrectly divided into two.
Communication type one-to-many means 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 communication. The left entity (on the "one" side) is called parental , right (from the “many” side) - subsidiary . A typical example of such a connection is shown in Fig. 4.
Communication type many-to-many means that each instance of the first entity can be associated with multiple instances of the second entity, and each instance of the second entity can be associated with multiple instances of the first entity. The many-to-many type of relationship is temporary type of communication acceptable in the early stages of model development. In the future, this type of relationship must be replaced by two one-to-many relationships by creating an intermediate entity.
Each connection can have one of two communication modalities :
Rice. 6
Modality" Maybe may be related with one or more instances of another entity, or maybe not related not a single copy.
Modality" must " means that an instance of one entity must be associated with at least one an instance of another entity.
Communication may have different modality from different ends (as in Fig. 4).
The described graphical syntax allows definitely read the diagrams using the following phrase structure:
<Каждый экземпляр СУЩНОСТИ 1> <МОДАЛЬНОСТЬ СВЯЗИ> <НАИМЕНОВАНИЕ СВЯЗИ> <ТИП СВЯЗИ> <экземпляр СУЩНОСТИ 2>.
Each link can be read either from left to right or from right to left. Connection in Fig. 4 reads like this:
From left to right: "each employee can have several children."
From right to left: “Each child must belong to exactly one employee.”
An example of developing a simple ER model
When developing ER models, we must obtain the following information about subject area:
List of domain entities.
List of entity attributes.
Description of the relationships between entities.
ER diagrams are convenient because the process of identifying entities, attributes and relationships is iterative. Having developed the first approximate version of the diagrams, we refine them by interviewing subject matter experts. At the same time, the documentation in which the results of the conversations are recorded are the ER diagrams themselves.
Let's assume that we are faced with the task of developing an information system for a certain wholesale trading company. First of all, we must study the subject area and the processes occurring in it. To do this, we interview company employees, read documentation, study order forms, invoices, etc.
For example, during a conversation with a sales manager, it turned out that he (the manager) believes that the system being designed should perform the following actions:
Store customer information.
Print invoices for goods released.
Monitor the availability of goods in the warehouse.
Let's select all the nouns in these sentences - these will be potential candidates for entities and attributes, and analyze them (we will highlight unclear terms with a question mark):
Buyer
Invoice is a clear candidate for the entity.
Product- a clear candidate for the entity
(?)Stock- In general, how many warehouses does the company have? If there are several, then it will be a candidate for a new entity.
(?)Product availability– this is most likely an attribute, but an attribute of what entity?
An obvious connection immediately arises between the entities - “buyers can buy many goods” and “goods can be sold to many buyers.” The first version of the diagram looks like this:
Rice. 7
Having asked additional questions manager, we found out that the company has several warehouses. Moreover, each product can be stored in several warehouses and be sold from any warehouse.
Where should I place the entities “Invoice” and “Warehouse” and what should I link them to? Let us ask ourselves, how are these entities related to each other and to the entities “Buyer” and “Product”? Buyers purchase goods and receive invoices containing data on the quantity and price of the purchased goods. Each buyer can receive several invoices. Each invoice must be issued to one buyer. Each invoice must contain several goods (there are no empty invoices). Each product, in turn, can be sold to several buyers through several invoices. In addition, each invoice must be issued from a specific warehouse, and many invoices can be issued from any warehouse. Thus, after clarification, the diagram will look like this:
Rice. 8
It's time to think about entity attributes. Speaking with employees of the company, we found out the following:
Each buyer is a legal entity and has a name, address, and bank details.
Each product has a name, price, and is also characterized by units of measurement.
Each invoice has a unique number, date of issue, a list of goods with quantities and prices, as well as the total amount of the invoice. The invoice is issued from a specific warehouse and to a specific buyer.
Each warehouse has its own name.
Let's write down all the nouns that will be potential attributes again and analyze them:
Entity- the term is rhetorical, we do not work with individuals. We don't pay attention.
Buyer's name
Address- a clear characteristic of the buyer.
Bank details- a clear characteristic of the buyer.
Name of product- a clear characteristic of the product.
(?)The price of the product- it seems that this is a characteristic of the product. Does this characteristic differ from the price on the invoice?
Unit- a clear characteristic of the product.
Invoice number- a clear unique characteristic of the invoice.
Invoice date- a clear characteristic of the invoice.
(?)List of goods in the invoice- a list cannot be an attribute. You probably need to separate this list into a separate entity.
(?)Quantity of goods in the invoice- this is an obvious characteristic, but a characteristic of what? This is a characteristic of not just a “product”, but a “product in the invoice”.
(?)The price of the goods in the invoice- again, this should not just be a description of the product, but a description of the product in the invoice. But the price of the product has already been seen above - is it the same thing?
Invoice amount- a clear characteristic of the invoice. This characteristic is not independent. The amount of the invoice is equal to the sum of the costs of all goods included in the invoice.
Warehouse name- a clear characteristic of the warehouse.
During an additional conversation with the manager, it was possible to clarify various concepts of prices. It turned out that each product has a certain current price. This is the price at which the product is currently being sold. Naturally, this price may change over time. The price of the same product in different invoices issued at different times may be different. Thus there is two prices- the price of the goods in the invoice and the current price of the goods.
With the emerging concept of “List of goods in the invoice” everything is quite clear. The entities "Invoice" and "Product" are related to each other by a many-to-many relationship. Such a relationship, as we noted earlier, must be split into two one-to-many relationships. This requires an additional entity. This entity will be the “List of goods in the invoice” entity. Its connection with the entities “Invoice” and “Product” is characterized by the following phrases - “each invoice must have several entries from the list of goods in the invoice”, “each entry from the list of goods in the invoice must be included in exactly one invoice”, “each product can be included into several entries from the list of goods in the invoice", "each entry from the list of goods in the invoice must be associated with exactly one product." The attributes "Quantity of goods in the invoice" and "Price of the goods in the invoice" are attributes of the entity "List of goods in the invoice".
We will do the same with the connection connecting the entities “Warehouse” and “Product”. Let's introduce an additional entity "Item in warehouse". The attribute of this entity will be “Quantity of goods in stock”. Thus, the product will be listed in any warehouse and its quantity in each warehouse will be different.
Now you can put all this into a diagram:
Rice. 9
Conceptual and physical ER models
The example ER diagram developed above is an example concept diagram . This means that the diagram does not take into account features of a specific DBMS. From this conceptual diagram you can construct physical diagram , which will already take into account such features of the DBMS as permissible types and names of fields and tables, integrity restrictions, etc. Physical version of the diagram shown in Fig. 9 might look like this, for example:
Rice. 10
In this diagram, each entity represents a database table, each attribute becomes a column of the corresponding table. Please note that in many tables, for example, "CUST_DETAIL" and "PROD_IN_SKLAD", corresponding to the entities "Invoice list record" and "Item in warehouse", new attributes have appeared that were not in the conceptual model - these are the key attributes of the parent tables , migrated into child tables in order to provide relationships between tables using foreign keys.
It is easy to notice that the resulting tables are immediately in 3NF.
conclusions
The real means of data modeling is not the formal method of normalizing relations, but the so-called semantic modeling .
Various options are used as a semantic modeling tool entity-relationship diagrams (ER - Entity-Relationship ).
Entity-relationship diagrams allow you to use visual graphical notations to model entities and their relationships.
Distinguish conceptual And physical ER diagrams. Conceptual diagrams do not take into account the specific features of specific DBMSs. Physical diagrams are built on conceptual ones and represent a prototype of a specific database. Entities defined in the conceptual diagram become tables, attributes become table columns (taking into account the data types and column names allowed for a given DBMS), connections are implemented by migration key attributes of parent entities and creating foreign keys.
If entities are correctly defined, the resulting tables will immediately be in 3NF. The main advantage of the method is that the model is built by successive refinements of the initial diagrams.
This chapter, which is an illustration of ER modeling methods, does not cover more complex aspects of diagramming, such as subtypes, roles, exclusive relationships, non-transferable relationships, identifying relationships, etc.
Elements of the entity-relationship model Entity - Entity Class - Entity Instance Attributes - Composite Attributes - Multivalued Attributes Identifiers - Unique/non-unique - Composite Relationships - Relationship Classes - Relationship Instances - Recursive Relationships
Elements of the entity-relationship model An entity class is a collection of entities described by the structure or format of the entities that make up this class. An entity instance represents a specific entity. Typically, an entity class holds many instances of an entity.
Elements of the “entity-relationship” model Attributes Attributes (properties) – describe the characteristics of an entity. Example of a composite attribute: An address consisting of a group of attributes (Street, City, Postal Code). An example of a multi-valued attribute: the Student Name attribute of the TEACHER entity, which can contain the names of several students he teaches.
Elements of the entity-relationship model Identifiers Identifiers are attributes by which instances of entities are named or identified. If the identifier is unique, its value will point to one and only one instance of the entity. If the identifier is non-unique, its value will point to some set of instances. Identifiers consisting of several attributes are called composite identifiers.
Elements of the “entity-relationship” model Relationships Relationships between entities are expressed by connections. Relationship classes are relationships between classes of entities. Relationship instances—relationships between entity instances; relationship degree—number of entity classes involved in the relationship. Notation by means in UML diagrams: Relationship is indicated by
Elements of the entity-relationship model Three types of binary relationships Notation by means in UML diagrams: A 1:1 relationship (“one to one”) is denoted a 1:N relationship (“one to N” or “one to many”) – N relationship :M (read “N to M” or “many to many”) - The connection of possession in a generalized form, when a specific type of connection is not specified - The numbers inside the diamond symbolizing the connection indicate the maximum number of entities on each side of the connection. These restrictions are called maximum cardinal numbers, and the set of two such restrictions for both sides of the connection is called the maximum cardinality of the connection.
Entity-Relationship Diagrams The binary relationship diagrams shown above are called entity-relationship diagrams (ER-diagrams). There are several ways to specify minimum cardinality. One of them is shown below. Relationship to a specified minimum cardinality
Weak entities Weak entities are entities that can exist in the database only if an entity of some other type is present in the database. An entity that is not weak is called a strong entity. ID-dependent entities are those entities whose identifiers contain the identifier of another entity.
27
UML-style entity-relationship diagrams OOP constructs introduced by the UML language All entity classes that must be stored in the database are labeled with the stereotype "Persistent" UML allows attributes to be assigned to entity classes UML uses object-oriented notation to indicate the visibility of attributes and methods “+” - open “#” - protected “-” - closed
UML-style entity-relationship diagrams A public attribute is an attribute that can be read and modified by any method on any object. The term protected means that an attribute or method is available only to methods of this class and its subclasses. And the term private indicates that the corresponding attribute or method is available only to methods of this class. UML defines constraints and methods.
The model was proposed by Peter Ping-Shen Chen in 1976. Most modern approaches to database design (mainly relational) are based on the use of variations of the ER model. Domain modeling is based on the use of graphical diagrams that include a small number of heterogeneous components. Due to the clarity of presentation of conceptual database diagrams, ER models have become widespread in CASE systems that support automated design of relational databases. The basic concepts of the ER model are entity, relationship and attribute.
Essence - it is a real or imaginary object about which information is of interest. In ER model diagrams, an entity is represented as a rectangle containing the name of the entity. In this case, the name of the entity is the name of the type, and not a specific object - an instance of this type. Each instance of an entity must be distinguishable from every other instance of the same entity.
Connection is a graphically depicted association established between two entities. This association is always binary and can exist between two different entities or between an entity and itself (recursive relationship). In any connection, two ends are identified (in accordance with the pair of connected entities), each of which indicates the name of the end of the connection, the degree of the end of the connection (how many instances of this entity are connected), the mandatory nature of the connection (i.e., whether any instance of this entity must participate in this connection).
A connection is represented as a line connecting two entities or leading from an entity to itself. In this case, at the point where the connection “joins” with the entity, a three-point entry into the entity rectangle is used, if many instances of the entity can be used for this entity in the connection, and a single-point entry, if only one instance of the entity can participate in the connection. The required end of the connection is depicted with a solid line, and the optional end with a broken line.
Like an entity, a relationship is a generic concept; all instances of both pairs of related entities are subject to the rules of association.
Figure 12 shows an example of an image of entities and the relationship between them.
Rice. 12.
This diagram can be interpreted as follows: Each STUDENT studies in only one GROUP; Any GROUP consists of one or more STUDENTS. The following figure (Fig. 13) depicts the essence of MAN with a recursive connection connecting it with itself.
Fig. 13.
A laconic oral interpretation of the diagram depicted is as follows:
Every PERSON is the son of one and only one PERSON;
Each PERSON may be the father of one or more PEOPLE (“PERSON”).
Attribute An entity is any detail that serves to clarify, identify, classify, numerically characterize, or express the state of an entity. Attribute names are entered in a rectangle representing the entity, under the entity name and are depicted in small letters. For example (see Fig. 14):
Rice. 14.
An entity's unique identifier is an attribute, combination of attributes, combination of relationships, or combination of relationships and attributes that uniquely distinguishes any instance of the entity from other instances of the same type of entity.
As in relational database schemas, ER schemas introduce the concept of normal forms, and their meaning closely matches the meaning of relational normal forms. Note that the formulations of normal forms of ER-schemas make the meaning of normalization of relational schemes clearer. We will consider only very brief and informal definitions of the first three normal forms.
IN first normal form ER schemas eliminate duplicate attributes or groups of attributes, i.e., implicit entities “disguised” as attributes are identified.
In second normal form attributes that depend only on part of the unique identifier are eliminated. This part of the unique identifier identifies an individual entity.
IN third normal form attributes that depend on attributes that are not part of the unique identifier are eliminated. These attributes are the basis of a separate entity. We focused only on the most important concepts ER data models. More complex elements of the model include the following:
Subtypes and supertypes of entities. The ER model allows you to specify the IS-A relationship between types. Moreover, if T 1 IS-A T 2 (where T 1 and T 2 - entity types), then T 1 is called a subtype T 2 a T 2- supertype T 1. Thus, it is possible to inherit an entity type based on one or more supertypes.
Many-to-many relationships. Sometimes it is necessary to link entities in such a way that there may be multiple instances of the entity at both ends of the link (for example, all members of a cooperative jointly own the property of the cooperative). To do this, a type of “many-to-many” relationship is introduced.
Specifiable degrees of connection. Sometimes it is useful to define the possible number of entity instances participating in a given relationship (for example, an employee is allowed to participate in no more than three projects at a time). To express this semantic constraint, it is allowed to indicate at the end of the connection its maximum or mandatory degree.
Cascade deletions of entity instances. Some relationships are so strong (in the case of a one-to-many relationship, of course) that when you delete the reference entity instance (corresponding to the one end of the relationship), you must also delete all entity instances corresponding to the many end of the relationship. The corresponding requirement for "cascading deletion" can be formulated when defining an entity.
Domains. As with the relational data model, it is useful to be able to define a potentially valid set of values for an entity (domain) attribute.
These and other, more complex elements of the Entity-Relationship data model make it more powerful, but at the same time make it somewhat more difficult to use. Of course, when actually using ER diagrams for database design, you need to become familiar with all the possibilities.
subject area and tasks to be solved. So, in the relational data model, which we will study in “Relational Data Model”, it is impossible to set declarative integrity constraints other than primary, unique and foreign keys. The description of procedural restrictions generally lies outside this model.The entity-relationship model (ER diagrams, ER model) discussed below is special case semantically rich data models. It allows you to describe semantics intended for human use. That is, you can enter descriptions that are not implemented in software. On the other hand, it records the metadata and integrity constraints used to create scripts that generate the database schema.
2.1 Semantic models and cognitive aspect
2.1.1 Semantic data models
What do databases store? Of course, data. However, even to organize data storage, one must take into account the meanings associated with them. For example, the previous section described a primary key that prevents duplicate records in a set. This property defines the private meaning of a recordset with a primary key. Data types, domains, metadata define other meanings of the stored data.
But if only data is stored in the database, then how are the meanings stored? First of all, meanings are also data associated with the data whose meaning they represent.
Let us highlight the following types of meanings:
- Meanings intended only for humans. Can be stored in information systems ah (IS), but passive, that is, inaccessible to the system, and therefore do not affect its behavior. Can only be retrieved by humans
- Meanings internal to IS. They are active, that is, they change or create new behavior of the IS. Typical examples: keys, data types, metadata.
- External meanings associated with systems or tasks external to the IS, or, more narrowly, to the database. These meanings are also active.
How does the activity of internal meanings manifest itself? Let there be a primary key. You want to write a record to a set. However, the DBMS will first do what you did not ask - it will check the validity of the entered key value - and only if this value is missing will it write a record.
An example of the third type of meaning: There is a table containing the grades of all students in all disciplines. Is it possible to calculate the average score? Certainly. However, if you are familiar with measurement scales, then you know that performance is measured on an order scale. In it, the average score has no meaning, or, in official language, is an inadequate statistic.
At the initial stage of creating an application (business analysis), it is necessary to have a domain model that provides an informal description of all the significant features of the problem known to the designer. At the same time, discarding details that do not fit into the data models used at the project implementation stage can lead to a significant distortion of the problem statement. At the analysis stage, completeness of information should be preferred to the possibility of its formal description.
Semantic models are commonly called models that provide a representation of the semantics of data. Like other models, they can include structural, manipulative and holistic parts. However, given that there is some kind of semantics in any model, those models that contain more semantics can be considered semantic than “non-semantic” models that contain little semantics. This pseudo-definition is very vague. But for now this is enough for us.
Within the semantic model, a conceptual database schema is created, which is usually manually or automatically (but not automatically) converted into a database schema valid within the data models implemented in the following stages life cycle project - design, development and support.
Data semantics will be discussed in detail in the lecture “Database Semantics” of the textbook.
The most famous entity-relationship (ER) semantic model was proposed by Peter Chen in 1976.
2.1.2 Cognitive aspect
Semantic models are implemented in the form of human-readable diagrams. In modern science in general and in computer science in particular, much and deserved attention is paid to cognitive aspects. In the context of databases, this means identifying the two main actors - humans and programs - and developing natural, human-friendly models, languages, interfaces and algorithms for the user experience. Naturally, it is necessary to take into account the preliminary professional training of the user, which determines, along with everyday knowledge, the mental world of a person, the set of images (gestalts) with which he operates. Why do we expect a head menu at the top of the window? Only because the developers of some successful software products.
2.1.3 Model levels
Following Peter Chen's seminal work on entity-relationship diagrams, we distinguish four levels of data model representation with slightly modified definitions:
- Information about objects and relationships of the domain (software), presented in software terms (conceptual model).
- Structured information about software, presented in terms of information systems (logical model).
- Data structures that are independent of the access method, that is, not related to data structures, search, indexing, etc. (physical model).
- Data structures depending on the access method (hardware level model).
Looking ahead, we note that the relational model refers to levels 2 and 3. The network and hierarchical models, as they existed 20 years ago, work mainly at levels 3 and 4. UML is levels 1, 2 and 3, but UML goes far beyond describing data. The entity-relationship model works at levels 1 and 2.
To develop a database, the structure of which does not depend on specific information needs and allows you to fulfill any user requests, an “entity-relationship” information model diagram (ER diagram) is used.
Most often, the formalization of ideas about the subject area is carried out within the framework of the “entity-relationship” model (“objects-relationships”). On at this stage design, the “essence-relationship” method is used, which is also called the “ER-diagram” method (“Essence” - entity, “Relation” - connection). This method is based on the use of diagrams called ER-instance diagrams and ER-type diagrams, respectively.
ER – an “entity-relationship” diagram is a set of many objects and their characteristics, as well as the relationships between them, necessary to identify data that is subsequently used by the functions of the system being designed.
The main concepts of the entity-relationship method are the following:
Essence;
Entity attribute;
Entity key;
Relationship between entities;
Degree of connection;
The membership class of entity instances;
ER instance diagrams;
ER-type diagrams.
An information object is understood as some entity of a fragment of reality, for example: an organization, a document, an employee, a place, an event, etc. An entity is an object, information about which is stored in a database. Entity instances are distinct from each other and are uniquely identified. Entity names are nouns. Each object type is identified by its inherent set of attributes. In this course project, the entities are: employee, positions, education, forms of labor participation, faculties, departments and topics.
An attribute ((from Latin attribuo - attribute) - a property or thing inseparable from an object) is a logically indivisible element of the information structure, characterized by a multitude of atomic values. This concept is similar to the concept of "attribute" in a relation. An instance of an object is characterized by a set of specific attribute values of this type object. One or some group of attributes of an object of this type can play the role of a key attribute (entity key). In this course project, the above entities are characterized by attributes, such as: department_code, department_name, department_code, employee_name, etc.
An entity key is an attribute or set of attributes that identifies an instance of an entity (for example, job_code).
A relationship between two or more entities is a dependency between the attributes of those entities. It is denoted by a verb. Moreover, there are two types of connections:
Hierarchical;
Single-level.
To increase clarity and ease of design, graphical means of representing the entity, instances of the entity and the relationships between them are used. The ER diagram is presented in Appendix A.
Classification of connections
In real databases, information is placed in several tables. The tables are linked by information semantics. In relational DBMSs, to indicate table relationships, a linking operation is performed. This increases the reliability of the information stored in the database, since the DBMS controls the integrity of the data entered into the database in accordance with established connections.
Establishing connections makes it easier to access data when performing operations: searching, viewing, editing, retrieving and preparing a report, because access to any fields of related tables is provided.
The following can be installed between tables:
Binary connections;
Ternary connections;
N-ary bonds.
When linking two tables, a primary and a subordinate table (parent and child) are distinguished. Logical linking of tables is done using a link key. The main table fields can be simple or key. The link fields of an additional table are most often key. Depending on how the connection fields of the main and additional tables are defined (how the key fields relate to the connection fields), the types of connections are established:
1:1 (one to one);
1:M (one to many);
M:1 (many to one);
M:M (many to many).
A 1:1 relationship is formed if all fields of the relationship between the parent and child tables are key. Since the values in the key fields of the two tables are not repeated, there is a one-to-one correspondence between the records from these tables. The tables themselves, in fact, become equal here.
A 1:M relationship occurs when one record in the parent table corresponds to several records in the child table.
An M:1 relationship occurs when one or several records of the main table are matched with one record of an additional table.
An M:M relationship occurs in cases where several records of the main table correspond to several records of the additional table.
Similar to a 1:1 relationship, an M:M relationship does not establish subordination of tables. In practice, a relationship usually involves several tables. In this case, one table can have different kinds connections with several tables, forming a hierarchy or “relationship tree”.
In this course project, the tables are connected by 1:M (one-to-many) relationships. For example, the table "faculties" is the parent table of the child table "departments". These tables are related in a 1:M relationship using the key “faculty_code”
Loading...
