one , Brief introduction of relational model

1.1 The proposal of relational model

* First by E.F.Codd stay 1970 Proposed in
* It's from the table (Table) And table processing methods , It is based on the strict mathematical definition of traditional table and its operation , This paper introduces set theory and logic theory
* It is one of the three classic data models of database , It is also the data model still used by most commercial database systems
* Standard database language (SQL language ) It is based on the relational model , Many theories in database field are also based on relational model
1.2 What does relational model study

* Image theory , A relationship (relation) That's one Table
* Relational model is processing Table Of , It consists of three parts
* describe DB The basic structure of all kinds of data (Table/Relation)
* describe Table And Table All kinds of operations that may occur between （ Relational operation ）
* Describe the constraints that these operations should follow （ integrity constraint ）

1.3 Three elements of relational model

* Basic structure ：Relation/Table
* basic operation ：Relation Operator
* υ( and , UNION),-( difference , DIFFERENCE),×( Generalized product , PRODUCT),σ( choice , SELECTION),∏( Projection ,
PROJECTION)
* ∩( hand over , INTERSECTION),⋈( connect , JOIN),÷( except , DIVISION) operation
* integrity constraint ： Entity integrity , Referential integrity and user-defined integrity
1.4  Relationship between relational model and database language

* Relational operation ： Relational algebra and relational calculus ; Relational calculus ： Tuple calculus and domain calculus .
* Examples of relational algebra ： Set based operations
*
Namely ： The object and result of an operation are collections , It's one set at a time （Set-at-a-time） Operation of . The non relational data operation is usually one record at a time （Record-at-a-time） Operation of

* Database language based on relational algebra design （ISBL）： Using computer recognizable symbols to represent operational symbols of relational algebra

* Example of tuple calculus ： Operation based on logic

* Database language based on relational tuple calculus design (Ingres Systematic QUEL)： Representation of operational symbols of tuple calculus with computer recognizable symbols range of t is R range
of u is W retrieve t where t.sage < u.sage
* Examples of field calculus ： Example based operations

* An example of database language based on domain calculus design ：QBE（Query By Example）

1.5 Why learn relational model and relational database language

two , What is relationship

2.1 “ surface ” The basic elements of the system

2.2 “ surface ” The strict definition of -- relationship

2.2.1 definition “ column ” Value range of “ field (Domain)”

* field （Domain）
* A set of values , This set of values has the same data type
* Such as the set of integers , A collection of strings , A collection of all the students
* Another example , from 8 A set of digit strings consisting of digits , from 0 reach 100 A set of integers
* The number of elements in a set is called the cardinality of the field (Cardinality)

2.2.2 " tuple " And all possible tuples ： Cartesian product

* Cartesian product （Cartesian Product）
* The Cartesian product of a set of fields is ：
* Each element of the Cartesian product is called a n- tuple （n-tuple）
* Each value of a tuple is called a component （component）
* Tuple is a combination formed by taking any value from each field , The Cartesian product is the set of all these possible combinations , Namely ： The Cartesian product is made up of n All the possible n- Set of tuples
* If the cardinal number is , Then the cardinality of Cartesian product , That is, the number of tuples is

2.2.3 relationship

* Subsets of Cartesian products of a set of fields
* The tuples of elements in Cartesian product which have some meaning are called a relation （Relation）
* Columns may come from the same domain due to different relationships , In order to distinguish , You need to name each one , The name is the property name
* Relationships can be represented , It can be abbreviated as , This description is also called relational schema （Schema） Or the title （head）
* R It's the name of the relationship , It's a property , Is the domain corresponding to the property ,n It's the degree or purpose of the relationship (degree), The number of tuples in a relationship is called the cardinality of the relationship (Cardinality)
* The mapping of attributes to domains in a relational schema can be seen in many ways DBMS It is usually directly described as the type of property in , Length, etc

2.2.4 Relationship model and relationship

* Under the same relationship mode , There are a lot of relationships
* Relationship pattern is the structure of relationship , A relationship is the data of a relational schema at a certain time
* The relationship model is stable ; And the relationship is the value of a moment , It can change over time

2.3 Characteristics of relationships

* Columns are homogeneous ： That is, the components in each column come from the same field , Is the same data type

* Different columns can come from the same domain , Call each of these columns a property , Different attributes should be given different attribute names
* In relation mode , It has to be different , And it can be the same
* for example ： We define a domain as Person= All men , A collection of women and children ={ Li Ji , Zhang
Peng , Wang Fang , Liu Yu , Li Jian , Zhang Rui , Zhang Feng }, Then “ family ” The three columns of the relationship will come from the same domain Person, Therefore, different property names are required “ husband ”“ wife ”“ children ” In order to distinguish

* Column position interchangeability ： Which column is distinguished by its name
* Row position interchangeability ： Distinguish which row depends on the value of a column or columns （ keyword / Key word / Codeword ）
* Relationship is based on content （ Name or value ） To distinguish , It's not the position of the attribute in the relationship
* For example, the following two relationships are identical

* theoretically , Any two tuples of a relation cannot be exactly the same .（ Set requirements ： You cannot have two identical elements in a collection ）; In practical application , surface (Table) This feature may not be fully adhered to
* The same tuple means that each component of two tuples is the same

* Attribute non separability ： It is also called the first paradigm of relationship

2.4 Some important concepts of relationship

2.4.1 Candidate code / Candidate key

* An attribute group in a relationship , Its value can uniquely represent a tuple , If any attribute is removed from the attribute group , It does not have this property , Such attribute groups are called candidate codes .
* for example ：“ student (S#, Sname, Sage, Sclass)”,S# It's a candidate , Here
In the relationship , Of any two tuples S# It must be different , And these two tuples are Sname, Sage, Sclass It could all be the same ( The same name , Same age , classmate ), therefore S# It's a candidate .
* In, for example ：“ Course selection (S#, C#, Sname, Cname, Grade)”,(S#,C#) It's a combination Candidate code
* Sometimes , There are many groups of candidates in the relationship
* for example ： student (S#, Sname, Sage, Sclass, Saddress), Where attributes S# It's a candidate , Attribute group (Sname,
Saddress) It's also a candidate ( Same name and same place The two students in the address don't exist )
* In, for example ：Employee(EmpID, EmpName, Mobile), Every employee has a unique EmpID, No two employees have the same mobile phone number Mobile, be
EmpID It's a candidate ,Mobile It's also a candidate
2.4.2 Main code (Primary Key)/ Primary key

* When there are multiple candidates , You can select one as the main code
* DBMS Main code is used as main clue to manage tuples in relation
* for example ： Optional attributes S# As “ student ” Main code of table , You can also select attribute groups (Sname,
Saddress) As “ student ” Main code of table . selected EmpID by Employee Main code of .
2.4.3 Primary attribute and non primary attribute

* The attribute contained in any candidate code is called the primary attribute , Other properties are called non primary properties
* for example ：“ Course selection ” In S# , C# Primary attribute , and Sname, Cname, Grade Is a non primary property ;
* The simplest , The candidate code contains only one attribute
* The most extreme , All the attributes make up the candidate code of this relationship , It's called full code (All-Key)
* such as ： relationship “ Teaching by teachers ”(T#,C#) Candidates in (T#,C#) It's full code
2.4.4 External code / Foreign key

* relationship R A property group in , It's not R Candidate code for , But it has another relationship S Corresponding to the candidate code of , This property group is called R Foreign code or key of
* for example ：“ contract ” The customer number in the relationship is not a candidate , But it's a foreign code . Because it's related to “ customer ” shut Candidate codes in the system “ Customer number ” Corresponding .
* The two relationships are usually connected by external codes

three , Integrity in relational model

3.1 Entity integrity

* The attribute value in the main code of a relationship cannot be null
* Null value ： Unknown or meaningless values
* significance ： The tuples in the relationship correspond to the distinguishable individuals in the real world , These individuals are uniquely identified by the main code ; If the main code is empty , There are unidentifiable individuals , This is not allowed .

3.2 Null value and its meaning

* Meaning of null value
* Null value ： hear nothing of , A nonexistent or meaningless value
* When performing relational operations , Sometimes an attribute value in a relationship cannot be filled in at present , For example, there are “ Birthday unknown ”,“ One's whereabouts is a mystery. ”,“ The schedule has yet to be announced ” etc. , That's where it's needed
A null value represents this situation . In relation model ‘?’ characterization
* There is a null value in the database , It will affect many aspects , For example, the accuracy of aggregate function operation is affected , You can't take part in arithmetic , Comparison logic operation, etc
* for example ：“3 + ?” What was the result ? “3 * ?” What was the result ? “? and (A=A)” What's the result ?
* Another example ： There are many students in one class 30 Students , If all the students have achievements , Then the average value can be obtained achievement ; If one of the students has no grades , How to participate in the calculation of the average score , Is as
0, Or as 100 What about it ? Or don't you think about him ?
* Special processing is needed when there are null values , Pay special attention
3.3 Referential integrity

* If the relationship R1 The external code of Fk Relationship with R2 Main code of Pk Corresponding , be R1 Of each tuple in Fk Value or equal to R2 Of a tuple in Pk value , Or null
* significance ： If the relationship R1 A tuple of t1 Reference is made to the relationship R2 A tuple of t2, be t2 Must exist
* For example, relationships Student stay D# There are two possibilities for the value of
* Null value , Indicates that the student has not been assigned to any department
* If not null , It must be Dept Of a tuple in a relation D# value , It means that the student cannot be assigned to a non-existent department

3.4 User defined integrity

* User defined integrity constraints for specific application environments
* for example ：S# The requirement is 10 Bit integer , Among them, the top four are annual , Current year and their The difference must be between 4 within
* Another example ：

3.5 DBMS Support for relationship integrity

* Entity integrity and referential integrity are controlled by DBMS Automatic system support
* DBMS The system usually provides the following mechanisms ：
* It allows users to define the integrity constraints themselves
* When an update operation occurs ,DBMS The correctness of the update operation is automatically verified against integrity constraints , That is, whether it conforms to the user-defined integrity

Technology
Daily Recommendation
views 4