Just registered. 11.2 senior programmers. Let's review database knowledge today.

2.5.1 Key knowledge points

Master the basic knowledge of database models, database system structures, and relational database structures, and master the use of relational algebra in SQL.

2.5.1 1. Data Architecture of relational databases

The relational model follows the three-level architecture of the database:

(1) link mode. The conceptual schema of a database is defined as a set of Relational Schemas. Each link mode is a record type. The definition of link mode includes the key of mode name, attribute name, Value Domain Name, and mode. The relational model only describes the characteristics of data.

(2) The link submode is the description of the data used by the user. In addition to the data used by the user, the relationship between the data and the corresponding data in the mode should also be pointed out, that is, the correspondence between the submode and the mode.

(3) relational storage is viewed as a file, and each tuple is a record. Because the link mode has a key, you can use the hash or index method to store a link.

2.5.1 . 2 relational model and relational operation

A two-dimensional table structure is used to represent an object set. A key code is used to represent the data model of the association between objects. It is called a relational model. In a link, the attribute set that uniquely identifies the tuples is called the link candidate key, and the selected candidate key is called the link primary key. Each attribute in a link corresponds to a value range, which is called the value range of an attribute. A link can be defined as a combination of tuples with the same number of elements (number of attributes. A link is a set, and the composition in the set is tuples. The number of attributes of these tuples should be the same.

The data update operations of relational databases must follow the entity integrity rules, reference integrity rules, and user-defined integrity rules.

Relational query languages are divided into two categories based on their theoretical basis: relational algebra, which features query operations based on set operations, and relational computing language, the query operation is based on the predicate calculation.

Relational algebra is a set of advanced operations developed on the basis of a set of algebra. Relational calculation objects are a set of advanced operations. Considering the relationship as a set, the computation in the set algebra can be introduced into the relational computation. In addition, some operations are specially designed for the relational database environment.

Five basic operations of relational algebra: Sum, difference, Cartesian Product, projection, and selection.

Four combined operations of relational algebra: intersection, join, natural join, and division.

Expanded relational algebra operations: Outer Join (left Outer Join and right Outer Join), external join (out union) and semi join.

2.5.1 . 3. Relational Database SQL language

Structured Query Language SQL is a database language that integrates DDL, DML, and data control functions. SQL DML is a language between relational algebra and relational calculus.

Composition of the SQL language:

· A SQL database is a collection of tables, which are defined in one or more SQL modes.

· An SQL table consists of row sets. A row is a sequence of columns, and each column corresponds to a data item.

· A table, a basic table, or a view. A basic table is a table actually stored in a database, and a view is a definition of a table composed of several basic tables or other views.

· A basic table can store one or more basic tables across one or more storage files.

· You can use SQL statements to query views and basic tables.

· SQL users can be applications or end users. SQL statements can be embedded in programs in the host language. The host language can be a commonly used high-level language. SQL users can also be used as independent user interfaces for end users in the interactive environment.

SQL includes all database operations, including data definition (SQL ddl), data manipulation (SQL dml), access control, and embedded SQL.

2.5.1 4. Relational Database Standardization Theory

The relational database standardization theory mainly includes three aspects:

(1) function dependency. There are various associations and constraints between indexes. For example, building is a dependency. Function dependency is the most basic dependency.

(2) paradigm. Standard form of pattern decomposition. The two features of link Mode Decomposition actually involve the equivalence of two database modes, including data equivalence and dependency equivalence. Data equivalence means that two database instances should indicate the same information content, measured by "lossless connection. Dependency equivalence refers to the function dependency set in which two database modes have a logical relationship with each other. At this time, the data semantics will not be wrong.

(3) pattern design method. Design a standard database mode.
The standard for measuring the advantages and disadvantages of the Relationship Model is the model Paradigm (NF ). There are many kinds of paradigms that are directly related to data dependencies.

① First paradigm (INF)

If the attribute values of each link R in the relational mode R are atomic values that cannot be divided, r is the first normal mode (INF ).
, R is the canonicalized relationship. The most basic requirements of the lnf model for relational databases.

② Second Paradigm (2nf)

We divide the Relationship Mode R attributes into two categories: key attributes, called primary attributes, and non-primary attributes, which do not belong to any key. If the relational mode R is INF and each non-primary attribute fully function depends on the candidate key, R is called 2nf mode.

③ Third paradigm (3nf)

If the relational mode R is INF and no candidate set dependent on R is passed for each non-primary attribute, R is called 3nf mode. In 3nf mode, the key transfer dependency of non-primary attributes is excluded. 3nf mode must be 2nf mode. Local dependency and transmission dependency are two important reasons for redundancy and exceptions. 3nf mode has good performance because it does not have a local dependency and transmission dependency on the candidate key for non-primary attributes. However, for non-3nf INF, 2nf, or even non-INF relational models, their performance weaknesses are generally not suitable for database models, usually they need to be transformed into 3nf or a more advanced paradigm
This transformation process is called "canonicalized processing of links ".

④ BC Paradigm (bcnf for short)
If the relational mode R is INF and each attribute does not pass a candidate key dependent on R, R is called bcnf mode. In this case, any Attribute-to-key transmission dependency is excluded.

Relationships between the above four paradigms: bcnf
3nf 2nf 1nf.