第六次作業(yè)-數(shù)據(jù)庫建模

具體要求：

1、作業(yè)組織：先在作業(yè)本完成每題的答題，再將作業(yè)本中的每頁答題拍照貼到答題卡文件相應位置，答題卡文件命名：學號姓名-第6次作業(yè).doc（例如2020011111張三-第6次作業(yè).doc），注意排版格式。

2、遞交方式：在【優(yōu)學院】—【作業(yè)】相應欄目上傳自己的作業(yè)答題卡文件供老師批改，批改后返回給大家再上傳到網(wǎng)安學院作業(yè)系統(tǒng)的相關欄目。（注意：時間限制）。

?

第4章 數(shù)據(jù)庫建模

P154-156 ???4.2、4.3、4.4?

答案如下：

4.2 ?某企業(yè)的供應商、商品、倉庫之間存在多對多的三元聯(lián)系集“供應-庫存”， 如圖4-75所示。請在調(diào)研企業(yè)應用需求的基礎上，將該三元聯(lián)系集轉(zhuǎn)化為二元聯(lián)系集。

供應商與倉庫之間直接發(fā)生聯(lián)系不符合業(yè)務語義要求。剩下2種建模情況如下所示：

1、如何理解“供應-庫存”業(yè)務語義

????1）“供應”業(yè)務相當于“采購”業(yè)務，一個是站在供應商的角度命名，一個是站在企業(yè)員工的角度命名。

????2）“庫存”管理包括“入庫”和“出庫”管理，但對應“供應”業(yè)務的就只有“入庫”管理了?！?/span>出庫”管理與“銷售”業(yè)務相對應。為了便于理解，下面都將“庫存”改為“入庫”，“庫存量”改為“入庫量”。

2、圖(a)建模方案的業(yè)務語義分析

1）聯(lián)系集入庫反映企業(yè)每一種商品在各倉庫的入庫業(yè)務，主碼是{商品編號, 倉庫號}，聯(lián)系集入庫的聯(lián)系屬性有入庫量、單價、入庫日期等。

2）聯(lián)系集供應反映各供應商為企業(yè)每一次商品入庫(同一種商品在同一個倉庫的入庫，對應聯(lián)系實體集入庫，主碼是{商品編號, 倉庫號})的供應業(yè)務，主碼有{供應商號, 商品編號, 倉庫號}，聯(lián)系屬性有供應量、單價、供應日期等。

3）由于一種商品在同一個倉庫會發(fā)生多次入庫業(yè)務，因此入庫是一個多對多的多值聯(lián)系集?？蓪?/span>多對多的多值聯(lián)系集入庫轉(zhuǎn)化建模為依賴實體集入庫單：

4）由于一個供應商可為每一次商品入庫(同一種商品在同一個倉庫的入庫)發(fā)生多次供應業(yè)務，因此供應是一個多對多的多值聯(lián)系集?？蓪?/span>多對多的多值聯(lián)系集供應轉(zhuǎn)化建模為依賴實體集供應單。

5）因此，圖(a)建模方案的業(yè)務語義是：先有商品入庫，再有商品供應；即在辦理商品入庫業(yè)務時，再明確每一種入庫商品的商品供應情況。

????顯然，該建模方案不是太符合實際業(yè)務語義。

?

?

?

3、圖(b)建模方案的業(yè)務語義分析

1）聯(lián)系集供應反映每一個供應商向企業(yè)提供每一種商品的供應業(yè)務，主碼是{供應商號, 商品編號}，聯(lián)系集供應的聯(lián)系屬性有供應量、單價、供應日期等。

2）聯(lián)系集入庫反映各倉庫為每一次商品供應(同一個供應商對同一種商品的供應，對應聯(lián)系實體集供應，主碼是{供應商號, 商品編號})的入庫業(yè)務，主碼是{倉庫號, 供應商號, 商品編號}，聯(lián)系集入庫的聯(lián)系屬性有入庫量、單價、入庫日期等。

3）由于一個供應商對同一種商品會發(fā)生多次供應業(yè)務，因此供應是一個多對多的多值聯(lián)系集?？山橐蕾噷嶓w集供應單：

4）由于一個倉庫可為每一次商品供應(同一個供應商對同一種商品的供應)發(fā)生多次入庫業(yè)務，因此入庫是一個多對多的多值聯(lián)系集。可建模為：

5）因此，圖(b)建模方案的業(yè)務語義是：先有商品供應，再有商品入庫；即先辦理商品供應業(yè)務，待每一種供應商品到貨時再辦理商品入庫手續(xù)。

4、總之，圖(b)的建模方案能更好地反映三元聯(lián)系集“供應-庫存”的業(yè)務語義。

4.3 ?請將圖4-16所示的多值聯(lián)系集“貸款”分別建模為一個“貸款單”依賴實體集或弱實體集，并畫出相應的E-R圖。

?

如果將圖4-16所示的多值聯(lián)系集貸款建模為一個弱實體集——貸款單，它的屬性有貸款編號、貸款日期和貸款金額等，其中貸款編號為部分碼。貸款單弱實體集同時依賴于客戶實體集和銀行實體集，對應的標識聯(lián)系集分別是借貸和放貸。圖1所示的是建模結果。

圖1 ?將多值聯(lián)系貸款建模為弱實體集貸款單

也可以考慮將圖4-16所示的多值聯(lián)系集貸款直接建模為一個依賴實體集——貸款單，它的屬性有貸款編號、貸款日期和貸款金額等，其中貸款編號為主碼，唯一標識一張貸款單。顯然，貸款單實體集是同時依賴于客戶的“借貸”行為和銀行的“放貸”行為而存在的，因此可以引入實體集客戶與貸款單之間的“借貸”聯(lián)系集和實體集銀行與貸款單之間的放貸聯(lián)系集，且貸款單實體集是同時依賴于借貸和放貸聯(lián)系集。建模結果如圖2所示。

圖2 ?將多值聯(lián)系貸款建模為(強)實體集貸款單

如果允許多個客戶聯(lián)合借一筆貸款，則“借貸”可改為多對多聯(lián)系集“借貸明細”，且需要聯(lián)系屬性客戶借款額。

同理，如果允許多個銀行聯(lián)合發(fā)放一筆貸款，則“放貸”可改為多對多聯(lián)系集“放貸明細”，且需要聯(lián)系屬性銀行貸款額。

?

4.4??假定一個銷售公司的業(yè)務涉及如下基本實體：

(1) 職工：職工號、姓名、性別、電話、住址；

(2) 商品：商品編號、商品名稱、型號、計量單位、供貨商、進貨單價、庫存數(shù)量、銷售單價；????——庫存數(shù)量為派生屬性

——供貨商是獨立的實體集，E-R模型中需要通過聯(lián)系集(可轉(zhuǎn)化為關系表的外碼)來反映；

——進貨單價、銷售單價分別與進貨、銷售業(yè)務有關，應該是聯(lián)系集的屬性。

(3) 供貨商：供貨商編號、供貨商名稱、聯(lián)系電話、通信地址；

(4) 客戶：客戶編號、客戶名稱、聯(lián)系電話、通信地址。

假設每種商品可從多個供貨商采購，每個供貨商可供應多種商品；每個供貨商的每種商品可銷售給多個客戶，每個客戶可購買多個供貨商提供的多種商品。請根據(jù)你對銷售公司業(yè)務的理解進行數(shù)據(jù)庫設計，要求：

(1) 定義必要的實體集及其屬性。

(2) 設計該銷售系統(tǒng)的E-R模型，E-R圖重點反映實體集之間的聯(lián)系和聯(lián)系屬性，需標出聯(lián)系的映射基數(shù)。

(3) 將E-R模型轉(zhuǎn)化為關系數(shù)據(jù)庫模式，并指出每一個關系模式的主碼和外碼。

1) 實體集及其屬性

(1) 商品：商品編號、商品名稱、型號、計量單位、庫存數(shù)量；

(2) 客戶：客戶編號、客戶名稱、聯(lián)系電話、通信地址；

(3) 職工：職工號、姓名、性別、電話、住址；

(4) 供貨商：供貨商編號、供貨商名稱、聯(lián)系電話、通信地址；

(5) 銷貨單：銷貨單編號、銷售日期、銷售總金額，銷貨單是依賴實體集，它同時依賴于商品、客戶、職工實體集；

(6) 進貨單：進貨單編號、進貨日期、進貨總金額，進貨單是依賴實體集，它同時依賴于商品、供貨商、職工實體集。

2)?聯(lián)系集及E-R圖

(1) 銷貨單與商品實體集之間存在多對多聯(lián)系集銷售，聯(lián)系屬性有：銷售數(shù)量、銷售單價、銷售金額；

(2) 銷貨單與客戶實體集之間存在多對一聯(lián)系集購買，無聯(lián)系屬性；

(3) 銷貨單與職工(銷售員)實體集之間存在多對一聯(lián)系集出售，無聯(lián)系屬性；

(4) 進貨單與商品實體集之間存在多對多聯(lián)系集進貨，聯(lián)系屬性有：進貨數(shù)量、進貨單價、進貨金額；

(5) 進貨單與供貨商實體集之間存在多對一聯(lián)系集供貨，無聯(lián)系屬性；

(6) 進貨單與職工(采購員)實體集之間存在多對一聯(lián)系集采購，無聯(lián)系屬性。

——商品實體集中的庫存數(shù)量是派生屬性，它是根據(jù)進貨聯(lián)系集中的進貨數(shù)量和銷售聯(lián)系集中的銷售數(shù)量計算而得到，反映每一種商品的當前庫存情況。無法反映每個會計核算期的期初庫存、本期入庫、本期出庫、期末庫存情況。

4.5 ?為本章4.7節(jié)的大學選課系統(tǒng)安排期末考試考場，供學生和教師查詢考試信息。要求如下：

(1) 一門課程的所有開課班應安排在相同時間進行考試，不同課程的開課班可以安排在相同或不同的時間進行考試；

(2) 一個開課班的學生可能安排在多個考場參加考試，一個考場也可以包含同一門課程的多個開課班的學生，但不允許將選修不同課程的學生安排在同一考場考試(該語義也可以進行修改)；

(3) 一個考場根據(jù)參加考試的學生人數(shù)安排2至4名監(jiān)考老師，其中指定一名老師為主監(jiān)考老師；

(4) 一個學生選修的多門課程不能安排在同一時間進行考試；

(5) 一個老師不能安排在同一時間參加多個考場的監(jiān)考；

(6) 一個教室在同一時間不能安排多場考試；

(7) 安排在同一考場參加考試的學生人數(shù)不能超過該教室的考試容量(通常情況下，一個教室的考試容量不會超過其上課容量的一半)。

請你在對教務處進行調(diào)研的基礎上進行數(shù)據(jù)庫設計，要求：

(1) 定義必要的實體集及其屬性。

(2) 設計該考試安排的E-R模型，E-R圖反映實體集之間的聯(lián)系和聯(lián)系屬性，需標出聯(lián)系的映射基數(shù)；并通過數(shù)據(jù)字典定義E-R圖中的每一個實體集的屬性。

(3) 將E-R模型轉(zhuǎn)化為關系數(shù)據(jù)庫模式，并指出每一個關系模式的主碼和外碼。

(1)?學生排考聯(lián)系集反映的是學生考場安排情況，即一個考場安排了哪些學生參加考試？某課程所有考場的學生排考聯(lián)系集來自于該課程的所有選課學生(即該課程所有開課班的選課學生)。考試人數(shù)為派生屬性，可從學生排考聯(lián)系集中統(tǒng)計得到。

(2)?座位號反映的是一個學生在某個考場的座位序號——該考場內(nèi)的流水編號。

(3)?監(jiān)考角色的取值為主監(jiān)考或輔監(jiān)考。

(4)?一門課程的所有考場的考試時間要求相同，不同課程可以安排在相同或不同的時間進行考試。要求滿足：一個學生選修的多門課程不能安排在同一時間進行考試；一個老師不能安排在同一時間參加多個考場的監(jiān)考，一個教室在同一時間不能安排多場考試。

可轉(zhuǎn)化為下圖（由于考試時間實體集只有主碼屬性考試時間，沒有更多的屬性，因此，考試時間沒有必要作為實體集建模，而是直接建模為考場實體集的屬性）：

試比較上下圖差別

弱實體集和排監(jiān)考聯(lián)系集產(chǎn)生的關系模式如下：

ExamRoom (courseNo, examRoomNo?, examTime, examCount, classroomNo)

Invigilate?(courseNo, examRoomNo?, teacherNo, invigiRole)

說明：帶下劃線的屬性為主碼，斜體屬性為外碼。

對于上圖，學生排考聯(lián)系集和選課聯(lián)系集都是多對多的，將分別單獨產(chǎn)生如下關系模式：

StudentExam?(studentNo, courseNo, examRoomNo?, seatNo)

Enroll (studentNo, courseNo, cClassNo?, score) ??-- 選課系統(tǒng)中的關系表

對于下圖，學生排考聯(lián)系集是多對一的，不單獨產(chǎn)生關系模式，學生排考聯(lián)系集的信息將加入到選課聯(lián)系集產(chǎn)生的關系模式中：

??Enroll (studentNo, courseNo?, cClassNo?, score, examRoomNo, seatNo)

?

其他關系模式略去。

?

說明：

對于關系模式ExamRoom(courseNo, examRoomNo, examTime, examCount, classroomNo)，主碼是{?courseNo, examRoomNo?}，但courseNo→examTime，因此，存在部分函數(shù)依賴。為了減少連接運算，允許該部分函數(shù)依賴的存在。

另一種建模方案是：將examTime作為與課程實體集Course相關聯(lián)的屬性。由于一門課程每學期(或?qū)W年)都要安排考試，如何保留歷史排考信息呢？

?

學院是否作為實體集建模？

?

4.6?試根據(jù)圖4-62的內(nèi)容，設計交通違章處罰數(shù)據(jù)庫的E-R圖并轉(zhuǎn)化為關系模式。注意，一張違章單可能有多種處罰。

E-R圖可轉(zhuǎn)化為以下關系模式：

(1) 車主/司機OwnerDriver：(包括有車無照、有照無車、有照有車三種情況)

????OwnerDriver?(ownerDriverID,?driverName,?address,?telephone)

(2) 駕照DrivingLicence：

????DrivingLicence?(licenceNo, ownerDriverID, licenceTime, punishPointSum)

(3) 機動車Vehicle：

????Vehicle?(vehicleNo, ownerDriverID,?model,?producterName,?madeTime,?getcardTime)

(4) 警察實體集Police：

????Police?(policeNo, policeName, department)

(5) 違章分類Violation：

????Violation (violationNo, violationName, punishPointLower, punishPointUpper, punishMoneyLower, punishMoneyUpper)??--?罰分、罰款的范圍

(6) 處罰單Ticket：-- 還可以進一步反映處罰單的執(zhí)行情況(如在何時、何銀行交納了罰款)

????Ticket?(ticketNo,?violationTime,?violationSite,?description,?punishTime, punishPointSum, punishMoneySum, licenceNo,?vehicleNo,?policeNo)

?(7) 違章處罰明細TicketViolation：

????TicketViolation (ticketNo,?violationNo, punishPoint, punishMoney)

說明：——以下反映的是警察在執(zhí)法時的處罰量罪權力

(1) 違章處罰明細TicketViolation中的punishPoint取值只能在違章分類Violation中的[punishPointLower, punishPointUpper]范圍內(nèi)，punishMoney類似。

(2) 處罰單Ticket中的punishPointSum, punishMoneySum表示數(shù)罪并罰的結果，因此，它的語義分別是：可以等于、也可以小于違章處罰明細TicketViolation中的punishPoint, punishMoney的合計。

?

?

4.8??假定一個醫(yī)院門診系統(tǒng)的開處方和繳費開發(fā)票業(yè)務涉及如下基本實體：

(1) 職工：職工號、姓名、性別、電話、住址；

(2) 藥品：藥品號、藥品名、計量單位、進貨單價、庫存數(shù)量、銷售單價；

(3) 發(fā)票：發(fā)票號、開票日期、業(yè)務摘要、發(fā)票金額、開票職工；

(4) 病人：病人號、姓名、性別、出生日期、聯(lián)系電話。

業(yè)務語義如下：

(1) 假設每個處方可以開具多種藥品，每種藥品可在多個處方中開具，且每個處方中需要記錄處方號、開具日期、處方金額、開具處方的醫(yī)生、對應的病人以及藥品明細(即每種藥品的數(shù)量、單價、金額、每天的用藥次數(shù)、每次的用量等)；

(2) 每個處方可分多次繳費，且每次繳費對應一張發(fā)票，但一次繳費不允許跨多個處方；每次繳費(即每張發(fā)票)需要明確該次繳費(該張發(fā)票)所對應的藥品明細，且一個處方中開具的某種藥品只能一次繳費，即允許將一個處方中開具的所有藥品分多次繳費，但不允許將某種藥品開具的數(shù)量分拆繳費。

請根據(jù)上述對開處方和繳費開發(fā)票業(yè)務的描述進行局部數(shù)據(jù)庫設計，要求：

(1) 定義必要的實體集及其屬性。

(2) 設計開處方和繳費開發(fā)票業(yè)務的局部E-R模型，E-R圖重點反映實體集之間的聯(lián)系和聯(lián)系屬性，需標出聯(lián)系的映射基數(shù)。

(3) 將局部E-R模型轉(zhuǎn)化為關系數(shù)據(jù)庫模式，并指出每一個關系模式的主碼和外碼。

繳費業(yè)務的消費明細，則應該如何建模？

——如果允許將每種藥品開具的數(shù)量分拆開發(fā)票/繳費，則應該如何建模？

?