The 10-er model of database system concept

By establishing the mapping of the entity to the conceptual model, Entity-relationship model can express the logical structure of the whole database, and many database products use E-R model to express the database design.

first, the E-R model employs three basic concepts: entity sets, relationship sets, and attributes
A) Entity set
Entity entities are equivalent to things or objects in reality, such as a person in a university is an entity, an entity has a set of attributes that can be uniquely determined by one or several of these attributes, such as person_id. Entities can be concrete things in reality, such as person or book, or abstract things like money, course, etc.
An entity set is a collection of entities that have the same attribute, such as all teachers in a university make up a set of entities called instructors.
The entity set can be intersected, and if the persons entity set is the owner of the school, a person entity may be a instructor entity or a student entity, or neither.
b) Contact Set
Contact refers to the correlation between multiple entities. For example, you can define the link advisor between teacher A and student B, which indicates that a is a mentor of B. A contact set is a set of contacts of the same type and is a direct mathematical relationship of n>=2 entity sets. Associations between entity sets are called participation (participate), contacts can also have descriptive attributes (descriptive attribute), entity set instructor and student are linked as advisors, You can associate a property date with the contact to indicate the date that the teacher became a student mentor.
c) attribute
Each property has a collection of desirable values, called the domain of the attribute, or a set of values (value set). The course_id field may be a collection of all strings of a particular length, and the field of semester can be four seasons in spring and autumn. An entity has multiple properties, each of which can be described by a key-value pair of property name-value, such as an entity of instructor that can be described as: {(id,7677), (Name,crick), (Dept_name, Biology)}
Classification of attributes
Simple properties and composite properties. Simple attributes can no longer be divided into smaller parts, but composite properties are possible. For example, name can be further divided into first_name\middle_name\last_name. If you want to use the entire attribute in some scenarios, but only one part of the attribute is required in another scenario, the composite attribute is appropriate. Composite properties help to group related properties and make modeling clearer.
Single-valued and multi-valued properties.
In general, a property will only have one value, but there are multiple values, such as Phone_number, where a person may have multiple phone numbers. Multivalued attributes are distinguished by curly braces, {phone_number}, where appropriate, you can set the upper and lower bounds of a multivalued attribute, such as setting the number of Phone_number to 1-2.

second, the restraint constraints
A) mapping cardinality (Mapping cardinalities)
The map cardinality, also known as the mapping ratio, represents the number of entities that an entity can associate with a unified contact set, often in a two-yuan contact set. Assuming that R is a contact set between the entity A-B, there are several scenarios for mapping the cardinality:
Single to one, the entity of a is associated at most with a B entity, and vice versa.
One to many, the entity of a can be associated with any number of B entities (>=0), and B entities can only be associated with at most one a entity.
Many-to-many (many to many). The number of interrelated numbers between A and B entities is not limited.

b) Participation in constraints (participation Constraints)
If each entity in entity set E participates in at least one contact of the contact set R, the participation of E in R is all (total), otherwise partial (partial). For example, a-B is a one-to-one mapping, the number of entities in a is greater than B. then part a participates and B participates.

three, E-r model transformation to relational model
A) Convert strong entity sets with simple attributes
Suppose E is a a1,a2 with a simple descriptive attribute: Strong entity set, which can be converted to a relationship model E with the same attribute, one of its ancestors corresponds to an entity in the entity set, and the primary key is also the primary key of the entity sets E.
b) Converting strong entity sets with complex attributes
For composite properties, a separate property is created for each sub-property, such as splitting the name into first_name, Middle_name, last_name, or multiple meta-patriarch for multivalued attributes, such as the phone_ of a teacher with ID 22 Number has 555-1234 and 444-4321, which corresponds to two primitives (22, 555-1234) and (22, 444-4321), which together form the primary key.
c) Conversion of contact sets
The contact set is also converted into a relational model, and the properties of all participating entity sets and the descriptive properties of the contact set constitute the properties of the transformed model. With respect to the primary key, for many-to-many two-yuan contacts, the primary key is the set of the primary key of the entity set that participates in the association; for one-to-ones connections, you can use either of the primary keys, or the primary key for a many-to-a connection.


Learning materials: Database System concepts, by Abraham Silberschatz, Henry F.korth, S.sudarshan?

The 10-er model of database system concept