Database design-related materials

Source: Internet
Author: User
The role of database integrity is critical to the database application system. Its role is mainly reflected in the following aspects: 1. database integrity constraints prevent legal users from adding non-semantic data to the database when using the database. 2 mechanism to implement business rules, easy to define, easy to understand, and can reduce the complexity of applications

The role of database integrity is critical to the database application system. Its role is mainly reflected in the following aspects: 1. database integrity constraints prevent legal users from adding non-semantic data to the database when using the database. 2 mechanism to implement business rules, easy to define, easy to understand, and can reduce the complexity of applications

Functions of database integrity

Database integrity is critical to the database application system. Its role is mainly reflected in the following aspects:

1. database integrity constraints prevent legal users from adding non-semantic data to the database when using the database.

2 mechanism to implement business rules, easy to define, easy to understand, and can reduce the complexity of the application, improve the operational efficiency of the application. At the same time, the mechanism is centrally managed, so it is easier to achieve database integrity than applications.

3

4. In the functional test of application software, perfect database integrity helps to Detect application software errors as soon as possible.

Database integrity constraints can be divided into hierarchical static constraints, link-level static constraints, column-level dynamic constraints, meta-group-level dynamic constraints, and link-level dynamic constraints. Dynamic constraints are usually implemented by application software. The database integrity supported by different DBMS is basically the same.

Edit the integrity of the link model

Relational integrity is used to ensure the correctness of data in the database. When updating, inserting, or deleting data, the system must check the data integrity and its constraints, that is, the integrity rules of the relational model. There are four types of integrity constraints in a relational model: entity integrity, domain integrity, referential integrity, and user-defined integrity. entity integrity and referential integrity constraints are called the immutability of the relationship.

1. entity integrity

The integrity rules of relational databases are an important part of database design. Most relational database management systems

(

(

2. Domain integrity

Domain integrity (

3. Integrity of reference

Referential Integrity is an inter-Table rule. When updating, inserting, or deleting records for tables with permanent relationships, if you change only one of them, data integrity will be affected. For example, if the corresponding records of the sub-table are not deleted after a record of the parent table is deleted, these records are called isolated records. Integrity of data between update, insert, or delete tables is collectively referred to as integrity of reference. In general, there is a certain relationship between entities in objective reality. In a relational model, the associations between entities and entities are described as relations. Therefore, during the operation, there may be associations and references between links.

In relational databases, links are implemented through public attributes. This public attribute is often the primary key of a table and the foreign key of another table. The integrity of the reference is reflected in two aspects: the relationship between the table and the table is realized, the foreign key value must be the valid value of the primary key of the other table, or

Referential Integrity requirements: If attribute group F is the primary key of relational model R1 and F is the foreign key of relational model R2, the value of F can only be null or equal to a primary key value in the R1 relationship.

R1R2

Note: in practice, the foreign key does not necessarily have the same name as the corresponding primary key. The foreign key is commonly marked with a lower curve.

4. User-Defined integrity

User-defined Integrity

Edit the database integrity design phase

A good database integrity design first needs to determine the business rules to be implemented through database integrity constraints in the requirement analysis phase, and then, on the basis of a full understanding of specific mechanisms, follow the database design methods and application software design methods according to the system architecture and performance requirements, and reasonably select the implementation method of each business rule. Finally, perform the test carefully, eliminate implicit constraints conflicts and performance issues. DBMS-based database integrity design is divided into the following stages:

1. Demand analysis stage

Through the joint efforts of system analysts, database analysts, and users, determine the objects to be included in the system model, such as departments, employees, managers, and various business rules in the personnel and wage management system.

After searching for business rules, determine the business rules to be used as database integrity and classify the business rules. The integrity design as part of the database mode is carried out in the following process. The database integrity design implemented by application software is carried out in accordance with the software engineering method.

2. Conceptual Structure Design Stage

In the conceptual structure design phase, the results of requirement analysis are converted into a separate stage (the stage starts the substantive stage of database integrity design, because the physical relationships in this phase will be converted into Entity integrity constraints and reference integrity constraints in the logical structure design phase, the main work of the design will be completed in the logical structure design phase.

3. Logical Structure Design Stage

This stage is to convert the conceptual structure to

At the end of the logical structure design phase, the integrity design as part of the database model is basically completed. There may be several implementation methods for each type of business rule. You should select one that has the least impact on the database performance, and sometimes it needs to be determined by actual tests.

Edit the database integrity design principles for this section

Some basic principles need to be grasped when implementing database integrity design:

1. Determine the implementation system level and method based on the type of database integrity constraints, and consider the impact on system performance in advance. Generally, static constraints should be included in the database mode as much as possible, while dynamic constraints are implemented by applications.

2. entity integrity constraints and reference integrity constraints are the most important integrity constraints of relational databases. They should be applied as far as possible without affecting critical system performance. It is worthwhile to exchange a certain amount of time and space for the ease of use of the system.

Three functions: on the one hand, because of the high performance overhead of the trigger, on the other hand, the multi-level trigger of the trigger is difficult to control, prone to errors, when not available, it is best to use the Before statement-Level Trigger.

4

5. perform detailed tests on database integrity according to business rules to eliminate conflicts between implicit integrity constraints and impact on performance as soon as possible.

6. Audit the database integrity constraints.

7. workload of each stage. The work efficiency of good personnel is greatly improved, and it is easy to communicate with users.

What is the erdiagram in the database?

Is the relationship between the table and the table. You can use a tool chart, where the graph has a 1; N, n: 1, N: N relationship diagram to establish the relationship between the table and the table, for example, the relationship between 1: N is:

The student table and the curriculum, in which the curriculum refers to the number of the student table, so the relationship between the student table and the curriculum is one-to-multiple;

The following tools are used to create a database:

World, UML, power;

Table language table relationships are:

Include, associate, generic, and extend

1. Concepts of transactions

To understand

Generally, a transaction follows a specific rule called

The transaction must be atomic (

Atomicity: atomicity ensures that either all updates are executed or nothing happens. Due to the atomicity guarantee in the transaction, developers do not have to write code to handle a successful update, but the other fails.

Consistency: Consistency means that the transaction results keep the system consistent. Data remains valid before the transaction starts, just as at the end of the transaction. Consistency also ensures that the transaction must keep the database in a consistent state. If some operations of the transaction fail, the other parts must return to the original state.

Isolation: multiple users may access the same database at the same time. Isolation ensures that data changes in the transaction cannot be seen outside the transaction before the transaction is completed. And cannot access some intermediate states. if the transaction is terminated, these statuses will not occur.

Persistence: Persistence means consistency even if the system crashes. If the database system crashes, durability must ensure that committed transactions are indeed written to the database.

2.

Transactions are classified into local transactions and distributed transactions. (

Some software packages (such

In a distributed transaction environment, different resource managers need to implement reliable Commit Protocols. The most common implementation is two-phase commit. In two-phase submission, the actual submission is divided into two phases: the first stage includes preparing some required changes for the submission. In this way, the resource manager will communicate with the Transaction Coordinator to inform it that the update preparation is ready and the preparation is executed and submitted, but the actual process is not submitted yet. In the second phase, once all the resource managers inform the Transaction Coordinator that the preparation is ready, the Transaction Coordinator will make all the participants understand that the work is ready and then execute the changes. In two-phase commit, one or more databases can participate in distributed transactions. In fact, any;

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.