Generated a script for MySQL database. I don't want to learn VB.NET, but I can read up NHibernate if you need some help with the ORM creation.

http://pastebin.com/m1jYsXPz

Level of difficulty can be distinguished by specifying the depth of the search for the best move. There is a description at the following link: http://www.ics.uci.edu/~eppstein/180a/970422.html

10 green bottles standing on the wall,

just doing this to learn python,

The SQL script and an initial python file can be found at:

https://github.com/jazzyF/auction

Question about this part of the requirements

"The participants in the auction indicate the amount of dollars they want and the exchange rate at which they want to buy the dollars. The CBN looks through the bids and selects an optimal exchange rate at which (almost) all the dollars for sale in the auction will be bought. Those who offered to buy at that exchange rate or higher will get the amount they asked for at the chosen exchange rate. Those who offered less naira per dollar than the chosen rate
will get nothing. The rate at which the CBN sold dollars becomes the official exchange rate."



To sell the most dollars, the trivial solution would be to select the lowest bid.

Could you clarify the selection process of the optimal rate?

You can try this and see if it makes a difference

<input type="hidden" name="MAX_FILE_SIZE" value="30000" />
<input name="picture" type="file" id="picture" />

Reference: http://www.php.net/manual/en/features.file-upload.post-method.php

You guys can start coding now. I was thinking of using an ORM tool like hibernate to manage the data access layer but it seems you would like to get started ASAP. I guess you will have to settle for JDBC then.

Feel free to create more classes as you deem fit. I have modified the existing business objects to plain ole java objects.

Happy coding,

rokiatu - and I still wanna beat that shtick up

I know ColdFusion,

Check out this open source ColdFusion AppServer: http://www.getrailo.com/

The SQL script is now okay.


SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL';

DROP SCHEMA IF EXISTS `ebank` ;
CREATE SCHEMA IF NOT EXISTS `ebank` DEFAULT CHARACTER SET utf8 ;
USE `ebank` ;

-- -----------------------------------------------------
-- Table `ebank`.`accounttype`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `ebank`.`accounttype` ;

CREATE TABLE IF NOT EXISTS `ebank`.`accounttype` (
`typeID` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT ,
`type` CHAR(15) NULL DEFAULT NULL ,
`interestRate` FLOAT NULL DEFAULT '0' ,
`monthlyCharges` FLOAT NOT NULL DEFAULT '50' ,
`minimumBalance` DOUBLE NULL DEFAULT '0' ,
`overdraftAllowed` TINYINT(1) NULL DEFAULT '0' ,
PRIMARY KEY (`typeID`) ,
UNIQUE INDEX `type_UNIQUE` (`type` ASC) )
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;


-- -----------------------------------------------------
-- Table `ebank`.`account`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `ebank`.`account` ;

CREATE TABLE IF NOT EXISTS `ebank`.`account` (
`accountID` BIGINT(9) UNSIGNED ZEROFILL NOT NULL AUTO_INCREMENT ,
`nuban` VARCHAR(13) NOT NULL ,
`typeID` INT(10) UNSIGNED NOT NULL ,
`status` ENUM('ACTIVE','DORMANT','CLOSED') NOT NULL DEFAULT 'ACTIVE' ,
`balance` DOUBLE NOT NULL DEFAULT '0' ,
`openedSince` TIMESTAMP NOT NULL DEFAULT NOW() ,
`closedOn` TIMESTAMP NULL DEFAULT NULL ,
PRIMARY KEY (`accountID`) ,
UNIQUE INDEX `nuban_UNIQUE` (`nuban` ASC) ,
INDEX `typeID` (`typeID` ASC) ,
INDEX `accountID` (`accountID` ASC) ,
CONSTRAINT `typeID`
FOREIGN KEY (`typeID` )
REFERENCES `ebank`.`accounttype` (`typeID` )
ON UPDATE CASCADE)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;


-- -----------------------------------------------------
-- Table `ebank`.`customer`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `ebank`.`customer` ;

CREATE TABLE IF NOT EXISTS `ebank`.`customer` (
`customerID` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT ,
`userLogin` CHAR(45) NOT NULL ,
`firstName` CHAR(45) NOT NULL ,
`middleName` CHAR(45) NULL DEFAULT NULL ,
`LastName` CHAR(45) NOT NULL ,
`dateOfBirth` DATE NOT NULL ,
`cityOfBirth` CHAR(45) NOT NULL ,
`nationality` CHAR(45) NOT NULL DEFAULT 'NIGERIAN' ,
`residentialAddress` CHAR(45) NOT NULL ,
`email` CHAR(45) NULL DEFAULT NULL ,
`password` BLOB NOT NULL ,
`customerSince` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ,
PRIMARY KEY (`customerID`) ,
UNIQUE INDEX `userLogin_UNIQUE` (`userLogin` ASC) ,
UNIQUE INDEX `namesAndBirth` (`firstName` ASC, `LastName` ASC, `dateOfBirth` ASC) )
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;


-- -----------------------------------------------------
-- Table `ebank`.`activitylog`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `ebank`.`activitylog` ;

CREATE TABLE IF NOT EXISTS `ebank`.`activitylog` (
`logID` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT ,
`timeStamp` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ,
`customer` BIGINT(20) UNSIGNED NOT NULL ,
`activityDump` LONGTEXT NOT NULL ,
PRIMARY KEY (`logID`) ,
INDEX `customer` (`customer` ASC) ,
CONSTRAINT `customer`
FOREIGN KEY (`customer` )
REFERENCES `ebank`.`customer` (`customerID` )
ON UPDATE CASCADE)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;


-- -----------------------------------------------------
-- Table `ebank`.`customeraccounts`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `ebank`.`customeraccounts` ;

CREATE TABLE IF NOT EXISTS `ebank`.`customeraccounts` (
`custAcctID` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT ,
`customer` BIGINT(20) UNSIGNED NOT NULL ,
`account` BIGINT(9) UNSIGNED ZEROFILL NOT NULL ,
PRIMARY KEY (`custAcctID`) ,
INDEX `customer_fk` (`customer` ASC) ,
INDEX `account_fk` (`account` ASC) ,
CONSTRAINT `customerFK`
FOREIGN KEY (`customer` )
REFERENCES `ebank`.`customer` (`customerID` )
ON DELETE CASCADE
ON UPDATE CASCADE,
CONSTRAINT `accountFK`
FOREIGN KEY (`account` )
REFERENCES `ebank`.`account` (`accountID` )
ON DELETE CASCADE
ON UPDATE CASCADE)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;


-- -----------------------------------------------------
-- Table `ebank`.`systemuser`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `ebank`.`systemuser` ;

CREATE TABLE IF NOT EXISTS `ebank`.`systemuser` (
`userID` INT(9) UNSIGNED NOT NULL AUTO_INCREMENT ,
`login` CHAR(45) NOT NULL ,
`role` ENUM('CSR','ADMIN','MANAGER','RPT ANALYST','AUDITOR') NOT NULL DEFAULT 'CSR' ,
`firstName` CHAR(45) NOT NULL ,
`middleName` CHAR(45) NULL DEFAULT NULL ,
`lastName` CHAR(45) NOT NULL ,
`birthDate` DATE NOT NULL ,
`isActive` TINYINT(1) NOT NULL ,
`password` BLOB NOT NULL ,
`timeStampCreated` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ,
PRIMARY KEY (`userID`) ,
UNIQUE INDEX `login_UNIQUE` (`login` ASC) ,
UNIQUE INDEX `nameAndDOB` (`firstName` ASC, `lastName` ASC, `birthDate` ASC) )
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;


-- -----------------------------------------------------
-- Table `ebank`.`transaction`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `ebank`.`transaction` ;

CREATE TABLE IF NOT EXISTS `ebank`.`transaction` (
`transID` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT ,
`accountID` BIGINT(9) UNSIGNED ZEROFILL NOT NULL ,
`amount` FLOAT NOT NULL ,
`time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ,
`remarks` VARCHAR(50) NULL DEFAULT NULL ,
`customer` INT(9) NULL DEFAULT NULL ,
PRIMARY KEY (`transID`) ,
INDEX `accountID` (`accountID` ASC) ,
INDEX `transID` (`transID` ASC) ,
CONSTRAINT `accountID`
FOREIGN KEY (`accountID` )
REFERENCES `ebank`.`account` (`accountID` )
ON UPDATE CASCADE)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;


-- -----------------------------------------------------
-- Table `ebank`.`sequence`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `ebank`.`sequence` ;

CREATE TABLE IF NOT EXISTS `ebank`.`sequence` (
`nextval` INT(9) UNSIGNED NOT NULL AUTO_INCREMENT ,
PRIMARY KEY (`nextval`) )
ENGINE = InnoDB
AUTO_INCREMENT = 1
DEFAULT CHARACTER SET = utf8;


-- -----------------------------------------------------
-- function getNubanForID
-- -----------------------------------------------------

USE `ebank`;
DROP function IF EXISTS `ebank`.`getNubanForID`;

DELIMITER $$
USE `ebank`$$
CREATE DEFINER=`root`@`localhost` FUNCTION `getNubanForID`(ownerID BIGINT(9), bankCode INT(3)) RETURNS VARCHAR(13) CHARSET utf8
BEGIN

DECLARE checkDigit TINYINT(1);

DECLARE A TINYINT(1);
DECLARE B TINYINT(1);
DECLARE C TINYINT(1);

DECLARE D TINYINT(1);
DECLARE E TINYINT(1);
DECLARE F TINYINT(1);
DECLARE G TINYINT(1);
DECLARE H TINYINT(1);
DECLARE I TINYINT(1);
DECLARE J TINYINT(1);
DECLARE K TINYINT(1);
DECLARE L TINYINT(1);
DECLARE strOwnerID varchar(9);
DECLARE bank CHAR(3);
DECLARE len TINYINT(1);
DECLARE result VARCHAR(13);

SET strOwnerID = RIGHT(CONCAT('000000000', OwnerID), 9);

SET bank = CAST(bankCode AS CHAR(3));
SET L = MOD(ownerID, 10);
SET ownerID = ownerID DIV 10;
SET K = MOD(ownerID, 10);
SET ownerID = ownerID DIV 10;
SET J = MOD(ownerID, 10);
SET ownerID = ownerID DIV 10;
SET I = MOD(ownerID, 10);
SET ownerID = ownerID DIV 10;
SET H = MOD(ownerID, 10);
SET ownerID = ownerID DIV 10;
SET G = MOD(ownerID, 10);
SET ownerID = ownerID DIV 10;
SET F = MOD(ownerID, 10);
SET ownerID = ownerID DIV 10;
SET E = MOD(ownerID, 10);
SET ownerID = ownerID DIV 10;
SET D = MOD(ownerID, 10);

SET C = MOD(bankCode, 10);
SET bankCode = bankCode DIV 10;
SET B = MOD(bankCode, 10);
SET bankCode = bankCode DIV 10;
SET A = MOD(bankCode, 10);

SET checkDigit = MOD((A*3 + B*7 + C*3 + D*3 + E*7 + F*3 + G*3 + H*7 + I*3 + J*3 + K*7 + L*3), 10);

SET result = CONCAT(bank,strOwnerID,CAST(checkDigit AS CHAR(1)));

RETURN result;
END


$$

DELIMITER ;

-- -----------------------------------------------------
-- function currval
-- -----------------------------------------------------

USE `ebank`;
DROP function IF EXISTS `ebank`.`currval`;

DELIMITER $$
USE `ebank`$$
CREATE DEFINER=`root`@`localhost` FUNCTION `currval`() RETURNS BIGINT(9)
begin
return @lastInsert_val ;
end



$$

DELIMITER ;

-- -----------------------------------------------------
-- function nextval
-- -----------------------------------------------------

USE `ebank`;
DROP function IF EXISTS `ebank`.`nextval`;

DELIMITER $$
USE `ebank`$$
CREATE DEFINER=`root`@`localhost` FUNCTION `nextval`() RETURNS BIGINT(9)
begin
insert into `ebank`.`sequence` values (NULL) ;
set @lastInsert_val= LAST_INSERT_ID() ;
delete from `ebank`.`sequence` ;
return @lastInsert_val ;
end



$$

DELIMITER ;
USE `ebank`;

DELIMITER $$

USE `ebank`$$
DROP TRIGGER IF EXISTS `ebank`.`newAccount` $$
USE `ebank`$$


CREATE
DEFINER=`root`@`localhost`
TRIGGER `ebank`.`newAccount`
BEFORE INSERT ON `ebank`.`account`
FOR EACH ROW
BEGIN
SET NEW.nuban = ebank.getNubanForID(ebank.nextval(), 107);
SET NEW.openedSince = NOW();
END$$


DELIMITER ;

DELIMITER $$

USE `ebank`$$
DROP TRIGGER IF EXISTS `ebank`.`createCustomer` $$
USE `ebank`$$
CREATE
DEFINER=`root`@`localhost`
TRIGGER `ebank`.`createCustomer`
BEFORE INSERT ON `ebank`.`customer`
FOR EACH ROW
SET NEW.customerSince = NOW(),
NEW.password = AES_ENCRYPT('nairalandKennwort',NEW.Password)$$


DELIMITER ;

DELIMITER $$

USE `ebank`$$
DROP TRIGGER IF EXISTS `ebank`.`createUser` $$
USE `ebank`$$
CREATE
DEFINER=`root`@`localhost`
TRIGGER `ebank`.`createUser`
BEFORE INSERT ON `ebank`.`systemuser`
FOR EACH ROW
BEGIN
SET NEW.timeStampCreated = NOW();
SET NEW.password = AES_ENCRYPT('nairaland2', NEW.password);
END$$


DELIMITER ;


SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;


-- Insert script
-- Insert data

INSERT INTO ebank.accounttype
(type, interestRate, monthlyCharges, minimumBalance, overdraftAllowed)
VALUES ('CURRENT', 0.0, 50.00, 15000, '1');

INSERT INTO ebank.accounttype
(type, interestRate, monthlyCharges, minimumBalance, overdraftAllowed)
VALUES ('SAVINGS', 0.01, 0.00, 500, '0');

INSERT INTO ebank.accounttype
(type, interestRate, monthlyCharges, minimumBalance, overdraftAllowed)
VALUES ('MORTGAGE', 0.0, 0.00, 0, '0');

INSERT INTO ebank.accounttype
(type, interestRate, monthlyCharges, minimumBalance, overdraftAllowed)
VALUES ('CREDIT', -1.15, 5.00, 0, '1');


INSERT INTO ebank.customer
(userLogin, firstName, middleName, LastName, dateOfBirth, cityOfBirth, nationality, residentialAddress, email, password, customerSince)
VALUES ('nov1ce', 'Ado', 'John', 'Sule', '1982-10-12', 'Enugu', 'Nigerian', '201 Mbadiwe Rd, Imo state', 'nov1ce@yahoo.com', 'npassword1', NOW());

INSERT INTO ebank.customer
(userLogin, firstName, middleName, LastName, dateOfBirth, cityOfBirth, nationality, residentialAddress, email, password, customerSince)
VALUES ('fayimora', 'Femi', 'Fayimora', 'Balogun', '1980-09-01', 'Kano', 'Nigerian', '114 Maitama Sule Avenue, Kano state', 'info@fayimora.com', 'npassword1', NOW());


INSERT INTO ebank.account
(accountID, nuban, typeID, status, balance, openedSince, closedOn)
VALUES (0, 0, 1, 'ACTIVE', 35000.00, CAST(NOW() AS DATE), NULL);

INSERT INTO ebank.account
(accountID, nuban, typeID, status, balance, openedSince, closedOn)
VALUES (0, 0, 1, 'ACTIVE', 6535000.00, CAST(NOW() AS DATE), NULL);


INSERT INTO ebank.account
(accountID, nuban, typeID, status, balance, openedSince, closedOn)
VALUES (0, 0, 1, 'ACTIVE', 1534291.00, CAST(NOW() AS DATE), NULL);

COMMIT;


You guys should try to create some test data and let me know if you find any bugs.

Anyone still interested in this project? Things are just about to get interesting.

Spoke too soon,

I have some problems with getting MySQL to use the auto incremented indices in a SQL function called by a trigger.

My work-around - by using LAST_INSERT_ID() to get the latest generated index - is still buggy.

Code to illustrate.
-- Desired code
DELIMITER $$

USE `eBank`$$

CREATE TRIGGER `eBank`.`newAccount`
BEFORE INSERT ON `eBank`.`Account`
FOR EACH ROW BEGIN
    SET NEW.nuban = ebank.getNubanForID(NEW.accountID, 107);
    SET NEW.openedSince = CAST(NOW() AS DATE);
END;

$$


-- Work around
DELIMITER $$

USE `eBank`$$

CREATE TRIGGER `eBank`.`newAccount`
BEFORE INSERT ON `eBank`.`Account`
FOR EACH ROW BEGIN
    SET NEW.nuban = ebank.getNubanForID(LAST_INSERT_ID() + 1, 107);
    SET NEW.openedSince = CAST(NOW() AS DATE);
END;

$$

You can find the complete source code in the github repository.

for mysql, use AUTO_INCREMENT,
for oracle, create a sequence and get the nextval , see http://jen.fluxcapacitor.net/geek/autoincr.html

That is possible, for example in oracle,


select * from account where id = &id;

I think they are called bind variables or something

-- For eBank.Accounttype

INSERT INTO `ebank`.`accounttype` (`typeID`, `type`, `interestRate`, `monthlyCharges`, `minimumBalance`, `overdraftAllowed`) VALUES (1, 'CURRENT', 0, 50.00, 25000, true);

INSERT INTO `ebank`.`accounttype` (`typeID`, `type`, `interestRate`, `monthlyCharges`, `minimumBalance`, `overdraftAllowed`) VALUES (2, 'SAVINGS', 0.01, 0, 500, false);

INSERT INTO `ebank`.`accounttype` (`typeID`, `type`, `interestRate`, `monthlyCharges`, `minimumBalance`, `overdraftAllowed`) VALUES (3, 'MORTGAGE', 0, 0, 0, false);

INSERT INTO `ebank`.`accounttype` (`typeID`, `type`, `interestRate`, `monthlyCharges`, `minimumBalance`, `overdraftAllowed`) VALUES (4, 'CREDIT', 1.15, 5.00, 0, true);

hey Nov1ce, there is actually a couple of things that need doing. I have just been busy trying to meet some work deadlines, sorry,

To do
- Check that the tables are adequately normalized (don't bother if you are not studying computer science or related field)
- Create sample data for the tables (using insert statements)
- Create a data flow diagram or just notes (for example, log-in page -> user home -> accounts home, we are going to need that for the user interfaces)
- You can look at BIRT if you are interested in working on the Reports side

Thanks Fayimora,

Got the script working now.

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL';

DROP SCHEMA IF EXISTS `eBank` ;
CREATE SCHEMA IF NOT EXISTS `eBank` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci ;
USE `eBank` ;

-- -----------------------------------------------------
-- Table `eBank`.`Customer`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `eBank`.`Customer` ;

CREATE  TABLE IF NOT EXISTS `eBank`.`Customer` (
  `customerID` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT ,
  `userLogin` CHAR(45) NOT NULL ,
  `firstName` CHAR(45) NOT NULL ,
  `middleName` CHAR(45) NULL ,
  `LastName` CHAR(45) NOT NULL ,
  `dateOfBirth` DATE NOT NULL ,
  `cityOfBirth` CHAR(45) NOT NULL ,
  `nationality` CHAR(45) NOT NULL DEFAULT 'NIGERIAN' ,
  `residentialAddress` CHAR(45) NOT NULL ,
  `email` CHAR(45) NULL ,
  `password` BLOB NOT NULL ,
  `customerSince` TIMESTAMP NOT NULL ,
  PRIMARY KEY (`customerID`) ,
  UNIQUE INDEX `userLogin_UNIQUE` (`userLogin` ASC) ,
  UNIQUE INDEX `namesAndBirth` (`firstName` ASC, `LastName` ASC, `dateOfBirth` ASC) )
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `eBank`.`AccountType`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `eBank`.`AccountType` ;

CREATE  TABLE IF NOT EXISTS `eBank`.`AccountType` (
  `typeID` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
  `type` CHAR(15) NULL ,
  `interestRate` FLOAT NULL DEFAULT 0.0 ,
  `monthlyCharges` FLOAT NOT NULL DEFAULT 50.0 ,
  `minimumBalance` DOUBLE NULL DEFAULT 0.0 ,
  `overdraftAllowed` TINYINT(1)  NULL DEFAULT false ,
  PRIMARY KEY (`typeID`) ,
  UNIQUE INDEX `type_UNIQUE` (`type` ASC) )
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `eBank`.`Account`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `eBank`.`Account` ;

CREATE  TABLE IF NOT EXISTS `eBank`.`Account` (
  `accountID` INT(9) ZEROFILL UNSIGNED NOT NULL AUTO_INCREMENT ,
  `nuban` INT(13) NULL ,
  `typeID` INT UNSIGNED NOT NULL ,
  `status` ENUM('ACTIVE', 'DORMANT', 'CLOSED') NOT NULL DEFAULT 'ACTIVE' ,
  `balance` DOUBLE NOT NULL DEFAULT 0.0 ,
  `openedSince` DATE NOT NULL ,
  `closedOn` TIMESTAMP NULL DEFAULT NULL ,
  PRIMARY KEY (`accountID`) ,
  INDEX `typeID` (`typeID` ASC) ,
  CONSTRAINT `typeID`
    FOREIGN KEY (`typeID` )
    REFERENCES `eBank`.`AccountType` (`typeID` )
    ON DELETE RESTRICT
    ON UPDATE CASCADE)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `eBank`.`User`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `eBank`.`User` ;

CREATE  TABLE IF NOT EXISTS `eBank`.`User` (
  `userID` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT ,
  `login` CHAR(45) NOT NULL ,
  `role` ENUM('CSR', 'ADMIN', 'MANAGER', 'RPT ANALYST', 'AUDITOR') NOT NULL DEFAULT 'CSR' ,
  `firstName` CHAR(45) NOT NULL ,
  `middleName` CHAR(45) NULL ,
  `lastName` CHAR(45) NOT NULL ,
  `birthDate` DATE NOT NULL ,
  `isActive` TINYINT(1)  NOT NULL ,
  `password` BLOB NOT NULL ,
  `timeStampCreated` TIMESTAMP NOT NULL ,
  PRIMARY KEY (`userID`) ,
  UNIQUE INDEX `login_UNIQUE` (`login` ASC) ,
  UNIQUE INDEX `nameAndDOB` (`firstName` ASC, `lastName` ASC, `birthDate` ASC) )
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `eBank`.`ActivityLog`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `eBank`.`ActivityLog` ;

CREATE  TABLE IF NOT EXISTS `eBank`.`ActivityLog` (
  `logID` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
  `timeStamp` TIMESTAMP NOT NULL ,
  `customer` BIGINT UNSIGNED NOT NULL ,
  `activityDump` LONGTEXT NOT NULL ,
  PRIMARY KEY (`logID`) ,
  INDEX `customer` (`customer` ASC) ,
  CONSTRAINT `customer`
    FOREIGN KEY (`customer` )
    REFERENCES `eBank`.`Customer` (`customerID` )
    ON DELETE RESTRICT
    ON UPDATE CASCADE)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `eBank`.`Transaction`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `eBank`.`Transaction` ;

CREATE  TABLE IF NOT EXISTS `eBank`.`Transaction` (
  `transID` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
  `accountID` INT(9) ZEROFILL NOT NULL ,
  `amount` FLOAT NOT NULL ,
  `time` TIMESTAMP NOT NULL ,
  `remarks` VARCHAR(50) NULL ,
  `customer` INT(9) NULL ,
  PRIMARY KEY (`transID`) ,
  INDEX `accountID` (`accountID` ASC) ,
  INDEX `transID` (`transID` ASC) ,
  CONSTRAINT `accountID`
    FOREIGN KEY (`accountID` )
    REFERENCES `eBank`.`Account` (`accountID` )
    ON DELETE RESTRICT
    ON UPDATE CASCADE)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `eBank`.`CustomerAccounts`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `eBank`.`CustomerAccounts` ;

CREATE  TABLE IF NOT EXISTS `eBank`.`CustomerAccounts` (
  `custAcctID` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT ,
  `customer` BIGINT UNSIGNED NOT NULL ,
  `account` INT(9) UNSIGNED ZEROFILL NOT NULL ,
  PRIMARY KEY (`custAcctID`) ,
  INDEX `customer_fk` (`customer` ASC) ,
  INDEX `account_fk` (`account` ASC) ,
  CONSTRAINT `customerFK`
    FOREIGN KEY (`customer` )
    REFERENCES `eBank`.`Customer` (`customerID` )
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `accountFK`
    FOREIGN KEY (`account` )
    REFERENCES `eBank`.`Account` (`accountID` )
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- function getNubanForID
-- -----------------------------------------------------

USE `eBank`;
DROP function IF EXISTS `eBank`.`getNubanForID`;

DELIMITER $$
USE `eBank`$$
CREATE FUNCTION `eBank`.`getNubanForID` (ownerID INT(9), bankCode INT(3))
RETURNS INT(13)
BEGIN

    DECLARE result INT(13);
    DECLARE checkDigit INT(1);

    DECLARE A INT(1);
    DECLARE B INT(1);
    DECLARE C INT(1);

    DECLARE D INT(1);
    DECLARE E INT(1);
    DECLARE F INT(1);
    DECLARE G INT(1);
    DECLARE H INT(1);
    DECLARE I INT(1);
    DECLARE J INT(1);
    DECLARE K INT(1);
    DECLARE L INT(1);
   
    DECLARE strResult CHAR(13);

    SET L = ownerID % 10;
    SET ownerID = ownerID / 10;
    SET K = ownerID % 10;
    SET ownerID = ownerID / 10;
    SET J = ownerID % 10;
    SET ownerID = ownerID / 10;
    SET I = ownerID % 10;
    SET ownerID = ownerID / 10;
    SET H = ownerID % 10;
    SET ownerID = ownerID / 10;
    SET G = ownerID % 10;
    SET ownerID = ownerID / 10;
    SET F = ownerID % 10;
    SET ownerID = ownerID / 10;
    SET E = ownerID % 10;
    SET ownerID = ownerID / 10;
    SET D = ownerID % 10;



    SET C = bankCode % 10;
    SET bankCode = bankCode DIV 10;
    SET B = bankCode % 10;
    SET bankCode = bankCode DIV 10;
    SET A = bankCode % 10;
   
    SET checkDigit = (A*3 + B*7 + C*3 + D*3 + E*7 + F*3 + G*3 + H*7 + I*3 + J*3 + K*7 + L*3)%10;
   
    SET strResult = CONCAT(CAST(bankCode AS CHAR(3)),CAST(ownerID AS CHAR(9)),CAST(checkDigit AS CHAR(1)));
    SET result = CAST(strResult AS UNSIGNED);
       
    RETURN result;
END
$$

DELIMITER ;
USE `eBank`;

DELIMITER $$

USE `eBank`$$
DROP TRIGGER IF EXISTS `eBank`.`createCustomer` $$
USE `eBank`$$


CREATE TRIGGER createCustomer
BEFORE INSERT ON `Customer`
FOR EACH ROW
SET NEW.customerSince = NOW(),
NEW.password = AES_ENCRYPT('nairalandKennwort',NEW.Password);
$$


DELIMITER ;

DELIMITER $$

USE `eBank`$$
DROP TRIGGER IF EXISTS `eBank`.`newAccount` $$
USE `eBank`$$


CREATE TRIGGER `eBank`.`newAccount`
BEFORE INSERT ON `eBank`.`Account`
FOR EACH ROW
SET NEW.nuban = getNubanForID(NEW.accountID, 107),
NEW.openedSince = NOW()$$


DELIMITER ;

DELIMITER $$

USE `eBank`$$
DROP TRIGGER IF EXISTS `eBank`.`createUser` $$
USE `eBank`$$


CREATE TRIGGER createUser
BEFORE INSERT ON `User`
FOR EACH ROW
SET NEW.timeStampCreated = NOW(),
NEW.password = AES_ENCRYPT('nairaland2', NEW.password);
$$


DELIMITER ;


SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

The ER diagram used to generate the above script.

I need some help troubleshooting the following SQL script. MySQL server version is: 5.5.15 MySQL Community Server
The error message is: ERROR: Error 1005: Can't create table 'ebank.user' (errno: 150)


SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL';

DROP SCHEMA IF EXISTS `eBank` ;
CREATE SCHEMA IF NOT EXISTS `eBank` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci ;
USE `eBank` ;

-- -----------------------------------------------------
-- Table `eBank`.`Customer`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `eBank`.`Customer` ;

CREATE  TABLE IF NOT EXISTS `eBank`.`Customer` (
  `customerID` INT(9) ZEROFILL UNSIGNED NOT NULL AUTO_INCREMENT ,
  `userLogin` CHAR(45) NOT NULL ,
  `firstName` CHAR(45) NOT NULL ,
  `middleName` CHAR(45) NULL ,
  `LastName` CHAR(45) NOT NULL ,
  `dateOfBirth` DATE NOT NULL ,
  `cityOfBirth` CHAR(45) NOT NULL ,
  `nationality` CHAR(45) NOT NULL ,
  `residentialAddress` CHAR(45) NOT NULL ,
  `email` CHAR(45) NULL ,
  `password` BLOB NOT NULL ,
  `customerSince` TIMESTAMP NOT NULL ,
  PRIMARY KEY (`customerID`) )
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `eBank`.`AccountType`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `eBank`.`AccountType` ;

CREATE  TABLE IF NOT EXISTS `eBank`.`AccountType` (
  `typeID` INT UNSIGNED ZEROFILL NOT NULL AUTO_INCREMENT ,
  `type` CHAR(15) NOT NULL ,
  `interestRate` FLOAT NOT NULL ,
  `monthlyCharges` FLOAT NOT NULL ,
  `minimumBalance` DOUBLE NOT NULL ,
  `overdraftAllowed` TINYINT(1)  NOT NULL DEFAULT false ,
  PRIMARY KEY (`typeID`) ,
  INDEX `typeID` (`typeID` ASC) ,
  UNIQUE INDEX `type_UNIQUE` (`type` ASC) )
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `eBank`.`Account`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `eBank`.`Account` ;

CREATE  TABLE IF NOT EXISTS `eBank`.`Account` (
  `nuban` INT(13) UNSIGNED ZEROFILL NOT NULL ,
  `ownerid` INT(9) UNSIGNED ZEROFILL NOT NULL ,
  `type` CHAR(15) NOT NULL ,
  `status` CHAR(10) NOT NULL ,
  `balance` DOUBLE NOT NULL ,
  `openedSince` TIMESTAMP NOT NULL ,
  `closedOn` TIMESTAMP NULL ,
  PRIMARY KEY (`nuban`, `ownerid`) ,
  INDEX `fk_Account_AccountType` (`type` ASC) ,
  INDEX `fk_Account_Customer1` (`ownerid` ASC) ,
  CONSTRAINT `fk_Account_AccountType`
    FOREIGN KEY (`type` )
    REFERENCES `eBank`.`AccountType` (`type` )
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `fk_Account_Customer1`
    FOREIGN KEY (`ownerid` )
    REFERENCES `eBank`.`Customer` (`customerID` )
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `eBank`.`Role`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `eBank`.`Role` ;

CREATE  TABLE IF NOT EXISTS `eBank`.`Role` (
  `roleID` INT UNSIGNED ZEROFILL NOT NULL AUTO_INCREMENT ,
  `roleName` VARCHAR(15) NOT NULL ,
  PRIMARY KEY (`roleID`) )
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `eBank`.`User`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `eBank`.`User` ;

CREATE  TABLE IF NOT EXISTS `eBank`.`User` (
  `userID` INT(9) ZEROFILL NOT NULL AUTO_INCREMENT ,
  `login` CHAR(45) NOT NULL ,
  `firstName` CHAR(45) NOT NULL ,
  `middleName` CHAR(45) NULL ,
  `lastName` CHAR(45) NOT NULL ,
  `role` CHAR(15) NOT NULL ,
  `isActive` TINYINT(1)  NOT NULL ,
  `password` BLOB NOT NULL ,
  PRIMARY KEY (`userID`) ,
  INDEX `fk_User_Role` (`role` ASC) ,
  CONSTRAINT `fk_User_Role`
    FOREIGN KEY (`role` )
    REFERENCES `eBank`.`Role` (`roleName` )
    ON DELETE RESTRICT
    ON UPDATE CASCADE)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `eBank`.`ActivityLog`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `eBank`.`ActivityLog` ;

CREATE  TABLE IF NOT EXISTS `eBank`.`ActivityLog` (
  `logID` INT UNSIGNED ZEROFILL NOT NULL ,
  `timeStamp` TIMESTAMP NOT NULL ,
  `userLogin` CHAR(45) NOT NULL ,
  `activityDump` LONGTEXT NOT NULL ,
  PRIMARY KEY (`logID`) ,
  INDEX `fk_ActivityLog_Customer` (`userLogin` ASC) ,
  CONSTRAINT `fk_ActivityLog_Customer`
    FOREIGN KEY (`userLogin` )
    REFERENCES `eBank`.`Customer` (`userLogin` )
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `eBank`.`Transaction`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `eBank`.`Transaction` ;

CREATE  TABLE IF NOT EXISTS `eBank`.`Transaction` (
  `transID` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
  `nuban` INT(13) UNSIGNED ZEROFILL NOT NULL ,
  `amount` FLOAT NOT NULL ,
  `time` TIMESTAMP NOT NULL ,
  `remarks` VARCHAR(50) NULL ,
  PRIMARY KEY (`transID`) ,
  INDEX `fk_Transaction_Account` (`nuban` ASC) ,
  CONSTRAINT `fk_Transaction_Account`
    FOREIGN KEY (`nuban` )
    REFERENCES `eBank`.`Account` (`nuban` )
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- function getNubanForID
-- -----------------------------------------------------

USE `eBank`;
DROP function IF EXISTS `eBank`.`getNubanForID`;

DELIMITER $$
USE `eBank`$$
CREATE FUNCTION `eBank`.`getNubanForID` (ownerID INT(9), bankCode INT(3))
RETURNS INT(13)
BEGIN

    DECLARE result INT(13);
    DECLARE checkDigit INT(1);

    DECLARE A INT(1);
    DECLARE B INT(1);
    DECLARE C INT(1);

    DECLARE D INT(1);
    DECLARE E INT(1);
    DECLARE F INT(1);
    DECLARE G INT(1);
    DECLARE H INT(1);
    DECLARE I INT(1);
    DECLARE J INT(1);
    DECLARE K INT(1);
    DECLARE L INT(1);
   
    DECLARE strResult CHAR(13);

    SET L = ownerID % 10;
    SET ownerID = ownerID / 10;
    SET K = ownerID % 10;
    SET ownerID = ownerID / 10;
    SET J = ownerID % 10;
    SET ownerID = ownerID / 10;
    SET I = ownerID % 10;
    SET ownerID = ownerID / 10;
    SET H = ownerID % 10;
    SET ownerID = ownerID / 10;
    SET G = ownerID % 10;
    SET ownerID = ownerID / 10;
    SET F = ownerID % 10;
    SET ownerID = ownerID / 10;
    SET E = ownerID % 10;
    SET ownerID = ownerID / 10;
    SET D = ownerID % 10;



    SET C = bankCode % 10;
    SET bankCode = bankCode DIV 10;
    SET B = bankCode % 10;
    SET bankCode = bankCode DIV 10;
    SET A = bankCode % 10;
   
    SET checkDigit = (A*3 + B*7 + C*3 + D*3 + E*7 + F*3 + G*3 + H*7 + I*3 + J*3 + K*7 + L*3)%10;
   
    SET strResult = CONCAT(CHAR(bankCode),CHAR(ownerID),CHAR(checkDigit));
    SET result = CAST(strResult AS UNSIGNED);
       
    RETURN result;
END$$

DELIMITER ;
USE `eBank`;

DELIMITER $$

USE `eBank`$$
DROP TRIGGER IF EXISTS `eBank`.`createCustomer` $$
USE `eBank`$$


CREATE TRIGGER createCustomer
BEFORE INSERT ON `Customer`
FOR EACH ROW
SET NEW.customerSince = NOW(),
NEW.password = AES_ENCRYPT('nairalandKennwort',NEW.Password);
$$


DELIMITER ;

DELIMITER $$

USE `eBank`$$
DROP TRIGGER IF EXISTS `eBank`.`createAccount` $$
USE `eBank`$$


-- Bank code is 107
CREATE TRIGGER `createAccount` BEFORE INSERT ON `Account`
FOR EACH ROW
    SET NEW.nuban = getNubanForID(NEW.ownerid, 107),
    NEW.openedSince = NOW();
   
CREATE TRIGGER `updateAccount` BEFORE UPDATE ON `Account`
FOR EACH ROW
    SET NEW.nuban = getNubanForID(NEW.ownerid, 107)
$$


DELIMITER ;

DELIMITER $$

USE `eBank`$$
DROP TRIGGER IF EXISTS `eBank`.`createUser` $$
USE `eBank`$$


CREATE TRIGGER createUser
BEFORE INSERT ON `User`
FOR EACH ROW
SET NEW.TimeStampCreated = NOW(),
NEW.password = AES_ENCRYPT('nairaland2', NEW.password);
$$


DELIMITER ;


SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

I have been looking at sqlite as an alternative to mysql, but I have never used it before. I could not find out which SQL standard is supported and to what extent. It would be a lot neater if we could use it.

We could start with a flat file as the database, but we still need to generate a NUBAN for each account.

We need someone to write a mysql function to generate a NUBAN for each created account. That function will be called by a trigger to populate the 'nuban' field. Refer to the URL below


Reference: http://www.cenbank.org/out/2011/circulars/bspd/nuban%20proposals%20v%200%204-%2003%2009%202010.pdf

Hold on till we get the whole design in place,

I am partial to subversion, for those who are like minded, here goes

To use subversion to access the repository, use the command: svn checkout http://svn.github.com/fayimora/Banking-System.git

To use a subversion GUI client like tortoiseSVN (the repo URL is http://svn.github.com/fayimora/Banking-System.git)



Reference: https://github.com/blog/626-announcing-svn-support

Next step now is some sort of data architecture. (for example)
- What attributes are needed for an account?
- What are the relationships between the different entities?

Seems those who indicated interested are missing in action. Are there others willing to step up to the plate?

That is the norm in Egypt for criminal trials.

Good job Nov,

Part of the requirements from Fayimora: "The system system is to provide a report feature that lists allt he accounts and the interest the have accrued during the current period. The system should also provide an auditor feature that when invoked, prints all accounts whose balance falls below the minimum required for that type of account. The system would also be able to add, delete and modify accounts(account num exclusive).

The system should be able to send emails to all bank customers(newsletter ish). The system should be able to keep admin log reports, i.e who used what software and who changed what.

For each account the system would record, personal details for the user like, name, address, status, number and other bank details like account number, sort-code, current balance, interest rate. Also, the system should be able to deliver weekly, monthly, quarterly and annual bank statements"

You have the use case for CSR and Report System. Give me your feedback on the attached image.

Regards

Ramadan Kareem!

@190, u r hilarious!

@poster, u r okay, but u could really use some weight loss

The following list of use cases is not exhaustive. It is just a starting point. Feel free to come up with any others you identify.

Use case for Admin - Interraction with Bank system          (Ashkush)
                             - Interraction with Report system        (Danyl)
                             - Interraction with gateway                 (agi-tuedor)


Use case for CSR - Interraction with Bank system        (Mobinga)
                         - Interraction with Report system      (Nov1ce)
                         - Interraction with gateway               (cracky2)


Use case for Customer - Interraction with Bank system    (debokaz)
                                 - Interraction with gateway           (chimanet)


Note* There is a Wikipedia page for use cases at http://en.wikipedia.org/wiki/Use_case.

Video from ABC news at http://abcnews.go.com/International/video/leopard-attack-11-villagers-mauled-14123443

We can start working on the use cases. There is a Wikipedia page for use cases at http://en.wikipedia.org/wiki/Use_case.

I will come up with a task list later today, but feel free to volunteer for any use case that catches your fancy.

Some sample use case diagrams I found online are attached.