第一題:
設某商品-銷售資料庫中的資訊有:員工員、員工名、工資、銷售組名、銷售組負責人、商品號、商品價、單價、銷售日期、銷售量、供應者號、供應者名、供應者地址。假定:一個員工僅在一個銷售組;一個銷售組可銷售多種商品,一種商品只能由一個組銷售;一種商品每天有一個銷售量;一個供應者可以供應多種商品,一種商品可以多渠道供貨。
要求完成下列各題:
(1)根據以上資訊,給出E-R圖。(10分)
(2)按規範化要求設計出3NF的資料庫模式。(10分)
(3)給出資料庫模式中每個關係模式的主鍵和外來鍵。(5分)
(4)在所設計的資料庫模式上,檢索累計銷售總額超過50000元的商品號、商品名、銷售總額,並按商品號排序。(5分)
答案:
(1)E-R模型(10分;每個實體1分,包括屬性;每個聯絡2分,包括聯絡型別.)
(2)3NF的關係模式:(10分)
R1(員工號,員工名,工資,銷售組名);(2分,沒有銷售組名扣1分)
R2(銷售組名,銷售組負責人);(1分)
R3(商品號,商品名,單價,銷售組名);(2分,沒有銷售組名扣1分)
R4(商品號,銷售日期,銷售量);(2分)
R5(供應者號,供應者名,供應者地址);(1分)
R6(商品號,供應者號).(2分)
注:若R1,R3分為二個模式,也要扣1分.
(3)主鍵(3分)外來鍵(2分)
R1員工號
R2銷售組名
R3商品號銷售組名
R4商品號,銷售日期商品號
R5供應者號
R6商品號,供應者號商品號,供應者號
(4)(5分)
SelectR3.商品號,商品名,sum(銷售量)*單價(1分)
FromR3,R4whereR3.商品名=R4.商品名(1分)
GroupbyR3.商品名havingsum(銷售量)*單價>50000(2分)
OrderbyR3.商品名(1分)
第二題:
為學校中學生選課管理這個現實問題進行資料庫模式設計。根據調查分析,確定它的屬性集合為:
U={S#,C#,SNAME,CNAME,TEACHER,GRADE,SD}
下面給出兩種確定的模式設計方案:
方案一:只有一個關係模式:
R(S#,C#,SNAME,CNAME,TEACHER,GRADE,SD)
方案二:如果根據屬性之間存在的聯絡或相關性,建立如下三個關係:
S(S#,SNAME,SD),C(C#,CNAME,TEACHER),SC(S#,C#,GRADE)
(1).試分析這兩種模式設計方案各自的優缺點。(26分)
(2).通常情況下你認為哪種方案更“好”些?請說明理由。(4分)
答案:
對一個現實問題,進行關係資料庫模式設計,通常其設計結果不是唯一的。每個方案有各自的優缺點。對本題可以得到如下的分析結果:
對方案一:這個關係模式存在如下問題:(每個問題4分,共16分。若只給出問題,沒有說明得2.5分)
(1).資料存在大量冗餘。例如:(至少給出1項)
S#,SNAME,SD要重複“每個學生選修的課程數”
C#,CNAME,TEACHER要重複“學生選修這門課的人數”
(2).更新異常。由於資料存在大量冗餘,容易引起更新異常。例如,當更新某門課程的`教師時,由於資料存在大量冗餘,可能造成與這門課程有關的元組中,一部分元組的TEACHER的值被更新,而另一部分元組的TEACHER的值未被更新。
(3).插入異常。顯然,這個關係的主鍵是S#和C#。由於主鍵屬性值不能為空值,當登記學生資訊時,由於該學生尚未選課,C#未確定,該學生的其他資訊也無法登入,這不是我們的意願。
(4).刪除異常。如果某些課程因故刪除了,則只選修這些課程的學生資訊也被刪除了,這往往也不是我們的本意,我們並不希望刪除這些學生的資訊。
對方案二:如果根據屬性之間存在的聯絡或相關性,建立了三個關係,則方案一中存在的異常現象被消除了。(得5分)
但對諸如“查詢張三的資料庫技術課程的成績”之類的問題時,這需要連線這三個關係才能完成,這個查詢代價高而且會影響效率。(得2.5分)
相比之下,第一個方案則可直接投影、選擇就可以完成這類查詢,不需要連線操作,顯然代價低而且效率高。(得2.5分)
比較這兩種設計方案:本人認為第二種設計方案雖然對某些查詢問題連線操作較多,但它仍比第一種方案“好”,因為它不存在上述異常問題。(得4分)