SSD7全部答案Exam 2Exam 2 Practical

Exam 2 Practical Exam 2 Practical 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. 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 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, 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). 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. 3. 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. Exam 2 Practical Answer 1. E-R schema: Entities: 1. Bank (BankID, BankName, BankPhoneNum, BankAddress) PK: BankID AK: BankName 2. Branch (BranchID, BankID, BranchName, BranchPhoneNum, BranchAddress) PK: BranchID AK: BranchName FK: BankID references Bank (BankID) 3. Customer(CustomerID,FName,MName,LName,CustomerPhoneNum,CustomerAddress) PK: CustomerID AK: CustomerPhoneNum 4. Account (AccountNo, CustomerID, Balance) PK: AccountNo FK: CustomerID references Customer (CustomerID) 5. Transaction (TransactionID, AccountNo, OperationType, DateTime) PK: TransactionID FK: AccountNo references Account (AccountNo) Relationships: 1. has: , 1:N, partial/total; 2. belong: , N:1, total/partial; 3. own: , 1:N, partial/total; 4. operate: , 1:N, partial/total. Assumptions:(答案不限) 1. A new bank may have no branch, and a normal bank may have one or more branches. 2. An account must belong to one and only one branch. 3. A customer may own one or more accounts. 4. A transaction must be operated by one and only one account. 2. 1. PK: (PartID, SupplierID) 2. FDs: (PartID, SupplierID)-> PartName, SupplierName PartID->PartName SupplierID->SupplierName 3. The relation is in 1NF. Because the intersection of each row and column of the relation contains one and only one value, so it is in 1NF. But the non-primary-key attributes, PartName and SupplierName, are both partially dependent on the primary key (PartID, SupplierID), so it is not in 2NF. 4. Normalization: 1. The relation can be decomposed into two relations: PART (PartID, PartName) PK: PartID FDs: PartID->PartName CAN_SUPLY (PartID, SupplierID, SuplierName) PK: (PartID, SupplierID) FDs: SupplierID->SuplierName The relation PART is now in 3NF because the only non-primary-key attribute PartID is fully (not partially) and directly (not transitively) dependent on the primary key PartId. The relation CAN_SUPPLY is still in 1NF because the only non-primary-key attribute PartID is partially dependent on the primary key (PartId,SupplierID). 2. The relation of CAN_SUPLY can also be decomposed into two relations: SUPPLIER (SupplierID, SupplierName) PK: SupplierID FDs: SupplierID->SuplierName CAN_SUPLY (PartID, SupplierID) PK: (PartID, SupplierID) FDs: null Both relations are in 3NF because for each one no non-primary-key attribute is partially or transitively dependent on its primary key. 3. In the end there are three 3NF relations: PART (PartID, PartName) PK: PartID FDs: PartID->PartName SUPPLIER (SupplierID, SupplierName) PK: SupplierID FDs: SupplierID->SuplierName CAN_SUPLY (PartID, SupplierID) PK: (PartID, SupplierID) FDs: null 3. Step 1: For each strong entity, create a new table. Identify the primary key and the alternate key. Strong Entities: COACH (Name, Age) PK: Name PLAYER (Name, Age) PK: Name TEAM (Name) PK: Name GAME (Number, Score, Time, Date) PK: Number STADIUM (Name, Size, Location) PK: Name Step 2: There are no weak entities, so we don’t take any action. Step 3: There are no weak entities, so we don’t take any action. Step 4: For the binary 1:1 relationship between COACH and TEAM (total participation), include as a foreign key in TEAM, the primary key of COACH. TEAM (Name, CoachName) PK: Name FK: CoachName references COACH (Name) Step 5: For the binary 1:N relationship between PLAYER (at the N-side) and TEAM, include as a foreign key in PLAYER, the primary key of TEAM. PLAYER (Name, Age, TeamName) PK: Name FK: TeamName references TEAM (Name) Step 6: For each N-ary relationship, create a new table, and include as a foreign key, the primary key of the participating entity type. PRACTICE (StadiumName, TeamName, Date) PK: (StadiumName, TeamName) FK: StadiumName references STADIUM (Name) FK: TeamName references TEAM (Name) PLAYSWITH (GameNumber, HostName, VisitorName) PK: GameNumber FK: HostName references TEAM (Name) FK: VisitorName references TEAM (Name) Step 7: For the multi-valued attribute Colors in TEAM, create a new table TEAMCOLOR, and include as a foreign key, the primary key of the entity type TEAM. TEAMCOLOR (TeamName, Color) PK: (TeamName, Color) FK: TeamName references TEAM (Name) Result (8 tables): COACH (Name, Age) PK: Name PLAYER (Name, Age, TeamName) PK: Name FK: TeamName references TEAM (Name) TEAM (Name, CoachName) PK: Name FK: CoachName references COACH (Name) GAME (Number, Score, Time, Date) PK: Number STADIUM (Name, Size, Location) PK: Name PRACTICE (StadiumName, TeamName, Date) PK: (StadiumName, TeamName) FK: StadiumName references STADIUM (Name) FK: TeamName references TEAM (Name) PLAYSWITH (GameNumber, HostName, VisitorName) PK: GameNumber FK: HostName references TEAM (Name) FK: VisitorName references TEAM (Name) TEAMCOLOR (TeamName, Color) PK: (TeamName, Color) FK: TeamName references TEAM (Name) 4