Chapter 1 Database Design

1.1 why do we need to standardize the database design in the first stage? Based on business needs, we directly create databases, create tables, insert test data, and then query data, why do we need to emphasize the need to create a database and a table first? The reason is simple. Just like building a building, if you build a Mouch or a simple bungalow, you will pay for it.

1.1 why do we need to standardize the database design in the first stage? Based on business needs, we directly create databases, create tables, insert test data, and then query data, why do we need to emphasize the need to create a database and a table first? The reason is simple. Just like building a building, if you build a Mouch or a simple bungalow, you will pay for it.

1.1 why should we design a standardized database?

In the first stage, based on business needs, we directly create databases, create tables, insert test data, and then query data. Why do we need to emphasize design, create databases, and create tables? The reason is very simple. Just like building a building, if you build a Mouch or a simple bungalow, will you spend money to design the house drawings? No doubt, no one will ask. However, if a real estate developer develops a real estate project and builds a residential area with multiple buildings, will he ask someone to design the construction drawings? The answer is yes. Not only do developers consider design and construction drawings, but even many professional buyers may ask developers to present design drawings when looking at the house.

Similarly, in actual project development, if the system has a large amount of data storage, many tables are designed, and the relationship between tables is complex, we need to first consider the standard database design, and then create a specific database and table. Whether it is creating a dynamic website or a desktop window application, the importance of database design is self-evident. If the design is not proper, the query will be very difficult and the program performance will be affected. Whether you are using SQL Server or Oracle databases, the standardized database design and U.S. servers can make your program code more readable and scalable, this will also improve the application performance of the project.

1. What is database design?

Database Design refers to the process of planning and structuring the data objects in the database and the relationships between these data objects.

2. database design is very important

The types of data structures created in the database and the complex relationships between data objects are important factors that determine the efficiency of the database system.

Poor Database Design.

(1) efficiency.

(2) There are many problems during data update and retrieval.

Good database design performance is as follows.

(1) high efficiency.

(2) facilitate further expansion.

(3) make application development easier.

1.2 database design steps

After graduation project development, we have a general understanding of project development, project development involves several stages: requirement analysis, outline design, detailed design, code writing, running testing, and packaging and release. Next, we will focus on the database design process in each stage.

Demand analysis stage: analyzes customers' business and data processing needs.

Summary design phase: drawing the E-R model diagram of the database for communication within the project team, between the designer and the customer to confirm the correctness and integrity of the requirement information.

Detailed design phase: the E-R diagram is converted into multiple tables, logical design, confirm the main and Foreign keys of each table, and apply the three paradigm of Database Design for review. After discussion and confirmation by the project team, you also need to select a specific database (such as SQL Server or Oracle) for physical implementation based on the technical implementation of the project, the team's development capabilities, and the funding source of the project, this includes creating databases, tables, and stored procedures that we will learn later. After the creation, start the code writing stage and develop the front-end application.

Now, we will discuss the design steps of the background database in the requirement analysis stage.

The focus of the demand analysis stage is to investigate, collect, and analyze the data requirements, processing requirements, and security and integrity requirements of the customer's business.

Common requirements survey methods include internships in the customer's company, organizing surveys, inviting special personnel to introduce and design surveys, and asking users to fill in and view business-related data records.

Common requirements analysis methods include investigating the customer's company address, business requirements of each department, assisting the customer in analyzing various business requirements of the system, and determining the boundaries of the new system.

The size and complexity of a physical database can be analyzed by referring to the following basic steps.

(1) collect information.

(2) identify the object.

(3) Identify the details to be stored for each object.

(4) identify the relationship between objects.

1. Collect information

Before creating a database, you must fully understand the tasks and functions required by the database. Simply put, we need to know what information (data) The database needs to store and what functions to implement.

2. Identify an object (entity)

After collecting requirement information, you must identify the key objects or entities to be managed by the database. Objects can be tangible things, such as people or products, or intangible things, such as commercial transactions, company departments, or pay-as-you-go periods. After these objects are identified in the system, the objects related to them will be organized clearly.

3. Identify the details (attributes) to be stored for each object)

After the primary objects in the database are identified as candidate objects in the table, the next step is to identify the details of each object storage, also known as the attributes of this object. These attributes are grouped into columns in the table. To put it simply, the sub-member information contained in each object needs to be subdivided.

4. Identify the relationship between objects (entities)

A relational database has a very powerful function that can be used to associate information related to various projects in the database. Different types of information can be stored separately, but the database engine can combine the data as needed. During the design process, to identify the relationship between objects, You need to analyze the database tables, determine how these tables are logically related, and then add the relational columns to establish a connection between tables.

1.3 draw a E-R (entity-link) diagram

After solving the customer's business and data processing requirements in the demand analysis stage, we entered the outline design stage. We need to communicate with other members of the project team and our customers, discuss whether the database design meets the customer's business and data processing needs. And the mechanical industry needs mechanical drawing, construction industry needs construction samples, our database design also needs graphical expression --- E-R (Entity-Relationship) Entity Relationship diagram, it includes some graphical symbols with specific meanings.

1.3.1 entity-Relational Model

1. Entity

An object refers to an object in the real world that has the characteristics or attributes that distinguish other things and are associated with other entities. For example, in the BBS forum system, users (such as Zhang San, Li Si, and Wang Wu), posts, and sections (such as animation sections and emotional heaven and earth sections) are generally nouns, it corresponds to a row of data in our table. For example, the entity of user Zhang San corresponds to the row of data of user Zhang San in user table, including his password, date of birth, email, and other information. Strictly speaking, an entity refers to the specific data of a row in a table. However, during development, the entire table is often called an entity.

2. Attributes

Attributes can be understood as the features of objects. For example, "user" has attributes such as nickname, birth date, and email. Attribute corresponds to the column in the table.

3. Relationship

A link is a link between two or more entities.

It is the relationship between the user entity and the Forum entity. An object is represented by square. An object is generally a noun, and an attribute is identified by an ellipse. It is also a noun. A link is identified by a diamond. It is generally a verb.

　　

4. ing Base

The ing base number indicates the number of other entities that can be associated with the object through the link. For the binary relationship between object set X and Y, the ing base must be one of the following bases.