Bank_Managment_System_Database.sql

-- CREATE DATABASE
CREATE DATABASE BMS_DB1;
USE BMS_DB1;

-- CUSTOMER PERSONAL INFO TABLE---
CREATE TABLE CUSTOMER_PERSONAL_INFO (
    CUSTOMER_ID VARCHAR(20),
    CUSTOMER_NAME VARCHAR(20),
    DATE_OF_BIRTH DATE,
    GUARDIAN_NAME VARCHAR(20),
    ADDRESS VARCHAR(50),
    CONTACT_NO BIGINT(10),
    MAIL_ID VARCHAR(30),
    GENDER CHAR(1),
    MARITAL_STATUS VARCHAR(10),
    IDENTIFICATION_DOC_TYPE VARCHAR(20),
    ID_DOC_NO VARCHAR(10),
    CITIZENSHIP VARCHAR(10),
    CONSTRAINT CUST_PERS_INFO_PK PRIMARY KEY(CUSTOMER_ID)
);

-- CUSTOMER REFERENCE PERSONAL INFO TABLE----
CREATE TABLE CUSTOMER_REFERENCE_PERSONAL_INFO (
    CUSTOMER_ID VARCHAR(20),
    REFERENCE_ACC_NAME VARCHAR(50),
    REFERENCE_ACC_NO BIGINT,
    REFERENCE_ACC_ADDRESS VARCHAR(100),
    RELATION VARCHAR(20),
    CONSTRAINT CUST_REF_INFO_PK PRIMARY KEY(CUSTOMER_ID),
    CONSTRAINT CUST_REF_INFO_FK FOREIGN KEY(CUSTOMER_ID) REFERENCES CUSTOMER_PERSONAL_INFO(CUSTOMER_ID)
);

-- BANK INFO TABLE----
CREATE TABLE BANK_INFO (
    IFSC_CODE VARCHAR(15),
    BANK_NAME VARCHAR(15),
    BRANCH_NAME VARCHAR(50),
    CONSTRAINT BANK_INFO_PK PRIMARY KEY(IFSC_CODE)
);

-- ACCOUNT INFO TABLE--
CREATE TABLE ACCOUNT_INFO (
    ACCOUNT_NO BIGINT(20),
    CUSTOMER_ID VARCHAR(15),
    ACCOUNT_TYPE VARCHAR(15),
    REGISTRATION_DATE DATE,
    ACTIVATION_DATE DATE,
    IFSC_CODE VARCHAR(15),
    INTEREST DECIMAL(7,2),
    INITIAL_DEPOSIT BIGINT(20),
    CONSTRAINT ACCOUNT_INFO_PK PRIMARY KEY(ACCOUNT_NO),
    CONSTRAINT ACCOUNT_INFO_PERS_FK FOREIGN KEY(CUSTOMER_ID) REFERENCES CUSTOMER_PERSONAL_INFO(CUSTOMER_ID),
    CONSTRAINT ACCOUNT_INFO_BANK_FK FOREIGN KEY(IFSC_CODE) REFERENCES BANK_INFO(IFSC_CODE)
);

-- SHOW TABLES TO VERIFY CREATION
SHOW TABLES;

INSERT INTO CUSTOMER_PERSONAL_INFO 
(CUSTOMER_ID, CUSTOMER_NAME, DATE_OF_BIRTH, GUARDIAN_NAME, ADDRESS, CONTACT_NO, MAIL_ID, GENDER, MARITAL_STATUS, IDENTIFICATION_DOC_TYPE, ID_DOC_NO, CITIZENSHIP) 
VALUES 
('CUST001', 'Amit Sharma', '1990-05-12', 'Rajesh Sharma', 'Delhi, India', 9876543210, 'amit@gmail.com', 'M', 'Single', 'Aadhar', 'A123456789', 'Indian'),
('CUST002', 'Ravi Verma', '1988-07-24', 'Mahesh Verma', 'Mumbai, India', 9865234789, 'ravi@gmail.com', 'M', 'Married', 'PAN', 'P987654321', 'Indian'),
('CUST003', 'Neha Singh', '1992-11-10', 'Sunil Singh', 'Kolkata, India', 9875612398, 'neha@gmail.com', 'F', 'Single', 'Aadhar', 'A987654321', 'Indian'),
('CUST004', 'Priya Das', '1995-02-05', 'Suraj Das', 'Bangalore, India', 9856741235, 'priya@gmail.com', 'F', 'Single', 'Voter ID', 'V12345678', 'Indian'),
('CUST005', 'Rahul Jain', '1991-06-18', 'Pankaj Jain', 'Chennai, India', 9845123678, 'rahul@gmail.com', 'M', 'Married', 'Aadhar', 'A111222333', 'Indian'),
('CUST006', 'Anjali Kapoor', '1993-09-30', 'Vinod Kapoor', 'Hyderabad, India', 9856741234, 'anjali@gmail.com', 'F', 'Single', 'Aadhar', 'A222333444', 'Indian'),
('CUST007', 'Suman Joshi', '1997-12-25', 'Raj Joshi', 'Pune, India', 9812345678, 'suman@gmail.com', 'F', 'Single', 'Passport', 'PQR123456', 'Indian'),
('CUST008', 'Kunal Mehra', '1985-03-14', 'Naresh Mehra', 'Jaipur, India', 9823456789, 'kunal@gmail.com', 'M', 'Married', 'PAN', 'P567890123', 'Indian'),
('CUST009', 'Vikram Bhatia', '1989-08-22', 'Anil Bhatia', 'Lucknow, India', 9834567890, 'vikram@gmail.com', 'M', 'Single', 'Aadhar', 'A444555666', 'Indian'),
('CUST010', 'Simran Kaur', '1994-04-09', 'Harpreet Kaur', 'Chandigarh, India', 9845678901, 'simran@gmail.com', 'F', 'Married', 'Aadhar', 'A777888999', 'Indian');

select * FROM CUSTOMER_PERSONAL_INFO

INSERT INTO CUSTOMER_REFERENCE_PERSONAL_INFO 
(CUSTOMER_ID, REFERENCE_ACC_NAME, REFERENCE_ACC_NO, REFERENCE_ACC_ADDRESS, RELATION) 
VALUES 
('CUST001', 'Rajesh Sharma', 123456789012, 'Delhi, India', 'Father'),
('CUST002', 'Mahesh Verma', 234567890123, 'Mumbai, India', 'Father'),
('CUST003', 'Sunil Singh', 345678901234, 'Kolkata, India', 'Father'),
('CUST004', 'Suraj Das', 456789012345, 'Bangalore, India', 'Father'),
('CUST005', 'Pankaj Jain', 567890123456, 'Chennai, India', 'Father'),
('CUST006', 'Vinod Kapoor', 678901234567, 'Hyderabad, India', 'Father'),
('CUST007', 'Raj Joshi', 789012345678, 'Pune, India', 'Father'),
('CUST008', 'Naresh Mehra', 890123456789, 'Jaipur, India', 'Father'),
('CUST009', 'Anil Bhatia', 901234567890, 'Lucknow, India', 'Father'),
('CUST010', 'Harpreet Kaur', 112233445566, 'Chandigarh, India', 'Mother');



INSERT INTO BANK_INFO (IFSC_CODE, BANK_NAME, BRANCH_NAME) 
VALUES 
('HDFC0001234', 'HDFC Bank', 'Connaught Place, Delhi'),
('ICIC0005678', 'ICICI Bank', 'Andheri East, Mumbai'),
('SBI0009012', 'SBI', 'Park Street, Kolkata'),
('AXIS0012345', 'Axis Bank', 'MG Road, Bangalore'),
('PNB0098765', 'PNB', 'T. Nagar, Chennai'),
('BOI0034567', 'Bank of India', 'Banjara Hills, Hyderabad'),
('UBI0023456', 'Union Bank', 'Camp, Pune'),
('CANB0045678', 'Canara Bank', 'Malviya Nagar, Jaipur'),
('IDBI0078901', 'IDBI Bank', 'Hazratganj, Lucknow'),
('BOB0067890', 'Bank of Baroda', 'Sector 17, Chandigarh');


INSERT INTO ACCOUNT_INFO 
(ACCOUNT_NO, CUSTOMER_ID, ACCOUNT_TYPE, REGISTRATION_DATE, ACTIVATION_DATE, IFSC_CODE, INTEREST, INITIAL_DEPOSIT) 
VALUES 
(100001, 'CUST001', 'Savings', '2022-01-10', '2022-01-15', 'HDFC0001234', 3.5, 25000),
(100002, 'CUST002', 'Current', '2021-12-05', '2021-12-10', 'SBI0009012', 4.0, 50000),
(100003, 'CUST003', 'Savings', '2023-02-15', '2023-02-20', 'ICIC0005678', 3.2, 15000),
(100004, 'CUST004', 'Savings', '2022-08-20', '2022-08-25', 'AXIS0012345', 3.7, 20000),
(100005, 'CUST005', 'Current', '2021-06-10', '2021-06-15', 'PNB0098765', 4.5, 100000),
(100006, 'CUST006', 'Savings', '2023-05-05', '2023-05-10', 'BOB0067890', 3.0, 12000),
(100007, 'CUST007', 'Savings', '2022-04-01', '2022-04-05', 'UBI0023456', 3.5, 18000),
(100008, 'CUST008', 'Current', '2021-10-15', '2021-10-20', 'IDBI0078901', 4.2, 75000),
(100009, 'CUST009', 'Savings', '2023-07-12', '2023-07-17', 'CANB0045678', 3.1, 9000),
(100010, 'CUST010', 'Savings', '2022-09-25', '2022-09-30', 'BOI0034567', 3.6, 30000);


SELECT *FROM ACCOUNT_INFO