Some database notes

1nfRequirements: 1. Each row has no order relationship; 2. Each column has no order relationship; 3. Duplicate rows are not allowed; 4. null columns are not allowed.

Therefore, the actual requirement is: 1. The unique key; 2 is required, and the nullable attribute is not allowed. Of course, the unique key here can be an attribute or a superkey. If a relation satisfies 1nf, all attributes are a superkey.

Super-key is a set of attributes for each row. Minimal super-key is called a candidate key.

In all candidate keys, one is usually declared as the primary key.

2nfThe first is 1nf. Then, each non-Prime Attribute must be dependent on the entire set of candidate keys, not a part. For example, in a table such as (employee, skill, location), "employee" and "skill" are a candidate key (because one person may have multiple skills), while "location" only depends on "employee, does not rely on skill, so this is not a 2nf.

If a relationNot 2nf.

3nf2nf is the first, and candidate key is required that all non-prime attributes cannot be passed. Intuitively,Non-prime attributes must provide something about the key, and nothing else.

In other words, 3nf cannot be satisfied for objects with passing dependencies.

For example, in (tournament, year, winner, winner birthday), (tournament, year) is a candidate key, but winner birthday is associated with (T, Y) through winner, this does not satisfy 3nf.

If 3nf is not met, inconsistency may occur. For example, if a winner appears in a different competition or year, his or her birthday may be inconsistent. The solution is to split the table into two tables.

Boyce-codd NF (BcnfOr 3.5nf), slightly stronger than 3nf.

 

Primary Key: Select one of Multiple candidate keys as the unique identifier of the row. This can beSurrogate key(Manually introduced, such as the serial number of auto_increment) can be a specific attribute or a key composed of multiple attributes (calledComposite key)

Foreign key: Foreign key, one type of integrity constraints, requiring this attribute to appear in a key of another table.

Alternate key: Other keys except the primary key.

 

Clustered IndexAndNon-clustered Index: The former determines the storage location of the row (so there is only one), and the latter is only a logical index for accelerating search. Many RDBMS create indexes for keys by default.

 

Prepared statements: SQL statement template. Generally, there is no more complex logic (such as branch and jump ). The purpose of PS is to compile and optimize it only once and reduce the possibility of SQL injection.

Stored Procedure: A stored procedure, which is stored in special functions on the DBMS server. It is generally written in the language of vendor-specific. In addition to SQL, it also has some advanced features, it generally includes condition judgment, branch, jump, and so on. This subroutine is pre-compiled and stored on the database server.

Common SP: Oracle PL/SQL, ms SQL Server Transact-SQL, DB2 SQL-Pl, and PostgreSQL PL/pgsql.

The advantage of SP is that it has been pre-compiled and stored on the server side, so it can be better inherited to the dB (for example, triggered by trigger) reduce network overhead, hide business logic, prevent SQL injection attacks, and customize access right.

 

Trigger: trigger beforehand, trigger afterwards, and trigger row-level.

 

Database Normalization:

The process of organizing data to minimize redundancy is calledNormalization.

The goal of database Normalization is to decompose relations with anomalies in order to produce smaller, well-structured relations. Normalization usually involves dividing large tablesSmaller(And less redundant) tables and defining relationships between them. The objective is to isolate data so that additions, deletions, and modifications of a field can be madeIn just one tableAnd then propagated through the rest of the database via the defined relationships.

Informally, a relational database table (the computerized representation of a relation) is often described as "normalized" if it is inThird Normal Form. Most 3nf tables are free of insertion, update, and deletion anomalies, I. e. In most cases 3nf tables adhere to bcnf, 4nf, and 5nf (but typically not 6nf ).

A standard piece of database design guidance is that the designer shocould create a fully normalized design; selectiveDenormalizationCan subsequently be saved MedPerformanceReasons.

The normalization process is actually the process of minimizing a single table, reducing the dependencies in the table, and creating inter-Table dependencies through the auxiliary table.

Benefits After normalization:

1, free the database of modification anomalies

2. Minimize redesign when extending the database structure

3. Make the data model more informative to users

4, avoid bias towards any participant pattern of querying

 

In computing, denormalization is the process of attempting to optimze the read performance of a databaseAdding redundantData orGrouping data.

Normalization removes redundancy and simplifies the table structure. However, the cost is that in many cases, database operations must be performed on several tables. Such join operations are costly.

Solution: 1. Create a view on the table and create an index on the view to improve the performance.

2. denormalize some tables. Generally, after normalization, redundancy is added for performance bottlenecks to improve performance.

 

DML (data manipulation language ):
They are select, update, insert, and delete, just like their names. These four commands are used to operate the data in the database.
DDL (Data Definition Language ):
DDL is more than DML. The main Commands include create, alter, and drop. DDL is mainly used to define or change the table structure and data type, most of them use
DCL (Data Control Language ):
Is the database control function. Is a statement used to set or change database user or role permissions, including (Grant, deny, revoke, etc.) statements. By default, only SysAdmin, dbcreator, db_owner, db_securityadmin, and other personnel are authorized to execute DCL.