中國石油大學數(shù)據(jù)庫課程設(shè)計

中國石油大學（北京）遠程教育學院期末考試《數(shù)據(jù)庫課程設(shè)計》學習中心：姓名：學號：_關(guān)于課程考試違規(guī)作弊的說明1、提交文件中涉嫌抄襲內(nèi)容（包括抄襲網(wǎng)上、書籍、報刊雜志及其他已有論文），帶有明顯外校標記，不符合學院要求或?qū)W生本人情況，或存在查明出處的內(nèi)容或其他可疑字樣者，判為抄襲，成績?yōu)椤?”。2、兩人或兩人以上答題內(nèi)容或用語有50%以上相同者判為雷同，成績?yōu)椤?”。3、所提交試卷或材料沒有對老師題目進行作答或提交內(nèi)容與該課程要求完全不相干者，認定為“白卷”或“錯卷”，成績?yōu)椤?”。一、備注：提交一份數(shù)據(jù)庫應用系統(tǒng)的設(shè)計報告，報告內(nèi)容參見第二章數(shù)據(jù)庫設(shè)計中的范例——汽車修理廠信息管理系統(tǒng)內(nèi)容要求如下：從應用系統(tǒng)選題中選擇一個管理系統(tǒng)；（應用系統(tǒng)選題從以下20題里任選一題）概念模型設(shè)計（要求用E-R圖畫出概念模型）⑴確定實體及其主碼、屬性；⑵確定類型的類型及其屬性；邏輯結(jié)構(gòu)設(shè)計⑴定義數(shù)據(jù)庫中表的所有屬性及其類型、寬度；⑵定義關(guān)系模式及其主碼、外部碼；⑶定義視圖，說明每個視圖的作用；行為設(shè)計⑴說明查詢文件的作用；⑵說明表單文件的作用；⑶說明報表文件的作用；二、題目11、房屋中介公司售房信息系統(tǒng)數(shù)據(jù)庫主要包括如下內(nèi)容：現(xiàn)有房源情況、買賣房屋者的基本情況、交易情況等。該系統(tǒng)需實現(xiàn)三個主要功能：各種信息的維護、各種信息的查詢、各種信息的統(tǒng)計。、概念模型設(shè)計1）合并各分Ｅ－Ｒ圖,消除屬性沖突、命名沖突、結(jié)構(gòu)沖突等三類沖突，得到初步E-R圖，再消除不必要冗余，得到的基本E-R圖如下所示：（2）各E－R圖各實體的屬性如下所示：房源：房源（編號,城區(qū),環(huán)境,付款,來源,委托,產(chǎn)權(quán),狀態(tài)）客源：客源（客戶編號,客戶姓名,客戶電話,電子郵箱,客戶地址,具體位置,產(chǎn)權(quán)性質(zhì),最低購價,最高購價,最小面積,最大面積,委托日期,截止日期,錄入人員,當前狀態(tài)）職員：職員（職員編號,姓名,性別,身份證號,學歷,職務,所在部門,出生日期,聯(lián)系電話,聯(lián)系電話,家庭住址,電子郵箱,備注,簡歷）各E－R圖中聯(lián)系的屬性如下所示：銷售信息：銷售信息（售房編號,編號,具體位置,配套環(huán)境,房,廳,土地性質(zhì),產(chǎn)權(quán)性質(zhì),價格,產(chǎn)權(quán)名稱,產(chǎn)權(quán)編號,業(yè)主姓名,業(yè)主電話,聯(lián)系人,聯(lián)系電話,委托日期,截止日期,錄入人員,當前狀態(tài),備注）二、邏輯結(jié)構(gòu)設(shè)計關(guān)系模式房源信息，客源信息不存在非主屬性對主屬性的部分函數(shù)依賴，也不存在傳遞函數(shù)依賴，已經(jīng)達到了3NF，但是購房信息，租房信息中存在著一些數(shù)據(jù)冗余。（一）數(shù)據(jù)庫模式定義表2.1房源信息表列名數(shù)據(jù)類型可否為空說明列名數(shù)據(jù)類型可否為空說明編號Varcharnotnull房屋編號城區(qū)Nvarcharnotnull房屋所在城區(qū)環(huán)境Nvarchar房屋所在的環(huán)境付款Nchar房屋付款來源Nvarchar房屋來源委托NcharNotnull房屋委托產(chǎn)權(quán)NcharNotnull房屋產(chǎn)權(quán)狀態(tài)NcharNotnull房屋狀態(tài)表2.2客源信息表列名數(shù)據(jù)類型可否為空說明客戶編號Charnotnull客戶編號客戶姓名Nvarcharnotnull客戶姓名客戶電話Char客戶電話電子郵箱Char客戶郵箱客戶地址Nvarchar客戶地址具體位置Nvarchar具體位置產(chǎn)權(quán)性質(zhì)Nvarchar產(chǎn)權(quán)性質(zhì)最低購價Money最低購價最高購價Money最高購價最小面積Int最小面積最大面積Int最大面積委托日期Datetime委托日期截止日期Datetime截止日期錄入人員Nvarchar錄入人員當前狀態(tài)NcharNotnull當前狀態(tài)表2.3職員信息表列名數(shù)據(jù)類型可否為空說明職員編號Charnotnull職員編號姓名Nvarchar職員姓名性別CharNotnull職員性別身份證號Charnotnull職員身份證號學歷Nchar職員學歷職務Nvarchar職員職務所在部門Nvarchar職員所在部門出生日期Datetime職員出生日期聯(lián)系電話Char職員聯(lián)系電話家庭住址Nvarchar職員家庭住址電子郵箱Nvarchar職員電子郵箱備注Nvarchar職員備注簡歷Nvarchar職員簡歷表2.4操作員信息表列名數(shù)據(jù)類型可否為空說明賬號Charnotnull賬號密碼varcharnotnull密碼表2.5售房信息表列名數(shù)據(jù)類型可否為空說明售房編號Charnotnull售房編號編號Varcharnotnull編號具體位置Nvarchar具體位置配套環(huán)境Nvarchar配套環(huán)境房Int房間廳Int客廳土地性質(zhì)Nvarchar土地性質(zhì)產(chǎn)權(quán)性質(zhì)Nvarchar產(chǎn)權(quán)性質(zhì)價格Money價格產(chǎn)權(quán)名稱Nvarchar產(chǎn)權(quán)名稱產(chǎn)權(quán)編號Char產(chǎn)權(quán)編號業(yè)主姓名NcharNotnull業(yè)主姓名業(yè)主電話Char業(yè)主電弧委托日期Datetime委托日期截止日期Datetime截止日期錄入人員Nvarchar錄入人員當前狀態(tài)Char當前狀態(tài)備注Nvarchar備注表2.6租房信息表列名數(shù)據(jù)類型可否為空說明租房編號Charnotnull租房編號編號Charnotnull編號土地性質(zhì) 土地性質(zhì) Nvarchar l 土地性質(zhì)具體位置Nvarchar具體位置配套環(huán)境Nvarchar配套環(huán)境房Int房間廳Int客廳出租價格Money出租價格出租底價Money出租底價業(yè)主姓名Nvarchar業(yè)主姓名業(yè)主電話Char業(yè)主電話聯(lián)系人Nchar聯(lián)系人聯(lián)系電話Char聯(lián)系電話委托日期Datetime委托日期截止日期Datetime截止日期錄入人員Nvarchar錄入人員當前狀態(tài)CharNotnull當前狀態(tài)備注Nvarchar備注二）用戶子模式定義表2.7用戶子模式定義編號用戶子模式（View）作用（共性：提供數(shù)據(jù)保密和安全保護機制）V－1可售房源視圖便于查詢可售房源V－2可租房源視圖便于查詢可租房源V－3各房態(tài)統(tǒng)計視圖便于查看所有房屋的狀態(tài)V－4已售房統(tǒng)計視圖便于查看已售房屋信息V－5已租房統(tǒng)計視圖便于查看已租房屋信息V－6待售房統(tǒng)計視圖便于查看待售房屋信息V－7待租房統(tǒng)計視圖便于查看待租房屋信息V－8求購客源視圖便于查看客源信息表2.8可售房源視圖列名數(shù)據(jù)類型可否為空說明編號Charnotnull房屋編號具體位置Nvarchar具體位置配套環(huán)境Nvarchar配套環(huán)境房Int房間廳Int客廳產(chǎn)權(quán)性質(zhì)Nvarchar產(chǎn)權(quán)性質(zhì)價格Money價格權(quán)證名稱Nvarchar權(quán)證名稱權(quán)證編號Char權(quán)證編號業(yè)主姓名NvarcharNotnull業(yè)主姓名業(yè)主電話Char業(yè)主電弧聯(lián)系人Nchar聯(lián)系人聯(lián)系電話Char聯(lián)系電話委托日期Datetime委托日期截止日期Datetime截止日期錄入人員Nvarchar錄入人員當前狀態(tài)Char當前裝他表2.9可租房源視圖列名數(shù)據(jù)類型可否為空說明編號Charnotnull編號具體位置Nvarchar具體位置配套環(huán)境Nvarchar配套環(huán)境房Int房間廳Int客廳出租價格Money出租價格出租底價Money出租底價業(yè)主姓名Nvarchar業(yè)主姓名業(yè)主電話Char業(yè)主電話聯(lián)系人Nvarchar聯(lián)系人聯(lián)系電話Char聯(lián)系電話委托日期Datetime委托日期截止日期Datetime截止日期錄入人員Nvarchar錄入人員當前狀態(tài)Char當前狀態(tài)表2.10各房態(tài)統(tǒng)計視圖列名數(shù)據(jù)類型可否為空說明待售房數(shù)Int待售房數(shù)已售房數(shù)Int已售房數(shù)待租房數(shù)Int待租房數(shù)已租房數(shù)Int、已租房數(shù)

表2.11已售房統(tǒng)計視圖列名數(shù)據(jù)類型可否為空說明編號Charnotnull編號已售房數(shù)Int已售房數(shù)表2.12已租房統(tǒng)計視圖列名數(shù)據(jù)類型可否為空說明編號Charnotnull編號已租房數(shù)Int已租房數(shù)表2.13待售房統(tǒng)計視圖列名數(shù)據(jù)類型可否為空說明編號Charnotnull編號待售房數(shù)Int待售房數(shù)表2.14待租房統(tǒng)計視圖列名數(shù)據(jù)類型可否為空說明編號Charnotnull編號待租房數(shù)Int待租房數(shù)表2.15求購客源視圖列名數(shù)據(jù)類型可否為空說明客戶編號Charnotnull客戶編號客戶姓名Nvarchar客戶姓名客戶電話Char客戶電話電子郵箱Nvarchar電子郵箱客戶地址Nvarchar客戶地址具體位置Nvarchar具體位置產(chǎn)權(quán)性質(zhì)Nvarchar產(chǎn)權(quán)性質(zhì)最低購價Money最低購價最高購價Money最高購價最小面積Char最小面頰最大面積Char最大面積委托日期Datetime委托日期截止日期Datetime截止日期錄入人員Nvarchar錄入人員當前狀態(tài)Char當前狀態(tài)Char當前狀態(tài)三、行為設(shè)計(一)建立數(shù)據(jù)庫、數(shù)據(jù)表、視圖、索引1、建立數(shù)據(jù)庫CREATEDATABASE[房屋中介管理系統(tǒng)]ON(NAME=N'房屋中介管理系統(tǒng)_data',FILENAME=房屋中介管理系統(tǒng)房屋中介管理系統(tǒng)_data.mdf',SIZE=2,MAXSIZE=50,FILEGROWTH=10%)LOGON(NAME=N'房屋中介管理系統(tǒng)_data_log',FILENAME=房屋中介管理系統(tǒng)房屋中介管理系統(tǒng)_data_log.ldf',SIZE=2,MAXSIZE=50,FILEGROWTH=10%)COLLATEChinese_PRC_CI_ASGO2、建立數(shù)據(jù)表房源信息表的建立：CREATETABLE[dbo].[房源信息表]([編號][varchar](4)COLLATEChinese_PRC_CI_ASNOTNULL,[城區(qū)][nvarchar](35)COLLATEChinese_PRC_CI_ASNOTNULL,[環(huán)境][nvarchar](20)COLLATEChinese_PRC_CI_ASNULL,[付款][nchar](10)COLLATEChinese_PRC_CI_ASNULL,[來源][nvarchar](32)COLLATEChinese_PRC_CI_ASNULL,[委托][nchar](20)COLLATEChinese_PRC_CI_ASNOTNULL,[產(chǎn)權(quán)][nchar](15)COLLATEChinese_PRC_CI_ASNOTNULL,[狀態(tài)][nchar](5)COLLATEChinese_PRC_CI_ASNOTNULL)ON[PRIMARY]GO客源信息表的建立：ifexists(select*fromdbo.sysobjectswhereid=object_id(N'[dbo].[客源信息表]')andOBJECTPROPERTY(id,N'IsUserTable')=1)droptable[dbo].[客源信息表]GOCREATETABLE[dbo].[客源信息表]([客戶編號][char](6)COLLATEChinese_PRC_CI_ASNOTNULL,[客戶姓名][nvarchar](10)COLLATEChinese_PRC_CI_ASNULL,[客戶電話][char](12)COLLATEChinese_PRC_CI_ASNULL,[電子郵箱][char](25)COLLATEChinese_PRC_CI_ASNULL,[客戶地址][nvarchar](30)COLLATEChinese_PRC_CI_ASNULL,[具體位置][nvarchar](50)COLLATEChinese_PRC_CI_ASNULL,[產(chǎn)權(quán)性質(zhì)][nvarchar](15)COLLATEChinese_PRC_CI_ASNULL,[最低購價][money]NULL,[最高購價][money]NULL,[最小面積][int]NULL,[最大面積][int]NULL,[委托日期][datetime]NULL,[截止日期][datetime]NULL,[錄入人員][nvarchar](6)COLLATEChinese_PRC_CI_ASNULL,[當前狀態(tài)][nchar](2)COLLATEChinese_PRC_CI_ASNOTNULL)ON[PRIMARY]GO職員信息表的建立：ifexists(select*fromdbo.sysobjectswhereid=object_id(N'[dbo].[FK__操作員信息表__賬號__681373AD]')andOBJECTPROPERTY(id,N'IsForeignKey')=1)ALTERTABLE[dbo].[操作員信息表]DROPCONSTRAINTFK__操作員信息表__賬號__681373ADGOifexists(select*fromdbo.sysobjectswhereid=object_id(N'[dbo].[職員信息表]')andOBJECTPROPERTY(id,N'IsUserTable')=1)droptable[dbo].[職員信息表]GOCREATETABLE[dbo].[職員信息表]([職員編號][char](3)COLLATEChinese_PRC_CI_ASNOTNULL,[姓名][nvarchar](5)COLLATEChinese_PRC_CI_ASNULL,[性別][char](2)COLLATEChinese_PRC_CI_ASNULL,[身份證號][char](18)COLLATEChinese_PRC_CI_ASNOTNULL,[學歷][nchar](6)COLLATEChinese_PRC_CI_ASNULL,[職務][nvarchar](15)COLLATEChinese_PRC_CI_ASNULL,[所在部門][nvarchar](20)COLLATEChinese_PRC_CI_ASNULL,[出生日期][datetime]NULL,[聯(lián)系電話][char](12)COLLATEChinese_PRC_CI_ASNULL,[家庭住址][nvarchar](25)COLLATEChinese_PRC_CI_ASNULL,[電子郵箱][nvarchar](20)COLLATEChinese_PRC_CI_ASNULL,[備注][nvarchar](50)COLLATEChinese_PRC_CI_ASNULL,[簡歷][nvarchar](50)COLLATEChinese_PRC_CI_ASNULL)ON[PRIMARY]GO售房信息表的建立：ifexists(select*fromdbo.sysobjectswhereid=object_id(N'[dbo].[售房信息表]')andOBJECTPROPERTY(id,N'IsUserTable')=1)droptable[dbo].[售房信息表]GOCREATETABLE[dbo].[售房信息表]([售房編號][char](3)COLLATEChinese_PRC_CI_ASNOTNULL,[編號][varchar](4)COLLATEChinese_PRC_CI_ASNOTNULL,[具體位置][nvarchar](50)COLLATEChinese_PRC_CI_ASNULL,[配套環(huán)境][nvarchar](20)COLLATEChinese_PRC_CI_ASNULL,[房][int]NULL,[廳][int]NULL,[土地性質(zhì)][nvarchar](12)COLLATEChinese_PRC_CI_ASNULL,[產(chǎn)權(quán)性質(zhì)][nvarchar](15)COLLATEChinese_PRC_CI_ASNULL,[價格][money]NULL,[權(quán)證名稱][nvarchar](35)COLLATEChinese_PRC_CI_ASNULL,[權(quán)證編號][char](6)COLLATEChinese_PRC_CI_ASNOTNULL,[業(yè)主姓名][nchar](6)COLLATEChinese_PRC_CI_ASNULL,[業(yè)主電話][char](12)COLLATEChinese_PRC_CI_ASNULL,[聯(lián)系人][nchar](6)COLLATEChinese_PRC_CI_ASNULL,[聯(lián)系電話][char](12)COLLATEChinese_PRC_CI_ASNULL,[委托日期][datetime]NULL,[截止日期][datetime]NULL,[錄入人員][nvarchar](6)COLLATEChinese_PRC_CI_ASNULL,[當前狀態(tài)][nchar](2)COLLATEChinese_PRC_CI_ASNOTNULL,[備注][nvarchar](50)COLLATEChinese_PRC_CI_ASNULL)ON[PRIMARY]GO租房信息表的建立：ifexists(select*fromdbo.sysobjectswhereid=object_id(N'[dbo].[租房信息表]')andOBJECTPROPERTY(id,N'IsUserTable')=1)droptable[dbo].[租房信息表]GOCREATETABLE[dbo].[租房信息表]([租房編號][char](3)COLLATEChinese_PRC_CI_ASNOTNULL,[編號][varchar](4)COLLATEChinese_PRC_CI_ASNOTNULL,[具體位置][nvarchar](50)COLLATEChinese_PRC_CI_ASNULL,[配套環(huán)境][nvarchar](20)COLLATEChinese_PRC_CI_ASNULL,[房][int]NULL,[廳][int]NULL,[出租價格][money]NULL,[出租底價][money]NULL,[業(yè)主姓名][nvarchar](6)COLLATEChinese_PRC_CI_ASNULL,[業(yè)主電話][char](12)COLLATEChinese_PRC_CI_ASNULL,[聯(lián)系人][nchar](6)COLLATEChinese_PRC_CI_ASNULL,[聯(lián)系電話][char](12)COLLATEChinese_PRC_CI_ASNULL,[委托日期][datetime]NULL,[截止日期][datetime]NULL,[錄入人員][nvarchar](6)COLLATEChinese_PRC_CI_ASNULL,[當前狀態(tài)][nchar](2)COLLATEChinese_PRC_CI_ASNOTNULL,[備注][nvarchar](50)COLLATEChinese_PRC_CI_ASNULL)ON[PRIMARY]GO操作員信息表的建立：ifexists(select*fromdbo.sysobjectswhereid=object_id(N'[dbo].[操作員信息表]')andOBJECTPROPERTY(id,N'IsUserTable')=1)droptable[dbo].[操作員信息表]GOCREATETABLE[dbo].[操作員信息表]([賬號][char]（3）COLLATEChinese_PRC_CI_ASNOTNULL,[密碼][varchar]（8）COLLATEChinese_PRC_CI_ASNOTNULL）ON[PRIMARY]GO3、建立視圖（1）用于查詢可售房源視圖定義如下：CREATEVIEW可售房源視圖ASSELECT編號,具體位置,配套環(huán)境,房,廳,土地性質(zhì),產(chǎn)權(quán)性質(zhì),價格,權(quán)證名稱,權(quán)證編號,業(yè)主姓名,業(yè)主電話,聯(lián)系人,聯(lián)系電話,委托日期,截止日期,錄入人員,當前狀態(tài)FROM售房信息表WHERE當前狀態(tài)='待售'（2）用于查詢可租房源視圖定義如下：CREATEVIEW可租房源視圖ASSELECT編號,具體位置,配套環(huán)境,房,廳,出租價格,出租底價,業(yè)主姓名,業(yè)主電話,聯(lián)系人,聯(lián)系電話,委托日期,截止日期,錄入人員,當前狀態(tài)FROM租房信息表WHERE當前狀態(tài)='待租'（3）用于個房態(tài)統(tǒng)計視圖定義如下：createview各房態(tài)統(tǒng)計視圖asselectsum（待售房數(shù)）as待售房數(shù),sum（已售房數(shù)）as已售房數(shù),sum（待租房數(shù)）as待租房數(shù),sum（已租房數(shù)）as已租房數(shù)from待售房統(tǒng)計視圖fullouterjoin已售房統(tǒng)計視圖on待售房統(tǒng)計視圖.編號=已售房統(tǒng)計視圖.編號fullouterjoin待租房統(tǒng)計視圖on待租房統(tǒng)計視圖.編號=待售房統(tǒng)計視圖.編號fullouterjoin已租房統(tǒng)計視圖on待租房統(tǒng)計視圖.編號=已租房統(tǒng)計視圖.編號（4）用于已售房統(tǒng)計視圖定義如下：createview已售房統(tǒng)計視圖asselect編號,count（狀態(tài)）as已售房數(shù)from房源信息表groupby狀態(tài),編號having狀態(tài)='已售'l（5）用于已租房統(tǒng)計視圖定義如下：createview已租房統(tǒng)計視圖asselect編號,count（狀態(tài)）as已租房數(shù)from房源信息表groupby狀態(tài),編號having狀態(tài)='已租'（6）用于待售房統(tǒng)計視圖定義如下：createview待售房統(tǒng)計視圖asselect編號,count（狀態(tài)）as待售房數(shù)from房源信息表groupby狀態(tài),編號having狀態(tài)='待售'（7）用于待租房統(tǒng)計視圖定義如下：createview待租房統(tǒng)計視圖assel