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 ,
* ∩( 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

©2019-2020 Toolsou All rights reserved,
Huawei 2021 session Hardware Engineer Logical post (FPGA) Super detailed surface !!!Vue-element-admin upgrade ui edition virtual machine VMware Download and install the most detailed tutorial !C++ Move constructor and copy constructor sound of dripping water java Backstage interview pygame Realize full screen mode and adjustable window size mysql Database setting character set configuration modification my.ini file (windows)30 What's the experience of being a junior programmer at the age of 20 C++ Multithreading programming ( Summary of common functions and parameters )python_ cherry tree