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.

Hw abt adding an online clinic management software to the project list,see what uu can domam also interested in learning programming

I like to learn programming pls kindly inform the cost and duration 08065851980

KALMA-1:

I like to learn programming pls kindly inform the cost and duration 08065851980

Wrong thread

josho1:

Hw abt adding an online clinic management software to the project list,see what uu can domam also interested in learning programming

You sure you know what project we are working on?

wassolldas:

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.

I use sqlite with Ruby on Rails. I still would advice we stick to MySql.Just for standards!

Okey, Am kinda lost here!!!

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;

The ER diagram used to generate the above script.

dd u really have to write out those statements when you could just use phpmyadmin ?

Back to the problem. I think  MySql is unable to create tables having a foreign key on a table that is also being used as a foreign key in different table. Aint gonna read dat stuff so check if you are doing that! I solved this for someone on a forum and now i cant even remember the forum. Am sure it has to do with foreign keys tho,

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;

Pretty cool guyz, I'm impressed.
@Fayimora I made a post about a week ago, making a suggestion on the value object proposed by maybe wassoldas, I'm not really sure and I can't find it. 'nyway, I can see we are working from back to middle to front, do we have any back-end interface on ground for guyz to implement? 'coz I see many indicating that they want to code and they may not want to get their hands dirty, planting these SQL seeds.

hi man! i am interested. i knw java to some certain xtent. my mobile: 07069500520

thank you

@Fayimora:
From the outset of this project, you said it was meant to help us build up on our skills more but don't see that happening,
I have been following from inception but it looks like you are just doing pro work here!!!

Well, when we started it was just me, wassolldas dat were available, Still wanna blame me for that? Anyways as you can see we havent gona dat far and no its not pro work, wasso gave some people duties and i think only one responded.

@Fayimora:
That was me!!!

Yope! Just realised that! hehehe so why are you complaining? You are still on the team? Wasso just decided to handle the sql stuffs The diagrams are here and are open to criticism, if you dnt like something, change it .

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

-- 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);

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.

i join nairaland because of u FAYIMORA ur not just a programmer but a committed one ur always willing to help people irrespetective of there knowledge am proud of u and u make me proud of Nigeria i dont know y ur so unique maybe its because u dont live in Nigeria but ma brother that am using his laptop now also lives in london but very stingy ur true example of leader i wish i could join ur team but am novice am not a begginer because av not start at all not because i dont want to but helpless i av passion for programming not because of money but the challenge hope someday i can be like u guys for the rest Nigerian learn from the leader because Nigeria is goin no where untill we cab identify a leader from a ruler and the leader is wat we need keep coding am proud of u all

you guys are doing great.

xsammyx:

i join nairaland because of u FAYIMORA your not just a programmer but a committed one your always willing to help people irrespetective of there knowledge am proud of u and u make me proud of Nigeria i dont know y your so unique maybe its because u dont live in Nigeria but ma brother that am using his laptop now also lives in london but very stingy your true example of leader i wish i could join your team but am novice am not a begginer because av not start at all not because i dont want to but helpless i av passion for programming not because of money but the challenge hope someday i can be like u guys for the rest Nigerian learn from the leader because Nigeria is goin no where untill we cab identify a leader from a ruler and the leader is wat we need keep coding am proud of u all

WOW! Thanks a lot. Never knew anyone could say this to me on NL. Anyways your free to contribute, Its opensource, you don't have to know how to code. You can just contribute, lol

Hmmmm, i really love dis dude name fayimora.
After i finish my computer science course in d uni, i will love to b a programmer.
Hope u've gat book for java?because i will love to use one, cus i love java so dearly.
Long live fayimora.Bye

Barrywhite8620:

Hmmmm, i really love dis dude name fayimora.

Thanks!

Barrywhite8620:

After i finish my computer science  course in d uni, i will love to b a programmer.
Hope u've gat book for java?because i will love to use one, cus i love java so dearly.

You dont have to finish your course! Get on it. .

Barrywhite8620:

Long live fayimora.Bye

Amen! lol thanks mate. .

would be very interested in programming. Experienced freelance programmer. would be available throughout the lifespan of the project.

I know java to some certain extent. with a little knowledge of gwt for web applications. jst want to be clear if its going to be a web application or jst a desktop application. but in any case i'm in as a designer. though not good in that but since I also get the chance to code. then Im in
my email is: talktomko4life@yahoo.co.uk

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.

When is dis whole sequel thing going to end?

End? Nt soon but for now i would move us back to the UML and we start coding!

Yeah! The whole thing is about to get a little more interesting. Errmm, Do you guys use GUI tools to generate sql statements at all?

SayoMarvel:

When is dis whole sequel thing going to end?

Gud question ooo,

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,

@wassolldas
Y don't u implement some reverse engineering techniques so that u can use ORM tools as against these buggy SQL scripts. Life will b a lot more easier 4 u believe me