Database System Week2 | Anthony2004

type

status

date

slug

summary

Enhanced Entity-Relationship Model(EER)

specialization\generalization

superclass（超类/父类）

一个包含了一个或多个子类的实体类型（一个通用的大类里面包含了若干个更具体的小类），例如：staff为superlcass，manager、supervisor（主管）等为子类

subclass（子类）

一个更具体的，从某个实体类型中分出来的子分组（继承了父类的属性，还有自己特有的属性），例如manager作为staff的子类，除了继承staff的姓名，编号，薪水等信息外。还有部门管理范围这样的额外属性

connection

superclass/subclass的关系是1：1的（子类中的每一个实体都对应父类中的一个实体，因为子类是由父类分化出来的，例如一个manager一定是一个staff）；

超类中的成员可以属于多个子类，也可以互不重叠（重叠/互斥）：overlapping(一个人可以既是讲师又是研究院),distinct（一个人要么是男人要么是女人）

Attribute Inheritance

子类中的实体代表了与超类中同一个“现实世界对象”

子类的属性=超类的全部属性+自己特有的属性

specialization特殊化

通过识别一个实体类型内部成员之间的差异性特征，将其细分为更多具体的子类（放大差异，细分子类），例如：实体Staff中可能有多种类型，如manager,sales assistant,secretary等，可以根据职位的差异将其细分出来

generalization泛化

通过识别多个实体类型之间的相似点，将它们合并为一个更通用的超类（缩小差异，提炼共性）

participation constraint 参与约束（控制是否必须属于子类）

mandatory强制参与：每个超类中的实体必须属于至少一个子类，例：公司中每位员工必须是“销售”或“经理”等具体岗位。

optional可选参与：有些超类实体可以不属于任何子类，例：一些员工可能是临时人员，不属于任何正式子类。

disjoint constraint 互斥约束（控制是否能属于多个子类）

disjoint(or)：每个超类对象最多属于一个子类例：一个员工只能是“经理”或“秘书”，不能同时是两个角色。

no disjoint(and)：超类对象可以同时属于多个子类。例：一个人可以既是“导师”又是“研究员”。

四种约束组合

何时使用超类和子类

一些属性只适用于部分对象（避免主表NULL混乱）

一些对象有独立参与的关系（逻辑上与其他不同），例如manager是staff的子类，但是只有manager类型的staff才有管理别的员工的关系

Mapping ER model concepts to relations

Entity

将实体转换为关系型表的时候注意将复合属性（composite attribute）分解为其子属性，比如name(复合属性)→lname+fname(子属性)，address(复合属性)→country+city+area+street(子属性)

Staff(staffNo,fName,IName,position,sex,DoB)

One-to-many (1:*) binary relationships

One-to-one (1:1) binary relationships

共有三种构建方式

在Dept中放外键：将empno放入Dept中（适用于每个部门都有主管，所以这个字段不会是NULL）这个部门的主管是谁?

在Emp中放外键：将deptname放入Emp中（表示这个员工管理哪个部门，因为大多数员工不是主管，所以这个字段值大多数为NULL）这个员工管理的部门是什么（可能不是主管，这个时候外键deptnmae为NULL）

双方都放外键：容易造成冗余

如何选择：看哪一边字段的非空数量最少，这个例子建议放在dept中，因为每个部门都有主管，empno不会是NULL 上图的1-*表示的是在这个部门工作的员工

两边都是 mandatory（必须参与）情况：每个 A 必须对应一个 B，反之亦然。合并两个实体为一张表适用于：完全绑定的一对一关系，例如：每位用户必须有一个账户，每个账户也必须对应一位用户。

只有一边是 mandatory，另一边是 optional 情况：A 必须有对应的 B，但 B 不一定必须有 A 在 mandatory（强制）一方 加外键，指向可选的一方。例子：每个部门必须有一个主管，但员工不一定是主管 → 在 Dept 中加外键 empno

两边都是 optional（可选参与）情况：A 可能和 B 有关系，也可能没有；B 也是一样单独创建一张关系表，表示这对关系。这张关系表包含两个外键（分别来自 A 和 B）,外键组成主键或唯一组合键,如果需要，还可以添加其他关系属性（如时间戳）。例子：每位员工可能使用公司车辆，但不是每个人都有

One-to-many (1:*) recursive relationships

recursive relationship（递归关系）：一个实体类型和自己发生关系

mandatory participation on both sides（两边都必须参与）

在原表中插入一个外键，引用自身的主键

optional participation on both sides（两边都是可选）

建立一个新的关系表，表中分有两个外键，分别引用原表的主键

mandatory participation on only one side（只有一边是参与的）

如果方便，就在原表添加一个字段（与双方 mandatory 类似）

如果不方便或更灵活，则像 optional 一样新建关系表

Many-to-many (:) binary relationships

只能通过构建第三张表，分别引用两张表的主键作为新表的联合外键

complex relation:多对多关系；三元关系；带有中间属性的关系

建立一个新的关系表来表示这个复杂的关系

把关系中的附加属性包含进来

把参与实体的主键作为外键放入新表

多方的外键通常组成新表的主键

新表的主键可能还需要包含附加字段

复杂关系就要建新表，把主键变外键，再加上自己的属性，必要时联合主键。

Multi-Valued Attributes多值属性

一个实体的某个属性可以有多个值.但是一列不能存多个值，所以要新建一个表。

例如：一个分店可能有多个电话，创建Telephone(teleNo,BranchNo);

Superclass/subclass relationships

Participation Constraint	Disjoint Constraint	建表策略（Relations Required）
Mandatory（强制）	Nondisjoint（非互斥，AND）	建一张表，包含所有子类属性 + 类型区分字段（discriminator）
Optional（可选）	Nondisjoint（非互斥，AND）	建两张表：一张是父类表，一张是所有子类共享的联合表（有类型标识字段）
Mandatory（强制）	Disjoint（互斥，OR）	多张表：每个子类一张表（含父类属性），相当于"分裂"出多个子类表
Optional（可选）	Disjoint（互斥，OR）	多张表：一张父类表 + 每个子类一张子类表

SQL

Data definition language(DDL)

create table

语法

CREATE TABLE branch (
BranchNo CHAR(4) NOT NULL,
street VARCHAR(45) DEFAULT NULL,
city VARCHAR(45) DEFAULT NULL,
postcode VARCHAR(45) DEFAULT NULL,
PRIMARY KEY (BranchNo)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

drop table

语法

DROP TABLE IF EXISTS Staff, Branch;

Data manipulation language(DML)

insert

语法

插入所有列 INSERT INTO Staff VALUES ('S001', 'John', 'Smith', 'Manager');

插入指定列 INSERT INTO Staff (staffNo, fName, lName) VALUES ('S002', 'Alice', 'Brown');

delete

语法

DELETE FROM TableName
[WHERE searchCondition];

例子

删除特定数据行： DELETE FROM Viewing WHERE propertyNo = 'PG4';

删除整张表的数据（清空表）： DELETE FROM Viewing;

update

语法

UPDATE 表名
SET 列名1 = 新值1, 列名2 = 新值2, ...
[WHERE 条件]

例子

更新所有员工工资： UPDATE Staff SET salary = salary * 1.03;

只更新 Manager 的工资： UPDATE Staff SET salary = salary * 1.05 WHERE position = 'Manager';

更新多列（职位和工资）： UPDATE Staff SET position = 'Manager', salary = 18000 WHERE staffNo = 'SG14';

select

基础用法

All Columns, All Rows: select * from Staff ”*”可以代表所有

Specific Columns, All Rows: select staffNo,fName,lName... from Staff

Use of DISTINCT: select distinct address from Staff

Calculated Fields: select staffNo,salary/12 as monthlySalary from Staff

Comparison Search Condition: select staffNo,fName,lName from Staff where salary > 10000

Compound Comparison Search Condition: select * from Staff where address='Beijing' or address='Nanjing'

Range Search Condition: select staffNo,fName,lName from Staff where salary between 20000 and 30000

Pattern Matching: select ownerNo,fName,lName,address from Staff where address like "%Glasgow%" 查找地址中包含“Glasgow”字样的，%符号表示任意个符号位置，_符号只表示单个符号位置

NULL Search Condition: select staffNo where comment is NULL

Single Column Ordering: select staffNo from Staff order by salary desc

Multiple Column Ordering: select staffNo,male,age from Staff order by male 这里是根据不同属性来排序的，在order by male后面还可以加别的排序规则，在前一个排序等级相同的情况下，再按照后一个的等级进行排序以此类推

聚合函数

注意：

除了count(*)会统计所有行，其他函数都会自动忽略NULL值，count(col_name)只统计非NULL值的数量。

distinct（去除重复的）可以修饰属性，对count,avg,summ有作用，对min,max没有作用

usage:

COUNT(*): select count(*) from Staff where age>35

COUNT(DISTINCT): select count(distinct address) from Staff where age between 10 and 20

COUNT and SUM: select count(staffNo),sum(salary) from Staff where position = "Manager"

MIN, MAX, AVG: select min(salary),max(salary),avg(salary) from Staff

函数	中文解释	用途示例
`COUNT()`	计数	统计有多少行数据
`SUM()`	求和	把一列的数值加起来
`AVG()`	平均值	求一列的平均数
`MIN()`	最小值	找出一列中的最小值
`MAX()`	最大值	找出一列中的最大值

分组

group by 是用来按照某一列的值把数据分组的关键字，配合聚合函数来对每一组进行统计。 where的优先级更高，先执行。 NULL默认相同，分在一组。

usage: select brachNo,count(staffNo),sum(salary) from Staff group by brachNo order by branchNo

having

select brachNo,count(staffNo),sum(salary) from Staff group by branchNo having count(staffNo)>1 order by branchNo 注意辨析where,group by,having，where最先执行，筛选原始数据；group by接着分组，本身没有筛选作用；having是对组与组之间的筛选（这时才可以使用聚合函数）

多表查询

simple join 简单连接（使用where来连接两张表） SELECT c.clientNo, v.propertyNo FROM Client c, Viewing v WHERE c.clientNo = v.clientNo 这里给Client Viewing加了一个代名方便引用

sorting a join 排序连接（也是一种简单连接） SELECT s.branchNo, s.staffNo, fName, lName, propertyNo FROM Staff s, PropertyForRent p WHERE s.staffNo = p.staffNo ORDER BY s.branchNo, s.staffNo, propertyNo; 排序优先级按照顺序

three table join三表连接（使用多个连接条件） SELECT b.branchNo, b.city, s.staffNo, fName, lName, propertyNo FROM Branch b, Staff s, PropertyForRent p WHERE b.branchNo = s.branchNo AND s.staffNo = p.staffNo ORDER BY b.branchNo, s.staffNo, propertyNo;

multiple grouping columns多列分组(更细粒度的分组) SELECT s.branchNo, s.staffNo, COUNT(*) AS myCount FROM Staff s, PropertyForRent p WHERE s.staffNo = p.staffNo GROUP BY s.branchNo, s.staffNo ORDER BY s.branchNo, s.staffNo;

执行顺序：

所有行拼成一个大表（相乘）

筛选出符合条件的行（WHERE）

取出需要的字段（SELECT）

去重（DISTINCT）

排序（ORDER BY）

subquery子查询

SELECT name
FROM Student
WHERE age > (SELECT AVG(age) FROM Student);

写在一个sql查询中的select语句，因为where不能直接使用聚合函数

Subquery with Equality SELECT staffNo, fName, lName, position FROM Staff WHERE branchNo = (SELECT branchNo FROM Branch WHERE street = '163 Main St');

Subquery with Aggregate SELECT staffNo, fName, lName, position, salary - (SELECT AVG(salary) FROM Staff) AS SalDiff FROM Staff WHERE salary > (SELECT AVG(salary) FROM Staff);

嵌套子查询Nested Subquery SELECT propertyNo, street, city, postcode, type, rooms, rent FROM PropertyForRent WHERE staffNo IN ( SELECT staffNo FROM Staff WHERE branchNo = ( SELECT branchNo FROM Branch WHERE street = "163 Main St" ) );

子查询的目的是返回值，而不是排序显示结果，所以子查询中不能有order by 子查询只能有一个列明或表达式比较中，子查询要放在右边

any\some all

any\some:只要子查询中有一个值满足条件就为真 SELECT name FROM Staff WHERE salary > ANY ( SELECT salary FROM Staff WHERE position = 'Assistant' ); 这里的返回值是所有assitant的工资，只要staff满足比最低的那个人的薪资高就可以

all:子查询中的所有值都满足条件才为真 SELECT name FROM Staff WHERE salary > ALL ( SELECT salary FROM Staff WHERE position = 'Assistant' )； 子查询的返回值是所有assitant的工资，staff需要满足薪资高于全部这些人才行

exist not exist

exist 当子查询有至少一个返回值时为true，反之false SELECT staffNo, fName, lName, position FROM Staff s WHERE EXISTS (SELECT * FROM Branch b WHERE s.branchNo = b.branchNo AND city = ‘London’);

not exist 当子查询没有返回值时为true，反之false SELECT staffNo, fName FROM Staff s WHERE NOT EXISTS ( SELECT * FROM PropertyForRent p WHERE s.staffNo = p.staffNo ); 每个 Staff 都去检查一下 PropertyForRent 表里有没有他们的名字。如果找不到，就表示这个人没有管理房产，就被选出来。

summary

SELECT [DISTINCT | ALL] column_list
FROM table_name [alias]
[WHERE condition]
[GROUP BY column_list]
[HAVING group_condition]
[ORDER BY column_list]

语句部分	用途说明
`SELECT`	指定要显示的列，可使用表达式或重命名（`AS`）
`DISTINCT`	去除重复结果行
`FROM`	指定查询的表或多表，可加别名
`WHERE`	对原始表数据行进行行级过滤
`GROUP BY`	按列分组，用于配合聚合函数（如 COUNT、SUM 等）
`HAVING`	对分组后的结果进行过滤（不能替代 WHERE）
`ORDER BY`	排序，默认升序，用 `DESC` 表示降序

🧾 执行顺序（背后的逻辑顺序）

FROM（先确定数据来源）

WHERE（先过滤原始数据）

GROUP BY（再按条件分组）

HAVING（对分组结果做进一步筛选）

SELECT（挑出要显示的列）

ORDER BY（最后排序）

join statement

natural join

自动基于同名字段匹配，如果两个表中有多个同名字段会导致错误连接

SELECT *
FROM Students
NATURAL JOIN Years;

如果两个表有多个相同的属性名，则要同时相等才会被筛选出来

join using

指定匹配的列 SELECT * FROM Client c

JOIN Meeting m USING (Cid);指定只要cid

join on

只通过显示提供的条件进行合并，最灵活

SELECT *
FROM Client c
JOIN Meeting m
ON c.Cid = m.Cid;

cross join\cartesian product

SELECT * FROM Students CROSS JOIN Courses;

Student有m行，Courses有n行，最后返回的是所有Student和Courses的组合，共mn种

left join

返回左表中的所有记录，即使右表没有匹配项，若右表没有匹配项时，对用字段为NULL

SELECT *
FROM Client c
LEFT JOIN Meeting m
ON c.Cid = m.Cid;

right join

RIGHT JOIN 会保留右表（Meeting）中的所有记录，即使左表（Client）中没有匹配项。如果左表中找不到对应的数据，就会在左表的字段上填 NULL。

SELECT *
FROM Client c
RIGHT JOIN Meeting m ON c.Cid = m.Cid;

full join

会返回两张表中的所有数据，本质上它是 LEFT JOIN 和 RIGHT JOIN 的结合。

两边都保留，谁无匹配谁null

SELECT *
FROM Client c
FULL JOIN Meeting m ON c.Cid = m.Cid;

Database Design Methodology

需求收集（Gather Requirements）

概念设计（Conceptual Design）

逻辑设计（Logical Design）

物理设计（Physical Design）