DBMS Lab Cycle 8

 

 8. Implement a banking environment with following tables, procedures and function.

bank_customer(cust_id,cust_name,email)

cust_id is the primary key and it must be auto generated by the databse system.

account(acc_no,acc_type,cust_id,balance)

acc_no is the primary key and cust_id is foreign key.

The account_type field will accept ONLY two values saving and current. 

if the account type is saving then the minimum balance should be RS.1000 however there is no minimum balance condition for the current account.

 

1. Import DATA of account table from a csv file. [Download CSV]

mysql> SHOW GLOBAL VARIABLES LIKE 'local_infile';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| local_infile  | OFF   |
+---------------+-------+
1 row in set (0.90 sec)

mysql> SET GLOBAL local_infile=1;
Query OK, 0 rows affected (0.05 sec)

mysql> SHOW GLOBAL VARIABLES LIKE 'local_infile';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| local_infile  | ON    |
+---------------+-------+
1 row in set (0.01 sec)

mysql> quit

mysql> mysql --local-infile=1 -u root -p

mysql> use DBNAME;

mysql> LOAD DATA LOCAL INFILE '/file_name.csv' INTO TABLE table_name FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n' IGNORE 1 LINES;

2. create a function getBalance(acc_no) to get the balance details of a bank account.

 

3. create two procedures

    credit(acc_no,amount)

    debit(acc_no,amount)

Display appropriate messages of every operations and errors.

 

4. Use curosr and create a procedure to list all email ids of customer separated by semicolon;

(Refer: https://www.mysqltutorial.org/mysql-cursor/)

account

acc_no acc_type     cust_id balance

1001 saving 1 10000

1002 saving 2 20000

1003 saving 3 30000

2001 current 4 10000

2002 current 5 20000

bank_customer

cust_id cust_name email

1 hari hari@gmail.com

2 sam sam@gmail.com

3 sree sree@lbscek.ac.in

4 ali ali@lbscek.ac.in

5 john john@yahoo.co.in

select * from account;

select getBalance(1001) as currentBalance;

call credit(1001,2000); 12000

call credit(1004,1000); ERROR

call debit(1001,1000); 11000

call debit(1004,1000); ERROR

call debit(1001,10500); ERROR

call debit(1001,10000); 1000

call debit(2001,10000); 0

select getBalance(2001) as currentBalance;

select * from account;