Implement a relational database management system

Assignment Help Database Management System
Reference no: EM131330990

Database Management Assignment

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.

Reference no: EM131330990

Identify the type dependencies found

CIT 310- In the diagram below there are dependencies. Identify the dependencies. In a Word document, identify the type dependencies found and the attributes found in them.

Describe a dbms and its functions

Describe a DBMS and its functions. List, at minimum, three of the popular DBMS products and give a brief description of each.Explain what mobile devices are and why mobile sys

Define what database to use with a use statement

Use SQL Management Studio to create a database called ITCO630_P1.MDF.Add the following tables and data to the database.Create the following queries and save them all in a fil

Explain distinction between total and partial constraints

Explain the distinction between total and partial constraints. Explain the difference between a weak and a strong entity set. Explain the distinction between disjoint and over

Building non-trivial database-backed web application

Goal - To enable students to experience the process of building a non-trivial database-backed web application. Develop an ER diagram for your chosen area - use Oracle SQL Deve

Write vba code to extract the data and display it in listbox

Create a form (frmUpdateMedia), write a VBA code to extract the data and display it in a listbox, dropdownbox or any from of grid. Delete the current records then insert the

Develop a table mainmenu with at least three fields

Create a table MainMenu with at least 3 fields: ID, caption, and form or report to run. Your code (for the button) will dynamically read the caption from the table and as

What are the ethical implication of the data challenges

Write a two page, single space, 12 point ft, inch margin paper on your impressions on the two topics: what are the data challenges you observe. what are the ethical implicatio

Reviews

Write a Review

 
Free Assignment Quote

Assured A++ Grade

Get guaranteed satisfaction & time on delivery in every assignment order you paid with us! We ensure premium quality solution document along with free turntin report!

All rights reserved! Copyrights ©2019-2020 ExpertsMind IT Educational Pvt Ltd