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 房源信息表编号 房屋编号Varchar notnull城区 房屋所在城区Nvarchar notnull环境 房屋所在的环境Nvarchar付款 Nchar 房屋付款来源 房屋来源Nvarchar委托 房屋委托Nchar Notnull产权 房屋产权Nchar Notnull状态
5、房屋状态Nchar Notnull表 2.2 客源信息表客户编号 客户编号Char notnull客户姓名 客户姓名Nvarchar notnull客户电话 客户电话Char电子邮箱 Char 客户邮箱客户地址 客户地址Nvarchar具体位置 Nvarchar 具体位置产权性质 产权性质Nvarchar最低购价 Money 最低购价最高购价 最高购价Money最小面积 Int 最小面积最大面积 最大面积Int委托日期 Datetime 委托日期截止日期 截止日期Datetime录入人员 录入人员Nvarchar当前状态 当前状态Nchar Notnull表 2.3 职员信息表数据类型 可否为
6、空Char notnull姓名 职员姓名Nvarchar性别 职员性别Char Notnull身份证号 职员身份证号Char notnull学历 Nchar 职员学历职务 职员职务Nvarchar所在部门 Nvarchar 职员所在部门出生日期 职员出生日期Datetime联系电话 Char 职员联系电话家庭住址 职员家庭住址Nvarchar电子邮箱 Nvarchar 职员电子邮箱备注 职员备注Nvarchar简历 Nvarchar 职员简历表 2.4 操作员信息表数据类型 可否为空账号 账号Char notnull密码 密码varchar notnull表 2.5 售房信息表售房编号 售房编
7、号Char notnull编号 编号Varchar notnull具体位置 具体位置Nvarchar配套环境 Nvarchar 配套环境房 房间Int厅 Int 客厅土地性质 土地性质Nvarchar产权性质 Nvarchar 产权性质价格 价格Money产权名称 Nvarchar 产权名称产权编号 产权编号Char业主姓名 业主姓名Nchar Notnull业主电话 业主电弧Char委托日期 委托日期Datetime截止日期 Datetime 截止日期录入人员 录入人员Nvarchar当前状态 Char 当前状态备注 备注Nvarchar2.6列名 数据类型 可否为空 说明Char notn
8、ull编号 Char 编号notnullNvarchar配套环境 Nvarchar 配套环境房 房间Int厅 Int 客厅出租价格 出租价格Money出租底价 Money 出租底价业主姓名 业主姓名Nvarchar业主电话 Char 业主电话联系人 联系人Nchar联系电话 Char 联系电话委托日期 委托日期Datetime截止日期 Datetime 截止日期录入人员 录入人员Nvarchar当前状态 当前状态Char Notnull备注 备注Nvarchar（二）用户子模式定义表 2.7 用户子模式定义 作用（共性：提供数据保密和安全保护机编号 用子模式(View): 制）可售房源视图 便
9、于查询可售房源 V-1可租房源视图 便于查询可租房源 V-2各房态统计视图 便于查看所有房屋的状态 V-3已售房统计视图 便于查看已售房屋信息 V-4已租房统计视图 便于查看已租房屋信息 V-5待售房统计视图 便于查看待售房屋信息 V-6待租房统计视图 便于查看待租房屋信息 V-7求购客源视图 便于查看客源信息 V-82.8列名 数据类型 可否为空 说明编号Char notnull具体位置 Nvarchar 具体位置配套环境 配套环境Nvarchar房 Int 房间厅 客厅Int土地性质 Nvarchar l 土地性质产权性质 Nvarchar 产权性质价格 价格Money权证名称 Nvarc
10、har 权证名称权证编号 权证编号Char业主姓名 业主姓名Nvarchar Notnull业主电话 业主电弧Char联系人 联系人Nchar联系电话 Char 联系电话委托日期 委托日期Datetime截止日期 Datetime 截止日期录入人员 录入人员Nvarchar当前状态 Char 当前装他表 2.9 可租房源视图列名 可否为空编号 notnull 编号Char具体位置 具体位置Nvarchar配套环境 Nvarchar 配套环境房 房间Int厅 Int 客厅出租价格 出租价格Money出租底价 Money 出租底价业主姓名 业主姓名Nvarchar业主电话 Char 业主电话联系人
11、 联系人Nvarchar联系电话 Char 联系电话委托日期 委托日期Datetime截止日期 Datetime 截止日期录入人员 录入人员Nvarchar当前状态 Char 当前状态2.10列名 数据类型 可否为空待售房数 待售房数Int已售房数 Int 已售房数待租房数 待租房数Int已租房数 Int 已租房数表 2.11 已售房统计视图编号 Char notnull 编号已售房数 已售房数Int2.12数据类型编号 Char notnull 编号已租房数 已租房数Int2.13数据类型编号 Char notnull 编号待售房数 待售房数Int表 2.14 待租房统计视图编号 Char
12、notnull 编号待租房数 Int待租房数表 2.15 求购客源视图可否为空客户编号 Char notnull 客户编号客户姓名 客户姓名Nvarchar客户电话 Char 客户电话电子邮箱 电子邮箱Nvarchar客户地址 Nvarchar 客户地址具体位置 具体位置Nvarchar产权性质 Nvarchar 产权性质最低购价 最低购价Money最高购价 Money 最高购价最小面积 最小面颊Char最大面积 Char 最大面积委托日期 委托日期Datetime截止日期 Datetime 截止日期录入人员 录入人员Nvarchar当前状态 Char 当前状态二、行为设计(一)建立数据库、数
13、据表、视图、索引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,FILEGROWTH=10%)COLLATEChinese_PRC_CI_ASGO2、建立
14、数据表(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,委托nchar(20)COLLATEChinese_PRC_CI_ASNOTNULL,产权nch
15、ar(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)COLLATEChinese_PRC_CI_ASNOTNULL,客户姓名nvarc
16、har(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,最低购价moneyNULL,最高购价moneyNULL,最小面积intNULL,最大面积int
17、NULL,委托日期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,NIsForeignKey)=1)ALTERTABLEdbo. 操 作 员 信 息 表 DROPCON
18、STRAINTFK_ 操 作 员 信 息 表 账 号_681373ADGOifexists(select*fromdbo.sysobjectswhereid=object_id(Ndbo.职员信息表)andOBJECTPROPERTY(id,NIsUserTable)=1)droptabledbo.职员信息表GOCREATETABLEdbo.职员信息表(职员编号char(3)COLLATEChinese_PRC_CI_ASNOTNULL,姓名nvarchar(5)COLLATEChinese_PRC_CI_ASNULL,性另Jchar(2)COLLATEChinese_PRC_CI_ASNUL
19、L,身份证号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)COLLATEChinese_PRC_CI_ASNULL,电子邮箱nvarchar(20)COLLATEC
20、hinese_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.售房信息表GOCREATETABLEdbo.售房信息表( 售 房 编 号 char(3)COLLATEChines
21、e_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,价格moneyNULL, 权 证 名 称 n
22、varchar(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, 录
23、入 人 员 nvarchar(6)COLLATEChinese_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.
24、租房信息表(ffl 房编号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,出租价格moneyNULL,出租底价moneyNULL,业主姓名nvarchar(6)COLLATEChinese_PRC_CI_ASNULL,业主电话char(12)COLLATEChine
25、se_PRC_CI_ASNULL,联系 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(sel
26、ect*fromdbo.sysobjectswhereid=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 编号，具体位置，配套环境，房，厅，
27、土地性质，产权性质,价格，权证名称，权证编号，业主姓名，业主电话，联系人，联系电话，委托日期，截止日期，录入人员，当前状态FROM 售房信息表WHERE 当前状态=待售(2)用于查询可租房源视图定义如下：CREATEVIEW 可租房源视图ASSELECT 编号，具体位置，配套环境，房，厅，出租价格，出租底价，业主姓名，业主电话，联系人，联系电话，委托日期，截止日期，录入人员，当前状态FROM 租房信息表WHERE 当前状态=待租(3)用于个房态统计视图定义如下：createview 各房态统计视图 asselectsum(待售房数)as 待售房数,sum(已售房数)as 已售房数，sum(待租
28、房数)as 待租房数,sum(已租房数)as 已租房数from 待售房统计视图 fullouterjoin 已售房统计视图on 待售房统计视图.编号=已售房统计视图.编号fullouterjoin 待租房统计视图on 待租房统计视图.编号=待售房统计视图.编号fullouterjoin 已租房统计视图on 待租房统计视图.编号=已租房统计视图.编号(4)用于已售房统计视图定义如下：createview 已售房统计视图 asselect 编号,count（状态）as 已售房数from 房源信息表groupby 状态，编号having 状态=已售1（5）用于已租房统计视图定义如下：createvi
29、ew 已租房统计视图 asselect 编号,count（状态）as 已租房数from 房源信息表groupby 状态，编号having 状态=已租（6）用于待售房统计视图定义如下：createview 待售房统计视图 asselect 编号,count（犬态）as 待售房数from 房源信息表groupby 状态，编号having 状态=待售（7）用于待租房统计视图定义如下：createview 待租房统计视图 asselect 编号,count（犬态）as 待租房数from 房源信息表groupby 状态，编号having 状态=待租（8）用于求购客源视图定义如下：CREATEVIEW 求
30、购客源视图ASSELECT 客户编号，客户姓名，客户电话，电子邮箱，客户地址，具体位置，产权性质，最低购价，最高购价，最小面积，最大面积，委托日期，截止日期，录入人员，当前状态FROM 客源信息表WHERE 当前状态=待售4、建立索引createclusteredindexPK_售房信息表_489AC854on 售房信息表（售房编号）;createclusteredindexPK_租房信息表_503BEA1Con 租房信息表（租房编号）;5、建立触发器(1)当删除房源信息表中某一房源信息信息时，触发客源信息表，提示无法删除createtrigger 硼除房源信息表记录ondbo 房源信息表 f
31、ordeleteasif(selectcount(*)fromdeletedinnerjoin源信息表 ondelete 断号=left(客源信息表.客户编号,4)0beginraiserror(客源信息表存在此编号，禁止删除！,10,1)rollbacktransactionend(2)当修改售房信息表时，同时修改房源信息表的当前状态属性createtrigger 脩改售房信息表记录ondbo 售房信息表forupdateasif(selectcount(*)frominserted)0beginupdate 房源信息表 set 状态=(select 当前状态 frominserted)wh
32、ere 编号)=(select 编号 fromdeleted)end(二)数据入库系统包括图书基本信息管理、读者基本信息管理、管理员信息管理、借阅信息管理、查询信息管理等四大功能模块，共有 8 张基本表，采用事先在 Excel 中录入数据然后使用SQLServer2000 数据导入/导出向导功能,直接将数据导入到相应的基本表中。(三)创建各个功能的存储过程系统共创建了 10 个存储过程，具体列表如下：表 3.1 创建的存储过程列表：编存储过程名称定义作用号P-1 房源信息存储过程详见附录 1-1 按输入的房源状态查找房源P-2 职员基本信息存储过程详见附录 1-2 按输入的职员名称查找职员信息
