Database and database Learning

Database and database Learning
Database integrity

Data correctness and compatibility

Data integrity and security are two different concepts
Data Integrity
Prevent the database from having non-semantic data, that is, prevent the database from having incorrect data.
Prevention object: Non-semantic and incorrect data
Data security
Protects the database against malicious damages and illegal access
Intended audience: illegal users and illegal operations
To maintain database integrity, DBMS must:
1. provides a mechanism to define integrity constraints
2. Provide methods for integrity check
3. Default handling

Entity integrity

Entity integrity Definition
Entity integrity of a link model
Use primary key in CREATE TABLE
There are two methods to describe the code composed of a single attribute
Column-level constraints
Table-level constraints
There is only one method to describe the code composed of multiple attributes.
Table-level constraints

[Example 1] defining the Sno attribute in the Student TABLE as code (1) Defining the PRIMARY code at the 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 primary code create table Student (Sno CHAR (9), Sname CHAR (20) not null, Ssex CHAR (2), Sage SMALLINT, sdept CHAR (20), primary key (Sno ));

[Example 2] define the Sno and Cno attribute groups in the SC table as codes create table SC (Sno CHAR (9) NOT NULL, Cno CHAR (4) NOT NULL, Grade SMALLINT, primary key (Sno, Cno)/* The PRIMARY code can only be defined at the table level */);

Entity integrity check and default handling
When the primary code column is inserted or updated, RDBMS automatically checks the primary code column according to the entity integrity rules. Including:
1. Check whether the primary code value is unique. If it is not unique, insertion or modification is rejected.
2. Check whether the attributes of the master code are empty. If one of them is empty, insertion or modification is denied.

Integrity of reference

Definition of integrity
Definition of link model integrity
In create table, use the foreign key phrase to define which columns are external codes.
Use the REFERENCES phrase to specify which tables the external codes refer.

For example, a tuples In the relation SC indicate the scores of a course selected by a student, and (Sno, Cno) indicate the primary code. Sno and Cno define the reference integrity create table SC (Sno CHAR (9) NOT NULL, cno CHAR (4) not null, Grade SMALLINT, primary key (Sno, Cno),/* define entity integrity at the table level */foreign key (Sno) REFERENCES Student (Sno ), /* Define reference integrity at the table level */foreign key (Cno) REFERENCES Course (Cno)/* Define reference integrity at the table level */);

Integrity check and default handling
Integrity default handling

[Example 4] explicit description Reference integrity default handling example create table SC (Sno CHAR (9) NOT NULL, Cno CHAR (4) NOT NULL, Grade SMALLINT, primary key (Sno, Cno), foreign key (Sno) REFERENCES Student (Sno) on delete cascade/* cascade delete the corresponding tuples in the SC table */ON UPDATE CASCADE, /* cascade updates the corresponding tuples in the SC table */FOREIGN KEY (Cno) REFERENCES Course (Cno) on delete no action/* When the tuples in the course table are deleted and the SC table is inconsistent */ON UPDATE CASCADE/* When the cno in the course table is updated, cascade updates the corresponding tuples in the SC table */);

User-Defined integrity is the semantic requirement that data of a specific application must meet.
Provided by RDBMS without being undertaken by the application

Attribute Constraints

When creating TABLE, the column value is defined as non-NULL (not null). The UNIQUE column value (UNIQUE) checks whether the column value meets a Boolean expression (CHECK)

1. null values are not allowed. [Example 5] when defining the SC table, null values are not allowed for the Sno, Cno, and Grade attributes. 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 entity integrity is defined, Sno is implied, and Cno does not allow null values, the column-Level Definition of null values is not allowed */);

2. the column value is unique [Example 6]. The Department table dept must be created. The Department name Dname column must have a unique value. The Department Number Column Deptno is the primary code create table dept (Deptno NUMERIC (2 ), dname CHAR (9) UNIQUE,/* requires that the value of the Dname column be UNIQUE */Location CHAR (10), primary key (Deptno ));

3. Use the CHECK phrase to specify the conditions that the column value should meet. [Example 7] Ssex of Student table only allows "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 attributes Ssex only allow 'male' or 'female '*/Sage SMALLINT, Sdept CHAR (20 ));

Attribute constraints check and default handling

When you insert a tuple or modify the attribute value, RDBMS checks whether the attribute constraints are met.
If not, the operation is denied.

Define constraints on tuples

When creating a TABLE, you can use the CHECK phrase to define the constraints on the tuples, that is, the restriction on the metagroup level.
Compared with attribute value restrictions, you can set mutual constraints for values of different attributes at the meta-group level.

[Example 9] when a student is male, his/her name cannot start with Ms. Create table Student (Sno CHAR (9), Sname CHAR (8) not null, Ssex CHAR (2), Sage SMALLINT, Sdept CHAR (20), primary key (Sno ), CHECK (Ssex = 'female 'OR Sname not like 'Ms. % ')/* defines the constraints between the values of Sname and Ssex in the tuples */); gender means that all tuples of women can pass this check because Ssex = 'female' is true; when the gender is male, the name must not be Ms. headers

CONSTRAINT <Integrity CONSTRAINT Condition Name> [primary key phrase | foreign key phrase | CHECK Phrase]

[Example 10] Create a Student registration form Student with a Student ID ranging from 90000 ~ Between 99999, the name cannot be null, the age is less than 30, the gender can only be "male" or "female ". Create table Student (Sno NUMERIC (6) CONSTRAINT C1 CHECK (Sno BETWEEN 90000 AND 99999), Sname CHAR (20) 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 )); five constraints are created on the Student table, including the primary code constraint (named StudentKey) and four column-level constraints: C1, C2, C3, and C4.

[Example 13] modifying the constraints in Student requires that the Student ID be changed to 900000 ~ Between 999999, if the age is changed from less than 30 to less than 40, you can first Delete the original CONSTRAINT, and then add the new constraint alter table Student drop constraint C1; alter table Student add constraint C1 CHECK (Sno BETWEEN 900000 AND 999999), alter table Student drop constraint C3; alter table Student add constraint C3 CHECK (Sage <40 );