Bank database

 CREATE DATABASE BANKMG1;

USE BANKMG1;

CREATE TABLE customer_details (

  cid INT PRIMARY KEY,

  cname VARCHAR(50),

  balance DECIMAL(10, 2)

);

CREATE TABLE transaction (

  tid INT PRIMARY KEY AUTO_INCREMENT,

  cid INT,

  transaction_type ENUM('credit', 'debit'),

  amount DECIMAL(10, 2),

  FOREIGN KEY (cid) REFERENCES customer_details(cid)

);

DROP TABLE TRANSACTION;

-- Insert values into customer_details table

INSERT INTO customer_details (cid, cname, balance)

VALUES (1, 'John Doe', 5000.00),

       (2, 'Jane Smith', 10000.00),

       (3, 'Mike Johnson', 7500.00),

       (4,'Segar',100000.00);

-- Assertions

ALTER TABLE transaction ADD CONSTRAINT check_debit_amount CHECK (transaction_type = 'credit' OR (transaction_type = 'debit' AND amount < 10000));

-- ALTER TABLE CUSTOMER_DETAILS ADD CONSTRAINT debit_below_zero CHECK (BALANCE - transaction.amount > 0);

-- Trigger 

DELIMITER //

CREATE TRIGGER update_balance

AFTER INSERT ON Transaction

FOR EACH ROW

BEGIN

    IF NEW.transaction_type = 'credit' THEN

        UPDATE customer_details

        SET balance = balance + NEW.amount

        WHERE CID = NEW.CID;

    ELSEIF NEW.transaction_type = 'debit' THEN

        UPDATE customer_details

        SET balance = balance - NEW.amount

        WHERE CID = NEW.CID;

    END IF;

END //

DELIMITER ;

drop trigger update_balance;

INSERT INTO transaction (cid, transaction_type, amount) VALUES (4,'debit',500);

       

SELECT *FROM CUSTOMER_DETAILS;

SELECT *FROM TRANSACTION;