[Database] basic knowledge Summary

Database integrity in order to maintain database integrity DBMS needs to provide: 1. provides a mechanism to define integrity constraints. 2. provides integrity check methods: Generally, check is started after the INSERTUPDATEDELETE statement is executed or before the transaction is submitted. default handling mechanism: for example, deny, cascade, or other operation entity integrity with PRIMARY

Database integrity in order to maintain database integrity DBMS needs to provide: 1. provides a mechanism to define integrity constraints. 2. provides the integrity check method: generally checks after the insert update delete statement is executed or before the transaction is committed. default handling mechanism: for example, deny, cascade, or other operation entity integrity with PRIMARY

Database integrity

To maintain database integrity, DBMS must provide:
1. provides a mechanism to define integrity constraints
2. Provide the integrity check method: Generally, check is started after the insert update delete statement is executed or before the transaction is submitted.
3. Default handling mechanism: such as rejection, cascade, or other operations

Entity integrity

Use primary key for definition. For a single attribute, entity integrity can be defined as a column-level constraint or a table-level constraint. For codes consisting of multiple attributes, only table-level constraints can be defined.

For example:
Column-level constraints (column-level master code)

    CREATE TABLE S(        Sno CHAR(9) PRIMARY KEY,        Sname CHAR(20) NOT NULL,        .....    );

Table-level constraints (Table-level master code)

    CREATE TABLE S(        Sno CHAR(9) ,        Sname CHAR(20) NOT NULL,        .....        PRIMARY KEY(Sno)    );

Check and default handling:
1. Check whether the primary code value is unique. If the primary code value is not unique, the insertion or modification is rejected (full table scan or query on the index created by the DBMS for the primary code, such as B + tree index)
2. check whether all attributes corresponding to the primary code value are empty. If any attribute is empty, insert or modify it.

Integrity of reference

When creating a table, use the foreign key to define the external code. Use the REFERENCES phrase to specify which tables the external code REFERENCES. Similar to the primary code, the external code can also define table-level and column-level reference integrity. But there is no difference.

For example:

    CREATE TABLE SC(    Sno CHAR(9) NOT NULL,    Cno CHAR(4) NOT NULL,    .....    PRIMARY KEY(Sno, Cno),    FOREIGN KEY (Sno) REFERENCES Sudent(Sno),    FOREIGN KEY (Cno) REFERENCES Course(Cno),    );

Damage to the integrity of the reference and the handling of the breach:
1. Insert a tuples in the SC table, but the Sno attribute of the tuples does not exist in Student of the referenced table.
2. Update a tuples in the SC table, but the Sno attribute of the tuples does not exist in Student of the referenced table.
3. delete a tuples in the Student table. As a result, Sno of some tuples in the reference table SC does not exist in the Student table.
4. Update a tuples in the Student table. As a result, Sno of some tuples in the reference table SC does not exist in the Student table.

When an inconsistency occurs, the following measures are generally taken:
1. Execution rejected
2. cascade operation: When the referenced table is deleted or modified and does not meet the integrity of the reference, the cascade operation is deleted by all inconsistent tuples in the table.
3. Set a null value (this also causes the problem that whether to allow the external code column to be empty when defining the external code. If it is not allowed to be empty, it cannot be processed by 3)

You can explicitly specify the processing policy when creating a table.

    CREATE TABLE SC(    Sno CHAR(9) NOT NULL,    Cno CHAR(4) NOT NULL,    .....    PRIMARY KEY(Sno, Cno),    FOREIGN KEY (Sno) REFERENCES Sudent(Sno)        ON DELETE CASCADE        ON UPDATE CASCADE,    FOREIGN KEY (Cno) REFERENCES Course(Cno)        ON DELETE NO ACTION        ON UPDATE CASCADE,      );

User-Defined integrity

The Semantic requirements that data defined based on specific applications must meet. Category:

1. The column value is not null)
2. UNIQUE column value (UNIQUE phrase)
3. CHECK whether the column value meets a Boolean expression (CHECK phrase)

    CREATE TABLE SC(    Sno CHAR(9) UNIQUE,    Cno CHAR(4) NOT NULL,    Grade SMALLINT CHECK(Grade >=0 AND Grade <=100),    .....    PRIMARY KEY(Sno, Cno),    FOREIGN KEY (Sno) REFERENCES Sudent(Sno),    FOREIGN KEY (Cno) REFERENCES Course(Cno),    );

As before, user-defined integrity can also be defined as a column-level restriction or a table-level restriction. The preceding example shows the column-level user-defined integrity. The table-level user-defined integrity is as follows:

Create table Student (Sno CHAR (9) UNIQUE, Sname CHAR (8) not null, Ssex CHAR (2 ),..... primary key (Sno), CHECK (Ssex = 'female 'OR Sname not like 'Ms. % '));

The difference between the two is:Table-level restrictions can be defined between different attribute values. For example, the preceding example defines the constraints between gender attributes and name attributes.

Constraints check and default handling:
Generally, the request is rejected.

Integrity naming clause

In addition to using integrity constraints in section 3 above, SQL also provides a CONSTRAINT integrity constraints naming clause to name integrity constraints to facilitate the addition and deletion of integrity constraints.

Definition of integrity naming clauses:
Let's look at the example:

Create table Student (Sno NUMERIC (6) CONSTRAINT C1 CHECK (Sno BETWEEN 90000 AND 99999), Sname CHAR (8) CONSTRAINT C2 not null, Sage NUMERIC (3) CONSTRAINT C3 CHECK (Sage <30), Ssex CHAR (2) CONSTRAINT C4 CHECK (Ssex IN ('male', 'female ')),..... CONSTRAINT StudentKey primary key (Sno ));

Modify the integrity naming clause:
Delete and redefine it.

    ALTER TABLE Student        DROP CONSTRAINT C1;    ALTER TABLE Student        ADD CONSTRAINT C1 CHECK(Sno BETWEEN 900000 AND 9999999);

Trigger

A trigger is a special event-driven process defined in a relational table. After the definition, the DBMS automatically activates the corresponding trigger for any user to add or delete the database. Triggers are similar to constraints but more flexible than constraints. It can implement more complex checks and operations than the foreign key check constraints, and has more precise and powerful data control capabilities.

Trigger instance:

    CREATE TABLE Sql_log(    Eno NUMERIC(4) REFERENCE teacher(Eno),    Sal NUMERIC(7, 2),    Username CHAR(10),    Date TIMESTAMP    );    CREATE TRIGGER Insert_Sal        AFTER INSERT ON Teacher        FOR EACH ROW        AS BEGIN            INSERT INTO Sal_log VALUES(new.Eno, new.Sal, CURRENT_USER, CURRENT_TIMESTAMP);        END;    CREATE TRIGGER Update_Sal        AFTER UPDATE ON Teacher        FOR EACH ROW        AS BEGIN            IF(new.Sal <> old.Sal) THEN INSERT INTO Sal_log VALUES(new.Eno, new.Sal, CURRENT_USER, CURRENT_TIMESTAMP);            END IF;        END;

Note:
1. The table owner can create a trigger for the table.
2. The trigger event can make INSERT/DELETE/UPDATE a combination of the three.
3. Row-level triggers

Trigger activation sequence for the same table:
1. Execute the before trigger-multiple before triggers on the same table are executed in the defined order.
2. Activate the SQL statement of the trigger
3. After a trigger is activated, the trigger body is executed only when the trigger condition is true. If the when trigger condition is omitted, the trigger action body is executed immediately after the trigger is activated.
4. The trigger action body can be either an anonymous SQL statement or a call to a created stored procedure.

Trigger deletion:
The deleted trigger must be a created trigger and must also have the corresponding user permissions.

    DROP TRIGGER Insert_Sql ON Teacher;

Trigger is divided into trigger beforehand and trigger afterwards. What is the difference between the two? What is the difference between ROW-level triggering (for each row) and STATEMENT-level triggering (for each statement?
Before an event is triggered, verify some conditions or preparations. After the event is triggered, complete the work. Pre-trigger can obtain the old and new values, and trigger later can ensure the integrity of the transaction.
A statement-Level Trigger can be performed before or after a statement is executed. Generally, a row-Level Trigger is performed only once. A trigger event is executed multiple times based on the number of affected rows. At the same time, row-level triggers can use new and old to reference values before and after the update/insert event is executed, but statement-level triggers cannot.

Paradigm

The paradigm is a set of relational patterns that conform to a certain level, generally 1NF 2NF 3NF BCNF 4NF

1NF

1NF indicates that each column in the database is a basic data item that cannot be further divided. It is the most basic requirement of the database (the same column cannot contain multiple values or duplicate attributes ).

2NF

2NF is based on 1NF and eliminates some function dependencies of every non-primary attribute. In general, each row or instance in the database can be uniquely partitioned (there are no multiple differentiation methods, and part of the function dependency is absent ).

3NF

3NF is eliminated Based on 2NFTransfer Function dependency for non-primary attributes.

BCNF

Based on 3NF, BCNF eliminatesThe transmission dependency and partial dependency of primary attributes on CodesThat is to say, BCNF eliminates part of the code dependency and transmission dependencies between any attributes (including the primary and non-primary attributes.
A special BCNF example is full code. Obviously, the full code does not have a non-primary attribute, so it must be at least 3NF, and the full code does not have transmission and partial dependencies. Therefore, it is also BCNF.

BCNF is the best optimization within the scope of function dependency. It basically eliminates insertion and deletion exceptions, but does not work within the scope of multi-value dependency.

4NF

The attribute of the 4NF restricted link mode does not exist between the attributes.Non-trivial and non-functional multi-value dependency(The only non-trivial multi-value dependency allowed is the function dependency ).

Involved concepts: multi-value dependency, ordinary multi-value dependency, function dependency, etc. You can simply look at this book and it is indeed a little abstract.

Within the multi-value dependency range, 4NF is already the most optimized.

Standardization process:
1NF: remove some function dependencies of non-primary attributes-> 2NF: Remove function dependencies of non-primary attributes-> 3NF: remove some function dependencies of primary attributes and transmit function dependencies-> BCNF: remove non-trivial and multi-value dependency of non-function dependency> 4NF

View

A view is a table exported from one or more basic tables. The database value stores the view definition without storing the data of the corresponding view. After a view is defined, it can be queried and deleted like a basic table, and the view can be further defined on The View.There are certain restrictions on the view update operation (add, delete, modify ).

View definition:

    CREATE VIEW IS_Student    AS    SELECT Sno, Sname, Sage    FROM Student    WHERE Sdept='IS'    WITH CHECK OPTION;

With check option indicates that the update and insert delete operations on data must meet the predicate conditions in the definition view. That is, subquery after.
In addition,The attribute column names that make up the view are either omitted or all specified, You must specify the column name. (1) The target column is a clustering function or a column expression. (2) The same column is selected as the field of the view when multiple tables are connected. (3) you need to define a more appropriate name in the view.

For example, in the preceding example, the created view is only exported from a basic table. Some columns in some rows of the basic table are removed but the primary code is retained.Row and column subset View.

    CREATE VIEW BT_S(Sno, Sname, Sbirth)    AS    SELECT Sno, Sname, 2016-Sage    FROM Student

Data in a view is not stored. In some cases, columns in the view are derived from basic tables. These derived columns are called virtual columns because they are not stored in the database. A view with virtual columns is also calledView with Expression;

    CREATE VIEW S_G(Sno, Gavg)    AS    SELECT Sno, AVG(Grade)    FROM SC    GROUP BY Sno;

The view defined BY the aggregate function and the query of the group by clause is calledGroup view.

The row-column subset view can be updated, but the Group view and expression-based view cannot be updated.

Delete a view. delete a view or cascade a view. When a cascade operation is deleted, the view defined based on this view is also deleted.

View digestion: When performing a view-based query, the validity check is performed first. After passing the check, the defined subquery and the query are combined to convert it into an equivalent basic table query, finally, the corrected query is executed. This conversion process is called view resolution.

View resolution is not always successful, especially for non-row-column subset views. Therefore, queries of such views should be directly performed based on basic tables.

View update is also based on view resolution, and the view update requirements are more strict, except that the row and column subset views can be directly updated, in theory, some views in other views cannot be updated.

View function:
1. simplified user operations
2. allows users to view the same data from multiple perspectives
3. Views provide a certain degree of logical independence for restructuring Databases
4. Views can provide security protection for confidential data
5. Make sure that the view is clear and concise.

Transactions

A transaction is a database operation sequence defined by the user. These operation sequences are an inseparable unit of work, either completely or completely. The start and end of a transaction can be defined and controlled by the user. If no transaction is defined, the DBMS automatically divides the transaction according to the default rules.

Features of transactions:
1. atomicity
2. Consistency: when a transaction is executed, the database is in another consistent state.
3. Isolation: the operations and data used within a transaction are isolated for other concurrent transactions, that is, the transactions executed concurrently do not interfere with each other.
4. Continuity: Once a transaction is committed, nine changes are permanent. The subsequent transactions or faults do not have an impact on them.

Transactions are the basic unit of recovery and concurrency control.
Scenarios that may damage the ACID feature:
1. Multiple transactions are executed concurrently, and different transactions are crossed.
2. The transaction is forcibly stopped during operation.

Differences between other internal connections and external connections

A query involves multiple tables, which are called connection queries. Including equi-join query, natural connection query, non-equi-join query, self-connection query, external connection query, and qualified connection query.

When the connection operator is '=', it is calledEquijoin, And all others are non-equivalent connections. In the equijoin, remove the duplicates in the target column, which is calledNatural connection.
If a table is connected to itselfSelf-connection.

In normal queries, only the tuples that meet the connection conditions are output. If you output the tuples that do not meet the conditions in the subject table in the plug-in, and enter NULL in the column that does not meet the conditions, this type of connection is calledExternal Connection. Partitions are dividedLeft Outer Join(The main table is a left-side link, and the columns in the left-side link that do not meet the connection conditions are output) andOuter right connection(The main table is the right link, and the columns in the output right link do not meet the connection conditions ).

If the where clause contains multiple conditions (connection conditions or other restrictions ),Composite condition join.
For example:

    SELECT Student.Sno, Sname    FROM Student, SC    WHERE Student.Sno=SC.Sno AND SC.Cno='2' AND SC.Grade>90;

A connection can be used between two tables or between multiple tables. A connection between multiple tables is calledMulti-table join.

Differences between stored procedures and functions

SQL Execution requires compilation before execution. To improve efficiency, large DBMS compiles and optimizes SQL statements that have completed specific functions and stores them on database servers. You can specify the name of the stored procedure to call and execute the statements.

Create:
Create procedure pro_name @ [parameter name] [type]
As
Begin
....
End

Call: exec pro_name [parameter name]
Delete: drop procedure pro_name

Stored Procedures can enhance the functions and flexibility of the SQL language. Because process control statements can be used for writing, stored procedures are highly flexible and can be used for complex judgment and operations. The stored procedure is not a function. The difference between the two is as follows:
1. stored procedures can be executed as independent parts, and functions can be called as part of query statements.
2. The functions of stored procedures are generally complicated, and function implementation is more targeted.
3. functions can be nested in SQL or used in select, but stored procedures cannot.
4. functions cannot operate entity tables. They can only operate built-in tables.
5. The storage process is compiled on the server when it is created, so the speed is faster.