房屋租赁管理数据库系统设计与设计

房屋租赁管理数据库系统

1 需求分析

1.1系统分析

1.1.1系统开发背景和开发目的

房屋租赁管理是一项琐碎、复杂而又十分细致的工作，房屋的基本资料，客户资料的管理，房屋租赁管理，各项收费以及统计报表的管理，一般不允许出错，如果实行手工操作，须手工填制大量的表格，这就会耗费工作人员大量的时间和精力，计算机进行房屋租赁工作的管理，能够保证各项信息准确无误。而且建立房屋数据库系统可以快速的进行各种信息的查询，方便快捷。

开发房屋租赁管理数据库系统主要通过计算机系统来管理房屋出租情况，可以解决大量房产信息的查询和管理，便于更好的进行物业管理，同时也为租赁者提供方便。这样，使原本复杂的房产、租金、合同信息简单化。解决物业管理企业在房屋租赁信息管理中的一些不规范，使房屋租赁信息的管理向着规范化、简单化、有效化的方向发展。

1.1.2 数据需求与处理

数据需求主要从房主、租赁者、中介公司业务员，中介公司财务人员四方面入手。 对于房主来说，其向中介公司提供房源，需要对自己的房源进行了解，出租情况，自己登记的信息情况的查询与修改等，还要了解自己与中介公司签订的合同的信息的查询，了解合同签订日期，到期日期等信息。

对于租赁者来说，需要对待出租房源的查询，了解待出租房源的租金，房屋设施，房屋地址等各种信息的查询。同时对自身信息的查询与修改。同时需要了解签订的租房合同的信息的查询。

对于中介公司业务员来说，需要登记各种信息，包括租赁者信息，房主个人信息和房屋的信息。因此，业务员需要获取租赁者信息，并登记到租赁者信息文件中；获取房主的信息和他拥有的房屋的信息，并登记到房主信息文件和房源信息文件中。和租赁者,房主签订赁合同，并对合同进行登记；和房主签订委托出租合同，并对委托出租合同进行登记。

作为租赁公司的业务员，主要负责促成合同的签订和服务费用的收取。 1.1.3数据存储

数据存储主要包括：房主基本信息，房源基本信息，委托出租合同，租赁者信息，

租赁合同等。

1.1.4业务流程

房主提供房源，中介公司对房主个人信息与房源信息进行登记，将房主信息存入数据库。根据房主登记的房源信息，对房源进行实地考察，确认属实后存入数据库。中介公司与房主签订委托出租合同。

租赁者向中介公司提出出租请求，中介公司派遣业务员带领租赁者现场看房，撮合房主与租赁者完成租赁，签订租赁者、中介公司、房主三方合同。租赁者向中介公司交付服务费用，房主根据委托出租合同向中介公司交付委托费用。

业务员主要负责登录房主、房源信息，带领租赁者现场看房，撮合房主和租赁者签订出租三方合同。委托租赁合同到期，则注销房主信息和房源信息。若存在继续约情况，则重新签订新的委托出租合同。

财务人员根据三方合同收取中介费用和委托费用，并进行入账处理。

公司根据业务员一年的业务水平对业务员的业绩做出评估，根据评估结果给业务员加薪并修改业务员的等级。

1.2 数据信息

1.2.1静态数据信息 房主的个人信息主要包括：

房主编号，作为房主在数据库中的编码。 房主姓名 房主联系电话 房主联系地址

房主个人银行账户，用于中介公司定期支付租金。

房主邮箱地址，用于中介公司发送各种文件，以及传递租金支付通知。 房源的信息包括：

房屋编号，作为房屋在数据库中的编码 房屋地址，房屋的具体地址，坐落位置等。

房屋的设施，包括房屋的配套设施，如空调，家电，家具情况的说明。 房屋的面积，房屋的建筑面积。 房屋的装修情况

房屋的出租状态，用来标示房屋是否已经出租，是处于出租状态还是未出租状态。 出租要求，对出租对象的一些具体要求。 委托合同的信息包括：

委托合同编号，作为合同在数据库中的编码

房主编号，通过房主编号，可以查询房主的具体信息。 房源编号，通过房源编号，可以查询房屋的具体信息。

出租价位，确定中介公司在房屋出租后，每月支付给房主的租金。 签订日期，合同签订日期。 到期日期，合同到期日期。

备注，用于记录其他相关需要备注的信息。 租赁者的个人信息包括：

租赁者编号，唯一标识租赁者，作为租赁者在数据库中的编码。 租赁者姓名 租赁者联系电话

租赁者个人银行账户，用于中介公司定期支付租金。

租赁者邮箱，用于中介公司发送各种文件，以及传递租 金支付通知 备注，用于记录其他相关需要备注的信息。 三方合同信息：

租赁合同编号，作为房屋在数据库中的编码。

租赁者编号，通过租赁者编号，可以查询房主的具体信息。 房源编号，通过房源编号，可以查询房屋的具体信息。

出租价位，确定中介公司在房屋出租后，每月向租赁者收取的租金。 服务费用，确定中介公司双方收取的服务费用。 业务员：促成合同的业务员编号 签订日期，合同签订日期。 到期日期，合同到期日期。

备注，用于记录其他相关需要备注的信息。 财务信息：

财务信息编号，作为报表在数据库中的编号。 三方合同编号：生成财务信息的合同编号 日期：生成财务信息的日期 金额：收取的服务费用

审核员：审核财务信息的业务员编号 审核：是否已经审核 看房信息：

看房信息编号，作为看房情况在数据库中的编号 租赁者编号，看房时候的租赁者信息 业务员编号，看房时候的业务员 看房日期，看房的时间 房源信息，看房的房屋信息 业务员信息：

业务员编号，唯一标识业务员

姓名，业务员的姓名 性别，业务员的性别 等级，业务员的等级 出生日期，业务员的出生日期 工作单位，业务员的工作单位 薪酬，业务员的薪资 入职时间，业务员的入职时间

1.3数据流图

房主信息房主房主信息P1委托 房源信息委托合同p3缴费房源信息中介费用明细账租赁者p2委托费用三方合同p4反馈信息看房统计看房信息业务员看房安排租赁者信息报表1.

4数据字典

1,4.1 数据项

房主：

房主编号={房主编号，作为房主在数据库中的编码，数值型，10个字符} 房主姓名={房主姓名, 房主姓名，字符型, 字符型，10个字符} 房主联系电话={房主联系电话, 房主联系电话，字符型, 15个字符} 房主联系地址={房主地址, 房主联系地址，字符型, 60个字符}

房主个人银行账户={房主个人银行账户, 房主个人银行账户，字符型, 25个字符} 房主邮箱地址={房主邮箱地址, 房主邮箱地址，字符型, 25个字符}

房源：

房源编号={房源编号，作为房主在数据库中的编码，数值型，10个字符} 房主编号={房主编号，作为房主在数据库中的编码，数值型，10个字符} 房屋地址={物业地址，房屋的具体地址、坐落位置等，字符型, 60个字符} 房屋的设施={房屋的设施, 包括房屋的配套设施，如空调、家电、家具情况的说明，字符型, 100个字符}

房屋的面积={建筑面积, 房屋的建筑面积，数值型, 10个字符} 房屋的装修情况={房屋的装修情况, 字符型, 60个字符}

房屋的出租状态={房屋的出租状态, 是处于出租状态还是未出租状态，字符型, 10个字符}

出租要求={出租要求, 对出租对象的一些具体要求，字符型, 60个字符} 备注={备注，房屋其他信息，字符型，50，房屋其他信息} 委托合同：

委托合同编号={委托出租合同编号，作为房主在数据库中的编码，数值型，10个字符}

房源编号={房源编号，作为房源在数据库中的编码，数值型，10个字符} 出租价位={出租价位，每月支付给房主的租金，数值型，10个字符} 签订日期={签订日期，日期/日期} 到期日期={到期日期，日期/日期}

备注={备注，用于记录其他相关需要备注的信息，字符型，50个字符 } 租赁者：

租赁者编号={租赁者编号，唯一标识租赁者，求租客户编号，字符型，10个字符}、 租赁者姓名={租赁者姓名，租赁者的真实姓名，求租客户姓名，字符型，6个字符} 租赁者联系电话={租赁者联系电话，租赁者的真实手机号码，求租客户手机号，字符型，15个字符}

租赁者个人银行账户={租赁者个人银行账户, 租赁者个人银行账户，字符型, 25个字符}

租赁者邮箱={租赁者邮箱，字符型, 25个字符} 备注={备注，租赁者其他信息，字符型，50个字符} 三方合同：

合同编号={合同编号，唯一标识一份合同，字符型，10个字符} 房主编号={房主编号，唯一标识房主，租户编号，字符型，10个字符} 房源编号={房源编号，作为房源在数据库中的编码，数值型，10个字符} 租赁者编号={租赁者编号，唯一标识租赁者，求租客户编号，字符型，10个字符} 出租价位={出租价位，每月支付给房主的租金，数值型，10个字符} 服务金额={服务金额，需要缴纳的金额，数型，5个字符}

签订日期={日期，签订合约的日期，日期型，8个字符} 到期日期={日期，合同到期的日期，日期型，8个字符} 备注={备注，合同其他信息，字符型，50，合同其他信息} 财务信息：

财务信息编号={编号，唯一标识一条财务信息，字符型，10个字符} 三方合同编号={合同编号，唯一标识一份合同，字符型，10个字符} 日期 ={日期，财务信息生成日期，日期型，8个字符} 金额={金额，需要缴纳的金额，数型，5个字符}

审核员={审核员，审核财务信息的人员编号，数值型，10个字符} 审核={审核，是否已经审核，布尔型，2个字符}

备注={备注，合同其他信息，字符型，50，合同其他信息} 看房信息：

看房信息编号={编号，看房情况在数据库中的编号，字符型，10个字符} 租赁者编号={租赁者编号，看房时候的租赁者信息，字符型 ，10个字符} 业务员编号={业务员编号，看房时候的业务员，字符型，10个字符} 看房日期={看房日期，看房的时间，日期型，8个字符}

房源信息={房源编号，作为看房的房屋信息，字符型，10个字符} 业务员信息:

业务员编号={编号，唯一标识业务员，字符型，10个字符} 姓名={姓名，业务员的姓名，字符型，10个字符} 性别={性别，业务员的性别，字符型，5个字符} 等级={等级，业务员的等级，字符型，10个字符}

出生日期={出生日期，业务员的出生日期，日期型，8个字符} 工作单位={工作单位，业务员的工作单位，字符型，30个字符} 薪酬={薪酬，业务员的薪资，数值型，10个字符}

入职时间={入职时间，业务员的入职时间，日期型，8个字符} 1.4.2 数据结构

房主描述={房主，房主信息，组成：{房主编号，房主姓名，联系电话，联系地址，个人银行账号，房主邮箱地址}}

委托出租合同描述={委托出租合同，房主与中介公司签订的合同，组成：{委托出租合同编号，房主编号，房源编号，出租价位，签订日期，到期日期，备注}}

租赁者描述={租赁者，租赁者信息，组成：{租赁者编号，租赁者姓名，联系电话，个人银行账户，邮箱地址}

三方合同描述={三方合同，租赁者、房主与中介公司签订的合同，组成：{三方合同编号，租赁者编号，房源编号，出租价位，服务金额，签订日期，到期日期，备注}}

房源描述={房源，房屋的具体信息，组成：{房源编号，房屋地址，房屋的设施，建

筑面积，装修情况，出租状态，出租要求，备注}}

财务信息={财务，公司的财务信息，组成：{财务信息编号，三方合同编号，日期，金额，审核员，审核，备注}}

看房信息={看房情况，组成：{看房信息编号，租赁者编号，业务员编号，看房日期，房源信息}}

业务员信息={业务员的信息，组成：{业务员编号，姓名，性别，等级，出生日期，工作单位，薪酬，入职时间}}

1.4.3数据流

房源信息和房主信息={房源信息和房主信息，登记房源信息和房主信息，业务员，组成：{房主，房源}}

委托合同信息流描述={合同签订，合同信息，业务员，财务管理，组成：{委托出租合同编号}}

租赁者信息流描述={租赁者信息，租赁者信息，业务员，财务管理，组成{租赁者编号}}

三方合同信息流描述={合同签订，合同信息，业务员，财务管理，组成：{租赁合同编号}}

看房信息={看房信息编号，租赁者编号，业务员编号，看房日期，房源信息，组成：{看房信息编号}}

业务员信息={业务员编号，姓名，性别，等级，出生日期，工作单位，薪酬，入职时间，组成：{业务员编号}}

财务信息={财务信息编号，三方合同编号，日期，金额，审核员，审核，备注，组成：{公司的财务编号}} 1.4.4数据存储

房主信息：

流入的数据流/来源：房主 流出的数据流/去向：房主信息存储 组成：房主编号，房主姓名，联系电话，联系地址，个人银行账号，房主邮箱地址 存储方式：顺序 委托合同：

流入的数据流/来源：业务员 流出的数据流/去向：委托出租合同存储 组成：委托出租合同编号，房源编号，出租价位，签订日期，到期日期，备注 存储方式：顺序 租赁者信息：

流入的数据流/来源：租赁者 流出的数据流/去向：租赁者信息存储 组成：租赁者编号，租赁者姓名，联系电话，个人银行账户，邮箱地址 存储方式：顺序 三方合同信息：

流入的数据流/来源：业务员 流出的数据流/去向：租赁合同信息存储 组成：租赁合同编号，租赁者编号，房源编号，出租价位，定金金额，签订日期，到期日期，备注 存储方式：顺序 房源信息：

流入的数据流/来源：业务员 流出的数据流/去向：房源信息存储 组成：房源编号，房主编号，房屋地址，房屋的设施，建筑面积，装修情况，出租状态，出租要求，备注 存储方式：顺序 财务信息：

流入的数据流/来源：缴费 流出的数据流/去向：明细账 组成：财务信息编号，三方合同编号，日期，金额，审核员，审核，备注 存储方式：顺序 看房信息：

流入的数据流/来源：看房 流出的数据流/去向：看房信息 组成：看房信息编号，租赁者，业务员编号，看房日期，房源信息 存储方式：顺序 业务员信息：

流入的数据流/来源：看房 流出的数据流/去向：业务员信息 组成：业务员编号，姓名，等级，性别，工作单位，出生日期，薪酬，入职时间 存储方式：顺序 2 功能设计

2.1功能模块

2.1.1主要处理功能

（1）对房主个人信息的增加，查询，修改，删除。 （2）对委托合同进行增加，查询，修改，删除。 （3）对租赁者个人信息的增加，查询，修改，删除。 （4）对三方合同进行增加，查询，修改，删除。

（5）根据三方合同生产财务信息表。并对其查询，修改，删除。 （6）对房源信息的增加，查询，修改，删除。 （7）对业务员信息的增加，查询，修改，删除。 （8）对看房信息的增加，查询，修改，删除。 2.1.2功能模块的划分

房屋租赁管理数据库系统 用户管房屋管合同管财务管房主信业务员管租赁者信房源信看房信委托出租合租赁合同财务信息

2.1.3 各功能模块的任务

客户管理模块主要负责房主信息的维护与租赁者信息的维护。房主信息包括：房主编号、房主姓名、联系电话、联系地址、个人银行账号，房主邮箱地址。租赁者信息包括租赁者编号，租赁者姓名，联系电话，个人银行账户，邮箱地址。在新增客户时，要对客户的这些信息进行登记。当信息发生变更时，要对数据库中的信息进行修改。当数据库中的客户不再是公司的客户时，删除该客户在数据库中的记录。

房源管理模块主要负责房源信息的维护。房源信息包括：房源编号，房屋地址，房屋的设施，建筑面积，装修情况，出租状态，出租要求，备注。在对房屋进行过现场确认后，要对房源的这些信息进行登记。当信息发生变更时，要对数据库中的信息进行修改。当数据库中的房源不再是中介公司的房源时，在数据库中删除相关信息。

合同管理主要负责委托合同和三方合同的信息维护，委托合同包括：委托合同编号，房主编号，房源编号，出租价位，签订日期，到期日期，备注。三方合同包括：三方合同编号，租赁者编号，房源编号，业务员编号，出租价位，服务金额，签订日期，到期日期，备注。对最新签订的合同进行登记，在合同到期后进行删除。

财务管理模块主要负责根据新增的三方合同形成财务信息表。

3 概念设计

3.1确认实体及其属性

房主{房主编号，房主姓名，联系电话，联系地址，个人银行账号，房主邮箱地址} 委托出租合同{委托出租合同编号，房主编号，房源编号，出租价位，签订日期，到期日期，备注}

租赁者{租赁者编号，租赁者姓名，联系电话，个人银行账户，邮箱地址} 三方合同{三方合同编号，租赁者编号，房源编号，业务员编号，出租价位，服务金额，签订日期，到期日期，备注}

房源{房源编号，房屋地址，房屋的设施，建筑面积，装修情况，出租状态，出租要求，备注}

业务员{业务员编号，姓名，性别，等级， 出生日期 ，工作单位 ，薪酬 入职时间

}

看房信息{看房信息编号，租赁者，业务员编号，看房日期，房源信息} 财务信息{财务信息编号，三方合同编号，日期，金额，审核员，审核，备注} 3.2 E-R图

4 逻辑设计

4.1逻辑模型

房主{房主编号，房主姓名，联系电话，联系地址，个人银行账号，房主邮箱地址} 委托出租合同{委托出租合同编号，房主编号，房源编号，出租价位，签订日期，到期日期，备注}

租赁者{租赁者编号，租赁者姓名，联系电话，个人银行账户，邮箱地址} 三方合同{三方合同编号，租赁者编号，房源编号，业务员编号，出租价位，服务金额，签订日期，到期日期，备注}

房源{房源编号，房屋地址，房屋的设施，建筑面积，装修情况，出租状态，出租要求，备注}

业务员{业务员编号，姓名，性别，等级， 出生日期 ，工作单位 ，薪酬 入职时间

}

看房信息{看房信息编号，租赁者，业务员编号，看房日期，房源信息} 财务信息{财务信息编号，三方合同编号，日期，金额，审核员，审核，备注}

4.2确认各表字段及其属性

表名 描述 PK FK homeOwner 房主 ownerId No. Column name data type Null Default Memo 1 ownerId varchar(20) × 房主 房主姓名 联系电话 data sample 2 ownerName varchar(20) × 3 ownerPhone varchar(15) × 4 ownerAddresvarchar(100× s ) 5 ownerEmail varchar(50) 6 Status 表名 描述 PK FK homeOwner(ownerId) NulNo. Column name data type l 1 houseId 0) 2 ownerId 0) 3 houseAddresvarchar(1× s 4 houseFaci 00) varchar(1× varchar(2× varchar(2× Bit houses 房源 houseId foreign key 联系地址 邮箱地址 是否注销 0-否，1-是 × (ownerId) references data Default Memo sample 房源编号 房主编号 房屋地址 房屋的设 00) 5 houseArea 6 houseDeco 00) 7 houseStatus bit 8 zent money e 10 status 11 comment 12 IsHot 13 style Varchar(5) 00) bit varchar(1 00) × x x 0 float varchar(1 × 施 房屋的面 积 装修情况 0-未出租；出租状态 1-出租 租金 出租要求 是否注销 0-否，1-是 备注 是否热门0-否，1-是 9 houseRequirvarchar(1bit 房源 廉价型，经房源类型 济型，奢侈型 表名 描述 PK FK authorize 委托合同 contractId foreign key (ownerId) references homeOwner(ownerId) foreign key (bid) references businessman (bid) No. Column name data type Null Default Memo 1 contractId varchar(20) × 2 houseId varchar(20) × × × × ) 7 text 8 bid 9 rent 表名 描述 PK FK NoColumn . name 1 leaId 0) Nuldata type l varchar(2× 号 Default Memo sample 租赁者编 data varchar(10) × money Leaser 租赁者 leaId text 合同内容 BS0000000业务员编号 1 租金 data sample 合同编号 房源编号 最低租金 签订日期 到期日期 备注 3 lowestMoney money 4 bedate 5 dueDate 6 comment datetime datetime varchar(1002 lname varchar(2× 0) 租赁者姓 名 x 联系电话 租赁者邮 × 箱 备注 3 lphone varchar(15) 4 lemail varchar(50) 5 lothers varchar(100) 表名 描述 PK Contract 三方合同 Cno foreign key (bid) references businessman (bid) foreign key (ownerId) references FK homeOwner(ownerId) foreign key (leaId) references leaser(leaId) NoColumn . name 1 Cno 0) 2 ownerId 0) 3 leaId varchar(2× 租赁者编 varchar(2× 房主编号 Nuldata type l varchar(1× 合同编号 Default Memo sample data 0) 4 号 按规定一般是一个月的租金作为中介费用相中介的佣Cmoney money × 对于月租金金，也可以的百分比 自己规定一个额度，比如大于0.7小于1 5 CAmoney money 甲方中介费0.5 用占的百分比 6 CBmoney money 乙方中介费0.5 用占的百分比 6 Csdate 7 rent 8 comment Datetime Money varchar(1 00) text varchar(1 × × 签订日期 月租金 备注 内容 业务员编 BS0000000 9 content 10 Bid 0) 11 HouseId 表名 描述 PK FK NoColumn data type . name 1 Fno 2 Cno ) 3 payPeoplevarchar(4) × A Int varchar(10× × ll Varchar(20) Finance 财务信息 Fno 号 1 房源编号 FOREIGN KEY (Cno) REFERENCES Contract(Cno) NuDefault Memo sample 财务信息编 号 三方合同编 号 甲方-房主， 甲方 乙方-租赁者 data payPeoplevarchar(4) × B 乙方 甲方-房主， 乙方-租赁者 4 Fdate 5 Fmoney Datetime money × × 付款日期 金额 6 varchar(20Fassessor ) × 审核员 7 Fverify 8 comment 0) 9 status 表名 描述 PK FK NoColumn data type . name 1 aid ) 2 leaId ) 3 houseId ) 4 bmanId ) varchar(10× 业务员编号 varchar(20× 房源编号 varchar(20× 租赁者编号 varchar(12× 号 ll 看房信息编 NuDefault Memo Varchar(5) × arrangeInfo 看房信息 Aid 状态 Varchar(5) × 是 varchar(10 备注 审核 已审核，未审核 已交付，未交付 data sample L+年月日+3位流水号 5 adate 6 comment datetime varchar(10 0) × 看房日期 备注信息 表名 描述 PK NoColumn businessman 业务员 bid Nudata type Default Memo ll varchar(10× ) varchar(20sample BS000000业务员编号 01 × ) char(2) x 男 姓名 性别 男，女 初级业务员; 中级varchar(10初级业x ) 务员 高级业务员 等级 业务员; data . name 1 bid 2 bname 3 bsexy 4 bgrade 5 bbirthday datetime 6 bsalary 7 bbegindatdatetime e money x x 出生日期 薪酬 入职时间 8 status 9 comment 0) 4.3自定义函数

varchar(5) x varchar(10 备注信息 状态 在职，离职 1. 统计某位租赁者的看房次数和所看房的信息(房屋面积平均值,价格平均值等信息) 。

(已写)

2. 查询某位房主的所有房源信息和房源看房信息统计：看房信息：统计各房源被看次数

（已写）

4.4触发器

3. 添加三方合同时同时插入两条未审核、未交付的财务信息，同时修改房源状态为已出

租。(已写)

4. 注销房主信息的同时注销房主所有的房源信息。

5. 添加房源的同时，根据房源的出租价格确定房源的类型,800元以下为廉价型，

800-2500元为经济型，2500元以上为奢侈型。

6. 添加委托合同插入触发器，若最低价格大于房源出租价格，则操作失败（这里应该是

签订三方合同时，要去考察委托合同里的最低租金，如果协议价格低于最低租金的话，操作失败，该题目也可以使用存储过程，完成三方合同数据的存入，并三方合同表的插入和租金修改触发器，确保租金高于最低）

4.5存储过程

7. 修改业务员工资的存储过程，修改后的工资必须满足一下条件：初级业务员的工资在

1500-2500元，中级业务员的工资在2500-3500元,高级业务员的工资在3500元以上元。返回值-1为不满足工资条件，-2该业务员不存在，1修改成功。(已写) 8. 根据业务员一年的业绩（成功撮合成交所盈利的金额数）修改业务员等级和工资情况，

金额数大于150000元的工资提高15%，金额数大于100000元的工资提高10%，金额数大于50000元的工资提高5%，同时根据提升后工资水平修改业务员的等级。(存储过程+游标，已写)

9. 检查并注销房源信息的存储过程，检查房源的委托合同是否到期，如果到期就注销该

条房源。

10. 根据房源一段时间的看房信息情况修改房源的状态，是否为热门房源。一个月内看房

数量达到5次以上为热门，一年内看房数量达到30次以上为热门房源。 --建库

if exists(select * from sys.sysdatabases where name='fwzlxt') begin use master

drop database fwzlxt end go

create database fwzlxt go use fwzlxt go

---房主信息表

create table homeOwner ( ownerId varchar(20) ,

ownerName varchar(20) not null, ownerPhone varchar(15) not null, ownerAddress varchar(100) not null,

[status] bit default 0 check([status] in(0,1)) not null, ownerEmail varchar(50) null ,

constraint pk_homeOwner primary key(ownerId), constraint

chk_ownerPhone

check

(ownerPhone

'1[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]')

) go

insert homeOwner values go

---房源信息表 create table houses ( houseId varchar(20) , ownerId varchar(20) not null, houseAddress varchar(100) not null, houseFaci varchar(100) not null, houseArea float,

houseDeco varchar(100) not null,

houseStatus bit default 0 check(houseStatus in(0,1)), like

) go

zent money not null,

[status] bit default 0 check([status] in(0,1)) not null, houseRequire varchar(100) , comment varchar(100) ,

isHot bit default 0 check(isHot in(0,1)) not null,

style varchar(10) check(style in('廉价型','经济型','奢侈型')), constraint pk_houses primary key (houseId),

constraint FK_houown foreign key (ownerId) references homeOwner(ownerId)

insert houses values go

--业务员建表

create table businessman ( ) go

insert businessman values

('BS00000001','王','男','初级业务员','1990/1/1',3500,'2010/1/1','

bid varchar(10) primary key ,--check(bid like '[BS] [0-9] [0-9] [0-9] bname varchar(20) not null,

bsexy char(2) default '男' check(bsexy in('男','女')),

bgrade varchar(10) default '初级业务员' check(bgrade in('初级业务员','中级bbirthday datetime, bsalary money not null, bbegindate datetime not null,

[status] varchar(5) check([status] in('在职','离职')) not null , comment varchar(100) , [0-9][0-9][0-9][0-9][0-9]')

('hs000000001','00001','杭州','设施齐全',500,'装修精美',0,1200,1,'要('hs000000002','00001','杭州','设施不齐全',600,'装修精美',0,1500,1,'('hs000000003','00002','杭州','设施齐全',400,'装修精美',0,1600,1,'要

求租给本地人','无',0,'廉价型'), 要求租给外地人','无',1,'廉价型'), 求租给本地人','无',0,'廉价型')

业务员','高级业务员')) not null,

在职',''), go

--委托合同信息表 create table authorize ( ) go

insert authorize values go --租赁者

create table leaser (

leaId varchar(20) primary key, lname varchar(20) not null, lphone varchar(15) not null, lemail varchar(50) not null, lothers varchar(100) , --手机号的约束

('a00000001','hs000000001',1000.0,1200.0,'2011/1/1','2012/7/1','无','合同信息','BS00000001')

contractId varchar(20) primary key, houseId varchar(20) not null,

lowestMoney money ,--最低租金必须比租金小，否则无法生成 rent money,

bedate datetime not null, dueDate datetime not null, comment varchar(100) , [text] text, bid varchar(10),

constraint FK_authou foreign key (houseId) references houses (houseId), constraint FK_autbid foreign key (bid) references businessman (bid), constraint chk_dueDate check (dueDate>beDate)

('BS00000002','刘','男','中级业务员','1990/1/1',4500,'2010/1/1','('BS00000003','赵','女','高级业务员','1990/1/1',5500,'2010/1/1','

在职',''), 在职','')

constraint

--邮箱的约束(建议改) constraint

l_email

l_phone check(lphone like

'[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'),

check

or

(charindex('@163.',lemail)>0 charindex('@QQ.',lemail)>0

or or

charindex('@gmail.',lemail)>0 charindex('@yahoo.',lemail)>0) --银行账户的约束 ) go

insert leaser values go

--看房信息建表

create table arrangeInfo (

aid

varchar(12)

primary key check(aid like

'[L][1-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'),--'L+年月日+3位流水号 ) go

insert arrangeInfo values 2012/5/8','hs000000001'), 2012/5/8','hs000000001'), 2012/5/8','hs000000001') go --三方合同

CREATE TABLE [contract] (

Cno varchar(10) not null, ownerId varchar(20)not null, leaId varchar(20)not null, bid varchar(10) not null, adate datetime not null, houseId varchar(20) not null,

constraint fk_leaarr foreign key (leaId) references leaser (leaId), constraint fk_busarr foreign key (bid) references businessman (bid), constraint fk_houarr foreign key (houseId) references houses (houseId)

) go

leaId varchar(20)not null, houseId varchar(20) not null, Cmoney money not null, CAmoney money , CBmoney money ,

Csdate Datetime not null, rent money ,

comment varchar(100), content text not null, bid varchar(10),

constraint pk_Contract primary key(Cno), constraint

cno_check

check(Cno

like

'[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'),

constraint FK_conbid foreign key (bid) references businessman (bid), constraint Fk_conown foreign key (ownerId) references homeOwner(ownerId), constraint Fk_conlea foreign key (leaId) references leaser(leaId),

insert [contract] values go

---财务信息表 CREATE TABLE finance (

Fno int identity(0,1), Cno varchar(10) not null ,

payPeople varchar(4) check(payPeople in ('甲方','乙方')), Fdate Datetime, Fmoney money,

Fassessor varchar(20), Fverify char(2) default '是', comment varchar(100),

[status] varchar(5) check([status] in('已交','未交')), constraint pk_Finance primary key(Fno),

('0000000001','00001','le00000001','hs000000001',1600,800,800,'2012/1/1',

2200,'无','三方合同内容','BS00000001')

) go

constraint chk_Fverify check(Fverify in ('是','否')),

constraint fk_Finance FOREIGN KEY (Cno) REFERENCES [contract](Cno),

insert finance values Go

--自定义函数

-- 统计某位租赁者的看房次数和所看房的信息(房屋面积平均值,价格平均值等信息) use fwzlxt go

if OBJECT_ID(N'fn_leinfo',N'TF') is not null go

create function fn_leinfo(@leaId varchar(20))

returns @tmptab table (nums int,area float,price money) as go

select * from fn_leinfo('le00000001') go

--查询某位房主的所有房源信息和房源看房信息统计：看房信息：统计各房源被看次数 use fwzlxt go

if OBJECT_ID(N'fn_houinfo',N'TF') is not null

drop function fn_houinfo; begin

if not exists(select * from leaser where leaId=@leaId) return; else

insert @tmptab select COUNT(*) as nums, AVG(houseArea) as area,

drop function fn_leinfo;

('0000000001','甲方','2012/1/1',000,'who','是','','已交')

AVG(zent) as price

from (arrangeInfo join houses on arrangeInfo.houseId = houses.houseId)

ON houses.houseId =authorize.houseId where arrangeInfo.leaId =

JOIN authorize @leaId

return end

go

create function fn_houinfo(@ownerId varchar(20)) returns

@tmptab

table(ownerName varchar(100),houseDeco varchar(100),style

varchar(20),houseFaci

varchar(100),zent varchar(10),houseStatus

varchar(100),houseAddress money,houseRequire as go

select * from fn_houinfo('00001') go --触发器

--1、 添加三方合同时同时插入两条未审核、未交付的财务信息，同时修改房源状态为已出租。 use fwzlxt go

if OBJECT_ID('tr_conin','P') is not null

begin

if not exists (select * from homeOwner where ownerId = @ownerId) return ; else

insert

houseStatus = case houseStatus WHEN '0' THEN '未租' WHEN '1' THEN '已租' end ,

isHot = CASE isHot WHEN '0' THEN '不热门' WHEN '1' THEN '热门' end ,

rentNums =(SELECT COUNT(*) FROM arrangeInfo WHERE from houses JOIN homeOwner ON houses.ownerId =

@tmptab

select

varchar(10),isHot varchar(10),rentNums int)

ownerName,houseFaci,houseAddress,houseDeco,zent,houseRequire, style,

arrangeInfo.houseId =houses.houseId) homeOwner.ownerId

return end

drop procedure tr_conin; go

create trigger tr_conin on [contract] after insert as go insert

[contract]

values

('0000000002','00001','le00000001','hs000000001',1600,800,800,'2012/1/1',2200,'无','三方合同内容','BS00000001')

if OBJECT_ID('modifyBusinessman','P')is not null drop proc modifyBusinessman go

--2、 注销房主信息的同时注销房主所有的房源信息。

create trigger del_homeowner on homeOwner instead of delete as begin

declare @id int

select @id=ownerId from deleted delete from houses where ownerId=@id delete from homeOwner where ownerId=@id

declare @houseId varchar(20),@cno varchar(10),@cAmoney money ,@cBmoney declare incur cursor dynamic for select houseId ,cno,cAmoney,cBmoney from open incur;

fetch first from incur into @houseId,@cno,@cAmoney,@cBmoney while @@FETCH_STATUS=0 begin

update houses SET houseStatus =1 WHERE houseId =@houseId

INSERT into finance VALUES(@cno,'甲方',GETDATE(),@cAmoney,'','否','','INSERT into finance VALUES(@cno,'乙方',GETDATE(),@cBmoney,'','否','','fetch next from incur into @houseId,@cno,@cAmoney,@cBmoney

money; inserted ;

未交') 未交')

end

close incur; deallocate incur;

end

--3、 添加房源的同时，根据房源的出租价格确定房源的类型,800元以下为廉价型，800-2500元为经济型，2500元以上为奢侈型。

create trigger insert_houses on houses for insert as

declare @curcal cursor set @curcal= cursor for select houseId,zent from inserted open @curcal

declare @price money,@houseId varchar(20) Fetch next from @curcal into @houseId,@price While (@@fetch_status=0) begin

declare @style varchar(10) if @price<=800 set @style='廉价型'

else if @price<=2500 and @price >800 set @style='经济型' else set @style='奢侈型'

update houses set style=@style where houseId=@houseId Fetch next from @curcal into @houseId,@price end

close @curcal deallocate @curcal use fwzlxt go

--4、 添加委托合同插入触发器，若最低价格大于房源出租价格，则操作失败 create trigger insert_auth on authorize instead of insert as

declare @contractId varchar(20),@houseId varchar(20),@lowestMoney money, @bedate datetime,@dueDate datetime,@comment varchar(100), @bid varchar(10),@rent money begin tran select

@contractId=contractId,@houseId=houseId,@lowestMoney=lowestMoney,@bedate=bedate,

@dueDate=dueDate,@comment=comment,@bid=bid,@rent=rent from inserted insert into authorize values (@contractId,@houseId,@lowestMoney,@bedate, @dueDate,@comment,'合同信息',@bid,@rent) if @lowestMoney>@rent rollback tran

else commit tran --存储过程

--1、 修改业务员工资的存储过程，修改后的工资必须满足一下条件：初级业务员的工资在1500-2500元，中级业务员的工资在2500-3500元,高级业务员的工资在3500元以上元。返回值-1为不满足工资条件，-2该业务员不存在，1修改成功。 create proc modifyBusinessman @bno varchar(10), @bgrade varchar(10), @bsalary money as begin

declare @salary money,@grade varchar(10)

--select @salary=bsalary,@grade=bgrade from businessman where bno=@bno; if exists(select * from businessman where bid=@bno) begin

if not((@bgrade='初级业务员' and @bsalary>=1500 and @bsalary<=2500)or(@bgrade='中级业务员' and @bsalary>=2500 and @bsalary<=3500)or(@bgrade='高级业务员' and @bsalary>=3500 and @bsalary<=5000)) return -1

update businessman set bgrade=@bgrade,bsalary=@bsalary where bid=@bno end

else return -2 end go

declare @info varchar(20)

exec @info = modifyBusinessman 'BS00000001','中级业务员',3000.00 print @info go

--存储过程+游标

--2、 根据业务员一年的业绩（成功撮合成交所盈利的金额数）修改业务员等级和工资情况，金额数大于150000元的工资提高15%，金额数大于100000元的工资提高10%，金额数大于50000元的工资提高5%，同时根据提升后工资水平修改业务员的等级。 if OBJECT_ID('addSalary','P')is not null drop proc addSalary go

create proc addSalary as begin

declare modifySalaryCursor Cursor

for select SUM(Cmoney),c.bid,b.bsalary from [contract] c join businessman b on c.bid=b.bid group by c.bid,b.bsalary open modifySalaryCursor;

declare @tmp_money money,@tmp_bid varchar(10),@tmp_salary money; fetch next from modifySalaryCursor into @tmp_money,@tmp_bid,@tmp_salary; while @@FETCH_STATUS =0 begin

print @tmp_money; print @tmp_bid;

declare @grade varchar(10),@new_salary money;

if @tmp_money>=150000 set @new_salary=@tmp_salary*1.15 else if @tmp_money<150000 and @tmp_money>=100000 set @new_salary=@tmp_salary*1.1

else if @tmp_money<100000 and @tmp_money>=50000 set @new_salary=@tmp_salary*1.05 else set @new_salary=@tmp_salary

if @new_salary<=2500 set @grade ='初级业务员' else if @new_salary<=3500 set @grade='中级业务员' else if @new_salary>3500 set @grade='高级业务员' if(@tmp_money!=@new_salary)

update businessman set bgrade=@grade,bsalary=@new_salary where bid = @tmp_bid

fetch next from modifySalaryCursor into @tmp_money,@tmp_bid,@tmp_salary; end

close modifySalaryCursor; deallocate modifySalaryCursor; end go --测试 exec addSalary

--3、检查并注销房源信息的存储过程，检查房源的委托合同是否到期，如果到期就注销该条房源。

Create proc checkHouse as begin

declare checkAuthorize cursor

for select houseId,dueDate from authorize

open checkAuthorize

declare @houseId varchar(20), @dueDate datetime Fetch next from checkAuthorize into @houseId,@dueDate while @@FETCH_STATUS=0 begin

-- print @houseId if(@dueDateupdate houses set status=1 where houseId=@houseId Fetch next from checkAuthorize into @houseId,@dueDate end

close checkAuthorize deallocate checkAuthorize end

--4、根据房源一段时间的看房信息情况修改房源的状态，是否为热门房源。一个月内看房数量达到5次以上为热门，一年内看房数量达到30次以上为热门房源。 Create proc checkIsHOT as begin --按月

declare checkReports cursor for select '次数'=count(houseId),houseId from arrangeInfo group by month(adate),houseId --遍历这张表 open checkReports

declare @num int,@houseId varchar(20)

fetch next from checkReports into @num,@houseId while @@FETCH_STATUS=0 begin if(@num>=5)

update houses set isHot=1 where houseId=@houseId fetch next from checkReports into @num,@houseId end

close checkReports deallocate checkReports --按年

declare checkReports cursor for select '次数'=count(houseId),houseId from arrangeInfo group by year(adate),houseId open checkReports

declare @num1 int,@houseId1 varchar(20)

fetch next from checkReports into @num1,@houseId1 while @@FETCH_STATUS=0 begin

if(@num1>=30)

update houses set isHot=1 where houseId=@houseId1 fetch next from checkReports into @num1,@houseId1 end

close checkReports deallocate checkReports end

exec checkIsHOT