Database design (e-r diagram, Database Model diagram, three paradigms)

I. Concept of database design

Database design is the process of planning and structuring the relationship between the data entities in the database and the data entities.

Two. Importance of database design

If a database does not have a good design, then after the database is complete his disadvantage is:

1. Efficiency will be very low

2 There are many problems when updating and retrieving data.

On the contrary, a database has been well-planned, with good design, then his advantages are:

1. The efficiency will be very high.

2. Facilitate further expansion.

3. Making applications easier to develop.

Three. Steps to design a database

1. Requirements Analysis phase: Analyze the customer's business and data processing needs.

2. Outline design phase: He is mainly the e-r diagram of the drawing database.

3. Detailed design phase: application database of the three major paradigms to audit the structure of the database.

Summary: In the system analysis of the database, all the following 4 points refer to the basic steps.

collect information.

A. Identify the entity.

A. identify the details that each entity needs to store .

the relationship between entities is identified .

Four. Learn to draw E-r diagram

Drawing E-r diagram First to understand what is an entity, what is an attribute, and what is a contact.

1. The first entity refers to an entity in the real world that has a characteristic or attribute that distinguishes other things from other entities, and for a table in a database, an entity refers to a row of specific data in a table, but in development we often refer to the entire table as an entity.

2. Attributes can be understood as the characteristics of an entity, which refers to a column in a table for a table in the database.

3. A contact is an association between two or more entities.

4. Entity Relationship Diagram:

Example: Hotel Management System E-R chart:

Five. Map cardinality

1. Single-to-one: an entity in X is most aligned with an entity in Y, and an entity in Y is associated with at most one entity in X.

Eg: A person has only one ID card.

2. One-to-many: an entity in X can be associated with any number of entities in Y; An entity in Y is associated with at most one entity in X.

Eg: There are many students in a class.

3. Many-to-one: an entity in X is associated with at most one entity in Y; An entity in Y can be associated with any number of entities in X.

Eg: The relationship between guest and room, one guest can only stay in one room.

4. Many-to-many: an entity in X can be associated with any number of entities in Y, and vice versa.

Eg: The relationship between students and the curriculum, a student can have multiple courses, a course can correspond to more than one student.

Six. Drawing the Database Model diagram

Take the hotel management system as an example:

Seven. Database Normalization

If the database is not designed appropriately, it may be easier to query the database, but sometimes it can cause problems, the main problems are as follows:

1. Duplication of information (which can result in wasted storage space and some other problems).

2. Update exceptions (redundant information not only wastes space, but also increases the difficulty of updating).

3. Inserting an exception

4. Remove the exception (in some cases, useful information may be lost when a row is deleted).

Eight. Three main paradigms

1. First Paradigm:

The goal is to ensure the atomicity of each column. If each column is the smallest unit of data that cannot be re-divided, the first normal form is satisfied.

2. Second paradigm:

The second paradigm is based on the first paradigm, with the goal of ensuring that each column in the table is related to the primary key, and if a relationship satisfies the first normal form, and other columns other than the primary key are dependent on the primary key. The second paradigm is satisfied.

3. The third paradigm:

The third paradigm is based on the second paradigm, and the goal of the third paradigm is to ensure that each column is directly related to the primary key column, rather than indirectly. If a relationship satisfies the second normal form, and the columns other than the primary key can depend on the primary key column, there is no dependency between the columns and columns, and the third paradigm is met.

Nine. The relationship between normative and performance

In order to meet the three paradigms, our data operation performance will be affected by the corresponding, so in the actual database design, we should consider three paradigms, avoid data redundancy and various data manipulation anomalies; to take into account data access performance, sometimes, in order to reduce the inter-table connection, improve the database access performance, Allow the appropriate data redundancy column, which is probably the most appropriate database design scenario.

Database design (e-r diagram, Database Model diagram, three paradigms)