Database-Database Integrity

Integrity of the database

Correctness and compatibility of data

Data integrity and security are two different concepts
Integrity of data
Prevents non-conforming data in the database, that is, prevents incorrect data from being present in the database
Object of protection: non-semantic, incorrect data
Security of data
Protect databases against malicious destruction and illegal access
Object protection: Illegal users and illegal operations
To maintain the integrity of the database, the DBMS must:
1. Provide a mechanism for defining integrity constraints
2. Methods of providing integrity checks
3. Default Handling

Entity integrity

Entity Integrity definitions
Entity integrity of the relational model
CREATE table with primary key definition
There are two ways to describe a single-attribute code
defined as column-level constraints
Defined as a table-level constraint
There is only one way to describe a code that consists of multiple attributes
Defined as a table-level constraint

[Example 1] Define student attribute in Sno table as code (1) Define master code at column level create  table  Student (Sno char  (9 ) primary  KEY , Sname char  (20 ) not  null , Ssex char                  (2 ), Sage smallint , Sdept char  (20 ));   

 (2) define the main code create  table  Student (Sno char  (9 ), Sname char  (20 ) not  null , Ssex char  (2  ), Sage smallint , sdept char  (20 ), primary  key  ( Sno));  

［例2］将SC表中的Sno，Cno属性组定义为码      CREATE TABLE SC           (Sno   CHAR(9)  NOT NULL，             Cno  CHAR(4)  NOT NULL，              Grade    SMALLINT，            PRIMARY KEY (Sno，Cno)     /*只能在表级定义主码*/          );

Entity integrity check and default handling
When you insert or update a main code column, the RDBMS checks automatically according to the entity integrity rules. Including:
1. Check if the primary code value is unique, and if not unique, reject the insert or modify
2. Check if each property of the main code is empty, and if one is empty, reject the insert or modify

Referential integrity

Referential integrity definitions
Referential integrity definitions for relational models
Define which columns are external codes in the CREATE table with the foreign key phrase
Use the references phrase to indicate which table's main code these foreign codes refer to

For example, a tuple in the relationship SC represents the result of a course that a student is taking, (SNO,CNO) is the main code. Sno,cno defining referential integrity in SC by referring to the main code of the student table and the course table (example 3) respectivelyCREATE TABLESC (SnoCHAR(9) not NULL, CnoCHAR(4) not NULL, GradeSMALLINT，PRIMARY KEY(Sno, Cno),/* Define entity integrity at the table level */FOREIGN KEY(Sno)REFERENCESStudent (Sno),/* Define referential integrity at the table level */FOREIGN KEY(Cno)REFERENCESCourse (Cno)/* Define referential integrity at the table level */);

Referential integrity check and default handling
Referential integrity default handling

1. Reject (NO ACTION) execution
   Default Policy

2. Cascading (CASCADE) operations

3. Set to null value (Set-null)
   For referential integrity, you should also define whether the outer code column allows null values in addition to defining the outer code

[Example 4] Explicit description of referential integrity examples of default handlingCREATE TABLESC (SnoCHAR(9) not NULL, CnoCHAR(4) not NULL, GradeSMALLINT，PRIMARY KEY(SNO,CNO),FOREIGN KEY(Sno)REFERENCESStudent (Sno) on DELETE CASCADE/* Cascade delete the corresponding tuple in the SC table */ on UPDATE CASCADE,/* cascade update the corresponding tuple in the SC table */FOREIGN KEY(Cno)REFERENCESCourse (Cno) on DELETE NO ACTION/* Deny delete when deleting tuples in course table due to inconsistent with SC table */ on UPDATE CASCADE/* When updating the CNO in the course table, cascade updates the corresponding tuple in the SC table */);

User-defined integrity is the semantic requirement that data must meet for a specific application
Provided by the RDBMS without the application being assumed

Definition of a constraint on a property

CREATE TABLE时定义列值非空（NOT NULL）列值唯一（UNIQUE）检查列值是否满足一个布尔表达式（CHECK）

1. Null values are not allowed [example 5] when defining the SC table, the SNO, Cno, and grade properties are not allowed to take a null value.      create  TABLE  SC (Sno char  (9 ) NOT  null , Cno char  (4 ) not  null , Grade smallint  not  NULL  , primary  key  (Sno, Cno),/* If the table Level defines entity integrity, which implies that SNO,CNO does not allow null values, the definition of not allowing null values at the column level does not have to be written */);   

2.列值唯一 ［例6］  建立部门表DEPT，要求部门名称Dname列取值唯一，部门编号Deptno列为主码    CREATE TABLE DEPT        (Deptno  NUMERIC(2)，          Dname  CHAR(9)  UNIQUE，/*要求Dname列值唯一*/          Location  CHAR(10)，          PRIMARY KEY (Deptno)       )；

3. Use a check phrase to specify the conditions that the column values should meet [example 7] The ssex of the student table is only allowed to take "male" or "female". CREATE TABLE Student (Sno CHAR(9) PRIMARY KEY, Sname Char(8)  not NULL, Ssex char(2
      ) CHECK (Ssex  in (' Male ', ' female '),/* Gender attribute Ssex only allow ' male ' or ' Female ' */Sage SMALLINT, sdept CHAR;

Constraint checking and default handling on attributes

When you insert a tuple or modify the value of an attribute, the RDBMS checks whether the constraint on the attribute is satisfied
If not satisfied, the operation is denied execution

Definition of constraints on tuples

Constraints on tuples can be defined with a check phrase at CREATE TABLE, which is the tuple-level limit
Tuple-level restrictions can be used to set mutual constraints on values between different attributes, as compared to attribute value limits

[Example 9] When a student's gender is male, its name cannot begin with Ms.CREATE TABLEStudent (SnoCHAR(9), SnameCHAR(8) not NULL, SsexCHAR(2), SageSMALLINT, sdeptCHAR( -)，PRIMARY KEY(Sno),CHECK(ssex=' Woman ' ORSname not  like ' ms.% '/* Defines the constraints between Sname and Ssex two attribute values in tuples */); Sex is a female tuple that can pass this examination because ssex= ' woman ' is established; when sex is male, the name must not be in Ms.

CONSTRAINT 约束CONSTRAINT <完整性约束条件名>［PRIMARY KEY短语   |FOREIGN KEY短语   |CHECK短语］

[Example 10] The establishment of Student registration form student, the requirements of the school number in 90000~99999 between, the name can not take null value, age is less than 30, gender can only be "male" or "female".CREATE TABLEStudent (SnoNUMERIC(6)CONSTRAINTC1CHECK(Sno between90000  and 99999), SnameCHAR( -)CONSTRAINTC2 not NULL, SageNUMERIC(3)CONSTRAINTC3CHECK(Sage < -), SsexCHAR(2)CONSTRAINTC4CHECK(Ssexinch(' man '，' Woman '))，CONSTRAINTStudentkeyPRIMARY KEY(Sno)) On the student table.5Constraints, including the main code constraint (named Studentkey) and the C1, C2, C3, and C4 four column-level constraints.

[Example 13] modify the constraints in the table student, the need to change between 900000~999999, the age from less than 30 to less than 40 can first delete the original constraints, and then add new constraints ALTER TABLE Student DROP CONSTRAINT C1;         ALTER TABLE Student ADD CONSTRAINT C1 CHECK (Sno between 90000 0  and 999999), ALTER TABLE Student DROP Constrain T C3;         ALTER TABLE Student ADD CONSTRAINT C3 CHECK (Sage < +) ;

Database-Database Integrity