PostgreSQL

An Entity-Relationship Diagram (ERD) is a visual representation of data entities, their attributes, and the relationships between them in a database. In PostgreSQL, ERDs help design the structure of the database by outlining how tables (entities) relate to one another. ERDs are essential for structuring a banking system database by defining entities like customers, accounts, and transactions.

Key Components of an ERD:

1. Entities:

   Represent tables in the database. In the context of a banking system, common entities include:

   • Customers: Stores customer information.
   • Accounts: Stores account details for each customer.
   • Transactions: Stores details of financial transactions.
2. Attributes:

   Represent the columns within each entity (table). Attributes define the properties of an entity, such as:

   • Customers Table:
     • CustomerID (Primary Key)
     • FirstName
     • LastName
     • Email
     • PhoneNumber
   • Accounts Table:
     • AccountID (Primary Key)
     • AccountType
     • Balance
     • CustomerID (Foreign Key referencing Customers)
   • Transactions Table:
     • TransactionID (Primary Key)
     • TransactionDate
     • Amount
     • AccountID (Foreign Key referencing Accounts)
3. Primary Key (PK):

   A unique identifier for each record in an entity. This key ensures each row is unique. For example:

   • CustomerID in the Customers table.
   • AccountID in the Accounts table.
   • TransactionID in the Transactions table.
4. Foreign Key (FK):

   An attribute in one table that links to the primary key of another table. Foreign keys maintain referential integrity between tables. For example:

   • CustomerID in the Accounts table refers to the CustomerID in the Customers table.
   • AccountID in the Transactions table refers to the AccountID in the Accounts table.
5. Relationships:

   Define how entities interact with each other. Common types of relationships include:

   • One-to-Many:
     • One customer can have multiple accounts.
     • One account can have many transactions.
   • Many-to-One:
     • Many transactions can relate to one account.
6. Cardinality:

   Describes the numeric relationship between entities:

   • 1:N
     • One customer can own multiple accounts.
     • One account can have many transactions.
   • N:1 (Many-to-One):
     • Many transactions belong to one account.
7. Optionality (Participation):

   Indicates whether the participation of an entity in a relationship is optional or mandatory:

   • Mandatory: Every account must belong to a customer.
   • Optional: A customer may not have any accounts, but still exists in the system.
8. Normalization:

   A process used to organize data, reduce redundancy, and improve data integrity. In the banking system:

   • Customer details are stored separately in the Customers table.
   • Accounts are stored in a separate Accounts table with a reference to the Customers table.
   • Transactions are stored in the Transactions table with a reference to the Accounts table.

PostgreSQL and ERD for Banking System:

In PostgreSQL, creating an ERD for a banking system ensures a clear understanding of how data related to customers, accounts, and transactions is structured and related. PostgreSQL supports relational integrity through constraints, making it ideal for implementing relationships modeled in an ERD. Tools like pgAdmin or DBeaver can be used to design, visualize, and validate ERDs for PostgreSQL databases.

By following these components and principles, you can design a scalable and efficient database for a banking system using PostgreSQL.

Components of an ERD

Entities

These are objects or concepts that can have data stored about them. An entity represents a table in a database. Entities are usually nouns, such as Customer, Order, or Product.

Attributes

These are the data we store about an entity. They represent the columns in a table. For example, an Employee entity might have attributes such as Employee_ID, Name, and Department.

Relationships

These illustrate how entities share information in the database and are the lines that connect entities. Relationships show how two entities share information and interact with each other. They can be one-to-one, one-to-many, or many-to-many.

Primary Key (PK)

This is a unique attribute (or combination of attributes) that identifies a specific instance of an entity. Each entity in the ERD must have a primary key.

Foreign Key (FK)

This is an attribute in one entity that links to the primary key of another entity, establishing a relationship between the two entities.

Types of ERDs

Conceptual ERD

This is the most abstract form of ERD. It focuses on the high-level design and concept without including details like primary keys or attributes. It's used in the initial planning phase.

Logical ERD

This type adds more detail to the conceptual ERD by including key attributes for each entity and relationships. It does not include detailed attributes or methods but focuses on the structure of data.

Physical ERD

This is the most detailed ERD and includes all entities, relationships, key attributes, and constraints. It is directly used to implement the database schema in a specific database management system (DBMS).

CONCEPTUAL DATA MODEL

Only entities and their corresponding relationships are depicted here.

LOGICAL DATA MODEL

Here, we display entities along with their relationships, attributes, primary keys, foreign keys, and whether attribtues are optional or mandatory. Note that data types are missing from this ERD, this is not specific to any database vendors like MySQL or Oracle.

PHYSICAL DATA MODEL

This pertains specifically to certain database vendors such as MySQL, Oracle, PostgreSQL, or MS SQL Server. Therefore, the data types presented here are tailored to a specific database vendor. In this instance, this physical ERD was designed for PostgreSQL, hence you will observe data types intended for PostgreSQL.