用户包含学生信息包含楼栋信息包含寝室 关系设计
2.逻辑数据库设计
用户表：UserInfo(Name,Duties,ID,Password)
学生表：StudentInfo(ID,Name,Sex,Major,Nation,RoomNo) 寝室表：RoomInfo(RoomNo,FloorNo,SZname,Tel) 维修表：RepairInfo(RoomNo,FloorNo,Date,WXInfo) 楼栋表：FloorInfo(FloorNo,ManagerNo,Tel) 违规表：BreaInfo(Name,ID,Date,WGInfo) 卫生表：Invent(FloorNo,Week1,RoomNo) 通知表：Massage(ID,Massage,Title) 3.数据库优化 3.1触发器
创建触发器，当向学生信息表插入数据时也向用户表插入数据。
create trigger TRI_INSERT on StudentInfo for insert as
declare @snum char(8),@sname char(10) set @snum= (select ID from inserted) set @sname= (select Name from inserted) insert into login values(@sname,@snum,@snum)
3.2完整性约束
3.2.1实体完整性设置
UserInfo，ID; StudentInfo,ID; RoomInfo,RoomNo; FloorInfo,FloorNo;
3.2.2参照完整性设置 3.2.2.1违规信息表
ALTER TABLE [dbo].[BreaInfo] WITH CHECK ADD CONSTRAINT [FK_BreaInfo_StudentInfo] FOREIGN KEY([ID])
6
REFERENCES [dbo].[StudentInfo] ([ID]) GO
3.2.2.2卫生登记表
ALTER TABLE [dbo].[Invent] WITH CHECK ADD CONSTRAINT [FK_Invent_RoomInfo1] FOREIGN KEY([RoomNo])
REFERENCES [dbo].[RoomInfo] ([RoomNo]) GO
ALTER TABLE [dbo].[Invent] CHECK CONSTRAINT [FK_Invent_FloorInfo] GO
ALTER TABLE [dbo].[Invent] WITH CHECK ADD CONSTRAINT [FK_Invent_FloorInfo1] FOREIGN KEY([FloorNo])
REFERENCES [dbo].[FloorInfo] ([FloorNo]) GO
ALTER TABLE [dbo].[Invent] CHECK CONSTRAINT [FK_Invent_FloorInfo1] GO
3.2.3用户自定义完整性设置
ALTER TABLE [dbo].[StudentInfo] ADD CONSTRAINT [DF_StudentInfo_Nation] DEFAULT (N'汉') FOR [Nation] GO
ALTER TABLE [dbo].[UserInfo] ADD CONSTRAINT [DF_UserInfo_Duties] DEFAULT (N'学生') FOR [Duties] GO
ALTER TABLE Invent
ADD constraint [ck_inv] CHECK(week1>0 and week1<=20)
四、 项目数据库设计
1.web.config配置文件
7
PublicKeyToken=31bf3856ad364e35\ />
Source=USERSWO-0I96CA3;Initial Catalog=Dorm;Integrated Security=True\ providerName=\System.Data.SqlClient\/>
Source=USERSWO-0I96CA3;Initial Catalog=Dorm;Integrated Security=True\ providerName=\System.Data.SqlClient\/>
type=\System.Web.UI.DataVisualization.Charting.ChartHttpHandler, System.Web.DataVisualization, Version=4.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35\ validate=\false\ />
Culture=neutral, PublicKeyToken=31BF3856AD364E35\/>
2.母版页面
<%@ Master Language=\ AutoEventWireup=\ CodeFile=\ Inherits=\ %>
8