Banking Database Management Using SQL

This SQL project focuses on designing and managing a banking database to store customer details, account information and transaction records. It uses PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK and DEFAULT constraints to maintain data integrity, consistency and accuracy.

Dataset Overview

The Banking Database consists of three main tables connected through primary and foreign keys.

Step 1: Create the Customers Table

First, create the customers table using the CREATE TABLE statement. The PRIMARY KEY constraint uniquely identifies each customer, while the UNIQUE constraint prevents duplicate email addresses.

CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    customer_name VARCHAR(100) NOT NULL,
    email VARCHAR(150) UNIQUE,
    phone VARCHAR(15) UNIQUE
);

Output:

The customers table is created successfully with primary key, unique and not-null constraints.

Step 2: Create the Accounts Table

Next, create the accounts table. The FOREIGN KEY constraint connects each account to an existing customer, while CHECK and DEFAULT constraints help validate and initialize account information.

CREATE TABLE accounts (
    account_id INT PRIMARY KEY,
    customer_id INT,
    account_type VARCHAR(20)
        CHECK (account_type IN ('Savings', 'Current')),
    balance DECIMAL(12, 2) DEFAULT 0.00
        CHECK (balance >= 0),
    FOREIGN KEY (customer_id)
        REFERENCES customers(customer_id)
);

Output:

Step 3: Create the Transactions Table

Create a transactions table to record deposits and withdrawals. The CHECK constraint validates transaction types and amounts, while the foreign key associates each transaction with a bank account.

CREATE TABLE transactions (
    transaction_id INT PRIMARY KEY,
    account_id INT,
    transaction_type VARCHAR(20)
        CHECK (transaction_type IN ('Deposit', 'Withdrawal')),
    amount DECIMAL(12, 2)
        CHECK (amount > 0),
    transaction_date DATE DEFAULT CURRENT_DATE,
    FOREIGN KEY (account_id)
        REFERENCES accounts(account_id)
);

Output:

Step 4: Insert Customer Records

Insert sample customer details into the customers table.

INSERT INTO customers
    (customer_id, customer_name, email, phone)
VALUES
    (101, 'James Wilson', 'james@example.com', '5551234567'),
    (102, 'Emily Carter', 'emily@example.com', '5552345678'),
    (103, 'Michael Brown', 'michael@example.com', '5553456789');

Output:

This query inserts three customer records into the customers table.

Step 5: Insert Bank Account Records

Insert account details for existing customers.

INSERT INTO accounts
    (account_id, customer_id, account_type, balance)
VALUES
    (1001, 101, 'Savings', 15000.00),
    (1002, 102, 'Current', 25000.00),
    (1003, 103, 'Savings', 10000.00);

Output:

This query creates three bank accounts associated with existing customers.

Step 6: Insert Transaction Records

Insert deposit and withdrawal records into the transactions table.

INSERT INTO transactions
    (transaction_id, account_id, transaction_type, amount)
VALUES
    (5001, 1001, 'Deposit', 5000.00),
    (5002, 1002, 'Withdrawal', 2000.00),
    (5003, 1003, 'Deposit', 3000.00);

Output:

This query inserts three transactions. The transaction date is automatically set to the current date because the DEFAULT CURRENT_DATE constraint is defined.

Step 7: Prevent Duplicate Customer Emails

A new customer tries to register using an email address that already exists.

INSERT INTO customers
    (customer_id, customer_name, email)
VALUES
    (105, 'Michael Brown', 'james@example.com');

Output:

Error: UNIQUE constraint failed: customers.email

This query fails because the UNIQUE constraint prevents duplicate email addresses.

Step 8: Prevent Accounts for Invalid Customers

A bank employee tries to open an account for a customer who does not exist.

INSERT INTO accounts
    (account_id, customer_id, account_type, balance)
VALUES
    (1005, 999, 'Savings', 5000.00);

Output:

Error: FOREIGN KEY constraint failed

This query fails because customer ID 999 does not exist. The FOREIGN KEY constraint prevents invalid customer references.

Step 9: Validate Account Details

An employee enters a negative opening balance while creating a bank account.

INSERT INTO accounts
    (account_id, customer_id, account_type, balance)
VALUES
    (1006, 101, 'Savings', -500.00);

Output:

Error: CHECK constraint failed: balance >= 0

This query fails because the CHECK constraint does not allow negative balances.