Database

1.Overview of Data Models

1.1.关系数据模型（Relational Model）

A two-dimensional table called a relation

Each relation has a name——关系名

Provides a simple, limited approach to structuring data

Provides a limited, yet useful, collection of operations on data

1.1.1.Attribute(属性)

The columns of a relation are named by attributes

Any two attributes of a relation can’t have same name

第一行就是每列的attribute

1.1.2.Schemas(模式)

Relation schema = relation nameand the set of attributes.(关系名+属性集)

Example:

Movies (title, year, length, genre)

Movies (title: string, year: integer, length:integer, genre: string)

关系名大写开头、属性名小写开头

数据库是collection of relations

数据库的关系模式集合——Relational database schema

1.1.3.Tuples (元组/⾏)

The rows of a relation, other than the **header rowcontaining the attribute names,**are called tuples.

A tuple has one **component(**属性的分量) for each attribute of the relation.

Use commas(,)逗号 to separate components, and **use parentheses(圆括号) to surround the tuple

Example: (‘Star Wars’, 1977, 124, ‘sciFi’)

1.1.4.Domains（域）

The set of allowed values 允许的值 *for each***attribute is called the domain of the attribute

Attribute values are (normally) required to be atomic, that is, indivisible.(不可分割类型)

Movies ( title:string, year:integer, length:integer, genre:string)

1.1.5.Equivalent Representations of a Relation

一个给定的关系中元组的集合：Relation Instances(实例)

属性、元组都是无序的，可以随便改变顺序但仍然为一个关系模型

Order of tuples is irrelevant

1.1.6.Key

A set of attributes forms a key for a relation if we do not allow two tuples in a relation instance to have the same values **in all the **attribute of the key.

关系中可作键的属性集可能有多个

1.2.Defining a Relation Schema in SQL

1.2.1.SQL启动语句以及基本语法

1.2.2.SQL分类

1.2.3. Data Types

（mysql 不区分大小写）

All attributes must have a data type.

1.字符串

CHAR(n)#定长字符串

VARCHAR(n)#变长字符串

其他：

2.位串由01组成

bit(n)#固定长度
bit varying(n)#可变长度

3.boolean

boolean
#可能的值有
true false unknown

4.int/interger、浮点型

double(2,1)#代表2位数据总长度，小数点在从右往左1的位置，如1.1

Dates and times

1.2.4.定义表和表操作

CREATE TABLE Salesman/*SQL对⼤⼩写不敏感*/
(empid char(9) PRIMARY KEY,
idno char(18) UNIQUE,
name char(8) NOT NULL,
gender bit NOT NULL,
phone char(20),
deptid int NULL
);

Note that there is no cognominal(同名的) tables in a DB

Modifying Relation Schemas

ALTER TABLE ADD column-name datatype NULL；
增加一列
ALTER TABLE DROP column-name;
删除一列
ALTER TABLE tablename RENAME TO newname;
改表名
DROP TABLE tablename;
删除表

Default Values

When we create or modify tuples, we sometimes do not have values for all components.

SQL provides the NULL value as default value. (隐含默认NULL)

When we declare an attribute and its data type, we may add the keyword ==DEFAULT== and ==an appropriate value==.

gender CHAR(1) DEFAULT “？”

Declaring Keys

声明某⼀属性(⾮多属性)为“键”：在属性被列入关系时就声明。

deptid  int    PRIMARY KEY,

如果声明一组(一个及以上)属性为键需要:

PRIMARY KEY (orderno, lineno)

Primary Key and Unique

Atable has and only has one Primary Key, (键只能声明⼀次)，while it may has any number of Unique declarations.

1、Primary key的1个或多个列必须为NOT NULL，如果列为NULL，在增加PRIMARY KEY时，列自动更改为NOT NULL。而UNIQUE KEY 对列没有此要求。

2、一个表只能有一个PRIMARY KEY，但可以有多个UNIQUE KEY。

1.3.An Algebraic Query Language

关系代数的表达式称为查询（query）

Operands /操作数are relations or variables

Operators /操作符号are symbols denoting

1.3.1.传统关系代数分类

The usual set operations: union, intersection and difference (关系：并、交、差)
Operations that remove parts of a relation:

Selection: eliminates(消除) some rows (tuples)按条件选取行

Projection(投影): eliminates some colunms (attributes)选列