數(shù)據(jù)結(jié)構(gòu)與數(shù)據(jù)庫(kù).ppt

數(shù)據(jù)結(jié)構(gòu)與數(shù)據(jù)庫(kù),習(xí)題課（3） 2016年12月16日,目 錄,一、關(guān)系模式 二、關(guān)系代數(shù) 三、SQL 四、關(guān)系規(guī)范化,E-R 圖,學(xué)校中有若干系，每個(gè)系有若干班級(jí)和教研室，每個(gè)教研室有若干教員，其中教授和副教授每人各帶若干研究生；每個(gè)班有若干學(xué)生，每個(gè)學(xué)生選修若干課程，每門課可由若干學(xué)生選修。請(qǐng)用 E-R 圖畫(huà)出此學(xué)校的概念模型。,學(xué)校中有若干系，每個(gè)系有若干班級(jí)和教研室，每個(gè)教研室有若干教員，其中教授和副教授每人各帶若干研究生；每個(gè)班有若干學(xué)生，每個(gè)學(xué)生選修若干課程，每門課可由若干學(xué)生選修。請(qǐng)用 E-R 圖畫(huà)出此學(xué)校的概念模型。,學(xué)校中有若干系，每個(gè)系有若干班級(jí)和教研室，每個(gè)教研室有若干教員，其中教授和副教授每人各帶若干研究生；每個(gè)班有若干學(xué)生，每個(gè)學(xué)生選修若干課程，每門課可由若干學(xué)生選修。請(qǐng)用 E-R 圖畫(huà)出此學(xué)校的概念模型。,某醫(yī)院病房計(jì)算機(jī)管理中心需要如下信息： 科室：科名、科地址、科電話 病房：病房號(hào)、床位號(hào) 醫(yī)生：姓名、職稱、年齡、工作證號(hào) 病人：病歷號(hào)、姓名、性別 其中，一個(gè)科室有多個(gè)病房、多個(gè)醫(yī)生，一個(gè)病房只能屬于一個(gè)科室，一個(gè)醫(yī)生只屬于一個(gè)科室，但可負(fù)責(zé)多個(gè)病人的診治，一個(gè)病人的主管醫(yī)生只有一個(gè)，一個(gè)病房可以入住多個(gè)病人。 設(shè)計(jì)該計(jì)算機(jī)管理系統(tǒng)的E-R圖,某醫(yī)院病房計(jì)算機(jī)管理中心需要如下信息： 科室：科名、科地址、科電話 病房：病房號(hào)、床位號(hào) 醫(yī)生：姓名、職稱、年齡、工作證號(hào) 病人：病歷號(hào)、姓名、性別 其中，一個(gè)科室有多個(gè)病房、多個(gè)醫(yī)生，一個(gè)病房只能屬于一個(gè)科室，一個(gè)醫(yī)生只屬于一個(gè)科室，但可負(fù)責(zé)多個(gè)病人的診治，一個(gè)病人的主管醫(yī)生只有一個(gè)，一個(gè)病房可以入住多個(gè)病人。 設(shè)計(jì)該計(jì)算機(jī)管理系統(tǒng)的E-R圖,圖書(shū)管理中，圖書(shū)信息包括：書(shū)號(hào)、書(shū)名、作者、出版社；借書(shū)證信息包括：借書(shū)證號(hào)、姓名、辦證日期、身份證號(hào)。請(qǐng)畫(huà)出反映以上兩實(shí)體間借閱聯(lián)系和屬性的E-R圖，并分別轉(zhuǎn)換為對(duì)應(yīng)的關(guān)系模式結(jié)構(gòu)。,圖書(shū)管理中，圖書(shū)信息包括：書(shū)號(hào)、書(shū)名、作者、出版社；借書(shū)證信息包括：借書(shū)證號(hào)、姓名、辦證日期、身份證號(hào)。請(qǐng)畫(huà)出反映以上兩實(shí)體間借閱聯(lián)系和屬性的E-R圖，并分別轉(zhuǎn)換為對(duì)應(yīng)的關(guān)系模式結(jié)構(gòu)。,圖書(shū)管理中，圖書(shū)信息包括：書(shū)號(hào)、書(shū)名、作者、出版社；借書(shū)證信息包括：借書(shū)證號(hào)、姓名、辦證日期、身份證號(hào)。請(qǐng)畫(huà)出反映以上兩實(shí)體間借閱聯(lián)系和屬性的E-R圖，并分別轉(zhuǎn)換為對(duì)應(yīng)的關(guān)系模式結(jié)構(gòu)。 關(guān)系模式：關(guān)系的描述稱謂關(guān)系模式。關(guān)系模式通?？梢院?jiǎn)記R(A1,A2,An)，其中R為關(guān)系名， A1,A2,An為屬性名。 在此題中，圖書(shū)實(shí)體和借書(shū)證實(shí)體可用下面的關(guān)系表示（下劃線為主碼）,圖書(shū)（書(shū)號(hào)，書(shū)名，作者，出版社） 借書(shū)證（借書(shū)證號(hào)，姓名，辦證日期，身份證號(hào)）,某企業(yè)集團(tuán)有若干工廠，每個(gè)工廠聘用多名職工，且每名職工只能在一個(gè)工廠工作，工廠聘用職工有聘期和工資。工廠的屬性有工廠編號(hào)、廠名、地址；職工的屬性有職工號(hào)、姓名、技術(shù)等級(jí)。 （1）畫(huà)出E-R圖。 （2）寫(xiě)出關(guān)系模式。,一個(gè)工廠聘用多名職工，且每名職工只能在一個(gè)工廠工作，工廠聘用職工有聘期和工資。工廠的屬性有工廠編號(hào)、廠名、地址；職工的屬性有職工號(hào)、姓名、技術(shù)等級(jí)。 （1）畫(huà)出E-R圖。 （2）寫(xiě)出關(guān)系模式。,一個(gè)工廠聘用多名職工，且每名職工只能在一個(gè)工廠工作，工廠聘用職工有聘期和工資。工廠的屬性有工廠編號(hào)、廠名、地址；職工的屬性有職工號(hào)、姓名、技術(shù)等級(jí)。 （1）畫(huà)出E-R圖。 （2）寫(xiě)出關(guān)系模式。 P43例2: 學(xué)生，課程，學(xué)生與課程之間的多對(duì)多聯(lián)系 學(xué)生（學(xué)號(hào)，姓名，性別，專業(yè)號(hào)，年齡） 課程（課程號(hào)，課程名，學(xué)分） 選修（學(xué)號(hào)，課程號(hào)，成績(jī)）,選修,課程,學(xué)生,n,m,某企業(yè)集團(tuán)有若干工廠，每個(gè)工廠聘用多名職工，且每名職工只能在一個(gè)工廠工作，工廠聘用職工有聘期和工資。工廠的屬性有工廠編號(hào)、廠名、地址；職工的屬性有職工號(hào)、姓名、技術(shù)等級(jí)。 （1）畫(huà)出E-R圖。 （2）寫(xiě)出關(guān)系模式。,職工（職工號(hào)，姓名，技術(shù)等級(jí) 工廠（工廠編號(hào)，廠名，地址） 聘用（職工號(hào)，工廠編號(hào)，聘期，工資）,關(guān)系代數(shù) 習(xí)題,1、關(guān)系模型的三要素: 關(guān)系數(shù)據(jù)結(jié)構(gòu) 關(guān)系操作集合 關(guān)系完整性約束 2、關(guān)系運(yùn)算的三要素 運(yùn)算對(duì)象 運(yùn)算符 運(yùn)算結(jié)果 3、關(guān)系代數(shù)運(yùn)算的分類 傳統(tǒng)的集合運(yùn)算：并、差、交、廣義笛卡爾積 專門的關(guān)系運(yùn)算：選擇、投影、連接、除,實(shí)體完整性、 參照完整性、 用戶定義完整性,4. 設(shè)k元關(guān)系R，則24表示（ ）。 A、從R中挑選第4個(gè)分量的值小于2的元組所構(gòu)成的關(guān)系 B、從R中挑選第2個(gè)分量值大于4的元組所構(gòu)成的關(guān)系 C、從R中挑選第2個(gè)分量值大于第4個(gè)分量值的元組所構(gòu)成的關(guān)系 D、 24與R相比,基數(shù)不變,元數(shù)減少 答案：B,5、設(shè)關(guān)系R和S的結(jié)構(gòu)相同,且各有10個(gè)元組,那么這兩個(gè)關(guān)系的并操作結(jié)果的元組個(gè)數(shù)為（ ）。 A、20 B、20 C、10 D、10 答案：B,6. 分析：C 在有n個(gè)頂點(diǎn)的無(wú)向完全圖中，每一個(gè)頂點(diǎn)都有一條邊與其它 某一頂點(diǎn)相連，所以每一個(gè)頂點(diǎn)有n-1條邊與其他n-1個(gè)頂點(diǎn)相連， 總計(jì)n個(gè)頂點(diǎn)有n(n-1)條邊。但在無(wú)向圖中，頂點(diǎn)i到頂點(diǎn)j與頂點(diǎn)j到 頂點(diǎn)i是同一條邊，所以總共有n(n-1)/2條邊。,分析： 并、交、差要求關(guān)系R 、S具有相同的目，（即兩個(gè)關(guān)系都有n個(gè)屬性），相應(yīng)的屬性取自同一個(gè)域。 答案：A,7、設(shè)關(guān)系R、S、W各有10個(gè)元組，那么這3關(guān)系的自然聯(lián)接的元組個(gè)數(shù)為（ ）。 A、10 B、30 C、1000 D、不確定（與計(jì)算結(jié)果有關(guān)） 答案：D,8、下列式子中,不正確的是（ ）。 A、R-S=R-(RS) B、R=(R-S)(RS) C、RS=S-(S-R) D、RS=S-(R-S) 答案：D,9、設(shè)4元關(guān)系R(A,B,C,D), 則（ ）。 A、D,B(R)為取屬性值為D，B的兩列組成新關(guān)系 B、4,2(R)為取屬性值為4，2的兩列組成新關(guān)系 C、D,B(R)和4,2(R)是等價(jià)的 D、D,B(R)與4,2(R)不是等價(jià)的 答案：C 10、對(duì)表進(jìn)行水平方向的分割用的運(yùn)算是 （） 。 A、交 B、投影 C、選擇 D、連接 分析：投影操作主要是從列的角度進(jìn)行運(yùn)算 答案：C,11、若有關(guān)系選課（學(xué)號(hào)，課號(hào)，成績(jī)），對(duì)于每一門課，教室是固定的。已知有50個(gè)學(xué)生選修了課x，則當(dāng)課x換教室時(shí)，需要修改的元組有（）。 A、1個(gè) B、3個(gè) C、50個(gè) D、100個(gè) 答案：A,11、若有關(guān)系選課（學(xué)號(hào)，課號(hào)，成績(jī)），對(duì)于每一門課，教室是固定的。已知有50個(gè)學(xué)生選修了課x，則當(dāng)課x換教室時(shí)，需要修改的元組有（）。 A、1個(gè) B、3個(gè) C、50個(gè) D、100個(gè) 答案：A,11、,答案： 基本關(guān)系的性質(zhì),SQL,3.2 建立4個(gè)表 CREATE TABLE S (SNO C(2) UNIQUE, SNAME C(6), CITY C(4); CREATE TABLE P(PNO C(2) UNIQUE, PNAME C(6), COLOR C(2), WEIGHT INT); CREATE TABLE J(JNO C(2) UNIQUE, JNAME C(8), CITY C(4); CREATE TABLE SPJ(SNO C(2), PNO C(2), JNO C(2), QTY INT). 3.3 1) 求供應(yīng)工程 J1 零件的供應(yīng)商號(hào)碼 SNO ; SELECT DIST SNO FROM SPJ WHERE JNO=J1 2) 求供應(yīng)工程 J1 零件P1的供應(yīng)商號(hào)碼 SNO ; SELECT DIST SNO FROM SPJ WHERE JNO=J1 AND PNO=P1,作業(yè)題解析,3) 求供應(yīng)工程 J1 零件為紅色的供應(yīng)商號(hào)碼 SNO SELECT SNO FROM SPJ,P WHERE JNO=J1 AND SPJ.PNO=P.PNO AND COLOR=紅 4)求沒(méi)有使用天津供應(yīng)商生產(chǎn)的紅色零件的工程號(hào) JNO SELECT DIST JNO FROM SPJ WHERE JNO NOT IN (SELECT JNO FROM SPJ,P,S WHERE S.CITY=天津 AND COLOR=紅 AND S.SNO=SPJ.SNO AND P.PNO=SPJ.PNO) 5) 求至少用了供應(yīng)商 Sl 所供應(yīng)的全部零件的工程號(hào) JNO 不存在這樣的零件Y，供應(yīng)商S1生產(chǎn)了它，而工程X沒(méi)有用(不存在工程X使用)。 Select DIST JNO from SPJ SPJX where not exist ( select * from SPJ SPJY where SPJY.SNO=S1 AND not exist (select * from SPJ SPJZ where SPJZ.JNO=SPJX.JNO AND SPJZ.PNO=SPJY.PNO);,3.4 1) 統(tǒng)計(jì)每種零件的供應(yīng)總量: Select PNO, sum(Qty) from SPJ group by PNO 2) 零件供應(yīng)總量在1000以上的供應(yīng)商名 Select SNAME from S where SNO in (Select SNO from SPJ group by SNO having Sum(Qty) =1000) 3) 在S表中插入一條供應(yīng)商信息(S6,華天,深圳) INSERT INTO S(SNo, SName, City) VALUES (S6,華天,深圳) 4) 把全部紅色零件改成粉紅色 UPDATE P SET Color = 粉紅色 where Color = 紅色 5) 將s1供應(yīng)給J1的零件p1改成p2 UPDATE SPJ SET PNO = P2 where SNO = S1 and JNO = J1 and PNO = P1 6)刪除全部藍(lán)色零件及其相應(yīng)的SPJ記錄 先從表再主表 delete from SPJ where PNO in (select PNO from P where Color = 藍(lán)色) delete from P where Color = 藍(lán)色,設(shè)有關(guān)系S（S，SNAME，SAGE，SEX），C（C，CNAME），SC（S，C，GRADE）。其中S是學(xué)生號(hào)，SNAME是學(xué)生姓名，SAGE是學(xué)生年齡，SEX是學(xué)生的性別，C是課程號(hào)，CNAME是課程名稱，GRADE是成績(jī)。 1、用SQL語(yǔ)句創(chuàng)建S表，聲明“S”為主碼，“SNAME”不能為空，“SEX” 的取值為男或女。 2、用SQL語(yǔ)句修改C表中的列CNAME為CNAME char(40)。 3、用SQL語(yǔ)句使用嵌套查詢，查詢出沒(méi)有選修1號(hào)課程的學(xué)生學(xué)號(hào)和姓名。,練習(xí)題,1. 2. ALTER TABLE C MODIEY CNAME CHAR(40) 3.,W3School SQL 教程: 搜索“著名的 SQL 50題”,資源,數(shù)據(jù)結(jié)構(gòu)與數(shù)據(jù)庫(kù)習(xí)題課（三） 關(guān)系規(guī)范化,今要建立關(guān)于系、學(xué)生、班級(jí)、學(xué)會(huì)諸信息的一個(gè)關(guān)系數(shù)據(jù)庫(kù)。一個(gè)系有若干專業(yè)，每個(gè)專業(yè)每年只招一個(gè)班，每個(gè)班有若干學(xué)生。一個(gè)系的學(xué)生住在同一宿舍區(qū)。每個(gè)學(xué)生可參加若干學(xué)會(huì)，每個(gè)學(xué)會(huì)有若干學(xué)生。 描述學(xué)生的屬性有：學(xué)號(hào)、姓名、出生年月、系名、班號(hào)、宿舍區(qū)。 描述班級(jí)的屬性有：班號(hào)、專業(yè)名、系名、人數(shù)、入校年份。 描述系的屬性有：系名、系號(hào)、系辦公室地點(diǎn)、人數(shù)。 描述學(xué)會(huì)的屬性有：學(xué)會(huì)名、成立年份、地點(diǎn)、人數(shù)。學(xué)生參加某學(xué)會(huì)有一個(gè)入會(huì)年份。 請(qǐng)給出關(guān)系模式，寫(xiě)出每個(gè)關(guān)系模式的極小函數(shù)依賴集，支出是否存在傳遞函數(shù)依賴，對(duì)于函數(shù)依賴左部是多屬性的情況，討論函數(shù)依賴是完全函數(shù)依賴，還是部分函數(shù)依賴。指出各關(guān)系的候選碼和外部碼。,關(guān)系模式： （1）學(xué)生：S(Sno, Sname, Sbirth, Dept, Class, Rno) （2）班級(jí)：C(Class, Pname, Dept, Cnum, Cyear) （3）系：D(Dept, Dno, Office, Dnum) （4）學(xué)會(huì)：M(Mname, Myear, Maddr, Mnum) （5）學(xué)生-學(xué)會(huì)：SM(Sno, Mname, Smyear),最小函數(shù)依賴集： 定義：如果函數(shù)依賴集F滿足下列條件，則稱F為最小函數(shù)依賴集或最小覆蓋： （1）F中的任何一個(gè)函數(shù)依賴的右部?jī)H含有一個(gè)屬性； （2）F中不存在這樣一個(gè)函數(shù)依賴XA，使得F與F-XA等價(jià)；（即：不存在冗余依賴關(guān)系） （3）F中不存在這樣一個(gè)函數(shù)依賴XA，X有真子集Z使得F-XAZA與F等價(jià)。（即：依賴關(guān)系左部不存在冗余屬性）,最小函數(shù)依賴集： 求解方法： （1）用分解的法則，使F中的任何一個(gè)函數(shù)依賴的右部?jī)H含有一個(gè)屬性； （2）去掉多余的函數(shù)依賴：從第一個(gè)函數(shù)依賴XY開(kāi)始將其從F中去掉，然后在剩下的函數(shù)依賴中求X的閉包X+，看X+是否包含Y，若是，則去掉XY；否則不能去掉，依次做下去。直到找不到冗余的函數(shù)依賴； 屬性X的閉包就是該屬性直接或間接推導(dǎo)出的所有屬性集合 （3）去掉各依賴左部多余的屬性。一個(gè)一個(gè)地檢查函數(shù)依賴左部非單個(gè)屬性的依賴。例如XYA，若要判Y為多余的，則以XA代替XYA是否等價(jià)？若是，則Y是多余屬性，可以去掉,最小函數(shù)依賴集： S(Sno,Sname,Sbirth,Dept,Class,Rno) 根據(jù)現(xiàn)實(shí)生活及題中的規(guī)則，可以看出有如下的依賴關(guān)系： Sno Sname， Sno Dept ，Sno Sbirth ，Sno Class， Sno Rno，Class Dept，Dept Rno （一個(gè)系的同學(xué)住同一個(gè)宿舍區(qū)） （1）把Sno Sname去掉后，尋找Sno的閉包為Sno, Sbirth, Dept, Class, Rno，不包括Sname，因此Sno Sname不能去除； （2）把Sno Dept 去掉后，尋找Sno的閉包為Sno, Sname, Sbirth, Dept, Class, Rno，由Sno Class Dept從而包含Dept，因此Sno Dept 應(yīng)該去除； （3）同理依次操作。 Sno Rno也可以去除。 最小函數(shù)依賴集為： Sno Sname, Sno Sbirth, Sno Class, Class Dept, Dept Rno,最小函數(shù)依賴集： 同理，可以依次得到各個(gè)關(guān)系模式的最小函數(shù)依賴集： C：Class Pname, Class Cnum, Class Cyear, Pname Dept (Pname,Cyear) Class（完全函數(shù)依賴） D：Dept Dno, Dno Dept, Dno Office, Dno Dnum M：Mname Myear, Mname Maddr, Mname Mnum SM：(Sno, Mname) Smyear（完全函數(shù)依賴）,傳遞函數(shù)依賴： （1）對(duì)于關(guān)系模式S： Sno