Summary of "database" basics

Database integrity

In order to maintain database integrity The DBMS needs to provide:
1. Provide a mechanism for defining integrity constraints
2. Provide an integrity check method: Generally check after the Insert UPDATE DELETE statement is executed or before the thing is submitted
3. Default handling mechanism: rejection, cascading, or other operations

Entity integrity

Defined with primary key, entity integrity can be defined as a column-level constraint or as a table-level constraint for single-attribute, and can only be defined as a table-level constraint for multiple property-composed codes.

For example:
defined as column-level constraints (column-level master code)

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

defined as table-level constraint (table-level master code)

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

Inspection and default handling:
1. Check if the primary code value is unique, if not unique, reject the insert or modify (full table scan or find on the index established by the DBMS, e.g. B + Tree index)
2. Check whether the individual attributes of the main code value are empty, and if there is an empty attribute, then reject the insert or modify

Referential integrity

Use foreign KEY to define the external code when creating the table, and specify the main code of the table with the references phrase. Similar to the main code, the external code can also define table-level and column-level referential integrity. But it doesn't feel any different.

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),);   

Breaches of referential integrity and default handling:
1. Insert a tuple into the SC table but the Sno property of the tuple does not exist in the referenced table student
2. Update a tuple in the SC table however, the Sno property of the tuple does not exist in the referenced table student
3. Deleting a tuple in the student table causes the Sno of certain tuples in the reference table SC to not exist in the student table
4. Updating a tuple in the student table causes the Sno of certain tuples in the reference table SC to not exist in the student table

The general measures taken in the event of inconsistency are:
1. Refusal to execute
2. Cascade Operations: Delete or modify the referenced table causes the referential integrity is not satisfied, cascade Delete all the inconsistent tuples in the table
3. Set the null value (this also causes a problem is that when the external code is defined when the Outer Code column is allowed to empty, if not allowed to be empty, then can not be processed by the other)

As for which processing strategy to use, you can explicitly specify when you are building a table

    CREATE TABLESC (SnoCHAR(9) not NULL, CnoCHAR(4) not NULL,    .....PRIMARY KEY(Sno, Cno),FOREIGN KEY(Sno)REFERENCESSudent (Sno) on DELETE CASCADE         on UPDATE CASCADE,FOREIGN KEY(Cno)REFERENCESCourse (Cno) on DELETE NO ACTION         on UPDATE CASCADE,      );

User-defined Integrity

The semantic requirements that the data defined according to the specific application must meet. Classification:

1. Column value is non-null (NOT NULL phrase)
2. Column value unique (unique phrase)
3. Checks whether a column value satisfies a Boolean expression (check phrase)

    CREATE TABLESC (SnoCHAR(9)UNIQUE, CnoCHAR(4) not NULL, GradeSMALLINT CHECK(Grade >=0  andGrade <= -),    .....PRIMARY KEY(Sno, Cno),FOREIGN KEY(Sno)REFERENCESSudent (Sno),FOREIGN KEY(Cno)REFERENCESCourse (Cno),);

As before, user-defined integrity can also be defined as a column-level restriction or as a table-level constraint. In the above example is the column-level user-defined integrity, the table-level user customization is complete new 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  Span class= "Hljs-keyword" >like   ' ms.% ' );   

The difference between the two is that table-level restrictions can define the limits between different attribute values . As the above example defines the constraints between the gender attribute and the name attribute.

Constraint checking and default handling:
It is generally handled in a way that denies execution.

Integrity naming clauses

In addition to using the above 3 integrity constraints directly, SQL also provides a constraint integrity constraint naming clause that is used to name integrity constraints to facilitate the addition and deletion of integrity constraints.

Definition of the integrity naming clause:
See examples directly:

    CREATE TABLEStudent (SnoNUMERIC(6)CONSTRAINTC1CHECK(Sno between90000  and 99999), SnameCHAR(8)CONSTRAINTC2 not NULL, SageNUMERIC(3)CONSTRAINTC3CHECK(Sage < -), SsexCHAR(2)CONSTRAINTC4CHECK(Ssexinch(' man ',' Woman ')),    .....CONSTRAINTStudentkeyPRIMARY KEY(Sno));

Modification of the integrity naming clause:
Delete and redefine directly

    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 that a user defines on a relational table. Once defined, any user additions or deletions to the database are automatically activated by the DBMS by the appropriate trigger. Triggers are similar to constraints but more flexible than constraints. It enables more sophisticated checks and operations than the foreign key check constraint, with more granular 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 BEGININSERT  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 BEGINIF(new. Sal <> old. Sal)  then INSERT  into Sal_log VALUES(new. Eno, New. Sal, current_user, current_timestamp);                                 END IF;         END;

Attention:
1. The owner of the table can create a trigger for the table
2. Triggering events can make the insert/delete/update a combination of the three
3. Row-level triggers

Trigger activation order on the same table:
1. Execute before trigger-multiple before triggers on the same table are executed in a defined order
2. Activating the trigger's SQL statement
3. After the trigger is activated, the trigger action body is executed only when the trigger condition is true. If the When trigger condition is omitted, then the triggering action body executes immediately after the trigger is activated.
4. The triggering action body can be either an anonymous SQL statement or a call to a stored procedure that has already been created.

Deletion of triggers:
The deleted trigger must be a trigger that has already been created, and the deleted person must have the appropriate user rights.

    DROP TRIGGER Insert_Sql ON Teacher;

The trigger is divided into pre-trigger and post-trigger, the difference between the two? What is the difference between a row-level trigger (for each row) and a statement-level trigger (for each STATEMENT)?
A pre-trigger occurs before an event occurs, with the validation of conditions or preparations, and the subsequent triggering occurs after the event has been completed. The pre-trigger can obtain the previous value old and new value new, and the post-event trigger guarantees the integrity of the transaction.
Statement-level triggering can occur before or after a statement is executed, typically only once, and row-level triggering, and touch events are typically executed several times depending on the number of rows affected. At the same time, row-level triggers can use new and old to refer to values before and after the Update/insert event execution, but statement-level triggering is not possible.

Paradigm

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

1NF

1NF is a basic data item that satisfies every column in a database that is non-repeatable, and is the most basic requirement for a database (there cannot be multiple values or duplicate attributes in the same column).

2NF

2NF is based on the 1NF, eliminating the partial function dependency of each non-principal attribute. In layman's parlance, every row or instance in a database is required to be uniquely separated (there are no multiple ways of distinguishing, and there is no part of the function dependency).

3NF

3NF is based on the 2NF elimination of the non-primary property of the transfer function dependency .

BCNF

Based on the 3NF, BCNF also eliminates the dependency and partial dependency of the main attribute on the code , that is, bcnf eliminates the partial dependency and transitive dependence of any attributes (both primary and non-primary) on the code.
A special BCNF example is full code, obviously no non-main property, so at least 3NF, and there is no transmission and partial dependence, so it is also bcnf

BCNF is the optimal optimization within the function-dependent range, which basically eliminates the insertion and deletion exceptions, but is not valid for multivalued dependencies.

4NF

There is no non-trivial and non-function dependent multivalued dependency between the attributes of the 4NF restricted relational pattern (the only allowed non-trivial multivalued dependency is function dependency).

The concepts involved: multivalued dependencies, trivial multivalued dependencies, function dependencies, and so on, directly looking for a book to see on it, indeed a little bit abstract.

4NF is already the most optimized in a multi-valued dependency range.

Normalization process:
1NF partial functions that eliminate non-primary attributes rely on->2NF to eliminate the function dependency of non-primary properties->3nf eliminate the partial function dependency of the main attribute and the transfer function dependency->bcnf eliminate the non-trivial and non-function dependent multi-valued dependency->4nf

View

A view is a table that is exported from one or more base tables, where values in the database hold the definition of the view without storing the data for the corresponding view. Once a view is defined, it can be queried and deleted as if it were a basic table, and the view can continue to be defined on top of the view. There are certain limitations to update operations on Views (add and change)

Definition of the View:

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

With CHECK option means that updating the data with the update Insert delete operation requires that the update operation satisfies the predicate condition in the defined view. That is, a subquery after as.
In addition, the attribute column names that make up the view are either omitted or all specified , and the column name must be specified in the case: 1) The target column is a clustered function or a column expression 2) when a multi-table connection is selected as the same as the View field 3), you need to define a more appropriate name in the view.

As in the example above, the views created are simply exported from a basic table, some columns of some rows of the base table are removed, but the main code is preserved, and such views are called the view of the subset of rows.

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

The data in the view is not stored, and sometimes the columns in the view are derived from the base table, which is called a virtual column because it is not stored in the database. A view with a virtual column is also known as a view with an expression ;

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

A view defined using a query of a clustered function and a GROUP BY clause is referred to as a grouped view .

The row and column subset views can be updated, but grouped views and views with expressions are generally not updatable.

Deletion of views, general deletions, and cascading deletions. When cascading is deleted, views that are defined based on this view are also deleted.

View digestion : In the implementation of the view-based query, the first validation checks, through the definition of sub-query and used to combine the query into the equivalent of the basic table query, and finally execute the revised query. The conversion process is called view digestion.

View digestion is not always successful, especially for non-column subset views, so queries for this type of view are based directly on the base table as much as possible.

Updates to views are also based on view digestion, and the update requirements of the view are more stringent, except that the view of a subset of rows can be directly updated, and some views of other views are theoretically proving impossible to update.

The role of the view:
1. Simplified user operation
2. Enable users to view the same data in multiple ways
3. Views provide a degree of logical independence for refactoring a database
4. Views can provide security for confidential data
5. An appropriate view for clearer and more concise queries

Transaction

A transaction is a user-defined sequence of database operations that is an indivisible unit of work, either wholly or wholly. The start and end of a transaction can be defined and controlled by the user, and if no transaction is defined, the DBMS automatically divides the transaction by default.

Features of the transaction:
1. atomicity
2. Consistency: The execution of a transaction causes the database to reach another consistent state from one consistent state
3. Isolation: The operation within a transaction and the data used are isolated for other concurrently executing transactions, that is, the concurrent execution of the transactions between each other is non-interference.
4. Continuity: Once a transaction is committed, the change to frost is permanent. The next transaction or failure should not have an impact on it.

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

Differences between internal and external connections

More than one table is involved in a query, which is called a connection query. Including equivalent connection query, natural connection query, non-equivalent connection query, self-connection query, external connection query and eligible connection query.

When the join operator is ' = ', it is called an equivalent connection , and the others are non-equivalent. In an equivalent connection, the repetition of the target column is removed, called a natural connection .
A connection in a query is called a connection if it is a table connected to itself.

In a typical query, only tuples that satisfy the join condition are output, and if you output a tuple in the principal table that does not meet the criteria in socialize, and NULL on a column that does not meet the criteria, this type of connection is called an outer join . Depending on the selection of the main table, the grams is divided into left outer joins (the main table is the left relationship, the columns that do not meet the join condition in the left relationship) and the right outer join (the main table is the right relationship, and the column that does not satisfy the join condition in the right relationship).

If there is more than one condition (join condition or other qualification) in the WHERE clause, it is called a composite conditional join .
For example:

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

Connections can be made between two tables, or between multiple tables, and a connection between multiple tables is called a multi-table connection .

The difference between a stored procedure and a function

SQL execution needs to be compiled before it can be executed. To improve efficiency, a large DBMS compiles and optimizes SQL statements that perform specific functions, which are stored in the database server and can be invoked by the user by specifying the name of the stored procedure.

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 functionality and flexibility of the SQL language because they can be written using Process Control statements, so they are highly flexible and can be used to implement complex judgments and operations. Stored procedures are not functions, the difference between the two:
1. Stored procedures can be executed as separate parts, and functions can be called as part of a query statement
2. The function of the general implementation of the stored procedure is more complex, and the function implementation is more specific.
3. Functions can be nested in SQL, or can be used in select, and stored procedures cannot be
4. Functions cannot manipulate entity tables, only the built-in tables can be manipulated
5. When the stored procedure is created, it is compiled on the server, so it is faster

Summary of "database" basics