校友录 SQLServer应用示例.docx

校友录 SQLServer应用示例.docx

《校友录 SQLServer应用示例.docx》由会员分享，可在线阅读，更多相关《校友录 SQLServer应用示例.docx（32页珍藏版）》请在冰豆网上搜索。

校友录SQLServer应用示例

校友录—SQL_Server_2005应用示例

第1章概述

案例教学的特点：

1）项目运作方式。

案例教学通过指导学生参与一个实际的项目开发使其能够了解整个软件项目开发的过程，了解项目整个开发过程中所涉及到的文档，了解项目代码的书写规范。

2）任务驱动教学。

通过使学生完成具体的任务，让他们可以掌握实际工作中需要的技能和方法。

案例教学同时覆盖了所对应课程的必要知识点，也是对课本内容很好的回顾。

SQLServer2005数据库程序设计案例是配合《SQLServer2005数据库开发和实现》课程展开的，除应用到本门课程的知识外，还会帮助学生强化以前学到的知识，真正将知识转化为实际的技能。

3）不占用教学课时。

教师指导学生在课外时间逐步完成整个案例设计，也可以在学期结束后增加一个小学期集中完成。

学生最好在同一个实验环境中完成开发任务，如果没有条件的话，也可以在宿舍或家中完成学习任务。

目的

培养学生独立开发一套完整的数据库系统的能力。

该案例教学要求学生从信息收集开始，逐步进行系统需求分析、数据库设计、编码及调试、数据库维护等任务。

内容

本案例为一个校友录系统。

校友录网站功能共分为两大部分：

用户个人信息功能和班级功能。

用户个人信息功能中包含：

创建新账号（包含用户个人信息）、编辑用户个人信息以及上传更新用户个人头像。

班级功能中包含：

搜索班级、创建学校、创建班级、加入班级、班级留言、班级相册、上传相册以及相册留言等功能。

该校友录系统的前台网站是使用ASP.NET开发，后台系统采用SQLServer2005作为数据库，学生将通过实施该数据库解决方案，学习到网站应用程序架设的全过程，掌握数据库系统的开发、实现和维护，同时学生还将了解到如何结合SQLServer2005和ASP.NET来开发一套数据库应用程序。

第2章数据库的生命周期

在企业中部署数据库，需要遵循严格的项目流程。

通常，项目流程会被细化为若干个阶段的工作，并且这些工作是周始往复的，这种流程被称作生命周期，如图2-1所示。

图21数据库的生命周期

在信息收集阶段，必须了解系统中数据的组成，确定所需的数据是已经存在还是需要新建。

如在我们的校友录系统中，需要确定系统的使用者，他们对系统的功能要求（如可留言、可上传相片）等。

这一阶段，系统调研人员必须详尽了解系统使用者对于系统的功能要求。

需求分析的主要任务是：

详细调查现实世界要处理的对象（组织、部门、企业等）；充分了解系统的概况；明确用户的各种需求；确定系统的功能。

这一阶段我们将根据用户的描述将校友录系统的总体功能划分为两个子功能模块：

用户个人信息功能模块和班级功能模块。

用户个人信息功能模块中包含：

创建新账号（包含用户个人信息）、编辑用户个人信息以及上传更新用户个人头像。

班级功能模块中包含：

搜索班级、创建学校、创建班级、加入班级、班级留言、班级相册、上传相册以及相册留言等功能。

设计阶段可划分为：

概念结构设计阶段，逻辑结构设计阶段和物理结构设计阶段。

概念结构设计是整个数据库设计的关键，通过对用户需求进行综合、归纳与抽象，形成一个独立于具体数据库管理系统（DBMS）的概念模型。

对于我们的校友录系统，就是要确定哪些可以做为实体，哪些做为属性以及确定实体间的联系，并设计出E-R图。

逻辑结构设计是将概念结构转换为某个DBMS所支持的数据模型。

这里我们把E-R图转换为关系模式。

物理设计阶段是为逻辑数据模型选取一个最适合应用环境的物理结构（包括存储结构和存取方法）。

在部署和试运行阶段，运用DBMS提供的数据语言、工具及宿主语言，根据逻辑设计和物理设计的结果建立数据库，编写与调试应用程序，组织数据入库，并进行试运行。

同时预估数据库系统的工作量，确定系统的安全性，预期系统的存储和内存需求；然后把新的数据库从测试环境中迁移到实际环境中进行使用。

当系统开始运行之后，需要持续地负责保证系统的可用性、进行性能的监视、性能调优（此部分不在本案例讨论范围）、进行数据备份和恢复，同时进行权限的管理。

但是，任何一个数据库是不可能保持不变的，因为实际的业务、IT架构都会不断的改变，当系统需要进行维护的时候，需要根据收集到的信息再次重复整个生命周期的流程。

最后，需要确定数据库的最终状态；确定某些数据是否需要继续保存；是否有新的应用程序需要额外的数据；系统是否对数据有着更多的保密需求；是否需要在应用程序终止使用后，保存相应的数据。

如上所述，数据库专家需要在这样的生命周期中不断地维护数据库的整个环境。

后面的几章，我们将按照图中数据库生命周期的各个阶段，逐步完成整个系统的设计。

第3章需求分析和数据库设计

需求分析是整个数据库设计过程中最重要的步骤之一，是后续各阶段的基础。

它包含这样几个步骤：

收集资料、分析整理、绘制数据流图、建立数据字典和用户确认。

数据库设计又可分为概念设计、逻辑设计和物理设计。

接下来将简要的介绍一下收集资料到逻辑设计阶段（为了简化设计，我们略过数据流图和数据字典，并假定此系统通过了用户确认）。

信息收集和需求分析

通常，校友录网站的操作流程如下所示：

1．新用户通过注册系统，获得用户登录账号。

同时发送用户账号信息以及个人信息到后台数据库相关表进行存储。

2．注册用户可修改登录密码、个人信息及个人头像，并将数据发送到后台数据库相关表进行更新。

3．注册用户可通过搜索功能进行同学、班级以及学校搜索，并可加入搜索到的相关班级，同时将相关数据发送到后台数据库相关表进行更新。

如果没有搜索到学校或班级，则可创建新的学校或班级。

4．用户登录进入校友录后，可以通过数据库的查询列举所加入的所有班级。

5．班级成员可在班级首页的留言板或班级留言板中进行留言，同时发送相关留言信息到后台数据库相关表进行存储。

6．班级成员可在上传照片页面中上传相片到所属的所有班级，同时发送相关留言信息到后台数据库相关表进行存储。

7．班级成员可在班级相册页面内对照片进行评论，同时发送相关评论信息到后台数据库相关表进行存储。

概念设计——E-R图

概念模型用于信息世界的建模。

概念模型不依赖于某一个数据库管理系统（DBMS），但可以方便的转换为计算机上某一DBMS所支持的特定的数据模型。

通过对用户对数据的需求进行综合、归纳与抽象，将形成一个完善的概念模型，可以用E-R（实体联系）图来表示。

E-R图是对现实世界的一种抽象，它的主要成份是实体、属性和联系。

1.实体：

客观存在并可以互相区分的事物称为实体，是现实世界中各种事物的抽象。

如本案例中一个班级为一个实体。

一般来说，每个实体都相当于数据库中的一个表。

实体用一个矩形框来表示。

2.属性：

属性是实体所具有的某些特征，通过属性对实体进行刻画。

实体是由属性组成的，如班级有班级名称、创建人等属性。

一个实体本身具有许多属性，能够唯一标识实体的属性称为该实体的码。

属性用一个椭圆来表示，本案例中，为使E-R图清晰直观，我们将属性内置于实体的矩形框中。

3.联系：

现实世界的事物内部或事物之间都有联系，这些联系在信息世界里反映为实体内部或实体之间的联系，如班级属于某个学校，那么班级和学校之间是“属于”联系。

联系有一对一联系，一对多联系和多对多联系，分别用1：

1、1：

n和m：

n来表示。

如一个登录用户名只能填写一份个人资料，那么登录用户和个人资料就是一对一联系；一个班级只能属于一个学校，而一个学校可以拥有多个班级，学校和班级之间就是一对多联系；一个班级可以有多张相片，而一张相片也可为多个班级所拥有，所以班级和相片之间是多对多联系。

联系用菱形表示，并用线段联接相关的两个或多个实体，在菱形两端线段上标明联系的类型。

根据上节的需求分析，我们作出E-R图，如图3-1所示：

图3-1校友录系统E-R图

逻辑设计

概念设计的结果得到一个与计算机、软硬件的具体性能无关的全局概念模式。

数据库逻辑设计的任务是将概念结构转换成特定DBMS所支持的数据模型（如关系模型）的过程。

本案例中我们将其转换为关系模型。

将E-R图转换为关系模型实际上就是要将实体、实体的属性和实体之间的联系转化为关系模式，这种转换一般遵循如下原则：

•一个实体转换为一个关系模式，实体的属性就是关系的属性，实体的关键字就是关系的关键字。

•一个1:

1联系可以转换为一个独立的关系模式，也可以与任意一端实体所对应的关系合并，在被合并关系中增加属性，其新增的属性为联系本身的属性和与联系相关的另一个实体的码。

•一个1:

n联系可以转换为一个独立的关系模式，也可以与n端对应的关系模式合并。

如果转换为一个独立的关系模式，则与该联系相连的各实体的码以及联系本身的属性均转换为关系的属性，而关系的码为n端实体的码。

如果与n端的关系模式合并则在n端实体集中增加新属性，新属性由联系对应的1端实体集的码和联系自身的属性构成，新增属性后原关系的码不变。

•一个m：

n联系转换为一个关系模式，与该联系相连的各实体的码以及联系本身具有的属性均转换为关系的属性，而关系的码为各实体码的组合。

1）每一实体转换成一个关系模式（带下划线的属性为码）

⏹实体：

用户

对应关系模式：

用户表（登录名，密码）

⏹实体：

个人资料

对应关系模式：

个人资料表（登录名，用户名，性别，出生日期，注册时间，电话，住址，E-MAIL，头像）

⏹实体：

头衔

对应关系模式：

头衔表（头衔编号，头衔名称，描述）

⏹实体：

班级

对应关系模式：

班级表（班级编号，班级名称，创建者，创建日期，所属学校）

⏹实体：

学校

对应关系模式：

学校表（学校编号，学校名称）

⏹实体：

留言

对应关系模式：

留言表（留言编号，留言内容，留言人，留言日期，修改日期，所属班级编号）

⏹实体：

相片

对应关系模式：

相片表（相片编号，相片名称，上传人，上传日期，相片路径，相片大小）

⏹实体：

评论

对应关系模式：

评论表（评论编号，评论人，评论日期，评论内容，评论相片，所属班级）

2）每一m：

n的联系转换为一个关系模式

⏹联系：

登录

所联系的实体及关键字：

用户（登录名），头衔（头衔编号），班级（班级编号）。

对应关系模式：

用户班级表（登录名，班级编号，头衔编号）

⏹联系：

相册

所联系的实体及关键字：

班级（班级编号），相片（相片编号）。

对应关系模式：

相册表（班级编号，相片编号）

第4章数据库部署

对于给定的逻辑数据模型选取一个最适合应用环境的物理结构的过程，称为数据库物理设计。

物理设计的任务是为了有效地实现逻辑模式，确定所采取的存储策略。

此阶段以逻辑设计的结果作为输入，结合具体DBMS的特点与存储设备特性进行设计，选定数据库在物理设备上的存储结构和存取方法。

通过上一章的分析和设计，我们对要建立的数据库已经有了一个完整的概念，本章将介绍如何安装和配置SQLServer2005，并创建和配置数据库；第五章将在上一章逻辑设计的基础上建立表，协同本章一起完成数据库的部署。

任务布置

1）安装SQLServer2005。

2）创建数据库SchoolMates，其数据文件名为SchoolMates.mdf，置于D：

目录下；日志文件名为SchoolMates_log.ldf，置于根目录下；设置恢复模式为“完整”。

3）建立架构webapp，用于维护数据库安全性，将后面所建数据库对象均置于架构webapp下。

SQLSERVER2005的安装和配置

⏹如果您的电脑上已经安装了SQLServer2005，可跳过以下步骤。

⏹找到您电脑里SQLServer2005安装文件所在位置，双击安装文件进行SQLServer的安装。

⏹在“最终用户许可协议”项中，选中“我接受许可条款和条件”项，然后单击“下一步”，此时进行必备组件的安装。

⏹在“安装必备组件”界面中单击“下一步”。

⏹在“欢迎使用MicrosoftSQLServer安装向导”界面中单击“下一步”。

⏹在“系统配置检查”界面中单击“下一步”。

⏹在“注册信息”界面中输入你的个人信息，然后单击“下一步”。

⏹在“安装组件”页，勾选“SQLServerDatabaseServices”、“NotificationServices”、以及“工作站组件、联机丛书和开发工具”，并单击下一步。

⏹在“实例名”界面选择“默认实例”，单击“下一步”。

⏹在“服务账户”界面，选择“使用内置系统账户”，然后确定在“安装结束时启动服务”中勾选所有服务。

单击“下一步”。

⏹在“身份验证模式”界面，选择“Windows身份验证模式”，然后单击“下一步”。

⏹在“排序规则设置”界面，单击“下一步”。

⏹单击“安装”，完成安装向导。

数据库的创建和配置

1）单击“开始”，指向“所有程序”，指向“MicrosoftSQLServer2005”，然后单击“SQLServerManagementStudio”。

2）在“连接到服务器”对话框中，使用WINDOWS身份验证连接服务器。

3）在“对象资源管理器”中，右击“数据库”，在弹出的“新建数据库”对话框中输入新数据库的名称：

SchoolMates；其数据文件名为SchoolMates.mdf，置于“D:

\”目录下；日志文件名为SchoolMates_log.ldf，置于默认目录下。

（事务日志文件放置在与数据文件分开的物理磁盘上，这将减少争用，并且可使一组驱动器磁头在其他磁头从数据文件中读取数据的同时，将事务记录到事务日志中。

由于事务可立即写入磁盘，而无需等待数据读操作的完成，因此更新数据将非常快速。

同时由于日志文件是顺序写入的，因此如果日志存储在一个专用磁盘中，则磁盘磁头将保留在正确的位置以便于下一次写操作进行）

4）单击“确定”，完成操作。

创建架构webapp

概述

通过架构webapp可以用于实现数据库安全管理，通过授权可以使应用程序只能访问webapp架构下的数据库对象，注意后面所建表、视图及存储过程都属于此架构。

架构定义

架构名称

架构所有者

webapp

dbo

第5章表和索引的创建及数据完整性

在创建完数据库之后，首先要做的就是在数据库中创建表。

通常来说，表的设计是由开发人员完成，必须满足实际数据的内容和关系数据库的要求（比如范式要求），而数据库管理员的任务是根据要求向数据库中添加这些表。

在逻辑上，数据库由大量的表构成，表中包含了由行和列组织起来的数据；在物理上，表存储在文件中，表中的数据存储于页中。

在数据库的开发和应用中，快速地从数据库中查询到所需的数据是十分重要的。

但是，随着数据量的不断增大，查询所花费时间也在大量增加。

使用索引可以对查询速度进行优化，SQLServer2005使用索引指向数据页上某行的位置，这样查询数据就不需要查遍表的所有数据页了。

数据质量对于使用效率和数据库程序运行效率起着决定性的作用。

如果数据库中存在大量错误数据，那么效率会大大降低。

在数据库的使用中，诸如数据录入错误和表间关联数据的修改等操作都会造成错误数据的产生。

因此，不论从首次输入还是收集到存储的整个过程都需保证数据的唯一性和一致性。

数据的唯一性、一致性称为数据完整性。

数据完整性分为实体完整性、引用完整性（也称为参照完整性）和用户自定义完整性。

实体完整性一般通过设置主键来实现，参照完整性一般通过设置外键实现，而用户自定义完整性则可通过CHECK约束、DEFAULT约束和UNIQUE约束实现。

本章将在前两章的基础上，把逻辑设计得到的关系模式转换为表，同时为实现数据完整性设置主、外键及各种约束。

另外根据系统的查询要求在表上建立索引。

任务布置

1）建表。

根据第三章的关系模式设计表，为表的各属性选择合适的数据类型以及属性可否为空。

注意所有表的架构均为webapp。

2）设置主键，为数据库SchoolMates的每个表设置主键。

每个表都应该具有主键，主键的存在就代表着表结构的完整性，表的记录必须得有唯一区分的字段，主键主要是用于与其他表的外键关联，本记录的修改与删除，如果没有主键，这些操作会变的非常麻烦。

（提示：

可以使用自动编号作为主键，就是新建一个ID字段，自动增长）

3）设置外键。

例如，班级表中的属性所属学校，引用学校表的学校编号，所以应该将班级表的所属学校设置为外键。

4）设置其他约束。

根据系统的实际要求，设置约束。

例如，用户资料中的性别只能为男或女，则可对性别设置CHECK约束；用户注册日期如果为空，就默认为当前时间，则可设置DEFAULT约束；图片表中的图片如果不允许重复，则可设置UNIQUE约束，等等。

5）建立索引。

考虑系统对表的查询频率，为查询次数较多的表的字段建立索引。

例如，进入班级相册时，系统会自动检索出相片的评论信息显示出来，系统通常是按照发表评论的时间检索，则可为评论表的评论时间建立聚集索引。

（提示：

为表设置主键时，数据库会自动为主键建立聚集索引，如果要在其他属性上建立聚集索引，可先将主键去除，再使用ALTERTABLE[TABLENAME]ADDCONSTRAINT[PK_TABLEFIELD]PRIMARYKEYNONCLUSTERED）修改主键索引为非聚集索引，再在相关属性上建立聚集索引）

表Album

概述

表Album用于记录相片及所属相册，通过字段AlbumID与ClassInfo表关联，通过字段PhotoID与Photo表关联。

表定义

表Album定义如下：

列名

数据类型

允许空

说明

备注

AlbumID

Int

否

相册编号

与ClassInfo表中ClassID同义

PhotoID

Int

否

图片编号

主键

表Album的主键是（AlbumID,PhotoID）。

外键

表Album的外键是AlbumID，类型为int，用于与表ClassInfo中的ClassID字段关联。

AlbumID字段可以为空。

表Album的外键是PhotoID，类型为int，用于与表Photo中的PhotoID字段关联。

PhotoID字段可以为空。

约束

表Album无其他约束。

索引

主键字段AlbumID，PhotoID具有自动创建的聚集索引。

表ClassInfo

概述

表ClassInfo用于记录班级信息，每一个独立的班级在该表中都对应一条记录。

该表通过字段ClassID与其他表关联，通过字段SchoolID与SchoolInfo表关联。

表定义

表ClassInfo定义如下：

列名

数据类型

允许空

说明

备注

ClassID

int

否

班级编号

也是Album表中的AlbumID

ClassName

varchar（50）

否

班级名称

CreateBy

varchar（20）

否

创建人

CreateDate

datetime

是

创建日期

SchoolID

int

否

所属学校编号

主键

表ClassInfo的主键是ClassID。

外键

表ClassInfo的外键是SchoolID，类型为int，用于与表SchoolInfo中的SchoolID字段关联。

表ClassInfo的外键是CreateBy，类型为varchar（20），用于与表UserAccount中的UserID字段关联。

约束

表ClassInfo中的CreateDate默认为系统当前时间。

索引

主键字段ClassID具有自动创建的聚集索引。

表Commnet

概述

表Commnet用于记录图片评论信息，每一条评论在该表中都对应一条记录。

该表通过字段AlbumID,PhotoID与Album表关联，通过CommentBy字段与UserAccount表关联。

表定义

表Commnet定义如下：

列名

数据类型

允许空

说明

备注

CommentID

int

否

评论编号

自动生成

CommentBy

varchar（20）

否

评论人

CommentDate

datetime

是

评论日期

CommentContent

varchar（200）

否

评论内容

PhotoID

int

否

评论图片编号

AlbumID

Int

否

图片所属相册编号

主键

表Commnet的主键是CommentID，类型为int，设置自动增量。

外键

表Commnet的外键有CommentBy，类型为varchar（20），用于与表UserClass中的UserID字段关联。

表Commnet的外键有PhotoID，类型为int，用于与表Photo表关联。

约束

表Commnet中的CommentDate默认为系统当前时间。

索引

主键字段CommentID具有唯一、非聚集索引，字段CommentDate具有聚集索引。

表MessageBoard

概述

表MessageBoard用于记录班级留言信息，每一条留言在该表中都对应一条记录。

该表通过字段ClassID与ClassInfo表关联，通过CreateBy字段与UserAccount表关联。

表定义

表MessageBoard定义如下：

列名

数据类型

允许空

说明

备注

MessageBoardID

int

否

留言编号

自动增长

MessageContent

varchar（500）

否

留言内容

CreateBy

varchar（20）

否

留言人

CreateDate

datetime

是

留言日期

EditDate

datetime

是

修改日期

ClassID

int

否

班级编号

主键

表MessageBoard的主键是MessageBoardID，类型为int，设置自动增量。

外键

表MessageBoard的外键有CreateBy，类型为varchar（20），用于与表UserClass中的UserID字段关联。

表MessageBoard的外键有ClassID，类型为int，用于与表ClassInfo表关联。

约束

表MessageBoard中的CreateDate和EditDate默认为系统当前时间。

索引

主键字段MessageBoardID具有自动创建的聚集索引。

表Photo

概述

表Photo用于记录图片信息，每一图片在该表中都对应一条记录。

表定义

表Photo定义如下：

列名

数据类型

允许空

说明

备注

PhotoID

int

否

图片编号

自动增长

PhotoName

varchar（200）

否

图片名称

UploadBy

varchar（20）

否

上传人

UploadDate

datetime

是

上传日期

PhotoPath

varchar（50）

否

图片路径

唯一

PhotoSize

Int

否

图片大小

主键

表Photo的主键是PhotoID，类型为int，设置自动增量。

外键

表Photo的外键有UploadBy，类型为varchar（20），用于与表UserClass中的UserID字段关联。

约束

表Photo中的UploadDate默认为系统当前时间。

表Photo中的Pho