Database-relational database

relational database

提出关系模型的是美国IBM公司的E.F.Codd （Edgar Frank Codd，1923－2003) , “关系数据库之父”1970年提出关系数据模型E.F.Codd, “A Relational Model of Data for Large Shared Data Banks”, 《Communication of the ACM》,1970E.F.Codd 1923年出生在英格兰多塞特郡波特兰市的一个大家庭中。他曾经就读于牛津大学，主修数学，获得学士与硕士学位。第二次世界大战期间曾在皇家空军服役。第二次世界大战后，动身前往美国成为IBM的一名程序员。在40岁的时候，重返校园，在密歇根大学主修计算机与通信专业，于1965年获得博士学位。因为在数据库管理系统的理论和实践方面的杰出贡献于1981年获图灵奖。 1984年从IBM退休， 于2003年谢世。

Relationship

A single data structure--relationship
The relationships between real-world entities and entities are expressed in relation to each other.
Logical Structure--two-dimensional table
From the user's perspective, the logical structure of data in a relational model is a two-dimensional table
The relationship model is based on set algebra, and the formal definition of relational data structure is given from the angle of set theory.

⒈ domains (domain)

域是一组具有相同数据类型的值的集合。例:整数实数介于某个取值范围的整数长度小于25字节的字符串集合{‘男’，‘女’}……………..

1. Cartesian product (Cartesian product)

笛卡尔积   给定一组域D1，D2，…，Dn，这些域中可以有相同的。    D1，D2，…，Dn的笛卡尔积为：    D1×D2×…×Dn ＝             ｛（d1，d2，…，dn）｜di?Di，i＝1，2，…，n｝所有域的所有取值的一个组合 元组（Tuple）笛卡尔积中每一个元素（d1，d2，…，dn）叫作一个n元组（n-tuple）或简称元组(Tuple)(张清玫，计算机专业，李勇)、(张清玫，计算机专业，刘晨)等都是元组 分量（Component）笛卡尔积元素（d1，d2，…，dn）中的每一个值di叫作一个分量张清玫、计算机专业、李勇、刘晨等都是分量 基数（Cardinal number）笛卡尔积的表示方法笛卡尔积可表示为一个二维表表中的每行对应一个元组，表中的每列对应一个域

1. Relationship (Relation)

1A subset of the relationship D1xd2x...xdn is called a relationship on the domain D1,D2,...,DN, expressed as R (D1,D2,...,DN) r: Relationship name N: The element of the relationship, or the degree (degree)2Each element in a tuple relationship is a tuple in a relationship, usually expressed in T.3) Unit relationship with two-yuan relationship when n=1, the relationship is called a cell relationship (unary relation) or a unary relationship when n=2, the relationship is said to be a two-yuan relationship (binary relation)4The representation of a relationship is also a two-dimensional table, each row of the table corresponds to a tuple, and each column of the table corresponds to a field5The different columns in the attribute relationship can correspond to the same field in order to differentiate, you must have a name for each column, called the attribute (Attribute) n-Mesh (yuan) relationship will have n attributes6) Code candidate (candidate key) if the value of an attribute group in a relationship uniquely identifies a tuple, the attribute group is called a candidate code (is the definition given in the textbook rigorous?). Simple case: The candidate code contains only one attribute full code ( All-keyThe most extreme case: all attribute groups of the relational pattern are candidates for this relational pattern, called full yards ( All-key）6) Code candidate (candidate key) if the value of an attribute group in a relationship uniquely identifies a tuple, the attribute group is called a candidate code (is the definition given in the textbook rigorous?). Simple case: The candidate code contains only one attribute full code ( All-keyThe most extreme case: all attribute groups of the relational pattern are candidates for this relational pattern, called full yards ( All-keyA subset of the Cartesian product of the D1,D2,...,DN is a practical example: Table2.1The Cartesian product has no practical significance to pull out meaningful tuples to construct relationships: SAP (supervisor,speciality,postgraduate) hypothesis: Mentor and Professional:1:1, Tutors and graduate students:1: N Main code: postgraduate (assuming graduate students do not have the same name) SAP relationships can contain three tuples {Qing Zhang Mei, computer professional, Li Yong), (Qing Zhang Mei, computer professional, Liu Chen), (Liu Yi, Information Professional, Wang Min)}7) A three-class relationship basic relationship (base table or base table) actually exists in the table, which is the logical representation of the actual data stored in the query table query results Corresponding table View chart is derived from the base table or other View chart table, is a virtual table, does not correspond to the actual stored data8The nature of the basic relationship ① column is homogeneous (homogeneous) ② different columns can originate from the same field where each column is called a property different properties to give different property names ③ The order of the columns does not matter, the sequence of columns can be exchanged arbitrarily ④ any two tuple candidate code cannot be the same ⑤ the order of the rows does not matter, The order of the rows can be exchanged arbitrarily

Relationship Mode

Relational schema (Relation schema) is type
Relationship is a value
The relationship pattern is a description of the relationship
Structure of a tuple collection
Attribute composition
The domain from which the property is derived
Image relationship between attribute and domain
Tuple semantics and integrity constraints
Collection of data dependencies between attributes

The relational pattern can be formally expressed as:
R (U,D,DOM,F)
R Relationship Name
The set of property names that make up the relationship
The domain from which the attribute in the D attribute group U comes from
The image collection of the DOM attribute to the domain
Collection of data dependencies between F attributes
Cases:
Mentors and graduate students come from the same domain--people,
Attribute names, and define attributes in the schema to the domain
to indicate which domain they were from:

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

Relational patterns can usually be précis-writers to
R (U) or R (A1,a2,...,an)
R: Relationship Name
A1,a2,...,an: Property Name
Note: domain names and attribute-to-domain mappings are often directly described as
Type, length of the property

Relational data structures

Type of relational database: relational database schema
A description of the relational database.
The relational database schema includes
Definition of several domains
Several relationship patterns defined on these domains
The value of a relational database: the set of relationships that the relationship pattern corresponds to at some point, referred to as the relational database
Common relationship Operations
Query: Selection, projection, connection, addition, and, intersection, difference, Cartesian product
Data updates: Inserting, deleting, modifying
The expression ability of a query is the most important part
Selection, projection, and, difference, Cartesian product is 5 basic operations, other operations can be exported by the basic operation
Characteristics of relational operations
Set operation mode: the object and result of the operation is a collection, one-time collection method
Relational algebra language
Use the operation of the relationship to express the query request
Representative: ISBL
Relational calculus language: Using predicates to express query requirements
Tuple Relational Calculus language
The basic object of a predicate argument is a tuple variable
Representative: Aplha, Quel
Domain Relational Calculus language
The basic object of a predicate argument is a domain variable
Representative: QBE
Language with the dual characteristics of relational algebra and relational calculus
Representative: SQL (structured Query Language)

Three types of integrity constraints for relationships

Entity integrity and referential integrity:
The integrity constraints that a relational model must meet
Two invariants, called relationships, should be automatically supported by the relational system
User-defined integrity:
The constraints that need to be followed in the field of application, which embody the semantic constraints in specific areas

Entity integrity

Rule 2.1 Solid Integrity rules (entity Integrity)
If attribute A is the primary property of the basic relationship R, then property a cannot take a null value
Cases:
SAP (Supervisor,speciality,postgraduate)
Postgraduate:
Master code (assuming the graduate does not have duplicate names)
Cannot take null value

Description of the Entity integrity rule
(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 distinguishable, that is, they have some kind of uniqueness identity.
(3) The main code is identified as the uniqueness in the relational model.
(4) The attribute in the main code is the primary attribute and cannot be empty.

Referential integrity

Example 2 Many-to-many linkages between students, courses, students and courses
Student (school number, name, gender, professional number, age)
Course (Course number, course name, credits)
Elective (school number, course number, grade)
Set F is one or a set of properties of the basic relationship r, but not the code of the relationship R. If f corresponds to the main code KS of the basic relation S, then the F is the outer code of the basic relation R
Basic Relationship R is called reference relationship (referencing Relation)
The basic relationship S is referred to as the referenced relationship (referenced Relation)
or target relationship (Relation)
Referential integrity rules
If the attribute (or attribute group) f is the outer code of the basic relationship R, it corresponds to the main code KS of the basic relationship s (the basic relationship R and S are not necessarily different relationships), then the value for each tuple on F in R must be:
or null value (each property value of f is null)
or equal to the primary code value of a tuple in s

[例1]：学生关系中每个元组的“专业号”属性只取两类值：（1）空值，表示尚未给该学生分配专业（2）非空值，这时该值必须是专业关系中某个元组的“专业号”值，表示该学生不可能分配一个不存在的专业

〔例2〕 ：选修（学号，课程号，成绩）“学号”和“课程号”可能的取值 ： （1）选修关系中的主属性，不能取空值 （2）只能取相应被参照关系中已经存在的主码值

〔例3〕：学生（学号，姓名，性别，专业号，年龄，班长）“班长”属性值可以取两类值：（1）空值，表示该学生所在班级尚未选出班长（2）非空值，该值必须是本关系中某个元组的学号值

User-defined Integrity

The constraints on a particular relational database reflect the semantic requirements that the data involved in a particular application must meet
The relational model should provide a mechanism for defining and verifying such integrity so that they can be handled in a unified, systematic way, without the application assuming this function

例:     课程(课程号，课程名，学分)“课程号”属性必须取唯一值非主属性“课程名”也不能取空值“学分”属性只能取值{1，2，3，4}

Database-relational database