3.(2000年)設(shè)計(jì)一個(gè)用于管理會(huì)議室的數(shù)據(jù)庫(kù)。經(jīng)調(diào)查分析，得到的數(shù)據(jù)項(xiàng)列出如下：
房間號(hào)r#CHAR(4)，部門(mén)號(hào)d#CHAR(3)，部門(mén)名稱(chēng)dnameCHAR(20)，
房間容量contentINT，部門(mén)主管dmanagerCHAR(8)，開(kāi)會(huì)日期mdateDATE，
會(huì)議名稱(chēng)cnameCHAR(20)，會(huì)議主題(subject)CHAR(80)，會(huì)議類(lèi)型(type)，
部門(mén)人數(shù)dnumINT，當(dāng)前房間狀態(tài)stateCHAR(1)，
并確定以會(huì)議室（room）信息，部門(mén)（department）信息，會(huì)議（conference）信息為實(shí)體，它們通過(guò)“開(kāi)會(huì)（meeting）”聯(lián)系起來(lái)。為了簡(jiǎn)化問(wèn)題做如下假定：
l 會(huì)議以部門(mén)召開(kāi)，但一個(gè)部門(mén)在同一會(huì)議室不能重復(fù)召開(kāi)同一個(gè)會(huì)議。
l 會(huì)議室按部門(mén)借用，且不預(yù)借。
l 當(dāng)前房間狀態(tài)取值為‘1’表示該會(huì)議室當(dāng)前正在開(kāi)會(huì)，取值為‘0’表示該會(huì)議室當(dāng)前未被借用。
數(shù)據(jù)庫(kù)概念設(shè)計(jì)的只包含主鍵屬性的簡(jiǎn)化E-R圖如下：
cname
r#
會(huì)議conference
房間room
部門(mén)department
d#
開(kāi)會(huì)meeting
m
n
p
要求：
⑴數(shù)據(jù)庫(kù)的邏輯結(jié)構(gòu)設(shè)計(jì)：給出相應(yīng)的表名、表的屬性名(數(shù)據(jù)類(lèi)型可省)、主鍵。(14分)
⑵給出下列查詢的SQL語(yǔ)句：（16分，每小題4分）
a)當(dāng)前未借用的會(huì)議室（房間號(hào)）和容量：
b)2000年4月8日有哪些部門(mén)（部門(mén)名稱(chēng)）在開(kāi)會(huì)？他們的主管是誰(shuí)？
c)2000年4月8日‘信息中心’在哪個(gè)會(huì)議室（房間號(hào)）開(kāi)會(huì)？會(huì)議名稱(chēng)和主題是什么？
d)當(dāng)前能夠適合‘信息中心’開(kāi)會(huì)的有哪些會(huì)議室（房間號(hào)）？
答案:
(1)數(shù)據(jù)庫(kù)的邏輯結(jié)構(gòu)設(shè)計(jì)(給出相應(yīng)的表名,表結(jié)構(gòu),主鍵;(14分)
room(r#,content,state)主鍵:r#(3分)
department(d#,dname,dmanager,dnum)主鍵:d#(3分)
conference(cname,subject,type)主鍵:cname(3分)
meeting(r#,d#,cname,mdate)主鍵(r#,d#,cname)(5分)
(2)給出下列查詢的SQL語(yǔ)句:(16分,每小題4分)
a)當(dāng)前未借用的會(huì)議室(房間號(hào))和容量:
selectr#,contentfromroomwherestate=’0’
b)2000年4月8日有哪些部門(mén)(部門(mén)名稱(chēng))在開(kāi)會(huì)?他們的主管是誰(shuí)?
Selectdname,dmanagerfromdepartmentd,meetingm
whered.d#=m.d#andmdate=’2000年4月8日’
或
Selectdname,dmanagerfromdepartmentd
whered.d#=in(selectm.d#=frommeetingmWheremdate=’2000年4月8日’)
c)2000年4月8日’信息中心’在哪個(gè)會(huì)議室(房間號(hào))開(kāi)會(huì)?會(huì)議名稱(chēng)和主題是什么?
Selectr#,m.cname,subjectfromdepartmentd,meetingm,conferencec
whered.d#=m.d#andm.cname=c.cnameand
Mdate=’2000年4月8日’anddname=’信息中心’
或(注:本題中Selectr#,c.cname,......也可以是Selectr#,m.cname,......
Selectr#,c.cname,subjectfrommeetingm,conferencec
WhereMdate=’2000年4月8日’andm.cname=c.cnameand
m.d#=(selectd.d#fromdepartmentdwheredname=’信息中心’)
d)當(dāng)前能夠適合’信息中心’開(kāi)會(huì)的有哪些會(huì)議室(房間號(hào))?
Selectr#fromroomwherestate=’O’and
content>=(selectdnumfromdepartmentwheredname=’信息中心’)
4.(2001年)
(l)、為了維護(hù)數(shù)據(jù)庫(kù)的參照完整性，當(dāng)刪除被參照關(guān)系的元組時(shí)，系統(tǒng)可能采取哪些做法？（15分）
(2)、若有學(xué)生關(guān)系S(S#,SNAME,SEX,AGE)，其主鍵為S#；選課關(guān)系SC(S#,C#,GRADE)，其主鍵為(S#，C#)，且S.S#=SC.S#。假定學(xué)生號(hào)為‘01001’的學(xué)生離開(kāi)學(xué)校不再回來(lái)了，為此若刪除關(guān)系S中S#=‘01001’的元組時(shí)，如果關(guān)系SC中有4個(gè)元組的S#=‘01001’，應(yīng)該選用哪一種做法？為什么？（15分）
答案:
(1)、為了維護(hù)數(shù)據(jù)庫(kù)的參照完整性，當(dāng)刪除被參照關(guān)系的元組時(shí)，系統(tǒng)可能采取如下三種做法（每種做法得5分）：
l)級(jí)聯(lián)刪除(cascades)：即，當(dāng)刪除被參照關(guān)系的元組時(shí)，同時(shí)將參照關(guān)系中所有外鍵值與被參照關(guān)系中要被刪除元組的主鍵值相等（相對(duì)應(yīng)）的元組一起刪除。
2)拒絕刪除(restricted)：即，只當(dāng)參照關(guān)系中沒(méi)有任何元組的外鍵值與被參照關(guān)系中要被刪除的元組的主鍵值相等（相對(duì)應(yīng)）時(shí)，系統(tǒng)才執(zhí)行該刪除操作，否則拒絕執(zhí)行該刪除操作。
3)置空值刪除(nullfies)：即，當(dāng)刪除被參照關(guān)系的元組時(shí)，同時(shí)將參照關(guān)系中所有與被參照關(guān)系中要被刪除元組的主健值相等(相對(duì)應(yīng))的外鍵值都置為空值。
(2)、對(duì)于本題的情況，應(yīng)該選用第一種做法。即，將關(guān)系SC中S#=‘01001’的4個(gè)元組也一起刪除。(得10分)
因?yàn)楫?dāng)一個(gè)學(xué)生離開(kāi)學(xué)校不再回來(lái)，他的個(gè)人信息記錄若從S關(guān)系中刪除了，那么他的選課信息記錄就沒(méi)有保存的必要，也應(yīng)隨之從SC關(guān)系中刪除。(得5分)
房間號(hào)r#CHAR(4)，部門(mén)號(hào)d#CHAR(3)，部門(mén)名稱(chēng)dnameCHAR(20)，
房間容量contentINT，部門(mén)主管dmanagerCHAR(8)，開(kāi)會(huì)日期mdateDATE，
會(huì)議名稱(chēng)cnameCHAR(20)，會(huì)議主題(subject)CHAR(80)，會(huì)議類(lèi)型(type)，
部門(mén)人數(shù)dnumINT，當(dāng)前房間狀態(tài)stateCHAR(1)，
并確定以會(huì)議室（room）信息，部門(mén)（department）信息，會(huì)議（conference）信息為實(shí)體，它們通過(guò)“開(kāi)會(huì)（meeting）”聯(lián)系起來(lái)。為了簡(jiǎn)化問(wèn)題做如下假定：
l 會(huì)議以部門(mén)召開(kāi)，但一個(gè)部門(mén)在同一會(huì)議室不能重復(fù)召開(kāi)同一個(gè)會(huì)議。
l 會(huì)議室按部門(mén)借用，且不預(yù)借。
l 當(dāng)前房間狀態(tài)取值為‘1’表示該會(huì)議室當(dāng)前正在開(kāi)會(huì)，取值為‘0’表示該會(huì)議室當(dāng)前未被借用。
數(shù)據(jù)庫(kù)概念設(shè)計(jì)的只包含主鍵屬性的簡(jiǎn)化E-R圖如下：
cname
r#
會(huì)議conference
房間room
部門(mén)department
d#
開(kāi)會(huì)meeting
m
n
p
要求：
⑴數(shù)據(jù)庫(kù)的邏輯結(jié)構(gòu)設(shè)計(jì)：給出相應(yīng)的表名、表的屬性名(數(shù)據(jù)類(lèi)型可省)、主鍵。(14分)
⑵給出下列查詢的SQL語(yǔ)句：（16分，每小題4分）
a)當(dāng)前未借用的會(huì)議室（房間號(hào)）和容量：
b)2000年4月8日有哪些部門(mén)（部門(mén)名稱(chēng)）在開(kāi)會(huì)？他們的主管是誰(shuí)？
c)2000年4月8日‘信息中心’在哪個(gè)會(huì)議室（房間號(hào)）開(kāi)會(huì)？會(huì)議名稱(chēng)和主題是什么？
d)當(dāng)前能夠適合‘信息中心’開(kāi)會(huì)的有哪些會(huì)議室（房間號(hào)）？
答案:
(1)數(shù)據(jù)庫(kù)的邏輯結(jié)構(gòu)設(shè)計(jì)(給出相應(yīng)的表名,表結(jié)構(gòu),主鍵;(14分)
room(r#,content,state)主鍵:r#(3分)
department(d#,dname,dmanager,dnum)主鍵:d#(3分)
conference(cname,subject,type)主鍵:cname(3分)
meeting(r#,d#,cname,mdate)主鍵(r#,d#,cname)(5分)
(2)給出下列查詢的SQL語(yǔ)句:(16分,每小題4分)
a)當(dāng)前未借用的會(huì)議室(房間號(hào))和容量:
selectr#,contentfromroomwherestate=’0’
b)2000年4月8日有哪些部門(mén)(部門(mén)名稱(chēng))在開(kāi)會(huì)?他們的主管是誰(shuí)?
Selectdname,dmanagerfromdepartmentd,meetingm
whered.d#=m.d#andmdate=’2000年4月8日’
或
Selectdname,dmanagerfromdepartmentd
whered.d#=in(selectm.d#=frommeetingmWheremdate=’2000年4月8日’)
c)2000年4月8日’信息中心’在哪個(gè)會(huì)議室(房間號(hào))開(kāi)會(huì)?會(huì)議名稱(chēng)和主題是什么?
Selectr#,m.cname,subjectfromdepartmentd,meetingm,conferencec
whered.d#=m.d#andm.cname=c.cnameand
Mdate=’2000年4月8日’anddname=’信息中心’
或(注:本題中Selectr#,c.cname,......也可以是Selectr#,m.cname,......
Selectr#,c.cname,subjectfrommeetingm,conferencec
WhereMdate=’2000年4月8日’andm.cname=c.cnameand
m.d#=(selectd.d#fromdepartmentdwheredname=’信息中心’)
d)當(dāng)前能夠適合’信息中心’開(kāi)會(huì)的有哪些會(huì)議室(房間號(hào))?
Selectr#fromroomwherestate=’O’and
content>=(selectdnumfromdepartmentwheredname=’信息中心’)
4.(2001年)
(l)、為了維護(hù)數(shù)據(jù)庫(kù)的參照完整性，當(dāng)刪除被參照關(guān)系的元組時(shí)，系統(tǒng)可能采取哪些做法？（15分）
(2)、若有學(xué)生關(guān)系S(S#,SNAME,SEX,AGE)，其主鍵為S#；選課關(guān)系SC(S#,C#,GRADE)，其主鍵為(S#，C#)，且S.S#=SC.S#。假定學(xué)生號(hào)為‘01001’的學(xué)生離開(kāi)學(xué)校不再回來(lái)了，為此若刪除關(guān)系S中S#=‘01001’的元組時(shí)，如果關(guān)系SC中有4個(gè)元組的S#=‘01001’，應(yīng)該選用哪一種做法？為什么？（15分）
答案:
(1)、為了維護(hù)數(shù)據(jù)庫(kù)的參照完整性，當(dāng)刪除被參照關(guān)系的元組時(shí)，系統(tǒng)可能采取如下三種做法（每種做法得5分）：
l)級(jí)聯(lián)刪除(cascades)：即，當(dāng)刪除被參照關(guān)系的元組時(shí)，同時(shí)將參照關(guān)系中所有外鍵值與被參照關(guān)系中要被刪除元組的主鍵值相等（相對(duì)應(yīng)）的元組一起刪除。
2)拒絕刪除(restricted)：即，只當(dāng)參照關(guān)系中沒(méi)有任何元組的外鍵值與被參照關(guān)系中要被刪除的元組的主鍵值相等（相對(duì)應(yīng)）時(shí)，系統(tǒng)才執(zhí)行該刪除操作，否則拒絕執(zhí)行該刪除操作。
3)置空值刪除(nullfies)：即，當(dāng)刪除被參照關(guān)系的元組時(shí)，同時(shí)將參照關(guān)系中所有與被參照關(guān)系中要被刪除元組的主健值相等(相對(duì)應(yīng))的外鍵值都置為空值。
(2)、對(duì)于本題的情況，應(yīng)該選用第一種做法。即，將關(guān)系SC中S#=‘01001’的4個(gè)元組也一起刪除。(得10分)
因?yàn)楫?dāng)一個(gè)學(xué)生離開(kāi)學(xué)校不再回來(lái)，他的個(gè)人信息記錄若從S關(guān)系中刪除了，那么他的選課信息記錄就沒(méi)有保存的必要，也應(yīng)隨之從SC關(guān)系中刪除。(得5分)