[Solved]Using Sql Create Tables According Given Schema Shown Figure 1 Note Section Exactly One Ass Q37286217

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. 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.
2. 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.
3. 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.

Expert Answer

---

Answer to Using SQL, create tables according to the given schema shown in Figure 1. Note that this section is exactly the same as … . . .