Database Design 20, Conceptual Database Design

Khan: To make a database, you have to meet at least 3 paradigms.

To design a concept with the E-R data model, you must first identify entities, connections, and attributes based on the requirements description.

The conceptual design of the database using the E-R method can be divided into three steps: firstly, the local E-R diagram is designed; Then, the local E-R diagrams were merged, and the possible conflicts were resolved to obtain preliminary E-R diagrams. Finally, the preliminary E-R diagram is modified and reconstructed to eliminate the redundant parts and obtain the final global E-R diagram, that is, the conceptual model. Add a conceptual design phase between requirements analysis and logical design, so that the designer only looks at the data and deals with the requirements and constraints from the user's point of view, resulting in a conceptual model that reflects the user's point of view.

There are three benefits to this:

1) The database design is not as simple as the tasks at each stage, and the complexity of the design is reduced, which is convenient for organization and management.

2) The generalized schema is not limited by a specific DBMS and is independent of the storage arrangement, so it is more stable than the schema obtained by logical design.

3) The conceptual model does not contain the technical details attached to the specific DBMS, which is easier for the user to understand and thus accurately reflects the user. Information needs.

In the preliminary E-R diagram, there may be some redundant data and redundant connections between entities. The so-called redundant data refers to the data that can be derived from the basic data, and the redundant connections are the contacts that can be exported by other contacts. Redundant data and redundant connections can easily undermine the integrity of the database and increase the difficulty of database maintenance, and should be eliminated.

The preliminary e-r diagram after the redundancy is eliminated is called the basic e-r diagram.

However, not all redundant data and redundant connections must be eliminated, and sometimes redundant information has to be used at the expense of redundant information in order to improve efficiency. Therefore, when designing the conceptual structure of the database, those redundant information must be eliminated, and those redundant information that is allowed to exist need to be determined according to the overall needs of the user. If some redundant data is artificially retained, the description of the data association in the data dictionary should be used as a completeness constraint.

What makes good database design?

There are a few principles that guide the database design process. The first principle is that duplicate information (also known as redundant data) is bad because duplicate information wastes space and increases the likelihood of errors and inconsistencies. The second principle is that the correctness and completeness of the information is very important.

If the database contains incorrect information, any report that pulls information from the database will also contain incorrect information. As a result, any decisions made based on these reports will provide error information.

So, a good database design should look like this:

Divide information into topic-based tables to reduce redundant data.

Provide AESS with the information needed to join the information in the table as needed.

to help support and ensure the accuracy and completeness of information.

It can meet the needs of data processing and reporting.

Design process. The design process consists of the following steps:

Determine what the database is for: This can help with the preparation for the other steps.

Find and organize the information you need: Gather a variety of information you might want to record in your database, such as product names and order numbers.

Information into tables: Divide information items into primary entities or topics, such as Products or Orders. Each topic constitutes a table.

Close the columns for information item import to determine what information you want to store in each table. Each item becomes a field and appears as a column in the table. For example, the Employees table might contain fields such as Last Name and Date Hired.

Specify Primary Key: Select the primary key for each table. The primary key is a column that uniquely identifies each row. For example, the primary key can be either "Product ID" or "Order ID".

Set table relationships: Review each table and determine how the data in each table relates to each other. Add fields to the table or create new tables as needed to clearly express these relationships.

Optimize your design: Analyze your design for errors. Create a table and add a few sample data records. Determine if you can get the desired results from the table. Adjust the design as needed.

Apply normalization rules: Apply data normalization rules to determine if the table is structured correctly. Make adjustments to the table as needed.