[Solved]Create Database Exists Bankaccountdb Use Bankaccountdb Create Table Exists Branch Name Var Q37249550

create database IF NOT EXISTS BankAccountDB;

use BankAccountDB;

create table if not exists Branch(
name varchar(25) primary key,
address varchar(50)
);

insert into branch values (‘Bank of America’,’99th street,here’);

insert into branch values (‘Wellsfargo’,’what ave’);

select * from Branch;

create table if not exists Customer(
ssn char(9) primary key,
fname varchar(25),
lname varchar(25),
phone char(10),
address varchar(50),
fy_branch varchar(25),
foreign key (fy_branch) references branch(name)
);
insert into Customer values (‘12345′,’j’,’s’,’1234567890′,
‘here’,’Wellsfargo’);
select * from Customer;

create table if not exists Account(
acc_no char(9) primary key,
balance decimal(15,2),
open_date date,
atype varchar(10)
);
insert into Account values (‘3355′,50,’20190403′,’deposit’);
select * from Account;

create table if not exists Owns(
ssn char(9),
acc_no char(9),
own_date date,
foreign key (ssn) references customer(ssn),
foreign key (acc_no) references account (acc_no)
);

insert into Owns values (‘12345′,’3355′,’20190404’);
select * from Owns;

create table if not exists Loan(
loan_no char(9) primary key,
amount decimal(15,2),
ltype varchar(10)
);

insert into Loan values (‘1′,12345,’car’);
select * from Loan;

create table if not exists Payment(
loan_no char(9),
payment_no char(4),
amount decimal(15,2),
method varchar(10),
status varchar(10),
due_date date ,
primary key(loan_no,payment_no),
foreign key (loan_no) references loan(loan_no)
);
insert into Payment values (‘1′,’600′,50000,’not know’,’notpaid’,
‘20190404’);
insert into Payment values(‘1′,’700′,500,’Check’,’Paid’,’20190403′);
select * from Payment;

create table if not exists Borrows(
ssn char(9),
loan_no char(9),
primary key(loan_no,ssn),
foreign key (ssn) references customer(ssn),
foreign key (loan_no) references loan(loan_no)
);
insert into Borrows values (‘12345′,’1’);
select * from Borrows;

delete from payment where status=’paid’;

select * from payment;

update Account set balance=7890 where acc_no=’3355′;

select * from account;

drop table Borrows;
drop table payment;
drop table loan;
drop table owns;
drop table Account;
drop table Customer;
drop table branch;

Section 1

Using SQL, create tables according to the given schema shown inFigure 1. Note that this section is exactly the same as the one inAssignment 4. You can use your Assignment 4 directly, or modifyyour Assignment 4 (if needed). The type of each attribute isdefined in Figure 2. Note that it is case-sensitive.

Branch (name, address)

Customer (ssn, fname, lname, phone, address, fv_branch) foreignkey (fv_branch) references Branch (name)

Account (acc_no, balance, open_date, atype)

Owns( ssn, acc_no, own_date) foreign key (ssn) referencesCustomer(ssn), foreign key (acc_no) references Account (acc_no)

Loan( loan_no, amount, ltype)

Payment ( loan_no, payment_no, amount, method, status, due_date)foreign key(loan_no) references Loan(loan_no)

Borrows(ssn, loan_no) foreign key (ssn) referencesCustomer(ssn), foreign key (loan_no) references Loan (loan_no)

Figure 1 Schema

Branch (name: VARCHAR(25), address: VARCHAR(50))

Customer (ssn: CHAR(9), fname: VARCHAR(25), lname: VARCHAR (25), phone CHAR(10), address: VARCHAR (50), fv_branch: VARCHAR(25))

Account (acc_no: CHAR(9), balance: DECIMAL (15,2), open_date:DATE, atype: VARCHAR(10))

Owns( ssn: CHAR(9), acc_no: CHAR(9), own_date: DATE) foreign key(ssn) references customer(ssn), foreign key (acc_no) referencesaccount (acc_no)

Note: DATE is in the format of ‘yyyy-mm-dd’.

Loan( loan_no: CHAR(9), amount: DECIMAL(15,2), ltype:VARCHAR(10))

Payment ( loan_no: CHAR(9), payment_no: CHAR(4), amount:DECIMAL(15,2), method: VARCHAR(10), status: VARCHAR(10), due_date:DATE) foreign key(loan_no) references loan(loan_no)

Borrows(ssn: CHAR(9), loan_no: CHAR(9)) foreign key (ssn)references customer(ssn), foreign key (loan_no) references loan(loan_no)

Figure 2 Schema with type

Figure 3 ER Diagram

Section 2

Populate the database. Please check sections 3 for details.

Section 3

Use SQL to specify the following queries.   When youpopulate the database, insert data such that at least one row willbe display as the result of running each query.

1. List each the loan payment (loan_no, payment_no, amount,method) that was paid and the payment was less than $15, order theresult by loan_no. (once a payment is done, the status of thepayment will be “Paid”)
2. List all the customers (ssn, first name, last name) who have atleast one account with balance more than $10000. Don’t list acustomer twice if he/she has more than one account satisfy thecondition.
3. List all customers (ssn, first name, last name) who own lessthan 3 accounts.
4. List all the customers (ssn, first name, last name, phonenumber) who own account ‘000000001’.
5. List each the loan payment (payment no, due_date) of “JohnSmith” that has been paid by check.
6. List each the customer (ssn, first name, last name) who has atleast one loan. List a customer only once even if the customer hasmore than two loan.
7. Retrieve all the loans that “John Smith” has. If a loan isshared by him and other customers, you should count it in. Pleaselist John’s ssn and number of loans he has.
8. Retrieve the total account balance of a customer if a customerhas more than 2 accounts. If an account is shared, you should alsocount it in. List ssn, number of accounts, and total balance.
9. List each customer with his/her favorite branch (customer SSN,Customer Last Name, Branch Name, Branch address). If a customerdoes not have a favorite branch, then the branch name and branchaddress should be NULL.

Section 4

Specify the statements to drop all the tables. Pay attention tothe order of the drop statements in order to drop everythingsuccessfully. Note that this section is exactly the same as the onein Assignment 4. You can use your assignment 4 directly, or modifyyour assignment 4 (if needed).

Submission

Submit the following files to Canvas. Note: Submit through anyother ways, such as email attachment, will not be graded. Do NOTzip them.

1. *Create table statements (file name must be:1_create_table.txt)
2. Insert statements to populate database (file name must be :2_populate_db.txt)
3. Queries (file name must be: 3_query.txtl)
4. *Statements to drop all tables (file name must be:4_drop_all.txt)
5. An output file showing query results (file name must be:5_output.pdf). In this file, for each query, you must:
   1. Include the number and query description (e.g Query 1: Listeach the loan payment ….)
   2. Your SQL query (e.g. select… from…)
   3. The result of running this query (screen shots).

Note: 1* and 4* can be the same as theones that you submitted for Assignment 4.

Expert Answer

---

Answer to create database IF NOT EXISTS BankAccountDB; use BankAccountDB; create table if not exists Branch( name varchar(25) prim… . . .