第2讲数据资源管理 V3.0

管理信息系统第2讲数据资源管理 高宝俊博士副教授武汉大学经济与管理学院sinkgao 2020年4月 本讲内容 数据库系统文件系统方法的问题什么是数据库数据库管理系统关系数据模型查询与SQL语言关系模型的规范化关系数据库的设计 ER图Access应用 问题 本讲内容 数据库系统文件系统方法的问题什么是数据库数据库管理系统关系数据模型查询与SQL语言关系模型的规范化关系数据库的设计 ER图Access应用 文件系统方法 用文件管理数据 数据文件按应用程序的要求而建立程序和数具有一定的独立性 仍相互依赖各文件相互独立 分散保管 数据的一致性不易控制 文件系统是用户 用户应用程序与数据文件间的接口 不足 文件系统方法的不足 数据冗余大冗余是指在多个数据文件中重复出现相同的数据 数据一致性差客户数据 客户投诉数据数据与程序相互依赖数据文件和维护它的相关程序之间过分依赖 缺乏数据共享安全性低 本讲内容 数据库系统文件系统方法的问题什么是数据库数据库管理系统关系数据模型查询与SQL语言关系模型的规范化关系数据库的设计 ER图Access应用 什么是数据库 数据库 Database DB 数据库是一系列逻辑相关的数据的集合 通过对数据的集中管理来控制数据冗余 可以有效地支持多个应用程序 数据vs 现实事物 对现实事物的记录或描述不仅仅是数字 1 2 3 在数据库中 能被计算机接受和处理的数字 字符 图形 声音等统称为数据如何把现实事物转换为计算机中的数据 某一事物不同的侧面 可以用若干个数据项描述如 职工数据包括 姓名 性别 年龄 照片 事物之间有没有联系 如何描述 学生 课程 成绩 概念模型的术语 实体与属性 实体数据所代表的事件或事物 属性实体的特征 对实体的描述 实体集实体的集合 如 全厂职工 一个具体的实体vs 一种类型的实体 实体型 Q 实体集 实体 属性如何在数据库中表达呢 数据组织的层次 数据按照层次方式进行组织由位 字节 字段 记录 文件和数据库组成位 Bit 位是计算机中最小的数据单位 其值为0或1 是一个二进位 是计算机运算的基础 字节 Byte 字节是由若干位组成的 代表一个字符 字符是信息的最基本构架 1Byte 8bits数据传输的单位vs文件大小 数据项 记录 文件 数据项 Field 字段 描述数据处理对象的某一个特征 对应着实体的属性 数据类型 如数字 文本 日期 货币等记录 Record 相关的一些字段组成一条记录在数据库中一个实体就可以表示为一条记录 文件 File 同一类型的所有记录组成一个文件文件就是与某个特定主题相关的数据记录的集合 Acomputersystemorganizesdatainahierarchythatbeginswithbits andproceedstobytes fields records files anddatabase File Record Record Field Filed Field Field Byte Byte Byte Byte Bit Bit Bit Bit 数据层次 1 数据层次 2 关键字 关键字是能够唯一标识一条记录的一个或一组数据项 是识别记录和文件中查找记录的标志无重复 不能为空 本讲内容 数据库系统文件系统方法的问题什么是数据库数据库管理系统关系数据模型查询与SQL语言关系模型的规范化关系数据库的设计 ER图Access应用 Q 应用程序如何访问数据库中的数据 应用程序并不直接访问数据库而是通过数据库管理系统来访问数据库即数据库管理系统则担当着用户和数据库之间接口的角色 问题 数据库管理系统DataBaseManagementSystem 简称DBMS是一种操纵和管理数据库的软件 用于建立 使用和维护数据库 DBMS的例子微软的Access是个人电脑使用的DBMSIBM的DB2 Oracle的OracleDatabase和微软SQLServer是企业级的DBMSMYSQL是一种流行的开源DBMS 免费 DBvs DBMS 数据库管理系统功能 1 建立 使用和维护数据库Q 如何实现这些功能 数据库管理系统的组成数据定义语言数据操作语言数据字典 数据库管理系统功能 2 如何建立数据库 定义数据库中数据的结构 主要包括 定义一个新表 修改一个已经存在的表 定义表中的字段的属性 以及指定表和表之间的关系等 使用数据定义语言来创建数据库datadefinitionlanguage DDL 描述数据库内容和结构的正式语言数据定义的信息存储在哪里 数据字典 datadictionary 数据定义的信息存储在数据字典中它被形象地称为 数据的数据 数据字典既可以由系统自动生成也可以手工创建 数据字典 数据库管理系统功能 3 使用数据库 4个方面的操作插入 在表中增加一条新的记录修改 修改表中现有的某一条记录的一个或多个字段的值删除 删除表中现有的条记录数据查询使用数据操纵语言来实现datamanipulationlanguage DML操作数据库中的数据 提供了一组提取数据的命令最常用的数据操纵语言是结构化查询语言 SQL DBMS提供其它的数据操作工具例如视图 报表生成器 按例查询工具等 数据库管理系统功能 4 管理与维护数据库安全性 帐号密码等备份数据库DBMS的组成部分间的关系 数据库系统的优点 数据独立性强物理独立性 数据库的物理组织发生变化不影响数据的逻辑结构 逻辑独立性 数据库的逻辑结构改变时 无须改变应用程序 数据冗余度小数据的一致性好数据共享性高有数据安全和完整性保障ATM取款 逻辑视图和物理视图 DBMS把数据的逻辑视图和物理视图分开逻辑视图以最终用户所使用的直观的方式来显示数据物理视图则显示了数据的实际组织形式以及它在物理存储介质上的结构一个物理视图可支持多个逻辑视图DBMS使用专门的数据库管理软件 使得物理数据库可用于不同应用程序所需的不同的逻辑视图 本讲内容 数据库系统关系数据模型数据模型理解关系模型的表外键与完整性约束查询与SQL语言关系模型的规范化关系数据库的设计 ER图Access应用 Q 现实事物如何在数据库中表达 现实世界存在于人脑之外的客观世界概念模型有哪些数据处理对象 各对象有哪些特征 对象之间有什么关系 实体 属性 联系 逻辑模型概念模型转换成DBMS能够接受的形式 数据库 文件 记录 字段 物理模型 实体间的联系 联系 Relation 实体之间的相互关系一对一联系厂长 工厂 总统 国家一对多联系班级 学生 国家 省份多对多联系医生 病人 学生 课程 数据模型 Q 现实事物如何在数据库中表达 数据模型对客观事物 实体 以及它们之间的联系的数据组织 它描述了数据库中记录间关系的组织方式数据模型有三种层次模型 网状模型 关系模型 层次模型 层次模型的数据结构用树状结构来表示实体之间联系的模型叫层次模型层次数据库系统的典型代表是IBM公司的IMS系统特点只有一个结点无父结点除根结点之外 每个记录只有一个父结点 适合表示一对多 当表示M N关系时必然存在冗余总是从父记录指向子记录联系一般用指针实现每个记录只有一条查找路径 网状模型 网状模型的数据结构用有向图结构表示实体类型及实体间的联系网状数据模型的典型代表是DBTG系统特点可以有一个以上的结点无父结点至少有一个结点多于一个父结点联系由指针实现每条记录可以有多条查找路径 关系数据模型 用二维表格来描述实体 以及实体与实体间的联系关系 表的数学术语行 记录 实体列 字段 属性文件 表关系数据模型中的二维表格还有什么特点 关系数据模型如何表示实体之间的联系 本讲内容 数据库系统关系数据模型数据库模型理解关系模型的表外键与完整性约束查询与SQL语言关系模型的规范化关系数据库的设计 ER图Access应用 两种方案哪种好 优点 查询效率高 Q 从北京机床厂购进了哪些零部件 供应商表 零件供应表 零件表 从 小表 中查询数据 结果是什么 怎么表示 缺点 查询复杂 效率低 机械厂采购数据 供应商表 零件表 零件供应表 结果 优点 信息没有丢失 查询效率高 关系模型的三种基本操作 选择从关系表格中选取满足某种条件的记录 行 以建立新的关系连接通过公共字段 把两个关系中属性满足一定条件 如相等 的记录接合在一起形成新的关系投影从关系表格中选取指定的某些属性 列 大表 的缺点 数据冗余度大插入异常 如 要添加 钉子 因没有进货 无法插入删除异常 如 删除北京螺帽 将把该零件的基本信息删掉更新异常 如要更新螺栓规格 则要逐一更换 记录多麻烦 比较的结果 大表的优点查询效率高大表的缺点数据冗余度大插入异常删除异常更新异常结论数据库中的表应当设计成小表的形式 采用小表的方案之后 如何从 小表 中查询信息SQL语言 结构化查询语言如何把数据库设计成若干张 小表 的形式规范化理论E R模型 讨论 小表是孤立的么 Q1 各个表怎么联系起来 Q2 有什么限制么 外键 外键 ForeignKey 若有两个关系 表 A B Keys是A的主键 而B中也有Keys字段 则Keys就是表B的外键 又称作外关键字由此可见 外键表示了两个关系之间的联系 以另一个关系的外键作主关键字的表被称为主表 具有此外键的表被称为主表的从表表B 从表 的外键可以是其主键中的字段 n m 也可以是其它字段 1 n 1 1 关系模型 完整性约束 外键的作用 参照完整性建立了外键之后会产生一致性的制约 在外键上不允许出现主表中没有的的值 比如说 在前面一对多的例子中 不允许在学生表中出现主 班级 表中没有的班级号外键的设立可以保证数据的对应关系避免因为错误操作而遭到破坏 删除 修改会受到限制 保证数据的一致性 实体完整性若属性A是基本关系R的主键 则属性A不能取空值用户定义的完整性 数据的一致性好 本讲内容 数据库系统关系数据模型数据库模型理解关系模型的表外键与完整性约束查询与SQL语言关系模型的规范化关系数据库的设计 ER图Access应用 问题 关系模型的三种基本操作 选择从关系表格中选取满足某种条件的记录 行 以建立新的关系连接通过公共字段 把两个关系中属性满足一定条件 如相等 的记录接合在一起形成新的关系投影从关系表格中选取指定的某些属性 列 SQL语言 关系模型的操作 StructuredQueryLanguage 结构化查询语言功能 集数据操纵语言 DML 数据定义语言 DDL 数据控制语言 DCL 为一体两种使用方式 统一语法结构语言简洁 接近英语口语 高度非过程化 用户只要提示干什么 不需指使怎么干 查询 SELECT语句 1 基本形式SELECT FROM WHERE SELCET列出查询结果中想要看到的列 字段 FROM指出这些字段是从哪个 些 表面选取WHERE一个表内选择特定的行 记录 的条件或多个表中符合连接的条件 查询 SELECT语句 2 S noS nameS genderDepartmentAgePlace95001李勇男计算机20江苏95004张立男工商管理19北京95700杨晓冬男管理科学21山西SELECTS no S name FROMstudentsWHEREDepartment 计算机 UPDATEstudentsSETAge 20WHERES no 95004 INSERTINTOstudentsVALUES 95060 王英 女 物理 19 浙江 SQL语言操作示例 1 问题 SQL语言操作示例 2 SELECT供应商 代号 供应商 生产厂家 供应商 城市 零件数量 编号 零件数量 数量 零件 名称 零件 材质 零件 规格FROM供应商 零件数量 零件WHERE供应商 代号 S2AND供应商 代号 零件数量 代号AND零件数量 编号 零件 编号 本讲内容 数据库系统关系数据模型数据库模型理解关系模型的表外键与完整性约束查询与SQL语言关系模型的规范化关系数据库的设计 ER图Access应用 问题 外键 外键 ForeignKey 若有两个关系 表 A B Keys是A的主键 而B中也有Keys字段 则Keys就是表B的外键 又称作外关键字由此可见 外键表示了两个关系之间的联系 以另一个关系的外键作主关键字的表被称为主表 具有此外键的表被称为主表的从表表B 从表 的外键可以是其主键中的字段 n m 也可以是其它字段 1 n 1 1 关系模型 完整性约束 外键的作用 参照完整性建立了外键之后会产生一致性的制约 在外键上不允许出现主表中没有的的值 比如说 在前面一对多的例子中 不允许在学生表中出现主 班级 表中没有的班级号外键的设立可以保证数据的对应关系避免因为错误操作而遭到破坏 删除 修改会受到限制 保证数据的一致性 实体完整性若属性A是基本关系R的主键 则属性A不能取空值用户定义的完整性 数据的一致性好 关系模型的规范化理论 如何把大表分成若干小表关系模型的好坏对数据的存储 操作有很大影响 因此 一般要对得到的关系模式进行优化 规范化规范化理论Codd在1971年提出的研究关系模型中各属性之间的关系 探讨关系模型应具备的性质和设计方法 关系数据模型的范式结构 在关系模型的规范化理论中 规范化体系通过多层范式结构 NF 表示关系模型规范化的程度 第一范式 第一范式 每一个分量都是不可分的数据项如果在一个数据结构中所有数据元素都是基本的 不可分的数据项 就称该数据结构是规范的 任何满足规范化要求的数据结构都称为第一规范形式 记为1NF 简单表 平坦数据结构转化为第一范式的方法是消除重复项把所有非规范化的数据结构分解成若干个二维表形式的数据结构 并指定一个或若干个关键字 例 不规范转为第一范式 1 不符合规范约束条件 处理后符合规范约束条件 例 不规范转为第一范式 2 职工基本情况 职工简明表 关键字 职工号 例 不规范转为第一范式 3 第二范式 1 部分依赖假设ABC分别是同一个数据结构R中的三个元素或分别是R中若干个数据元素的集合 C依赖于AB的子集 则称C部分依赖于AB 否则 称为C完全依赖于AB 学号 课程号 成绩 学号 姓名 第二范式 2 第二范式如果一个数据结构满足第一范式 并且所有非关键字数据项完全函数依赖于它的整个关键字 则称该数据结构是第二范式的 记为2NF 不满足2NF意味着什么 不满足 存在部分依赖 关键字由多个数据项组成 这不满足2NF的前提转化为第二范式的方法去掉部分依赖关系 把它分解成若干个都是2NF的数据结构 第一范式转为第二范式 例1 1 主键为学号和课程号 而姓名等仅依赖于学号 与课程号无关 是部分依赖 第一范式转为第二范式 例1 2 将表进行分解为两个满足2NF的关系模式 转化为第二范式操作方法总结 去掉部分依赖关系 把它分解成若干个都是2NF的数据结构找出关键字判断非关键字段是否存在部分依赖如果存在 部分依赖的非关键字段及其所依赖的那部分关键字提取出来做为一个单独的表原表中除部分依赖的非关键字段之外的部分作为一个表 第一范式转为第二范式 例2 1 材料供应商库存的关系如下 材料编号材料名称规格供应商名称供应商地址价格库存量库存占用资金 步骤关键字判断依赖关系提取部分依赖作为一个表完全依赖作为一个表 第一范式转为第二范式 例2 2 材料供应商库存的关系如下 材料编号材料名称规格供应商名称供应商地址价格库存量库存占用资金 关键字 材料编号 供应商名称 材料名称 规格 供应商地址不完全依赖于关键字 不是2NF 第一范式转为第二范式 例2 3 分解成三个2NF的数据结构 材料编号 供应商名称 价格库存量库存占用资金 材料编号 材料名称规格 供应商名称 供应商地址 1 材料库存 2 材料库存 3 供应商 第三范式 第三范式如果一个数据结构满足第二范式 并且其中任何一个非关键字数据项都不传递依赖于它的关键字 则称该数据结构是第三范式的 记为3NF 传递依赖假设ABC分别是同一个数据结构R中的三个元素或分别是R中若干个数据元素的集合 如果C依赖B 而B依赖于A 那么C自然依赖于A 即称C传递依赖A 转化为第三范式的方法去掉传递依赖关系 就是3NF 第二范式转为第三范式 例1 1 系主任列通过系别传递依赖于主键学号 不满足3NF要求 第二范式转为第三范式 例1 2 处理后符合规范约束条件 转化为第三范式操作方法总结 去掉传递依赖关系 把它分解成若干个都是3NF的数据结构判断是否存在传递依赖非关键字段是否传递依赖于关键字C NotKey 依赖于B NotKey B NotKey 依赖于A Key 若存在 CB提取出来 做为一个单独的表 B为关键字原表中除C的之外的部分作为一个表 第二范式转为第三范式 例2 教师 教师代码 教师姓名 院系名称 院系 院系名称 院系负责人 第二范式转为第三范式 例3 因为价格与库存量 库存占用资金都是非关键字 但库存占用资金依赖于价格和库存量 所以非3NF 转为第三范式 去掉多余的 库存占用资金 而在程序中加以解决即可 材料编号 供应商名称 价格库存量 材料库存 材料编号 供应商名称 价格库存量库存占用资金 材料库存 转换 数据结构规范化设计的步骤 非规范化的数据 有重复的数据 1NF 没有重复的数据 2NF 所有的非关键字均完全依赖于整个关键字 3NF 任何一个非关键字数据项都不传递依赖于它的关键字 把所有非规范化的数据结构分解成若干个二维表形式的数据结构 并指定一个或若干个关键字 若关键字由不止一个元素组成 必须保证所有的非关键字数据元素依赖于整个关键字 否则去掉部分依赖关系 检查所有非关键字数据元素是否彼此独立 如果不是 去掉传递依赖关系 通过去除冗余的数据元素 构成都是3NF的数据结构 规范化小结 在关系数据库中 对关系模式最基本的要求是满足第一范式 这样的关系模式就是合法的 允许的 但是有些关系模式存在数据冗余 存储操作异常等不足 因此需要进一步改善关系模式 这就是规范化的目的关系模式的规范化过程是采用投影分解的方法来实现的 分解时应注意保证分解前和分解后的等价性规范化等级的选择要综合考虑多种因素 要在减少数据冗余 消除存储操作异常和响应时间之间之间做出合理折衷 一般而言规范化等级越高 检索时需要的联接操作就越多 DBMS的开销就越大 响应时间就越长实际开发数据库系统时 多采用3NF和BCNF 回顾理解 数据库系统的优点 数据独立性强数据冗余度小规范化的数据数据的一致性好数据共享性高有数据安全和完整性保障机制 从1NF到3NF逐步减小数据冗余 1 从1NF到3NF逐步减小数据冗余 2 答题方法 1 表中 课程成绩 是可以拆分的数据项 不满足1NF要求 转化为如下满足1NF的形式 学生成绩表 学号 姓名 年龄 系别 系主任 课程号 成绩 2 在学生成绩表中 姓名 年龄 系别 系主任只依赖于关键字中的学号 即存在部分依赖关系 不满足2NF要求 转化为如下满足2NF的形式 学生基本情况 学号 姓名 年龄 系别 系主任学生成绩表 学号 课程号 成绩 3 在学生基本情况表中 非关键字段 系主任 通过 系别 传递依赖于关键字 学号 不满足3NF要求 转化为如下满足3NF的形式 学生基本情况 学号 姓名 年龄 系别系所 系别 系主任学生成绩表 学号 课程号 成绩 学生选修样本报表 课堂练习 1 课堂练习 2 课堂练习 3 作业 1 某家电公司销售系统该公司在全国许多城市都有销售网点 每个城市有多个门店每一笔销售业务发生 登记 XX公司销售单 每个月底 统计全国各地区 各类产品的销售情况 输出 XX公司销售统计表 问题根据上述业务要求 没有出现的信息不必考虑 必要时可增加编号字段 设计符合3NF的数据存储 思考根据所学的关系模型的三种操作和SQL的相关知识 思考 XX公司销售统计表 的信息如何得到 写出思路或语句 作业 2 作业 3 数据库设计的步骤 对现实世界进行需求分析调查 数据 与 处理 得到使用者对数据库的要求建立信息世界中的概念模型在对现实世界需求分析的基础上 用 E R图 建立信息世界中的实体 实体的属性和实体之间联系的概念模型 从而转入信息世界 从E R图导出计算机世界的关系数据模型物理设计设计数据库在物理设备上的存储结构和存取方法的主要由数据库专业人员或DBA根据DBMS所提供的技术支持进行设计 实体联系图 实体联系模型 简称为E R模型 P S Chen于1976年提出 用于从现实世界中直接抽象出实体类型及实体间联系 然后用图形 E R图 表示E R图中的四个基本成分矩形 表示实体菱形 表示实体之间的联系椭圆形 表示实体 和联系 的属性直线 表示连接 联系与其涉及的实体之间以直线连接 并且在直线的端部标上联系的种类 1代表出现一次 M N等代表多次1 1 1 n m n 实体间的不同联系 E R图的基本符号 1 n 厂长 管理 工厂 仓库 存放 产品 E R图示例 1 学生 学习 课程 E R图示例 2 E R图示例 3 设计E R图的过程 一般原则建立E R图的过程是对数据进行归纳 分析 抽象的过程 首先把企业全部数据综合组织 然后根据不同用户对数据的使用需求进行分组 从局部或子系统入手 建立局部E R模型 再综合成总体E R模型步骤确定实体集确定实体间的联系和联系类型 1 1 1 n m n 把实体和联系组合成E R图确定实体和联系的属性确定实体的关键字 并做标记将E R图优化 学生选修样本报表 E R图设计例1 1 E R图设计例1 2 学号 姓名 专业名 专业号 学分 成绩 N M 1 学生 课程 专业 选修 分配 M 课程名 课程号 E R图设计例2 仓库零件材料企业有多个仓库 各仓库可存放零件 材料零件由材料制成 E R图设计例3 1 排课系统安排各门课程的授课教师和教学参考书 课程 讲授 教师 参考书 m n k 区别 三个实体型之间的多对多联系三个实体涉及同一个联系 而且参与该联系的每一个实体型都可以是多个的三个实体型两两之间的三个多对多联系具有三个意义各不同的联系 E R图设计例3 1 教务管理系统每学期开始时 教务部门给各个班级安排本学期的课程和授课教师 课程的教学参考书由授课教师指定 学期结束时 教师将学生的考试成绩交给教务部门登记保存 班级 组成 学生 学习 课程 讲授 教师 参考书 1 n n m p m E R图设计例3 2 n 成绩 E R图设计例3 3 E R模型转换为关系模式 1 基本策略所有的实体转换为关系根据不同的联系方式 将联系反映在关系中 或将联系转换为关系实体E R图中每个实体 都相应转换为一个关系实体名作为关系名实体的所有属性就是关系的属性实体的关键字就是关系关键字 E R模型转换为关系模式 2 1 n联系将 1 方的主键纳入 n 方实体对应的关系中 同时将联系的属性也一并纳入 n 方关系中 学生 属于 班级 学号 姓名 籍贯 班级号 班级名 1 n 学生 学号 姓名 籍贯 班级号 班级 班级号 班级名 外键 1 n联系转换例子 1 外键 1 n联系转换例子 2 数量 仓库 仓库号 地点 面积 产品 货号 品名 价格 仓库号 数量 联系的属性 外键 外键 ForeignKey 若有两个关系 表 A B Keys是A的主键 而B中也有Keys字段 则Keys就是表B的外键 又称作外关键字由此可见 外键表示了两个关系之间的联系 以另一个关系的外键作主关键字的表被称为主表 具有此外键的表被称为主表的从表表B 从表 的外键可以是其主键中的字段 n m 也可以是其它字段 1 n 1 1 关系模型 完整性约束 外键的作用 参照完整性建立了外键之后会产生一致性的制约 在外键上不允许出现主表中没有的的值 比如说 在前面一对多的例子中 不允许在学生表中出现主 班级 表中没有的班级号外键的设立可以保证数据的对应关系避免因为错误操作而遭到破坏 删除 修改会受到限制 保证数据的一致性 实体完整性若属性A是基本关系R的主键 则属性A不能取空值用户定义的完整性 数据的一致性好 E R模型转换为关系模式 3 m n联系一个m n联系单独用一个二维表来表示