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

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

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

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

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

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

6、ll职员性别身份证号Charnot null职员身份证号学历Nchar职员学历职务Nvarchar职员职务所在部门Nvarchar职员所在部门出生日期Datetime职员出生日期联系电话Char职员联系电话家庭住址Nvarchar职员家庭住址电子邮箱Nvarchar职员电子邮箱备注Nvarchar职员备注简历Nvarchar职员简历表2.4操作员信息表数据类型可否为空账号Charnot null账号密码varcharnot null密码表2.5售房信息表售房编号Charnot null售房编号编号Varcharnot null编号具体位置Nvarchar具体位置配套环境Nvarchar配套环境

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

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

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

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

11、售房数2.12数据类型编号Charnot null编号已租房数Int已租房数2.13数据类型编号Charnot null编号待售房数Int待售房数表2.14待租房统计视图编号Charnot null编号待租房数Int待租房数表2.15求购客源视图可否为空客户编号Charnot null客户编号客户姓名Nvarchar客户姓名客户电话Char客户电话电子邮箱Nvarchar电子邮箱客户地址Nvarchar客户地址具体位置Nvarchar具体位置产权性质Nvarchar产权性质最低购价Money最低购价最高购价Money最高购价最小面积Char最小面颊最大面积Char最大面积委托日期Datetim

12、e委托日期截止日期Datetime截止日期录入人员Nvarchar录入人员当前状态Char当前状态二、行为设计(一)建立数据库、数据表、视图、索引1、建立数据库CREATE DATABASE 房屋中介管理系统ON (NAME = N房屋中介管理系 统data, FILENAME = 房屋中介管理系统 房屋中介管理系统_data.mdf , SIZE = 2, MAXSIZE = 50, FILEGROWTH = 10%) LOG ON (NAME = N 房屋中介 管理系统_data_log, FILENAME =房屋中介管理系统房屋中介管理系统_data_log.ldf , SIZE = 2

13、, MAXSIZE = 50, FILEGROWTH = 10%)COLLATE Chinese_PRC_CI_AS GO 2、建立数据表(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 ,来

14、源nvarchar (32) COLLATE Chinese_PRC_CI_AS NULL ,委托nchar (20) COLLATE Chinese_PRC_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 OBJEC

15、TPROPERTY(id, NIsUserTable) = 1)drop table dbo.客源信息表 GOCREATE TABLE dbo.客源信息表(客户编号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 Chi

16、nese_PRC_CI_AS NULL ,具体位置nvarchar (50) COLLATE Chinese_PRC_CI_AS NULL ,产权性质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_PR

17、C_CI_AS NOT NULL)ON PRIMARYGO(3)职员信息表的建立：if exists (select * 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(Ndb

18、o. 职员信息 表)and OBJECTPROPERTY(id, NIsUserTable) = 1)drop table dbo.职员信息表GOCREATE TABLE dbo.职员信息表(职员编号char (3) COLLATE Chinese_PRC_CI_AS NOT NULL ,姓名nvarchar (5) COLLATE Chinese_PRC_CI_AS NULL ,性另J char (2) COLLATE Chinese_PRC_CI_AS NULL ,身份证号char (18) COLLATE Chinese_PRC_CI_AS NOT NULL ,学历nchar (6) C

19、OLLATE Chinese_PRC_CI_AS NULL ,职务nvarchar (15) COLLATE Chinese_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 ,备注nvar

20、char (50) COLLATE Chinese_PRC_CI_AS NULL ,简历nvarchar (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

21、_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 ,土地性质nvarchar (12) COLLATE Chinese_PRC_CI_AS NULL , 产权性质nvarchar (15) COLLATE Chinese_PRC_CI_AS NULL , 价

22、格money NULL ,权证名称nvarchar (35) COLLATE Chinese_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 , 联系 A nchar (6) COLLATE Chinese_PRC_CI_AS NULL , 联系电话char (12) COLLATE Chinese_PRC_CI_AS NULL

23、 , 委托日期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 PRIMARYGO(5)租房信息表的建立：if exists (select * from dbo.sysobjects where id = object_id(Ndbo. 租房信息 表)and OBJECTPRO

24、PERTY(id, NIsUserTable) = 1)drop table dbo.租房信息表GOCREATE TABLE dbo.租房信息表(ffl 房编号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 ,出租价

25、格money NULL ,出租底价money NULL ,业主姓名nvarchar (6) COLLATE Chinese_PRC_CI_AS NULL ,业主电话char (12) COLLATE Chinese_PRC_CI_AS NULL ,联系 A 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 NU

26、LL ,当前状态nchar (2) COLLATE Chinese_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)

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

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

29、房统计视图on待租房统计视图.编号=已租房统计视图.编号(4)用于已售房统计视图定义如下：create view 已售房统计视图 asselect编号,count（状态）as已售房数from房源信息表group by状态，编号having状态=已售1（5）用于已租房统计视图定义如下：create view 已租房统计视图 asselect编号,count（状态）as已租房数from房源信息表group by状态，编号having状态=已租（6）用于待售房统计视图定义如下：create view待售房统计视图asselect编号,count（犬态）as待售房数from房源信息表group by状态，编号having状态=待售（7）用于待租房统计视图定义如下：create view待租房统计视图asselect编号,count（犬态）as待租房数fr