2022年数据库ER练习归纳 .pdf

DB Modeling Exam Practical 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, specify all its 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 that a part whose name is PartName and whose ID is PartID can be supplied 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_SUPPLY PartID PartName SupplierID SupplierName 1234 Nut 223 ProMetal 1234 Nut 224 Biscayne 2134 Bolt 223 ProMetal 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. That is, if the 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 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 Xs table structure, primary key(s), and the FDs. Explain why you think it is in the normal form X. For example, if you think there is a partial dependency, fully defend your conclusion by explaining how a column is partially dependent on some other column(s). 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 1 页，共 14 页 - - - - - - - - - That is, if the 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.Convert the 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. 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 2 页，共 14 页 - - - - - - - - - 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: , M:N, PARTIAL/ TOTAL; 3.AofBranch: , 1:N, PARTIAL/ TOTAL; 4.TofAccount: , 1:N, PARTIAL/ TOTAL; Assumptions: 1.A new bank can establish no branch. 2.One normal bank establishes one or more braches. 3.A bank has one or more telephones for customers. 4.A customer can open one or more Account. 5.An account must belong to one and only one branch. Bank Account Customer Transaction Branch BankName BankPhone BranchAddr BranchName Has N 1 BranchPhoneAssets CustName PhoneNum CustID AccountNo Balance OperationType TID TDateTime AofBranch TofAccounOpen 1 N M 1 1 N 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 3 页，共 14 页 - - - - - - - - - 6.One branch opens one or more accounts. 7.A branch has one or more telephones for customers. 8.An accounts belongs to just one branch; 2. 1.pk:(PartID, SupplierID) 2.FDs: FD1: PartID-PartName FD2: SupplierID-SupplierName 3. The relation is in the first normal form(1NF). Each attribute of the relation allows a single atomic value, so it is in 1NF. But some none-primary-key attributes, such as PartName and SupplierName, partially dependant on the primary key (as FD1 and FD2 show), so it is not in 2NF. 4. Normalization: 1) FD1: PartID-PartName The relation can be decomposited into two relations: PART(PartID, PartName),FDs=PartID-PartName, PK:(PartID); CAN_SUPLY(PartID, SupplierID, SuplierName), FDs=SupplierID-SuplierName), PK:(PartID,SupplierID). The relation PART is now in the third normal form because the only none-primary-key attribute PartID, fully (not partially) and directly (not transively) dependants on the primary key PartId. The relation CAN_SUPPLY is still in the first normal because the only none-primary-key attribute PartID, partially dependants on the primary key (PartId,SupplierID). 2)CAN_SUPLY(PartID, SupplierID, SuplierName), FDs=SupplierID-SuplierName): For SupplierID-SuplierName, the relation can be decomposited into two relations: SUPPLIER(SupplierID,SupplierName),FDs=SupplierID-SuplierName, PK:(SupplierID); CAN_SUPLY(PartID, SupplierID), FDs=, PK:(PartID,SupplierID). Both relations are in the third normal form, because for each one, no none-primary-key attribute patially or transively dependants on its primary key. 3) Three 3NF 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. 九步算法：名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 4 页，共 14 页 - - - - - - - - - 三种异常：修改异常、插入异常、删除异常。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:CoachName references Coach(Name) S5: T3: Player(Name,Age,TeamName) PK:(Name) FK:TeamName references Teach(Name) S6: T6: Practice(TeamName,StadiumName,Date) PK:(TeamName,StadiumName) 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 5 页，共 14 页 - - - - - - - - - FK:TeamName references Teach(Name) FK:StadiumName references Stadium(StadiumName) T7: PlaysWith(HostName,VisitorName,GameNumber) PK:(GameNumber) FK:HostName references Team(name) FK:VisitorName references Team(name) S7: T8: TeamColor(TeamName,color) PK:(TeamName,color) FK:TeamName references Team(name) The last seven tables: T1: Coach(Name,Age) PK:(Name) T2: Team(Name,CoachName) PK:(Name) FK:CoachName references Coach(Name) T3: Player(Name,Age,TeamName) PK:(Name) FK:TeamName references Teach(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 references Teach(Name) FK:StadiumName references Stadium(StadiumName) T7: PlaysWith(HostName,VisitorName,GameNumber) PK:(GameNumber) FK:HostName references Team(name) FK:VisitorName references Team(name) t8: TeamColor(TeamName,color) PK:(TeamName,color) FK:TeamName references Team(name) (T4 and T7 have the same primary key, so, they can be joined to a new table.) Normalization Provide your solution to the following in a file named normalization.txt . Note: The discussion in this exercise is independent of (i.e., completed unrelated to) the E-Commerce project described in the Appendix.名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 6 页，共 14 页 - - - - - - - - - The following table captures the following fact about an E-Commerce bookstore: the employee whose name is EmpName and whose ID is EmpID has shipped the order (whose Order Number is OrderNo) to the address ShipToAddr on the date ShippedDate. The tracking number for the shipment is TrackingNum. The TrackingNum isprovided by the courier company that picks up the shipment. The bookstore uses only one courier company. Note that a single order could be split up into multiple shipments based on the availability of the ordered items. Only one employee handles a shipment. However, multiple employees could handle an order if the order is shipped in multiple shipments. SHIPMENT EmpID EmpName OrderNo ShipToAddr ShippedDate TrackingNum 1234 Joe 223 4615 Forbes Ave, Pittsburgh, PA 15147 12/21/99 12435678 2134 Jones 224 4615 Forbes Ave, Pittsburgh, PA 15147 12/25/99 21345678 1.List the primary key. 2.List all the FDs. 3.List all the update anomalies and provide an example of each. 4.What normal form is the relation in? Explain. 5.Apply normalization to it incrementally, bringing the relation to 3NF. That is, if the relation is 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 normal form to third normal form. For each transformation to the next higher normal form X, oExplain the steps you took to bring it to the normal form X. oProvide the normal form Xs table structure, primary key(s), and the FDs. oExplain why you think it is in the normal form X. That is, if the 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. Answer: 1 PK TrackingNum 2 FD1: EmpID - EmpName FD2: OrderNo - ShipToAddr FD3: TrackingNum - EmpID,OrderNo,ShippedDate3 A. A update anomaly. It alway takes inconsistencies. For example, an employee have shipped more than one shipment, his employee ID and name repeated more than noce. When we must change the name of this employee with particular 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 7 页，共 14 页 - - - - - - - - - EmpID, we have to change many rows. If we just changes one row, these rows with the same EmpId, but have different names, so which leads to inconsistencies. B. A insertion anomaly. It prevents us from keeping some necessary information. For example, if a new employee have never shipped any goods, his name cannot be stored. C. A deletion 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 The normal relation is in 2NF. The 2NF is every attribute of the relation allows a single atomic value, firstly, it is in 1NF. Further more, the primary key is a single attribute, all none-primary-key attributes must completely depends on the primary key, secondly, it is in 2NF. lastly, existing some noe-primary-key attributes transferable depends on the primay key (For example EmpID - EmpName). so it is not in 3NF. 5 A. F=EmpID - EmpName, OrderNo - ShipToAddr, TrackingNum - EmpID,OrderNo,ShippedDate F=EmpID-EmpName, OrderNo-ShipToAddr, TrackingNum-EmpID, TrackingNum-OrderNo, TrackingNum-ShippedDate B. Three relations 1. Employee(EmpID,EmpName) PK EmpID FDs=EmpID-EmpName 2. Order(OrderNo,ShipToAddr) PK OrderID FDs=OrderNo-ShipToAddr 3. Shipment(TrackingNum,EmpID,OrderNo,ShippedDate) PK TrackingNum FDs=TrackingNum-OrderNo, TrackingNum-ShippedDate C. The above three relations are all in 3NF, because for each of them, no exists none-primary-key attribute partially or transferable depends on the primary key. ER 图映射为关系图Here is the ER schema of the Library database. To simplify the drawing, we show the attributes of each entity type separately in a textual form. 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 8 页，共 14 页 - - - - - - - - - Entities: Weak Entity 1. DEPENDENT: Name, Date of Birth, Kinship - Relationships: 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 9 页，共 14 页 - - - - - - - - - 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; Some Assumptions/Clarifications: One author writes one or more titles. Several co-authors write one or more titles. A book is a copy of a title. A title can have one or more copies of the book. A book has a unique ID (not a copy ID). If a copy ID is used then book is a weak entity type. A particular member places a hold on a particular title. A particular member borrows a particular book up to a maximum of five books. Not all members necessarily borrow books. Not all books are necessarily borrowed. Not all titles need necessarily be of books. However, all books must have a title and only one title. 九步算法：In step1, we map all the entities types into tables and define their primary keys PKs and alternate keys AKs. Note that Author in TITLE is a multi-valued attribute and as such will be handled in Step 7. All the composite attributes, e.g., DriverLic in MEMBER, are broken down to their components that are included as separate columns. S1 TITLE(Name, ISBN, CallNumber, Year, Publisher); S1 PK(CallNumber) S1 AK(ISBN) S1 MEMBER(MemNo, DriverLicState, DriverLicNo, Fname, MI, Lname, Address, PhoneNumber); S1 PK(MemNo) S1 AK(DriverLicState,DriverLicNo) S1 BOOK(Book_Id, Edition); S1 PK(Book_Id) S1 LIBRARIAN(SSN, Name, Address, Salary, Gender, Birthday); S1 PK(SSN) S1 SECTION(SectNo, Name); S1 PK(SectNo) In step 2, we do not take any action, since there are no weak entities satisfying the 1:1 relationship constraint. DEPENDENT is a weak entity with 1:N relationship constraint, namely, DEPENDS, and will be handled in the next step. If we had to take any action, this would have been along the lines of step 4. 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 10 页，共 14 页 - - - - - - - - -