The development of database technology and the understanding of SQL database

First, the development of database management technology

Database technology is a technology that studies how to scientifically manage data in order to provide people with shareable, safe and reliable data. Database technology includes two parts: data management and processing.

Human Management Phase

Before the mid 1950s, computers were used primarily for scientific calculations. At this stage, the computer in addition to hardware, there is no software to manage data, data processing method is batch processing. The organization and management of data is done by the programmer by hand, and the management efficiency of this phase is very low, and its characteristics are as follows.

1. Data not saved

This phase of the computer is mainly used for scientific calculations, do not perform other operations on the data, generally do not need to save the data for a long time, but in the calculation of a topic when the data are bulk input, the data after processing does not save the original program and data. When the computer loses power, the calculation results will disappear.

2. Application Management Data

Data needs to be managed by the application itself, and no corresponding software system is responsible for the management of the data. Programmers not only need to specify the logical structure of the data, but also to design the physical structure of the data, including the storage structure, access methods and input and output modes, so that the programmer is very heavy burden.

3. Data not shared

A set of data can only correspond to one program, the data is application-oriented. The data of each application is organized, cannot be used and cross-referenced, so there is a lot of redundant data between the program and the program.

4. Data is not independent

Neither the logical structure nor the physical structure of the data is independent. When the logical or physical structure of the data changes, the application must be modified accordingly, thus burdening the programmer with the design and maintenance of the application.

File system phase

From the late the 1950s to the 60 's, hardware has been a disk, drum and other storage devices, software, the operating system has a dedicated data management software, commonly known as the file system. At this time the computer is not only used for scientific calculation, but also for data processing.

1. Advantages of the file system

This phase of data management has the following characteristics.

(1) data can be stored for a long time

Due to the large number of computers used for data processing, data needs to be retained for long periods of time to query, modify, insert, and delete external memory.

(2) File System Management data

Data management by a specialized software or file system, the file system organizes data into separate data files, and uses the management technology of "access by file name and access by record" to modify, insert and delete the data in the file. The file system is structured within the record, but is unstructured in terms of the file as a whole. The process and data are transformed by the access method provided by the file system, which makes the application and data have some independence. Programmers can concentrate on algorithms without having to think too much about physical details. And the change of data in storage is not necessarily reflected in the program, which greatly saves the work of maintenance program.

2. Shortcomings of the file system

Although the file system has the above advantages, but it still has some shortcomings, mainly in the following aspects:

(1) Poor sharing of data, high redundancy

In the file system, data creation, access is still dependent on the application, basically a (or a set of) data files corresponding to an application, that is, the data is still application-oriented. When different applications have part of the same data, they must also establish their own files, and not share the same data, so the redundancy of the data is large, wasting storage space. At the same time, because of the repeated storage and management of the same data, it is easy to cause the inconsistency of data, which makes the data modification and maintenance difficult.

(2) Lack of data independence

Although the data in the file system has some independence, because the data file only stores the data, the application determines the logical structure of the data, the physical structure of the design data, once the logical structure or the physical structure of the data need to change, the application must be modified, or because the change of the language environment needs to modify the application, it will also Causes changes in file data structures. Therefore, the logical independence between the data and the application is not strong. In addition, it is difficult to add new applications to existing data and the system is not easy to expand.

(3) Concurrent access is prone to exceptions

The file system lacks the mechanism to control concurrent operations, so the system allows multiple users to access the data concurrently, but because of the interaction of concurrent update operations, it is easy to cause inconsistent data.

(4) Data security control is difficult to achieve

Data is not centrally managed. The structure, coding, presentation format, naming and output format of the data are not

Easy to standardize and standardize, so its security, integrity is not reliable guarantee, and the file system is difficult to achieve different user access rights of the security constraints.

Database Management Phase

Since the late 1960s, the data object of Computer Management has become larger and wider, the application scope is more and more wide, the data volume has increased sharply, and the requirement of data processing speed and sharing is more and more high. At the same time, disk technology has made important progress, providing material conditions for the development of database technology. Then, a new and advanced data management method is developed: Storing the data in a database, which is managed uniformly by the database management software, and the application accesses the data through the database management software.

1. Data structuring

Data structure is the essential difference between database and file system.

In the file system, there is a structure inside the records of the files that are independent of each other. The simplest form of a traditional file is a collection of equal-length records in the same format.

2. High data sharing, low redundancy, easy to expand

The database system looks at and describes the data from a holistic perspective, and the data is no longer oriented to an application but to the entire system, so data can be shared by multiple users and multiple applications. Data sharing can significantly reduce data redundancy, save storage space, and avoid incompatibility and inconsistencies between data.

The so-called inconsistency of data refers to different copies of the same data value. When using manual management or file system management, data is easily inconsistent when different applications use and modify different copies due to repeated storage. Data sharing in the database reduces inconsistencies caused by data redundancy.

Because the data is oriented to the whole system, it is structured data, which can be used not only by multiple applications, but also easily added to new applications. This makes the database system elastic, easy to expand, can adapt to the requirements of various users. Various subsets of the overall data can be used for different applications, and when the application needs change or increase, the new requirements can be met by simply re-selecting different subsets or adding some data.

3. High data independence

Data independence is a common term in the field of database, including the physical independence of data and the logical independence of data.

Physical independence refers to the fact that the user's application is independent of the data stored in the database on disk. That is, how the data is stored in the database on disk is managed by the DBMS, and the user program does not need to know that the application is only dealing with the logical structure of the data so that when the physical storage of the data changes, the application does not change.

Logical independence refers to the user's application and the logical structure of the database is independent of each other. In other words, the logical structure of the data changes, the user program can be unchanged.

The independent data and program separates the definition of the data from the program, and the data access is also the responsibility of the DBMS, thus simplifying the programming of the application and greatly reducing the maintenance and modification of the application.

4. Unified management and control of data by DBMS

The sharing of the database is concurrent (Concurrency) sharing, that is, multiple users can access the data in the database at the same time, even can access the same data in the database at the same time.

For this reason, the DBMS must also provide data control functions in the following areas.

(1) Security Protection of data

Data security refers to the protection of data, in order to prevent illegal use of data leakage and destruction, so that each user can only be prescribed for certain data in certain ways to use and processing.

(2) Data integrity (Integrity) Check

The integrity of the data refers to the correctness, validity, and compatibility of the data. The integrity check controls the data in a valid range or ensures that the data satisfies a certain relationship.

(3) concurrency (Concurrency) control

When concurrent processes of multiple users access and modify the database at the same time, it is possible to interfere with each other and get the wrong result or destroy the integrity of the database, so it is necessary to control and coordinate the concurrent operation of multi-user.

(4) Database recovery (Recovery)

computer system hardware failure, software failure, operator error and intentional destruction will also affect the correctness of data in the database, and even the loss of some or all of the data in the database. The DBMS must have the ability to recover a database from an error state to a known correct state (also known as a full state or a consistent state), which is the recovery function of the database.

In summary, the database is a long-term storage in the computer organization, a large number of shared data collection. It can be shared by a variety of users with minimal redundancy and high data independence. DBMS unifies the database in the database establishment, the use and the maintenance, guarantees the data integrity and the security, and carries on the concurrency control when the multi-user uses the database simultaneously, after the fault has the system restores.

The emergence of the database system makes the information system shift from the process of processing data to a new phase centered around the shared database. This facilitates the centralized management of data, facilitates the development and maintenance of applications, improves the utilization and compatibility of data, and improves the reliability of decision-making.

Second, the database system

the composition of the database system

Database system refers to the system after the introduction of the database. Generally includes four main parts: database, Database management system, application program and database administrator, database management system is the core

1. Database

A database is a warehouse where data is stored, and the data is stored in a certain format. Database data has three basic features of persistent storage, organization, and sharing

2. Database management System

A database management system is a layer of data management software located between the user and the operating system. How to organize and store data scientifically, how to obtain and maintain data efficiently is done by database management software, which is the core of database system.

3. Application

Applications are applications that are based on databases and database data, and library management systems.

4. Database administrator

The database administrator is responsible for the planning, design, coordination, maintenance and management of the database, and ensures that the database is running correctly and efficiently.

Database management System

The main functions of the database management system are as follows:

1. Data definition

The DBMS provides a data definition language DDL that allows users to define the database's objects through the DDL, define a three-level structure of the database, a two-level image, define the integrity constraints of the database, and so on.

2. Database manipulation functions

DBMS provides a manipulation of the database DML, the user can use DML to the data in the database for the operation of pruning and checking.

3. Database protection function

The DBMS protects the database mainly through the database recovery, the database concurrency control, the database integrity control, the database security control 4 aspects.

4. Storage management of the database

The DBMS's storage management subsystem provides an interface for data and applications in the database, and DBMS statements transform the underlying file system commands to store, retrieve, and update data.

5. Maintenance function of database

The utility program for database maintenance in DBMS mainly includes data loader, backup program, file reorganization program and Performance Monitor program.

6. Data dictionary

The database in the database system that holds the three-level structure definition becomes the data dictionary, and the operation of the database is realized by accessing DD.

Third, the structure of the database system

Three-level mode of the database

External mode

The external mode is also called sub-mode or user mode, which corresponds to the user level. It is a data view of a database that one or several users see, and is a logical representation of the data associated with an application. The outer mode is a subset of the export from the schema that contains the portion of data that is allowed to be used by a particular user in the pattern. The user can describe, define the data record (external mode) corresponding to the user through the external mode description language, or use the data manipulation language,dml to make the data records. The external model reflects the user view of the database.

Mode

a pattern is also called a conceptual or logical pattern, which corresponds to a conceptual level. It is by the Database Designer synthesizes all user's data, constructs the global logical structure according to the unified viewpoint, is the entire data in the database logical structure and the characteristic general description, is all user's public Data View (global view). It is described and defined by data Description LANGUAGE,DDL, which is provided by database management system, and reflects the whole view of database system.

Internal mode

Internal mode, also known as the storage mode, corresponds to the physical level, which is the internal representation or the underlying description of all data in the database, is the logical description of the lowest level of the database, it describes how the data is stored on the storage medium and the physical structure, corresponding to the actual storage on the external storage media database. The inner mode is described and defined by the internal Schema description language. Level Two image feature               

1. External mode/mode image

When the pattern changes (such as adding new relationships, new attributes, changing the data type of the attribute, etc.), the database administrator changes the image of each external mode/pattern so that the external mode remains unchanged. The application is written according to the external mode of the data, so that the application does not have to be modified, which guarantees the logical independence of the data and the program.

2. Mode/Internal mode image

There is only one pattern in the database, and there is only one inner mode, so the pattern/inner mode image is unique, it defines the correspondence between the global logical structure of the database and the storage structure, which is usually contained in the definition description of the conceptual schema.

When the internal schema storage structure of the database has changed (such as selecting another storage structure), the database administrator changes the pattern/internal mode image so that the pattern remains unchanged so that the application does not have to be changed, which guarantees the physical independence of the data and the program, or the physical independence of the data.

In the three-level schema structure of the database, the database schema, the global logical structure, is the center and key of the database, which is independent of the other layers of the database. Therefore, when designing a database schema, you should first determine the logical schema of the database.

The internal schema of the database relies on its global logical structure, but is independent of the database's external schema and the specific storage device. It is the data structure defined in the global logical structure and its relations are organized according to a certain physical storage strategy, in order to achieve better time and space efficiency.

The external schema of the database is for a specific application, which is defined on the logical mode, but independent of the internal mode and storage device. When the application requirements change greatly, the corresponding external mode can not meet its view requirements, the external mode needs to be modified accordingly, so the design of the external mode should take into account the extensibility of the application. Different applications can sometimes share the same out-of-the-way mode.

The two-level image of the database guarantees the stability of the out-of-database model, guaranteeing the stability of the application from the bottom up, unless the application requirements themselves change, otherwise the application generally does not need to be modified.

The three-level and two-level images of the database implement the independence between the data and the program, so that the definition and description of the data can be separated from the application. In addition, because data access is managed by the DBMS, users do not have to consider details such as access paths, which simplifies application development and greatly reduces application maintenance and modification costs.

The development of database technology and the understanding of SQL database