Create a Database using MySQL Workbench

Part 1:

1. Create the Nova database on the MySQL Workbench.
2. Create the tables on the MySQL Script and save the script as NovaTables.sql
3. Create the Nova Model for this database. You can use any software application or word document to create it.
4. Make the customer email, bankers email, loan officers email, the loan payment number unique
5. Make the customer phone number, banker email, and branch office number an index. This part must be shown in the NovaTables.sql
6. You must show foreign key constraints, you must add null or not null to all columns check excel spreadsheet to see which one(s) can be null
7. Insert all the data provided from the excel spreadsheet to the tables. Save the script as Nova(your Initials) data insert. Example NovaCGDataInsert

8. Reverse engineer the database and save the model as the letter N and your Initials. For example: NCG

Part 2: Create the following Queries. You must create a new MySQL Script for query’s 1 thru 13 save the script as NovaQuerys 1-13 You must add comments to every query explaining your thought process and the query number your created . ie / *PT2

Query#2 this query is getting the highest credit card balance for all customers in our database */

1. Show me the customers first and last name, email and mobile number
2. Show me the banker full name and their email address except for homer
3. Show me the amount of customers we have per city
4. Show me all loan officers full name
5. Show me all the customers who live in NY or FL or TX
6. Show me all the account information but show the balance in the order from highest to lowest
7. Show all the customers identification number, first and last name, whose last name ends with the letters SON or ends with ER
8. Show me all the Bankers identification number, full name whose first name begins with the letters A
9. Show me all the customer’s that are not located in CO
10. Show the loans number, payment dates, payment amount and the amount they paid we want those payments made from September 1st to September 15th. You must format the date and the payment amount
11. Show me the loan with the lowest amount and the associated customer identification number

12. Show me all the accounts information in our database format the balance and sort them from lowest to highest, the balance is not 500, 1000, 1500, 2000 and it can not be a savings nor money market. Show me the sum of all the loans, their associated customer id and the amount of loans the customer has and sort it by the customer id in ascending order

Part 3: You must create a new MySQL Script for query’s 1 thru 10 save the script as PT3Querys1-9. You must add comments to every query explaining your thought

process and the query number your created. ie /*PT3 Query#1 */ Note: Some of them you must apply left outer join, right outer join, inner join and subqueries.

1. Show the customers full name and full address and any matching loans associated to the customer and including their amount. The customer full name in ascending order. You must format the amount and concatenate the address.
2. Retrieve all our customers identification number, first and last name including there balance, but I only need the loan amount that exceed $5000 but below $20000 *You must format the amount
3. Show list of all our customers first and last name, account number and category. We only want the customers who has a checking account and has a balance with our bank.
4. Show list of all our customers full name, loan number and average amount of all the loans. We need to know which customer is above this average amount
5. Show the customers identification number, first and last name, address and loan amount. You must calculate and include a 5% interest to the amount and obtain a new amount. *You must format the amount and new amount and concatenate the customer address and name
6. Show all the branch name and full address and office phone number, the amount of bankers that work for the each branch. You concatenate the branch address
7. Show the customers full name and the amount of loans a customer has.
8. show the customer identification number, first and last name, customers loan number and amount and how much they paid. I also want to know how much their current amount is after they made a payment to there account. It must be sorted by the customer id. You must format the amount, payment amount and current amount

9. Show the first and last name, their identification number and the amount of accounts the customers opened with our bank and their total balance of all their accounts.

Part 4 Views: You must use a new MySQL Script. Save the script as NovaViews. You must add comments to every query explaining your thought process and the Query number your created. ie /* View #1 */

1. Creating a view for the branch manager of nova1. The view must show first and last name the customer, the amount of accounts opened by a particular banker, the banker(s) first and last name that work for a particular that location branch. It must be sorted by the banker identification number. You must concatenate the branch address, the customer name and bankers name. The name of view must be Branch_Manager_Nova1
2. Your creating a view for the NOVA CFO . The view must show their customers first and last name for the customer, loan account number, their current balance and show the identification of the branch that opened the account. You must format the amount Sort the branch identification in ascending order Call the view Nova_CFO_Open_Loans
3. Create a view for Kenny McCormick: The view must show his first name, money account number, market market balance and loan account and loan amount in addition you must to calculate the balance with interest the accrued on the money market account and show it’s new balance, subtract the payment amounts made to the loan and get it’s new amount. Format the money market balance, new balance with interest on the money market account, loan amount, the loan amount new amount and, round the interest rate value and new balance, and only show the last four digits of each account number and loan number. Call the view KennyMMLoanAccounts