Question

home / study / engineering / computer science / computer science questions and answers / 2. design an er-diagram for a bank that implements the following requirements. the database ...

Question: 2. Design an ER-diagram for a bank that implements the following requirements. The database you d...

2. Design an ER-diagram for a bank that implements the following requirements. The database you design should store information about customers, accounts, branches and employees

• Customer: Customers are identified by their SSN. For each customer we store a name, multiple phone numbers (one or more), and an occupation.

• Account: Accounts are identified by an account number and the branch they belong to. For each account we store a balance and the type of account (e.g., savings).

– An account belongs to one or more customers. A customer can have any number of accounts.

– An account belongs to exactly one branch. Obviously, branches can have multiple accounts (branches are not required to have accounts).

• Branch: A branch is identified by a unique branch code. For each branch we want to store a location and number of employees.

• Employee: Employees are identified by their SSN. For each employee we store a name and salary.

– An employee works for exactly one branch. Branches have one or more employees.

– An employee is the contact for zero or more customers. Every customer has at most one employee as a contact.                                                    [20 marks]

Answer 1

Description

As per given requirements following steps needs to be taken

Customer: Customers are identified by their SSN. For each customer we store a name, multiple phone numbers (one or more), and an occupation.

Make an entity set (rectanle) named Employee with attributes CSSN , NAME, PHONE and OCCUPATION shown in elipses and underline CSSN to show it as primary key and attribute phone number in double ellipse to show it is multivalued attribute

• Account: Accounts are identified by an account number and the branch they belong to. For each account we store a balance and the type of account (e.g., savings).

Here the account is identified by Account Number and branch_code together this means account number is not unique and hence Account table will be weak entity set shown by double rectangle and acct_no. will be underlined by dashed line to show that its is dicreminator. hence account will have total participation in relation acct_branch

– An account belongs to one or more customers. A customer can have any number of accounts.

Depositor relatioin will be many to many relation

– An account belongs to exactly one branch. Obviously, branches can have multiple accounts (branches are not required to have accounts).

Relation acct_branch will be one to may relation between branch and account

• Branch: A branch is identified by a unique branch code. For each branch we want to store a location and number of employees.

Br_Code will be primary key

• Employee: Employees are identified by their SSN. For each employee we store a name and salary.

ESSN will be primary key of Employee table and other attributes will be as listed

– An employee works for exactly one branch. Branches have one or more employees.

Branch to employee relation will be one to many relation

– An employee is the contact for zero or more customers. Every customer has at most one employee as a contact.                  

Relation between employee and customer is shown as CONTACT