软件设计师笔记——（第九章：数据库）

一、下午题总结（试题二）（⭐⭐⭐）1、第一问总结2、第二问总结3、第三问总结二、数据库设计（⭐⭐⭐）1、数据库设计2、三级模式结构三、数据模型（⭐⭐⭐）1、E-R模型四、关系代数（⭐⭐⭐）1、关系数据库基本概念2、关系代数运算五、数据库SQL语句（⭐⭐⭐）1、SQL数据定义（DDL）2、SQL数据查询（DQL）3、SQL数据更新（DML）4、SQL访问控制（DCL）六、规范化理论（⭐⭐⭐）1、函数

大小胖虎

964人浏览 · 2023-10-06 17:17:15

大小胖虎 · 2023-10-06 17:17:15 发布

一、下午题总结（试题二）（⭐⭐⭐）

1、第一问总结

1.E-R图连线：

①需求分析里说明了，一定要按照说明上连线。
②当一段话中出现了3个实体集，基本就是“三岔路口”（实体集1：实体集2：实体集3）。
③联系集无要求命名下，命名合适即可，尽量不要重名。

2、第二问总结

1.补全填空

先根据需求分析描述的内容确定一部分。
最后根据E-R图的连接内容最终确定。（联系的唯一标识）
若是补全联系集，找所有连接的属性集。

2.“□”(实体集)的主键和外键

“□”的主键需求分析中一般都会给出；“□”的外键则是找由“◊”连接的外部实体集（唯一标识）且自己属性中含有的键。

3.“◊”(联系集)的主键和外键（主键与外键重复出现也没事）

一对一：根据题目要求选择。
一对多：“◊”的主键是”多“对应的那个实体集的主键；“◊”的外键则是找自己连接的“□”（前提是唯一标识）且自己属性中含有的键。
多对多：“◊”的主键一般是2~3个（会涉到时间一类的，不同时间点干相同属性）；“◊”的外键则是找自己连接的“□”（唯一标识）且自己属性中含有的键。
多对多对多：主键仔细看需求说明、关系模式。外键则是找自己连接的“□”（唯一标识）且自己属性中含有的键。

3、第三问总结

1.A属于弱实体吗？

答：A属于弱实体，因为A的存在要依赖B的存在为前提。

2.地址进一步分为邮编、省、市、街道是否是简单属性？

答：该属性不属于简单属性，因为简单属性是原子的，不可再分的，而该地址进一步分为邮编、省、市、街道，所以通信地址是复合属性。

3.同名不同意思，是否属于命名冲突？

答：不属于命名冲突。因为这两个属性分别属于不同的关系模式，可以通过"关系.属性"进行区别，既可以用“.......”和“.......”来进行区别。

4.规范性问题→（看处在第几范式，找所对应范式存在的问题）

答：存在规范性问题。关系模式存在传递依赖，没有达到3NF。该模式可分解为......。

5.增加一个实体集的多个属性，是否需要增设一个实体？

答：不需要增设一个实体。因为部门和主管之间的联系是多对多的，那么只需要将联系转换成一个独立的关系模式即可，如下关系模式为........。

6.在职员关系模式中，假设每个职员有多名家属成员，那么职员关系模式存在什么问题？

答：存在数据冗余、修改异常、插入异常、删除异常等问题，应将“职员”关系模式进行分解，分解如下：...........。

7.请问该关系模式的主键为全码吗？为什么？

不正确，全码是该关系模式的所有属性组成的属性组是该关系模式的主键。但是该关系模式的主键是“......”，并不是所有属性组成的，所以不是全码。

二、数据库设计（⭐⭐⭐）

1、数据库设计

新奥尔良法将数据库分为4个主要阶段：

①用户需求分析：收集用户需求，确定系统边界。（产物包括：数据流图、数据字典、需求说明）（逻辑设计以需求分析结果为依据）

②概念设计：设计E-R图（实体-属性图）。（存在属性冲突、命名冲突、结构冲突）

③逻辑设计：将E-R转换成关系模式。（需要考虑：关系规范化，确定完整性约束）

④物理设计：根据生成的表等概念，生成物理数据库。

2、三级模式结构

1、数据库系统采用三级模式结构

①概念模式（也称模式）：对应→基本表

②外模式（也称用户模式或子模式）：对应→视图

③内模式（也称存储模式）：对应→存储文件。（索引→物理层（对应内模式）看到索引就选物理层/内模式即可）

2、数据库系统在三级模式结构之间采用两级映射（保证了数据的逻辑和物理独立性）

①模式/内模式映像：实现概念模式到内模式之间的相互转换（物理独立性）

②外模式/模式映像：实现了外模式到概念模式之间的相互转换（逻辑独立性）（创建视图构建的是外模式和外模式/模式之间的映射）

3.经典例题

三、数据模型（⭐⭐⭐）

数据模型三要素：

①数据结构：是所研究的对象类型的集合（系统静态特性）
②数据操作：是对数据库中各种对象(型)的实例(值)允许执行的操作集合（系统动态特性）
③数据的约束条件：是一组完整性规则的集合。

1、E-R模型

1.实体：每个实体由一组特性(属性)来表示，其中的某一部分属性可以唯一表示实体。实体集是具有相同属性的实体集合。

2.联系：实体集之间的对应关系称为联系。实体集之间的联系类型有：

一对一联系
一对多联系
多对多联系

3.属性：是实体某方面的特性。E-R模型中的属性有以下分类：

简单属性：简单属性是原子，不可再分的
复合属性：复合属性可以细分为更小的部分（eg：通信地址可以进一步分为：邮编、省、市、街道）
单值属性：定义的属性对于一个特定的实体只有一个值
多值属性：定义的属性对应一组值，则称为多值属性。
NULL属性：当实体在某个属性上没有值或属性值未知时，使用NULL值，表示无意义或不知道
派生属性：派生属性可以从其他属性得来。（eg：2020年入职目前2022年→2年（工作年限））

四、关系代数（⭐⭐⭐）

1、关系数据库基本概念

1.属性和域：描述一个事物常常取其若干特征表示，这些特征称为属性（表中的列称之为属性，列中第一行称之为属性名）。每个属性的取值范围的集合，称为该属性的域。

2.笛卡尔积(×)：S1×S2最终属性列为S1+S2属性列，记录数为S1*S2记录数。（在SQL语句中直接逗号隔开就行不加×号）

3.关系的相关名词

候选码：关系中的一个属性或属性组的值能够唯一地标识一个元组，且他的子集不能唯一的标识一个元组。
主码：若一个关系有多个候选码，则选定其中一个为主码。
主属性：候选键内的属性为主属性，其他属性为非主属性。
外码：如果关系模式R中的属性或属性组非该关系的码，但它是其他关系的码，那么该属性集对关系模式R而言是外码。
全码：关系模式的所有属性组是这个关系模式的候选码，称为全码。

4.关系的三种类型

基本关系：实际存在的表，它是实际存储数据的逻辑表示。
查询表：查询结果对应的表。
视图表：基本表或其他视图表导出的表，不独立存储在数据库中，常称为虚表。

5.完整性约束

实体完整性约束：即主键约束，主键值不能为空，也不能重复。
参照完整性约束：即外键约束，外键必须是其他表中已经存在的主键的值，或者为空。
用户自定义完整性约束：自定义表达式约束（如设定年龄属性的值必须在0到150之间）。

2、关系代数运算

1、基本的关系代数运算如下（中文必须要用单引号括起来，数字不能用单引号）

1、内连接：从笛卡尔积中选出满足条件的元组

自然连接：特殊的等值连接，相同属性对应的值必须相同才能进行连接。（去除重复列）
θ连接
等值连接

2、外连接：用于处理确实的信息

左外连接：取出左侧关系中所有与右侧关系中任一元组都不匹配的元组，用空值NULL来填充所有来自右侧关系的属性，构成新的元组，将其加入自然连接的结果中
右外连接：同理。
全外连接：完成左外连接和右外连接的操作。

3.经典例题

五、数据库SQL语句（⭐⭐⭐）

1、SQL数据定义（DDL）

1、创建数据库
--1、创建库
create database db1;
--2、创建库是否存在，不存在则创建
create database if not exists db1;
--3、查看所有数据库
show databases;
--4、查看某个数据库的定义信息 
show create database db1; 
--5、删除数据库
drop database db1; 
2、表的创建、修改、删除（完整性约束条件有NULL(空)、UNIQUE(唯一的)、NOT NULL UNIQUE(不空且取值唯一)）
--1、创建表
create table student(
    id int,
    name varchar(32),
    age int 
);
--2、修改表名
alter table 表名 rename to 新的表名;
--3、添加一列
alter table 表名 add 列名 数据类型;
--4、删除列
alter table 表名 drop 列名;
--5、删除表
drop table 表名;
3、索引的创建和删除。（采用三级模式的数据库系统中如果对一个表创建聚簇索引，那么改变的是数据库的内模式）

<次序>：可选升序(ASC)或降序(DSC)，默认值为ASC
UNIQUE：表明此索引的每一个索引值只对应唯一的数据记录
CLUSTER：表示要建立的索引是聚集索引。

4、视图的创建删除和修改（WITH CHECK OPTION ）

视图是从一个表或多个表或视图中导出的，是一个虚拟表。
子查询可以是任意复杂的select语句，但通常不允许含有order by子句和distinct 短语
WITH CHECK OPTION 表示对 UPDATE、INSERT、DELETE 操作时要保证更新、插入或删除的行满足视图定义中的谓词条件
--1、创建视图
CREATE VIEW student
AS SELECT * from 学生表 whuere 专业='计算机'
WITH CHECK OPTION（满足视图的谓词条件）
--2、修改视图
update student
set name='wymh'
where 学号=211021116
--3、删除视图
drop view student 

2、SQL数据查询（DQL）

聚集函数：以一个值的集合为输入，返回单个值的函数。（平均值AVG、最小值MIN、最大值MAX、求和SUM、计数COUNT）
where后不能更聚合函数
GROUP BY子句：对元组进行分组，保留字GROUP BY后面跟着一个分组属性列表。
字符串模式匹配（LIKE）： “%” 匹配任意字符串、“-” 匹配任意一个字符。
--1、按照性别分组。分别查询男、女的平均分,人数。要求：分数高于70分，分组之后。人数要大于2个人
SELECT sex , AVG(math),COUNT(id) 
FROM student 
WHERE math > 70
GROUP BY sex 
HAVING COUNT(id) > 2;
--3、分组排序
SELECT * FROM person ORDER BY math; --默认升序
SELECT * FROM person ORDER BY math desc; --降序
--3、查询姓马的有哪些？
SELECT * FROM student WHERE NAME LIKE '马%';
--4、查询姓名第二个字是化的人			
SELECT * FROM student WHERE NAME LIKE "_化%";	

3、SQL数据更新（DML）

1、增加、删除、修改的操作

--1、不写列名（所有列全部添加）
insert into 表名 values(值1,值2,...值n);
--2、插入部分数据
insert into 表名(列名1,列名2) values(值1,值2);
--3、删除表中数据
delete from 表名 where 列名  = 值;
--4、删除表中所有数据
delete from 表名;
--5、不带条件的修改(会修改所有行)
update 表名 set 列名 = 值;
--6、带条件的修改
update 表名 set 列名 = 值 where 列名=值;

4、SQL访问控制（DCL）

1、授予、撤销权限的操作——[WITH GRANT OPTION]

--1、给某人授予某权限
grant 权限列表 on 数据库名.表名 
to '用户名'@'主机名';
[WITH GRANT OPTION]
--2、给张三用户授予所有权限，在任意数据库任意表上
GRANT ALL ON *.* 
TO 'zhangsan'@'localhost';
[WITH GRANT OPTION]
--3、撤销权限：
revoke 权限列表 on 数据库名.表名 from '用户名'@'主机名';

5、经典例题

六、规范化理论（⭐⭐⭐）

1、函数依赖

1.部分函数依赖：A可确定C, (A,B)也可以确定C，(A,B)中的一部分（即A）可以确定C，称为部分函数依赖。

2.完全函数依赖：在R(U)中，如果X→Y，并且对于X的任何一个真子集X’，都有X’不能决定Y，则称Y对X完全函数依赖。

3.传递函数依赖：当A和B不等价时， A可确定B，B可确定C，则A可确定C，是传递函数依赖。

4.候选码：关系中的一个属性或属性组的值能够唯一地标识一个元组，且他的子集不能唯一的标识一个元组。

5.主码：若一个关系有多个候选码，则选定其中一个为主码。

6.主属性：候选键内的属性为主属性，其他属性为非主属性（闭包的求解结果就是主属性）

7.常用的函数依赖公理

传递律：如果X→Y且Y→Z，则X→Z成立。
合并规则：｛X→Y，X→Z｝X → YZ 。
分解规则：如果X→Y且Z⊆Y，则X→Z成立。
伪传递规则：｛X→Y，WY→Z｝XW→Z

8.经典例题

2、规范化——范式

1.第一范式（1NF）

存在数据冗余、修改异常、插入异常、删除异常（部分函数依赖造成的）。
进行模式分解，消除非主属性的部分函数依赖的问题，就会变成第二范式。

2.第二范式（2NF）

虽然不存在部分函数依赖，但仍存在传递依赖
进行模式分解，消除非主属性的传递依赖的问题，就会变成第三范式。

3.第三范式（3NF）

仍存在主属性的部分函数依赖和传递依赖
进行模式分解，消除主属性的传递依赖的问题，就会变成BCNF。

4.巴克斯范式（BCNF）

完全消除了插入、修改、删除的异常
BCNF不是第四范式

5.经典例题

3、模式分解

1.对一个给定的模式进行分解，分解后的模式要与原模式等价：

分解具有无损连接性【如果R的分解为p={R1，R2}，R1∩R2->(R1-R2)或者R1∩R2->(R2-R1)】
分解要保持函数依赖
分解既要无损连接性，又要保持函数依赖

2、经典例题

七、数据库的控制功能（⭐⭐）

1、事务管理与并发控制

1.事务的四个特性：

原子性：事务的所有操作在数据库中要么全做，要么全都不做。
一致性：一个事务独立执行的结果，将保持数据的一致性，即数据不会因为事务的执行而被破坏。
隔离性：一个事务的执行不能被其他事务干扰。
持久性：一个事务一旦提交，它对数据库中数据的改变必须是永久的，即便系统出现故障时也是如此。

2.并发控制技术(封锁技术)（并发操作带来的问题：丢失更新，不可重复读，读脏数据）

排它锁(X)：若事务T对A加上X锁，则只允许T读取和修改A，其他事务都不能再对A加任何类型的锁，直达T释放A上的锁。
共享锁(S)：若事务T对A加上S锁，则只允许T读取A，但不能修改A，其他事务只能在对A加S锁，直到T释放A上的S锁。

3.经典例题

2、数据库的备份与恢复

1.事故类型：内部故障、系统故障、介质故障、计算机病毒

2.备份方法：（原理是"建立冗余数据"）

日志文件：进入事务处理先写入日志文件，当系统正常运行时，将缓冲区内容写入数据文件。
静态转储和动态转储
海量转储和增量转储

3.恢复

4.数据库镜像

八、零碎知识点（⭐⭐⭐）

1.关系模式

由数据结构、关系操作集合、关系完整性约束三部分组成
用二维表结构表示实体

2.分布透明性

分片透明性：用户或应用程序不需要知道逻辑上访问的表具体是如何分块存储的
位置透明性：应用程序不关心数据存储物理位置的改变
逻辑透明性：用户或应用程序无需知道局部使用的是哪种数据模型
复制透明性：用户或应用程序不关心复制的数据从何而来

3.分布数据库

共线性：存储在不同节点的数据共享
自治性：每个结点对本地数据能独立管理
可用性：某一场地故障时，可以用其他场地副本从而不至于瘫痪
分布性：数据在不同场地上的存储

4.OLAP通过多维的方式进行分析

5.DES共享密钥加密（对称加密）

6.数据库函数

首先第一步：return integer
接着第二步：declare d_count integer