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

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

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

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

4、性质，价格 ,产权名称，产权编号，业主姓名,业主电话,联系人，联系电话，委托日期，截止日期，录入人员，当前状态,备注）二、逻辑结构设计关系模式房源信息，客源信息不存在非主属性对主属性的部分函数依赖，也不存在传递函数依赖，已经达到了 3NF,但是购房信息，租房信息中存在着一些数据冗余。（一）数据库模式定义表 2.1 房源信息表编号城区环境付款来源委托产权状态VarcharNvarcharnotnullnotnull房屋编号房屋所在城区房屋所在的环境NvarcharNchar房屋付款房屋来源NotnullNotnullNotnullNvarcharNcharNcharNchar房屋委托房屋产权房屋

5、状态表 2.2 客源信息表客户编号客户姓名客户电话电子邮箱客户地址具体位置产权性质最低购价最高购价最小面积最大面积委托日期截止日期录入人员当前状态CharNvarcharnotnullnotnull客户编号客户姓名客户电话CharChar客户邮箱客户地址NvarcharNvarchar具体位置产权性质NvarcharMoney最低购价最高购价MoneyInt最小面积最大面积IntDatetime委托日期截止日期DatetimeNvarcharNcharNotnull录入人员当前状态表 2.3 职员信息表数据类型可否为空Charnotnull姓名性别身份证号NvarcharCharCharNot

6、nullnotnull职员姓名职员性别职员身份证号学历职务Nchar职员学历职员职务Nvarchar所在部门出生日期联系电话家庭住址电子邮箱备注简历NvarcharDatetime职员所在部门职员出生日期Char职员联系电话职员家庭住址NvarcharNvarchar职员电子邮箱职员备注NvarcharNvarchar职员简历表 2.4 操作员信息表数据类型账号密码可否为空notnullnotnullCharvarchar账号密码表 2.5 售房信息表售房编号编号具体位置配套环境房厅土地性质产权性质价格产权名称产权编号业主姓名业主电话委托日期截止日期录入人员当前状态备注CharVarcharn

7、otnullnotnull售房编号编号具体位置NvarcharNvarchar配套环境房间IntInt客厅土地性质NvarcharNvarchar产权性质价格MoneyNvarchar产权名称产权编号NotnullCharNcharChar业主姓名业主电弧委托日期DatetimeDatetime截止日期录入人员NvarcharChar当前状态备注Nvarchar2.6列名数据类型Char可否为空说明notnullnotnull编号Char编号Nvarchar配套环境房厅出租价格出租底价业主姓名业主电话联系人联系电话委托日期截止日期录入人员当前状态备注Nvarchar配套环境房间IntInt客厅

8、出租价格MoneyMoney出租底价业主姓名NvarcharChar业主电话联系人NcharChar联系电话委托日期DatetimeDatetime截止日期录入人员NotnullNvarcharCharNvarchar当前状态备注（二）用户子模式定义表 2.7 用户子模式定义编号用子模式(View):可售房源视图可租房源视图各房态统计视图已售房统计视图已租房统计视图待售房统计视图待租房统计视图求购客源视图作用（共性：提供数据保密和安全保护机制）便于查询可售房源V-1V-2V-3V-4V-5V-6V-7V-8便于查询可租房源便于查看所有房屋的状态便于查看已售房屋信息便于查看已租房屋信息便于查看待

9、售房屋信息便于查看待租房屋信息便于查看客源信息2.8列名编号具体位置配套环境房厅数据类型Char可否为空说明notnullNvarchar具体位置配套环境NvarcharInt房间客厅Int土地性质Nvarcharl土地性质产权性质价格权证名称权证编号业主姓名业主电话联系人联系电话委托日期截止日期录入人员当前状态Nvarchar产权性质价格MoneyNvarchar权证名称权证编号NotnullCharNvarcharChar业主姓名业主电弧联系人NcharChar联系电话委托日期DatetimeDatetime截止日期录入人员NvarcharChar当前装他表 2.9 可租房源视图列名编号具

10、体位置配套环境房厅出租价格出租底价业主姓名业主电话联系人联系电话委托日期截止日期录入人员当前状态Char可否为空notnull编号具体位置NvarcharNvarchar配套环境房间IntInt客厅出租价格MoneyMoney出租底价业主姓名NvarcharChar业主电话联系人NvarcharChar联系电话委托日期DatetimeDatetime截止日期录入人员NvarcharChar当前状态2.10列名待售房数已售房数待租房数已租房数数据类型可否为空待售房数IntInt已售房数待租房数IntInt已租房数表 2.11 已售房统计视图编号已售房数Charnotnull编号已售房数Int2.

11、12数据类型编号已租房数Charnotnull编号已租房数Int2.13数据类型编号待售房数Charnotnull编号待售房数Int表 2.14 待租房统计视图编号Charnotnull编号待租房数待租房数Int表 2.15 求购客源视图可否为空客户编号客户姓名客户电话电子邮箱客户地址具体位置产权性质最低购价最高购价最小面积最大面积委托日期截止日期录入人员当前状态Char当前状态Charnotnull客户编号客户姓名NvarcharChar客户电话电子邮箱NvarcharNvarchar客户地址具体位置NvarcharNvarchar产权性质最低购价MoneyMoney最高购价最小面颊Char

12、Char最大面积委托日期DatetimeDatetime截止日期录入人员Nvarchar二、行为设计(一)建立数据库、数据表、视图、索引1、建立数据库CREATEDATABASE 房 屋 中 介 管 理 系 统 ON(NAME=N 房 屋 中 介 管 理 系 统data,FILENAME=房屋中介管理系统房屋中介管理系统_data.mdf,SIZE=2,MAXSIZE=50,FILEGROWTH=10%)LOGON(NAME=N 房 屋 中 介 管 理 系 统_data_log,FILENAME=房屋中介管理系统房屋中介管理系统_data_log.ldf,SIZE=2,MAXSIZE=50,F

13、ILEGROWTH=10%)COLLATEChinese_PRC_CI_ASGO2、建立数据表(1)房源信息表的建立：CREATETABLEdbo.房源信息表(编号varchar(4)COLLATEChinese_PRC_CI_ASNOTNULL,城区nvarchar(35)COLLATEChinese_PRC_CI_ASNOTNULL,环境nvarchar(20)COLLATEChinese_PRC_CI_ASNULL,付款nchar(10)COLLATEChinese_PRC_CI_ASNULL,来源nvarchar(32)COLLATEChinese_PRC_CI_ASNULL,委托nc

14、har(20)COLLATEChinese_PRC_CI_ASNOTNULL,产权nchar(15)COLLATEChinese_PRC_CI_ASNOTNULL,状态nchar(5)COLLATEChinese_PRC_CI_ASNOTNULL)ONPRIMARYGO(2)客源信息表的建立：ifexists(select*fromdbo.sysobjectswhereid=object_id(Ndbo.客表)andOBJECTPROPERTY(id,NIsUserTable)=1)droptabledbo.客源信息表GOCREATETABLEdbo.客源信息表(客户编号char(6)COLL

15、ATEChinese_PRC_CI_ASNOTNULL,客户姓名nvarchar(10)COLLATEChinese_PRC_CI_ASNULL,客户电话char(12)COLLATEChinese_PRC_CI_ASNULL,电子邮箱char(25)COLLATEChinese_PRC_CI_ASNULL,客户地址nvarchar(30)COLLATEChinese_PRC_CI_ASNULL,具体位置nvarchar(50)COLLATEChinese_PRC_CI_ASNULL,产权性质nvarchar(15)COLLATEChinese_PRC_CI_ASNULL,最低购价moneyN

16、ULL,最高购价moneyNULL,源信息最小面积intNULL,最大面积intNULL,委托日期datetimeNULL,截止日期datetimeNULL,录入人员nvarchar(6)COLLATEChinese_PRC_CI_ASNULL,当前状态nchar(2)COLLATEChinese_PRC_CI_ASNOTNULL)ONPRIMARYGO(3)职员信息表的建立：ifexists(select*fromdbo.sysobjectswhereid=object_id(Ndbo.FK_操作员信息表账号_681373AD)andOBJECTPROPERTY(id,NIsForeignK

17、ey)=1)ALTERTABLEdbo. 操 作 员 信 息 表 DROPCONSTRAINTFK_ 操 作 员 信 息 表 账 号_681373ADGOifexists(select*fromdbo.sysobjectswhereid=object_id(Ndbo.职员信息表)andOBJECTPROPERTY(id,NIsUserTable)=1)droptabledbo.职员信息表GOCREATETABLEdbo.职员信息表(职员编号char(3)COLLATEChinese_PRC_CI_ASNOTNULL,姓名nvarchar(5)COLLATEChinese_PRC_CI_ASNU

18、LL,性另Jchar(2)COLLATEChinese_PRC_CI_ASNULL,身份证号char(18)COLLATEChinese_PRC_CI_ASNOTNULL,学历nchar(6)COLLATEChinese_PRC_CI_ASNULL,职务nvarchar(15)COLLATEChinese_PRC_CI_ASNULL,所在部门nvarchar(20)COLLATEChinese_PRC_CI_ASNULL,出生日期datetimeNULL,联系电话char(12)COLLATEChinese_PRC_CI_ASNULL,家庭住址nvarchar(25)COLLATEChines

19、e_PRC_CI_ASNULL,电子邮箱nvarchar(20)COLLATEChinese_PRC_CI_ASNULL,备注nvarchar(50)COLLATEChinese_PRC_CI_ASNULL,简历nvarchar(50)COLLATEChinese_PRC_CI_ASNULL)ONPRIMARYGO(4)售房信息表的建立：ifexists(select*fromdbo.sysobjectswhereid=object_id(Ndbo.售房信息表)andOBJECTPROPERTY(id,NIsUserTable)=1)droptabledbo.售房信息表GOCREATETABL

20、Edbo.售房信息表(售房编号char(3)COLLATEChinese_PRC_CI_ASNOTNULL,号varchar(4)COLLATEChinese_PRC_CI_ASNOTNULL,具体置nvarchar(50)COLLATEChinese_PRC_CI_ASNULL,配套境nvarchar(20)COLLATEChinese_PRC_CI_ASNULL,房intNULL,厅intNULL,土地性质nvarchar(12)COLLATEChinese_PRC_CI_ASNULL,产权质nvarchar(15)COLLATEChinese_PRC_CI_ASNULL,价格moneyN

21、ULL,权证名称nvarchar(35)COLLATEChinese_PRC_CI_ASNULL,权证号char(6)COLLATEChinese_PRC_CI_ASNOTNULL,业主名nchar(6)COLLATEChinese_PRC_CI_ASNULL,业主话char(12)COLLATEChinese_PRC_CI_ASNULL,联Anchar(6)COLLATEChinese_PRC_CI_ASNULL,联系话char(12)COLLATEChinese_PRC_CI_ASNULL,委托日期datetimeNULL,截止日期datetimeNULL,录入人员nvarchar(6)C

22、OLLATEChinese_PRC_CI_ASNULL,当前态nchar(2)COLLATEChinese_PRC_CI_ASNOTNULL,注nvarchar(50)COLLATEChinese_PRC_CI_ASNULL)ONPRIMARYGO(5)租房信息表的建立：ifexists(select*fromdbo.sysobjectswhereid=object_id(Ndbo.租房信表)andOBJECTPROPERTY(id,NIsUserTable)=1)droptabledbo.租房信息表GOCREATETABLEdbo.租房信息表(ffl 房编号char(3)COLLATEChi

23、nese_PRC_CI_ASNOTNULL,编号varchar(4)COLLATEChinese_PRC_CI_ASNOTNULL,编位环性编姓电系电状备息具体位置nvarchar(50)COLLATEChinese_PRC_CI_ASNULL,配套环境nvarchar(20)COLLATEChinese_PRC_CI_ASNULL,房intNULL,厅intNULL,出租价格moneyNULL,出租底价moneyNULL,业主姓名nvarchar(6)COLLATEChinese_PRC_CI_ASNULL,业主电话char(12)COLLATEChinese_PRC_CI_ASNULL,联

24、系 Anchar(6)COLLATEChinese_PRC_CI_ASNULL,联系电话char(12)COLLATEChinese_PRC_CI_ASNULL,委托日期datetimeNULL,截止日期datetimeNULL,录入人员nvarchar(6)COLLATEChinese_PRC_CI_ASNULL,当前状态nchar(2)COLLATEChinese_PRC_CI_ASNOTNULL,备注nvarchar(50)COLLATEChinese_PRC_CI_ASNULL)ONPRIMARYGO(6)操作员信息表的建立：ifexists(select*fromdbo.sysobj

25、ectswhereid=object_id(Ndbo.操作员表)andOBJECTPROPERTY(id,NIsUserTable)=1)droptabledbo.操作员信息表GOCREATETABLEdbo.操作员信息表(账号char(3)COLLATEChinese_PRC_CI_ASNOTNULL,密码varchar(8)COLLATEChinese_PRC_CI_ASNOTNULL)ONPRIMARYGO3、建立视图(1)用于查询可售房源视图定义如下：CREATEVIEW 可售房源视图ASSELECT 编号，具体位置，配套环境，房，厅，土地性质，产权性质,价格，权证名称，权证编号，业主

26、姓名，业主电话，联系人，联系电话，信息委托日期，截止日期，录入人员，当前状态FROM 售房信息表WHERE 当前状态=待售(2)用于查询可租房源视图定义如下：CREATEVIEW 可租房源视图ASSELECT 编号，具体位置，配套环境，房，厅，出租价格，出租底价，业主姓名，业主电话，联系人，联系电话，委托日期，截止日期，录入人员，当前状态FROM 租房信息表WHERE 当前状态=待租(3)用于个房态统计视图定义如下：createview 各房态统计视图 asselectsum(待售房数)as 待售房数,sum(已售房数)as 已售房数，sum(待租房数)as 待租房数,sum(已租房数)as

27、已租房数from 待售房统计视图 fullouterjoin 已售房统计视图on 待售房统计视图.编号=已售房统计视图.编号fullouterjoin 待租房统计视图on 待租房统计视图.编号=待售房统计视图.编号fullouterjoin 已租房统计视图on 待租房统计视图.编号=已租房统计视图.编号(4)用于已售房统计视图定义如下：createview 已售房统计视图 asselect 编号,count（状态）as 已售房数from 房源信息表groupby 状态，编号having 状态=已售1（5）用于已租房统计视图定义如下：createview 已租房统计视图 asselect 编号,

28、count（状态）as 已租房数from 房源信息表groupby 状态，编号having 状态=已租（6）用于待售房统计视图定义如下：createview 待售房统计视图 asselect 编号,count（犬态）as 待售房数from 房源信息表groupby 状态，编号having 状态=待售（7）用于待租房统计视图定义如下：createview 待租房统计视图 asselect 编号,count（犬态）as 待租房数from 房源信息表groupby 状态，编号having 状态=待租（8）用于求购客源视图定义如下：CREATEVIEW 求购客源视图ASSELECT 客户编号，客户姓名

29、，客户电话，电子邮箱，客户地址，具体位置，产权性质，最低购价，最高购价，最小面积，最大面积，委托日期，截止日期，录入人员，当前状态FROM 客源信息表WHERE 当前状态=待售4、建立索引createclusteredindexPK_售房信息表_489AC854on 售房信息表（售房编号）;createclusteredindexPK_租房信息表_503BEA1Con 租房信息表（租房编号）;5、建立触发器(1)当删除房源信息表中某一房源信息信息时，触发客源信息表，提示无法删除createtrigger 硼除房源信息表记录ondbo 房源信息表 fordeleteasif(selectcoun

30、t(*)fromdeletedinnerjoin源信息表 ondelete 断号=left(客源信息表.客户编号,4)0beginraiserror(客源信息表存在此编号，禁止删除！,10,1)rollbacktransactionend(2)当修改售房信息表时，同时修改房源信息表的当前状态属性createtrigger 脩改售房信息表记录ondbo 售房信息表forupdateasif(selectcount(*)frominserted)0beginupdate 房源信息表 set 状态=(select 当前状态 frominserted)where 编号)=(select 编号 fromdeleted)end(二)数据入库系统包括图书基本信息管理、读者基本信息管理、管理员信息管理、借阅信息管理、查询信息管理等四大功能模块，共有 8 张基本表，采用事先在 Excel 中录入数据然后使用SQLServer2000 数据导入/导出向导功能,直接将数据导入到相应的基本表中。(三)创建各个功能的存储过程系统共创建了 10 个存储过程，具体列表如下：表 3.1 创建的存储过程列表：编存储过程名称定义作用号P-1房源信息存储过程详见附录1-1按输入的房源状态查找房源P-2职员基本信息存储过程详见附录1-2按输入的职员名称查找职员信息