Database-Relational calculus

Relational calculus

Based on predicate calculus in mathematical logic
Classify by predicate variable
1. Tuple Relationship calculus:
Primitive objects with tuple variables as predicate arguments
Tuple Relationship Calculus Language alpha
2. Domain Relational calculus:
Base object with domain variable as predicate argument
Domain Relational Calculus language QBE

由E.F.Codd提出INGRES所用的QUEL语言是参照ALPHA语言研制的语句检索语句GET更新语句PUT，HOLD，UPDATE，DELETE，DROP

Retrieving operations

语句格式：     GET　工作空间名  [（定额）]（表达式1）               [：操作条件] [DOWN/UP 表达式2] 定额：规定检索的元组个数格式：        数字表达式1：指定语句的操作对象格式：关系名| 关系名. 属性名| 元组变量. 属性名| 集函数 [，…  ]操作条件：将操作结果限定在满足条件的元组中格式：        逻辑表达式表达式2：指定排序方式格式：        关系名. 属性名| 元组变量. 属性名[，…  ]

(1) Simple search

GET　工作空间名 （表达式1）

[Example 1] query all the elective course numbers.
GET W (SC. Cno)
[Example 2] Query the data of all students.
GET W (Student)
Format
GET Workspace name (expression 1): Operating conditions

[例3]查询信息系(IS)中年龄小于20岁的学生的学号和年龄    GET  W  (Student.Sno，Student.Sage):      Student.Sdept=‘IS‘∧ Student.Sage<20

Format
GET Workspace name (expression 1) [: Operating conditions]
Down/up Expression 2

  [例4]查询计算机科学系(CS)学生的学号、年龄，结果按年龄降              序排序    GET  W  (Student.Sno，Student.Sage):               Student.Sdept=‘CS‘ DOWN Student.Sage

Format
GET Workspace Name (quota) (expression 1)
[: Operating conditions] [Down/up expression 2]

[例5]  取出一个信息系学生的学号。    GET  W  (1)  (Student.Sno):             Student.Sdept=‘IS‘ [例6]  查询信息系年龄最大的三个学生的学号及其年龄，结果按年龄降序排序。        GET  W (3)  (Student.Sno，Student.Sage):         Student.Sdept=‘IS‘ DOWN Student.Sage

Retrieving with a tuple variable

What a tuple variable means
Represents the ability to vary within a relationship (also known as range variable range Variable)
Use of tuple variables
① Simplified Relationship name: sets a tuple variable of a shorter name instead of a longer relationship name.
You must use a tuple variable when using quantifiers in ② operation conditions.
Defining tuple variables
Format: RANGE relationship name Variable name
A relationship can set multiple tuple variables

Searching with quantifier of existence

Tuple variables must be used when using quantifiers in operation conditions

[例8]  查询选修2号课程的学生名字。   RANGE  SC  XGET W (Student.Sname): ?X(X.Sno=Student.Sno∧X.Cno=‘2‘)[例9] 查询选修了这样课程的学生学号，其直接先行课是6号课程。   RANGE Course  CX   GET  W  (SC.Sno):   ?CX (CX.Cno=SC.Cno∧CX.Pcno=‘6‘)

[ExampleTen] Inquiry at least one of the courses6The student name of the course is RANGE Course CX SC SCX GET W (Student. Sname): ? SCX (SCX. Sno=student. Sno∧? CX (CX. Cno=scx. Cno∧cx. Pcno=' 6 ')) Front bundle paradigm Form: GET W (Student. Sname):                       ? SCX? CX (SCX. Sno=student. Sno∧cx. Cno=scx. Cno∧cx. Pcno=' 6 ')

Retrieval of an expression with multiple relationships

 [例11]  查询成绩为90分以上的学生名字与课程名字。     RANGE  SC  SCX     GET  W(Student.Sname，Course.Cname):                   ?SCX (SCX.Grade≥90 ∧               SCX.Sno=Student.Sno∧              Course.Cno=SCX.Cno)

Searching with the universal quantifier

 [例12]  查询不选1号课程的学生名字          RANGE  SC  SCX          GET  W (Student.Sname):         SCX (SCX.Sno≠Student.Sno∨SCX.Cno≠‘1‘)用存在量词表示：          RANGE  SC  SCX          GET  W  (Student.Sname):    ??SCX (SCX.Sno=Student.Sno∧SCX.Cno=‘1‘)

Searching with two kinds of quantifiers

[例13]  查询选修了全部课程的学生姓名。          RANGE  Course  CX                         SC         SCX          GET  W  (Student.Sname):    CX ?SCX (SCX.Sno=Student.Sno∧                 SCX.Cno=CX.Cno)

Search using the Yun letter (implication)

[Example -] Inquiries are at least optional200215122Student number of student selected courseRANGE  couse  CX                           SC     SCX                            SC     SCY          GET  W(Student.sno):      CX(?SCX(SCX. Sno=' 200215122 ' ∧scx.cno=cx. Cno)?SCY(SCY. Sno=Student.sno∧                                                                   SCY. Cno=CX. Cno))

Aggregation functions

[例15]  查询学生所在系的数目。         GET  W  ( COUNT(Student.Sdept) )      COUNT函数在计数时会自动排除重复值。[例16]  查询信息系学生的平均年龄              GET  W  (AVG(Student.Sage):                                Student.Sdept=‘IS’  )

Database-Relational calculus