數(shù)據(jù)庫(kù)課程設(shè)計(jì)宿舍管理系統(tǒng)

下載文檔

下載文檔到電腦，查找使用更方便還剩頁(yè)未讀，繼續(xù)閱讀>>

侵權(quán)申訴舉報(bào)

1 / 14

此文檔下載收益歸作者所有下載文檔

版權(quán)提示

文本預(yù)覽

常見(jiàn)問(wèn)題

數(shù)據(jù)庫(kù)設(shè)計(jì)說(shuō)明書(shū)題目：宿舍管理系統(tǒng)二級(jí) 學(xué) 院：汽車(chē)與電子工程學(xué)院年級(jí)、專(zhuān)業(yè)：2010級(jí)計(jì)算機(jī)科學(xué)與技術(shù)學(xué) 生姓名：學(xué) 號(hào)：指導(dǎo) 教師：完成時(shí) 間：2011年12月20日目錄1 系統(tǒng)需求分析 31.1 系統(tǒng)功能 31.2 系統(tǒng)功能 42 數(shù)據(jù)庫(kù)概念結(jié)構(gòu)設(shè)計(jì) 52.1 數(shù)據(jù)庫(kù)的整體實(shí)體關(guān)系圖 52.2 數(shù)據(jù)庫(kù)的的實(shí)體E-R圖 63 數(shù)據(jù)庫(kù)邏輯結(jié)構(gòu)設(shè)計(jì) 94 數(shù)據(jù)庫(kù)及的表實(shí)現(xiàn)（代碼） 101 系統(tǒng)需求分析系統(tǒng)采用MICROSOFT公司的 VISUAL BASIC語(yǔ)言編寫(xiě)的學(xué)生公寓管理系統(tǒng),本系統(tǒng)在研制開(kāi)發(fā)過(guò)程中，嚴(yán)格遵循軟件工程方法，完全采用結(jié)構(gòu)化程序設(shè)計(jì)方法本系統(tǒng)的主要功能可以方便的對(duì)學(xué)生宿舍進(jìn)行管理，系統(tǒng)采用Microsoft SQL Server 2005數(shù)據(jù)庫(kù)使數(shù)據(jù)具有較強(qiáng)的完整性、較好的數(shù)據(jù)安全性等特點(diǎn)以及提供了標(biāo)準(zhǔn)的幫助，使用戶(hù)方便的獲得所需的幫助界面友好、操作簡(jiǎn)單、功能齊全、具有較好的人機(jī)接口界面是本系統(tǒng)的最大優(yōu)點(diǎn)1.1 系統(tǒng)功能宿舍管理系統(tǒng)的后臺(tái)網(wǎng)站系統(tǒng)的功能結(jié)構(gòu)，如下圖所示學(xué)生宿舍管理系統(tǒng)系統(tǒng)登錄模塊系統(tǒng)控制模塊系統(tǒng)控制模塊舍區(qū)管理模塊房間管理模塊系統(tǒng)管理模塊打印數(shù)據(jù)模塊門(mén)衛(wèi)登記模塊水費(fèi)管理模塊電費(fèi)管理模塊數(shù)據(jù)備份模塊1.2 系統(tǒng)功能宿舍管理系統(tǒng)的前臺(tái)網(wǎng)站系統(tǒng)的功能結(jié)構(gòu)，如下圖所示。

學(xué)生宿舍管理系統(tǒng)系統(tǒng)登錄模塊系統(tǒng)控制模塊系統(tǒng)登錄模塊用戶(hù)登錄界面來(lái)訪查看界面宿舍留言界面宿舍編號(hào)欄登錄信息界面來(lái)訪時(shí)間欄信息查詢(xún)界面離開(kāi)時(shí)間欄2 數(shù)據(jù)庫(kù)概念結(jié)構(gòu)設(shè)計(jì) 2.1 數(shù)據(jù)庫(kù)的整體實(shí)體關(guān)系圖本網(wǎng)站的實(shí)體有：用戶(hù)，樓管，舍區(qū)，電費(fèi)，房間，宿舍成員，水費(fèi)，數(shù)據(jù)庫(kù)的整體實(shí)體關(guān)系如下圖所示用戶(hù)水費(fèi)上繳屬于屬于舍區(qū)電費(fèi)房間宿舍成員從屬樓管上繳管理 2.2 數(shù)據(jù)庫(kù)的的實(shí)體E-R圖舍區(qū)電話號(hào)碼區(qū)名稱(chēng)舍區(qū)編號(hào)房間數(shù)床位數(shù)現(xiàn)住人數(shù)電費(fèi)樓房名稱(chēng)房間號(hào)舍區(qū)編號(hào)年份月份用電量超支量電話號(hào)碼水費(fèi)樓房名稱(chēng)房間號(hào)舍區(qū)編號(hào)年份月份用水量超支量電話號(hào)碼用戶(hù)密碼權(quán)限用戶(hù)名機(jī)密問(wèn)題答案創(chuàng)建日期宿舍成員樓房名稱(chēng)房間號(hào)舍區(qū)編號(hào)床位號(hào)學(xué)生姓名院系名稱(chēng)班級(jí)名稱(chēng)家庭地址電話號(hào)碼房間樓房名稱(chēng)房間號(hào)舍區(qū)編號(hào)舍長(zhǎng)床位數(shù)現(xiàn)住人數(shù)電話號(hào)碼來(lái)訪者來(lái)訪者姓名來(lái)訪者證件名稱(chēng)來(lái)訪者序號(hào)來(lái)訪者證件號(hào)碼受訪者姓名受訪者舍區(qū)號(hào)受訪者房間號(hào)關(guān)系來(lái)訪時(shí)間離開(kāi)時(shí)間3 數(shù)據(jù)庫(kù)邏輯結(jié)構(gòu)設(shè)計(jì)社區(qū)表列名?數(shù)據(jù)類(lèi)型?長(zhǎng)度?說(shuō)明?索引舍區(qū)編號(hào)?Int?4?為主鍵?主索引區(qū)名稱(chēng)?char?4?小于等于4的漢字?電話號(hào)碼?char?4?只能是數(shù)字與’-‘包括區(qū)號(hào)與號(hào)碼房間數(shù)?int8??床位數(shù)?int?8??現(xiàn)住人數(shù)?Int?8??電費(fèi)表：列名?數(shù)據(jù)類(lèi)型?長(zhǎng)度?說(shuō)明?索引舍區(qū)編號(hào)?Int?4?為主鍵?主索引樓房名稱(chēng)?Char?4為主鍵（只能是英文字母）?房間號(hào)?Int?4?為主鍵年份?Chat4??為主鍵（只能是數(shù)字）?月份?char4??為主鍵（只能是數(shù)字）?用電量?Int?8??超支量?Int?8??電話號(hào)碼?Char4?宿舍電話號(hào)碼?水費(fèi)表：列名?數(shù)據(jù)類(lèi)型?長(zhǎng)度?說(shuō)明?索引舍區(qū)編號(hào)?Int?4?為主鍵?主索引樓房名稱(chēng)?char?4為主鍵（只能是英文字母）?房間號(hào)?Int?4?為主鍵年份?Char4??為主鍵（只能是數(shù)字）?月份?Char4??為主鍵（只能是數(shù)字）?用水量?int?8??超支量?Int?8??電話號(hào)碼?Char4?宿舍電話號(hào)碼?用戶(hù)表：列名?數(shù)據(jù)類(lèi)型?長(zhǎng)度?說(shuō)明?索引用戶(hù)名?Char?4?為主鍵（中文或英文字母）?主索引密碼?char?4?權(quán)限?Int?4?機(jī)密問(wèn)題?char4???答案?Char4???創(chuàng)建日期?Datatime?8?用getdate()來(lái)設(shè)置默認(rèn)值?宿舍成員表：列名?數(shù)據(jù)類(lèi)型?長(zhǎng)度?說(shuō)明?索引舍區(qū)編號(hào)?int?4?為主鍵?主索引樓房名稱(chēng)?Char4為主鍵（只能是英文字母）?房間號(hào)?Int?4?為主鍵床位號(hào)??Int4??為主鍵?學(xué)生姓名?char10???院系名稱(chēng)char?20??班級(jí)名稱(chēng)?char?10??家庭地址?char50電話號(hào)碼char10?宿舍電話號(hào)碼（數(shù)字與-）?房間表：列名?數(shù)據(jù)類(lèi)型?長(zhǎng)度?說(shuō)明?索引舍區(qū)編號(hào)?Int?4?為主鍵?主索引樓房名稱(chēng)?Char4為主鍵（只能是英文字母）?房間號(hào)?Int?4?為主鍵舍長(zhǎng)?char10??床位數(shù)?char?20??現(xiàn)住人數(shù)?char?10??電話號(hào)碼?char10?宿舍電話號(hào)碼（數(shù)字與-）?來(lái)訪登記表：列名?數(shù)據(jù)類(lèi)型?長(zhǎng)度?說(shuō)明?索引來(lái)訪者序號(hào)?Int?4?為主鍵?主索引來(lái)訪者姓名?char15?來(lái)訪者證件名稱(chēng)?char?15?來(lái)訪者證件號(hào)碼??char20???受訪者姓名?char15???受訪者舍區(qū)號(hào)?Int?20??受訪者房間號(hào)?Int?10??關(guān)系?char50來(lái)訪時(shí)間?Datadate4??離開(kāi)時(shí)間?Datadate4備注char16 用戶(hù)日志表：列名?數(shù)據(jù)類(lèi)型?長(zhǎng)度?說(shuō)明?索引用戶(hù)名稱(chēng)?Char20為主鍵主索引錯(cuò)誤次數(shù)?Int?4??最后登錄時(shí)間Datadate10?4 數(shù)據(jù)庫(kù)及的表實(shí)現(xiàn)（代碼）創(chuàng)建數(shù)據(jù)庫(kù)及數(shù)據(jù)庫(kù)表的sql語(yǔ)句，如下所示。

//創(chuàng)建數(shù)據(jù)庫(kù)USE [master]GOCREATE DATABASE [學(xué)生宿舍管理系統(tǒng)] ON PRIMARY ( NAME = N'學(xué)生宿舍管理系統(tǒng)', FILENAME = N'E:\學(xué)習(xí)資料\《數(shù)據(jù)庫(kù)原理與應(yīng)用》大作業(yè)\新建文件夾\學(xué)生宿舍管理系統(tǒng).mdf' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ) LOG ON ( NAME = N'學(xué)生宿舍管理系統(tǒng)_log', FILENAME = N'E:\學(xué)習(xí)資料\《數(shù)據(jù)庫(kù)原理與應(yīng)用》大作業(yè)\新建文件夾\學(xué)生宿舍管理系統(tǒng)_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)GOALTER DATABASE [學(xué)生宿舍管理系統(tǒng)] SET COMPATIBILITY_LEVEL = 100GO//舍區(qū)表CREATE TABLE [dbo].[舍區(qū)表]( [舍區(qū)編號(hào)] [int] NOT NULL, [區(qū)名稱(chēng)] [char](10) NULL, [電話號(hào)碼] [char](10) NULL, [房間數(shù)] [int] NULL, [床位數(shù)] [int] NULL, [現(xiàn)住人數(shù)] [int] NULL, CONSTRAINT [PK_舍區(qū)表] PRIMARY KEY CLUSTERED ( [舍區(qū)編號(hào)] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]GOSET ANSI_PADDING OFF//電費(fèi)表CREATE TABLE [dbo].[電費(fèi)表]( [舍區(qū)編號(hào)] [int] NOT NULL, [樓房名稱(chēng)] [char](10) NOT NULL, [房間號(hào)] [int] NOT NULL, [年份] [char](10) NOT NULL, [月份] [char](10) NOT NULL, [用電量] [int] NULL, [超支量] [int] NULL, [電話號(hào)碼] [char](10) NULL, CONSTRAINT [PK_電費(fèi)表] PRIMARY KEY CLUSTERED ( [舍區(qū)編號(hào)] ASC, [樓房名稱(chēng)] ASC, [房間號(hào)] ASC, [年份] ASC, [月份] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]GOSET ANSI_PADDING OFFGOALTER TABLE [dbo].[電費(fèi)表] WITH CHECK ADD CONSTRAINT [FK_電費(fèi)表_房間表] FOREIGN KEY([舍區(qū)編號(hào)], [樓房名稱(chēng)], [房間號(hào)])REFERENCES [dbo].[房間表] ([舍區(qū)編號(hào)], [樓房名稱(chēng)], [房間號(hào)])GOALTER TABLE [dbo].[電費(fèi)表] CHECK CONSTRAINT [FK_電費(fèi)表_房間表]//水費(fèi)表GOCREATE TABLE [dbo].[水費(fèi)表]( [舍區(qū)編號(hào)] [int] NOT NULL, [樓房名稱(chēng)] [char](10) NOT NULL, [房間號(hào)] [int] NOT NULL, [年份] [char](10) NOT NULL, [月份] [char](10) NOT NULL, [用水量] [int] NULL, [超支量] [int] NULL, [電話號(hào)碼] [char](10) NULL, CONSTRAINT [PK_水費(fèi)表] PRIMARY KEY CLUSTERED ( [舍區(qū)編號(hào)] ASC, [樓房名稱(chēng)] ASC, [房間號(hào)] ASC, [年份] ASC, [月份] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]GOSET ANSI_PADDING OFFGOALTER TABLE [dbo].[水費(fèi)表] WITH CHECK ADD CONSTRAINT [FK_水費(fèi)表_房間表] FOREIGN KEY([舍區(qū)編號(hào)], [樓房名稱(chēng)], [房間號(hào)])REFERENCES [dbo].[房間表] ([舍區(qū)編號(hào)], [樓房名稱(chēng)], [房間號(hào)])GOALTER TABLE [dbo].[水費(fèi)表] CHECK CONSTRAINT [FK_水費(fèi)表_房間表]//用戶(hù)表CREATE TABLE [dbo].[用戶(hù)表]( [用戶(hù)名] [char](10) NOT NULL, [密碼] [char](10) NULL, [權(quán)限] [int] NULL, [機(jī)密問(wèn)題] [char](10) NULL, [答案] [char](10) NULL, [創(chuàng)建日期] [datetime] NULL, CONSTRAINT [PK_用戶(hù)表] PRIMARY KEY CLUSTERED ( [用戶(hù)名] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]GOSET ANSI_PADDING OFF//宿舍成員表CREATE TABLE [dbo].[宿舍成員表]( [舍區(qū)編號(hào)] [int] NOT NULL, [樓房名稱(chēng)] [char](10) NOT NULL, [房間號(hào)] [int] NOT NULL, [床位號(hào)] [int] NOT NULL, [學(xué)生姓名] [char](10) NULL, [院系名稱(chēng)] [char](10) NULL, [班級(jí)名稱(chēng)] [char](10) NULL, [家庭地址] [char](50) NULL, [電話號(hào)碼] [char](10) NULL, CONSTRAINT [PK_宿舍成員表] PRIMARY KEY CLUSTERED ( [舍區(qū)編號(hào)] ASC, [樓房名稱(chēng)] ASC, [房間號(hào)] ASC, [床位號(hào)] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]GOSET ANSI_PADDING OFFGOALTER TABLE [dbo].[宿舍成員表] WITH CHECK ADD CONSTRAINT [FK_宿舍成員表_房間表] FOREIGN KEY([舍區(qū)編號(hào)], [樓房名稱(chēng)], [房間號(hào)])REFERENCES [dbo].[房間表] ([舍區(qū)編號(hào)], [樓房名稱(chēng)], [房間號(hào)])GOALTER TABLE [dbo].[宿舍成員表] CHECK CONSTRAINT [FK_宿舍成員表_房間表]//房間表CREATE TABLE [dbo].[房間表]( [舍區(qū)編號(hào)] [int] NOT NULL, [樓房名稱(chēng)] [char](10) NOT NULL, [房間號(hào)] [int] NOT NULL, [舍長(zhǎng)] [char](10) NULL, [床位數(shù)] [char](10) NULL, [現(xiàn)住人數(shù)] [char](10) NULL, [電話號(hào)碼] [char](10) NULL, CONSTRAINT [PK_房間表] PRIMARY KEY CLUSTERED ( [舍區(qū)編號(hào)] ASC, [樓房名稱(chēng)] ASC, [房間號(hào)] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]GOSET ANSI_PADDING OFFGOALTER TABLE [dbo].[房間表] WITH CHECK ADD CONSTRAINT [FK_房間表_舍區(qū)表] FOREIGN KEY([舍區(qū)編號(hào)])REFERENCES [dbo].[舍區(qū)表] ([舍區(qū)編號(hào)])GOALTER TABLE [dbo].[房間表] CHECK CONSTRAINT [FK_房間表_舍區(qū)表]//來(lái)訪者登記表CREATE TABLE [dbo].[來(lái)訪登記表]( [來(lái)訪者序號(hào)] [int] NOT NULL, [來(lái)訪者姓名] [char](10) NOT NULL, [來(lái)訪者證件名稱(chēng)] [char](15) NULL, [來(lái)訪者證件號(hào)碼] [char](20) NULL, [受訪者姓名] [char](10) NULL, [受訪者舍區(qū)號(hào)] [int] NULL, [受訪者房間號(hào)] [int] NULL, [受訪者樓房名稱(chēng)] [char](10) NULL, [關(guān)系] [char](10) NULL, [來(lái)訪時(shí)間] [datetime] NULL, [離開(kāi)時(shí)間] [datetime] NULL, [備注] [text] NULL, CONSTRAINT [PK_來(lái)訪登記表] PRIMARY KEY CLUSTERED ( [來(lái)訪者序號(hào)] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]GOSET ANSI_PADDING OFFGOALTER TABLE [dbo].[來(lái)訪登記表] WITH CHECK ADD CONSTRAINT [FK_來(lái)訪登記表_房間表] FOREIGN KEY([受訪者舍區(qū)號(hào)], [受訪者樓房名稱(chēng)], [受訪者房間號(hào)])REFERENCES [dbo].[房間表] ([舍區(qū)編號(hào)], [樓房名稱(chēng)], [房間號(hào)])GOALTER TABLE [dbo].[來(lái)訪登記表] CHECK CONSTRAINT [FK_來(lái)訪登記表_房間表]//用戶(hù)日志表CREATE TABLE [dbo].[用戶(hù)日志表]( [用戶(hù)名稱(chēng)] [char](10) NOT NULL, [錯(cuò)誤次數(shù)] [int] NULL, [最后登錄時(shí)間] [datetime] NULL, CONSTRAINT [PK_用戶(hù)日志表] PRIMARY KEY CLUSTERED ( [用戶(hù)名稱(chēng)] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]GOSET ANSI_PADDING OFFGOALTER TABLE [dbo].[用戶(hù)日志表] WITH CHECK ADD CONSTRAINT [FK_用戶(hù)日志表_用戶(hù)表] FOREIGN KEY([用戶(hù)名稱(chēng)])REFERENCES [dbo].[用戶(hù)表] ([用戶(hù)名])GOALTER TABLE [dbo].[用戶(hù)日志表] CHECK CONSTRAINT [FK_用戶(hù)日志表_用戶(hù)表]。

點(diǎn)擊閱讀更多內(nèi)容