Go to database application basics series-Chapter 2 database Basics

Document directory

• 1.5.1 View
• 1.5.3 triggers and stored procedures
• 1.5.4 Constraints
• 1.5.5 Database Transaction Processing
• 1.5.6 index and primary key

From http://www.cnblogs.com/zhenyulu/articles/204227.html

Chapter 2 database basics 1st a superficial Definition

What is a database? This is a difficult question to answer. Classic textbooks often start from information and data until the database. Here I want to give an inaccurate and superficial definition (this is also the earliest FOXBASE era, most people know about the database ):

[Definition]: (1) a database is a two-dimensional table, which consists of a table header (field) and table content (record. (2) The operations created on the table mainly include: search, insert, delete, and update.

This definition is very different from the existing database, but it does not affect the beginning of this part. From the above definition, we can see that the tables that record information in the database are inseparable from the operations created on the tables. In addition, there are four common database operations: search, insert, delete, and update.

1.2 exceptions

Driven by this original concept, many people started the database design process. Let's look at this example:

[Requirement]: build a database to describe the students in school. The following attributes must be recorded for the students: Student ID, name, age, and department.

Many people will think this is too simple. In the guidance of the above database concepts, we can easily give the following designs:

Table 1-1 Initial Database Design

Student ID	Name	Age	Department
1	Zhang San	20	Management System
2	Li Si	22	Mechanical System
3	Wang Wu	21	Management System
4	Zhao Liu	23	Automation

This design records the student ID, name, age, and department attributes, and supports searching, inserting, deleting, and updating. But is this a perfect design? Before the discussion, let's answer a few questions (although many may sneer at the following questions ).

Question 1: How many departments does the school have? (Answer: 3)

Question 2: How many students are there in the Department of Management? (Answer: 2)

Every time I talk about this, there are always a lot of students who don't care about it. When I ask, "How do you know? "Sometimes, many people just say" what you see. "In fact, these two questions can be clearly explained without" looking at them. The best answer should be "counting" (dizzy !). The answer to question 1 is to get the number of remaining rows by removing the duplicate rows in the department column. The answer to question 2 is to calculate the number of rows in the Department of Management. Well, let's put these two solutions here for later reference.

Let's take a look at several operations created on the table. The search has already been done. We will not mention them here. Let's look at the insert, delete, and update operations. The following requirements are available:

1. The State just approved to allow schools to open a new "Art Department". However, art students will not be recruited until two months later.

2. After Li Si graduated, he is no longer a "student at school". Delete the student.

3. The Department of Economics and Management should be renamed as the "School of Economics and Management ".

We have already encountered "insertion exception", "deletion exception", and "Update exception. How to insert a system without any students? This is a dilemma. Because there is an art department in the school, we have to insert a row to retrieve 4 when we retrieve "several departments in the school". The Department field of the row is recorded as "art", but there are no students in the art department, therefore, we have to leave the student ID, name, and age field blank (as shown in Table 1 2 ).

Table 1-2 insert an Art Series

Student ID	Name	Age	Department
1	Zhang San	20	Management System
2	Li Si	22	Mechanical System
3	Wang Wu	21	Management System
4	Zhao Liu	23	Automation
(Null)	(Null)	(Null)	Art Department

If I ask "several students in the art department" again, I am afraid some may not look so nice. In this case, the number of rows in the art department cannot be accurate. Therefore, the logic for solving the problem must also change: if the number of students is 1, determine whether the student ID, name, and age are empty. If the number is null, It is 0; otherwise, it is 1. Do you think this retrieval is so simple?

Let's take a look at the deletion operation. If you delete the four lines, you will be surprised to find that the mechanical system is gone! Therefore, you have to modify the deletion logic. If only the last record is left in a department, you cannot delete it. Instead, do you want to clear the student ID, name, and age fields?

The update operation also seems to have some problems. In the above design, the rename of the management system requires two rows of data to be modified. Assume that the first row has just been modified and the next row is about to be modified. If the power is down and the machine crashes, the machine cannot run normally. How many systems do you have after the next boot?

1.3 resolve exceptions

Is there any solution to the above problem? Yes! The database standardization theory provides our solutions (for details about the first paradigm, the second paradigm, and the third paradigm, refer to database principles), that is, "splitting ". The preceding table can be split into the following two tables to relieve the exception:

Table 1-3 student database

Student ID	Name	Age	Don't code
1	Zhang San	20	1
2	Li Si	22	2
3	Wang Wu	21	1
4	Zhao Liu	23	3

Table 1-4 Database

Don't code	Department
1	Management System
2	Mechanical System
3	Automation

The two tables are associated by the alias and retain the original information. However, this design eliminates the exceptions mentioned above. You can manually re-execute the search, insert, delete, and update operations under the current design to see if any exceptions have occurred?

1.4 conflict between data expression and Cognition

From the above example, we can see that in order to eliminate the insertion, deletion, and update exceptions during database operations, we must use the database standardization theory to standardize the database design. But if the principal wants you to provide a student list, what should you provide to the principal? Undoubtedly, what the principal wants to see most is table 1-1, rather than table 1-3 and Table 1-4. Therefore, this leads to a new problem: the user of the database and the designer of the database tend to have different perceptions of the data. Database users want to see intuitive and easy-to-understand data. Database designers want to design solutions that are easy to modify, expand, and develop systems. Therefore, the real database design must solve the conflicts between the two. Modern databases provide numerous solutions to this problem (as described in 1.1.5 ). Let's look at another example of cognitive conflict.

How can we use two-dimensional tables to describe the data in a tree structure (like folders in Windows) through a database? Of course, users want to see a tree, and database designers are faced with the problem of how to convert the tree into two-dimensional tables. We can achieve this through the following ing relationships:

 

Figure 1-1 tree structure to be expressed

Table 1-5 use a two-dimensional table to express the Tree Structure

ID	Parentid	Fodername
1	1	Program Files
2	1	Ac3filter
3	2	PIC
4	1	ACD Systems
5	4	ACDSee
6	4	Fotocanvas
7	4	Fotoslate

 

We can see that table 1-5 uses the ID field and parentid field to describe the parent-child relationship between folders, and then records the contents of the tree structure through a two-dimensional table.

In short, data cognitive conflicts are inevitable. Through various methods, we can always balance database users with database designers on data presentation issues. We can solve this cognitive conflict in at least two ways. On the one hand, we can use the functions provided by the existing database (such as views, see 1.1.5) to solve this problem, on the other hand, we can design a dedicated data structure to solve (for example, the above tree structure record problem ).

1.5 What should a database look like?

Since the database definition given in 1.1.1 has a big problem, What should a real database be like? What services does the database provide for us? What are the forms of these services?

To clarify this, let's talk about it from the example of "students in school. The above analysis shows that in order to eliminate various exceptions, we split the original design into two parts by means of sharding, and the two tables are associated by code. Although the exception does not exist, it also brings a series of troubles.

1.5.1 View

First, the cognitive contradiction in data expression makes the principal have to process the data before obtaining the report, and then "Stitch" the two tables. If the Office of Academic Affairs wants to know the number of students in each department, another data "stitching" approach is required. In order to make the public interface no longer difficult to adjust, the concept of "View" is introduced in the database, allowing users to observe data from different angles and get their desired results. "Looking at the mountain side as the peak", the Mountain (physical design of the database) will never change. We can see different data representations from different perspectives (Views. This is a very important role of the view. Of course, the functions provided by the view are far more than that. As an initial understanding, we should first talk about this degree.

The three-level model and two-level image mentioned in database principles are actually about this layer of relationship. Let's move away from the internal mode (physical implementation) in the third-level mode, single-view external mode and mode. There is only one mode, that is, the table designed by the database designer, and there can be multiple external modes. Each external mode can be understood as a "View ", if you want to see it, you can see it (1-2 ). In this way, the "independence" of data is also realized. The database designer can design the logical structure of the database and separate the design scheme from different views.

 

Figure 1-2 solving cognitive conflicts through views

1.5.2 integrity constraints

However, things are not as smooth as expected. Database designers often fall into the trap of "data inconsistency" after eliminating the troubles of various exceptions and data cognitive conflicts. Database designers will find that data insertion, deletion, and update operations are not as free as they think. They must always be careful to avoid data inconsistency.

For example, an irresponsible data entry person may input the following two records into the database: A department table (system number: 3, Department: Art); a student table (student ID: 4, name: and 2, age 25, Department number: 9 ).

Here, the newly inserted Art series and automation series have the same system number, and the student table and the Department table are associated by this system number field, so the problem arises, we cannot normally retrieve the number of students in the art department, or determine the number of students whose number is 3. Therefore, make sure that the system number in the department table uniquely identifies a row of records. This field is called a "primary key" (not strictly or accurately defined, but we can understand it for the time being. For detailed definitions, refer to database principles). The existence of a primary key is used to ensure "entity integrity". The value must be null: (1) NULL; (2) repeated values are not allowed. Therefore, the database needs to provide certain restrictive policies to prevent violations of "entity integrity. Different databases implement different methods. in Visual Foxpro, we can create a primary index or candidate index on this field. In SQL Server, we can add unique constraints for indexes.

Let's take a look at the inserted student record. We entered the number 9 for the student's system number field, and there was no department with the number 9 in the system table, the student was "Homeless. In order to prevent similar incidents, the database must provide some mechanisms to ensure that the system numbers in the student table have some corresponding relationships with those in the department table to prevent inconsistency. This mechanism relies on the implementation of "reference integrity. The system number (foreign key) in the student table (slave table) must refer to the system number (primary key) in the Department table (master table ).

The master table and slave table mentioned in the reference integrity are not necessarily two different tables. They can also be the same table. For example, table 1-6:

Table 1-6 integrity of references in a table

Student ID	Name	Shift Leader student ID
1	Zhang San	2
2	Li Si	2
3	Wang Wu	2
4	Zhao Liu	2

 

The student ID and student ID constitute the relationship between foreign keys and primary keys. The student ID of the class leader can be set to either of the following two conditions: (1) the class leader has not been selected. (2) It is a value in the student ID field, indicating that the class leader is taken from the students in this class.

In addition, In the 1.1.4 data presentation and cognitive conflicts section, we introduced how to use a two-dimensional table to express a tree structure, and carefully observe the table 1-5, the ID field and parentid field also constitute the relationship between the primary key and the foreign key. The value of parentid must come from the ID column.

In the actual application process, the integrity of reference can often be abstracted into several principles. Here we provide the integrity constraints of reference in Visual Foxpro for reference (take the student and department table as an example ):

• Delete rule:

Cascade deletion: if you delete a system administrator, the system automatically deletes the students in the system administrator.

Restrict deletion: if any student in the student table is managed by the Department, you are prohibited from deleting the department in the table.

• Update rules

Cascade update: if the number of the department administrator is changed, the number of the corresponding department administrator in the student table is also changed.

Restriction update: if any student in the student table is managed by the Department, you are prohibited from changing the Department number in the other table.

• Insert rule

Insert restriction: It is prohibited to insert a record into the student table. The system number of this record is not in the department table.

In addition to understanding the constraints of integrity, we should also pay attention to the order of data processing after the application references integrity. We still use the student table and department table as examples. Assume that you have created a reference integrity rule for "restrict deletion, restrict update, and restrict insertion" between the two tables, when deleting the Department of Management and all students, you must first Delete the student table, then delete the Department table, and the updates are the same. When inserting the Department of art and its students, you must first Insert the Department table and then insert the student table. The Operation Sequence of the two tables is different. Complete steps 1, 2, 3, and 4 of [integrity settings and data operation sequence of Visual FoxPro In experiment 1-1] for better understanding.

 

Experiment 1-1 configure integrity and data operation sequence in Visual Foxpro

1.5.3 triggers and stored procedures

In experiment 1-1, we achieved the ability to set the integrity of the reference in Visual Foxpro. What is the magic force that makes cascading deletion possible? In Visual Foxpro, triggers and stored procedures are implemented (different databases use different technologies ). Triggers are usually divided into insert triggers, delete triggers, and update triggers. By setting these triggers, you can ensure that some code is automatically executed when the database table is inserted, deleted, or updated. In Visual Foxpro, these codes are stored in the database. They are called stored procedures ". For the time being, we can understand the stored procedure as follows: it is a program code that is stored in the database and pre-compiled and can be directly called by the outside. With the gradual deepening of our content, our understanding of the stored procedure will be further deepened. Complete Step 5 in [tutorial 1-1 visual foxpro's reference integrity settings and data operation sequence.

1.5.4 Constraints

We have learned the entity integrity constraints and the reference integrity constraints in the integrity constraints. There is also a user-defined integrity constraint, as the name suggests, that is, the User-Defined integrity constraints. Generally, two types of user-defined integrity constraints are provided in databases: (1) field-level integrity constraints and (2) record-level integrity constraints.

During the design of database tables, we can define the field type, field length, and number of decimal places, but this is not enough for some applications. For example, a field "age ". We usually use an integer as the data type of this field. However, "-5" and "1000" are all integers, but obviously they are not age. How can we ensure the integrity of the field? This is achieved by field-level integrity constraints. With field integrity constraints, we can set that the data in the age field must be an integer between 0 and 150 to ensure that the age data is correct. Please complete [Experiment 1-2 implementing field-level integrity constraints in Visual Foxpro] to deepen your understanding of field integrity.

 

Experiment 1-2 implement field-level integrity constraints in Visual Foxpro

Field integrity only checks the data integrity of a field. If multiple fields are correlated, the field-level constraints are insufficient. For example, a table contains two fields: "Age" and "length of service". The age field constraint is 0 ~ An integer between 150 and the length of service constraints is 0 ~ An integer between 60. We can insert a record. The age is 10 and the length of service is 30. Although the data does not violate the constraints of a single field, how can a 10-year-old have a working age of 30 years? To ensure data integrity and consistency between fields, we have to rely on record-level integrity constraints. By setting the constraint "Length of Service <= age-18", the data consistency between the two fields can be ensured. Please complete [Experiment 1-3 implementing record-level integrity constraints in Visual Foxpro] to deepen your understanding of field integrity.

 

Lab 1-3 implement record-level integrity constraints in Visual Foxpro

1.5.5 Database Transaction Processing

As mentioned above, some users may have long been confused about the functions that the database should have. "In terms of reference integrity, there is a concept called cascade update, that is, if you change the code, the corresponding department code in the student table is also automatically changed. The problem is that if the power is cut to half, isn't there a data inconsistency problem? Is there another update exception? ".

Indeed, if there is no effective mechanism to prevent similar incidents, the database will still face great threats. The existing databases support transactions. What is a transaction? To put it simply, it is a mechanism to ensure "both success and success, and failure upon any failure. A transaction usually includes three actions: Begin transaction, commit transaction, and rollback ). All Database modifications that occur from the start of the transaction to the commit of the transaction are either successful at the same time (committed and solidified in the database) or fail at the same time, and the original status (rollback, the database returns to the status at the beginning of the transaction ). A typical transaction program may be as follows:

 

Tx = begintransaction ();
Try
{
// Modify the Department table
// Modify the student table
TX. Commit ();
}
Catch
{
TX. rollback ();
}

When begintransaction is executed, we can assume that the database is taken a snapshot to record the current status. Then, update the system table and student table. If all the data can be correctly executed, submit the modification, and the snapshot will be useless. However, if an update exception occurs in any of the tables, the program will fall into the catch segment. Here we will roll back the database and restore it to the status at the time of snapshot.

For more details about transactions and data updates, refer to Chapter 2nd consistency of concurrent operations.

1.5.6 index and primary key

Indexing is a very important concept in databases. It serves the same purpose as the word-checking table before Xinhua Dictionary, which can improve our retrieval efficiency. The primary key is used to uniquely represent a row in the table. Once you know a primary key, you can locate a unique record based on it, just like the ID card number of a table. (To be improved)