Document directory
- 4) Check Integrity Constraint rules-how to execute Integrity Constraint rules in actual operations
Introduction to database application development-basic concepts in relational databases are written before: relational databases are currently the most widely used databases. Understanding the basic concepts of relational databases helps application development. 1. Basic Concepts in relational databases are databases built on relational models. They process data in databases by means of mathematical concepts and methods such as set algebra. In the real world, the relationships between entities and entities are expressed by relational models. The basic concepts involved in relational databases (the code and the key are the same, and the master code is the primary key) are as follows:
Relationship ):A link corresponds to a table.
Tuple ):A row in the table is a tuples.
Attribute ):A column in the table is an attribute, and each attribute is named
Candidate Key): If the value of an attribute group in a link uniquely identifies a tuples, this attribute group is called a candidate code. In simple cases, the candidate Code only contains one attribute.
Full code (all-key): In the most extreme case, all attribute groups in the link mode are candidate codes of the link mode, called all-key ).
Primary Key): An Attribute Group in a table that uniquely identifies a tuples. If a link has multiple candidate codes, select one of them as the master code.
External code (foreign key): For more information about the concept, see integrity.
Prime Attribute): The attributes of candidate codes are called prime attributes)
Non-Prime Attribute): An attribute that is not included in any candidate code is called a non-Prime Attribute or a non-key attribute)
Domain): The value range of the attribute.
Component: An Attribute Value in the tuples.
Link Mode: Description of the link, which is formally represented as: Link name (attribute 1, attribute 2 ,..., Attribute N)
For example, student (student ID, name, age, gender, department, grade) 2. three types of integrity relationship data models are composed of three basic parts: relationship data structure, relationship operation, and Integrity Constraint rules in the relationship. Focus on understanding three types of Integrity Constraint rules and link operations. There are three types of integrity constraints in the relational model: entity integrity, referential integrity, and user-defined integrity.
1) entity integrity rules: If attribute a is the primary attribute of relational R, a cannot take null values.
Entity integrity rules stipulate that the attribute (that is, the primary attribute) in the primary code of a link cannot be null. Null is neither 0 nor a null string, but has no value. In other words, the so-called null value is the value of "unknown" or "meaningless. Because the primary code is the only identifier of an object, if the primary attribute is null, an unidentifiable entity exists in the link, that is, an entity that cannot be distinguished. This conflicts with the definition of an object, therefore, this rule is called an entity integrity rule.
Note: The entity integrity rules stipulate that each attribute of all primary keys in the basic relationship cannot be null, not only the primary key cannot be null as a whole.For example, in the Course Selection (course number, course number, and score) relationship, the "student ID" and "course number" in the attribute group are the primary keys and are also the primary attributes, the two attributes cannot be null. 2) integrity of reference
A. External code and reference relationships (important concepts)
Set F to one or more attributes of the basic relational R, but not the primary code (or candidate Code) of the relational R ). If f corresponds to the primary key ks of the basic link S, it is called F as the External Key of the basic link R, and the basic link R is called a referencing relation ), it is called the basic link S as the referenced Link (referenced relation ).
For example, the model of instructor-related teaching is as follows:
Course (
Course No., Course name, credits)
Instructor (employee ID, name, title,
Course No.)
Reference books (book number, title,
Course No.)
The "course number" in the relationship instructor is not the primary code. This attribute corresponds to the "course number" in the relationship course. Therefore, the "course number" is the external code of the relationship teacher. Relationship teachers refer to relationships, and relationship courses refer to relationships.
B. Reference integrity rulesReference integrity rules: If attribute (or attribute group) F is an external code of the basic link R, it corresponds to the primary code ks of the basic link S, the values of each tuples in R on F must meet the following requirements: Take null values (each attribute of F is null), or equal to the primary code value of a tuples in S.
For example
External code "course No"It can only be the following two types of values:
(1) null value. The instructor has not been assigned a course.
(2) A non-null value, but this value must be the "course number" of a course in the referenced link course ". In relational databases, the relationship between tables is achieved through public attributes. This public attribute is usually the primary code of a table and the external code of another table.
Note: in practice, the external code does not necessarily have the same name as the corresponding primary code.3) User-Defined integrity all relational database systems should support Entity integrity and reference integrity. In addition, relational database systems often need additional constraints based on different application environments in the real world. User-Defined integrity is a constraint defined for a specific requirement. It reflects the semantic requirements that must be met by the data involved in a specific application.
For example, a property must have a unique value, some attributes must meet certain functional relationships, and some attributes must have a value range between 0 and. The relational model should provide a mechanism to define and test such integrity so that the system can process them in a unified way, without the need for applications to undertake this function. 4) Check integrity constraints rules-how to execute integrity constraints rules in actual operations to maintain data integrity in the database, when performing insert, delete, and modify operations on the relational database, check whether the above three types of integrity rules are met.
(1) When performing the insert operation, first check the entity integrity rules and whether the values on the primary code attribute of the inserted row already exist. If it does not exist, you can perform the insert operation. Otherwise, you cannot perform the insert operation. Check the integrity rules again. If you want to insert a reference link, you do not need to consider the integrity rules. If you want to insert a reference link, whether the value of the row's outer code attribute already exists in the primary code attribute value of the corresponding referenced relationship. If yes, you can perform the insert operation. Otherwise, you cannot perform the insert operation, you can also change the value of the outer code attribute of the inserted row to a null value before performing the insert operation (assuming that the outer code allows null values ). Finally, check the User-Defined integrity rules, and check whether the inserted relationship defines the User-Defined integrity rules. If yes, check whether the values of the inserted rows in the corresponding attributes comply with the User-Defined integrity rules. If yes, you can perform the insert operation. Otherwise, you cannot perform the insert operation.
(2) When performing the delete operation, you generally only need to check the integrity rules. If the row is deleted from the reference link, check whether the value of the deleted row in the master code attribute is being referenced by the external code of the corresponding reference link. If the row is not referenced, you can perform the delete operation. If the operation is being referenced, there are three possible methods: you cannot perform the delete operation (delete is denied ), you can also change the value of the external code attribute of the corresponding row in the reference link to a null value, and then perform the delete operation (null value deletion), or delete the corresponding row in the reference Link (cascade deletion ).
(3) When performing the modification operation, because the modification operation can be regarded as executing the delete operation first and then executing the insert operation, it is a combination of the above two situations.
3. the Operations used by relational databases on relational models can be expressed by abstract relational algebra and relational calculus. This part involves simple mathematical problems such as set operations, which are represented by abstract symbols, the ability to express queries, updates, control, and other operations in a specific language is very useful for understanding database operations. I will not describe them too much here. I 'd like to feel it when I study SQL statements later. 4. References (1) database principle and development and application (2) http://shujuku.zjwchc.com/
