數(shù)據(jù)庫設(shè)計例題解析.docx

問題 1 需要評分 有關(guān)系R如下表所示。 EMPNOEMPNAMEAGESEXDEPTNODEPTNAMEE1Zhao20FD3CCCE2Qian25MD1AAAE3Sun38MD3CCCE4Li25FD3CCC其中，EMPNO為員工號，EMPNAME為員工姓名，AGE為年齡，SEX為員工性別，DEPTNO為部門編號，DEPTNAME為部門名稱。1）R是第幾范式，為什么？ 2) R中是否存在異常？若存在，則說明什么情況下發(fā)生。3) 將它分解為高一級范式，分解后的關(guān)系能否解決操作異常問題？ 正確答案： 1） R關(guān)系屬于2NF。從表中的數(shù)據(jù)看，該關(guān)系的主鍵為：員工號EMPNO，或員工名EMPNAME，因為只有這兩列數(shù)據(jù)不重復(fù)。又因為存在著EMPNO DEPTNO, DEPTNODEPTNAME,即存在 著非主屬性（部門名稱）對碼（員工號）的傳遞依賴。2）存在數(shù)據(jù)冗余、修改異常、插入異常和刪除異常，如，部門名稱數(shù)據(jù)冗余，如果沒有員工信息，則部門信息無法添加；如果刪除一個部門所有員工信息，則也刪除了該部門信息；更新一個部門名稱復(fù)雜。3）分解為R1和R2R1：(EMPNO, EMPNAME, AGE, SEX, DEPTNO)， 候選碼為EMPNO ，或 EMPNAME，選EMPNO 為主鍵。 外部鍵：DEPTNO R2：(DEPTNO, DEPTNAME) ，候選碼為DEPTNO，即為主鍵，外部鍵：無R1，R2均達(dá)到了BCNF范式（決定因素包含候選碼），消除了上述的各種異常。 問題 2 需要評分 有一個應(yīng)用包括三個實體集。 實體“商店”的屬性有：商店編號，店名，店址，店經(jīng)理。實體“會員”的屬性有：會員編號，會員名，地址。 實體“職工”的屬性有：職工編號，職工名，性別，工資 。 每個商店有若干職工，但每個職工只能服務(wù)于一個商店。每個商店有若干會員，每個會員可以屬于多個商店。職工參加某商店工作后記錄開始工作時間；會員成為商店會員時記錄加入時間。 (1)試畫出反映應(yīng)用的ER圖； (2)將ER圖轉(zhuǎn)換成關(guān)系模式，并指出每個表的主鍵和外鍵 正確答案： （1） ER圖圖省略。（2） 實體轉(zhuǎn)換為關(guān)系表商店（商店編號，店名，店址，店經(jīng)理）會員（會員編號，會員名，地址）職工（職工編號，職工名，性別，工資）聯(lián)系轉(zhuǎn)換為關(guān)系表商店-會員（商店編號，會員編號，會員的加入時間）商店-職工（商店編號，職工編號，開始工作的時間） 主碼相同的表可以合并，合并得到：職工（職工編號，職工名，性別，工資，商店編號，開始工作的時間） 最終得到關(guān)系模型：商店（商店編號，店名，店址，店經(jīng)理） 主鍵：商店編號，外部鍵：無會員（會員編號，會員名，地址） 主鍵：會員編號 ，外部鍵：無職工（職工編號，職工名，性別，工資，商店編號，開始工作的時間）主鍵：職工編號， 外部鍵：商店編號商店-會員（商店編號，會員編號，會員的加入時間） 主鍵：（商店編號，會員編號）外部鍵：商店編號，外部鍵：會員編號問題 3 需要評分 某醫(yī)院病房的管理如下：有若干科室，一個科室有多個病房，多個醫(yī)生，一個病房只能屬于一個科室；一個病人可以有多個醫(yī)生治療，但主管醫(yī)生只有一個；一個醫(yī)生只屬于一個科室，可負(fù)責(zé)多個病人的診治?？剖覍傩杂校嚎剖颐?，科地址，科電話病房屬性有：病房號，床位號醫(yī)生屬性有：工作證號，姓名，職稱，年齡病人屬性有：病歷號，姓名，性別，出生日期根據(jù)上述描述，完成如下設(shè)計：1）畫出E-R，并在圖上注明聯(lián)系類型；2）將實體及聯(lián)系轉(zhuǎn)換為關(guān)系模型，并注明主鍵和外部鍵。正確答案： 1）找出所有的實體：科室，病房，醫(yī)生，病人。根據(jù)題意，畫出E-R圖：省略。實體轉(zhuǎn)換成關(guān)系表科室（科室名，科地址，科電話），主鍵：科室名病房（病房號，床位號），主鍵：（病房號，床位號）醫(yī)生（工作證號，姓名，職稱，年齡），主鍵：工作證號病人（病歷號，姓名，性別，出生日期），主鍵：病歷號實體聯(lián)系轉(zhuǎn)換成關(guān)系表科室-醫(yī)生（科室名，工作證號），主鍵：工作證號科室-病房（科室名，病房號，床位號），主鍵：病房號，床位號病房-病人（病房號，病歷號），主鍵：病歷號主治（病歷號，工作證號），主鍵：病歷號。因為是1:1聯(lián)系，也可以是工作證號診治（病歷號，工作證號），主鍵：（病歷號，工作證號）合并主鍵相同的表，最后得到：科室（科室名，科地址，科電話），主鍵：科室名病房（病房號，床位號，科室名），主鍵：（病房號，床位號），外鍵：科室名醫(yī)生（工作證號，姓名，職稱，年齡，科室名） ，主鍵：工作證號，外鍵：科室名病人（病歷號，姓名，性別，病房號，工作證號），主鍵：病歷號，外鍵：病房號，外鍵：工作證號診治（病歷號，工作證號），主鍵：（病歷號，工作證號），外鍵：病歷號，外鍵：工作證號問題 4 需要評分 圖書館有多個讀者和多種圖書，一個讀者可以借閱多種圖書，一種圖書可以供多個讀者借閱。讀者借書后有借出日期和應(yīng)還日期。讀者的屬性包括：讀者號、姓名、地址、性別、年齡、單位；圖書的屬性包括：書號、書名、作者、出版社、ISBN、價格。根據(jù)上述描述，要求：1）畫出E-R,并在圖上注明聯(lián)系類型;2）將實體及聯(lián)系轉(zhuǎn)換為關(guān)系模型，并注明主鍵和外部鍵。正確答案： 1)圖：省略 2)關(guān)系模型為： 讀者（讀者號，姓名，地址，性別，年齡，單位） 主鍵：讀者號 ， 外部鍵：無圖書（書號，書名，作者，出版社，ISBN，價格） 主鍵：書號 ， 外部鍵：無借書（讀者號，書號，借出日期，應(yīng)還日期） 主鍵：（讀者號，書號），外部鍵：讀者號 ； 外部鍵：書號 問題 5 需要評分 現(xiàn)有關(guān)于學(xué)會、學(xué)生、課程、教師的信息如下：學(xué)生的屬性有：學(xué)號、姓名、性別、年齡；課程的屬性有：課程號、課程名、學(xué)分；教師的屬性有：教師號、姓名、職稱、專業(yè)；學(xué)會的屬性有：學(xué)會名稱、成立時間、負(fù)責(zé)人姓名、會費(fèi)。假設(shè)每門課程可由多位教師講授，每位教師只講授一門課程；每個學(xué)生可加入多個學(xué)會，學(xué)生進(jìn)入學(xué)會有一個入會年份。學(xué)生可選修多門課程，每個學(xué)生選修的每門課程有一個成績記載。1）根據(jù)語義畫出E-R圖，并在圖上注明聯(lián)系類型；2）將E-R圖轉(zhuǎn)換成關(guān)系模式，并根據(jù)屬性的現(xiàn)實意義指明主鍵和外鍵。所選答案： 學(xué)生（學(xué)號，姓名，性別，年齡）課程（課程號，課程名，學(xué)分）教師（教師號，姓名，職稱，專業(yè)）學(xué)會（學(xué)會名稱，成立時間，負(fù)責(zé)人姓名，會費(fèi) ）選課（學(xué)號，課程號，成績）入會（學(xué)號，學(xué)會名稱，入會年份）正確答案： 1）E-R圖省略2）關(guān)系模式：學(xué)生(學(xué)號、姓名、性別、年齡) 學(xué)號為主鍵課程(課程號、課程名、學(xué)分、課程號) 課程號為主鍵,，課程號為外鍵教師(教師號、姓名、職稱、專業(yè)) 教師號為主鍵學(xué)會(學(xué)會名稱、成立時間、負(fù)責(zé)人姓名、會費(fèi)) 學(xué)會名稱為主鍵選修(學(xué)號、課程號、成績) 學(xué)號和課程號聯(lián)合為主鍵，學(xué)號和課程號均為外鍵入會(學(xué)號、學(xué)會名稱、入會年份) 學(xué)號和學(xué)會名稱聯(lián)合為主鍵，學(xué)號和學(xué)會名稱為外鍵問題 6 需要評分 設(shè)有商店和顧客兩個實體，“商店”有屬性商店編號、商店名、地址、電話，“顧客”有屬性顧客編號、姓名、地址、年齡、性別。假設(shè)一個商店有多個顧客購物，一個顧客可以到多個商店購物，顧客每次去商店購物有一個消費(fèi)金額和日期，而且規(guī)定每個顧客在每個商店里每天最多消費(fèi)一次。試畫出E-R圖，注明屬性和聯(lián)系類型，并將E-R模型轉(zhuǎn)換成關(guān)系模式，并要求指出關(guān)系模式主碼、外碼。所選答案： 商店（商店編號，商店名，地址，電話）顧客（顧客編號，姓名，地址，年齡，性別）購物（商店編號，顧客編號，消費(fèi)金額，日期）正確答案： 關(guān)系ER圖（略）。關(guān)系模型 顧客（顧客編號，姓名，地址，年齡，性別），主鍵：顧客編號商店（商店編號，商店名，地址，電話），主鍵：商店編號）購物（顧客編號，商店編號，消費(fèi)日期，消費(fèi)金額），主鍵：（顧客編號，商店編號，消費(fèi)日期），這里考慮到每天消費(fèi)一次的限制。 外部鍵：顧客編號，商店編號問題 7 需要評分 某企業(yè)集團(tuán)有若干工廠，每個工廠生產(chǎn)多種產(chǎn)品，且每一種產(chǎn)品可以在多個工廠生產(chǎn)，每個工廠按照固定的計劃數(shù)量生產(chǎn)產(chǎn)品；每個工廠聘用多名職工，且每名職工只能在一個工廠工作，工廠聘用職工有聘期和工資。工廠的屬性有工廠編號、廠名、地址，產(chǎn)品的屬性有產(chǎn)品編號、產(chǎn)品名、規(guī)格，職工的屬性有職工號、姓名。（1）根據(jù)上述語義畫出E-R圖；（2）將該E-R模型轉(zhuǎn)換為關(guān)系模型；（要求：1:1和1:n的聯(lián)系進(jìn)行合并）（3）指出轉(zhuǎn)換結(jié)果中每個關(guān)系模式的主碼和外碼。 所選答案： 工廠（工廠編號，廠名，地址）產(chǎn)品（產(chǎn)品編號，產(chǎn)品名，規(guī)格）職工（職工號，姓名）聘用（職工號，工廠編號，聘期，工資）生產(chǎn)（工廠編號，產(chǎn)品編號，產(chǎn)品數(shù)）正確答案： （1）ER圖略 （2）轉(zhuǎn)化后的關(guān)系模式如下：工廠（工廠編號，廠名，地址）產(chǎn)品（產(chǎn)品編號，產(chǎn)品名，規(guī)格）職工（職工號，姓名 ）聘用（職工號，工廠編號，聘期，工資） ，因為工廠與職工是1：n的聯(lián)系，因此主碼是職工號生產(chǎn)（工廠編號，產(chǎn)品編號，計劃數(shù)量） ，因為工廠與產(chǎn)品是n:m的聯(lián)系，因此主碼是（工廠編號，產(chǎn)品編號）（3）每個關(guān)系模式的主碼、外碼如下：工廠（工廠編號，廠名，地址）： 主碼: 工廠編號，外碼:無；產(chǎn)品（產(chǎn)品編號，產(chǎn)品名，規(guī)格）： 主碼: 產(chǎn)品編號，外碼:無；職工（職工號，姓名，工廠編號，聘期，工資）主碼：職工號，外碼：工廠編號；生產(chǎn)（工廠編號，產(chǎn)品編號，計劃數(shù)量） 主碼（工廠編號，產(chǎn)品編號），外碼：工廠編號，外碼：產(chǎn)品編號 問題 8 需要評分 假設(shè)某商業(yè)集團(tuán)數(shù)據(jù)庫中有一關(guān)系模式R如下：R（商店編號，商品編號，商品庫存數(shù)量，部門編號，負(fù)責(zé)人）如果規(guī)定：（1）每個商店的每種商品只在該商店的一個部門銷售；（2）每個商店的每個部門只有一個負(fù)責(zé)人；（3）每個商店的每種商品只有一個庫存數(shù)量。試回答下列問題（1）根據(jù)上述規(guī)定，寫出關(guān)系模式R的基本函數(shù)依賴；（2）找出關(guān)系模式R的候選碼；（3）試問關(guān)系模式R最高已經(jīng)達(dá)到第幾范式？為什么？（4）如果R不屬于3NF,請將R分解成3NF模式集。正確答案： 1）根據(jù)題意，F(xiàn)=（商店編號，商品編號）部門編號，（商店編號，部門編號）負(fù)責(zé)人，（商店編號，商品編號）商品庫存數(shù)量2）根據(jù)F， 求(商店編號，商品編號)F+ =商店編號，商品編號，部門編號，負(fù)責(zé)人，商品庫存數(shù)量,因此，R的唯一候選碼是（商店編號，商品編號）3）根據(jù)F：（商店編號，商品編號）部門編號， 而（商店編號，商品編號）商店編號（這是平凡函數(shù)依賴），得到：（商店編號，商品編號）（商店編號，部門編號） （這是根據(jù)Armastrong的合并原則）根據(jù)：（商店編號，商品編號）（商店編號，部門編號）,（商店編號，部門編號）負(fù)責(zé)人得到：（商店編號，商品編號）負(fù)責(zé)人， 即非主屬性“負(fù)責(zé)人”對候選碼 （商店編號、商品編號）傳遞函數(shù)依賴。所以R屬于2NF，R不屬于3NF。4）將R分解成：R1（商店編號，商品編號，商品庫存數(shù)量，部門編號），候選碼：（商店編號，商品編號），R13NFR2（商店編號，部門編號，負(fù)責(zé)人）,候選碼：（商店編號，部門編號），R23NF問題 9 需要評分 現(xiàn)有如下關(guān)系模式：借閱（圖書編號，書名，作者名，出版社，讀者編號，讀者姓名，借閱日期，歸還日期），基本函數(shù)依賴集F=圖書編號（書名，作者名，出版社），讀者編號讀者姓名，（圖書編號，讀者編號，借閱日期）歸還日期（1）讀者編號是候選碼嗎？（2）寫出該關(guān)系模式的主碼。（3）該關(guān)系模式中是否存在非主屬性對碼的部分函數(shù)依賴？如果存在，請寫出一個。（4）該關(guān)系模式滿足第幾范式？并說明理由。 正確答案： （1）不是。因為它不能決定關(guān)系中的每個屬性。（2）求（圖書編號，讀者編號，借閱日期）的閉包，閉包包含了關(guān)系中的每個屬性，且任何（圖書編號，讀者編號，借閱日期） 的子集的閉包都不能包含關(guān)系中的所有屬性，因此，（圖書編號，讀者編號，借閱日期）的關(guān)系的一個候選碼。（3）存在。（圖書編號，讀者編號，借閱日期）書名、（圖書編號，讀者編號，借閱日期）作者名、（圖書編號，讀者編號，借閱日期）出版社、（圖書編號，讀者編號，借閱日期）讀者姓名（四個部分函數(shù)依賴任選一個即可）（4）1NF。因為存在非主屬性對碼的部分函數(shù)依賴。 問題 10 需要評分 某工廠生產(chǎn)若干產(chǎn)品，每種產(chǎn)品由不同的零件組成，有的零件可用在不同的產(chǎn)品上。這些零件由不同的原材料制成，不同零件所用的原材料可以相同。這些零件按所屬的不同產(chǎn)品分別放在倉庫中，原材料按照類別放在若干倉庫中。產(chǎn)品屬性：產(chǎn)品號，產(chǎn)品名；零件屬性：零件號，零件名；原材料屬性：原材料號，原材料名，類別；倉庫屬性：倉庫號，倉庫名。請完成如下設(shè)計： 1)用E-R圖畫出概念模型，并注明聯(lián)系的類型。 2)將該模型轉(zhuǎn)換為關(guān)系模型，并指出每個關(guān)系模式的主碼。正確答案： 1）找出實體：產(chǎn)品，零件，原材料，倉庫，根據(jù)題意畫出E-R圖：略。2）實體轉(zhuǎn)換為關(guān)系表:產(chǎn)品（產(chǎn)品號，產(chǎn)品名），主鍵：產(chǎn)品號零件（零件號，零件名），主鍵：零件號原材料（原材料號，原材料名，