同濟大學數(shù)據(jù)庫習題課.ppt

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

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

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

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

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

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

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

8、與姓名。 4.檢索選修課程號為C2或C4的學生學號。 5.檢索至少選修課程號為C2和C4的學生學號。 6.檢索不學課程號為C2的學生姓名與年齡。 7.檢索學習全部課程的學生姓名。 8.檢索所學課程包含學生S3所學課程的學生學號。,22,1.檢索學習課程號為C2的學生學號與成績。,Select snum,score From sc Where cnum=c2;,23,2.檢索學習課程為C2的學生學號與姓名。,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,也可寫為：,24,3.檢索選修課程名為Maths的學生學號與姓名。,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,也可寫為：,25,4.檢索選修課程號為C2或C4的學生學號。,Selec

10、t snum From sc Where cnum=c2 or cnum=c4;,26,5.檢索至少選修課程號為C2和C4的學生學號。,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、檢索不學課程號為C2的學生姓名與年齡。,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、檢索學習全部課程的學生姓名。,select sname from s,sc where s.snum=sc.snum group by s.snun,sname having count(cnum)=( select count(*) from c);,29,8、檢索所學課程包含學生S3所學課程的學生學號。,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,日常事務處理中的SQL查詢,通配符的使用 單個字符： _ 多個字符：%,設有關系模式： S(snum,sname,age,sex,homeAddress) SC(snum,cnum,score),Select count(*) From S Where homeAddress like %湖南%,31,注意知識的靈活性,統(tǒng)計每位學生的平均成績并按從高到低排序,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運算符與通配符,34,Like運算符,集合運算符,35,36,6、檢索不學課程號為C2的學生姓名與年齡。,SELECT sname,age FROM s EXCEPT SELECT snum FROM sc WHERE cnum=c2);,37,求所有獲獎學金的同學名單（條件：每門課程成績在80分以上，平均

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