《数据库er图练习及答案》由会员分享，可在线阅读，更多相关《数据库er图练习及答案（14页珍藏版）》请在金锄头文库上搜索。
1、4013 小游戏游戏交友4013 小游戏游戏交友DBDBDBDB ModelingModelingModelingModeling ExamExamExamExam PracticalPracticalPracticalPracticalAnswer the following questions.1.Create an E-R schema for a database system used to manage account information at a communitybank. The bank has several branches with unique names. A
2、 customer may have one or more accounts in oneor more branches. An account must belong to one and only one branch. Each account is operated on bytransactions, which may be deposits to or withdrawals from some account. The database keeps track of all thetransactions on each account, in addition to th
3、e balance of individual accounts and the assets of individualbranches. For each entity, specifyallits attributes, primary key, and alternate key(s). In your ER schema, besure to capture the cardinality constraints and participation constraints of all relationships.Make reasonable assumptions to comp
4、lete the specification. Explicitly state all your assumptions. EVERYconstruct in your ER schema should be substantiated by either the specification above or your explicitassumptions.2.The following table stores information about which suppliers can supply which parts. The table capturesthe fact that
5、a part whose name is PartName and whose ID is PartID can besupplied by suppliers whose namesare in SupplierName and whose IDs are in SupplierID. Note that a part can be supplied by many suppliers, anda supplier can supply many parts.CAN_SUPPLYCAN_SUPPLY PartIDPartIDPartNamePartNameSupplierIDSupplier
6、IDSupplierNameSupplierName1234Nut223ProMetal1234Nut224Biscayne 2134Bolt223ProMetal3.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 thehi
7、gher normal forms possible up to 3NF. Thatis,ifthe relation were unnormalized, bring it to first normal form,then bring the first normal form youve just created to second normal form, and then bring the second normalform to third normal form.For each transformation to the next higher normal form X,E
8、xplain the steps you took to bring it to the normal form X.Provide the normal form Xs 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 apartial dependency, fully defend your conclusion by explaining how a column is partia
9、lly dependent on some4013 小游戏游戏交友4013 小游戏游戏交友other column(s).That is,ifthe relation were in an unnormalized form, you would explain the transformation you performed tobring it to first, second, and third normal forms. You would also provide the table structure, the primary key andthe FDs for the fir
10、st, second, and third normal forms. You would also provide explanation for why you believeit is in first, second, and third normal forms.4.Convertthe following E-R schema into a relational schema using the mapping algorithm specified in thiscourse. Specify key and referential integrity constraints,
11、using directed arcs. Make sure you also identifyalternate keys. Label each step of the mapping algorithm.Answer: 1. 1. 1. 1.4013 小游戏游戏交友4013 小游戏游戏交友BankAccountCustomerTransactionBranchBankNameBankPhoneBranchAddrBranchNameHasN1BranchPhoneAssetsCustNamePhoneNumCustIDAccountNoBalanceOperationTypeTIDTDa
12、teTimeAofBranchTofAccounOpen1NM 11NEntity: 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: ( BranchN
13、ame) 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
14、. 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 cus
15、tomers. 8.An account belongs to just onebranch;2. 2. 2. 2.1.pk:(PartID,SupplierID)2.FDs: FD1: PartID-PartName FD2: SupplierID-SupplierName3. Therelation isinthe first normal form(1NF). Each attribute of therelation allows asingle atomic value, soitisin1NF. But somenone-primary-key attributes, such a
16、s 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
