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

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