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.
➲Bank_Branch (Branch - no: string, Code: string, Addr: string)
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.
➲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?
➲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
➲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.

➲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.
➲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
➲Loan (Loan - no: string, Type: string, Amount: real, Ssn: string, Branch - no: string, Code: string