Create the following SQL objects based on your WBank information available:

Create two SQL views.

• One view will get the customers’ name, address and phone number with deposit over $1000.00.
• Save as CIS435_AllCUstomer_yourname.sql.
• Another view will be an Employee view, in this view you will need to get all the information about those employees who work in the same state, such as state of Virginia (VA).
• Save as CIS435_EmployeeSameState_yourname.sql.

Create a function.

• Name the function fn_getFulName with 2 input parms (first name and last name)
• The function will return a full name based on the available data stored in Customers table.
• Save as CIS435_fn_getFulName_yourname.sql.

Create a stored procedure.

• The stored procedure should modify the employee table, such as employee HourlyPay increase.
• Save as CIS435_Sp_employeePay_yourname.sql.

 

 

CIS435L Course Project WBank
Entities
WBank.Locations
Column Name Data Type Relation Required LocationID int PK Y LocationCode
Address
City State
WBank. AccountType
Column Name Data Type Relation Required AccountTypeID int PK Y AccountType
WBank. Employees
Column Name Data Type Relation Required EmployeeID int PK Y EmployeeNumber
FirstName
LastName
Title
CanCreateNewAccount HourlySalary
Address
City
State
Zip code
Email Address
WBank. Customers
Column Name Data Type Relation Required CustomerID int PK Y int FK Y Column Name Data Type Relation Required DepositID int PK Y LocationID int FK Y EmployeeID int FK Y CustomerID int FK Y DateCreated
AccountNumber
AccountTypeID
Fname
Lname
Gender
Address
City
State PhoneNumber
Email Address
WBank. Deposits DepositDate
DepositAmount
WBank. Withdrawals
Column Name Data Type Relation Required WithdrawalID int PK Y LocationID int FK Y EmployeeID int FK Y CustomerID int FK Y Column Name Data Type Relation Required CheckCashingID int PK Y LocationID int FK Y EmployeeID int FK Y CustomerID int FK Y WithdrawalDate
WithdrawalAmount
WithdrawalSuccessful
WBank. CheckCashing CheckCashingDate
CheckCashingAmount

Attachments:

• 1493694267-WBankOfAmericaDiagram__Golby.jpg
• 1493694268-CIS435L Course Project WBank Entities.pdf