中国石油大学数据库课程设计

《中国石油大学数据库课程设计》由会员分享，可在线阅读，更多相关《中国石油大学数据库课程设计（14页珍藏版）》请在装配图网上搜索。

1、中国石油大学数据库课程设计 1 中国石油大学（北京）远程教育学院 期 末 考 试 数据库课程设计 关于课程考试违规作弊的说明 1、提交文件中涉嫌抄袭内容（包括抄袭网上、书籍、报刊杂志及其他已有论文），带有明显外校标记，不符合学院要求或学生本人情况，或存在查明出处的内容或其他可疑字样者，判为抄袭，成绩为“0”。2、两人或两人以上答题内容或用语有 50%以上相同者判为雷同，成绩为“0”。3、所提交试卷或材料没有对老师题目进行作答或提交内容与该课程要求完全不相干者，认定为“白卷”或“错卷”，成绩为“0”。一、备注：提交一份数据库应用系统的设计报告，报告内容参见第二章数据库设计中的范例汽车修理厂信息管

2、理系统 内容要求如下：1.从应用系统选题中选择一个管理系统；（应用系统选题从以下20 题里任选一题）2.概念模型设计（要求用 E-R 图画出概念模型）确定实体及其主码、属性；确定类型的类型及其属性；3.逻辑结构设计 定义数据库中表的所有属性及其类型、宽度；定义关系模式及其主码、外部码；定义视图，说明每个视图的作用；4.行为设计 说明查询文件的作用；说明表单文件的作用；说明报表文件的作用；二、题目 11、房屋中介公司售房信息系统 数据库主要包括如下内容：现有房源情况、买卖房屋者的基本情况、交易情况等。该系统需实现三个主要功能：各种信息的维护、各种信息的查询、各种信息的统计。中国石油大学数据库课程

3、设计 2 一、概念模型设计（1）合并各分图,消除属性冲突、命名冲突、结构冲突等三类冲突，得到初步 E-R 图，再消除不必要冗余，得到的基本 E-R 图如下所示：（2）各 ER 图各实体的属性如下所示：房源：房源(编号,城区,环境,付款,来源,委托,产权,状态)客源：客源(客户编号,客户姓名,客户电话,电子邮箱,客户地址,具体位置,产权性质,最低购价,最高购价,最小面积,最大面积,委托日期,截止日期,录入人员,当前状态)职员：职员(职员编号,姓名,性别,身份证号,学历,职务,所在部门,出生日期,联系电话,联系电话,家庭住址,电子邮箱,备注,简历)各ER 图中联系的属性如下所示：销售信息：销售信息

4、(售房编号,编号,具体位置,配套环境,房,厅,土地性质,产权性质,价格,产权名称,产权编号,业主姓名,业主电话,联系人,联系电话,委托日期,截止日期,录入人员,当前状态,备注)二、逻辑结构设计 关系模式房源信息，客源信息不存在非主属性对主属性的部分函数依赖，也不存在传递函数依赖，已经达到了 3NF，但是购房信息，租房信息中存在着一些数据冗余。（一）数据库模式定义 表 2.1 房源信息表 列名 数据类型 可否为空 说明 编号 Varchar not null 房屋编号 城区 Nvarchar not null 房屋所在城区 1 1 1 n n n n 客房职购 房房 屋购 房中国石油大学数据库课

5、程设计 3 环境 Nvarchar 房屋所在的环境 付款 Nchar 房屋付款 来源 Nvarchar 房屋来源 委托 Nchar Not null 房屋委托 产权 Nchar Not null 房屋产权 状态 Nchar Not null 房屋状态 表 2.2 客源信息表 列名 数据类型 可否为空 说明 客户编号 Char not null 客户编号 客户姓名 Nvarchar not null 客户姓名 客户电话 Char 客户电话 电子邮箱 Char 客户邮箱 客户地址 Nvarchar 客户地址 具体位置 Nvarchar 具体位置 产权性质 Nvarchar 产权性质 最低购价 Mo

6、ney 最低购价 最高购价 Money 最高购价 最小面积 Int 最小面积 最大面积 Int 最大面积 委托日期 Datetime 委托日期 截止日期 Datetime 截止日期 录入人员 Nvarchar 录入人员 当前状态 Nchar Not null 当前状态 表 2.3 职员信息表 列名 数据类型 可否为空 说明 职员编号 Char not null 职员编号 姓名 Nvarchar 职员姓名 性别 Char Not null 职员性别 身份证号 Char not null 职员身份证号 学历 Nchar 职员学历 职务 Nvarchar 职员职务 所在部门 Nvarchar 职员所

7、在部门 出生日期 Datetime 职员出生日期 联系电话 Char 职员联系电话 中国石油大学数据库课程设计 4 家庭住址 Nvarchar 职员家庭住址 电子邮箱 Nvarchar 职员电子邮箱 备注 Nvarchar 职员备注 简历 Nvarchar 职员简历 表 2.4 操作员信息表 列名 数据类型 可否为空 说明 账号 Char not null 账号 密码 varchar not null 密码 表 2.5 售房信息表 列名 数据类型 可否为空 说明 售房编号 Char not null 售房编号 编号 Varchar not null 编号 具体位置 Nvarchar 具体位置

8、配套环境 Nvarchar 配套环境 房 Int 房间 厅 Int 客厅 土地性质 Nvarchar 土地性质 产权性质 Nvarchar 产权性质 价格 Money 价格 产权名称 Nvarchar 产权名称 产权编号 Char 产权编号 业主姓名 Nchar Not null 业主姓名 业主电话 Char 业主电弧 委托日期 Datetime 委托日期 截止日期 Datetime 截止日期 录入人员 Nvarchar 录入人员 当前状态 Char 当前状态 备注 Nvarchar 备注 表 2.6 租房信息表 列名 数据类型 可否为空 说明 租房编号 Char not null 租房编号

9、编号 Char not null 编号 具体位置 Nvarchar 具体位置 配套环境 Nvarchar 配套环境 房 Int 房间 中国石油大学数据库课程设计 5 厅 Int 客厅 出租价格 Money 出租价格 出租底价 Money 出租底价 业主姓名 Nvarchar 业主姓名 业主电话 Char 业主电话 联系人 Nchar 联系人 联系电话 Char 联系电话 委托日期 Datetime 委托日期 截止日期 Datetime 截止日期 录入人员 Nvarchar 录入人员 当前状态 Char Not null 当前状态 备注 Nvarchar 备注（二）用户子模式定义 表2.7 用户

10、子模式定义 编号 用户子模式(View)作用(共性：提供数据保密和安全保护机制)V1 可售房源视图 便于查询可售房源 V2 可租房源视图 便于查询可租房源 V3 各房态统计视图 便于查看所有房屋的状态 V4 已售房统计视图 便于查看已售房屋信息 V5 已租房统计视图 便于查看已租房屋信息 V6 待售房统计视图 便于查看待售房屋信息 V7 待租房统计视图 便于查看待租房屋信息 V8 求购客源视图 便于查看客源信息 表 2.8 可售房源视图 列名 数据类型 可否为空 说明 编号 Char not null 房屋编号 具体位置 Nvarchar 具体位置 配套环境 Nvarchar 配套环境 房 I

11、nt 房间 厅 Int 客厅 土地性质 Nvarchar l 土地性质 产权性质 Nvarchar 产权性质 价格 Money 价格 权证名称 Nvarchar 权证名称 中国石油大学数据库课程设计 6 权证编号 Char 权证编号 业主姓名 Nvarchar Not null 业主姓名 业主电话 Char 业主电弧 联系人 Nchar 联系人 联系电话 Char 联系电话 委托日期 Datetime 委托日期 截止日期 Datetime 截止日期 录入人员 Nvarchar 录入人员 当前状态 Char 当前装他 表 2.9 可租房源视图 列名 数据类型 可否为空 说明 编号 Char no

12、t null 编号 具体位置 Nvarchar 具体位置 配套环境 Nvarchar 配套环境 房 Int 房间 厅 Int 客厅 出租价格 Money 出租价格 出租底价 Money 出租底价 业主姓名 Nvarchar 业主姓名 业主电话 Char 业主电话 联系人 Nvarchar 联系人 联系电话 Char 联系电话 委托日期 Datetime 委托日期 截止日期 Datetime 截止日期 录入人员 Nvarchar 录入人员 当前状态 Char 当前状态 表2.10 各房态统计视图 列名 数据类型 可否为空 说明 待售房数 Int 待售房数 已售房数 Int 已售房数 待租房数 I

13、nt 待租房数 已租房数 Int、已租房数 表2.11 已售房统计视图 列名 数据类型 可否为空 说明 编号 Char not null 编号 中国石油大学数据库课程设计 7 已售房数 Int 已售房数 表2.12 已租房统计视图 列名 数据类型 可否为空 说明 编号 Char not null 编号 已租房数 Int 已租房数 表2.13 待售房统计视图 列名 数据类型 可否为空 说明 编号 Char not null 编号 待售房数 Int 待售房数 表2.14 待租房统计视图 列名 数据类型 可否为空 说明 编号 Char not null 编号 待租房数 Int 待租房数 表 2.15

14、 求购客源视图 列名 数据类型 可否为空 说明 客户编号 Char not null 客户编号 客户姓名 Nvarchar 客户姓名 客户电话 Char 客户电话 电子邮箱 Nvarchar 电子邮箱 客户地址 Nvarchar 客户地址 具体位置 Nvarchar 具体位置 产权性质 Nvarchar 产权性质 最低购价 Money 最低购价 最高购价 Money 最高购价 最小面积 Char 最小面颊 最大面积 Char 最大面积 委托日期 Datetime 委托日期 截止日期 Datetime 截止日期 录入人员 Nvarchar 录入人员 当前状态 Char 当前状态 三、行为设计 中

15、国石油大学数据库课程设计 8（一）建立数据库、数据表、视图、索引 1、建立数据库 CREATE DATABASE 房屋中介管理系统 ON(NAME=N房屋中介管理系统_data,FILENAME=NF:房屋中介管理系统房屋中介管理系统_data.mdf,SIZE=2,MAXSIZE=50,FILEGROWTH=10%)LOG ON(NAME=N房屋中介管理系统_data_log,FILENAME=NF:房屋中介管理系统房屋中介管理系统_data_log.ldf,SIZE=2,MAXSIZE=50,FILEGROWTH=10%)COLLATE Chinese_PRC_CI_AS GO 2、建立数

16、据表（1）房源信息表的建立：CREATE TABLE dbo.房源信息表(编号 varchar(4)COLLATE Chinese_PRC_CI_AS NOT NULL,城区 nvarchar(35)COLLATE Chinese_PRC_CI_AS NOT NULL,环境 nvarchar(20)COLLATE Chinese_PRC_CI_AS NULL,付款 nchar(10)COLLATE Chinese_PRC_CI_AS NULL,来源 nvarchar(32)COLLATE Chinese_PRC_CI_AS NULL,委托 nchar(20)COLLATE Chinese_PR

17、C_CI_AS NOT NULL,产权 nchar(15)COLLATE Chinese_PRC_CI_AS NOT NULL,状态 nchar(5)COLLATE Chinese_PRC_CI_AS NOT NULL )ON PRIMARY GO（2）客源信息表的建立：if exists(select*from dbo.sysobjects where id=object_id(Ndbo.客源信息表)and OBJECTPROPERTY(id,NIsUserTable)=1)drop table dbo.客源信息表 GO CREATE TABLE dbo.客源信息表(客户编号 char(6)

18、COLLATE Chinese_PRC_CI_AS NOT NULL,客户姓名 nvarchar(10)COLLATE Chinese_PRC_CI_AS NULL,客户电话 char(12)COLLATE Chinese_PRC_CI_AS NULL,电子邮箱 char(25)COLLATE Chinese_PRC_CI_AS NULL,客户地址 nvarchar(30)COLLATE Chinese_PRC_CI_AS NULL,具体位置 nvarchar(50)COLLATE Chinese_PRC_CI_AS NULL,中国石油大学数据库课程设计 9 产权性质 nvarchar(15)

19、COLLATE Chinese_PRC_CI_AS NULL,最低购价 money NULL,最高购价 money NULL,最小面积 int NULL,最大面积 int NULL,委托日期 datetime NULL,截止日期 datetime NULL,录入人员 nvarchar(6)COLLATE Chinese_PRC_CI_AS NULL,当前状态 nchar(2)COLLATE Chinese_PRC_CI_AS NOT NULL )ON PRIMARY GO（3）职员信息表的建立：if exists(select*from dbo.sysobjects where id=obje

20、ct_id(Ndbo.FK_操作员信息表_账号_681373AD)and OBJECTPROPERTY(id,NIsForeignKey)=1)ALTER TABLE dbo.操作员信息表 DROP CONSTRAINT FK_操作员信息表_账号_681373AD GO if exists(select*from dbo.sysobjects where id=object_id(Ndbo.职员信息表)and OBJECTPROPERTY(id,NIsUserTable)=1)drop table dbo.职员信息表 GO CREATE TABLE dbo.职员信息表(职员编号 char(3)

21、COLLATE Chinese_PRC_CI_AS NOT NULL,姓名 nvarchar(5)COLLATE Chinese_PRC_CI_AS NULL,性别 char(2)COLLATE Chinese_PRC_CI_AS NULL,身份证号 char(18)COLLATE Chinese_PRC_CI_AS NOT NULL,学历 nchar(6)COLLATE Chinese_PRC_CI_AS NULL,职务 nvarchar(15)COLLATE Chinese_PRC_CI_AS NULL,所在部门 nvarchar(20)COLLATE Chinese_PRC_CI_AS

22、NULL,出生日期 datetime NULL,联系电话 char(12)COLLATE Chinese_PRC_CI_AS NULL,家庭住址 nvarchar(25)COLLATE Chinese_PRC_CI_AS NULL,中国石油大学数据库课程设计 1 0 电子邮箱 nvarchar(20)COLLATE Chinese_PRC_CI_AS NULL,备注 nvarchar(50)COLLATE Chinese_PRC_CI_AS NULL,简历 nvarchar(50)COLLATE Chinese_PRC_CI_AS NULL )ON PRIMARY GO（4）售房信息表的建立：

23、if exists(select*from dbo.sysobjects where id=object_id(Ndbo.售房信息表)and OBJECTPROPERTY(id,NIsUserTable)=1)drop table dbo.售房信息表 GO CREATE TABLE dbo.售房信息表(售房编号 char(3)COLLATE Chinese_PRC_CI_AS NOT NULL,编号 varchar(4)COLLATE Chinese_PRC_CI_AS NOT NULL,具体位置 nvarchar(50)COLLATE Chinese_PRC_CI_AS NULL,配套环境

24、nvarchar(20)COLLATE Chinese_PRC_CI_AS NULL,房 int NULL,厅 int NULL,土地性质 nvarchar(12)COLLATE Chinese_PRC_CI_AS NULL,产权性质 nvarchar(15)COLLATE Chinese_PRC_CI_AS NULL,价格 money NULL,权证名称 nvarchar(35)COLLATE Chinese_PRC_CI_AS NULL,权证编号 char(6)COLLATE Chinese_PRC_CI_AS NOT NULL,业主姓名 nchar(6)COLLATE Chinese_P

25、RC_CI_AS NULL,业主电话 char(12)COLLATE Chinese_PRC_CI_AS NULL,联系人 nchar(6)COLLATE Chinese_PRC_CI_AS NULL,联系电话 char(12)COLLATE Chinese_PRC_CI_AS NULL,委托日期 datetime NULL,截止日期 datetime NULL,录入人员 nvarchar(6)COLLATE Chinese_PRC_CI_AS NULL,当前状态 nchar(2)COLLATE Chinese_PRC_CI_AS NOT NULL,备注 nvarchar(50)COLLATE

26、 Chinese_PRC_CI_AS NULL )ON PRIMARY GO（5）租房信息表的建立：中国石油大学数据库课程设计 1 1 if exists(select*from dbo.sysobjects where id=object_id(Ndbo.租房信息表)and OBJECTPROPERTY(id,NIsUserTable)=1)drop table dbo.租房信息表 GO CREATE TABLE dbo.租房信息表(租房编号 char(3)COLLATE Chinese_PRC_CI_AS NOT NULL,编号 varchar(4)COLLATE Chinese_PRC_

27、CI_AS NOT NULL,具体位置 nvarchar(50)COLLATE Chinese_PRC_CI_AS NULL,配套环境 nvarchar(20)COLLATE Chinese_PRC_CI_AS NULL,房 int NULL,厅 int NULL,出租价格 money NULL,出租底价 money NULL,业主姓名 nvarchar(6)COLLATE Chinese_PRC_CI_AS NULL,业主电话 char(12)COLLATE Chinese_PRC_CI_AS NULL,联系人 nchar(6)COLLATE Chinese_PRC_CI_AS NULL,联

28、系电话 char(12)COLLATE Chinese_PRC_CI_AS NULL,委托日期 datetime NULL,截止日期 datetime NULL,录入人员 nvarchar(6)COLLATE Chinese_PRC_CI_AS NULL,当前状态 nchar(2)COLLATE Chinese_PRC_CI_AS NOT NULL,备注 nvarchar(50)COLLATE Chinese_PRC_CI_AS NULL )ON PRIMARY GO（6）操作员信息表的建立：if exists(select*from dbo.sysobjects where id=objec

29、t_id(Ndbo.操作员信息表)and OBJECTPROPERTY(id,NIsUserTable)=1)drop table dbo.操作员信息表 GO CREATE TABLE dbo.操作员信息表(账号 char(3)COLLATE Chinese_PRC_CI_AS NOT NULL,密码 varchar(8)COLLATE Chinese_PRC_CI_AS NOT NULL )ON PRIMARY 中国石油大学数据库课程设计 1 2 GO 3、建立视图 （1）用于查询可售房源视图定义如下：CREATE VIEW 可售房源视图 AS SELECT 编号,具体位置,配套环境,房,厅

30、,土地性质,产权性质,价格,权证名称,权证编号,业主姓名,业主电话,联系人,委托日期,截止日期,录入人员,当前状态 FROM 售房信息表 WHERE 当前状态=待售 （2）用于查询可租房源视图定义如下：CREATE VIEW 可租房源视图 AS SELECT 编号,具体位置,配套环境,房,厅,出租价格,出租底价,业主姓名,业主电话,联系人,联系电话,委托日期,截止日期,录入人员,当前状态 FROM 租房信息表 WHERE 当前状态=待租 （3）用于个房态统计视图定义如下：create view 各房态统计视图 as select sum(待售房数)as 待售房数,sum(已售房数)as 已售房

31、数,sum(待租房数)as 待租房数,sum(已租房数)as 已租房数 from 待售房统计视图 full outer join 已售房统计视图 on 待售房统计视图.编号=已售房统计视图.编号 full outer join 待租房统计视图 on 待租房统计视图.编号=待售房统计视图.编号 full outer join 已租房统计视图 on 待租房统计视图.编号=已租房统计视图.编号 （4）用于已售房统计视图定义如下：create view 已售房统计视图 as select 编号,count(状态)as 已售房数 from 房源信息表 group by 状态,编号 中国石油大学数据库课程

32、设计 1 3 having 状态=已售l （5）用于已租房统计视图定义如下：create view 已租房统计视图 as select 编号,count(状态)as 已租房数 from 房源信息表 group by 状态,编号 having 状态=已租（6）用于待售房统计视图定义如下：create view 待售房统计视图 as select 编号,count(状态)as 待售房数 from 房源信息表 group by 状态,编号 having 状态=待售（7）用于待租房统计视图定义如下：create view 待租房统计视图 as select 编号,count(状态)as 待租房数 fr

33、om 房源信息表 group by 状态,编号 having 状态=待租（8）用于求购客源视图定义如下：CREATE VIEW 求购客源视图 AS SELECT 客户编号,客户姓名,电话,电子邮箱,客户地址,具体位置,产权性质,最低购价,最高购价,最小面积,最大面积,委托日期,截止日期,录入人员,当前状态 FROM 客源信息表 WHERE 当前状态=待售 4、建立索引 create clustered index PK_售房信息表_489AC854 on 售房信息表(售房编号);create clustered index PK_租房信息表_503BEA1C on 租房信息表(租房编号);5、

34、建立触发器（1）当删除房源信息表中某一房源信息信息时，触发客源信息表，提示无法删除 create trigger T 删除房源信息表记录 on dbo.房源信息表 中国石油大学数据库课程设计 1 4 for delete as if(select count(*)from deleted inner join 客源信息表 on deleted.编号=left(客源信息表.客户编号,4)0 begin raiserror(客源信息表存在此编号，禁止删除！,10,1)rollback transaction end（2）当修改售房信息表时，同时修改房源信息表的当前状态属性 create trigg

35、er T 修改售房信息表记录 on dbo.售房信息表 for update as if(select count(*)from inserted)0 begin update 房源信息表 set 状态=(select 当前状态 from inserted)where(编号)=(select 编号 from deleted)end（二）数据入库 系统包括图书基本信息管理、读者基本信息管理、管理员信息管理、借阅信息管理、查询信息管理等四大功能模块，共有 8 张基本表,采用事先在 Excel 中录入数据,然后使用 SQL Server 2000 数据导入/导出向导功能,直接将数据导入到相应的基本表中。（三）创建各个功能的存储过程 系统共创建了 10 个存储过程，具体列表如下：表 3.1 创建的存储过程列表：编号 存储过程名称 定义 作用 P-1 房源信息_存储过程 详见附录1-1 按输入的房源状态查找房源 P-2 职员基本信息_存储过程 详见附录1-2 按输入的职员名称查找职员信息