ER DIAGRAM FOR A BANK DATABASE: QUESTIONS AND ANSWERS

Consider the ER diagram shown in the Figure above for part of a BANK database. Each bank can have multiple branches, and each branch can have multiple accounts and loans.

a. List the (nonweak) entity types in the ER diagram.

➲bank, account, loan, customer

b. Is there a weak entity type? If so, give its name, partial key, and identifying the relationship.

➲weak entity type: bank_branch
➲partial key: branch_no
➲identifying relationship: branches

c. What constraints do the partial key and the identifying relationship of the weak entity type specified in this diagram?

➲a bank_branch cannot exist without an associated bank

d. List the names of all relationship types, and specify the (min, max) constraint on each participation of an entity type in a relationship type. Justify your choices.

➲branches- bank:bank_branch, 1:N
➲accts- bank_branch:account, 1:N
➲loans- bank_branch:loan, 1:N
➲a_c- account: customer, N:M
➲l_c- loan:customer, N:M

e. List concisely the user requirements that led to this ER schema design.

➲a bank entity that can be related to many bank branches where each branch can have many accounts and loans to customers. each customer can have a number of loans and accounts. each loan and account must be associated with a customer

f. Suppose that every customer must have at least one account but is restricted to at most two loans at a time and that a bank branch cannot have more than 1000 loans. How does this show up on the (min, max) constraints?

➲a_c-customer to accounts: (1,N)
➲l_c-customer to loans: (0,2)
➲loans-bank_branch to loan: (0,1000)

Consider the ER diagram shown below for part of a BANK database. Each bank can have multiple branches, and each branch can have multiple accounts and loans. a. List the (nonweak) entity types in the ER diagram.

➲Customer, Account, Loan, Bank

b. Is there a weak entity type? If so, give its name, partial key, and identifying the relationship

➲Bank_Branch is a weak entity. Its partial key is Branch-no and its identifying relationship is BRANCHES with Bank.

c. What constraints do the partial key and the identifying relationship of the weak entity type specified in this diagram?

➲The constraint of the partial key Branch - no is that we need to combine Branch - no with Code, the key from its owner entity set Bank, to uniquely identify a Bank_Branch.

The constraints of the identifying relationship are:

1) The identifying relationship between the owner entity set, Bank, and the weak entity set, Bank_Branch must be one too many and Bank_Branch could only have one Bank as its owner.

2) The weak entity set, Bank_Branch, must have total participation in the identifying relationship set, BRANCHES.

d. Translate this ER model into a relational schema (not SQL notation)

➲Customer (Ssn: string, Phone:string, Name: string, Addr: string) 

➲Bank (Code: string, Name: string, Addr: string)
➲Bank_Branch (Branch - no: string, Code: string, Addr: string)

➲Account (Acct - no: string, Balance: real, Type: string, Ssn: string, Branch - no: string, Code: string)
➲Loan (Loan - no: string, Type: string, Amount: real, Ssn: string, Branch - no: string, Code: string

$ads={2}