数据库基础 课堂笔记 1-3章

Introduction

专业术语

• DBMS

  • database manegement system

• Database Users

  • End users 最终用户、交互式用户
    • Casual users
    • Naive users
  • Application programmers 应用程序员
  • DataBase Administrators 数据库管理员 (DBA)

• 数据库系统的结构

• 

• 数据模型

• 

---

Chapter 2 The Relational Model

2.3 Relational Rules

• RULE 1 : First Normal Form Rule (第一范式规则）

  • In defining tables, the relational model insists that columns that have multi-valued attributes (sometimes called repeating fields) are not permitted
  • column values must be simple types
  • E.G.
    • NOT ALLOWED:
    • the dependents column can’t be defined like this
    • the solution in relational model goes like this: (use two tabels)
      
  • not over yet, the ename col is also not legal, since its type is not simple types, we could not construct a tabel with a column name ename that had three components: ename.fname, ename.lname and ename.mi(类似于面向对象的用法）
    
    

• RULE 2: Access Rows by Content Only Rule

  Rule tow implies that there is no order on the rows, there shoude be no way that a queryin a (pure) relational languagecan ask, for example to retrieve the third row of the table. This simply says that a relation is a set of tuples. which also implies that a pointer to a row to retrieve it at a later time is not permitted

  However, many comercial database systems have always broken RULE 2, prividing a method to retrieve a row of a table by its row identification number(RID or ROWID or TID tuple identification number).

  retrieval by RID is sometimes useful to a database administrator in examing situations where it is suspected that the rows if the table are stored in the proper way.

  though it is sometimes useful, we should not perform normal queries in terms of saved RID values, since RIDS are subject to change when tables are updated in certain circumstances

  ( relational model also states that there should be bo order to the columns of a relation, this rule is broken by the standard SQL language)

• RULE 3 : The Unique Row Rule
  two tuples in a relation cannot be identical in all column values at once

2.4 Keys, Superkeys, and Null Values

• KEY

  

• SuperKey

  we refer to a set of attributes that fulfills property 1 but not necessarily property 2 as a superkey

  so a key is always a superkey, but not vice versa

  A kry or superkey for a table is required to remain a key or superkey as new rows are added

  once keys are defined, it becomes impossible for normal updatesof the table to cause the uniqueness condition for such keys to fail

• THEOREM 2.4.2 Every table has at least one key

  prof starts from S1 is a superkey

• DEFINITION 2.4.3 Primary Key of a Table

  (the various keys of a relation are often known as candidate keys, the name implies a selection process whereby one if the candidates will be designated as the primary key)

  A primary key of a table T is the candidate key chosen by the ddatabase designer to uniquely indentify specific rows of T
  
  

• Null Values

• Rule 4 : Entity Integrity Rule
  No column belonging to a primary key of a table T is allowed to take on null values for any row in T
  (primary key values are the designated identifier for the row,until we have the identifier value settled, we will not allow the eow to be stroed in the table)

---

2.5 Relational Algebra

there are two types of operations in relational algebra:

• set-theoretic opreations
  • making use of the fact that tables are essentially sets of rows
    
• native relational operations
  • focusing on the structure of the rows
    
    
    

---

2.6 Set-Theoretic Operations

Gor sets of rows to be involved in unions, intersections, or differences and to form new tables, the rows in different sets must have the same heading structure

only tables that are compatible can be involved in unions, intersections, and differences

• Assignment and Alias
  
  
• The Product Operation
  
  

---

2.7 Native Relational Operations

• The Projection Operation
  
  Note that different rows of a table R, When projected onto a subset of columns, may become identical. When this happens, the projection operator will also delete duplicate row, until only onecopy of each duplicate set of rows existed in cloumns that were deleted

• The Selection Operation
  
  
  
  examples:

  • 
  • 
  • 

• The Join Operation
  
  
  例子：
  
  
  要注意CHEAPS投射到pid上是很有必要的， 不然第一个join会产生一个更大的表， 这个更大的表可能就多出来和 CUSTOMERS 属性一样的列， 从而第二次连接时多了额外的限制条件。而使用了投射到pid上就可以避免额外信息的出现
  
  

• The Division Operation
  
  
  
  不提前投射的话，约束条件就会太多，导致失去一些情况
  
  

  2.7.3 Precedence of Relational Operations

  
  
  

2.8 The Interdependence of Operations

证明用韦恩图

2.10 Other Relational Operations

• Outer Join
  
  The idea of the left outer and right outer join operators is that we might wish to preserve unmatched rows on one side only.

• Theta Join
  
  
  
  

  ---

  Chapter 3 Basic SQL Query Language

  3.3 Simple Select Statements

• 格式
  

  select aid, aname from agents where city = 'New York';
  

• The simple * in the select list is a shorthand symbol meaning retrieve all fields

  select * from customers;
  

• 注意区分下面两个语句，一个会出现大量重复，还有一个是检索出来的结果只出现一次

  select pid from orders;
  select distinct  pid from orders;
  

• retrive from several tables
  
  

• 可以运算
  
  

• 技巧： 从一个方向搜索，避免重复
  
  
  
  

  
  这里的DISTINCT不能省
  

  • an important example
    
    
    
    
    

3.4 Subqueries

• The IN Predicate
• The idea: a set of values is returned from the evaluation of the Subquery and then a test for membership is performed by the outer course of events

Membership can be tested not only against the set provided by a Subquery but also against sets that are explicitly defined, as we defined in the follwing example

As we see in the following example, multiple levels of subquery nesting are allowed

It does not require a qualified reference to a local column name.

• we can provide an inner Subquery with data that orifinates in the outer Select, but not vice versa
  
  

• In FORM
  

• We can test a pair of values
  
  
  
  

• The Quantified Comparison Predicate

  A quantified predicate compares the simple value of an expression with the result of a Subquery.
  
  
  
  Note that, while the predicate expr =SOME(Subquery) means the same as expr IN(Subquery), the form expr NOT IN (Subquery) is not the same as expr <>SOME( Subquery). Instead, NOT IN (Subquery)is identical to expr <>ALL(Subquery)

  • 少用any， 多用some
    

• The EXISTS Predicate

  The EXISTS predicate tests whether the set of rows retrieved in a Subquery is non-empty.

  
  The predicate EXISTS(Subquery) is TRUE if and only if the Subquery returns a non empty set( if there exists an element in the set).

  
  
  
  
  
  

• NOT EXISTS can be used to implement the MINUS operator from relational algebra
  
  EXCEPT operator directly copies the effect of the MINUS operator
  

3.5 UNION Operators and FOR ALL Conditions

• THE UNION Operator
  Any number of Subqueries that produce compatible tables can be combined with repeated use of the UNION syntax of Figure 3.9
  
  

• Division: SQL “FOR ALL…” Conditions
  
  先找反例
  
  更进一步
  
  最终：
  
  一个综合性的例子
  
  思考步骤：
  
  
  

• 最好加上括号
  
  
  
  
  
  
  

---

3.6 Some Advanced SQL Syntax

• The INTERSECT and EXCEPT Operators in Advanced SQL
  
  figure 3.9 indicates that two Subquery terms can be connected by a UNION, INTERSECT, or EXCEPT to produce a new Subquery
  
  
  

• Join Forms in Advanced SQL
  
  
  在FROM里使用Subquery
  

• (INNER) JOIN
  
  
  
  

• OUTER JOIN
  
  
  
  
  
  

---

3.7 Set Functions in SQL

NOTE carefully that null values in a column are not counted

• Handling Null Values
  
  
  
  
  

---

3.8 Groups of Rows in SQL

• However, the GROUP BY clause of a Select statement can contain more than one column name.
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  

---

3.9 A complrte Description of SQL Select

As we see in this general form, the ORDER BY clause is not allowed to appear in Subquery forms, but only in full Select statements

• Identifiers

• 用引号括起来的别名
  

• Expressions, Predicates, and the search_condition

Expressions defined below can also appear in the select list of a Select statement.

• numeric

• string
  
  
  
  
  

• Scalar Subqueries as Expressions: Advanced SQL
  A scalar subquery is a Subquery that returns a single value rather than a set of more than one row or more than one cloumn

Scalar Subqueries 可以用在 select list 里， 也可以用在 where clause 里

• A Discussion of the Predicates
• Comparison Predicate
  
  
  The Subquery on the right is only permitted provided that the result retrieved is known to either contain a single value or be an empty set.
  

because we know that the subquery retrieved only a single value.

如果是与NULL比较, 就返回NULL

• Truth Values: TRUE(T), FALSE(F), and UNKNOWN (U)
  
  
  

• ** The BETWEEN Predicate**
  

• The Quantified Comparison Predicate
  

• The IN Predicate
  

• The EXISTS Predicate
  
  It ecaluates to TRUE exactly when the Subquery does not result in an empty set. there are no conditions under which this predicate evaluates to UNKNOWN

• The IS NULL Predicates
  

• The LIKE Predicate
  
  
  
  
  

就是转义字符

3.10 Insert, Update, and Delete Statements

** The Insert Statement**

The Update Statement

Delete Statement

---

3.11 The Power of The Select Statement

• The Non-Procedural Select Statement
  
  
• Turing Power(计算完整性）
• Limited Power of the Basic SQL Select Statement
  
  方差和中位数在过程性的语言中很容易计算出来，但是在非过程的SQL中，如果数据库系统没有提供相关函数，就没有办法求得

• Non-Procedural Reports
  
  
  

• Transitive Closure
  
  
  
  
  
  

• Limited Power of Boolean Conditions