房地产开发项目管理

房地产开发项目管理数据库设计数据库在房地产项目开发系统中非常的重要，数据库的设计一般包括以下四个部分:数据库需求分析、数据库概念结构设计、数据库逻辑结构设计、数据库物理结构实现。

一、数据库需求分析1.项目计划：项目号(01,02,03,04,05,06)，项目名称（电梯公寓，爱情公寓，别墅，商品房，养老院，幼儿园），项目可行性报告(可行，不可行，难度很大)，项目造价（高昂，高，低成本，），项目地点勘查（贵阳市龙洞堡，贵阳市小河区，贵阳市蔡家关，贵阳市喷水池），项目销售预测（热销，滞销，），项目计划日期，项目进度（已完成，正在施工，正在勘测），2.部门类别：部门编号（1,2,3,4,5），部门名称（管理部，项目执行部，宣传部，销售部，服务部），部门地址（北京，贵阳，成都，贵阳，重庆），部门人数（4，11，5，5，5）3.项目实施人员：员工编号，姓名，性别，职务, 权限级别, 部门编号，家庭住址，联系电话4.项目监督：项目号，员工编号，监督方式（网络监控，实地监控，电话询问，），监督要求（责任，使命，安全，效率），监督内容（一期工程监督，二期工程监督，三期工程监督），监督目的（保障质量，保障工期，保障设备）5.设备管理：设备号（01，02，03, 04，05），员工编号，设备名称（挖掘机，拖拉机，推土机，勘探仪器，装潢仪器），设备数量（20，20,20，5，6），设备保养状况（良好，好，维修中，使用中），使用日期，归还日期（未归还，已归还）6.宣传：宣传方案编号（），部门编号，宣传方式（电视，网络，广播，发传单，人工宣传），宣传时间，宣传地点，宣传对象（富二代，青年，中年人，家长，老人）7.顾客：顾客号，顾客名，联系方式，家庭住址，购房日期，付款方式，是否是会员8.销售：项目号，顾客号，销售日期，销售单价，销售数量，打折9.售后服务：顾客号，员工编号，房屋故障服务（没自来水，下水道堵塞，没有电，没有气，），满意度调查（好，中，差），走访服务（定期检查，不定期检查），顾客服务次数，服务响应时间，售后服务意见（业务不熟，业务一般，业务很熟）二、数据库的逻辑结构设计三、数据库的概念结构设计图一项目计划E-R图图二部门类别E-R图图三项目实施人员E-R图图四项目监督E-R图图五设备管理信息E-R图图六宣传管理信息E-R图图七顾客信息E-R图图八销售信息E-R图图九售后服务信息E-R图实体与实体之间的关系E-R图四、数据库的物理结构设计Create database房地产开发项目管理on primary(name='房产_管理',filename='D:\房产_管理.mdf', size=3MB,Maxsize=25MB,filegrowth=10%)Log on(name='房管_log',filename='D:\房管.ldf',size=3MB,maxsize=25MB,filegrowth=10%)use房地产开发项目管理Create table programe(pno int not null primary key, pname char(20)not null, possible char(20)not null, pfind char(20)not null, pexpence char(20)not null, predict char(20),pdatatime char(20)not null, progress char(20))create table class_department (dno int not null primary key, dname char(20)not null,did char(20)not null,dquantity char(20))Create table employee(eno int not null primary key, ename char(20)not null,sex char(20)not nullcheck(sex='男'or sex='女'), position char(20)not null, degree char(20),pno int not null,eid char(20)not null,ephone int not null,foreign key(pno)references programe(pno))Create table inpect_programe (pno int not null,eno int not null,style char(20),demand char(20),context char(20),purpose char(20)not null, primary key(pno,eno),foreign key(pno)references programe(pno),foreign key(eno)references employee(eno))Create table control_programe (cno int not null,eno int not null,cpname char(20)not null, cpquantity char(20)not null, cpquality char(20)not null, cpdatetime char(20)not null, cpreturn char(20)not null, foreign key(eno)references employee(eno))Create table ad_programe(adno int primary key not null, dno int not null,style char(20)not null,adtime char(20),adplace char(20),adobject char(20),foreign key(dno)references employee(eno))Create table customer(cuno int not null primary key,cuname char(20)not null,phone char(20)not null,cuid char(20)not null,cutime char(20)not null,cupaystyle char(20)not null,member char(20))Create table sale(pno int not null,cuno int not null,stime char(20)not null,price bigint not null,quantity char(20)not null,discount char(20),primary key(pno,cuno),foreign key(pno)references programe(pno),foreign key(cuno)references customer(cuno))Create table serve(cuno int not null,eno int not null,trouble char(20),survey char(20),interview char(20),quantity char(20),needtime char(20)not null,suppose char(20)not null,primary key(cuno,eno),foreign key(cuno)references customer(cuno),foreign key(eno)references employee(eno))insert into programe values('01','电梯公寓','可行','高','贵阳市龙洞堡','热销','2010-11-25','正在施工')insert into programe values('02','爱情公寓','可行','高','贵阳市小河区','热销','2010-1-15','正在勘测')insert into programe values('03','别墅','难度大','高','贵阳市蔡家关','滞销','2010-10-15','已完成')insert into programe values('04','商品房','可行','低成本','贵阳市喷水池','热销','2010-8-15','正在施工')insert into programe values('05','养老院','可行','高','贵阳市龙洞堡','滞销','2010-11-25','正在勘测')insert into programe values('06','幼儿园','不可行','高','贵阳市蔡家关','滞销','2010-11-15','正在施工')insert into class_department values('1','管理部','北京','4')insert into class_department values('2','项目执行部','贵阳','11') insert into class_department values('3','宣传部','成都','5')insert into class_department values('4','销售部','贵阳','5')insert into class_department values('5','服务部','重庆','5')insert into employee values('001','李亚楠','男','CEO','A','1','河南','0851001')insert into employee values('002','王小慧','女','总经理','B','1','江西','0851002')insert into employee values('003','杨国春','男','总经理','B','1','山东','0851003')insert into employee values('004','马桃','女','总监','B','1','贵州','0851004')insert into employee values('005','高晓源','男','技术总监','C','2','河南','0851005')insert into employee values('006','余志斌','男','技术总监','C','2','江西','0851006')insert into employee values('007','章娣','女','技术总监','C','2','湖北','0851007')insert into employee values('008','乔升','男','技术总监','C','2','山东','0851008')insert into employee values('009','夏转','女','项目总监','C','2','湖北','0851009')insert into employee values('010','王勇','男','技术总监','C','2','贵州','0851010')insert into employee values('011','陈易懿','男','技术总监','C','2','江西','0851011')insert into employee values('012','王艺栋','男','项目总监','C','2','江西','0851012')insert into employee values('013','何鹏','男','技术总监','C','2','贵州','0851013')insert into employee values('014','王启蒙','男','项目总监','C','2','湖南','0851014')insert into employee values('015','陈文斌','男','技术总监','C','2','江西','0851015')insert into employee values('016','魏伟','男','宣传经理','D','3','山东','0851016')insert into employee values('017','何运','男','宣传经理','D','3','贵州','0851017')insert into employee values('018','王贵军','男','宣传经理','D','3','湖南','0851018')insert into employee values('019','陈宏','男','宣传助理','D','3','贵州','0851019')insert into employee values('020','万仁','男','宣传助理','D','3','江西','0851020')insert into employee values('021','黄晋','男','销售经理','D','4','河南','0851021')insert into employee values('022','何宴华','男','销售经理','D','4','贵州','0851022')insert into employee values('023','乐珊','男','销售助理','D','4','江西','0851023')insert into employee values('024','杨杰','男','销售助理','D','4','重庆','0851024')insert into employee values('025','顾月平','男','销售经理','D','4','江苏','0851025')insert into employee values('026','任鹏飞','男','服务经理','E','5','山东','0851026')insert into employee values('027','黄忠刚','男','服务经理','E','5','河南','0851027')insert into employee values('028','唐伟','男','服务经理','E','5','湖南','0851028')insert into employee values('029','窦威','男','服务经理','E','5','贵州','0851029')insert into employee values('030','刘孝峰','男','服务经理','E','5','湖南','0851030')insert into inpect_programe values('01','002','网络监控','责任','一期工程','保障工期')insert into inpect_programe values('02','003','实地监控','使命','二期工程','保障质量')insert into inpect_programe values('03','004','电话询问','安全','三期工程','保障设备')insert into inpect_programe values('04','008','网路监控','效率','一期工程','保障工期')insert into inpect_programe values('05','009','实地监控','安全','二期工程','保障质量')insert into inpect_programe values('06','012','电话询问','安全','三期工程','保障工期')insert into control_programe values('01','010','挖掘机','20','良好','三个月','未归还')insert into control_programe values('02','011','拖拉机','20','好','三个月','未归还')insert into control_programe values('03','012','推土机','20','维修中','四个月','已归还')insert into control_programe values('04','013','探测仪','5','良好','两个月','未归还')insert into control_programe values('05','015','装潢仪器','6','良好','四个月','已归还')insert into ad_programe values('0001','3','电视','2012','贵阳','富二代，老人')insert into ad_programe values('0002','3','网络','2011','成都','青年') insert into ad_programe values('0003','3','广播','2013','重庆','中年人')insert into ad_programe values('0004','2','发传单','2013','北京','家长，孩子')insert into ad_programe values('0005','3','人工宣传','2012','贵阳','老人')insert into customer values('101','龙国荣','8233301','兴义','2009-10-01','现金支付','否')insert into customer values('102','邓亮','8233302','遵义','2010-11-09','分期付款','否')insert into customer values('103','陈银龙','8233303','重庆','2009-09-02','分期付款','否')insert into customer values('104','杨秀海','8233304','凯里','2009-08-07','现金支付','否')insert into customer values('105','楚浩','8233305','湖南','2009-05-05','分期付款','否')insert into customer values('106','王志文','8233306','兴义','2010-10-02','分期付款','否')insert into sale values('01','103','2009-09-02','500000','1','无') insert into sale values('02','104','2009-08-07','800000','2','无') insert into sale values('03','102','2010-11-09','1000000','1','无') insert into sale values('02','106','2010-10-02','600000','2','无') insert into sale values('04','105','2009-05-05','680000','1','无') insert into sale values('01','101','2009-10-01','900000','3','9.5折')insert into serve values('102','026','没自来水','好','定期检查','2','2小时','业务一般')insert into serve values('101','027','下水道堵塞','差','不定期检查','5','5小时','业务不熟')insert into serve values('104','029','没电','中','定期检查','3','3小时','业务一般')insert into serve values('106','030','没气','差','不定期检查','4','12小时','业务不熟')insert into serve values('103','026','没自来水','中','定期检查','18','20小时','业务不熟')insert into serve values('105','029','没电','差','定期检查','6','21小时','业务不熟')--查询数据库中的各表信息select*from programeselect*from class_departmentselect*from employeeselect*from inpect_programeselect*from control_programeselect*from ad_programeselect*from customerselect*from saleselect*from serve五、数据库的功能扩展的实现--创建并查询技术总监是男性的视图create view empasselect*from employeewhere sex='男'and position='技术总监'select*from emp--创建职位是总经理、项目总监等的存储过程create procedure sp_epy@position char(20)asselect*from employeewhere position=@positionEXEC sp_epy '总经理'EXEC sp_epy '项目总监'--创建sale 表中当price大于时将customer表的member变成“是”的触发器create trigger price_memberon salefor updateasbeginupdate customerset member='是'from customer c, deleted d where c.cuno=d.cuno and d.price>1000000 end--将顾客邓亮买的别墅的价格增加，以实现触发器的功能update saleset price=1100000where pno=03。