dbSchema.sql

CREATE TABLE IF NOT EXISTS `ATM` (  `machineID` INTEGER NOT NULL,  `machineAddress` VARCHAR(45) NULL,  `sessionTimeOut` INTEGER NOT NULL,  `sessionActive` TINYINT NOT NULL,  `maxPinEntryAttempts` INTEGER NOT NULL,  `withdrawalBillsRemaining` INTEGER NOT NULL,  `depositBillCount` INTEGER NOT NULL,  `minBillThreshold` INTEGER NOT NULL,  `maxBillThreshold` INTEGER NOT NULL,  `maxWithdrawalCapacity` INTEGER NOT NULL,  `maxDepositCapacity` INTEGER NOT NULL,  `branchNumber` INTEGER NOT NULL,  PRIMARY KEY (`machineID`),  CONSTRAINT `ATM_BankBranch`    FOREIGN KEY (`branchNumber`)    REFERENCES `BankBranch` (`branchNumber`)    ON DELETE RESTRICT    ON UPDATE CASCADE);

CREATE TABLE IF NOT EXISTS `AccountOpening` (  `customerID` INTEGER NOT NULL,  `accountNumber` INTEGER NOT NULL,  `dateTimeOpening` TIMESTAMP NOT NULL,  PRIMARY KEY (`customerID`, `accountNumber`),  CONSTRAINT `AccountOpening_Client`    FOREIGN KEY (`customerID`)    REFERENCES `Client` (`customerID`)    ON DELETE RESTRICT    ON UPDATE CASCADE,  CONSTRAINT `AccountOpening_Account`    FOREIGN KEY (`accountNumber`)    REFERENCES `Account` (`accountNumber`)    ON DELETE RESTRICT    ON UPDATE CASCADE);

CREATE TABLE IF NOT EXISTS `Account` (  `accountNumber` INTEGER NOT NULL,  `accountName` VARCHAR(45) NOT NULL,  `accountStatus` TINYINT NULL,  `accountBal` DOUBLE NOT NULL,  `accountType` INTEGER NOT NULL,  `interestRate` DOUBLE NULL,  `minReqBalance` DOUBLE NULL,  PRIMARY KEY (`accountNumber`));

CREATE TABLE IF NOT EXISTS `ATMSession` (  `sessionID` INTEGER NOT NULL,  `sessionStartTime` TIMESTAMP NOT NULL,  `sessionEndTime` TIMESTAMP NULL,  `sessionActive` TINYINT NOT NULL,  `machineID` INTEGER NOT NULL,  `cardNumber` BIGINT NULL,  PRIMARY KEY (`sessionID`),  CONSTRAINT `ATMSession_ATM`    FOREIGN KEY (`machineID`)    REFERENCES `ATM` (`machineID`)    ON DELETE CASCADE    ON UPDATE CASCADE,  CONSTRAINT `ATMSession_DebitCard`    FOREIGN KEY (`cardNumber`)    REFERENCES `DebitCard` (`cardNumber`)    ON DELETE SET NULL    ON UPDATE CASCADE);

CREATE TABLE IF NOT EXISTS `BankBranch` (  `branchNumber` INTEGER NOT NULL,  `branchAddress` VARCHAR(45) NOT NULL,  PRIMARY KEY (`branchNumber`));

CREATE TABLE IF NOT EXISTS `CardActivation` (  `cardNumber` BIGINT NULL,  `accountNumber` INTEGER NULL,  `dateTimeActivated` TIMESTAMP NOT NULL,  PRIMARY KEY (`cardNumber`, `accountNumber`),  CONSTRAINT `CardActivation_DebitCard`    FOREIGN KEY (`cardNumber`)    REFERENCES `DebitCard` (`cardNumber`)    ON DELETE SET NULL    ON UPDATE RESTRICT,  CONSTRAINT `CardActivation_Account`    FOREIGN KEY (`accountNumber`)    REFERENCES `Account` (`accountNumber`)    ON DELETE RESTRICT    ON UPDATE CASCADE);

CREATE TABLE IF NOT EXISTS `Client` (  `customerID` INTEGER NOT NULL,  `customerName` VARCHAR(45) NOT NULL,  `customerAddress` VARCHAR(45) NOT NULL,  `customerTel` VARCHAR(45) NOT NULL,  `customerDob` TIMESTAMP NOT NULL,  `branchNumber` INTEGER NOT NULL,  PRIMARY KEY (`customerID`),  CONSTRAINT `Client_BankBranch`    FOREIGN KEY (`branchNumber`)    REFERENCES `BankBranch` (`branchNumber`)    ON DELETE RESTRICT    ON UPDATE CASCADE);

CREATE TABLE IF NOT EXISTS `DebitCard` (  `cardNumber` BIGINT NOT NULL,  `cardHolderName` VARCHAR(45) NOT NULL,  `cardExpDate` TIMESTAMP NOT NULL,  `pinNumber` INTEGER NOT NULL,  `customerID` INTEGER NOT NULL,  `locked` TINYINT NOT NULL,  `branchNumber` INTEGER NOT NULL,  PRIMARY KEY (`cardNumber`),  CONSTRAINT `DebitCard_Client`    FOREIGN KEY (`customerID`)    REFERENCES `Client` (`customerID`)    ON DELETE RESTRICT    ON UPDATE CASCADE,  CONSTRAINT `DebitCard_BankBranch`    FOREIGN KEY (`branchNumber`)    REFERENCES `BankBranch` (`branchNumber`)    ON DELETE RESTRICT    ON UPDATE CASCADE);

CREATE TABLE IF NOT EXISTS `Transaction` (  `transactionID` INTEGER NOT NULL,  `timeDateOfTrans` TIMESTAMP NOT NULL,  `transactionType` INTEGER NOT NULL,  `amount` DOUBLE NOT NULL,  `targetAccNumber` INTEGER NULL,  `sessionID` INTEGER NOT NULL,  `accountNumber` INTEGER NOT NULL,  PRIMARY KEY (`transactionID`),  CONSTRAINT `Transaction_ATMSession`    FOREIGN KEY (`sessionID`)    REFERENCES `ATMSession` (`sessionID`)    ON DELETE RESTRICT    ON UPDATE CASCADE,  CONSTRAINT `Transaction_Account`    FOREIGN KEY (`accountNumber`)    REFERENCES `Account` (`accountNumber`)    ON DELETE RESTRICT    ON UPDATE CASCADE);