SQL mode, external mode, internal mode secondary image Conversion

A really painful Concept

 

From http://blog.sina.com.cn/databasegao

"

When preparing lessons in the morning, I found some conceptual errors in the database design overview in chapter 2 of the textbook, which reminds me, students may not be able to fully understand the concept of external mode, internal mode, and internal mode, so I want to discuss it through my blog.

First, let's talk about the concept of patterns. patterns are the standard form of a thing or the standard style that people can follow. This word is used in databases. It is translated from an English schema. In the database management system, the data schema is used as the result of data abstraction.LINK modelThe basic information of abstract students is expressed:Student (student ID, name, gender, date of birth, date of entry, number of major)This indicates a data mode.

In the database management system, data is abstracted based on a three-tier structure, which is essentially inseparable from the database design steps. The database design should first analyze the actual requirements, that is Requirement Analysis The task of requirement analysis is to analyze users' business processes through surveys, interviews, and discussions, so as to obtain users' needs and describe user requirements using data flowcharts and data dictionaries. Secondly, based on requirement analysis Database concept design The design is irrelevant to the specific database management system. The task is to abstract the data view required by each user ( Corresponds to the concept of external mode ). (Corresponding to the schema Concept Can be abstracted using conceptual data models. The ER model or object model can be used. Step 3: Convert the data view represented by the ER model or object model to the link mode and optimize the obtained link mode. This is called Database Logic Design(This step still corresponds to the external mode and Mode) Step 4: based on the logic design, organize and store the obtained data mode to the physical media. Physical Design of databases(This step corresponds to the Internal Mode) Finally Database Security Design That is, to allow users to access the database, as well as the permissions of legal users to access data in the database.

the Database Management System Research Team of the American National Standard Institute (ANSI) proposed the standardization proposal in 1978, dividing the database structure into three levels: user-level for users or application Programs , conceptual level for database creation and maintenance personnel, and physical level for system programmers. The user level corresponds to the external mode, the concept level corresponds to the mode, and the physical level corresponds to the internal mode, so that different levels of users form different views of the database. The so-called view refers to the scope, angle, and method of observing, understanding, and understanding data. It is a reflection of the database in the user's eyes. Obviously, different levels (levels) the databases displayed by the user are different.

1. mode
A mode is also called a conceptual mode or a logical mode. It corresponds to a conceptual level. It is a global logical structure built by database designers based on a unified view of all user data. It is a general description of the logical structure and features of all data in the database, is the public data view (Global View) of all users ). It is described and defined by the data description language (DDL) provided by the database management system, reflecting and reflecting the overall concept of the database system.
2. External mode
external mode, also known as Child mode, corresponds to the user level. It is the data view of a database seen by one or several users, and is a logical representation of data related to an application. The external mode is a subset of the exported data from the mode, which contains the data that can be used by a specific user in the mode. You can use an external mode description language to describe and define data records corresponding to the user (external mode). You can also use the data manipulation language (DML) to process these data records. The external mode reflects the user view of the database.
3. internal Mode
the internal mode, also known as the storage mode, corresponds to the physical level. It is the internal representation or underlying description of all data in the database, it describes the storage method and physical structure of data on the storage medium, corresponding to the database actually stored on the external storage medium. The internal mode is described and defined by the description language of the internal mode. It is the storage concept of the database.
in a database system, there is only a unique database. Therefore, it is the only mode that defines and describes the database storage structure, however, the applications built on the database system are very extensive and diverse. Therefore, the corresponding external mode is not the only one and cannot be the only one.
4. ing between three modes (second-level image)

The three-level mode of a database is the abstraction of a database at three levels, so that users can process data logically and abstracted without having to care about the physical representation and storage of data on the computer. In fact, for a database system, physical databases exist objectively and are the basis for database operations, A conceptual database is a logical and abstract description (that is, a mode) of a physical database. A user-level database is an interface between a user and a database, it is a subset of concept-level databases (external mode ).
User applications perform data operations according to the external mode,

Defines and establishes the correspondence between an external mode and a mode through external mode/mode ingAssociate the external mode with the mode. When the mode changes, you only need to change its ing so that the external mode remains unchanged and the corresponding application can also remain unchanged. (logic)

Defines the correspondence between the logical structure (mode) and storage structure (internal mode) of data through mode/Internal Mode ing.When the storage structure of data changes, you only need to change the mode/Internal Mode ing to keep the mode unchanged, so the application can also remain unchanged. (Physical)

"