Database Management

Database Management

Database Management

Subject: General Questions    / College life    

Question

You have been hired by National Bank as their new database administrator. Your first task is to upgrade the bank’s outdated computer system and implement a relational database management system.

You have designed the preliminary structure of the database as follows:

 

Branch Table

· BranchID

· BranchName

· BranchAddress

· BranchCity

· BranchState

· BranchZip

· BranchPhone

· BranchManager

Customer Table

· CustId

· CustName

· CustAddress

· CustCity

· CustState

· CustZip

· CustPhone

· AccountNo

· CurrentBal

 

Teller Table

· TellerID

· TellerFirstName

· TellerLastName

Transaction Table

· Type (Deposit, Withdrawal)

· CustName

· AccountNo

· AccountType (Savings, Checking, Money Market, IRA)

· BranchName

· TransAmount

· TellerName

· Date

· Time

 

1. Based on the tables above:

· Normalize the database to third normal form and create an E-R diagram to determine which fields in each table should serve as the primary key field(s) and which should serve as the foreign key field(s). In some cases, a combination of fields will be needed to create a primary key. Each table must have a primary key field. Explain why you have chosen as you did.

· Based on your answers to the step above, use Access to designate each of the selected fields as primary key fields.

· Create the relationships in Access between each of the related tables. Be sure to enforce referential integrity in each case.

· Apply a validation rule indicating that the values for the AccountID field are limited to 1, 2, 3, or 4 (where each number represents a different account type). Explain how this will limit input values in the AccountID field.

· Change the field size of the AccountNo field to 7. Explain how this will limit input values in the AccountNo field.

· Which field(s) would generate the most benefit from being indexed without creating unnecessary tables that require additional storage space? Justify your answers.

· Use Access or SQL to create appropriate indexes for this database.

 

2. Populate the tables with the data supplied below.

Branches

1. National Bank East, 1522 N. Main Street, Bayonne, NJ, 07002, 201-908-5676, Paul Nelson

2. National Bank West, 89 Treetop Street, Elizabeth, NJ, 07201, 908-762-1278, Mary Carter

Customers and Accounts

AN45 Max Anderson, 6 Mountain Ln, Elizabeth, NJ, 07201, 908-233-0987

Savings, #0255684, balance $9875

Checking, #0126478, balance $850

MA12 Paula Maxwell, 45 New Rd, Bayonne, NJ, 07002, 201-567-1280

IRA, #0364897, balance $32,000

TH09 Joan Thompson , 1783 N Main St, Bayonne, NJ, 07002, 201-564-0964

Savings, #0236458, balance $10050

IRA, #0364597, balance $65000

LE78 Arthur Lewis, 96 Oak Park Ln, Hillside, NJ, 07205, 908-836-9182

Savings, #0856475, balance $12000

Checking, #0245877, balance $500

ST12 Adrian Stone, 123 Newton Rd, Elizabeth, NJ, 07201, 908-675-0182

Money Market, #0457965, balance $25000

RI79 Lillian Rivers, 791 North Ave, Greenville, NJ, 07097, 201-873-1298

Checking, #0126547, balance $222

Savings, #0245987, balance $7500

CA08 John Carpenter , 8 Old St, Elizabeth, NJ, 07201, 908-459-8237

IRA, #0324879, balance $6900

Checking, #0347895, balance $65

Money Market, #0557965, $1750

Tellers

MR01 Michael Roans

JM13 Jack Morrison

NS22 Natalie Stokes

PN01 Patricia Nelson

Transactions

TransID
    

Customer
    

AccountNo
    

TransType
    

Branch
    

Teller
    

TransAmt
    

Date
    

Time

1001
    

Anderson
    

0126478
    

Deposit
    

West
    

Roans
    

80.00
    

12/2/11
    

3:32 pm

1002
    

Carpenter
    

0347895
    

Deposit
    

West
    

Roans
    

4000.00
    

12/2/11
    

4:12 pm

1003
    

Thompson
    

0236458
    

Deposit
    

East
    

Nelson
    

1800.00
    

12/3/11
    

9:02 am

1004
    

Carpenter
    

0557965
    

Withdrawal
    

East
    

Morrison
    

-50.00
    

12/3/11
    

9:12 am

1005
    

Rivers
    

0126547
    

Withdrawal
    

East
    

Morrison
    

-200.00
    

12/3/11
    

11:22 am

1006
    

Stone
    

0457965
    

Deposit
    

East
    

Nelson
    

766.00
    

12/5/11
    

10:15 am

1007
    

Anderson
    

0255684
    

Withdrawal
    

East
    

Nelson
    

-310.00
    

12/5/11
    

10:22 am

1008
    

Lewis
    

0856475
    

Withdrawal
    

East
    

Nelson
    

-1650.00
    

12/5/11
    

12:01 pm

1009
    

Thompson
    

0364597
    

Deposit
    

West
    

Stokes
    

56.00
    

12/5/11
    

2:09 pm

1010
    

Maxwell
    

0364897
    

Deposit
    

West
    

Stokes
    

851.00
    

12/6/11
    

9:01 am

1011
    

Lewis
    

0245877
    

Withdrawal
    

West
    

Roans
    

-1000.00
    

12/6/11
    

9:05 am

1012
    

Lewis
    

0856475
    

Deposit
    

West
    

Stokes
    

1000.00
    

12/6/11
    

9:06 am

1013
    

Rivers
    

0245987
    

Withdrawal
    

West
    

Nelson
    

-45.00
    

12/6/11
    

10:11 am

1014
    

Stone
    

0457965
    

Deposit
    

West
    

Stokes
    

6572.00
    

12/6/11
    

10:15 am

1015
    

Carpenter
    

0347895
    

Withdrawal
    

East
    

Nelson
    

-300.00
    

12/6/11
    

2:22 pm

1016
    

Carpenter
    

0324879
    

Deposit
    

East
    

Nelson
    

250.00
    

12/6/11
    

2:34 pm

 

3. Query the database using QBE or SQL to produce the desired results. Save and name each query appropriately.

A. List the customer ID, first name, and last name of all National Bank customers.

B. List the complete Teller Info table (all rows and columns).

C. List the first and last name along with the complete mailing address of all customers who reside in Elizabeth, NJ.

D. List the first and last name along with the complete mailing address of all customers who do not reside in Elizabeth, NJ.

E. List the first and last name of all customers who have a savings account with a current balance of more than $10,000.

F. List the first and last name and current balance of all customers who have an IRA account.

G. List the first and last name, branch name, transaction amount, and account type of all customers who made a deposit in Dec 5, 2011.

H. List the first and last name, account number, account type, and new balance for each transaction completed on Dec 6, 2011. To obtain the new balance, add the current balance to the transaction amount. Use NewBalance as the column name for the calculated field.

I. List the first and last name of all customers who have a money market or IRA account.

J. List the first and last name of all customers who completed a transaction at either branch. Sort customers by last name within branches.

K. How many transactions were completed on Dec 2, 2011?

L. Calculate the total amount of money deposited at National Bank West on December 6, 2011.

M. For every account, list the last name, first name, address, city, state, ZIP code, and phone number of the owner. Sort by owner’s last name.

N. For every transaction in the Transaction table, list the first and last name of the owner, teller name, branch name, transaction amount, and transaction type.

O. List the owner’s last name, account number, transaction amount, and transaction type for all transactions completed by Michael Roans.

P. Create a new table named IRA Customers using the data in the CustID, AccountNo, and CurrentBal fields for all IRA account holders.

Q. Use an update query to change the address for Arthur Lewis to 86 Oak Park Ln.

4. Explain how this RDBMS that you have created benefits the bank, its customers, and its employees. Be sure to include the advantages and disadvantages of using such a system.