《数据库设计-网上书店.ppt》由会员分享，可在线阅读，更多相关《数据库设计-网上书店.ppt（48页珍藏版）》请在三一办公上搜索。
1、数据库设计网上书店,一、需求分析二、系统功能与流程设计三、数据库概念结构设计四、数据库逻辑结构设计五、数据库物理结构设计六、提高数据库效率的设计七、数据库的实施,一、需求分析,1.能通过互联网(Internet)访问网上书店B/S结构 选择合适的开发工具2.能在网页中浏览图书目录，可按图书类别分类浏览。(1)图书应当分类，按何种标准分类?分为几类?是否还有其他分类标准?又如何分类?(2)哪些信息构成图书目录?(3)是否需要图书搜索功能?能按哪些关键词搜索?图书名称、图书号、出版社、单价,一、需求分析,3.可浏览所选图书的详细信息。具有包括哪些信息?ISBN，书名，作者，出版社，出版日期，简介，
2、封面，库存数量，单价4.浏览图书时可订购图书，生成并提交订单(1)在浏览的哪些环节可以订购?浏览图书目录、详细信息时(2)一个“订单”应包含哪些信息?订单号，书号，顾客号，数量，金额，订购日期订单管理：创建、增加、修改、删除、确认、结算,一、需求分析,5.根据订单和支付信息发出图书，形成发货信息（“支付信息”暂不考虑）“发货信息”应当包括哪些信息?是否需要独立的“发货单”?“发货信息”如何与“订单信息”联动?,一、需求分析,6.顾客收到图书之后，对收货予以确认，形成收货信息“收货信息”应当包括哪些信息?是否需要独立的“收货单”?“收货信息”如何与“发货信息”联动?,一、需求分析,7.顾客必须注
3、册后才能订购图书(1)顾客登录用的信息注册账号，密码(2)顾客自身的基本信息姓名，性别，年龄，地址，邮编，电话，邮箱8.应当提供管理员对图书、顾客、订单、发货、收货等信息进行定期维护。“管理员”信息：管理员ID、姓名、类型“管理员”分类：系统预设的管理员、自定义的管理员,二、系统功能与流程设计,1.系统功能,网上书店,前台浏览模块,后台管理模块,匿名用户,注册用户,浏览/查找图书,用户注册,浏览/查找图书,订购图书/维护订单,支付货款,用户资料维护,(管理员),图书管理,用户管理,订单管理,思考：初步设计网站首页布局?,二、系统功能与流程设计,2.操作流程总体流程,网上书店首页,网上书店概况,
4、浏览/搜索图书,订购图书,用户登录,用户注册,导航,返回首页,二、系统功能与流程设计,3.操作流程购书流程,网上书店首页,浏览图书,订购图书,用户管理,登录,收货确认,支付,发货,三、数据库概念结构设计(E-R图),主要E-R图,管理员,图书,客户,管理,管理,订购,账号,密码,书号,书名,作者,单价,数量,日期,账号,密码,数量,姓名,地址,电话,1,n,n,1,n,m,类别,姓名,四、数据库逻辑结构设计(关系),E-R图转化为关系模式的方法注：“关系模式”是指一个未确定各个属性的数据类型、长度等参数的二维表，称为一个“关系”。例：管理员(账号，姓名，密码)(1)每一个实体对应一个关系(2)
5、一对多的联系当“联系”并无独有的“属性”时，通常不单独转换为一个关系模式，而是将“一方”的主码增加到“多方”关系模式中，构成其外码。例如：客户(账号，密码，姓名，管理员ID),四、数据库逻辑结构设计(关系),(3)多对多的联系：转换为一个关系模式关系的属性：n方的主键、m方的主键、“联系”的属性关系的主键：组合(n方的主键、m方的主键)例如：订单(客户号，书号，订购数量，订购日期)(4)三个以上的多元联系：转换为一个关系模式类似于多对多联系的转换方法例如：,供应(供应商号,项目号,零件号,数量),五、数据库物理结构设计(存储结构),包括：(一)物理结构是指含有字段类型、字段长度等参数的二维表结
6、构(包括主键、外键)(二)字段约束主键(primary key)、外键(foreign key)约束空/非空约束(null,not null)默认值(default)约束唯一(unique)约束自动增长(identify)约束检查(CHECK)约束,五、数据库物理结构设计(存储结构),(一)表结构定义1.管理员表(admins)2.顾客表(customers)3.图书表(books)4.图书分类表(booktypes)5.订单表(orders),写出各个关系表的结构定义,五、数据库物理结构设计(存储结构),(二)表的约束1.SQL Server约束的分类(1)实体完整性约束主要通过“主键约束”
7、来实现，以确保表内任意行记录数据是可区分的，是不相同的。主键约束(Primary Key):不重复、不为空(2)引用完整性约束主要是通过“外键约束”来实现，即将一个表的主键添加到另一个表中，称为外键(Foreign Key)，使两个表联合操作时能准确识别表内各条记录。外键(Foreign Key):,五、数据库物理结构设计(存储结构),(2)域完整性约束:给定列的输入正确性和有效性。A.唯一性约束(UNIQUE):要求某列任意两行的值不能相同B.标识约束(IDENTIFY):能自动产生唯一的标识值，一般用于主键。C.非空约束：Not NullD.默认值约束:Default，为字段规定默认值E.
8、检查约束：CHECK，为字段的值规定检查机制，如范围F.触发器、存储过程中定义的约束机制说明：除触发器、存储过程外，有些约束在定义字段时给出，当某个字段具有多个约束，可紧跟其后，排列不分次序。有些也可在表结构定义的末尾单独给出约束定义。,五、数据库物理结构设计(存储结构),2.常用约束的书写方法(1)主键约束用法1：独立书写constraint primary key(字段)用法2：紧跟字段定义语句字段名 类型 constraint primary key(2)外键约束用法1：独立书写constraint foreign key(字段)references 表名(字段名)用法2：类似于prim
9、ary key.,五、数据库物理结构设计(存储结构),(3)唯一性约束(Unique)置于字段之后，用法：Unique主键约束自动拥有唯一性；一个表只能有一个主键约束，但可以有多个唯一性约束。(4)空或非空约束置于字段定义之后，用法：Null，Not Null(5)唯一标识值约束(Identify)置于字段定义之后，用法：Identify(初值,步长)(6)默认值约束(Default)置于字段定义之后，用法：Default 常量/函数例如：bq int null default 1-字段bq:购买数量 orderdate date default GetDate(),五、数据库物理结构设计(存
10、储结构),(7)检查约束(check)用法:check(表达式)例：ID_P int not null check(ID_P0)age int check(age=10 and age=20),六、提高数据库效率的设计,包括：(一)表的索引(二)表的视图(三)存储过程(四)触发器,六、提高数据库效率的设计,(一)表的索引1.索引的分类(1)唯一索引以原表为基础，按给定字段创建任意两行值不重复的索引。说明：创建了唯一约束，将自动创建唯一索引。尽管唯一索引有助于提高查找信息的速度，但为了获得最佳性能，建议使用主键约束或唯一约束。(2)主键索引为表定义一个主键将自动创建主键索引，主键索引是唯一索引的
11、特殊类型。,六、提高数据库效率的设计,(3)聚集索引在聚集索引中，表中各行的物理顺序与键值的逻辑（索引）顺序相同。表只能包含一个聚集索引。例如：汉语字（词）典默认按拼音排序编排字典中的每页页码。拼音字母a，b，c，dx，y，z就是索引的逻辑顺序，而页码1，2，3就是物理顺序。默认按拼音排序的字典，其索引顺序和逻辑顺序是一致的。,六、提高数据库效率的设计,(4)非聚集索引对非聚集索引，表中各行的物理顺序与键值的逻辑顺序不匹配。聚集索引比非聚集索引（nonclustered index）有更快的数据访问速度。例如，按笔画排序的索引就是非聚集索引，“1”画的字（词）对应的页码可能比“3”画的字（词）
12、对应的页码大（靠后）。说明：SQL Server中，一个表只能创建1个聚集索引，多个非聚集索引。设置某列为主键，该列就默认为聚集索引。,六、提高数据库效率的设计,2.索引的创建方法CREATE UNIQUE CLUSTERED|NONCLUSTEREDINDEX 索引名 ON 表名(字段名)WITH FILLFACTOR=n说明：UNIQUE表示唯一索引，可选CLUSTERED、NONCLUSTERED表示聚集索引还是非聚集索引，可选FILLFACTOR表示填充因子，指定一个0到100之间的值，该值指示索引页填满的空间所占的百分比,六、提高数据库效率的设计,3.使用索引的几条原则(1)可以创建
13、索引的情形该列用于频繁搜索该列用于对数据进行排序(2)不要创建索引的情形列中仅包含几个不同的值。表中仅包含几行。为小型表创建索引可能不太划算，因为SQL Server在索引中搜索数据所花的时间比在表中逐行搜索所花的时间更长,六、提高数据库效率的设计,(二)视图的使用1.什么是视图？视图是一种数据库对象，为用户提供了一种查询数据表中数据的方式。视图的定义是由select语句构成，并存储在数据库中。引用视图实质上是引用select语句的查询结果。视图是虚拟表，具有数据表的一些特性，可以对视图进行查询、修改、删除等操作，还能成为另一个视图所引用的表。对视图的数据进行修改时，基表的数据也会发生变化，反
14、之亦然。,六、提高数据库效率的设计,2.使用视图的优点(1)简化查询语句可以把经常使用的联接、投影和查询语句定义为视图，当查询时，只需简单地查询视图而隐藏对基表的复杂查询操作。(2)增加可读性视图可以定制数据内容、可以使用字段别名。(3)保证数据逻辑独立性当数据表结构改变时，只需更改视图定义的查询语句，不需更改应用程序代码，保证了数据的逻辑独立性。(4)增加数据的安全性和保密性针对不同用户可以创建不同视图，真正的数据表是不可见、不可访问的。,六、提高数据库效率的设计,3.视图的创建Create View 数据库名.视图名(列名1,列名2,)WITH ENCRYPTION AS SELECT语句
15、 WITH CHECK OPTION 参数说明“列名1,列名2,”:是视图的列名，若省略则取数据表的列名“with encryption”:加密视图“with check option”：强制对视图执行的所有数据修改语句都必须符合条件。,六、提高数据库效率的设计,例：创建网上图书的简要信息视图简要信息：书名、作者、ISBN、出版社、出版日期创建视图Create view bSimpInfo(书名,作者,ISBN,出版社,出版日期)AS select bName,bAuthor,bISBN,bPub,bDate from books引用视图在视图bSimpInfo中查询书名含有“数据库”的图书s
16、elect*from bSimpInfo where 书名 like%数据库%,六、提高数据库效率的设计,4.视图的管理(1)修改视图定义:Alter view(2)更改视图名:利用SQL Server提供的系统存储过程Exec sp_rename,(3)删除视图Drop view(4)对视图插入、更新、删除记录数据(insert,update,delete)基表数据会立即改变，仍然要满足基表对数据的操作要求.,六、提高数据库效率的设计,(三)存储过程1.存储过程的概念和分类存储过程是由一系列对数据库进行复杂操作的SQL语句、流程控制语句和函数组成的。经过编译后，可以象系统类函数一样作为一个独
17、立的数据库对象进行管理，提供给应用程序调用。存储过程具有参数传递、判断和声明变量、返回信息、扩充标准SQL语言、可以嵌套调用等特点。存储过程可以分为系统存储过程和用户自定义存储过程系统存储过程：由系统提供，可直接使用用户自定义存储过程：由用户编写，需先定义后使用,六、提高数据库效率的设计,2.使用存储过程的优缺点优点：执行速度快(创建时经过了语法检查和编译)有利于模块化程序设计便于应用程序的维护管理减少网络通信量(在SQL Server服务器范围内执行)保证系统的安全性缺点不能实现复杂的逻辑操作(SQL语言所限制)并不能实现全部或某些特殊的功能不宜太多，不然难于记忆和使用。,六、提高数据库效率
18、的设计,3.创建存储过程Create procedure 形参变量 数据类型=默认值 output with recompile|encryptionAS select语句“过程名”：最多128个字符，必须唯一“形参变量”：必须以开头，最多1024个参量“默认值”：只能是常量或空值，不能是表名/列名等“output”：规定该变量是用以将执行结果返回的Recomplie:重编译,encryption:加密,六、提高数据库效率的设计,例：创建存储过程pr_sb，使其按书名查找该书的作者,出版社,出版日期。Create procedure pr_sb(bnm varchar(50),bau varc
19、har(20)output,bpb varchar(50)output,bdt date output)AS select bau=bName,bpb=bPub,bdt=bDate from books where bName=bnm,六、提高数据库效率的设计,4.存储过程的执行EXECUTE 例：Use booksexecute pr_sb 数据库技术说明：执行存储过程前应当先打开某个数据表,六、提高数据库效率的设计,5.存储过程的管理(1)修改存储过程：alter procedure，用法同创建(2)存储过程重命名：execute sp_rename,(3)删除存储过程：drop proc
20、edure,六、提高数据库效率的设计,(四)触发器1.触发器的概念与特点它是一段能自动执行的程序，是特殊的存储过程。其特殊性在于：不允许使用参数，没有返回值。不允许用户调用，当对表进行插入、删除、修改等操作时由系统自动调用并执行。,六、提高数据库效率的设计,2.触发器的主要用途：实现更复杂的完整性约束(1)实现数据库中多个表的级联修改当修改删除某个表的数据时，其他表的相应数据能自动修改外键约束也具有这种作用，且效率更高。(2)扩展约束、默认对象和规则对象的完整性检查例如“check约束”在限制数据输入时不能参照其他表中的数据，如在计算“销售金额=数量*单价”时，“数量”不应超过“库存数量”，此
21、时用check约束不能实现这种约束。(3)自动生成数据(4)自定义复杂的安全权限。,六、提高数据库效率的设计,3.触发器的触发方式为数据表的某个字段设置触发器后，当该字段的数据被insert(插入),delete(删除),update(修改)时，触发器被激活并执行。按激活的时机分为“后触发”和“替代触发”两种方式。,六、提高数据库效率的设计,(1)后触发若引发触发器执行的语句通过了各种约束检查，成功执行后才激活并执行触发器程序，称为后触发。特点：若引发触发器执行的语句违反了某种约束，该语句不会执行，则后触发方式的触发器也不被激活。后触发方式只能创建在数据表上，不能创建在视图上。一个表可以有多个
22、后触发器。,六、提高数据库效率的设计,(2)替代触发若激活触发器的语句仅仅起到激活触发器的作用，一旦激活触发器后该语句即停止执行，立即转去执行触发器的程序，激活触发器的语句并不被执行，相当于禁止某种操作，这种方式称为替代触发。特点：可以创建在表上，也可以创建在视图上。一个表只能有一个替代触发器。,六、提高数据库效率的设计,4.SQL Server系统对触发器的管理(1)触发器作为一种数据库对象，在syscomment系统表中存储有其完整的定义信息，在sysobject系统表中有该对象的记录。(2)触发器被激活时会创建两个临时表：inserted表和deleted表，以确保对数据的安全操作。两个
23、表的结构与激活触发器的原数据表相同。用insert语句插入记录激活触发器时，系统在原表插入记录的同时，也会自动把记录插入到inserted表中。用delete语句删除记录激活触发器时，系统在原表删除记录的同时，会把删除的记录添加到deleted表中。用update语句修改记录激活触发器时，系统先在原表删除原有记录，删除的记录被添加到deleted表中，再在原表插入新的记录，并同时插入到inserted表。用户可以用select语句查询这两个表，但不允许修改。触发器执行完后，这两个表将自动删除。,六、提高数据库效率的设计,5.创建触发器Create trigger owner.触发器名on ow
24、ner.表名/视图名 for/after/instead of insert,update,delete with encryption nor for replicationAS set nocount-不返回给变量赋值的结果 SQL语句系列 rollback transaction-事务回滚,六、提高数据库效率的设计,说明(1)for/after/instead of:用于指定触发方式For与after相同，是为了与以前版本兼容。其后可以指定“insert,update,delete”命令中的一个或多个(2)nor for replication:在复制过程中不激活(3)set nocou
25、nt:触发器一般不能有返回值，所以不应有select语句进行查询或给变量赋值(获取被操作数据的语句除外)如果必须使用变量赋值语句，应使用此选项来避免返回结果,六、提高数据库效率的设计,说明(4)SQL语句系列即触发器被激活后要执行的SQL代码，其中可以包含获得被操作数据的select语句。对后触发方式，被操作数据一定在两个临时表中。如果被操作数据是多值的，可用in判断是否包含其中被操作数据 in(select 被操作字段 from 临时表)如果被操作数据是单值的，可用以下语句获得Select 变量=被操作字段 from 临时表,六、提高数据库效率的设计,说明(5)rollback transa
26、ction：事务回滚语句对后触发方式，语句执行完毕才执行触发器，如果发现操作不符合规则，可用该语句取消操作。,六、提高数据库效率的设计,几点注意：(1)触发器不能创建在临时表或系统表上，后触发不能创建在视图上。(2)一个触发器只能创建在一个表上，一个表可以有一个替代触发和多个后触发(可以是同一种操作类型，可以同时触发)。(3)由于truncate table(删除全部记录)语句删除记录时不被记入事务日志，所以该语句不能激活delete删除操作的触发器。(4)如果外键所引用的父表已经创建了对子表级联修改或删除的触发器，则子表不允许创建具有相同动作的替代触发器。(5)触发器中不能使用create,alter,drop等语句。,六、提高数据库效率的设计,例：设有商品表和合同表，现为商品表创建一个“删除商品”的触发器，当删除商品表中的某个商品时，把这些商品在合同表中的销售合同同时删除，实现两个表的级联删除。Create trigger 删除商品 on 商品表after deleteas delete 合同表 where 货号 in(select 货号 from deleted),
