relational database theory and MySQL micro-structure

relational database, which is based on the relational model, is used to deal with the data in the database by means of mathematical concepts and methods such as set algebra. The relational model consists of three parts: relational data structure, relational operation set, and relationship integrity constraint.

1. Organizational structure of data: Hierarchical model, network model, and relational model.

2. There are 4 common relational models: relational model, Entity-relationship model, object-relational model, semi-structured data Model (XML).

3. Constraint classification:

Domain constraints: data type constraints;

FOREIGN KEY constraints: referential integrity constraints;

PRIMARY KEY constraint: A field can uniquely identify the entity to which this field belongs, and it is not allowed to be empty, only one primary key in a table;

Unique key constraint: Each row of a field does not allow the same value, can be empty a table can have multiple unique keys;

Check the constraint: Constraint a field can not appear in violation of the common sense of the value, MySQL support is limited;

4. Database Logical Relationship:

Presentation layer: Table

Logical layer: Storage engine

Physical Layer: Data file

5. Storage and query of data:

Storage Manager: Permissions and integrity manager, transaction manager, File Manager, buffer Manager;

Query Manager: DML interpreter, DDL interpreter, query execution engine;

6. Relational operations

Projection: Output only specified fields

Select: Output only rows that match the criteria

Natural connection: All rows with the same name in two tables have the same value on the row connection output

Cartesian product: (a+b) * (c+d) =AB+AD+BC+BD

And: The two relationships are part of each other.

7. How to interact with RDBMS using the programming language:
Embedded SQL: Similar to dynamic SQL, but its language must be fully determined when the program compiles;
Dynamic SQL: The programming language uses a function (mysql_connect ()) or a method to establish a connection with an RDBMS server and interact with it, send a query statement to the SQL Server by establishing a connection, and save the result to a variable and then process it;

8.MySQL Micro-structure

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/71/8C/wKiom1XT4d2jDynjAAFInVqIXRY799.jpg "title=" O] T5ek5sirneeufp__kd9mc.png "alt=" Wkiom1xt4d2jdynjaafinvqixry799.jpg "/>

Connection Manager: Listens for and accepts user requests;

Thread Manager: Creates a new thread or gives the user a response thread in a thread-reuse manner;

User module: Check user connection permissions;

Parser: Parse query and generate parse tree;

Access Control module: Verify that the user has permission to manipulate the object it wants to manipulate;

Optimizer: Responsible for creating the best query policy when responding to requests;

Table definition modules: Operations such as table creation, deletion, renaming, removing, updating, or inserting;
Table Maintenance Module: Inspection, modification, backup, recovery, optimization (defragmentation) and parsing;

Table Manager: Responsible for creating, reading, or modifying table definition files, maintaining table descriptor caches, and managing table locks;

9. Record organization in file: how each row in a table is stored in a file
Heap file organization: A record can be placed anywhere in the file;
Sequential file organization: According to the "Search code" value in order to store;
Hash file organization: A field or some fields of a table hash, according to the results are stored in a hash container (bucket);

relational database theory and MySQL micro-structure