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

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