数据库分析与设计 王有天 湖北经济学院 市场 表 现 层 界面 物流 业 务 流 程 用 户 用 户 用 户 用 户 用 户 业 务 控 制 业 务 支 持 … 信息流 权限管理程序 用 户 业 务 经 营 4 2 现金流 业 务 流 程 3 1 业 务 流 程 …… 业 务 流 程 用 户 用 户 用 户 用 户 控 制 层 用 户 控 制 用户管理程序 业务处理指令，往往体现为SQL的批处理。 数据处理指令：比如SQL。 数据库管理程序 数据 电子商务系统软硬件环境 数 据 层 数据 商务系统B/S;C/S 邮箱 域名 分布式软件系统(B/S;C/S. P2P) 2015/10/1 2 数据层 频 经营 用户 使用 道 数据发布程序 开发人员维护 数据库 数据资源层 内容输入界面 开发人员维护 逻辑控制层 内容输入 频道 市场 业务经营层 用户 需求 价值实现层 • 数据是企业信息系统的核心。 2015/10/1 3 对数据的管理系统 • 数据库 – DBMS • DataBase Management System 2015/10/1 4 为什么要有DBMS • 数据完整性 • 事务的处理 – 如：转账事务 • 相对独立的系统模块 – 数据与程序分开。 – 软件的相对独立研发 • 集成数据处理的机制。 – 故障恢复 • Log – 权限 2015/10/1 5 DBMS特点 • 封装 – 不能通过DBMS以外的渠道访问数据库。 – 否则破坏完整性、一致性。 • • • • 支持事务处理 能够故障恢复 控制访问 并发操作 2015/10/1 6 DBMS的发展 • 图书馆/资料库 – 如果管理不善，就不能成为系统。 –人 • 文件系统 – 没有封装；没有事务处理；没有故障恢复。 • 数据库 2015/10/1 7 DBMS的分类 • 网络型/层次型 • 关系型 • 扩展 – 面向对象 – 模糊数据 – 多媒体数据库 – 网络文件管理 2015/10/1 8 关系型数据库 • 数据存储在表中 学号 姓名 性别 1 张三 男 2 李四 女 3 2015/10/1 账号 名称 密码 学生 1 2 3 a b c 课程 1 2 3 名称 电商 信息 … 1 2 3 1 2 1 9 Entity Relation 账号1 学生1 张三 2015/10/1 10 关系种类： • 1:n • n:m 学号 姓名 性别 – 学生和课程 学号 1 1 3 2015/10/1 课程 1 2 2 1 张三 男 2 李四 女 3 课程 1 2 3 名称 电商 信息 … 11 关系型数据库的优点 • 记录等长 – 检索快。 • 例如：总记录个数。 • 某个字段值 2015/10/1 12 基本词汇 • 表 – 字段 – 记录 • • • • • 字段 关键字段 记录 记录的字段值 关系 – 定义某个表中某字段的值取自另外一个表 2015/10/1 13 记录的增加删除更新 • 记录没有顺序。 – 查询语言可以按字段排序。 • 删除记录 – 取决于DBMS • 一般作标记，重用空间。 • 可以压缩表。但DBMS一般不自动进行。 • 增加记录 – 删除记录或最后 • 更新记录 – 定位到记录字段，覆盖。 2015/10/1 14 约束和触发器 • 字段 – 取值范围 • 某个记录必须符合某个范畴。 • 更复杂的约束需要触发器。 2015/10/1 15 数据库中表和类的关系 • 表 – 固定字段，表示某一类对象。 – 每个记录相当于类的实例。 – 每个字段值相当于实例的属性或者与其他实例 的关系。 • 非同类的数据存储在同一个表，并不适合。 2015/10/1 16 记录和语句的关系 • 每个记录相当于符合特定语法的语句。 • 表示一定的语义。 • Null表示不知道 – Null的运算 2015/10/1 17 关系型数据库的范式 • 第一范式： – 字段的原子性。要么视 为原子。要么应该再分。 – 否则不便查询。 字段1 字段2 字段3 字段3.1 2015/10/1 字段4 字段3.2 18 第二范式 • 非关键字段完全函数依赖于关键字段。 – 函数依赖 • y=f(x); 不能说y=f(x)附近。 2015/10/1 19 不符合第二范式造成问题 • 假定选课关系表为SelectCourse(学号, 姓名, 年龄, 课程名称, 成绩, 学分) – 关键字为组合关键字(学号, 课程名称)，因为存在如下 决定关系： (学号, 课程名称) → (姓名, 年龄, 成绩, 学分) • 这个数据库表不满足第二范式，因为存在如下决 定关系： – (课程名称) → (学分) – (学号) → (姓名, 年龄) – 即存在组合关键字中的字段决定非关键字的情况。 2015/10/1 20 不符合第二范式造成问题 • 数据冗余： 同一门课程由n个学生选修，“学分”就重复n-1次；同一个学生选 修了m门课程，姓名和年龄就重复了m-1次。 • 更新异常： 若调整了某门课程的学分，数据表中所有行的“学分”值都要更 新，否则会出现同一门课程学分不同的情况。 • • 插入异常： 假设要开设一门新的课程，暂时还没有人选修。这样，由于还没 有“学号”关键字，课程名称和学分也无法记录入数据库。 删除异常： 假设一批学生撤销某些课程的选修，这些选修记录就应该从数据 库表中删除。但是，与此同时，课程名称和学分信息也被删除了。很 显然，这也会导致插入异常。 2015/10/1 21 不符合第二范式造成问题 • 冗余 – 重复的数据 • 更新麻烦 • 更新不一致造成冲突 • 冗余的根源在于 – 非关键字段不是由关键字段完全决定。 – 部分就能决定，结果另外一部分关键字改变时， 非关键字段重复。 2015/10/1 22 关系型数据库的范式 • 第三范式 – 非关键字段之间不存在传递依赖。 – 否则存在冗余。 • 冗余带来同样的问题。 2015/10/1 23 适当的冗余 • 适当的冗余 – 造成重复但可能提高性能。 – 如index是故意的冗余。 • 照顾语义。 2015/10/1 24 数据库设计的多样性 • 对同一件事情描述方法可以不同。 • 因此数据库的结构有所不同。 • 但这些数据库中的数据应该是等价的。 – 可以相互推导。 2015/10/1 25 关系型数据库的数据操作 • DBMS完成。 • RDBMS支持查询语言SQL • SQL=Structured Query Language – 是一个语言规范。DBMS一般支持该规范，并 有自己的扩展。 – 查询和操作：增删改查 2015/10/1 26 SQL的组成(分类) CREATE DATABASE TABLE VIEW INDEX SQL DDL： ALTER DROP SQL QUERY：SELECT INSERT SQL SQL DML： UPDATE DELETE GRANT SQL DCL： REVOKE 2015/10/1 … FROM … … … … 27 增加记录 • Insert Table1(Field1,…) Values(…,…) • Insert Table1(Field1,…) Select … 2015/10/1 28 更新 • Update Table1 Set Field1=…, Field2=… Where … 2015/10/1 29 删除 • Delete Table1 Where… 2015/10/1 30 Select, Where • 筛选记录 – Select Field1, Field4 from Table1 • Where (Field1=‘张三’ and Field2=2) or Field1=‘李 四’ • 筛选条件 – Like – Between And – >,>=,<,<=,= 2015/10/1 31 Join, On 竖着连接表，按一定条件 • Table1 join Table2 on Table1.Field1=Table2.Field1 – Left Join – Right Join • 对于连接的表可以进行查询（如选择） 2015/10/1 32 Into 查询结果存储在表中 • Select Table1.Field1,… into NewTable1 From … 2015/10/1 33 Union 竖着合并表 Select Field1,… From Table1 Where … Union Select Field1,… From Table2 Where 2015/10/1 34 交叉表查询 部门 人员 季 度 销售收 入 销售1部 张三 1 300000 销售1部 张三 2 560012 销售1部 张三 3 62000 销售1部 张三 4 329910 0 销售2部 李四 2 3780 销售2部 王五 2 328999 销售2部 王五 3 221411 1 销售2部 王五 4 234999 9 销售1部 张三 1 2425 销售1部 张三 2 3566 销售1部 张三 3 134213 销售1部 张三 4 233 销售2部 王五 4 2342 2015/10/1 TRANSFORM Sum(销售收入) AS 销售收 入之总计 SELECT 部门, 人员 FROM 销售 GROUP BY 部门, 人员 PIVOT 季度; 部门 人员 1 2 3 销售1部 张三 302425 563578 销售2部 李四 3780 销售2部 王五 328999 4 196213 3299333 2214111 2352341 35 as • 字段别名和表别名 • 表别名允许给表另起一个名字，相当于将 表复制一遍参加查询。 – 视为单独的表，临时。 2015/10/1 36 Group by • 进行分组集合运算 2015/10/1 37 Having， Where • Where 决定哪些元组被选择参加运算，作 用于关系中的元组 • Having 决定哪些分组符合要求，作用于分 组 2015/10/1 38 Order By • 排序 – Desc – Asc 2015/10/1 39 In,Exists SELECT FROM … WHERE S# IN ( SELECT S# FROM Student WHERE Course = ‘C1’ )； 2015/10/1 40 In, Exists SELECT SN FROM S WHERE EXISTS ( SELECT * FROM SC WHERE S.S#=SC.S# AND C#=‘C1’ )； 2015/10/1 41 查询的图形化-QBE 2015/10/1 42 查询的图形化 2015/10/1 43 权限管理语言的图形化 2015/10/1 44 电子商务系统中对SQL的调用 • DBMS可以批处理SQL语句。 • 电子商务系统调用SQL – 效率更高 – 安全，保持数据完整性。 – 支持事务。 2015/10/1 45 事务处理 • 事务 – 一组数据操作，要么成功要么失败。 • SQL BEGIN TRANSACTION … COMMIT ROLLBACK 2015/10/1 46 事务特点 • Atomicity • 事务是数据库的逻辑工作单位，事务中包括的诸操作 要么都做，要么都不做。 • Consistency • 事务执行的结果必须是使数据库从一个一致性状态变 到另一个一致性状态 • Isolation • 一个事务的执行不能被其他事务干扰。并发执行的各 个事务之间不能互相干扰。 • Durability • 一个事务一旦提交，它对数据库中数据的改变就应该 是永久性的。 2015/10/1 47 故障恢复 • 出现异常。造成事务中断。比如断电。 – 此时数据可能不一致。 – 单独的数据库文件难于解决该问题。 • Log – 写入数据库文件前，先写日志。在日志中保留 恢复信息。 – 在数据库文件写成功后，在日志中标记。 – 检查日志。没有成功的写入应该尝试再写或者 放弃。 2015/10/1 48 日志格式 • 每条日志记录的内容 – 事务标识 – 操作类型（插入、删除或修改） – 操作对象（记录ID、Block NO.） – 更新前数据的旧值（对插入操作而言，此项为 空值） – 更新后数据的新值（对删除操作而言, 此项为 空值） 2015/10/1 49 故障恢复 • 系统重启时， – 如果日志完整，向前继续执行事务 – 如果日志不完整，撤销该事务。 2015/10/1 50 数据库软件 • 一般是关系型的 – 文件型 • Access – 没有日志。 – 服务 • Oracle • Ms Sql Server • MySQL 2015/10/1 51 数据库软件 • 这些软件都支持SQL，并扩展。 – SQL Server下的T-SQL 2015/10/1 52 SQL Server简介 • 原来为另外一家公司产品，后被微软收购。 最新版本2005。 • 支持.net。 – 可以定义类型 – 可以定义函数、存储过程、触发器等。 – 支持xml 2015/10/1 53 SQL Server安装 • 安装时会检查： – 至少512M，建议1G内存。 – 安装在Windows Server 2003 • 需要Service pack较新版本 – 最好安装有.net 2015/10/1 54 SQL Server数据库文件 • 管理多个数据库 • 数据库文件 – 一个或多个文件组 • 每个文件组有一个或多个文件。 • 分布在多个磁盘上可以并行读取。 • 不放在压缩磁盘上。 • 日志 2015/10/1 55 连接 • 可以用客户端连接。比如: – 刚才的Management Studio – Access – 理解为一种服务/客户端使用关系。 • 程序连接 – 比如：Asp.net中建立SqlConnection对象，指 定数据库位置(地址)、用户名、密码、数据库 2015/10/1 56 管理 2015/10/1 57 Mgmt Studio: T-SQL 2015/10/1 58 Access+SQL Server Access文件 Access程序 SQL Server Mgt. Studio 2015/10/1 SQL Server服务 59 Access + SQL Server 2015/10/1 60 新建 2015/10/1 61 浏览 2015/10/1 62 2015/10/1 63 2015/10/1 64 2015/10/1 65 2015/10/1 66 2015/10/1 67 2015/10/1 68 2015/10/1 69 2015/10/1 70 2015/10/1 71 2015/10/1 72 2015/10/1 73 Access + SQL Server 实际例子 2015/10/1 74 2015/10/1 75 T-SQL • 除了标准SQL外，支持更多功能。 • 可以有变量、逻辑控制，因此可以编程。 • SQL Server 2005支持在.net环境中编程， 如类型、触发器等。 2015/10/1 76 T-SQL 2015/10/1 77 数据库空间 • 一般单独提供。 • 目前市场上提供的数据库空间有： – MySQL – MS Sql Server – Oracle较少。 • 价格差不多。 2015/10/1 78 数据库的维护 • • • • • 备份 复制/分布 数据完整性 权限控制 安全性 2015/10/1 79 数据库的备份 • 用于在系统发生故障后还 原和恢复数据。 • 对于例行的工作（例如， 将数据库从一台服务器复 制到另一台服务器、设置 数据库镜像、政府机构文 件归档和灾难恢复）也很 有用。 2015/10/1 80 数据备份和差异备份 • 数据备份是指包含一个或多个数据文件的完整映 像的任何备份。 – 数据备份会备份所有数据和足够的日志，以便恢复数 据。可以对整个或部分数据库，或者一个或多个文件 进行数据备份。 • 差异备份是基于之前进行的数据备份而言的。 – 它仅包含自数据备份之后更改的页面区。此数据备份 称为“基准备份”或差异“基准”。如果存在相应的 基准备份，则可以对整个或部分数据库，或者一个或 多个文件执行差异备份。 2015/10/1 81 数据库的复制 • 复制是一组技术，它将数据和数据库对象 从一个数据库复制和分发到另一个数据库， 然后在数据库间进行同步，以维持一致性。 • 使用复制，可以在局域网和广域网、拨号 连接、无线连接和 Internet 上将数据分发到 不同位置以及分发给远程或移动用户。 2015/10/1 82 OLAP vs OLTP • OLTP=OnLine Transactions Processing – 生产用 – 使用频率频繁 – 更新 • OLAP=OnLine Analysis Processing – 分析用 – 使用频率周期性 – 只读 2015/10/1 83 数据库设计文档 • 说清楚数据库各对象的语法和语义 – 包括表、约束等。 2015/10/1 84 数据库设计案例 • 网上书店 价值流 邮费 书 书 库存 书+邮费 物流服务 书 书 经销商 供应商 邮费 钱 现金 书 购买者 书费+邮费 钱 钱 书 钱 书费+邮费 书费+邮费 +汇费 手续费 金融 2015/10/1 85 作业 • 利用Access或SQL Server建立数据库。 • 建立本讲提到的账号、学生、课程三个表。 建立它们之间的关系。 • 尝试各种查询操作。 2015/10/1 86
© Copyright 2024 ExpyDoc
