Database ER diagram converted to relational schema

The E-R diagram is composed of three elements: entities, attributes of entities, and connections between entities. Converting an E-R diagram into a relational model actually means converting entities, their attributes, and the connections between entities into a relational model.

The E-R diagram is composed of three elements: entities, attributes of entities, and connections between entities. Converting an E-R diagram into a relational model actually means converting entities, their attributes, and the connections between entities into a relational model. .

2. E-R diagram conversion relationship model

1. Conversion of entity set to relational model

Principles followed by general conversion

Conversion rules of entity set : Convert an entity type to a relational schema. The attributes of the entity are the attributes of the relationship, and the code of the entity is the code.

Example: The student entity can be converted into the following relationship model:

Student (student number, name, gender, date of birth, department, grade)

2. Two Conversion of the relationship set between entity types to relationship schema

1:1 contact conversion method: 1:1 contact can be converted into an independent relationship schema, or it can be merged with the corresponding relationship schema at either end.

If converted to an independent relationship schema, the primary key of each entity connected to the relationship and the properties of the relationship itself are converted to the properties of the relationship, and the primary key of each entity is a candidate for the relationship. code.

If you merge with the relationship schema corresponding to one end, you need to add the main code of the other relationship schema and the attributes of the contact itself to the attributes of the relationship schema.

Example

1:n contact conversion method: 1:n contact can be converted into an independent relationship schema, or it can be merged with the corresponding relationship schema at the n end.

If converted to an independent relationship model, the primary code of each entity connected to the relationship and the properties of the relationship itself are converted into the properties of the relationship, and the primary code of the relationship is the primary code of the n-terminal entity. .

If merged with the n-terminal relationship model, a new attribute will be added to the n-terminal entity set. The new attribute consists of the primary code that contacts the corresponding 1-terminal entity set and the attributes that relate to itself. The primary code of the relationship model constant.

Example

Option 1: 1: The relationship formed by n contact exists independently

Warehouse (warehouse number, location, area)

Product (product number , product name, price)

Warehouse(product number, warehouse number, quantity)

Option 2: Merge the relationship formed by the contact with the n-end object

Warehouse(warehouse number) , location, area)

product (product number, product name, price,

warehouse number, quantity)

m:n contact conversion method: connected to the contact The codes of each entity and the attributes of the relationship itself are converted into attributes of the relationship. The code of the relationship is a combination of the codes of each entity.

Example

3. Conversion of the relationship set between the same entity type to the relationship model

vThe relationship between entities of the same entity type is a self-relationship, and can also be pressed The above three situations of 1:1, 1:n and m:n are handled separately.

1：n

m:n

4. Conversion of the relationship between multi-entity types to relational patterns

Generally, more than two There are also three situations: 1:1, 1:n and m:n between entity types. The conversion method generally follows the following principles:

1: Multiple connections of n: modify the relationship corresponding to the 1-end entity set , that is, adding the codes of other entity sets related to the contact and the attributes of the contact itself as new attributes to the 1-end entity set.

M:n Multiple relationship: Create a new independent relationship. The codes of each entity connected by the multiple relationship and the attributes of the relationship itself are converted into the attributes of the relationship. The code of the relationship is a combination of the codes of each entity.

5. Conversion of weak entity set to relational model

The concepts of strong entity set and weak entity set are closely related to existential dependence. The members of strong entity set must be dominant entities, while weak entities must be dominant entities. The members of a set are subordinate entities.

Since a weak entity cannot exist independently, it must be attached to an owner entity. Therefore, when converting the weak entity set into a relationship mode, the relationship corresponding to the weak entity must contain the master key of the owner entity.

6. Application Example

**After converting the ****E-R diagram to the relational model according to the above rules, the relational models with the same primary code can be merged. The main purpose is to reduce the system The number of relationships in it facilitates maintenance and data consistency.

The merging method is to add all the attributes of a relationship model in the relationship model with the same primary key to another relationship model, and then remove the synonymous attributes (which may have the same name or different names), And adjust the order of attributes appropriately.

Example 3-12 The E-R diagram of a certain project is shown in Figure 3-19. The attributes of each entity are as follows:

The above is the detailed content of Database ER diagram converted to relational schema. For more information, please follow other related articles on the PHP Chinese website!