[Test] database system principle (II)-detailed explanation (database design and ER Model), database system principle er

[Test] database system principle (II)-detailed explanation (database design and ER Model), database system principle er

Everything has a lifecycle. Of course, the database system is no exception!

The database design and ER model chapter mainly explains the design stages based on the database system lifecycle.

Let's take a look at the lifecycle of the database system:

The lifecycle of the database system is divided into five stages:Planning, demand analysis, design, implementation, operation and maintenance. The design phase includes,Concept, logic, physicsThree aspects of the design.

The content in the next two sections is mainly for the design phaseConcept DesignAndLogic DesignAndTransition from conceptual design to logical design.

I. Concept Design (ER Model)

Chapter 1 describes four models of data abstraction: Conceptual Data Model, Logical Data Model, external data model, and internal data model. Therefore, in this chapter, we also mentioned the concepts and logic.

Chapter 1 describes how to create a conceptual model based on user requirements. Our ER model is widely used in conceptual model design. Since it is a design method, we will explore how to use it.

1. The ER model contains two elements: entity and link. However, we should not forget that it also contains the attribute elements of both the entity and the link.
(1) Entity:Object: object; object set: Class; object type: object definition.
(2) Contact:Contact: the relationship between entities; contact set: the set of the same type of contact; contact type: the definition of the contact. (3) Attribute category: Simple attributes and composite attributes:Parent attributes can be divided into sub-attributes based on whether they can be decomposed into sub-attributes. The sub-attributes are dependent on each other. It can be decomposed into compound attributes, but not simple attributes. Single-value and multi-value attributes:Based on the number of values, the attribute of multi-value can be set to multi-value attribute, and the attribute of Single-value can be set to single-value attribute. Storage attributes and derived attributes:Divided by whether it is necessary to store. There is no need to store derived attributes, and it is necessary to store stored attributes. The attribute that can be null:When there are null values in the database, there may be many situations, which are difficult to process. We need to analyze all the situations.
2. ER model operation and design (1) ER model operation: Includes merging, splitting, adding, deleting, and other operations. (2) The design of the ER model follows one principle: first local, then global, and then optimization.
Ii. Logical design (relational model) the logical model is based on the database implementation point of view, modeling data. The relational model is a data model that uses key codes to represent links between entities.
1. terms:Attribute, link mode, tuples, links, and attribute values. The entire orders table is in relational mode, the attribute is a field in the orders table, the attribute value is a field value, and the tuples represent a record, and the link represents a set of multiple records.

2. Integrity rules(1) entity: The primary key attribute cannot be null. (2) Reference: the reference of non-existent entities is not allowed. (3) User-Defined: Set integrity rules for specific constraints.
Iii. Conceptual Design --> logical design 1. AlgorithmsThis section describes how to convert object type and contact type to link mode. In fact, they are all the same. attributes, identifiers, and so on are converted into keys. 2. Design Steps(1) Export the initial link set: Global ER Model --> relational mode (2) Normalization: reduce exceptions in relational mode (3) mode evaluation: check whether the function and performance meet user requirements. (4) pattern correction: Modify the evaluation result. (5) design sub-pattern: design the local logical structure.
4. Let's take a look at this map and recall the content according to the map. I think this book is actually very simple. Although Web weaving is not very good, they are trying to make these knowledge their own. In the course of learning, I realized many of my own shortcomings, such as weak web weaving and low learning efficiency. Now we are doing these things one by one. Since the use of the tomato template, the efficiency has improved a lot and learning is not as objective as it was before. Thank you! Stick to the tomato template to make your efficiency more and more efficient! Come On!

Database optimization (ER Model Design)

If you are the boss of your company, this design can quickly query the required information. However, if you manage it yourself, I think, combine the "Contact" and "Contact Unit" into a table and change the field of the contact unit to the contact. You can use SQL to query the contact unit or other fields separately; the inventory information and price information are also merged into a table. The table on the left is almost the same, but you can adjust some fields by yourself to check your situation, hope you can help ''''''

Database System principles relational data models are converted into ER Models

Based on the relational database model, the ER diagram contains three entities and one link:
Entity 1 --- a driver, whose attributes are the driver's license number, name, address, phone number, and zip code. The driver's license number is the main code.
Entity 2 --- vehicle, attribute: vehicle license plate number, model, production date, manufacturing plant, where vehicle license plate number is the main code
Entity 3 --- penalty method, attribute: penalty number, warning, penalty, temporarily detained driving license, where penalty number is the main code
Contact 1 --- violation. The unique property violation number is the primary code, which is connected to the preceding three entities in multiple ways.