1. Identify entities

   • Determine the real-world objects or concepts that will be part of the system
   • Entities should be able to be identified uniquely

2. Define attributes

   • For each entity, define the properties or characteristics that describe it
   • Attributes should be specific and relevant to the entity

3. Determine relationships

   • Identify how the entities are connected and interact with each other
   • Determine the type of relationship, such as one-to-one, one-to-many, or many-to-many

4. Draw the ER diagram

   • Use boxes or rectangles to represent entities and their attributes
   • Use lines or diamonds to represent the relationships between entities

5. Add points

   • Include additional information about the entities or relationships as notes or points on the diagram
   • Provide any constraints or rules that apply to the entities or relationships

6. Validate and refine the ER diagram

   • Review the diagram to ensure it accurately represents the system being modeled
   • Make any necessary changes or adjustments to improve the diagram's accuracy and usability.

1. Banking system → Branches

Bank may have multiple branches

2. Bank → customers.

Bank has multiple customers

3. Customers → accounts & take loans.

Customer may have multiple accounts & take multiple loans

4. Customer associated with some banker.

Customer may be associated with multiple bankers means when you deal with loan you need manager or branch manager or some banker.

5. Bank has employees.

Bank has multiple employees

6. Account → Savings, Current, FD, RD, Loan Account

Account may be of multiple types like savings, current, FD, RD, Loan Account

7. Loan originated by branch. loan = multiple customers → Payment schedulePayment schedule

Loan may be originated by multiple branches and multiple customers also have multiple payment schedules.

Payment schedule may be of multiple types like monthly, quarterly, half yearly, yearly.

We think this DB as bottom up approach. So we will start from weakest entity set and then we will move to strong entity set.

Step 1

Entity Sets

2. Customer

3. Employee

4. Savings A/C (Account Entity Set) → Generalization

5. Current A/C (Account Entity Set) → Generalization

6. Loan

7. Payment (loan) → Weakest entity set

Step 2

Attributes

1.Branch → Branch ID, Branch Name (primary key), Branch Address, Branch Manager , Liability, Assets, City, State, Country

2. Customer → Cust-id (primary key), Name , Address(Composite Attribute) , Contact no(Multi Valued) , DOB , Age(Derived Attribute)

3. Employee → Emp-id (primary key), Name , Contact no(Single Valued) , Department Name(Multi Valued) , Years of Service (Derived Valued) , Start Date(Single Valued)

4. Savings A/C → Acc-Number(primary key) , balance , interest rate , daily interest limit

5. Current A/C → Acc-Number (primary key), balance , Per transaction charge , overdraft amount

6. Generalized Entity "Account" → Acc-No , Balance

We have generalized Account entity set because both Savings A/C and Current A/C have same attributes.

7. Loan → loan-number(primary key) , amount

8. Weak Entity "Payment" → payment-number , amount , date

Step 3

Relationships & Constraints

1. Customer & Loan

• Customer <borrow> Loan → many to many (M : N)

Customer can borrow multiple loans and loan can be borrowed by multiple customers.

• loan and borrow is total participation constraint because if loan is exist then customer must be exist and vice versa.

2. Loan & Branch

• Loan <originated by> Branch → many to one (n : 1)

Loan can be originated by only one branch and one branch can originate multiple loans.

• loan and originated by is total participation constraint because if loan is exist then branch must be exist and vice versa.

3. Loan & Payment (weak relationship)

If we have a weak entity set in a database, and it participates in a relationship with another weak entity set, then this relationship is considered a weak relationship. To properly model this relationship, we should apply a total participation constraint to the weak entity set that participates in the relationship.

• Loan <has> Payment → one to many (1 : n)

Loan can have multiple payments and payment can be associated with only one loan.

• loan and has is total participation constraint because if loan is exist then payment must be exist and vice versa.

4. Customer & Account

• Customer <depsit> Account → many to many (M : N)

Customer can deposit multiple accounts and account can be deposited by multiple customers.

• account and deposit is total participation constraint because if account is exist then customer must be exist and vice versa.

5. Customer & Banker

• Customer <associated with> Banker → many to one (N : 1)

Customer can be associated with multiple bankers and banker can be associated with multiple customers.

• customer and associated with is total participation constraint because if customer is exist then banker must be exist and vice versa.

6. Employee & Employee (Unary Relationship)

• Employee <managed by> Employee → many to one (N : 1)

Employee can be managed by multiple employees and employee can be managed by multiple employees.

• employee and managed by is total participation constraint because if employee is exist then employee must be exist and vice versa.

Online Ordering System ER Diagram

Voting System ER Diagram