数据库设计-网上书店.ppt

数据库设计 网上书店 一 需求分析二 系统功能与流程设计三 数据库概念结构设计四 数据库逻辑结构设计五 数据库物理结构设计六 提高数据库效率的设计七 数据库的实施 一 需求分析 1 能通过互联网 Internet 访问网上书店B S结构 选择合适的开发工具2 能在网页中浏览图书目录 可按图书类别分类浏览 1 图书应当分类 按何种标准分类 分为几类 是否还有其他分类标准 又如何分类 2 哪些信息构成图书目录 3 是否需要图书搜索功能 能按哪些关键词搜索 图书名称 图书号 出版社 单价 一 需求分析 3 可浏览所选图书的详细信息 具有包括哪些信息 ISBN 书名 作者 出版社 出版日期 简介 封面 库存数量 单价4 浏览图书时可订购图书 生成并提交订单 1 在浏览的哪些环节可以订购 浏览图书目录 详细信息时 2 一个 订单 应包含哪些信息 订单号 书号 顾客号 数量 金额 订购日期订单管理 创建 增加 修改 删除 确认 结算 一 需求分析 5 根据订单和支付信息发出图书 形成发货信息 支付信息 暂不考虑 发货信息 应当包括哪些信息 是否需要独立的 发货单 发货信息 如何与 订单信息 联动 一 需求分析 6 顾客收到图书之后 对收货予以确认 形成收货信息 收货信息 应当包括哪些信息 是否需要独立的 收货单 收货信息 如何与 发货信息 联动 一 需求分析 7 顾客必须注册后才能订购图书 1 顾客登录用的信息注册账号 密码 2 顾客自身的基本信息姓名 性别 年龄 地址 邮编 电话 邮箱8 应当提供管理员对图书 顾客 订单 发货 收货等信息进行定期维护 管理员 信息 管理员ID 姓名 类型 管理员 分类 系统预设的管理员 自定义的管理员 二 系统功能与流程设计 1 系统功能 网上书店 前台浏览模块 后台管理模块 匿名用户 注册用户 浏览 查找图书 用户注册 浏览 查找图书 订购图书 维护订单 支付货款 用户资料维护 管理员 图书管理 用户管理 订单管理 思考 初步设计网站首页布局 二 系统功能与流程设计 2 操作流程 总体流程 网上书店首页 网上书店概况 浏览 搜索图书 订购图书 用户登录 用户注册 导航 返回首页 二 系统功能与流程设计 3 操作流程 购书流程 网上书店首页 浏览图书 订购图书 用户管理 登录 收货确认 支付 发货 三 数据库概念结构设计 E R图 主要E R图 管理员 图书 客户 管理 管理 订购 账号 密码 书号 书名 作者 单价 数量 日期 账号 密码 数量 姓名 地址 电话 1 n n 1 n m 类别 姓名 四 数据库逻辑结构设计 关系 E R图转化为关系模式的方法注 关系模式 是指一个未确定各个属性的数据类型 长度等参数的二维表 称为一个 关系 例 管理员 账号 姓名 密码 1 每一个实体对应一个关系 2 一对多的联系当 联系 并无独有的 属性 时 通常不单独转换为一个关系模式 而是将 一方 的主码增加到 多方 关系模式中 构成其外码 例如 客户 账号 密码 姓名 管理员ID 四 数据库逻辑结构设计 关系 3 多对多的联系 转换为一个关系模式关系的属性 n方的主键 m方的主键 联系 的属性关系的主键 组合 n方的主键 m方的主键 例如 订单 客户号 书号 订购数量 订购日期 4 三个以上的多元联系 转换为一个关系模式类似于多对多联系的转换方法例如 供应 供应商号 项目号 零件号 数量 五 数据库物理结构设计 存储结构 包括 一 物理结构是指含有字段类型 字段长度等参数的二维表结构 包括主键 外键 二 字段约束主键 primarykey 外键 foreignkey 约束空 非空约束 null notnull 默认值 default 约束唯一 unique 约束自动增长 identify 约束检查 CHECK 约束 五 数据库物理结构设计 存储结构 一 表结构定义1 管理员表 admins 2 顾客表 customers 3 图书表 books 4 图书分类表 booktypes 5 订单表 orders 写出各个关系表的结构定义 五 数据库物理结构设计 存储结构 二 表的约束1 SQLServer约束的分类 1 实体完整性约束主要通过 主键约束 来实现 以确保表内任意行记录数据是可区分的 是不相同的 主键约束 PrimaryKey 不重复 不为空 2 引用完整性约束主要是通过 外键约束 来实现 即将一个表的主键添加到另一个表中 称为外键 ForeignKey 使两个表联合操作时能准确识别表内各条记录 外键 ForeignKey 五 数据库物理结构设计 存储结构 2 域完整性约束 给定列的输入正确性和有效性 A 唯一性约束 UNIQUE 要求某列任意两行的值不能相同B 标识约束 IDENTIFY 能自动产生唯一的标识值 一般用于主键 C 非空约束 NotNullD 默认值约束 Default 为字段规定默认值E 检查约束 CHECK 为字段的值规定检查机制 如范围F 触发器 存储过程中定义的约束机制说明 除触发器 存储过程外 有些约束在定义字段时给出 当某个字段具有多个约束 可紧跟其后 排列不分次序 有些也可在表结构定义的末尾单独给出约束定义 五 数据库物理结构设计 存储结构 2 常用约束的书写方法 1 主键约束用法1 独立书写constraintprimarykey 字段 用法2 紧跟字段定义语句字段名类型constraintprimarykey 2 外键约束用法1 独立书写constraintforeignkey 字段 references表名 字段名 用法2 类似于primarykey 五 数据库物理结构设计 存储结构 3 唯一性约束 Unique 置于字段之后 用法 Unique主键约束自动拥有唯一性 一个表只能有一个主键约束 但可以有多个唯一性约束 4 空或非空约束置于字段定义之后 用法 Null NotNull 5 唯一标识值约束 Identify 置于字段定义之后 用法 Identify 初值 步长 6 默认值约束 Default 置于字段定义之后 用法 Default 常量 函数例如 bqintnulldefault 1 字段bq 购买数量orderdatedatedefaultGetDate 五 数据库物理结构设计 存储结构 7 检查约束 check 用法 check 表达式 例 ID Pintnotnullcheck ID P 0 ageintcheck age 10andage 20 六 提高数据库效率的设计 包括 一 表的索引 二 表的视图 三 存储过程 四 触发器 六 提高数据库效率的设计 一 表的索引1 索引的分类 1 唯一索引以原表为基础 按给定字段创建任意两行值不重复的索引 说明 创建了唯一约束 将自动创建唯一索引 尽管唯一索引有助于提高查找信息的速度 但为了获得最佳性能 建议使用主键约束或唯一约束 2 主键索引为表定义一个主键将自动创建主键索引 主键索引是唯一索引的特殊类型 六 提高数据库效率的设计 3 聚集索引在聚集索引中 表中各行的物理顺序与键值的逻辑 索引 顺序相同 表只能包含一个聚集索引 例如 汉语字 词 典默认按拼音排序编排字典中的每页页码 拼音字母a b c d x y z就是索引的逻辑顺序 而页码1 2 3 就是物理顺序 默认按拼音排序的字典 其索引顺序和逻辑顺序是一致的 六 提高数据库效率的设计 4 非聚集索引对非聚集索引 表中各行的物理顺序与键值的逻辑顺序不匹配 聚集索引比非聚集索引 nonclusteredindex 有更快的数据访问速度 例如 按笔画排序的索引就是非聚集索引 1 画的字 词 对应的页码可能比 3 画的字 词 对应的页码大 靠后 说明 SQLServer中 一个表只能创建1个聚集索引 多个非聚集索引 设置某列为主键 该列就默认为聚集索引 六 提高数据库效率的设计 2 索引的创建方法CREATE UNIQUE CLUSTERED NONCLUSTERED INDEX索引名ON表名 字段名 WITHFILLFACTOR n 说明 UNIQUE表示唯一索引 可选CLUSTERED NONCLUSTERED表示聚集索引还是非聚集索引 可选FILLFACTOR表示填充因子 指定一个0到100之间的值 该值指示索引页填满的空间所占的百分比 六 提高数据库效率的设计 3 使用索引的几条原则 1 可以创建索引的情形该列用于频繁搜索该列用于对数据进行排序 2 不要创建索引的情形列中仅包含几个不同的值 表中仅包含几行 为小型表创建索引可能不太划算 因为SQLServer在索引中搜索数据所花的时间比在表中逐行搜索所花的时间更长 六 提高数据库效率的设计 二 视图的使用1 什么是视图 视图是一种数据库对象 为用户提供了一种查询数据表中数据的方式 视图的定义是由select语句构成 并存储在数据库中 引用视图实质上是引用select语句的查询结果 视图是虚拟表 具有数据表的一些特性 可以对视图进行查询 修改 删除等操作 还能成为另一个视图所引用的表 对视图的数据进行修改时 基表的数据也会发生变化 反之亦然 六 提高数据库效率的设计 2 使用视图的优点 1 简化查询语句可以把经常使用的联接 投影和查询语句定义为视图 当查询时 只需简单地查询视图而隐藏对基表的复杂查询操作 2 增加可读性视图可以定制数据内容 可以使用字段别名 3 保证数据逻辑独立性当数据表结构改变时 只需更改视图定义的查询语句 不需更改应用程序代码 保证了数据的逻辑独立性 4 增加数据的安全性和保密性针对不同用户可以创建不同视图 真正的数据表是不可见 不可访问的 六 提高数据库效率的设计 3 视图的创建CreateView 数据库名 视图名 列名1 列名2 WITHENCRYPTION ASSELECT语句 WITHCHECKOPTION 参数说明 列名1 列名2 是视图的列名 若省略则取数据表的列名 withencryption 加密视图 withcheckoption 强制对视图执行的所有数据修改语句都必须符合条件 六 提高数据库效率的设计 例 创建网上图书的简要信息视图简要信息 书名 作者 ISBN 出版社 出版日期创建视图CreateviewbSimpInfo 书名 作者 ISBN 出版社 出版日期 ASselectbName bAuthor bISBN bPub bDatefrombooks引用视图在视图bSimpInfo中查询书名含有 数据库 的图书select frombSimpInfowhere书名like 数据库 六 提高数据库效率的设计 4 视图的管理 1 修改视图定义 Alterview 2 更改视图名 利用SQLServer提供的系统存储过程Execsp rename 3 删除视图Dropview 4 对视图插入 更新 删除记录数据 insert update delete 基表数据会立即改变 仍然要满足基表对数据的操作要求 六 提高数据库效率的设计 三 存储过程1 存储过程的概念和分类存储过程是由一系列对数据库进行复杂操作的SQL语句 流程控制语句和函数组成的 经过编译后 可以象系统类函数一样作为一个独立的数据库对象进行管理 提供给应用程序调用 存储过程具有参数传递 判断和声明变量 返回信息 扩充标准SQL语言 可以嵌套调用等特点 存储过程可以分为系统存储过程和用户自定义存储过程系统存储过程 由系统提供 可直接使用用户自定义存储过程 由用户编写 需先定义后使用 六 提高数据库效率的设计 2 使用存储过程的优缺点优点 执行速度快 创建时经过了语法检查和编译 有利于模块化程序设计便于应用程序的维护管理减少网络通信量 在SQLServer服务器范围内执行 保证系统的安全性缺点不能实现复杂的逻辑操作 SQL语言所限制 并不能实现全部或某些特殊的功能不宜太多 不然难于记忆和使用 六 提高数据库效率的设计 3 创建存储过程Createprocedure 形参变量数据类型 默认值 output withrecompile encryption ASselect语句 过程名 最多128个字符 必须唯一 形参变量 必须以 开头 最多1024个参量 默认值 只能是常量或空值 不能是表名 列名等 output 规定该变量是用以将执行结果返回的Recomplie 重编译 encryption 加密 六 提高数据库效率的设计 例 创建存储过程pr sb 使其按书名查找该书的作者 出版社 出版日期 Createprocedurepr sb bnmvarchar 50 bauvarchar 20 output bpbvarchar 50 output bdtdateoutput ASselect bau bName bpb bPub bdt bDatefrombookswherebName bnm 六 提高数据库效率的设计 4 存储过程的执行EXECUTE例 Usebooksexecutepr sb 数据库技术 说明 执行存储过程前应当先打开某个数据表 六 提高数据库效率的设计 5 存储过程的管理 1 修改存储过程 alterprocedure 用法同创建 2 存储过程重命名 executesp rename 3 删除存储过程 dropprocedure 六 提高数据库效率的设计 四 触发器1 触发器的概念与特点它是一段能自动执行的程序 是特殊的存储过程 其特殊性在于 不允许使用参数 没有返回值 不允许用户调用 当对表进行插入 删除 修改等操作时由系统自动调用并执行 六 提高数据库效率的设计 2 触发器的主要用途 实现更复杂的完整性约束 1 实现数据库中多个表的级联修改当修改删除某个表的数据时 其他表的相应数据能自动修改外键约束也具有这种作用 且效率更高 2 扩展约束 默认对象和规则对象的完整性检查例如 check约束 在限制数据输入时不能参照其他表中的数据 如在计算 销售金额 数量 单价 时 数量 不应超过 库存数量 此时用check约束不能实现这种约束 3 自动生成数据 4 自定义复杂的安全权限 六 提高数据库效率的设计 3 触发器的触发方式为数据表的某个字段设置触发器后 当该字段的数据被insert 插入 delete 删除 update 修改 时 触发器被激活并执行 按激活的时机分为 后触发 和 替代触发 两种方式 六 提高数据库效率的设计 1 后触发若引发触发器执行的语句通过了各种约束检查 成功执行后才激活并执行触发器程序 称为后触发 特点 若引发触发器执行的语句违反了某种约束 该语句不会执行 则后触发方式的触发器也不被激活 后触发方式只能创建在数据表上 不能创建在视图上 一个表可以有多个后触发器 六 提高数据库效率的设计 2 替代触发若激活触发器的语句仅仅起到激活触发器的作用 一旦激活触发器后该语句即停止执行 立即转去执行触发器的程序 激活触发器的语句并不被执行 相当于禁止某种操作 这种方式称为替代触发 特点 可以创建在表上 也可以创建在视图上 一个表只能有一个替代触发器 六 提高数据库效率的设计 4 SQLServer系统对触发器的管理 1 触发器作为一种数据库对象 在syscomment系统表中存储有其完整的定义信息 在sysobject系统表中有该对象的记录 2 触发器被激活时会创建两个临时表 inserted表和deleted表 以确保对数据的安全操作 两个表的结构与激活触发器的原数据表相同 用insert语句插入记录激活触发器时 系统在原表插入记录的同时 也会自动把记录插入到inserted表中 用delete语句删除记录激活触发器时 系统在原表删除记录的同时 会把删除的记录添加到deleted表中 用update语句修改记录激活触发器时 系统先在原表删除原有记录 删除的记录被添加到deleted表中 再在原表插入新的记录 并同时插入到inserted表 用户可以用select语句查询这两个表 但不允许修改 触发器执行完后 这两个表将自动删除 六 提高数据库效率的设计 5 创建触发器Createtrigger owner 触发器名on owner 表名 视图名 for after insteadof insert update delete withencryption norforreplication AS setnocount 不返回给变量赋值的结果SQL语句系列 rollbacktransaction 事务回滚 六 提高数据库效率的设计 说明 1 for after insteadof 用于指定触发方式For与after相同 是为了与以前版本兼容 其后可以指定 insert update delete 命令中的一个或多个 2 norforreplication 在复制过程中不激活 3 setnocount 触发器一般不能有返回值 所以不应有sel