数据库ER图练习及答案.pdf

4013 小游戏游戏交友 4013 小游戏游戏交友 DBDBDBDB ModelingModelingModelingModeling ExamExamExamExam PracticalPracticalPracticalPractical Answer the following questions 1 Create an E R schema for a database system used to manage account information at a community bank The bank has several branches with unique names A customer may have one or more accounts in one or more branches An account must belong to one and only one branch Each account is operated on by transactions which may be deposits to or withdrawals from some account The database keeps track of all the transactions on each account in addition to the balance of individual accounts and the assets of individual branches For each entity specifyallits attributes primary key and alternate key s In your ER schema be sure to capture the cardinality constraints and participation constraints of all relationships Make reasonable assumptions to complete the specification Explicitly state all your assumptions EVERY construct in your ER schema should be substantiated by either the specification above or your explicit assumptions 2 The following table stores information about which suppliers can supply which parts The table captures the fact thata part whose name is PartName and whose ID is PartID can besupplied by suppliers whose names are in SupplierName and whose IDs are in SupplierID Note that a part can be supplied by many suppliers and a supplier can supply many parts CAN SUPPLYCAN SUPPLY PartIDPartIDPartNamePartNameSupplierIDSupplierIDSupplierNameSupplierName 1234Nut223ProMetal 1234Nut224Biscayne 2134Bolt223ProMetal 3 Perform the following tasks 1 List the primary key 2 List all the FDs 3 What normal form is the relation in Explain 4 Apply normalization to it incrementally carrying the normalization process through each of the higher normal forms possible up to 3NF Thatis ifthe relation were unnormalized bring it to first normal form then bring the first normal form you ve just created to second normal form and then bring the second normal form to third normal form For each transformation to the next higher normal form X Explain the steps you took to bring it to the normal form X Provide the normal form X s table structure primary key s and the FDs Explain why you think it is in the normal form X For example ifyou think there is a partial dependency fully defend your conclusion by explaining how a column is partially dependent on some 4013 小游戏游戏交友 4013 小游戏游戏交友 other column s That is ifthe relation were in an unnormalized form you would explain the transformation you performed to bring it to first second and third normal forms You would also provide the table structure the primary key and the FDs for the first second and third normal forms You would also provide explanation for why you believe it is in first second and third normal forms 4 Convertthe following E R schema into a relational schema using the mapping algorithm specified in this course Specify key and referential integrity constraints using directed arcs Make sure you also identify alternate keys Label each step of the mapping algorithm Answer 1 1 1 1 4013 小游戏游戏交友 4013 小游戏游戏交友 Bank Account Customer Transaction Branch BankNameBankPhone BranchAddr BranchName Has N 1 BranchPhone Assets CustName PhoneNum CustID AccountNo Balance OperationType TID TDateTime AofBranch TofAccoun Open 1 N M 1 1 N Entity 1 Bank BankName BankPhone BankPhone is a multi valued attribute PK BankName 2 Cutomer CustID CustName PhoneNum PK CustID AK PhoneNum 3 Branch BranchName BranchAddr BranchPhone Assets BranchPhone is a multi valued attribute PK BranchName 4 Account AccountNo Balance PK AccountNo 5 Transaction TID OperationType TDateTime PK TID Relations 1 Has 1 N PARTIAL TOTAL 2 Open 1 N PARTIAL TOTAL 3 AofBranch 1 N PARTIAL TOTAL 4 TofAccount 1 N PARTIAL TOTAL Assumptions 1 Anew bank can establish no branch 2 One normal bank establishes one or morebraches 3 Abank has oneor moretelephones for customers 4 Acustomer can open one or moreAccount 5 An account must belong to one and only one branch 4013 小游戏游戏交友 4013 小游戏游戏交友 6 One branch opens oneor more accounts 7 Abranch has oneor more telephones for customers 8 An account belongs to just onebranch 2 2 2 2 1 pk PartID SupplierID 2 FDs FD1 PartID PartName FD2 SupplierID SupplierName 3 Therelation isinthe first normal form 1NF Each attribute of therelation allows asingle atomic value soitisin1NF But somenone primary key attributes such as PartNameand SupplierName partially dependant on the primary key asFD1 and FD2 show soitis notin2NF 4 Normalization 1 FD1 PartID PartName Therelation can be decomposited into two relations PART PartID PartName FDs PartID PartName PK PartID CAN SUPLY PartID SupplierID SuplierName FDs SupplierID SupplierName PK PartID SupplierID Therelation PART is nowinthe third normal form becausethe only none primary key attribute PartName fully not partially and directly not transively dependants on the primary key PartId Therelation CAN SUPPLY is stillinthe first normal becausetheonly none primary key attribute SupplierName partially dependants on the primary key PartId SupplierID 2 CAN SUPLY PartID SupplierID SuplierName FDs SupplierID SuplierName For SupplierID SuplierName therelation can be decomposited into two relations SUPPLIER SupplierID SupplierName FDs SupplierID SuplierName PK SupplierID CAN SUPLY PartID SupplierID FDs PK PartID SupplierID Both relations areinthe third normal form becausefor each one no none primary key attribute patially or transively dependants on its primary key 3 Three3NF relations PART PartID PartName FDs PartID PartName PK PartID SUPPLIER SupplierID SupplierName FDs SupplierID SuplierName PK SupplierID CAN SUPLY PartID SupplierID FDs PK PartID SupplierID 3 九步算法 4013 小游戏游戏交友 4013 小游戏游戏交友 三种异常 修改异常 插入异常 删除异常 S1 每一强实体用一个新表表示 S2 处理参与 1 1 标识联系的弱实体 W S3 处理参与 1 N 标识联系的弱实体 W S4 处理每一二元 1 1 联系 R 确定参与该联系的实体型对应的表 S 和T 将 T 的主码作为外码加入 S 将 R 的所 有简单属性和复合属性成分作为列加入 S S5 处理每一二元 1 N 联系 R 确定处于 N 端的实体表 S 和 1 端的实体表 T 将 T 的主码作为外码加入 S 将 R 的所有简单属性和复合属性成分作为列加入 S S6 处理每一 N 元联系 包括二元 M N 联系 对应新表 T 将 R 的所有简单属性和复合属性成分作为列加入T 将参与联系的 强 弱 实体型的主码作为外码加入 T 所有外码组合 共同构成 T 的主码 S7 处理每一多值属性 A 将 A 的所有简单属性和复合属性成分作为列加入 T 将 A 所属的实体或联系型的主码 作为外码加入 T 将 上步得到的 外码和 A 对应的属性确定为 T 的主码 S8 处理每一非相交子类的特化 S9 处理每一相交子类的特化 S1 T1 Coach Name Age PK Name T2 Team Name PK Name T3 Player Name Age PK Name T4 Game Number Score Time Date PK Number T5 Stadium Name Size Location PK Name S2 处理参与 1 1 标识符联系的弱实体 S3 处理参与 1 N 标识符联系的弱实体 S4 T2 Team Name CoachName PK Name FK CoachNamereferences Coach Name S5 T3 Player Name Age TeamName PK Name FK TeamName referencesTeach Name S6 T6 Practice TeamName StadiumName Date PK TeamName StadiumName 4013 小游戏游戏交友 4013 小游戏游戏交友 FK TeamName referencesTeach Name FK StadiumName references Stadium StadiumName T7 PlaysWith HostName VisitorName GameNumber PK GameNumber FK HostName references Team name FK VisitorName referencesTeam name S7 T8 TeamColor TeamName color PK TeamName color FK TeamName referencesTeam name Thelast seven tables T1 Coach Name Age PK Name T2 Team Name CoachName PK Name FK CoachNamereferences Coach Name T3 Player Name Age TeamName PK Name FK TeamName referencesTeach Name T4 Game Number Score Time Date PK Number T5 Stadium Name Size Location PK Name T6 Practice TeamName StadiumName Date PK TeamName StadiumName FK TeamName referencesTeach Name FK StadiumName references Stadium StadiumName T7 PlaysWith HostName VisitorName GameNumber PK GameNumber FK HostName references Team name FK VisitorName referencesTeam name t8 TeamColor TeamName color PK TeamName color FK TeamName referencesTeam name T4 and T7 have the sameprimary key so they can be joined to anew table NormalizationNormalization Provide your solution to the followingina file namednormalization txt Note The discussioninthis exercise is independent of i e completed unrelated to the E Commerceproject describedinthe Appendix 4013 小游戏游戏交友 4013 小游戏游戏交友 Thefollowing table captures the following fact about an E Commercebookstore the employee whosename is EmpName and whoseID is EmpID has shipped the order whoseOrder Number is OrderNo to theaddress ShipToAddr on the dateShippedDate Thetracking number forthe shipment is TrackingNum TheTrackingNum is provided by thecourier company that picks up the shipment The bookstoreuses only one courier company Note that a single order could besplit up into multiple shipments based on the availability of the ordered items Only one employee handles a shipment However multiple employees could handle an orderifthe order is shippedinmultiple shipments SHIPMENTSHIPMENTSHIPMENTSHIPMENTEmpIDEmpIDEmpIDEmpIDEmpNameEmpNameEmpNameEmpNameOrderNoOrderNoOrderNoOrderNoShipToAddrShipToAddrShipToAddrShipToAddrShippedDateShippedDateShippedDateShippedDateTrackingNumTrackingNumTrackingNumTrackingNum 1234Joe223 4615 Forbes Ave Pittsburgh PA 15147 12 21 9912435678 2134Jones224 4615 Forbes Ave Pittsburgh PA 15147 12 25 9921345678 1 List the primary key 2 ListalltheFDs 3 Listalltheupdate anomalies and provide an example of each 4 What normal form is the relation in Explain 5 Apply normalization toitincrementally bringing the relation to 3NF That is ifthe relation is unnormalized bringitto first normal form then bring thefirst normal form you ve just created to second normal form and then bring the second normal form to third normal form For each transformation to the next higher normal form X oExplain the steps you took to bringitto the normal form X oProvide the normal formX s table structure primary key s and the FDs oExplain why you thinkitisinthe normal form X That is iftherelation wereinan unnormalized form you would explain thetransformation you performed to bringitto first second and third normal forms You would also provide the table structure theprimary key and the FDsfor the first second and third normal forms You would also provide explanation for why you believeitisinfirst second and third normal forms Answer 1 PK TrackingNum 2 FD1 EmpID EmpName FD2 OrderNo ShipToAddr FD3 TrackingNum EmpID OrderNo ShippedDate 3 A A A A Aupdate anomaly It alway takes inconsistencies For example an employee have shipped more than one shipment his employee ID and name repeated more than once When we must change the name of this employee with particular 4013 小游戏游戏交友 4013 小游戏游戏交友 EmpID wehave to change many rows If wejust changes one row these rowswith the sameEmpId but have different names so which leads to inconsistencies B B B B Ainsertion anomaly It prevents us from keeping some necessary information For example ifa new employee have never shipped any goods his namecannot be stored C C C C Adeletion anomaly It leads to an unintended drop of data For example information about an employee such as his name is lost when the only shipment he has done has been deleted 4 Thenormal relation isin2NF The 2NF is every attribute of the relation allows a single atomic value firstly itisin1NF Further more the primary key is a single attribute allnone primary key attributes must completely depends on the primary key secondly itisin 2NF lastly existing some noe primary key attributes transferable depends on the primay key For example EmpID EmpName soitis notin3NF 5 A F EmpID EmpName OrderNo ShipToAddr TrackingNum EmpID OrderNo ShippedDate F EmpID EmpName OrderNo ShipToAddr TrackingNum EmpID TrackingNum OrderNo TrackingNum ShippedDate B Threerelations 1 Employee EmpID EmpName PK EmpIDFDs EmpID EmpName 2 Order OrderNo ShipToAddr PK OrderIDFDs OrderNo ShipToAddr 3 Shipment TrackingNum EmpID OrderNo ShippedDate PK TrackingNumFDs TrackingNum OrderNo TrackingNum ShippedDate C The above three relations areallin3NF because for each of them no exists none primary key attribute partially or transferable depends on the primary key ERERERER 图映射为关系图图映射为关系图 Here is theER schema of the Library database To simplify thedrawing weshow the attributes of each entity type separatelyinatextual form 4013 小游戏游戏交友 4013 小游戏游戏交友 Entities Weak Entity 1 DEPENDENT Name Date of Birth Kinship Relationships 4013 小游戏游戏交友 4013 小游戏游戏交友 1 HOLD M N PARTIAL PARTIAL Date 2 BORROW 1 5 PARTIAL PARTIAL BorrowDueDate 3 CHECKS 1 N PARTIAL PARTIAL 4 COPY 1 M PARTIAL TOTAL 5 WORKS 1 N TOTAL PARTIAL 6 MANAGES 1 1 PARTIAL PARTIAL 7 DEPENDS 1 N PARTIAL TOTAL 8 SUPERVISES 1 N PARTIAL PARTIAL SomeAssumptions Clarifications One author writes oneor moretitles Several co authors write one or moretitles A book is a copy of atitle A title can have oneor morecopies of the book A book has a unique ID not a copy ID If acopy ID is used then book is a weak entity type A particular member places ahold on a particular title A particular member borrowsa particular book up to amaximum of five books Notallmembers necessarily borrow books Notallbooks arenecessarily borrowed Notalltitles need necessarily be of books However allbooks must have a title and only one title 九步算法 In step1 we mapalltheentities types into tables and define their primary keys PKs and alternate keys AKs Note that AuthorinTITLE is a multi valued attribute and as such will be handledinStep 7 All thecomposite attributes e g DriverLicinMEMBER are broken down to their componentsthat areincluded as separate columns S1TITLE Name ISBN CallNumber Year Publisher S1PK CallNumber S1AK ISBN S1MEMBER MemNo DriverLicState DriverLicNo Fname MI Lname Address PhoneNumber S1PK MemNo S1AK DriverLicState DriverLicNo S1BOOK Book Id Edition S1PK Book Id S1LIBRARIAN SSN Name Address Salary Gender Birthday S1PK SSN S1SECTION SectNo Name S1PK SectNo In step 2 wedo not take any action sincethereare no weak entities satisfying the 1 1 relationship constraint DEPENDENT is aweak entity with 1 N relationship constraint namely DEPENDS and will behandledinthenext step Ifwehad to take any action this would have been along thelines of step 4 4013 小游戏游戏交友 4013 小游戏游戏交友 In step 3 wecreate a table for each remaining weak entity that was not handledinstep 2 In our example we create only one namely DEPENDENT S3DEPENDENT LIBSSN Name Birthday Kinship S3PK LIBSSN Name S3FK LIBSSN LIBRARIAN SSN Theprimary key is formed by LIBSSN the primary key of theowner entity LIBRARIAN and Name the partial key of DEPENDENT LIBSSN is a foreign key FK inthe DEPENDENT table In Step 4 weconsider the binary 1 1 relationships In our example MANAGES is such arelationship Since allsections have a head librarian wechoosethe SECTION table into which to capture collapse the relationship By choosing SECTION weavoid storing any NULL values which would have been the case had we decided to choose librarians This is becausenotalllibrarians head a section and forany librarian who is not a head librarian the corresponding column would beNULL We collapsethe MANAGES relationship by including the primary key of LIBRARIAN i e theSSN of thehead librarian as aforeign keyinSECTION i e HeadSSN S1SECTION SectNo Name HeadSSN S1PK SectNo S4FK HeadSSN LIBRARIAN SSN In step 5 weconsider 1 N relationships Here we havefive such relationshipsinour example namely BORROW CHECKS COPY WORKS and SUPERVISES In each case wechoose thetable that corresponds to the N side of the relationship and collapsethe relationshipintheselected table includingalltheattributes of the relationship as columnsinthe table Thereason for selecting the N side table is as follows Given that each rowinthe N side table can berelated to only one row of the 1 side table weneed to introduceonly onecolumninthe N side table to capturethe relationship This will be a foreign keyintheN side table corresponding to theprimary key of the 1 side table Ifwehad selected the 1 side table then wewould have to specify as many foreign keys as the number of related entities In general N is not bounded and hence wecannot predict how many foreign key columns to introduce Ifweattempt to make a prediction werun the risk of either overestimating or underestimating Ifweoverestimeate our table will contain a great number of NULL values which is awaste of space If weunderestimate our table will not beableto storea relationship and henceour database will fail to meet fully its intended usage In our example we chooseBOOK for BORROW BOOK forCHECKS and BOOK for COPY Hence we introduce threeforeign keysinBOOK namely BorrowerMemNo LibCheck and CallNumber respectively S1BOOK Book Id Edition BorrowerMemNo BorrowDueDate CallNumber LibCheck S1PK Book Id S5FK BorrowerMemNo MEMBER MemNo S5FK CallNumber TITLE CallNumber S5FK LibCheck LIBRARIAN SSN For the WORKS and SUPERVISES relationships wechoose LIBRARIAN and introducethe foreign keys Section and SuperSSN 4013 小游戏游戏交友 4013 小游戏游戏交友 S1LIBRARIAN SSN Name Address Salary Gender Birthday SuperSSN Section S1PK SSN S5FK SUPERSSN LIBRARIAN SSN S5FK Section SECTION SectNo In step 6 weconsider theM Nrelationships In our example HOLD is such a relationship We create a new table to store this relationship and its attribute namely Date As above therelationship is expressed using foreign keys For each participating table inour example MEMBER and TITLE weinclude aforeign key referring to their primary keys namely MemNo and CallNumber respectively S6HOLD MemNo CallNumber Date S6PK MemNo CallNumber S6FK MemNo MEMBER MemNo S6FK CallNumber TITLE CallNumber Thereason for creating anew table is that wecannot collapsean N M relationshipinany of theparticipating tables without guessing therequired number of foreign keys as wediscussedinstep 5 above In the last step for ER schemas step 7 weconsiderallmulti value attributes In our example wehave one multi valued attribute namely AuthorsinTITLE We storesuch an attribute as aseparate table e g AUTHOR whoserows correspond to each of the values of the multi valued attribute In order to relate thesevalues to the originating table weinclude theprimary key of the originating table inour case TITLE as a foreign keyin AUTHOR S7AUTHOR CallNumber Fname MI Lname s7PK CallNumber Fname MI Lname s7FK CallNumber TITLE CallNumber Let us conclude by listing the entire relational schema produced above It is worth recalling here that PK AK and FK correspond to PRIMARY KEY UNIQUE