数据库设计应用实践.ppt

第2章 数据库设计应用实践,图书管理系统功能分析,图书管理数据库设计,图书管理系统功能分析,图书管理数据库设计,2.1 图书管理系统功能分析,本书要完成的图书管理系统主要包含登录、图书查询、借书查询、读者管理、图书管理、借书管理、还书管理等功能模块，流程图如图2.1所示。,图2.1 图书管理系统流程图,2.1 图书管理系统功能分析,1登录功能 以读者身份登录后只能查看借书情况和图书馆中的藏书情况，登录的账户名可以直接使用读者的借书证号，密码在登录后可以自行修改。图书管理员身份的登录账户分为三类：读者管理员、图书管理员和超级管理员。三类管理员拥有的权限不同，以读者管理员身份登录后只能进行读者管理的操作；以图书管理员身份登录后只能进行图书管理的操作；以超级管理员身份登录后则可以进行图书查询、借书查询、读者管理、图书管理、借书管理、还书管理、数据备份等所有操作。 2读者管理 读者涉及的信息有：借书证号、密码、姓名、性别、出生时间、专业、借书量、照片、备注和联系方式，每个读者的借书证号都是唯一的，读者的借书量不能超过5。只有以读者管理员或超级管理员身份登录系统以后才能进行读者管理，读者管理的操作包括添加、修改和删除。新添加的读者借书量默认为0，在修改读者信息时不能修改读者的借书证号和借书量，删除读者信息时如果该读者借过书尚未归还的则不允许删除该读者的记录。在显示某读者信息时，需要包含该读者总的借书次数。,2.1 图书管理系统功能分析,3图书管理 图书涉及的信息有：ISBN、书名、作译者、出版社、出版年月、价格、复本量、库存量、分类号、内容提要和封面照片，每一种图书的ISBN都是唯一的，库存量不能大于复本量。只有以图书管理员或超级管理员身份登录系统以后才能进行图书管理，管理的操作包含添加、修改和删除。当添加一种新书时，系统自动生成每本图书的图书ID及在借情况，在借情况默认都为否。在删除图书信息时如果该图书已经借出则不允许删除该图书的记录，若允许删除则将该图书的在借情况都一起删除。 4图书查询 用户可以根据图书的ISBN、书名、作译者、出版社进行模糊查询，查询到结果后将图书信息分页显示，如果不输入任何条件则显示所有图书信息。,2.1 图书管理系统功能分析,5借书管理 借书管理只有以超级管理员身份登录系统后才能进行。用户可以首先输入读者的借书证号查询其借书记录，确认读者没有借过相同ISBN的书时，然后输入要借图书的ISBN、图书ID，先判断图书是否存在、该书是否在借、读者是否借过该书等情况，如果可借则完成借书操作，借书时间登记为系统当前时间，规定的还书期限为5天。当借阅操作完成后，系统自动将读者的借书量加1，图书的库存量减1，该本图书的借出情况变为在借。 6借书查询 以读者身份登录系统时，用户点击借书查询模块可以直接查询自己的借书情况。以管理员身份登录时，用户输入读者的借书证号即可以查询该读者的借书情况，不输入则显示所有的借书情况。,2.1 图书管理系统功能分析,7还书管理 还书操作只能由超级管理员来进行，输入读者的借书证号可以查到该读者现有的借书记录，将结果逐行显示，用户输入图书ID后单击还书按钮进行还书操作，即删除了该读者的这条借书记录。还书操作完成后系统自动将该读者的借书量减1，图书的库存量加1，该本图书的在借情况改为否。并根据删除的借书记录生成一条还书记录，还书时间为系统当前时间。 8数据备份 超级管理员可以指定一个后缀名为“.bak”的备份文件名，然后将图书管理数据库中的所有数据备份为该文件。 另外，系统还可以设计向读者自动发送电子邮件通知其归还过期图书的功能。 本书将分别在C/S模式和B/S模式下实现以上功能，C/S模式下使用VB6.0来完成，B/S模式下使用ASP.NET3.5（2008）来完成。,2.2 图书管理数据库设计,2.2.1 图书管理的E-R模型 通过了解图书管理系统要实现的功能，可以得出图书管理数据库包含的实体有“管理员”、“读者”、“图书”和“在借情况”。 管理员的属性有：角色名、密码和备注。 读者的属性有：借书证号、密码、姓名、性别、出生时间、专业、借书量、照片、备注和联系方式。 图书的属性有：ISBN、书名、作译者、出版社、出版年月、价格、复本量、库存量、分类号、内容提要和封面照片。 在借情况的属性有：图书ID和是否借出。,2.2.1 图书管理的E-R模型,图书管理的E-R模型就可以用图2.2来表示。,图2.2 图书管理的E-R模型,2.2.2 图书管理数据库的逻辑结构设计,图书管理数据库的逻辑结构设计就是将图书管理的E-R模型转化为关系模式。在图书管理的各个实体集中，管理员（命名为Administrator）的主码为“角色名”，读者（命名为TReader）的主码为“借书证号”，图书（命名为TBook）的主码为“ISBN”，在借情况（命名为TBLend）的主码为“图书ID”。实体集“读者”和“图书”之间通过联系“借阅（命名为TLend）”表示借阅关系，通过联系“归还（命名为HLend）”表示归还关系。 所以由图2.2的E-R模型可以得到如下关系模式： Administrator（角色名，密码，备注） TReader（借书证号，密码，姓名，性别，出生时间，专业，借书量，照片，备注，联系方式） TBook（ISBN，书名，作译者，出版社，出版年月，价格，复本量，库存量，分类号，内容提要，封面照片） TLend（借书证号，ISBN，图书ID，借书时间，应还时间） HLend（编号、借书证号、ISBN、图书ID、借书时间、还书时间） TBLend（图书ID，ISBN，是否借出）,2.2.3 图书管理数据库的物理结构设计,在图书管理数据库逻辑结构设计完成以后，我们只得到了图书管理数据库中要创建的表和字段。但具体的表结构，如字段在SQL Server中的数据类型等还需要在物理结构设计中完成。 可以在图书管理数据库中创建读者信息表TReader，表中包含的字段有：借书证号、密码、姓名、性别、出生时间、专业、借书量、照片、备注和联系方式。 在TReader表中，只有“借书证号”列能唯一标识一个读者，所以将“借书证号”列设为该表主键。最后设计的TReader的表结构如表2.1所示。,表2.1 读者信息表（TReader）结构,2.2.3 图书管理数据库的物理结构设计,参照TReader表结构的设计方法，同样可以设计出其他几个表的结构，如表2.2所示的是图书信息表（TBook）的表结构。,表2.2 图书信息表（TBook）结构,2.2.3 图书管理数据库的物理结构设计,表2.3所示的是借阅表（TLend）的表结构。,表2.3 借阅表（TLend）结构,2.2.3 图书管理数据库的物理结构设计,表2.4所示的是还书记录表（HLend）的表结构。,表2.4 还书记录表（HLend）结构,2.2.3 图书管理数据库的物理结构设计,表2.5所示的是图书借出表（TBLend）的表结构。,表2.5 图书借出表（TBLend）结构,L2.1 图书管理系统功能分析,L1C/S模式 对于一般的数据库应用系统，除了数据库管理系统外，需要设计适合普通人员操作数据库的界面。目前，流行的开发数据库界面的工具主要包括Visual Basic、Visual C+、Visual FoxPro、Delphi、PowerBuilder等。数据库应用程序与数据库、数据库管理系统之间的关系如图2.3所示。,图2.3 数据库应用程序与数据库、数据库管理系统之间的关系,L1C/S模式,应用程序和数据库管理系统可以运行在同一台计算机上（单机方式），也可以运行在网络方式。在网络方式下，数据库管理系统在网络上的一台主机上运行，应用程序可以在网络上的多台主机上运行即一对多的方式。例如，用Visual Basic 6.0开发的C/S模式的图书管理系统的“图书管理”窗口如图2.4所示。,图2.4 C/S模式的图书管理系统的“图书管理”窗口,返回,L2B/S模式,基于Web的数据库应用采用三层客户/服务器模式，也称B/S模式。第一层为浏览器，第二层为Web服务器，第三层为数据库服务器。浏览器是用户输入数据和显示结果的交互界面，用户在浏览器表单中输入数据，然后将表单中的数据提交并发送到Web服务器，Web服务器应用程序接受并处理用户的数据，通过数据库服务器，从数据库中查询需要的数据（或把数据录入数据库）返回给Web服务器，Web服务器再把返回的结果插入HTML页面，传送到客户端，在浏览器中显示出来，如图2.5所示。,图2.5 三层客户/服务器结构,L2B/S模式,例如，用ASP.NET3.5（2008）开发的B/S模式的图书管理系统“图书管理”页面如图2.6所示。,图2.6 B/S模式的图书管理系统“图书管理”页面,返回,L2.2 图书管理数据库设计,L1表结构 每个数据库包含了若干个表。表是SQL Server中最基本的数据库对象，它是用来存储数据的一种逻辑结构。表由行和列组成，因此也称之为二维表。表是在日常工作和生活中经常使用的一种表示数据及其关系的形式，表2.6就是用来表示学生情况的一个学生表。,表2.6 “学生”表,L1表结构,下面是几个与表有关的几个概念： （1）表结构。组成表的各列的名称及数据类型，统称为表结构。 （2）记录。每个表包含了若干行数据，它们是表的“值”，表中的一行称为一个记录。因此，表是记录的有限集合。 （3）字段。每个记录由若干个数据项构成，将构成记录的每个数据项称为字段。例如表2.6中，表结构为（学号，姓名，性别，出生时间，专业，总学分，备注），包含7个字段，由5个记录组成。 （4）空值。空值（NULL）通常表示未知、不可用或将在以后添加的数据。若一个列允许为空值，则向表中输入记录值时可不为该列给出具体值。而一个列若不允许为空值，则在输入时必须给出具体值。 （5）关键字。若表中记录的某一字段或字段组合能唯一标识记录，则称该字段或字段组合为候选关键字（Candidate key）。若一个表有多个候选关键字，则选定其中一个为主关键字（Primary key），也称为主键。当一个表仅有唯一的一个候选关键字时，该候选关键字就是主关键字。这里的主关键字与主码的所起的作用是相同的，都用来唯一标识记录行。,返回,L2SQL Server中的数据类型,表中的一列必须存储相同类型的数据，所以在创建表的列时，必须为其指定数据类型。SQL Server 2008提供了丰富的系统数据类型，将其列于表2.7中。,表2.7 系统数据类型表,L2SQL Server中的数据类型,1整数型 整数包括bigint、int、smallint和tinyint，从标识符的含义就可以看出，它们的表示数范围逐渐缩小。 bigint：大整数，数范围为：2632631，其精度为19，小数位数为0，长度为8字节。 int：整数，数范围为2312311，其精度为10，小数位数为0，长度为4字节。 smallint：短整数，数范围为2152151，其精度为5，小数位数为0，长度为2字节。 tinyint：微短整数，数范围为0255，长度为1字节，其精度为3，小数位数为0，长度为1字节。,L2SQL Server中的数据类型,2精确数值型 精确数值型数据由整数部分和小数部分构成，其所有的数字都是有效位，能够以完整的精度存储十进制数。精确数值型包括decimal 和 numeric两类。在SQL Server中，这两种数据类型在功能上完全相同。 声明精确数值型数据的格式是numeric | decimal(p,s)，其中p为精度，s为小数位数，s的默认值为0。例如指定某列为精确数值型，精度为6，小数位数为3，即decimal(6,3)，那么若向某记录的该列赋值56.342689时，该列实际存储的是56.3427。 decimal和numeric可存储从1038 +1到10381的固定精度和小数位的数字数据，它们的存储长度随精度变化而变化，最少为5字节，最多为17字节。,L2SQL Server中的数据类型,3浮点型 浮点型也称近似数值型。顾名思义，这种类型不能提供精确表示数据的精度，使用这种类型来存储某些数值时，有可能会损失一些精度，所以它可用于处理取值范围非常大且对精确度要求不是十分高的数值量，如一些统计量。 有两种近似数值数据类型：float(n)和real，两者通常都使用科学记数法表示数据，即形为：尾数E阶数，如5.6432E20，2.98E10，1.287659E-9等。 real：使用4字节存储数据，表数范围为-3.40E + 38到3.40E + 38，数据精度为7位有效数字。 float：float型数据的数范围为1.79E+308到1.79E+308。定义中的n取值范围是153，用于指示其精度和存储大小。当n在124之间时，实际上将定义一个real型数据，存储长度为4字节，精度为7位有效数字。当n在2553之间时，存储长度为8字节，精度为15位有效数字。当默认n时，代表n在2553之间。,L2SQL Server中的数据类型,4货币型 SQL Server提供了两个专门用于处理货币的数据类型：money和smallmoney，它们用十进制数表示货币值。 money：数据的数范围为2632631，其精度为19，小数位数为4，长度为8字节。money的数的范围与bigint相同，不同的只是money型有4位小数，实际上，money就是按照整数进行运算的，只是将小数点固定在末4位。 smallmoney：数范围为2312311，其精度为10，小数位数为4，长度为4字节。可见smallmoney与int的关系就如同money与bigint的关系。 当向表中插入money或smallmoney类型的值时，必须在数据前面加上货币表示符号（$），并且数据中间不能有逗号（,）；若货币值为负数，需要在符号$的后面加上负号（）。例如：$15000.32，$680，$20000.9088都是正确的货币数据表示形式。,L2SQL Server中的数据类型,5位型 SQL Server中的位（bit）型数据相当于其他语言中的逻辑型数据，它只存储0和1，长度为一个字节。但要注意，SQL Server对表中bit类型列的存储作了优化：如果一个表中有不多于8个的bit列，这些列将作为一个字节存储；如果表中有9到16个bit列，这些列将作为两个字节存储，更多列的情况依次类推。 当为bit类型数据赋0时，其值为0，而赋非0（如100）时，其值为1。 字符串值TRUE和FALSE可以转换为以下bit 值：TRUE转换为1，FALSE转换为0。,L2SQL Server中的数据类型,6字符型 字符型数据用于存储字符串，字符串中可包括字母、数字和其他特殊符号（如#、&等）。在输入字符串时，需将串中的符号用单引号或双引号括起来，如abc、AbcCde。 SQL Server字符型包括两类：固定长度（char）或可变长度（varchar）字符数据类型。 char(n)：定长字符数据类型，其中n定义字符型数据的长度，n在1到8000之间，默认为1。当表中的列定义为char(n)类型时，若实际要存储的串长度不足n时，则在串的尾部添加空格以达到长度n，所以char(n)的长度为n。例如某列的数据类型为char(20)，而输入的字符串为ahjm1922，则存储的是字符ahjm1922和12个空格。若输入的字符个数超出了n，则超出的部分被截断。 varchar(n)：变长字符数据类型，其中n的规定与定长字符型char中n完全相同，但这里n表示的是字符串可达到的最大长度。,L2SQL Server中的数据类型,7Unicode字符型 Unicode是“统一字符编码标准”，用于支持国际上非英语语种的字符数据的存储和处理。SQL Server的Unicode字符型可以存储Unicode标准字符集定义的各种字符。 Unicode字符型包括nchar(n)和nvarchar(n)两类。nchar是固定长度Unicode数据的数据类型，nvarchar是可变长度Unicode数据的数据类型，二者均使用UNICODE UCS-2字符集。 nchar(n)：nchar(n)为包含n个字符的固定长度Unicode字符型数据，n的值在1与4,000之间，长度2n字节。若输入的字符串长度不足n，将以空白字符补足。 nvarchar(n)：nvarchar(n)为最多包含n个字符的可变长度Unicode字符型数据，n的值在1与4,000之间，默认为1。长度是所输入字符个数的两倍。,L2SQL Server中的数据类型,8文本型 当需要存储大量的字符数据，如较长的备注、日志信息等，字符型数据的最长8000个字符的限制可能使它们不能满足这种应用需求，此时可使用文本型数据。 文本型包括text和ntext两类，分别对应ASC字符和Unicode字符。 text类型可以表示最大长度为2311个字符，其数据的存储长度为实际字符数个字节。 ntext类型可表示最大长度为2301个Unicode字符，其数据的存储长度是实际字符个数的两倍（以字节为单位）。,L2SQL Server中的数据类型,9二进制型 二进制数据类型表示的是位数据流，包括binary（固定长度）和varbinary（可变长度）两种。 binary (n) ：固定长度的n个字节二进制数据。n取值范围为1到8,000，默认为1。binary(n)数据的存储长度为 n+4 字节。若输入的数据长度小于n，则不足部分用0填充；若输入的数据长度大于n，则多余部分被截断。 varbinary (n)：n个字节变长二进制数据。n取值范围为1到8,000，默认为1。varbinary(n)数据的存储长度为实际输入数据长度+4个字节。,L2SQL Server中的数据类型,10日期时间类型 日期时间类型数据用于存储日期和时间信息，在SQL Server 2008中的日期时间数据类型有datetime、smalldatetime 、date、time、datetime2和datetimeoffset。 datetime：datetime类型可表示的日期范围从1753年1月1日到9999年12月31日的日期和时间数据，精确度为百分之三秒（3.33毫秒或0.00333 秒），例如1到3毫秒的值都表示为0毫秒，4到6毫秒的值都表示为4毫秒。 日期部分的表示形式常用的格式如下： 年 月 日 2001 Jan 20、2001 Janary 20 年 日 月 2001 20 Jan 月 日,年 Jan 20 2001、Jan 20,2001、Jan 20,01 月 年 日 Jan 2001 20 日 月,年 20 Jan 2001、20 Jan,2001 日 年 月 20 2001 Jan 年（4位数） 2001表示2001年1月1日 年月日 20010120、010120 月/日/年 01/20/01、1/20/01、01/20/2001、1/20/2001 月-日-年 01-20-01、1-20-01、01-20-2001、1-20-2001 月.日.年 01.20.01、1.20.01、01.20.2001、1.20.2001,L2SQL Server中的数据类型,时间部分常用的表示格式如下： 时:分 10:20、08:05 时:分:秒 20:15:18、20:15:18.2 时:分:秒:毫秒 20:15:18:200 时:分AM|PM 10:10AM、10:10PM,L2SQL Server中的数据类型,smalldatetime：smalldatetime类型数据可表示从1900年1月1日到2079年6月6日的日期和时间，数据精确到分钟。即29.998 秒或更低的值向下舍入为最接近的分钟，29.999 秒或更高的值向上舍入为最接近的分钟。 date：date类型数据可以表示从公元元年1月1日到9999年12月31日的日期，date类型只存储日期数据，不存储时间数据，存储长度为3个字节，表示形式与datetime数据类型的日期部分相同。 time：time数据类型只存储时间数据，表示格式为“hh:mm:ss.nnnnnnn”。hh表示小时，范围为0到23；mm表示分钟，范围为0到59；ss表示秒数，范围为0到59；n是0到7位数字，范围为0到9999999，表示秒的小数部分，即微秒数。所以time数据类型的取值范围为00:00:00.0000000到23:59:59.9999999。time类型的存储大小为5个字节。另外还可以自定义time类型微秒数的位数，例如time(1)表示小数位数为1，默认为7。,L2SQL Server中的数据类型,datetime2：新的datetime2数据类型和datetime类型一样，也用于存储日期和时间信息。但是datetime2类型取值范围更广，日期部分取值范围从公元元年1月1日到9999年12月31日，时间部分的取值范围从00:00:00.0000000到23:59:59.999999。 datetimeoffset：datetimeoffset数据类型也用于存储日期和时间信息，取值范围与datetime2类型相同。但datetimeoffset类型具有时区偏移量，此偏移量指定时间相对于协调世界时（UTC）偏移的小时和分钟数。datetimeoffset的格式为“YYYY-MM-DD hh:mm:ss.nnnnnnn +|hh:mm”，其中hh为时区偏移量中的小时数，范围为00到14，mm为时区偏移量中的额外分钟数，范围为00到59。时区偏移量中必须包含“+”（加）或“”（减）号。这两个符号表示是在UTC时间的基础上加上还是从中减去时区偏移量以得出本地时间。时区偏移量的有效范围为14:00到+14:00。,L2SQL Server中的数据类型,11时间戳型 标识符是timestamp。若创建表时定义一个列的数据类型为时间戳类型，那么每当对该表加入新行或修改已有行时，都由系统自动将一个计数器值加到该列，即将原来的时间戳值加上一个增量。 记录timestamp列的值实际上反映了系统对该记录修改的相对（相对于其他记录）顺序。一个表只能有一个timestamp 列。timestamp类型数据的值实际上是二进制格式数据，其长度为8字节。 12图像数据类型 标识符是image，它用于存储图片、照片等。实际存储的是可变长度二进制数据，介于 0 与2311 (2,147,483,647) 字节之间。在SQL Server 2008中该类型是为了向下兼容而保留的数据类型。微软推荐用户使用varbinary(MAX)数据类型来替代image类型。,L2SQL Server中的数据类型,13其他数据类型 除了上面所介绍的常用数据类型外，SQL Server还提供了其他几种数据类型：cursor、sql_variant、table、uniqueidentifier、xml和hierarchyid。 cursor：是游标数据类型，用于创建游标变量或定义存储过程的输出参数。 sql_variant：是一种存储SQL Server支持的各种数据类型（除text、ntext、image、timestamp 和 sql_variant 外）值的数据类型。sql_variant的最大长度可达8016字节。 table：是用于存储结果集的数据类型，结果集可以供后续处理。 uniqueidentifier：是唯一标识符类型。系统将为这种类型的数据产生唯一标识值，它是一个16字节长的二进制数据。 xml：是用来在数据库中保存xml文档和片段的一种类型，但是此种类型的文件大小不能超过2GB。 hierarchyid：hierarchyid数据类型是SQL Server 2008新增加的一种长度可变的系统数据类型。可使用 hierarchyid表示层次结构中的位置。,返回,