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

《中国石油大学数据库课程设计.docx》由会员分享，可在线阅读，更多相关《中国石油大学数据库课程设计.docx（19页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

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

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

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

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

5、ull房屋委托产权NcharNot null 房屋产权状态NcharNot null房屋状态表2.2 客源信息表列名数据类型可否为空说明客户编号Charnot null客户编号客户姓名Nvarcharnot null客户姓名客户电话Char客户电话电子邮箱Char客户邮箱客户地址Nvarchar客户地址具体位置Nvarchar具体位置产权性质Nvarchar产权性质最低购价Money最低购价最高购价Money最高购价最小面积Int最小面积最大面积Int最大面积委托日期Datetime委托日期截止日期Datetime截止日期录入人员Nvarchar录入人员当前状态NcharNot null当前状

6、态表2.3 职员信息表列名数据类型可否为空说明职员编号Charnot null职员编号姓名Nvarchar职员姓名性别CharNot null职员性别身份证号Charnot null职员身份证号学历Nchar职员学历职务Nvarchar职员职务所在部门Nvarchar职员所在部门出生日期Datetime职员出生日期联系电话Char职员联系电话家庭住址Nvarchar职员家庭住址电子邮箱Nvarchar职员电子邮箱备注Nvarchar职员备注简历Nvarchar职员简历表2.4 操作员信息表列名数据类型可否为空说明账号Charnot null账号密码varcharnot null密码表2.5 售

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

8、编号Charnot null租房编号编号Charnot null编号具体位置Nvarchar具体位置配套环境Nvarchar配套环境房Int房间厅Int客厅出租价格Money出租价格出租底价Money 出租底价业主姓名Nvarchar业主姓名业主电话Char业主电话联系人Nchar联系人联系电话Char联系电话委托日期Datetime委托日期截止日期Datetime截止日期录入人员Nvarchar录入人员当前状态CharNot null当前状态备注Nvarchar备注（二）用户子模式定义表2.7 用户子模式定义编号用户子模式(View)作用(共性：提供数据保密和安全保护机制)V1可售房源视图便

9、于查询可售房源V2可租房源视图便于查询可租房源V3各房态统计视图便于查看所有房屋的状态V4已售房统计视图便于查看已售房屋信息V5已租房统计视图便于查看已租房屋信息V6待售房统计视图便于查看待售房屋信息V7待租房统计视图便于查看待租房屋信息V8求购客源视图便于查看客源信息表2.8 可售房源视图列名数据类型可否为空说明编号Charnot null房屋编号具体位置Nvarchar具体位置配套环境Nvarchar配套环境房Int房间厅Int客厅土地性质Nvarcharl土地性质产权性质Nvarchar产权性质价格Money价格权证名称Nvarchar权证名称权证编号Char权证编号业主姓名Nvarch

10、arNot null业主姓名业主电话Char业主电弧联系人Nchar联系人联系电话Char联系电话委托日期Datetime委托日期截止日期Datetime截止日期录入人员 Nvarchar 录入人员当前状态Char当前装他表2.9 可租房源视图列名数据类型可否为空说明编号Charnot null编号具体位置Nvarchar具体位置配套环境Nvarchar配套环境房Int房间厅Int客厅出租价格Money出租价格出租底价Money出租底价业主姓名Nvarchar业主姓名业主电话Char业主电话联系人Nvarchar 联系人联系电话Char联系电话委托日期Datetime委托日期截止日期Datet

11、ime截止日期录入人员Nvarchar录入人员当前状态Char当前状态表2.10 各房态统计视图列名数据类型可否为空说明待售房数Int待售房数已售房数Int已售房数待租房数Int待租房数已租房数Int、已租房数表2.11 已售房统计视图列名数据类型可否为空说明编号Charnot null编号已售房数Int已售房数表2.12 已租房统计视图列名数据类型可否为空说明编号Charnot null编号已租房数Int已租房数表2.13 待售房统计视图列名数据类型可否为空说明编号Charnot null编号待售房数Int待售房数表2.14 待租房统计视图列名数据类型可否为空说明编号Charnot null

12、编号待租房数Int待租房数表2.15 求购客源视图列名数据类型可否为空说明客户编号Charnot null客户编号客户姓名Nvarchar客户姓名客户电话Char客户电话电子邮箱Nvarchar电子邮箱客户地址Nvarchar客户地址具体位置Nvarchar具体位置产权性质Nvarchar产权性质最低购价Money 最低购价最高购价Money最高购价最小面积Char最小面颊最大面积Char最大面积委托日期Datetime委托日期截止日期Datetime截止日期录入人员Nvarchar录入人员当前状态Char当前状态三、行为设计（一）建立数据库、数据表、视图、索引1、建立数据库CREATE DA

13、TABASE 房屋中介管理系统 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_ASGO2、建立数据表（1）房源信息表的建立：CREA

14、TE 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

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

16、 (客户编号 char (6) 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 ,产

17、权性质 nvarchar (15) 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 PRIMARYGO（3）职员信息表的建立：if exists (select *

18、from dbo.sysobjects where id = object_id(Ndbo.FK_操作员信息表_账号_681373AD) and OBJECTPROPERTY(id, NIsForeignKey) = 1)ALTER TABLE dbo.操作员信息表 DROP CONSTRAINT FK_操作员信息表_账号_681373ADGOif exists (select * from dbo.sysobjects where id = object_id(Ndbo.职员信息表) and OBJECTPROPERTY(id, NIsUserTable) = 1)drop table db

19、o.职员信息表GOCREATE TABLE dbo.职员信息表 (职员编号 char (3) 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 Chin

20、ese_PRC_CI_AS NULL ,所在部门 nvarchar (20) COLLATE Chinese_PRC_CI_AS NULL ,出生日期 datetime NULL ,联系电话 char (12) COLLATE Chinese_PRC_CI_AS NULL ,家庭住址 nvarchar (25) COLLATE Chinese_PRC_CI_AS NULL ,电子邮箱 nvarchar (20) COLLATE Chinese_PRC_CI_AS NULL ,备注 nvarchar (50) COLLATE Chinese_PRC_CI_AS NULL ,简历 nvarchar

21、 (50) COLLATE Chinese_PRC_CI_AS NULL ) ON PRIMARYGO（4）售房信息表的建立：if exists (select * from dbo.sysobjects where id = object_id(Ndbo.售房信息表) and OBJECTPROPERTY(id, NIsUserTable) = 1)drop table dbo.售房信息表GOCREATE TABLE dbo.售房信息表 (售房编号 char (3) COLLATE Chinese_PRC_CI_AS NOT NULL ,编号 varchar (4) COLLATE Chin

22、ese_PRC_CI_AS NOT NULL ,具体位置 nvarchar (50) COLLATE Chinese_PRC_CI_AS NULL ,配套环境 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 Chines

23、e_PRC_CI_AS NULL ,权证编号 char (6) COLLATE Chinese_PRC_CI_AS NOT NULL ,业主姓名 nchar (6) COLLATE Chinese_PRC_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 ,录入人员

24、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 PRIMARYGO（5）租房信息表的建立：if exists (select * from dbo.sysobjects where id = object_id(Ndbo.租房信息表) and OBJECTPROPERTY(id, NIsUserTable) = 1)drop table dbo.租

25、房信息表GOCREATE 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 ,配套环境 nvarchar (20) COLLATE Chinese_PRC_CI_AS NULL ,房 int NULL ,厅 int NULL ,出租价格 money NULL ,出租底价 money NULL ,业主姓名 nvar

26、char (6) COLLATE Chinese_PRC_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 Chines

27、e_PRC_CI_AS NOT NULL ,备注 nvarchar (50) COLLATE Chinese_PRC_CI_AS NULL ) ON PRIMARYGO（6）操作员信息表的建立：if exists (select * from dbo.sysobjects where id = object_id(Ndbo.操作员信息表) and OBJECTPROPERTY(id, NIsUserTable) = 1)drop table dbo.操作员信息表GOCREATE TABLE dbo.操作员信息表 (账号 char (3) COLLATE Chinese_PRC_CI_AS NO

28、T NULL ,密码 varchar (8) COLLATE Chinese_PRC_CI_AS NOT NULL ) ON PRIMARYGO3、建立视图（1）用于查询可售房源视图定义如下： CREATE VIEW 可售房源视图ASSELECT 编号, 具体位置, 配套环境, 房, 厅, 土地性质, 产权性质, 价格,权证名称, 权证编号, 业主姓名, 业主电话, 联系人, 联系电话, 委托日期, 截止日期, 录入人员, 当前状态FROM 售房信息表WHERE 当前状态 = 待售（2）用于查询可租房源视图定义如下：CREATE VIEW 可租房源视图ASSELECT 编号, 具体位置,配套环

29、境, 房, 厅, 出租价格,出租底价, 业主姓名, 业主电话, 联系人, 联系电话, 委托日期, 截止日期, 录入人员, 当前状态FROM 租房信息表WHERE 当前状态 = 待租（3）用于个房态统计视图定义如下：create view 各房态统计视图 asselect sum(待售房数) as 待售房数,sum(已售房数) as 已售房数,sum(待租房数) as 待租房数,sum(已租房数) as 已租房数from 待售房统计视图 full outer join 已售房统计视图on 待售房统计视图.编号 = 已售房统计视图.编号full outer join 待租房统计视图 on 待租房统

30、计视图.编号 = 待售房统计视图.编号full outer join 已租房统计视图on 待租房统计视图.编号 = 已租房统计视图.编号（4）用于已售房统计视图定义如下：create view 已售房统计视图 asselect 编号,count(状态) as 已售房数from 房源信息表group by 状态,编号having 状态 = 已售l（5）用于已租房统计视图定义如下：create view 已租房统计视图 asselect 编号,count(状态) as 已租房数from 房源信息表group by 状态,编号having 状态 = 已租（6）用于待售房统计视图定义如下： creat

31、e view 待售房统计视图 asselect 编号,count(状态) as 待售房数from 房源信息表group by 状态,编号having 状态 = 待售（7）用于待租房统计视图定义如下： create view 待租房统计视图 asselect 编号,count(状态) as 待租房数from 房源信息表group by 状态,编号having 状态 = 待租（8）用于求购客源视图定义如下： CREATE VIEW 求购客源视图ASSELECT 客户编号, 客户姓名, 客户电话, 电子邮箱, 客户地址, 具体位置, 产权性质, 最低购价, 最高购价, 最小面积, 最大面积, 委托日

32、期, 截止日期, 录入人员, 当前状态FROM 客源信息表WHERE 当前状态 = 待售4、建立索引create clustered index PK_售房信息表_489AC854 on 售房信息表(售房编号);create clustered index PK_租房信息表_503BEA1C on 租房信息表(租房编号);5、建立触发器（1）当删除房源信息表中某一房源信息信息时，触发客源信息表，提示无法删除create trigger T删除房源信息表记录on dbo.房源信息表for deleteasif(select count(*)from deleted inner join 客源信息

33、表on deleted.编号 = left(客源信息表.客户编号,4)0beginraiserror(客源信息表存在此编号，禁止删除！,10,1)rollback transactionend（2）当修改售房信息表时，同时修改房源信息表的当前状态属性create trigger T修改售房信息表记录on dbo.售房信息表for updateasif(select count(*) from inserted )0beginupdate 房源信息表 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按输入的职员名称查找职员信息