Shown below is an ER diagram for a bank.

SKS bank is organized into branches. Each branch is located in aparticular city and is identified by a unique name. Each branchkeeps a total of all the deposits and loan amounts.

Bank customers have a name, a customer ID, and a home address. Acustomer may have an account (checking or savings) and may take outloans. Customers may have personal bankers or loan officers thatthey always work with.

Bank employees (including bankers and loan officers) have uniqueemployee IDs. Each employee has a manager, a start date (so lengthof employment can be calculated), a name, a home address, and a setof locations where they work. A location may be a branch or may bean office that is not in a branch.

Checking and savings accounts can be held by more than onecustomer and a customer can have more than one account. Eachaccount has a balance and a most recent date that the account wasaccessed by the customer. Savings accounts have an associatedinterest rate and checking accounts keep track of dates, amounts,and check numbers for overdrafts.

A loan originates at a particular branch and can be held by oneor more customers. The bank tracks the loan amount and payments. Aloan payment number does not uniquely identify a particular paymentamong all loans, but it does identify a particular payment for aspecific loan. The date and amount are recorded for eachpayment.

1. Implement the relations using MySQL (MySQL Workbench 6.3 isrecommended)2. Populate the relations used in the queries according to thefollowing criteria: a. Every relation should have at least 5 tuples (someof which do not contribute to the query answer) b. The queries should return answers containing atleast 5 distinct tuples. c. The data should look realistic; do not use randomkeyboard text or obvious placeholder data. d. You may not have to populate all the relations,just the ones used in the queries.

3.Give the SQL queries and resulting answers for the followingqueries.

Do not use null values or any values not explicitly given in thedata request. If you use use intermediate queries, show theintermediate queries and results as well as the final query andfinal result.

Queries:

1. List all the branches (their names) located in Orlando.

2. Find all customers (names, customer ID, and savings accountnumber) who have a savings account at a Orlando branch but do notlive in Orlando.

3. Find all loans (loan ID and branch) and customer names wherethe loan is held by a single customer.

4. Find all employees (employee ID and name) who are a personalbanker for at least one customer and who do not work

at any branch.

5. Find all customers who have had more than three overdrafts inDecember 2009 and have loans (more than one) totaling more than$100,000.

Transcribed Image Text:

name city 1 custID Manages empID title Customer m Works with n n Employee name m m Borrows m date accessed Has_checking n overdraft date loan ID n amount Has savings Loan n amount Account balance check_num Pays m interest rate date accessed acctID Owned by date n Payment Loaned by 1 Bank branch name m amount Works at n payment num city name city 1 custID Manages empID title Customer m Works with n n Employee name m m Borrows m date accessed Has_checking n overdraft date loan ID n amount Has savings Loan n amount Account balance check_num Pays m interest rate date accessed acctID Owned by date n Payment Loaned by 1 Bank branch name m amount Works at n payment num city

Answer rating: 100% (QA)

SQL Query sql Copy code SELECT branchname FROM Branches WHERE city Orlando Resulting Answer objectiv... View the full answer