数据库

#### 数据库系统的特点、组成、概念

historical roots of DB: file and file system

#####文件术语：

data: raw facts and meaningless

field: a character or group of characters

record: one or more field logically connected

file: collection of records

#####文件基础的系统：

collection of app programs

program define and manage its own data

#####文件系统限制：

separation and isolation of data

duplication of data

data dependence

incompatible file formats

fixed queries

#####数据库产生的原因

Definition of data was embedded in application

No control over access and manipulation of data.

#####数据库定义

Shared collection of logically related data, and a description of this data, designed to meet the information needs of an organization   

logically related data: include entities, attributes and relationships of an organization's information.

system(metadata): description of data, enable program-data independence

#####数据库管理系统的定义

software system: define, create, maintain and control access to the DB.

DDL : Data Definition Language

DML: Data Manipulation Language

AP : (DB) Application Program issuing request(SOL statement) interacts with DB

#####数据库系统组成

DBS包括 DBMS, DB, AP

结构图：



#####数据库系统的特点

###### Data abstraction :

implementation details are hidden

- aspects:

  structured data

  program-data independence : Logical/Physical data independence

  program operation independence: implementation of abstract operations can be changed without affecting code of AP.

- methods:

  system catalog

  three-level architecture

  relational algebra


External schema： user's view/ part of DB relevant to a user

Conceptual schema: community view of the DB/data relationship and what data

internal schema: physical representation of DB / how the data is stored in DB

######Efficiency :

space utilization + access to data

Method: shared data

######Reliability :

perform and maintain functions in routine and unexpected circumstances

method : integrity constraints and data consistency

##### 数据库用户

DA: Data Administrator

DBA: DB Administrator

DB Designers

Application Programmers

End Users: Naiver user/ expert

##### 数据库历史：

Hierarchical and Network, Relational, Object-Relational or Object-Oriented   

##### 数据库管理系统的优劣：

控制数据冗余、数据一致性、空间利用率高、数据共享、改善数据完整性、改善安全性、增加并发性、备份和恢复

复杂、体积大、DBMS的开销、额外硬件花费、转变花费、错误的影响更大

#### 关系模型

##### Data Model

定义：Integrated collection of concepts for describing data, relationships between data, and constraints   

DM定义了数据如何组成和操作， 包括结构部分、操纵部分、完整性规则

#####Relation Model

目标：

1.提供高度的数据独立性，更改数据表示、记录顺序、访问路径不影响AP

2.为处理数据语义、数据冗余和一致性问题提供基础

3.支持面向集合的数据语言扩展

受欢迎原因：数据存表最简单通用； 数据模型中最正式。数学集合理论，关系即表

组成:  表格结构、关系操作、完整性约束

#####表格结构

用途：实体、实体关系、表操作的结果

关系模型术语：

| Name              | meaning                                | File  |

| ----------------- | -------------------------------------- | ------ |

| Relation          | table                                  | File  |

| Attribute        | column                                | Field  |

| Domain            | set of allowable values for attributes |        |

| Tuple            | row                                    | Record |

| degree            | number of columns                      |        |

| cardinality      | number of rows                        |        |

| Relation schema  | named relation with attribute & domain |        |

| Relation instance | rows at specific                      |        |

| Relational schema | set of Relation schemas                |        |

| Relational DB    | collection of relations                |        |

#####关系模型属性，和表区别：

1.关系名不同于其他的关系 2.属性有独特名字 3.属性顺序无意义 4.元组顺序无影响

5.单元格原子值 6.属性值来自同域或无关系 7.元组不重复

##### 关系键：

超键SK：元组不相同的一到多属性

候选键CK：最小超键

主键PK: 选中候选键

可选键AK：非主候选键

外键FK：外表主键

##### 完整性约束：

数据的正确和一致性

数据库中Null：数据未知，不等于零和空格

完整性约束包括：域约束、实体完整性、参照完整性、用户定义完整性

###### 实体完整性：

基本关系：对应概念模型中实体

定义：在基本关系中，主键的任何一个属性非空

######参照完整性：

外键的值必须匹配主关系的某一元组或者全null

####关系代数

定义：通过对原关系进行操作定义新关系，不需要改变原关系

性质(闭包)：一个操作的输出可以座位另外一个操作的输入，嵌套操作

五种基本操作：选择、投影(projection)、笛卡儿积、集合并、集合差

三种额外操作：连接、集合交、集合除

#####Native relational operations

| NAME      | SYMBOL                | KEYBOARD FORM | EXAMPLE              |

| ---------- | ---------------------- | ------------- | --------------------- |

| PROJECTION | ∏col1, . . . , coln(R) | R[ ]          | R[Ai1…Aik]            |

| SELECTION  | σpredicate (R)        | R where C    | R where A1 = 5        |

| JOIN      | ⋈                      | JOIN          | R ⋈ S， or R JOIN S  |

| DIVISION  | ÷                      | DIVIDE BY    | R ÷ S,or R DIVIDEBY S |

相容表：两表表头相同，并且对应的列中的数据是相同的类型和相同的语义。 

只有相容表能进行 交差并等集合操作

R × S： i*j元组，M+N属性 (R:i个元组M个属性, S:j个元组N个属性)

Selection ： σ salary > 10000 (Staff)    tuples satisfy the specified condition   

Projection：∏staffNo, fName(Staff)    subset of Relation eliminating duplicates

笛卡儿积联合查询：σ Client.clientNo = Viewing.clientNo((∏clientNo, fName, lName(Client)) X (∏clientNo, propertyNo, comment(Viewing)))   

#####Join:

R⋈S: 笛卡儿积，投影

θ-join: R⋈fS  笛卡儿积，满足F条件的

Equijoin: 如果F中有 = 号，则为等连接 R⋈a.v = b.v S 不去掉相同的属性组

Natural join: 要求RS比较分量为相同的属性组，过程中把相同的属性组去掉

Outer join:  左，右，中

#####Divide：

1 找出关系R和关系S中相同的属性，即Y属性

2 在关系S中对Y做投影（即将Y列取出）

3 被除关系R中与S中不相同的属性列是X ，关系R在属性（X）上做取消重复值的投影为{X1，X2}；

4 求关系R中X属性对应的像集Y 判断关系R中X各个值的像集Y是否包含关系S中属性Y的所有值

#####关系完备性

一个关系数据语言能够表示关系代数可以表示的查询

A∩B = A -（ A-B）   

R ⋈ S = ∏A, R.B, C (σ R.B=S.B （ R X S） )   

R÷ S = ∏C(R) – ∏C((S X (∏C(R))) – R)   

| example                                                      |                                                              |

| ------------------------------------------------------------ | ------------------------------------------------------------ |

| List the staff who work in the branch at ‘163 Main St.’.    | ∏ staffNo, fName, lName, position(σ street=‘163 Main St.’(Staff ⋈ Branch)) |

| Identify all clients who have viewed all properties with three rooms. 46 | (∏ clientNo, propertyNo(Viewing)) ÷ (∏ propertyNo(σ rooms = 3 (PropertyForRent))) |

#####QBE

通过使用查询模板来获取信息(DBMS创建SQL语句)

工作原理：提供用户模板，用户输入例子来更新、检索

查询模板就是一个空表

| Relation Name | Attribute Name 1 | Attribute Name 2 | Attribute Name n |

| ------------- | ---------------- | ---------------- | ---------------- |

|              | P. >100          | P.              |                  |

| P.            |                  |                  | P.John          |

Query commands: Retrieved P. Update U. Insert I. Delete D   

例子：常量、变量

不同行之间OR，同一行之间是AND

通用变量来实现Join

| a    | name | stunum |

| ---- | ---- | ------ |

|      | P.  | P.join |

| B    | StuNum | Score  |

| ---- | ------ | ------- |

|      | join  | P. > 80 |

QBE 允许用户：

一表多表查询；确定结果属性；根据条件筛选；插入删除；修改；创建新表

#### SQL：

DDL (Data Define Language) for defining database structure.

DML (Data Manipulation Language) for populating the tables.

DQL (Data Query Language) for querying the tables

DCL (Data Control Language) for accessing control data.   

特点：

非过程性语言，DDLDMLDQLDCL一体化，面向集合的操作，交互或者嵌入式，简单易学

| Functions | Statements              |

| --------- | ------------------------ |

| DQL      | SELECT                  |

| DDL      | CREATE， DROP， ALTER    |

| DML      | INSERT， UPDATE， DELETE |

| DCL      | GRANT， REVOKE          |

SQL 由关键字和自定义字，大小写无关

BNF：大写关键字，小写自定义，| 选择， {}必须元素， []可选的， ... 0或多

非数字常量'', 数字没有引号

environment : one or more catalogs :  set of schemas : collections of DB objects

DB objects include tables, views, domains, indexes, which are in a schema have the same owner

#####DDL:

|        | Create        | Modify      | Drop        |

| ------ | ------------- | ------------ | ----------- |

| SCHEMA | CREATE SCHEMA |              | DROP SCHEMA |

| DOMAIN | CREATE DOMAIN | ALTER DOMAIN | DROP DOMAIN |

| TABLE  | CREATE TABLE  | ALTER TABLE  | DROP TABLE  |

| INDEX  | CREATE INDEX  |              | DROP INDEX  |

| VIEW  | CREATE VIEW  |              | DROP VIEW  |


Create DB : CREATE SCHEMA Name [AUTHORIZATION CreatorIdentifier];   

Drop DB:    DROP SCHEMA database-name [RESTRICT | CASCADE];   

RESTRICE default, schema is required empty else failed

CASCADE: drop all objects

SQL identifier: less than 128 char, start with letter, cannot contain space

| data TYpe      | characteristic                                          |

| --------------- | ------------------------------------------------------- |

| Boolean        | TRUE,  FALSE, UNKNOWN(NULL)                            |

| CHAR/VARCHAR    | CHAR [length] (fixed, space) VARCHAR[length] (no space) |

| Bit            | 0/1  BIT [length]                                      |

| Exact Num      | NUMERIC/DECIMAL[precision[,scale]] INTEGER SMALLINT    |

| Approximate Num | FLOAT [ precision] / REAL / DOUBLE PRECISION            |

| Datetime        | DATE  TIME [timePrecision]  TIMESTAMP [timePrecision]  |

| Interval data  | YEAR, MONTH, DAY, HOUR, MINUTE, SECOND                  |

######Create Domain：

CREATE DOMAIN SexType AS CHAR DEFAULT ‘M’ CHECK (VALUE IN (‘M’, ‘F’));   

######Remove Domain

DROP DOMAIN DomainName [RESTRICT | CASCADE];   

Restrict :domain is used,drop failed

Cascade : change into underlying data type / column constraint

######Create Table：

CREATE DOMAIN PNumber AS VARCHAR(5);   

CREATE TABLE PropertyForRent (

propertyNo  PNumber NOT NULL UNIQUE DEFAULT 44, ….

staffNo StaffNumber，

PRIMARY KEY (propertyNo),

FOREIGN KEY (staffNo) REFERENCES Staff

ON DELETE SET NULL ON UPDATE CASCADE ….);

######Alter Table:

ALTER TABLE Staff ALTER position DROP DEFAULT;   

######Drop Table:

DROP TABLE TableName [RESTRICT | CASCADE]   

dependent objects Restrict

######Create Index:

CREATE [UNIQUE] INDEX IndexName ON TableName (columnName [ASC|DESC]\[, …])；   

DROP INDEX IndexName;   

#####DML

######SELECT

SELECT [DISTINCT | ALL]

  {* | [columnExpression [AS newName]]\[,...] }

FROM TableName [alias]\[, ...]

[WHERE condition]

[GROUP BY columnList [HAVING condition] ]

[ORDER BY columnList]   

SELECT DINSTINCT staffNo, fName, lName, salary/12 AS monthlySalary

FROM Staff 

WHERE (salary BETWEEN 20000 AND 30000)

AND position NOT IN (‘Manager’, ‘Supervisor’)

AND address LIKE ‘%Glasgow%’                                       

AND comment IS NULL

ORDER BY 4, type DESC;

%:zero or more char _: any single char

###### Aggregate Function:

COUNT: number of values in column

SUM、 AVG、 MIN、MAX

AF 仅用于SELECT 和 HAVING 从句中

除了COUNT(*) 外， 函数删除NULL然后再操作

SELECT DINSTINCT COUNT(*)

AS myCount FROM PropertyForRent

WHERE rent > 350;   

带有某列引用时，则不能使用函数，除非GroupBy

SELECT MIN(salary) AS myMin,                                                                                      MAX(salary) AS myMax,                                                                                              AVG(salary) AS myAvg                                                                                                FROM Staff;   

###### GROUPBY

SELECT  branchNo, COUNT(staffNo) AS myCount,

                SUM(salary) AS mySum

FROM  Staff                                                   

GROUP BY  branchNo

HAVING  COUNT(staffNo) > 1

ORDER BY branchNo;

###### Nested Queries   

SubSelect : in WHERE and HAVING

例子

SELECT staffNo, fName, lName, position,

      salary – (SELECT AVG(salary) FROM Staff) AS SalDiff

FROM Staff

WHERE salary >

      (SELECT AVG(salary)

      FROM Staff);   

SELECT staffNo, fName, lName, position, salary

FROM Staff

WHERE salary > SOME

            (SELECT salary

            FROM Staff

            WHERE branchNo = ‘B003’);   

ANY ：任意(SOME) ALL：所有

###### 多表查询

SELECT c.clientNo, fName, lName, propertyNo, comment

FROM Client c, Viewing v

WHERE c.clientNo = v.clientNo   

attributes A

① FROM Table1 JOIN Table2 USING A

② FROM Table1 JOIN Table2 ON Table1.A = Table2.A

③ FROM Table1 NATURAL JOIN Table2

④ FROM Table1 FULL|LEFT|RIGHT JOIN Table2 ON Table1.A = Table2.A   

Exists

SELECT staffNo, fName, lName, position

FROM Staff s

WHERE EXISTS

    (SELECT *

    FROM Branch b)

Subquery op [ALL] Subquery

Op could be UNION, INTERSECT, and EXCEPT   

ALL选项会包括重复的行

(SELECT city

FROM Branch) UNION

(SELECT city

FROM PropertyForRent);

###### DB Update

INSERT INTO TableName [ (columnList) ]

VALUES (dataValueList)

省略的行插入NULL，除非有Default

INSERT INTO StaffList

(SELECT staffNo, fName, lName

FROM Staff);   

##### DCL