web based in php and js
SET SQL_MODE = “NO_AUTO_VALUE_ON_ZERO”;
SET time_zone = “+00:00”;
— Customers Table
CREATE TABLE IF NOT EXISTS customers (
acc_number INT AUTO_INCREMENT PRIMARY KEY,
fname VARCHAR(255) NOT NULL,
lname VARCHAR(255) NOT NULL,
email VARCHAR(255) NOT NULL,
sex VARCHAR(10) NOT NULL,
age INT NOT NULL,
phone VARCHAR(20) NOT NULL,
image_path VARCHAR(2000) NOT NULL,
branch_name VARCHAR(33) NOT NULL,
pass VARCHAR(255) NOT NULL,
balance DECIMAL(10, 2) DEFAULT 0.00,
strt_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
— Employee Table
CREATE TABLE IF NOT EXISTS employee (
emp_id INT AUTO_INCREMENT PRIMARY KEY,
fname VARCHAR(255) NOT NULL,
lname VARCHAR(255) NOT NULL,
emp_username VARCHAR(255) UNIQUE NOT NULL,
email VARCHAR(255) NOT NULL,
pass VARCHAR(255) NOT NULL,
sex VARCHAR(10) NOT NULL,
age INT NOT NULL,
salary DECIMAL(10, 2) NOT NULL,
phone VARCHAR(20) NOT NULL,
files VARCHAR(2000) NOT NULL,
hire_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
— Admin Table
CREATE TABLE IF NOT EXISTS e_admins (
admin_id INT AUTO_INCREMENT PRIMARY KEY,
fname VARCHAR(255) NOT NULL,
lname VARCHAR(255) NOT NULL,
admin_username VARCHAR(255) UNIQUE NOT NULL,
email VARCHAR(255) NOT NULL,
pass VARCHAR(255) NOT NULL,
sex VARCHAR(10) NOT NULL,
age INT NOT NULL,
phone VARCHAR(20) NOT NULL,
files VARCHAR(2000) NOT NULL,
hire_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
— Branch Admin Table
CREATE TABLE IF NOT EXISTS branchs_admins (
branch_id INT AUTO_INCREMENT PRIMARY KEY,
fname VARCHAR(255) NOT NULL,
lname VARCHAR(255) NOT NULL,
b_username VARCHAR(255) UNIQUE NOT NULL,
sex VARCHAR(10) NOT NULL,
age INT NOT NULL,
b_email VARCHAR(255) UNIQUE NOT NULL,
pass VARCHAR(255) NOT NULL,
phone VARCHAR(255) NOT NULL,
files VARCHAR(255) NOT NULL,
hire_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
— Super Admin Table
CREATE TABLE IF NOT EXISTS super_admin (
sup_id INT AUTO_INCREMENT PRIMARY KEY,
fname VARCHAR(255) NOT NULL,
lname VARCHAR(255) NOT NULL,
sup_username VARCHAR(255) UNIQUE NOT NULL,
sex VARCHAR(10) NOT NULL,
age INT NOT NULL,
sup_email VARCHAR(255) UNIQUE NOT NULL,
pass VARCHAR(255) NOT NULL,
phone VARCHAR(255) NOT NULL,
files VARCHAR(255) NOT NULL,
hire_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
— Saving Table
CREATE TABLE IF NOT EXISTS saving (
save_id INT AUTO_INCREMENT PRIMARY KEY,
customer_acc INT NOT NULL,
save_amount DECIMAL(10,2) NOT NULL,
save_interest DECIMAL(5,2) NOT NULL,
interest_year VARCHAR(100) NOT NULL,
strt_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (customer_acc) REFERENCES customers(acc_number)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
ALTER TABLE customers
MODIFY acc_number INT NOT NULL;
ALTER TABLE saving
MODIFY customer_acc VARCHAR(20) NOT NULL,
DROP FOREIGN KEY saving_ibfk_1,
ADD FOREIGN KEY (customer_acc)
REFERENCES customers(acc_number);
— Loan Table
CREATE TABLE IF NOT EXISTS loan (
l_id INT AUTO_INCREMENT PRIMARY KEY,
customer_acc INT NOT NULL,
loan_status ENUM(‘pending’, ‘accepted’, ‘completed’, ‘canceled’) DEFAULT ‘pending’,
loan_amount DECIMAL(10,2) NOT NULL,
loan_interest DECIMAL(5,2) NOT NULL,
files VARCHAR(3000) NOT NULL,
strt_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (customer_acc) REFERENCES customers(acc_number)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
— Loan Payment Table
CREATE TABLE IF NOT EXISTS loan_pay (
p_id INT AUTO_INCREMENT PRIMARY KEY,
loan_id INT NOT NULL,
customer_acc INT NOT NULL,
loan_status ENUM(‘pending’, ‘accepted’, ‘completed’, ‘canceled’) DEFAULT ‘pending’,
loan_amount DECIMAL(10,2) NOT NULL,
loan_interest DECIMAL(5,2) NOT NULL,
strt_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (loan_id) REFERENCES loan(l_id),
FOREIGN KEY (customer_acc) REFERENCES customers(acc_number)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
— Transactions Table
CREATE TABLE IF NOT EXISTS transactions (
t_id INT AUTO_INCREMENT PRIMARY KEY,
customer_acc INT NOT NULL,
t_type ENUM(‘deposit’, ‘withdraw’) NOT NULL,
amount DECIMAL(10, 2) NOT NULL,
t_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (customer_acc) REFERENCES customers(acc_number)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
— Other Bank Transactions Table
CREATE TABLE IF NOT EXISTS transaction_otherbanks (
t_id INT AUTO_INCREMENT PRIMARY KEY,
customer_acc INT NOT NULL,
from_bank ENUM(‘CBE’,’BOA’,’Zemen’,’otherbanks’) NOT NULL,
t_type ENUM(‘receive’, ‘send’) NOT NULL,
amount DECIMAL(10, 2) NOT NULL,
t_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (customer_acc) REFERENCES customers(acc_number)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
— Feedback Table
CREATE TABLE IF NOT EXISTS feedback (
fe_id INT AUTO_INCREMENT PRIMARY KEY,
for_role ENUM(‘customers’, ’employee’, ‘e_admins’, ‘branchs_admins’, ‘super_admin’) NOT NULL,
txts VARCHAR(2000) NOT NULL,
feedback ENUM(‘suggestion’, ‘complaint’) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
— Contact Table
CREATE TABLE IF NOT EXISTS contact (
ct_id INT AUTO_INCREMENT PRIMARY KEY,
customer_acc INT NOT NULL,
contact_type ENUM(’email’, ‘call’) NOT NULL,
FOREIGN KEY (customer_acc) REFERENCES customers(acc_number)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;