Submit SQL commands used to answer Part 1, Part 2, and Part 3. Make sure that your SQL script runs without any errors. Submit screen shots of running commands for Part 2. Submit answers to Part 4 along with explanation of the system response to your command (so in case of error, explain why the error happened, in case the insert works, explain why it worked).

Part 1 (3 Points): Creating the database:

Create the following tables. The underlined bold column names are the primary keys. Make sure to specify the primary and foreign key constraints in your create table statements.

Branch(branch_id:integer, branch_name:varchar(50), branch_city:varchar(50), assets:numeric(11,2)

Loan(loan_number:integer, branch_id:integer, amount:numeric(8,2))

foreign key branch_id references Branch(branch_id)

Customer(customer_id:integer, customer_name:varchar(30), customer_street:varchar(30), customer_city:varchar(50))

Borrower(customer_id:integer, loan_number:integer)

foreign key (customer_id) references Customer(customer_id)

foreign key (loan_number) references Loan(loan_number)

Depositor(customer_id:integer, account_number:integer)

foreign key (customer_id) references Customer(customer_id)

foreign key (account_number) references Account(account_number)

Account(account_number:integer, branch_id:integer, balance:numeric(8,2))

foreign key branch_id references Branch(branch_id)

Part 2 (3 Points): Inserting data:

Insert the following data in the tables using insert into statements:

Branch:

1, DowntownChi, Chicago, 40000000.00

2, UptownMPL, Minneapolis, 32000000.00

3, DowntownSP, St. Paul, 21000000.00

Loan:

1001, 1, 2008.08

1002, 1, 3201.06

1003, 2, 4508.03

1004, 3, 21008.00

Customer:

1, Gretzky, 14 S 6^th St., St. Paul

4, Carr, 16699 39^th Ave N, Gary

2, Kilmer, 205 Dupont Ave. N, Minneapolis

3, Smith, 3598 Jones Rd., Hopkins

5, Smith, 9873 5^Th. St., Chicago

Borrower:

4, 1001

5, 1002

2, 1003

1, 1004

Depositor:

1, 232

2, 235

4, 294

3, 295

5, 249

Account:

232, 3, 456.23

235, 2, 4500.19

294, 1, 6003.63

295, 3, 7500.00

249, 1, 670.85

Part 3 (9 Points): SQL Queries

Write SQL statements to answer each of the following questions.

List the Customer Id, Name for all customers, sorted by Name.

List the Loan number and Loan amount for all loans belonging to branch 1.

List the Loan number, Loan amount from all Branches with the Branch name UptownMPL (use branch name, if you have a 1000 branches, you are not going to know the branch number for each branch name).

List all Account numbers for Customers with the Name of Smith.

List all the Loan numbers for the Branches in the City of Duluth.

For each Branch list the Branch name, Customer name, and Customer city for all Customers that have an Account at that Branch. (note: accounts, not loans)

Part 4 (5 Points): Checking entity and referential integrity:

Write an SQL statement to do each of the following tasks in the given order (try 1, 2, 3, 4, than 5). Explain whether and why the statement is correctly executed or not. If you cut and paste, you may have to correct the single quotes (those are not errors)

Insert the following entry in CUSTOMER

4, Ford, 19 14^th Ave N, Mankato

Insert the following entry in LOAN

1005, 2, 30000.00

Insert the following entry in Borrower

3, 1005

Insert the following entry to the LOAN table

1006, 4, 45000.00

Insert the following entry in Branch table

4, DowntownMK, Mankato, 2100000.00