API tools faq
paste
Advertisement
itkhansunny

MySql Bank Account System

itkhansunny
May 6th, 2021
1,621
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
MySQL 12.08 KB | None | 0 0
raw download clone embed print report
  1. DROP TABLE IF EXISTS Customer ;
  2. DROP TABLE IF EXISTS Employee ;
  3. DROP TABLE IF EXISTS Insurance ;
  4. DROP TABLE IF EXISTS Loans ;
  5. DROP TABLE IF EXISTS Branch ;
  6. DROP TABLE IF EXISTS Bank ;
  7. DROP TABLE IF EXISTS Account ;
  8. DROP TABLE IF EXISTS Customer_account_info ;
  9.  
  10. CREATE TABLE IF NOT EXISTS Customer (c_name varchar(20) NOT NULL,
  11.             c_id varchar(20) PRIMARY KEY,
  12.             c_acc_number varchar(20) NOT NULL,
  13.             c_dob varchar(20),
  14.             c_pan_no varchar(20),
  15.             c_address varchar(100) DEFAULT 'Bangladesh');
  16.  
  17. CREATE TABLE IF NOT EXISTS Employee(e_name varchar(20) NOT NULL,
  18.             e_type varchar(100),
  19.             e_salary integer,
  20.             e_emp_id varchar(20) PRIMARY KEY,
  21.              
  22.             CHECK(e_salary>0));
  23.  
  24. CREATE TABLE IF NOT EXISTS Insurance(i_issuing_company varchar(20)  DEFAULT 'Standard Life',
  25.              i_type varchar(100),
  26.             i_premium_pay integer,
  27.             i_term integer,
  28.             i_policy_no varchar(10) PRIMARY KEY,
  29.             i_custo_id varchar(20),
  30.             CHECK(i_premium_pay>0));
  31.  
  32. CREATE TABLE IF NOT EXISTS Loans(l_account_no varchar(20) PRIMARY KEY,
  33.             l_type varchar(100),
  34.             l_amount integer,
  35.             l_term integer,
  36.             l_rate integer DEFAULT 6,
  37.             l_cust_id varchar(20),
  38.             CHECK(amount>0));
  39.  
  40.  
  41. CREATE TABLE IF NOT EXISTS Branch(b_id varchar(10) PRIMARY KEY,
  42.             b_type varchar(100),
  43.             b_location varchar(40) NOT NULL,
  44.             b_bank_name varchar(20));
  45.  
  46.  
  47. CREATE TABLE IF NOT EXISTS Bank(bnk_name varchar(20),
  48.             bnk_type varchar(20),
  49.             bnk_head_office varchar(20),
  50.             bnk_branch_id varchar(20) PRIMARY KEY);
  51.  
  52.  
  53. CREATE TABLE IF NOT EXISTS Account(accnt_balance integer,
  54.             accnt_acc_number varchar(40),
  55.             accnt_overdraft integer,
  56.             accnt_customerid varchar(20) PRIMARY KEY);
  57.  
  58. CREATE TABLE IF NOT EXISTS Customer_account_info(c_name varchar(20),
  59.                 customer_id varchar(20),
  60.                 acc_num varchar(40),
  61.                 c_dob varchar(20),
  62.                 c_phn_no varchar(20),
  63.                 c_address varchar(100),
  64.                 acc_balance integer,
  65.                 /*acc_overdraft integer, */
  66.                 card_no varchar(20));
  67.  
  68.  
  69.  
  70. /* For account number, it is a 13 digit number where first 3 digits are branch id, next 3 numbers are type of account(savings-100, current-200), next two numbers are just 00, last 4 numbers are the unique customer id, so follow the same.
  71. for pan number */
  72.  
  73. INSERT INTO `customer`(`c_name`, `c_id`, `c_acc_number`, `c_dob`, `c_pan_no`, `c_address`) values('Redowan','001','056 100 00 10001','12-05-1988','10000','mymensingh,bangladesh');
  74. INSERT INTO `customer`(`c_name`, `c_id`, `c_acc_number`, `c_dob`, `c_pan_no`, `c_address`) values('Arefin','002','057 200 00 10002','02-09-1990', '10001','Borisal,bangladesh');
  75. INSERT INTO `customer`(`c_name`, `c_id`, `c_acc_number`, `c_dob`, `c_pan_no`, `c_address`) values('Tanim','003','058 100 00 10003','09-12-1984',NULL,'Nuyakali,Bangladesh');
  76. INSERT INTO `customer`(`c_name`, `c_id`, `c_acc_number`, `c_dob`, `c_pan_no`, `c_address`) values('Adnan Farabi','004','059 200 00 004','31-05-1998','10002','Dhaka,Bangladesh');
  77. INSERT INTO `customer`(`c_name`, `c_id`, `c_acc_number`, `c_dob`, `c_pan_no`, `c_address`) values('Borhan','005','060 200 00 10005','15-05-1988',NULL,'Borisal,Bangladesh');
  78. INSERT INTO `customer`(`c_name`, `c_id`, `c_acc_number`, `c_dob`, `c_pan_no`, `c_address`) values('Borhani Mia','006','070 100 00 10006','19-06-1965',NULL,'Nuyakali, Bangladesh');
  79. INSERT INTO `customer`(`c_name`, `c_id`, `c_acc_number`, `c_dob`, `c_pan_no`, `c_address`) values('Rasel','007','080 100 00 10007','12-06-1996','10003','Borisal,Bangladesh');
  80. INSERT INTO `customer`(`c_name`, `c_id`, `c_acc_number`, `c_dob`, `c_pan_no`, `c_address`) values('babu','008','090 200 00 10008','02-12-1989','10004','mymensingh,Bangladesh');
  81.  
  82.  
  83.  
  84.  
  85.  
  86.  
  87. INSERT INTO `employee`(`e_name`, `e_type`, `e_salary`, `e_emp_id`) values('Ridoy khan','Manager',60000,'001');
  88. INSERT INTO `employee`(`e_name`, `e_type`, `e_salary`, `e_emp_id`) values('Arefin Tanim','Vice President',150000,'002');
  89. INSERT INTO `employee`(`e_name`, `e_type`, `e_salary`, `e_emp_id`) values('Borhan Uddin','Assistant Vice President',100000,'006');
  90. INSERT INTO `employee`(`e_name`, `e_type`, `e_salary`, `e_emp_id`) values('babu rasel','Officer',40000,'003');
  91. INSERT INTO `employee`(`e_name`, `e_type`, `e_salary`, `e_emp_id`) values('Ahammed Sh','Manager',60000,'007');
  92. INSERT INTO `employee`(`e_name`, `e_type`, `e_salary`, `e_emp_id`) values('Sajal','Clerk',10000,'008');
  93. INSERT INTO `employee`(`e_name`, `e_type`, `e_salary`, `e_emp_id`) values('Mark zuker','Officer',40000,'009');
  94.  
  95.  
  96.  
  97.  
  98.  
  99. /* type - health,life,motor,car life,Home loan,LIC
  100. issuing company -  */
  101. INSERT INTO `insurance`(`i_issuing_company`, `i_type`, `i_premium_pay`, `i_term`, `i_policy_no`, `i_custo_id`) values('Standard Life','Health',2000,5,'001','003');
  102. INSERT INTO `insurance`(`i_issuing_company`, `i_type`, `i_premium_pay`, `i_term`, `i_policy_no`, `i_custo_id`) values('LIFC','Health',5000,4,'002','005');
  103. INSERT INTO `insurance`(`i_issuing_company`, `i_type`, `i_premium_pay`, `i_term`, `i_policy_no`, `i_custo_id`) values('Good Life','Motor',2800,3,'003','006');
  104. INSERT INTO `insurance`(`i_issuing_company`, `i_type`, `i_premium_pay`, `i_term`, `i_policy_no`, `i_custo_id`) values('Money on Money','Life',8000,5,'004','004');
  105. INSERT INTO `insurance`(`i_issuing_company`, `i_type`, `i_premium_pay`, `i_term`, `i_policy_no`, `i_custo_id`) values('Car Life','Motor',1000,1,'005','003');
  106. INSERT INTO `insurance`(`i_issuing_company`, `i_type`, `i_premium_pay`, `i_term`, `i_policy_no`, `i_custo_id`) values('Healty Life','Health',6000,7,'006','002');
  107. INSERT INTO `insurance`(`i_issuing_company`, `i_type`, `i_premium_pay`, `i_term`, `i_policy_no`, `i_custo_id`) values('Home Loan','Home',5500,1,'007','001');
  108. INSERT INTO `insurance`(`i_issuing_company`, `i_type`, `i_premium_pay`, `i_term`, `i_policy_no`, `i_custo_id`) values('LIC','Life',4000,5,'009','007');
  109.  
  110.  
  111.  
  112.  
  113.  
  114.  
  115. INSERT INTO `loans`(`l_account_no`, `l_type`, `l_amount`, `l_term`, `l_rate`, `l_cust_id`) values('0001 200 00 10002','Home',20000,3,5,'002');
  116. INSERT INTO `loans`(`l_account_no`, `l_type`, `l_amount`, `l_term`, `l_rate`, `l_cust_id`) values('0001 100 00 10006','Business',10000,5,7,'006');
  117. INSERT INTO `loans`(`l_account_no`, `l_type`, `l_amount`, `l_term`, `l_rate`, `l_cust_id`) values('0005 100 00 10007','House',70000,7,3,'007');
  118. INSERT INTO `loans`(`l_account_no`, `l_type`, `l_amount`, `l_term`, `l_rate`, `l_cust_id`) values('0008 200 00 10008','House',500000,3,5,'008');
  119. INSERT INTO `loans`(`l_account_no`, `l_type`, `l_amount`, `l_term`, `l_rate`, `l_cust_id`) values('0001 100 00 10003','Equipment',50000,3,3,'003');
  120. INSERT INTO `loans`(`l_account_no`, `l_type`, `l_amount`, `l_term`, `l_rate`, `l_cust_id`) values('0001 100 00 1000','Business',300000,7,7,'001');
  121. INSERT INTO `loans`(`l_account_no`, `l_type`, `l_amount`, `l_term`, `l_rate`, `l_cust_id`) values('0001 100 00 10001','Business',500000,7,9,'001');
  122. INSERT INTO `loans`(`l_account_no`, `l_type`, `l_amount`, `l_term`, `l_rate`, `l_cust_id`) values('0003 200 00 10005','House',600000,5,2,'005');
  123.  
  124. INSERT INTO `branch`(`b_id`, `b_type`, `b_location`, `b_bank_name`) values('056','Urban','Mymensingh','Rupali Bank');
  125. INSERT INTO `branch`(`b_id`, `b_type`, `b_location`, `b_bank_name`) values('057','Urban','Dhaka','Sonali Bank');
  126. INSERT INTO `branch`(`b_id`, `b_type`, `b_location`, `b_bank_name`) values('058','Rural','Borisal','Agroni Bank');
  127. INSERT INTO `branch`(`b_id`, `b_type`, `b_location`, `b_bank_name`) values('059','Urban','Noyakali','Trust Bank');
  128. INSERT INTO `branch`(`b_id`, `b_type`, `b_location`, `b_bank_name`) values('060','Urban',' Mymensingh','Ab Bank');
  129. INSERT INTO `branch`(`b_id`, `b_type`, `b_location`, `b_bank_name`) values('070','Rural','Mymensingh','Bd Bank');
  130. INSERT INTO `branch`(`b_id`, `b_type`, `b_location`, `b_bank_name`) values('080','Urban','Dhaka','Bangla Bank');
  131. INSERT INTO `branch`(`b_id`, `b_type`, `b_location`, `b_bank_name`) values('090','Urban',' Dhaka ','Today Bank');
  132. INSERT INTO `branch`(`b_id`, `b_type`, `b_location`, `b_bank_name`) values('110','Urban','Noyakali','Union Bank');
  133.  
  134.  
  135.  
  136.  
  137.  
  138. INSERT INTO `bank`(`bnk_name`, `bnk_type`, `bnk_head_office`, `bnk_branch_id`) values('Rupali Bank','Commercial','Dhaka','056');
  139. INSERT INTO `bank`(`bnk_name`, `bnk_type`, `bnk_head_office`, `bnk_branch_id`) values('Sonali Bank','Retail','Dhaka','057');
  140. INSERT INTO `bank`(`bnk_name`, `bnk_type`, `bnk_head_office`, `bnk_branch_id`) values('Agroni Bank','Retail','Dhaka','058');
  141. INSERT INTO `bank`(`bnk_name`, `bnk_type`, `bnk_head_office`, `bnk_branch_id`) values('Trust Bank','Commercial','Dhaka','059');
  142. INSERT INTO `bank`(`bnk_name`, `bnk_type`, `bnk_head_office`, `bnk_branch_id`) values('Ab Bank','Commercial','Dhaka','060');
  143. INSERT INTO `bank`(`bnk_name`, `bnk_type`, `bnk_head_office`, `bnk_branch_id`) values('Bd Bank','Retail','Dhaka','070');
  144. INSERT INTO `bank`(`bnk_name`, `bnk_type`, `bnk_head_office`, `bnk_branch_id`) values('Bangla Bank','Retail','Dhaka','080');
  145. INSERT INTO `bank`(`bnk_name`, `bnk_type`, `bnk_head_office`, `bnk_branch_id`) values('Today Bank','Commercial','Dhaka','090');
  146.  
  147.  
  148.  
  149.  
  150.  
  151. INSERT INTO `account`(`accnt_balance`, `accnt_acc_number`, `accnt_overdraft`, `accnt_customerid`) values(50000,'0001 100 00 10001' , 4000, '001');
  152. INSERT INTO `account`(`accnt_balance`, `accnt_acc_number`, `accnt_overdraft`, `accnt_customerid`) values(10000,'0001 200 00 10002' , 4000, '002');
  153. INSERT INTO `account`(`accnt_balance`, `accnt_acc_number`, `accnt_overdraft`, `accnt_customerid`) values(500000,'0001 100 00 10003' , 4000, '003');
  154. INSERT INTO `account`(`accnt_balance`, `accnt_acc_number`, `accnt_overdraft`, `accnt_customerid`) values(7500,'0002 200 00 10004' , 4000, '004');
  155. INSERT INTO `account`(`accnt_balance`, `accnt_acc_number`, `accnt_overdraft`, `accnt_customerid`) values(11000,'0003 200 00 10005' , 4000, '005');
  156. INSERT INTO `account`(`accnt_balance`, `accnt_acc_number`, `accnt_overdraft`, `accnt_customerid`) values(9000,'0001 100 00 10006' , 4000, '006');
  157. INSERT INTO `account`(`accnt_balance`, `accnt_acc_number`, `accnt_overdraft`, `accnt_customerid`) values(800000,'0005 100 00 10007' , 4000, '007');
  158. INSERT INTO `account`(`accnt_balance`, `accnt_acc_number`, `accnt_overdraft`, `accnt_customerid`) values(150000,'0008 200 00 10008' , 4000, '008');
  159.  
  160.  
  161.  
  162. INSERT INTO `customer_account_info`(`c_name`, `customer_id`, `acc_num`, `c_dob`, `c_phn_no`, `c_address`, `acc_balance`, `card_no`) values('Redowan','001','056 100 00 10001','12-05-1988','+8801743577778','mymensingh,bangladesh',50000,'1234 5678 9012 3456');
  163. INSERT INTO `customer_account_info`(`c_name`, `customer_id`, `acc_num`, `c_dob`, `c_phn_no`, `c_address`, `acc_balance`, `card_no`) values('Arefin','002','057 200 00 10002','02-09-1990','+8801743577779', 'Borisal,bangladesh',100000,'1357 8024 5791 2468');
  164. INSERT INTO `customer_account_info`(`c_name`, `customer_id`, `acc_num`, `c_dob`, `c_phn_no`, `c_address`, `acc_balance`, `card_no`) values('Tanim','003','058 100 00 10003','09-12-1984','+8801743577678','Nuyakali,Bangladesh',500000,'4321 8765 0912 5933');
  165. INSERT INTO `customer_account_info`(`c_name`, `customer_id`, `acc_num`, `c_dob`, `c_phn_no`, `c_address`, `acc_balance`, `card_no`) values('Adnan Farabi','004','059 200 00 004','31-05-1998','+8801543576778','Dhaka,Bangladesh',7500,'5494 3720 3464 3929');
  166. INSERT INTO `customer_account_info`(`c_name`, `customer_id`, `acc_num`, `c_dob`, `c_phn_no`, `c_address`, `acc_balance`, `card_no`) values('Borhan','005','060 200 00 10005','15-05-1988','+88015435778','Borisal,Bangladesh',11000,'3456 9464 1299 1938');
  167. INSERT INTO `customer_account_info`(`c_name`, `customer_id`, `acc_num`, `c_dob`, `c_phn_no`, `c_address`, `acc_balance`, `card_no`) values('Borhani Mia','006','070 100 00 10006','19-06-1965','+8801748677778','Nuyakali, Bangladesh',9000,'4021 6477 1927 1377');
  168. INSERT INTO `customer_account_info`(`c_name`, `customer_id`, `acc_num`, `c_dob`, `c_phn_no`, `c_address`, `acc_balance`, `card_no`) values('Rasel','007','080 100 00 10007','12-06-1996','+880164355678','Borisal,Bangladesh',800000,'5490 1277 1388 9500');
  169. INSERT INTO `customer_account_info`(`c_name`, `customer_id`, `acc_num`, `c_dob`, `c_phn_no`, `c_address`, `acc_balance`, `card_no`) values('Babu','008','090 200 00 10008','02-12-1989','+8801743667778','mymensingh,Bangladesh',150000,'4563 4616 9085 2763');
  170.  
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!