同濟(jì)大學(xué)數(shù)據(jù)庫(kù)習(xí)題課.ppt

1、1,數(shù)據(jù)庫(kù)技術(shù)與應(yīng)用,習(xí)題課,2,關(guān)系代數(shù)運(yùn)算,關(guān)系模型中常用的操作如下： 1.交 2.并 3.差 4.笛卡兒積 5.投影 6.選擇 7.聯(lián)接,3,關(guān)系代數(shù)運(yùn)算,8.自然連接 9.除法：設(shè)有關(guān)系R（X，Y）和S（Y），其中X、Y可以使單個(gè)屬性或?qū)傩约?，則除法運(yùn)算定義為：,4,關(guān)系代數(shù)實(shí)例,設(shè)有關(guān)系模式： S(snum,sname,age,sex) SC(snum,cnum,score) C(cnum,cname,teacher) 試用關(guān)系代數(shù)表達(dá)式表達(dá)每個(gè)查詢(xún)語(yǔ)句。 1.檢索學(xué)習(xí)課程號(hào)為C2的學(xué)生學(xué)號(hào)與成績(jī)。,5,2.檢索學(xué)習(xí)課程為C2的學(xué)生學(xué)號(hào)與姓名。 3.檢索選修課程名為Maths的學(xué)生學(xué)

2、號(hào)與姓名。 4.檢索選修課程號(hào)為C2或C4的學(xué)生學(xué)號(hào)。,6,5.檢索至少選修課程號(hào)為C2和C4的學(xué)生學(xué)號(hào)。 6.檢索不學(xué)課程號(hào)為C2的學(xué)生姓名與年齡。,7,7.檢索學(xué)習(xí)全部課程的學(xué)生姓名。 8.檢索所學(xué)課程包含學(xué)生S3所學(xué)課程的學(xué)生學(xué)號(hào)。,8,Take a break!,9,數(shù)據(jù)庫(kù)設(shè)計(jì)與ER模型,ER 模型的設(shè)計(jì)過(guò)程：數(shù)據(jù)庫(kù)設(shè)計(jì)者的任務(wù)就是要把現(xiàn)實(shí)世界中的數(shù)據(jù)以及數(shù)據(jù)間的聯(lián)系抽象出來(lái)，用“實(shí)體”、“屬性”與“聯(lián)系”來(lái)表示。具體設(shè)計(jì)步驟為：,首先設(shè)計(jì)實(shí)體及其屬性 再設(shè)計(jì)聯(lián)系及其屬性,10,數(shù)據(jù)庫(kù)設(shè)計(jì)舉例（一）,設(shè)某商業(yè)集團(tuán)數(shù)據(jù)庫(kù)中有3個(gè)實(shí)體集。一是“公司”實(shí)體集，屬性有公司編號(hào)、公司名、地址等；

3、二是“倉(cāng)庫(kù)”實(shí)體集，屬性有倉(cāng)庫(kù)編號(hào)、倉(cāng)庫(kù)名、地址等；三是“職工”實(shí)體集，屬性有職工編號(hào)、姓名、性別等。,實(shí)體及其屬性的描述,11,公司與倉(cāng)庫(kù)間存在“隸屬”聯(lián)系，每個(gè)公司管轄若干倉(cāng)庫(kù)，每個(gè)倉(cāng)庫(kù)只能屬于一個(gè)公司管轄；倉(cāng)庫(kù)與職工之間存在聘用聯(lián)系，每個(gè)倉(cāng)庫(kù)可聘用多個(gè)職工，每個(gè)職工只能在一個(gè)倉(cāng)庫(kù)工作，倉(cāng)庫(kù)聘用職工有聘期和工資。,聯(lián)系及其屬性的描述,（1）試畫(huà)出ER圖，并在圖上注明屬性、聯(lián)系的類(lèi)型。 （2）將ER圖轉(zhuǎn)換成關(guān)系模式集，并指出每個(gè)關(guān)系模式的主鍵和外健。,12,倉(cāng)庫(kù)（倉(cāng)庫(kù)編號(hào)，倉(cāng)庫(kù)名， 地址，公司編號(hào)）,公司（公司編號(hào)，公司名，地址）,職工（職工編號(hào)，姓名，性別， 倉(cāng)庫(kù)編號(hào)，聘期，工資）,13,

4、數(shù)據(jù)庫(kù)設(shè)計(jì)實(shí)例(二),設(shè)某商業(yè)集團(tuán)數(shù)據(jù)庫(kù)中有3個(gè)實(shí)體集。一是“商店”實(shí)體集，屬性有商店編號(hào)、商店名、地址等；二是“商品”實(shí)體集，屬性有商品號(hào)、商品名、規(guī)格、單價(jià)等；三是“職工”實(shí)體集，屬性有職工編號(hào)、姓名、性別、業(yè)績(jī)等。 商店與商品間存在“銷(xiāo)售”聯(lián)系，每個(gè)商店可銷(xiāo)售多種商品，每種商品也可以放在多個(gè)商店銷(xiāo)售，每個(gè)商店每銷(xiāo)售一種商品，有月銷(xiāo)售量；商店與職工間存在著“聘用”關(guān)系，每個(gè)商店有許多職工，每個(gè)職工只能在一個(gè)商店工作，商店聘用職工有聘期和月薪。 （1）試畫(huà)出ER 圖，并在圖上注明屬性、聯(lián)系的類(lèi)型。 （2）將ER圖轉(zhuǎn)換成關(guān)系模式集，并指出每個(gè)關(guān)系模式主鍵和外鍵。,14,商店（商店編號(hào)，商店名，

5、地址）,商品（商品號(hào)，商品名，規(guī)格，單價(jià)）,職工（職工編號(hào)，姓名，性別，業(yè)績(jī)，商店編號(hào)，聘期，月薪）,銷(xiāo)售（商店編號(hào)，商品號(hào)，當(dāng)年月份，月銷(xiāo)售量）,15,數(shù)據(jù)庫(kù)設(shè)計(jì)實(shí)例(三),供應(yīng)商、項(xiàng)目和零件三者之間具有多對(duì)多的聯(lián)系。即一個(gè)供應(yīng)商 可以供給若干項(xiàng)目多種零件，每個(gè)項(xiàng)目可以使用不同供應(yīng)商供應(yīng) 的零件，每種零件可由不同供應(yīng)商供給。 實(shí)體描述如下： 供應(yīng)商： 供應(yīng)商號(hào)、姓名、地址、電話(huà)號(hào)碼、帳號(hào)。 項(xiàng)目：項(xiàng)目號(hào)、預(yù)算、開(kāi)工日期。 零件：零件號(hào)、名稱(chēng)、規(guī)格、單價(jià)、描述。 （1）試畫(huà)出ER 圖，并在圖上注明屬性、聯(lián)系的類(lèi)型。 （2）將ER圖轉(zhuǎn)換成關(guān)系模式集，并指出每個(gè)關(guān)系模式主鍵和外鍵。,16,供應(yīng)（項(xiàng)

6、目號(hào)，供應(yīng)商號(hào)，零件號(hào)，供應(yīng)量）,供應(yīng)商（供應(yīng)商號(hào),姓名,帳號(hào),地址,電話(huà)號(hào)碼）,零件（零件號(hào), 名稱(chēng),描述,規(guī)格,單價(jià)）,項(xiàng)目（項(xiàng)目號(hào),預(yù)算,開(kāi)工日期）,17,數(shù)據(jù)庫(kù)設(shè)計(jì)思考題,一個(gè)圖書(shū)借閱管理數(shù)據(jù)庫(kù)要求提供下述服務(wù)： （1）可隨時(shí)查詢(xún)書(shū)庫(kù)中現(xiàn)有書(shū)籍的品種、數(shù)量與存放位置。所有各類(lèi)書(shū)籍均可由書(shū)號(hào)唯一標(biāo)識(shí)。 （2）可隨時(shí)查詢(xún)書(shū)籍借還情況，包括借書(shū)人單位、姓名、借書(shū)證號(hào)、借書(shū)日期和還書(shū)日期。規(guī)則約定：任何人可借多種書(shū)，任何一種書(shū)可為多個(gè)人所借，借書(shū)證號(hào)具有唯一性。 （3）當(dāng)需要時(shí)，可以通過(guò)數(shù)據(jù)庫(kù)中保存的出版社的電報(bào)編號(hào)、電話(huà)、郵編及地址等信息向相應(yīng)出版社增購(gòu)有關(guān)書(shū)籍。規(guī)則約定：一個(gè)出版社在規(guī)劃的

7、日期可出版多種書(shū)籍，同一本書(shū)僅為一個(gè)出版社出版，出版社名具有唯一性。 根據(jù)上述需求分析，試完成下列設(shè)計(jì)： 1. 構(gòu)造滿(mǎn)足需求的E-R圖。 2. 轉(zhuǎn)換為等價(jià)的關(guān)系模式。,18,19,SQL 語(yǔ)句,Select 屬性名|表達(dá)式|聚合函數(shù) From 表 Where 條件 Group by 屬性名 Having 條件 Order by 屬性名或表達(dá)式 asc|desc Compute,查詢(xún)的結(jié)果也是表,不允許出現(xiàn)聚合函數(shù),20,WHERE的條件表達(dá),21,用SQL實(shí)現(xiàn)上述操作,1.檢索學(xué)習(xí)課程號(hào)為C2的學(xué)生學(xué)號(hào)與成績(jī)。 2.檢索學(xué)習(xí)課程為C2的學(xué)生學(xué)號(hào)與姓名。 3.檢索選修課程名為Maths的學(xué)生學(xué)號(hào)

8、與姓名。 4.檢索選修課程號(hào)為C2或C4的學(xué)生學(xué)號(hào)。 5.檢索至少選修課程號(hào)為C2和C4的學(xué)生學(xué)號(hào)。 6.檢索不學(xué)課程號(hào)為C2的學(xué)生姓名與年齡。 7.檢索學(xué)習(xí)全部課程的學(xué)生姓名。 8.檢索所學(xué)課程包含學(xué)生S3所學(xué)課程的學(xué)生學(xué)號(hào)。,22,1.檢索學(xué)習(xí)課程號(hào)為C2的學(xué)生學(xué)號(hào)與成績(jī)。,Select snum,score From sc Where cnum=c2;,23,2.檢索學(xué)習(xí)課程為C2的學(xué)生學(xué)號(hào)與姓名。,Select snum,sname From s Where snum in(select snum from sc where cnum=c2);,Select snum,sname Fr

9、om s,sc Where s.snum=sc.snum and cnum=c2,也可寫(xiě)為：,24,3.檢索選修課程名為Maths的學(xué)生學(xué)號(hào)與姓名。,Select snum,sname From s Where snum in(select snum from sc where cnum in(select cnum from c where cname=Maths);,Select snum,sname From s,sc,c Where s.snum=sc.snum and um=um and cname=Maths,也可寫(xiě)為：,25,4.檢索選修課程號(hào)為C2或C4的學(xué)生學(xué)號(hào)。,Selec

10、t snum From sc Where cnum=c2 or cnum=c4;,26,5.檢索至少選修課程號(hào)為C2和C4的學(xué)生學(xué)號(hào)。,Select distinct snum From sc Where snum in(select snum from sc where cnum=c2) and snum in(select snum from sc where cnum=c4);,27,6、檢索不學(xué)課程號(hào)為C2的學(xué)生姓名與年齡。,select sname,age from s where snum not in(select snum from sc where cnum=c2);,sel

11、ect sname,age from s where snum in (select snum from sc where cnumc2);,28,7、檢索學(xué)習(xí)全部課程的學(xué)生姓名。,select sname from s,sc where s.snum=sc.snum group by s.snun,sname having count(cnum)=( select count(*) from c);,29,8、檢索所學(xué)課程包含學(xué)生S3所學(xué)課程的學(xué)生學(xué)號(hào)。,select snum,cnum from sc where cnum in( select cnum from sc where snu

12、m=s3) group by snum,cnum Having count(cnum)=(select count(cnum) from sc where snum=s3),30,日常事務(wù)處理中的SQL查詢(xún),通配符的使用 單個(gè)字符： _ 多個(gè)字符：%,設(shè)有關(guān)系模式： S(snum,sname,age,sex,homeAddress) SC(snum,cnum,score),Select count(*) From S Where homeAddress like %湖南%,31,注意知識(shí)的靈活性,統(tǒng)計(jì)每位學(xué)生的平均成績(jī)并按從高到低排序,select snum,avg(score) as avg

13、_score from sc group by snum order by avg_score desc,select snum,avg(score) as avg_score from sc group by snum order by avg(score) desc,32,33,Like運(yùn)算符與通配符,34,Like運(yùn)算符,集合運(yùn)算符,35,36,6、檢索不學(xué)課程號(hào)為C2的學(xué)生姓名與年齡。,SELECT sname,age FROM s EXCEPT SELECT snum FROM sc WHERE cnum=c2);,37,求所有獲獎(jiǎng)學(xué)金的同學(xué)名單（條件：每門(mén)課程成績(jī)?cè)?0分以上，平均

14、成績(jī)?cè)?0以上）,select sname from s,sc where s.snum=sc.snum group by s.snum,sname having min(score)=80 and avg(score)=90;,38,思考題（一）,設(shè)某商業(yè)集團(tuán)數(shù)據(jù)庫(kù)有3個(gè)實(shí)體集。一是“商品”實(shí)體集，屬性有商品號(hào)、商品名、規(guī)格、單價(jià)等；二是“商店”實(shí)體集，屬性有商品號(hào)、商店名、地址等；三是“供應(yīng)商”實(shí)體集，屬性有供應(yīng)商編號(hào)、供應(yīng)商名、地址等。 供應(yīng)商與商品之間存在“供應(yīng)”聯(lián)系，每個(gè)供應(yīng)商可供應(yīng)多種商品每種商品可向多個(gè)供應(yīng)商訂購(gòu)，每個(gè)供應(yīng)商應(yīng)供應(yīng)每種商品有個(gè)月供應(yīng)量；商店與商品間存在“銷(xiāo)售”聯(lián)系，每個(gè)商店可銷(xiāo)售多種商品，每種商品可在多個(gè)商店銷(xiāo)售，每個(gè)商店銷(xiāo)售每種商品有月計(jì)劃數(shù)。 （1）試畫(huà)出ER圖，并在圖上注明屬性、聯(lián)系的類(lèi)型。 （2）