From the dependency diagram shown above there are different types of dependencies in the table;
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;
Essay Writing Service Features
Our Experience
No matter how complex your assignment is, we can find the right professional for your specific task. Contact Essay is an essay writing company that hires only the smartest minds to help you with your projects. Our expertise allows us to provide students with high-quality academic writing, editing & proofreading services.
Free Features
Free revision policy
$10Free bibliography & reference
$8Free title page
$8Free formatting
$8How Our Essay Writing Service Works
First, you will need to complete an order form. It's not difficult but, in case there is anything you find not to be clear, you may always call us so that we can guide you through it. On the order form, you will need to include some basic information concerning your order: subject, topic, number of pages, etc. We also encourage our clients to upload any relevant information or sources that will help.
Complete the order form
Once we have all the information and instructions that we need, we select the most suitable writer for your assignment. While everything seems to be clear, the writer, who has complete knowledge of the subject, may need clarification from you. It is at that point that you would receive a call or email from us.
Writer’s assignment
As soon as the writer has finished, it will be delivered both to the website and to your email address so that you will not miss it. If your deadline is close at hand, we will place a call to you to make sure that you receive the paper on time.
Completing the order and download