数据库系统概论——设计与应用开发篇

设计与应用开发篇

6 关系数据理论

7 数据库设计——重点是E-R图鸭！

数据库设计概述

需求分析

概念结构设计

联系只能发生在两个实体型之间，实体型与属性不能有联系，联系不怕多，在E-R图的概念结构设计阶段，用实体型之间的联系解决问题，加！联！系！

逻辑结构设计

E-R图向关系模式的转换

• 目标：把概念结构设计阶段设计好的基本E-R图转换为数据库管理系统产品支持的数据模型相符合的逻辑结构（关系模式）
  • 输入：E-R图（及相关的文档）——由实体型、实体的属性和实体型之间的联系三要素组成
  • 输出：关系模型的逻辑结构是一组关系模式的集合（实体、实体型、联系都是用关系来表达的）
  • 将实体型、实体的属性和实体型之间的联系转化成关系模式
• 转换原则
  • 一个实体型转换为一个关系模式（关系的属性就是实体的属性、关系的码就是实体的码）
  • 实体型间的联系要不要建成关系模式呢？分情况考虑
    • 一个一对一的联系可以转换为一个独立的关系模式，也可以与任意一端对应的关系模式合并
      • 如果转换为一个独立的关系模式：关系的属性是与该联系相连的各实体的码以及联系本身的属性；关系的候选码是每个实体的码均是该关系的候选码。
      • 如果与任意一段对应的关系模式合并：合并后关系的属性加入另一个关系模式的码和联系本身的属性
    • 一个一对多的联系可以转换为一个独立的关系模式，也可以与n端对应的关系模式合并
      • 如果转换为一个独立的关系模式：
      • 如果与n端对应的关系模式合并：合并后的关系属性：在n端关系中加入1端的码和联系本身的属性；合并后的
    • 一个多对多的联系必须转换成一个关系模式：关系的属性——与该联系相连的各实体的码以及联系本身的属性；关系的码——各实体码的组合
    • 三个或者三个以上的实体间的一个多元联系转换为一个关系模式
    • 具有相同码的关系模式可以进行合并——减少系统中的关系个数

数据集成：把各数据源集成一下，匹配不同数据源的相同字段

数据模型的优化

• 数据库的逻辑设计结果不是唯一的，所以要各种修改，进一步优化提高性能——关系范式——满足的级别越高，查询的性能就越低，因为连接的消耗还是很大的，当查询经常设计两个或多个关系模式的属性时，系统必须经常进行连接运算，就放在一个关系里面吧
• 非BCNF的关系模式虽然会存在不同程度的更新异常，但是如果只是查询不更新就不会产生实际影响
• 所以在性能和数据异常与冗余之间做权衡
• 要对关系模式进行必要的分解，提高数据操作效率和存储空间利用率——水平分解与垂直分解（——数据量究极大的时候，现在的大数据就是要把数据分开存——多个结点上存储就出现了一个错了就完蛋了的问题，所以多副本——大数据要求易扩展、高容错）
  • 垂直分解——数据量减少，把中国分为南方北方，查南方的时候不
  • 水平分解——因为现在数据库好多都是行存，列很多的话，存取效率就很低，垂直分解之后把常用的列弄出来，把列分组跟主码组合，查询整个元组的时候就对齐，这样效率也比较低——本质上是分解模式（上面说具有相同码的关系模式可以合并，主要还是看实际情况，性能代价什么的）

设计用户子模式

就是设计不同用户的外模式（视图）

• 使用更符合用户习惯的别名
• 可以对不同级别的用户定义不同的视图
• 简化用户对系统的使用

物理结构设计

数据库在物理设备上的**存储结构（行存、列存、混合存、有没有压缩、数据的存放顺序等）与存取方法（要不要用索引、要不要全表扫描、要不要排序等）**称为数据库的物理结构，它依赖于选定的数据库管理系统

• 物理设计的步骤
  • 确定数据库的物理结构
  • 对物理结构进行评价：空间和时间效率

数据库物理设计的内容和方法

• 充分了解：应用环境、数据库管理系统的内部特征，特别是系统提供的存取方法和存储结构
• 选择物理数据库设计所需要的参数：查询相关、更新相关、事物在个关系上运行的频率和性能要求

关系模式存取方法选择

• 常用存取方法
  • B+树索引存取方法
  • Hash索引存取方法：动态hash避免冲突
  • 聚簇索引存取方法：数据的顺序按照索引建立的顺序来排序+把特定元组放在一起——尤其是当SQL语句中包含有与聚簇码有关的ORDER BY、GROUP BY、UNION、DISTINCT的时候，使用聚簇索引效果特别明显
    • 设计候选聚簇：常在一起进行连接操作的关系可以建立组合聚簇；如果一个关系的一组属性经常出现在
    • 检查候选聚簇中的关系，删除不必要的
• 建索引的条件
• 维护和查找索引的开销其实也是蛮大的，所以要结合实际的情况建立索引

确定数据库的存储结构

• 确定数据的存放位置
  • 易变部分与稳定部分分开存放
  • 经常存取部分与存取频率较低部分分开存放
• 确定系统配置：系统默认的配置变量：包括：同时使用数据库的用户数、同时打开数据库的对象数、内存分配参数、缓冲区分配参数、存储分配参数、物理块的大小、物理块填装因子、时间片大小、数据库的大小、锁的数目等

评价物理结构

存取的时间、空间效率和维护代价以及用户要求

数据库的实施和维护

8 数据库编程

概述

• 为什么要学习数据库编程？
  数据库编程的重要性：高级编程语言具有模块化、流程控制能力强等优点，但数据缺少结构化、缺少数据与程序之间逻辑独立性和物理独立性；数据库和数据库管理系统具有数据结构化的特点，数据与程序之间具备逻辑独立性和物理独立性，有强大的数据定义、操纵、控制功能，但是缺少流程控制能力，难以实现应用业务中的逻辑控制，所以数据库编程可以融合高级语言和数据库管理系统的优点，实现更强大的功能
• 什么是数据库编程？
  把数据库的技术融入到高级编程语言中（嵌入式SQL）——主要面向层次网状数据库，现在用的比较少了
  把高级语言编程中的流程控制能力融入到SQL中（过程化SQL）——现在较常用
  需要跨库操作的时候需要使用：高级语言有一个接口连接关系数据库管理系统（ODBC/JDBC编程）——现在常用

嵌入式SQL

• 嵌入式的SQL：C语言+文件存取：建结构体——打开文件——一条一条读记录给变量赋值——修改——写入——这种操作是面向每条记录进行操作的，而数据库是面向集合的操作，嵌入式SQL就需要解决这种差别：
  • 主程序是高级语言
  • SQLCA：通信区（是一个结构体），描述工作状态和运行环境
  • 主变量（建立数据库和高级语言中的交互——比如这个例子中的结构体变量）
  • 游标（构建集合到单条记录的过渡）游标是系统为用户开设的一个数据缓冲区，存放SQL语句的执行结果（集合）——不用游标的语句（结果集只有一条记录；create table；非current形式的update、delete）；使用方法：说明游标——打开游标——移动游标指针，然后取当前记录——关闭游标；delete和update操作的时候，where条件没有用，直接用UPDATE/DELETE ... WHERE CURRENT OF <游标名字>

EXEC SQL <embedded SQL statement> ENE_EXEC
# OR
EXEC SQL <embedded SQL statement>;
# 下面是一个例子
EXEC SQL INCLUDE SQLCA;
# 下面是主变量的定义
EXEC SQL BEGIN DECLARE SECTION;
/*说明主变量department, HSno, HSname*/
EXEC SQL END DECLARE SECTION;
# 下面是游标的定义与声明，CURSOR表示指向在集合的前面
# 游标其实是对集合中的数据一条一条的遍历
EXEC SQL DECLARE SX CURSOR FOR
	SELECT Sno, Sname, Ssex, Sage
	FROM Student
	WHERE SDept =: deptname
	FOR UPDATE/DELETE OF Sage; /*说明游标，是更新或删除的*/
EXEC SQL OPEN SX		/*打开游标*/
WHILE(1)
	# 把当前的游标记录赋给了主变量之后游标next
	EXEC SQL FETCH SX INTO :HSno, :HSname, :HSsex, :HSage;
if(sqlca.sqlcode <> SUCCESS)
	break;
printf

• 还有动态SQL

EXEC SQL BEGIN DECLARE SECTION;
const char *stmt = "CREATE TABLE test(a int);";
EXEC SQL END DECLARE SECTION
...
# 执行引号里面的语句
EXEC SQL EXECUTE IMMEDIATE :stmt;
# 动态的
EXEC SQL BEGIN DECLARE SECTION;
const char * stmt = "INSERT INTO test VALUES(?);";
EXEC SQL END DECLARE SECTION;
...
# 好处是不用每次都写一遍语句，告诉系统这个东西是动态的嵌入式SQL
EXEC SQL PREPARE mystmt FROM :stmt;
EXEC SQL EXECUTE mystmt USING 100;
EXEC SQL EXECUTE mystmt USING 200;

• 数据库负责将高级语言中的SQL语言翻译成高级语言编译器懂的函数语言

过程化SQL——PL/SQL（Procedural Language SQL）

• 将高级语言的流程化控制能力融入到SQL中去
• 应用：
  • 编写存储过程：procedure——存储过程由用户、应用程序或触发器显示调用
  • 编写触发器：trigger——触发器则是当触发事件发生时由系统触发执行
• 块结构
  • 定义部分
    DECLARE
    …变量、常量、游标、异常等
  • 执行部分
    BEGIN
    …SQL语句、流程控制语句
    EXCEPTION
    …异常处理部分
    END;

DECLARE sno INT; #变量名在前，类型在后
BEGIN
	sno := 1001; # :=赋值语句区分逻辑判断
	WHILE sno<1011 LOOP
		INSERT INTO Student(Sno)
		VALUES(to_char(sno,'9999'));
		sno := sno +1
	END LOOP
END;

数据库系统里面好多定义一个magic，大概是记录版本号，对于程序的更新过程做个记录，程序与内部结构版本号的统一

DECLARE
	cnt int;
	user varchar(20) := 'Levi';
BEGIN
	SELECT COUNT(*)
	INTO cnt
	FROM mylog
	WHERE who = user
	if cnt > 0 then
		update mylog
		

• 循环结构的控制
  • 简单的loop
  • while - loop
  • for - loop

# procedure是个类似于函数的东西，可重用
create or replace procedure loop1() as
	declare
		i int := 1
	begin
		loop
			insert into number_table
				values(i);
			i := i + 1;
			exit when i > 10;
		end loop;
	end;
perform procedure loop1();

# 异常的捕获与处理
CREATE PROCEDURE TestRaise(i int) as # i是输入变量
BEGIN
INSERT INTO testRaise VALUES(i);
IF 0 < i AND i < 10 THEN
	RAISE NOTICE 'your input value "%" is valid.',i;
ELSE
	RAISE EXCEPTION 'your input value "%" is invalid.',i;

# 游标的使用
CREATE TALBE emp(emp_no CHAR(10), salary INT);
CREATE PROCEDURE proc_open() as
DECLARE
	
BEGIN

JDBC——Java DataBase Connectivity

【使用】在不同数据库软件中的移植代价很低或者没有，实现跨库操作

• 概述
  • 提供了一组标准API，用于访问关系数据库
    • 如果不是标准API，就要把原生API映射到标注API
  • API类库位于java.sql.（包括基本的打开操作）*和java.sqlx.*中
    • PS：嵌入式的先打开文件，然后由数据库系统将SQL语言编译成高级语言看得懂的语句，然后再传输给高级语言系统运行
  • 为Applet、Servlet和应用程序提供了统一的访问方式
  • JDBC提供的并不是嵌入式的结构化查询语言

应用JDBC的七个步骤

• 装载驱动程序——假设有多个数据源，想要应用程序能识别所有的数据源，就得先装载驱动程序
• 定义连接的URL——IP地址、端口号（对应的服务）、用户名和密码、还可能有一些权限的设定
• 建立连接关系
• 创建一个Statement对象
• 执行一个查询
• 处理结果集
• 关闭连接