Database and database Learning

Database and database Learning
Relational Database

The relational model proposed is E. f. codd (Edgar Frank Codd, 1923-2003). "Father of Relational Database" proposed the Relational Data Model E in 1970. f. codd, "A Relational Model of Data for Large Shared Data Banks", Communication of the ACM, 1970E. f. codd was born in October 1923 in a big family in Portland, dorsse-gun, England. He once studied at Oxford University, majoring in mathematics and obtained a bachelor's and a Master's degree. Served in the Royal Air Force during the Second World War. After the Second World War, I left for the United States to become a programmer of IBM. At the age of 40, I went back to campus, majored in computer and communication at the University of Michigan, and obtained a doctorate in 1965. The outstanding contributions to the theory and practice of the database management system won the Turing Award in 1981. Retired from IBM in 1984, and Xie Shi in 2003.

Link

Single Data Structure-Relationship
Real-world entities and various relationships between entities are expressed by relationships.
Logical Structure-sequence table
From the user's perspective, the logical structure of data in the relational model is a two-dimensional table.
The relational model is built on the basis of the set algebra. The formal definition of the relational data structure is given from the perspective of the set theory.

Domains)

A domain is a set of values of the same data type. For example, a string set of integer real numbers with an integer length less than 25 bytes in a certain value range: {'mal', 'female '}.................

Descartes give a group of domains D1, D2 ,..., Dn. These domains can have the same. D1, D2 ,..., The Cartesian product of Dn is: D1 × D2 ×... × Dn = {(d1, d2 ,..., Dn) | diDi, I = 1, 2 ,..., N} each element (d1, d2 ,..., Dn) is called an n-tuple or Tuple (Zhang qingmei, Computer Major, LI Yong), (Zhang qingmei, Computer Major, Liu Chen) are the Cartesian Product elements (d1, d2 ,..., Each di value in dn is called a component, such as Zhang qingmei, computer science, LI Yong, and Liu Chen. It is the Cardinal number) cartesian Product Representation Method Cartesian product can be expressed as a two-dimensional table, each row corresponds to a tuple, each column in the table corresponds to a field

1) relationship D1 × D2 ×... * The subset of Dn is called in the domain D1, D2 ,..., The relationship on the Dn, expressed as R (D1, D2 ,..., Dn) R: Link name n: the element of the link, or the object or Degree (Degree) 2) each element in the tuples is a tuples in the Link, usually represented by t. 3) WHEN n = 1, the relationship is called Unary relation or mona1 when n = 2, this relationship is called Binary relation (Binary relation) 4) the relationship is also a two-dimensional table, each row of the table corresponds to a tuples, each column of the table corresponds to a field 5) different columns in an Attribute relationship can correspond to the same domain. to distinguish them, each column must be named Attribute n (Meta). There must be n attributes 6) candidate key: if the value of an attribute group in a link uniquely identifies a tuple, this attribute group is called a candidate code (is the definition given in the textbook rigorous ?) In simple cases, the candidate Code only contains one All-key attribute. the most extreme case is that All attribute groups in the relational model are candidate codes in this relational model, it is called full-key (All-key) 6) code Candidate code (Candidate key). If the value of an attribute group in a link uniquely identifies a tuple, this attribute group is called a candidate code (is the definition given in the textbook rigorous ?) In simple cases, the candidate Code only contains one All-key attribute. the most extreme case is that All attribute groups in the relational model are candidate codes in this relational model, it is called All-key D1, D2 ,..., Only a subset of the Cartesian product of Dn has actual meanings. For example, the Cartesian product of Table 2.1 has no practical significance to extract the actually meaningful tuples to construct the relational relationship: SAP (SUPERVISOR, SPECIALITY, POSTGRADUATE) suppose: Tutor and major: 1: 1, tutor and graduate student: 1: n master code: POSTGRADUATE (assuming that the graduate student will not duplicate the name) SAP relationship can contain three tuples {(Zhang qingmei, computer Major, LI Yong), (Zhang qingmei, Computer Major, Liu Chen), (Liu Yi, information major, Wang Min)} 7) Basic relationships (basic tables or basic tables) the actual table is the logical representation of the actually stored data. The table view chart corresponding to the query result is a virtual table exported from the basic table or other view charts, does not correspond to the data actually stored 8) the nature of the basic relationship ① The column is Homogeneous (Homogeneous) ② different columns can come from each column in the same domain. Each column is called an attribute. different attributes must be given different attribute names. ③ the sequence of columns does not matter, the column order can be exchanged arbitrarily. ④ the candidate codes of any two tuples cannot be the same. ⑤ the order of rows does not matter.

Link Mode

Relation Schema is a type
Link is value
The link mode is the description of the link.
Structure of the set of tuples
Attribute Composition
The domain from which the attribute is located.
Relationship between attributes and Domains
Tuples semantics and integrity constraints
Set of data dependencies between attributes

The relational model can be formally expressed:
R (U, D, DOM, F)
R Link name
The property name set of The Link consisting of U
D. The domain from which the attribute belongs in the U Attribute Group
Set of DOM attributes to the domain
Data dependency set between F attributes
Example:
Instructors and graduate students are from the same domain-persons,
Take different attribute names and define the attribute to the domain in the Mode
Which indicates the domain from which they are:

   DOM（SUPERVISOR-PERSON）= DOM（POSTGRADUATE-PERSON）=PERSON

The link mode can be abbreviated
R (U) or R (A1, A2 ,..., An)
R: Link name
A1, A2 ,..., An: attribute name
Note: domain name and Attribute-to-domain images are often directly described
Attribute type and length

Relational Data Structure

Relational Database: relational database model
Description of the relational database.
The relational database modes include:
Definition of several domains
Several relational modes defined on these domains
Relational Database Value: a set of relational databases corresponding to a relational model at a specific time point.
Common relational operations
Query: selection, projection, join, division, union, intersection, difference, Cartesian Product
Data Update: insert, delete, and modify
The query expression capability is the most important part.
Selection, projection, and union, difference, and Cartesian product are five basic operations. Other operations can be exported by basic operations.
Features of relational operations
Set operation method: The operation object and result are both set, one set at a time
Relational algebra language
Use relational operations to express query requirements
Representative: ISBL
Relational algorithm language: Use predicates to express query requirements
Tuples relational algorithm language
The basic object of the predicate variable is the tuple variable.
Representatives: APLHA, QUEL
Domain relational algorithm language
The basic object of the predicate variable is the domain variable.
Representative: QBE
Relational algebra and relational Calculus
Representative: SQL (Structured Query Language)

Three types of integrity constraints

Entity integrity and referential integrity:
Integrity constraints that must be met by the relational model
Two immutability relationships are automatically supported by the relational system.
User-Defined integrity:
The constraints that need to be observed in the application field reflect the semantic constraints in the specific field

Entity integrity

Rule 2.1 Entity Integrity rule (Entity Integrity)
If attribute A is the primary attribute of basic link R, attribute A cannot be null.
Example:
SAP (SUPERVISOR, SPECIALITY, POSTGRADUATE)
POSTGRADUATE:
Master code (assuming that graduate students do not duplicate names)
NULL values cannot be obtained.

Description of entity integrity rules
(1) entity integrity rules are for basic relationships. A basic table usually corresponds to an entity set in the real world.
(2) entities in the real world are differentiated, that is, they have some unique identifier.
(3) The primary code is used as the unique identifier in the relational model.
(4) The attribute in the master code means that the master attribute cannot be null.

Integrity of reference

Example 2: many-to-many connections between students, courses, and students and courses
Student (student ID, name, gender, Major ID, age)
Course (course number, course name, credits)
Electives (student ID, course number, score)
Set F to one or more attributes of the basic relational R, but not the code of the relational R. If F corresponds to the primary key of the basic link S, F is the external key of the basic link R.
Referencing Relation)
The basic Relation S is called a Referenced Relation)
Or Target Relation)
Integrity rules
If attribute (or attribute group) F is the external code of the basic relationship R, it corresponds to the primary code Ks of the basic relationship S (the basic relationship R and S are not necessarily different ), the value of each tuples in R on F must be:
Or take null values (each attribute value of F is null)
Or equal to the primary code value of a tuples in S.

[Example 1]: The "professional number" attribute of each tuples in the student relationship takes only two types of values: (1) null, indicating that the student has not been assigned a major (2) non-null value, this value must be the "professional number" value of a tuples in the professional relationship, indicating that the student cannot assign a non-existent major.

(Example 2): Optional values of "student ID" and "course number": (1) primary attributes in the elective relationship, NULL values cannot be obtained. (2) only the primary code values that already exist in the referenced relationship can be obtained.

(Example 3): the student (student ID, name, gender, Major ID, age, and shift leader) has two attribute values: (1) null value, this indicates that the student's class has not selected the shift leader (2). the value must be the student ID of a tuples in this relationship.

User-Defined integrity

The constraints of a specific relational database reflect the semantic requirements that must be met by the data involved in a specific application.
Relational Models should provide mechanisms to define and test such integrity so that they can be processed in a unified and systematic manner, rather than undertaken by applications.

Example: Course (course number, course name, credits) the "course number" attribute must have a unique value. The "Course name" attribute cannot have a null value. The "Credits" attribute can only be set to {1, 2, 3, 4}