Constructing A Data Model And Implementing It In MySQL: A Practical Guide
Constructing a Data Model
From the dependency diagram shown above there are different types of dependencies in the table;
- Partial dependency
- AccountNumber,BSBàaccountBalance, customerID, branchID, accountTypeTransitive dependency
- customerIDàcustomerName
- branchIDàbranchName
- AccountTypeàAccountName
Thus eliminating the partial and transitive dependencies results to the following tables;
Account (accountNumber, BSB, accountBalance, customerID, branchID, accountType)
Customer( CustomerID, customerName)
Branch( branchID, branchName)
Account_type ( AccountType, accountName)– MySQL Workbench Forward Engineering
SET @[email protected]@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @[email protected]@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @[email protected]@SQL_MODE, SQL_MODE=’TRADITIONAL,ALLOW_INVALID_DATES’;
CREATE TABLE IF NOT EXISTS `spock_bank`.`account_type` (
`accountTypeID` VARCHAR(10) NOT NULL,
`name` VARCHAR(50) NOT NULL,
`description` VARCHAR(500) NOT NULL,
`interestRate` DECIMAL(10,0) NOT NULL,
`yearlyServiceFee` DECIMAL(10,0) NOT NULL,
PRIMARY KEY (`accountTypeID`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1;
CREATE TABLE IF NOT EXISTS `spock_bank`.`account` (
`accountNO` VARCHAR(10) NOT NULL,
`BSB` VARCHAR(10) NOT NULL,
`accountTypeID` VARCHAR(10) NOT NULL,
`balance` DECIMAL(10,0) NOT NULL,
PRIMARY KEY (`accountNO`, `BSB`),
INDEX `accountTypeID` (`accountTypeID` ASC),
CONSTRAINT `account_ibfk_1`
FOREIGN KEY (`accountTypeID`)
REFERENCES `spock_bank`.`account_type` (`accountTypeID`)
ON UPDATE CASCADE)
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1;
CREATE TABLE IF NOT EXISTS `spock_bank`.`customer` (
`customerID` VARCHAR(10) NOT NULL,
`name` VARCHAR(50) NOT NULL,
`address` VARCHAR(100) NOT NULL,
`contactNO` VARCHAR(25) NOT NULL,
`email` VARCHAR(100) NOT NULL,
`joinDate` DATE NOT NULL,
PRIMARY KEY (`customerID`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1;
CREATE TABLE IF NOT EXISTS `spock_bank`.`account_holders` (
`accountNO` VARCHAR(10) NOT NULL,
`customerID` VARCHAR(10) NOT NULL,
INDEX `accountNO` (`accountNO` ASC, `customerID` ASC),
INDEX `customerID` (`customerID` ASC),
CONSTRAINT `account_holders_ibfk_1`
FOREIGN KEY (`accountNO`)
REFERENCES `spock_bank`.`account` (`accountNO`)
ON UPDATE CASCADE,
CONSTRAINT `account_holders_ibfk_2`
FOREIGN KEY (`customerID`)
REFERENCES `spock_bank`.`customer` (`customerID`)
ON UPDATE CASCADE)
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1;
CREATE TABLE IF NOT EXISTS `spock_bank`.`atm` (
`atmID` VARCHAR(10) NOT NULL,
`location` VARCHAR(50) NOT NULL,
`openingHrs` VARCHAR(50) NOT NULL,
`maxCashCap` DECIMAL(10,0) NOT NULL,
PRIMARY KEY (`atmID`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1;
CREATE TABLE IF NOT EXISTS `spock_bank`.`branch` (
`branchID` VARCHAR(10) NOT NULL,
`name` VARCHAR(50) NOT NULL,
`address` VARCHAR(100) NOT NULL,
`phoneNO` VARCHAR(25) NOT NULL,
PRIMARY KEY (`branchID`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1;
CREATE TABLE IF NOT EXISTS `spock_bank`.`debit_card` (
`cardNumber` VARCHAR(10) NOT NULL,
`expiryDate` VARCHAR(10) NOT NULL,
`cvvNumber` INT(11) NOT NULL,
`pinNumber` INT(11) NOT NULL,
`accountNO` VARCHAR(10) NOT NULL,
PRIMARY KEY (`cardNumber`),
INDEX `accountNO` (`accountNO` ASC),
CONSTRAINT `debit_card_ibfk_1`
FOREIGN KEY (`accountNO`)
REFERENCES `spock_bank`.`account` (`accountNO`)
ON UPDATE CASCADE)
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1;
CREATE TABLE IF NOT EXISTS `spock_bank`.`department` (
`departmentID` VARCHAR(10) NOT NULL,
`name` VARCHAR(50) NOT NULL,
`branchID` VARCHAR(10) NOT NULL,
PRIMARY KEY (`departmentID`),
INDEX `branchID` (`branchID` ASC),
CONSTRAINT `department_ibfk_1`
FOREIGN KEY (`branchID`)
REFERENCES `spock_bank`.`branch` (`branchID`)
ON UPDATE CASCADE)
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1;
CREATE TABLE IF NOT EXISTS `spock_bank`.`employee` (
`employeeID` VARCHAR(10) NOT NULL,
`name` VARCHAR(50) NOT NULL,
`address` VARCHAR(100) NOT NULL,
`phoneNO` VARCHAR(25) NOT NULL,
`position` VARCHAR(50) NOT NULL,
`annualSalary` DECIMAL(10,0) NOT NULL,
`departmentD` VARCHAR(10) NOT NULL,
PRIMARY KEY (`employeeID`),
INDEX `departmentD` (`departmentD` ASC),
CONSTRAINT `employee_ibfk_1`
FOREIGN KEY (`departmentD`)
REFERENCES `spock_bank`.`department` (`departmentID`)
ON UPDATE CASCADE)
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1;
CREATE TABLE IF NOT EXISTS `spock_bank`.`department_manager` (
`departmentID` VARCHAR(10) NOT NULL,
`employeeID` VARCHAR(10) NOT NULL,
`startDate` DATE NOT NULL,
`endDate` DATE NOT NULL,
PRIMARY KEY (`departmentID`, `employeeID`, `startDate`),
INDEX `employeeID` (`employeeID` ASC),
CONSTRAINT `department_manager_ibfk_1`
FOREIGN KEY (`departmentID`)
REFERENCES `spock_bank`.`department` (`departmentID`)
ON UPDATE CASCADE,
CONSTRAINT `department_manager_ibfk_2`
FOREIGN KEY (`employeeID`)
REFERENCES `spock_bank`.`employee` (`employeeID`)
ON UPDATE CASCADE)
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1;
CREATE TABLE IF NOT EXISTS `spock_bank`.`loan_type` (
`loanTypeID` VARCHAR(10) NOT NULL,
`name` VARCHAR(50) NOT NULL,
`dscription` VARCHAR(500) NOT NULL,
PRIMARY KEY (`loanTypeID`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1;
CREATE TABLE IF NOT EXISTS `spock_bank`.`loan` (
`loanID` VARCHAR(10) NOT NULL,
`loanTypeID` VARCHAR(10) NOT NULL,
`interestRate` DECIMAL(10,0) NOT NULL,
`totalAmount` DECIMAL(10,0) NOT NULL,
`balance` DECIMAL(10,0) NOT NULL,
`accountNO` VARCHAR(10) NOT NULL,
PRIMARY KEY (`loanID`),
INDEX `loanTypeID` (`loanTypeID` ASC),
INDEX `accountNO` (`accountNO` ASC),
CONSTRAINT `loan_ibfk_1`
FOREIGN KEY (`loanTypeID`)
REFERENCES `spock_bank`.`loan_type` (`loanTypeID`)
ON UPDATE CASCADE,
CONSTRAINT `loan_ibfk_2`
FOREIGN KEY (`accountNO`)
REFERENCES `spock_bank`.`account` (`accountNO`)
ON UPDATE CASCADE)
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1;
CREATE TABLE IF NOT EXISTS `spock_bank`.`loan_repayment` (
`repaymentNO` VARCHAR(10) NOT NULL,
`loanID` VARCHAR(10) NOT NULL,
`amount` DECIMAL(10,0) NOT NULL,
`repaymentTime` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`employeeID` VARCHAR(10) NOT NULL,
PRIMARY KEY (`repaymentNO`),
INDEX `loanID` (`loanID` ASC, `employeeID` ASC),
INDEX `employeeID` (`employeeID` ASC),
CONSTRAINT `loan_repayment_ibfk_1`
FOREIGN KEY (`loanID`)
REFERENCES `spock_bank`.`loan` (`loanID`)
ON UPDATE CASCADE,
CONSTRAINT `loan_repayment_ibfk_2`
FOREIGN KEY (`employeeID`)
REFERENCES `spock_bank`.`employee` (`employeeID`)
ON UPDATE CASCADE)
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1;
CREATE TABLE IF NOT EXISTS `spock_bank`.`withdrawal` (
`withdrawalNO` VARCHAR(10) NOT NULL,
`atmID` VARCHAR(10) NOT NULL,
`cardNumber` VARCHAR(10) NOT NULL,
`dateTime` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`amount` DECIMAL(10,0) NOT NULL,
PRIMARY KEY (`withdrawalNO`),
INDEX `atmID` (`atmID` ASC, `cardNumber` ASC),
INDEX `cardNumber` (`cardNumber` ASC),
CONSTRAINT `withdrawal_ibfk_1`
FOREIGN KEY (`atmID`)
REFERENCES `spock_bank`.`atm` (`atmID`)
ON UPDATE CASCADE,
CONSTRAINT `withdrawal_ibfk_2`
FOREIGN KEY (`cardNumber`)
REFERENCES `spock_bank`.`debit_card` (`cardNumber`)
ON UPDATE CASCADE)
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1;
SET [email protected]_SQL_MODE;
SET [email protected]_FOREIGN_KEY_CHECKS;
SET [email protected]_UNIQUE_CHECKS;
INSERT INTO `spock_bank`.`customer` (`customerID`, `name`, `address`, `contactNO`, `email`, `joinDate`) VALUES (‘C001’, ‘Jon Snow’, ‘winterfell’, ‘+ 234 3434 34434’, ‘[email protected]‘, ‘2018-05-08’), (‘C002’, ‘Arya Stark’, ‘winterfell av’, ‘+34 34 34343’, ‘[email protected]‘,’2018-07-01’), (‘C003’, ‘Cersei lannister’, ’12 th street kings landing’, ‘+4 343423 32’, ‘[email protected]‘, ‘2018-07-02’), (‘C004’, ‘Geoffrey Baratheon’, ‘kings landing drive’, ‘+33 343423 23’, ‘[email protected]‘, ‘2018-07-01’);
INSERT INTO `spock_bank`.`branch` (`branchID`, `name`, `address`, `phoneNO`) VALUES (‘Branch1’, ‘branch one’, ‘kings landing’, ‘+ 43 32332 3323’), (‘Branch2’, ‘branch two’, ‘winterfell’, ‘+ 3434 343434’), (‘Branch3’, ‘branch three’, ‘winterfell’, ‘+34434 433’);
INSERT INTO `spock_bank`.`account_type` (`accountTypeID`, `name`, `description`, `interestRate`, `yearlyServiceFee`) VALUES (‘SAV’, ‘Savings’, ‘savings account’, ‘5.99’, ‘200’), (‘CHECK’, ‘checking ‘, ‘checking account’, ‘4.23’, ‘500’), (‘IRA’, ‘Retirement’, ‘retirement account’, ‘5.23’, ‘400’);
INSERT INTO `spock_bank`.`department` (`departmentID`, `name`, `branchID`) VALUES (‘depart1’, ‘hr’, ‘Branch1’), (‘depart2’, ‘IT’, ‘Branch2’), (‘depart3’, ‘Sales’, ‘Branch3’);
INSERT INTO `spock_bank`.`employee` (`employeeID`, `name`, `address`, `phoneNO`, `position`, `annualSalary`, `departmentD`) VALUES (‘EMP1’, ‘Peter Griffin’, ‘rhode island’, ‘+234 4344 3434’, ‘HR department manager’, ‘243434’, ‘depart1’), (‘EMP2’, ‘Meg girffin’, ‘rhode island’, ‘+4 34 343434’, ‘Technical lead’, ‘232332’, ‘depart2’), (‘EMP3’, ‘Lois Griffin’, ‘Rhode island’, ‘+34 3434 43344’, ‘Sales Department manager’, ‘232242’, ‘depart3’);
INSERT INTO `spock_bank`.`loan_type` (`loanTypeID`, `name`, `dscription`) VALUES (‘PER’, ‘personal’, ‘personal loan’), (‘FIX’, ‘fixed’, ‘fixed interest loan’), (‘RED’, ‘reducing’, ‘reducing balance loan’);
INSERT INTO `spock_bank`.`account` (`accountNO`, `BSB`, `accountTypeID`, `balance`) VALUES (‘123321’, ‘456654’, ‘CHECK’, ‘343’), (‘321123’, ‘654456’, ‘IRA’, ‘433’), (‘789987’, ‘987789’, ‘SAV’, ’43’);
INSERT INTO `spock_bank`.`account_holders` (`accountNO`, `customerID`) VALUES (‘123321’, ‘C001’), (‘123321’, ‘C002’), (‘321123’, ‘C002’), (‘321123’, ‘C003’), (‘789987’, ‘C004’);
INSERT INTO `spock_bank`.`atm` (`atmID`, `location`, `openingHrs`, `maxCashCap`) VALUES (‘ATM1’, ‘winterfell’, ’08:00-16:00′, ‘500000’), (‘ATM2’, ‘kings landing’, ’08:00-16:00′, ‘500000’), (‘ATM3’, ‘winterfell’, ’08:00-16:00′, ‘500000’);
INSERT INTO `spock_bank`.`debit_card` (`cardNumber`, `expiryDate`, `cvvNumber`, `pinNumber`, `accountNO`) VALUES (‘12345678′, ’12/2020’, ‘555’, ‘2343’, ‘123321’), (‘87654321′, ’08/2019’, ‘545’, ‘4321’, ‘321123’), (‘12344321′, ’09/2019’, ‘432’, ‘6543’, ‘789987’);
INSERT INTO `spock_bank`.`department_manager` (`departmentID`, `employeeID`, `startDate`, `endDate`) VALUES (‘depart1’, ‘EMP1’, ‘2018-05-01’, ‘2018-09-29’), (‘depart2’, ‘EMP2’, ‘2018-04-08’, ‘2018-09-21’), (‘depart3’, ‘EMP3’, ‘2018-04-08’, ‘2018-09-21’);
INSERT INTO `spock_bank`.`loan` (`loanID`, `loanTypeID`, `interestRate`, `totalAmount`, `balance`, `accountNO`) VALUES (‘L001’, ‘FIX’, ’12’, ‘42342’, ‘2342’, ‘123321’), (‘L002’, ‘PER’, ’23’, ‘434223’, ‘2322’, ‘321123’), (‘L003’, ‘RED’, ‘5.23’, ‘232432’, ‘23234’, ‘789987’);
INSERT INTO `spock_bank`.`loan_repayment` (`repaymentNO`, `loanID`, `amount`, `repaymentTime`, `employeeID`) VALUES (‘R001’, ‘L001’, ‘2343’, CURRENT_TIMESTAMP, ‘EMP1’), (‘R002’, ‘L002’, ‘2333’, CURRENT_TIMESTAMP, ‘EMP2’), (‘R003’, ‘L003’, ‘2432’, CURRENT_TIMESTAMP, ‘EMP3’);
INSERT INTO `spock_bank`.`withdrawal` (`withdrawalNO`, `atmID`, `cardNumber`, `dateTime`, `amount`) VALUES (‘W001’, ‘ATM1’, ‘12344321’, CURRENT_TIMESTAMP, ‘234’), (‘W002’, ‘ATM2’, ‘12345678’, CURRENT_TIMESTAMP, ‘134’), (‘W003’, ‘ATM3’, ‘87654321’, CURRENT_TIMESTAMP, ‘234’); accountNO
FROM
account_holders
GROUP BY accountNO
HAVING COUNT(accountNO) > 1); FROM
loan
INNER JOIN
loan_type ON loan.loanTypeID = loan_type.loanTypeID
WHERE
loan_type.name = ‘personal’);c)
SELECT
department.name, branch.name, employee.name
FROM
department
INNER JOIN
branch ON branch.branchID = department.branchID
INNER JOIN
department_manager ON department_manager.departmentID = department.departmentID
INNER JOIN
employee ON employee.employeeID = department_manager.employeeID;
SELECT
department.name, branch.name, employee.name
FROM
department
INNER JOIN
branch ON branch.branchID = department.branchID
INNER JOIN
department_manager ON department_manager.departmentID = department.departmentID
INNER JOIN
employee ON employee.employeeID = department_manager.employeeID;