-- =============================================
-- Database:     KoloBank3
-- Author:       FunctionX
-- Date Created: Sunday 22 December 2010
-- =============================================
USE master
GO

-- Drop the database if it already exists
IF  EXISTS (
	SELECT name 
		FROM sys.databases 
		WHERE name = N'KoloBank3'
)
DROP DATABASE KoloBank3
GO
CREATE DATABASE KoloBank3
GO

USE KoloBank3
GO

CREATE USER Pat FOR LOGIN [central\pkatts];
GO
CREATE USER Orly FOR LOGIN [central\operez];
GO
CREATE USER Ray FOR LOGIN [central\rkouma];
GO
CREATE USER Gertrude FOR LOGIN [central\gmonay];
GO
CREATE SCHEMA Management;
GO
CREATE SCHEMA Personnel;
GO
CREATE SCHEMA Transactions;
GO
-- ================================================================================
-- Function:	CreateUsername
-- Description:	This function uses the first name and the last name to create a 
--				user name made of 5 characters. To do it, if the employee doesn't 
--				have a last name, the username is left empty. If the employee has 
--				only a last name, that will be the username. If the employee has a 
--				first name and a last name, the function takes the first letter of
--				the first name and the first 4 letters of the last name to create
--				a username.
--				Warning: This function doesn't check if the username exists already
-- ================================================================================
CREATE FUNCTION Personnel.CreateUsername 
(
	@FName nvarchar(20), @LName nvarchar(20)
)
RETURNS nvarchar(20)
AS
BEGIN
	DECLARE @Result nvarchar(20)

	IF @LName IS NULL
		SELECT @Result = N'';
	ELSE IF @FName IS NULL
		SELECT @Result = LOWER(LEFT(@LName, 4));
	ELSE
		SELECT @Result = LOWER(LEFT(@FName, 1) + LEFT(@LName, 4));

	RETURN @Result
END
GO
-- ================================================================================
-- Table:	Management.Locations
-- Description: A location is a branch where the banks operations can be performed
-- ================================================================================
CREATE TABLE Management.Locations
(
    LocationID int Identity(1,1) NOT NULL, 
    LocationCode nvarchar(10) UNIQUE NOT NULL,
    [Address] nvarchar(120),
    City nvarchar(50),
    [State] nvarchar(50),
    ZIPCode nvarchar(12),
    Country nvarchar(100) default N'USA',
    Notes nvarchar(max) NULL, 
    CONSTRAINT PK_Locations PRIMARY KEY (LocationID)
);
GO

-- ================================================================================
-- Table:       Management.AccountTypes
-- Description: The account types are checking, saving or certificate of deposit
-- ================================================================================
CREATE TABLE Management.AccountTypes
(
    AccountTypeID int Identity(1,1) NOT NULL, 
    AccountType nvarchar(40) NOT NULL, 
    Notes nvarchar(max) NULL, 
    CONSTRAINT PK_AccountTypes PRIMARY KEY (AccountTypeID)
);
GO
-- ================================================================================
-- Table:	Personnel.StartingSalaries
-- Description: This table is used to assign default 
--	        salaries to (newly hired) staff members
-- ================================================================================
CREATE TABLE Personnel.StartingSalaries
(
	Category nvarchar(30) not null,
	StartingSalary money null
);
-- ================================================================================
-- Table:    Employees
-- ================================================================================
CREATE TABLE Personnel.Employees
(
    EmployeeID int identity(1,1) NOT NULL, 
    EmployeeNumber nchar(10),
    FirstName nvarchar(32),
    LastName nvarchar(32) NOT NULL,
    Username nchar(5),
    [Password] nvarchar(20) default N'Password1',
    Title nvarchar(50),
    CanCreateNewAccount bit,
    EmailAddress nvarchar(50),
    WorkPhone nvarchar(20),
    Extension smallint,
    [Address] nvarchar(120),
    City nvarchar(40),
    [State] nvarchar(40),
    ZIPCode nvarchar(12),
    Country nvarchar(50) default N'USA',
    HomePhone nvarchar(20),
    HourlySalary money,
    Notes nvarchar(max), 
    CONSTRAINT PK_Employees PRIMARY KEY (EmployeeID)
);
GO
-- ================================================================================
-- Table:    Customers
-- ================================================================================
CREATE TABLE Management.Customers
(
    CustomerID int Identity(1, 1) NOT NULL,
    DateCreated date,
    AccountNumber nvarchar(20),
    AccountTypeID int Constraint FK_TypeOfAccount References Management.AccountTypes(AccountTypeID),
    CustomerName nvarchar(50),
    Username nvarchar(20),
    [Password] nvarchar(20) default N'Password1',
    PIN int,
    [Address] nvarchar(120) NOT NULL,
    City nvarchar(40) NOT NULL,
    [State] nvarchar(40) NOT NULL,
    ZIPCode nvarchar(12) NOT NULL,
    Country nvarchar(50) DEFAULT(N'USA'),
    EmailAddress nvarchar(50),
    HomePhone nvarchar(20) NULL,
    WorkPhone nvarchar(20),
    WorkExtension nvarchar(6),
    DateUpdated date,
    Notes nvarchar(max), 
    CONSTRAINT PK_Customers PRIMARY KEY (CustomerID)
);
GO
-- ================================================================================
-- Table:    Transactions.Deposits
-- ================================================================================
CREATE TABLE Transactions.Deposits
(
    DepositID int identity(1, 1) NOT NULL,
    LocationID int Constraint FK_DepositLocation References Management.Locations(LocationID) NOT NULL,
    EmployeeID int Constraint FK_Clerk References Personnel.Employees(EmployeeID),
    CustomerID int Constraint FK_CustomerAccount References Management.Customers(CustomerID) NOT NULL,
    DepositDate date NOT NULL,
    DepositAmount money NOT NULL,
    Notes nvarchar(max), 
    CONSTRAINT PK_Deposits PRIMARY KEY (DepositID)
);
GO
-- ================================================================================
-- View: Transactions.DepositsForNewAccounts
-- Description: This view allows a user to get a list of
--			    all deposits on bank accounts
-- ================================================================================
CREATE VIEW Transactions.DepositsSummary
AS
	SELECT LocationID, EmployeeID, CustomerID, DepositDate, DepositAmount, Notes
	FROM   Transactions.Deposits;
GO
-- ================================================================================
-- Table:    Transactions.Withdrawals
-- ================================================================================
CREATE TABLE Transactions.Withdrawals
(
    WithdrawalD int identity(1, 1) NOT NULL,
    LocationID int Constraint FK_WithdrawlLocation References Management.Locations(LocationID) NOT NULL,
    EmployeeID int Constraint FK_Employee References Personnel.Employees(EmployeeID),
    CustomerID int Constraint FK_AccountNumber References Management.Customers(CustomerID) NOT NULL,
    WithdrawalDate date NOT NULL,
    WithdrawalAmount money NOT NULL,
    ServiceChargeApplied money default 0.00,
    Notes nvarchar(max), 
    CONSTRAINT PK_Withdrawals PRIMARY KEY (WithdrawalD)
);
GO
-- ================================================================================
-- View: Transactions.WithdrawalsSummary
-- Description: This view allows a user to get a list of
--			    all withdrawals on bank accounts
-- ================================================================================
CREATE VIEW Transactions.WithdrawalsSummary
AS
	SELECT LocationID, EmployeeID, CustomerID, WithdrawalDate, WithdrawalAmount, Notes
	FROM   Transactions.Withdrawals;
GO
-- ================================================================================
-- Table:    Transactions.CheckCashing
-- ================================================================================
CREATE TABLE Transactions.CheckCashing
(
    CheckCashingID int identity(1, 1) NOT NULL,
    LocationID int Constraint FK_CashingLocation References Management.Locations(LocationID) NOT NULL,
    EmployeeID int Constraint FK_CashingEmployee References Personnel.Employees(EmployeeID),
    CustomerID int Constraint FK_CashingCustomer References Management.Customers(CustomerID) NOT NULL,
    CheckNumber int,
    Recipient nvarchar(50) NOT NULL,
    DateProcessed date NOT NULL,
    CheckAmount money NOT NULL,
    HasEnoughFunds bit Default 1,
    ServiceChargeAmount money default 0.00,
    Notes nvarchar(max), 
    CONSTRAINT PK_CheckCashing PRIMARY KEY (CheckCashingID)
);
GO

-- ===================================================
-- Table:	Management.DatabaseOperations
-- Description: This table is used to hold information
--              about operations performed on any table
--              of the database. It specifies:
--              a. The type of object on which the action
--                 was performed. The types of object can
--                 be a table
--              b. The name of the table
--              c. The name of the employee who 
--                 performed the action.
--              d. The action that was performed. This
--                 can be an insert, an update, or a 
--                 delete operation
--              e. The date/time the action was performed
-- ===================================================
CREATE TABLE Management.DatabaseOperations
(
    OperationID int identity(1,1) NOT NULL,
    ObjectType nchar(20) default N'Table',
    ObjectName nvarchar(40),
    PerformedBy nvarchar(50),
    ActionPerformed nvarchar(max),
    TimePerformed datetime,
    CONSTRAINT PK_Operations PRIMARY KEY(OperationID)
);
GO

GRANT SELECT, INSERT, UPDATE, DELETE
ON OBJECT::Transactions.Deposits
TO Gertrude, Pat
WITH GRANT OPTION;
GO
GRANT SELECT, INSERT, UPDATE, DELETE
ON OBJECT::Transactions.Withdrawals
TO Gertrude, Pat
WITH GRANT OPTION;
GO
GRANT SELECT, INSERT, UPDATE, DELETE
ON OBJECT::Transactions.CheckCashing
TO Gertrude, Pat
WITH GRANT OPTION;
GO
GRANT SELECT, INSERT, UPDATE, DELETE
ON OBJECT::Transactions.DepositsSummary
TO Gertrude, Pat
WITH GRANT OPTION;
GO
GRANT SELECT, INSERT, UPDATE, DELETE
ON OBJECT::Transactions.WithdrawalsSummary
TO Gertrude, Pat
WITH GRANT OPTION;
GO

GRANT SELECT, INSERT, UPDATE, DELETE
ON OBJECT::Management.DatabaseOperations
TO Gertrude, Pat;
GO

GRANT SELECT
ON OBJECT::Management.DatabaseOperations
TO Ray;
GO
DENY INSERT, UPDATE, DELETE
ON OBJECT::Management.DatabaseOperations
TO Ray;
GO

GRANT SELECT, INSERT, UPDATE, DELETE
ON OBJECT::Management.AccountTypes
TO Ray, Gertrude, Pat;
GO

GRANT SELECT, INSERT, UPDATE, DELETE
ON OBJECT::Personnel.Employees
TO Ray, Gertrude, Pat;
GO
GRANT SELECT, INSERT, UPDATE, DELETE
ON OBJECT::Management.Customers
TO Ray, Gertrude, Pat;
GO
GRANT SELECT, INSERT, UPDATE, DELETE
ON OBJECT::Personnel.StartingSalaries
TO Ray, Gertrude, Pat;
GO

GRANT SELECT, INSERT, UPDATE
ON OBJECT::Transactions.Deposits
TO Ray;
GO
DENY DELETE
ON OBJECT::Transactions.Deposits
TO Ray;
GO
GRANT SELECT, INSERT, UPDATE
ON OBJECT::Transactions.Withdrawals
TO Ray, Gertrude, Pat;
GO
DENY DELETE
ON OBJECT::Transactions.Withdrawals
TO Ray;
GO
GRANT SELECT, INSERT, UPDATE, DELETE
ON OBJECT::Transactions.CheckCashing
TO Ray;
GO
GRANT SELECT, INSERT
ON OBJECT::Transactions.DepositsSummary
TO Ray;
GO
GRANT SELECT, INSERT
ON OBJECT::Transactions.WithdrawalsSummary
TO Ray;
GO

DENY UPDATE, DELETE
ON OBJECT::Transactions.DepositsSummary
TO Ray;
GO
DENY UPDATE, DELETE
ON OBJECT::Transactions.WithdrawalsSummary
TO Ray;
GO

DENY SELECT
ON OBJECT::Management.Locations
TO Orly;
GO
DENY SELECT
ON OBJECT::Management.AccountTypes
TO Orly;
GO
DENY SELECT
ON OBJECT::Personnel.StartingSalaries
TO Orly;
GO
DENY SELECT
ON OBJECT::Personnel.Employees
TO Orly;
GO
DENY SELECT
ON OBJECT::Management.Customers
TO Orly;
GO
DENY DELETE
ON OBJECT::Transactions.Deposits
TO Orly;
GO
DENY DELETE
ON OBJECT::Transactions.Withdrawals
TO Orly;
GO
DENY DELETE
ON OBJECT::Transactions.CheckCashing
TO Orly;
GO

GRANT SELECT, INSERT
ON OBJECT::Transactions.Deposits
TO Orly;
GO
GRANT SELECT, INSERT
ON OBJECT::Transactions.Withdrawals
TO Orly;
GO
GRANT SELECT, INSERT
ON OBJECT::Transactions.CheckCashing
TO Orly;
GO

GRANT SELECT, INSERT, DELETE
ON OBJECT::Transactions.DepositsSummary
TO Orly;
GO
GRANT SELECT, INSERT, DELETE
ON OBJECT::Transactions.WithdrawalsSummary
TO Orly;
GO

-- ================================================================================
-- Records
-- ================================================================================
-- Records for Management.Locations
INSERT INTO Management.Locations(LocationCode, [Address], City, [State], ZIPCode)
VALUES(N'826005', N'3925 Euler Ave', N'Silver Spring', N'MD', N'20904');
GO
INSERT INTO Management.Locations(LocationCode, City, [State], ZIPCode)
VALUES(N'249615', N'Alexandria', N'VA', N'22132'),
      (N'824405', N'Silver Spring', N'MD', N'20906'),
      (N'936486', N'Washington', N'DC', N'20008'),
      (N'429025', N'Rockville', N'MD', N'20854'),
      (N'703648', N'Washington', N'DC', NULL);
GO
-- Records for Management.AccountTypes
INSERT INTO Management.AccountTypes(AccountType, Notes)
VALUES(N'Checking', N'Used to regularly deposit and withdraw money at will'),
      (N'Saving', N'Used to deposit money to save it but hardly withdraw it'),
      (N'CD', N'Certificate of Deposit');
GO
-- Records for Personnel.StartingSalaries
INSERT INTO Personnel.StartingSalaries
VALUES(N'Base', 10.00),
      (N'Intern', 12.35),
      (N'Regular', 14.50),
      (N'Manager', 20.00);
GO
-- Records for Personnel.Employees
INSERT INTO Personnel.Employees(EmployeeNumber, FirstName, LastName, Username, EmailAddress, Title, WorkPhone, Extension, [Address], City, [State], HourlySalary)
VALUES(N'662-286', N'Patricia', N'Katts', Personnel.CreateUsername(N'Patricia', N'Katts'), Personnel.CreateUsername(N'Patricia', N'Katts') + N'@kolobank.com', N'Branch Manager', N'(410) 653-1309', 105, N'15328 Crystal St.', N'Baltimore', N'MD', 35.75),
      (N'930-717', N'Gertrude', N'Monay', Personnel.CreateUsername(N'Gertrude', N'Monay'), Personnel.CreateUsername(N'Gertrude', N'Monay') + N'@kolobank.com', N'Head Teller', N'(410) 653-1309', 144, N'12 16th St. S. W.', N'Washington', N'DC', 30.02),
      (N'395-138', N'Raymond', N'Kouma', Personnel.CreateUsername(N'Raymond', N'Kouma'), Personnel.CreateUsername(N'Raymond', N'Kouma') + N'@kolobank.com', N'Accounts Manager', N'(410) 653-1309', 222, N'2888 Gwett Richards Av.', N'Glen Burnie', N'MD', 26.75),
      (N'822-730', N'Orlando', N'Perez', Personnel.CreateUsername(N'Orlando', N'Perez'), Personnel.CreateUsername(N'Orlando', N'Perez') + N'@kolobank.com', N'Cashier', N'(410) 653-1309', 128, N'308 Capitol Blvd', N'Baltimore', N'MD', 18.15),
      (N'487-525', N'Paulin', N'Guerrero', Personnel.CreateUsername(N'Paulin', N'Guerrero'), Personnel.CreateUsername(N'Paulin', N'Guerrero') + N'@kolobank.com', N'Intern', N'(410) 653-1309', 226, N'4445 Blue Oak St. #6A', N'Baltimore', N'MD', 16.85);
GO
-- Records for Management.Customers
/*
INSERT INTO Management.Customers(AccountNumber, DateCreated, AccountTypeID, CustomerName, [Address], City, [State], ZIPCode, Country, HomePhone, WorkPhone)
VALUES(N'28-3782-84', N'1/12/2011', 1, N'James Carlton Brokeridge', N'1022 Arlington Rd', N'Arlington', N'VA', N'20164', N'USA', N'(703) 645-1492', N'(703) 450-5577'),
      (N'92-3782-43', N'1/15/2011', 1, N'Chrissy Arlene McMahon', N'845 Arcadia Ave. #1512', N'Rockville', N'MD', N'20872', N'USA', N'(301) 684-2828', N'(301) 723-1882');
GO
INSERT INTO Management.Customers(AccountNumber, CustomerName, [Address], City, [State], ZIPCode, Country, DateCreated, HomePhone)
VALUES(N'38-4227-52', N'James Norris', N'1277 Cecil Maurice Av.', N'Chevy Chase', N'MD', N'20870', N'USA', N'1/13/2011', N'(301) 768-4024'),
      (N'68-6434-56', N'Eldridge Powers', N'273 S. Independence Ave.', N'Alexandria', N'VA', N'20185', N'USA', N'1/18/2011', N'(703) 622-7188');
GO
INSERT INTO Management.Customers(DateCreated, AccountNumber, AccountTypeID, CustomerName, [Address], City, [State], ZIPCode, Country, HomePhone, WorkPhone, WorkExtension)
VALUES(N'1/18/2011', N'83-4654-77', 2, N'Hobert Umbro Spampinato', N'8254 12th St. N.E.', N'Washington', N'DC', N'20008', N'USA', N'(202) 927-1040', N'(301) 726-8426', N'116');
GO
INSERT INTO Management.Customers(DateCreated, AccountNumber, CustomerName, [Address], City, [State], ZIPCode, Country, HomePhone)
VALUES(N'1/14/2011', N'47-4783-25', N'Gloria Aline Wright', N'15328 Crystal St.', N'Hyattsville', N'MD', N'20782', N'USA', N'(301) 723-5656');
GO
INSERT INTO Management.Customers(AccountNumber, DateCreated, AccountTypeID, CustomerName, [Address], City, [State], ZIPCode, Country, HomePhone, WorkExtension)
VALUES(N'82-3763-24', N'1/10/2011', 1, N'Liliana Wellie Ortez', N'4445 Blue Oak St. #6A', N'Chevy Chase', N'MD', N'20875', N'USA', N'(301) 821-4990', N'2220');
GO
INSERT INTO Management.Customers(AccountNumber, AccountTypeID, CustomerName, [Address], City, [State], ZIPCode, Country, DateCreated, HomePhone, WorkPhone, WorkExtension)
VALUES(N'72-3474-24', 3, N'Ornella Maiwand', N'2888 Gwett Richards Av.', N'Rockville', N'MD', N'20815', N'USA', N'1/12/2011', N'(301) 478-8244', N'(301) 726-8224', N'100');
GO
INSERT INTO Management.Customers(AccountNumber, CustomerName, [Address], City, [State], ZIPCode, Country, HomePhone, WorkExtension, DateCreated)
VALUES(N'34-5458-49', N'Leonel James Harbor', N'308 Capitol Blvd', N'Washington', N'DC', N'20010', N'USA', N'(202) 439-2864', N'114', N'1/12/2011');
GO
INSERT INTO Management.Customers(DateCreated, AccountNumber, AccountTypeID, CustomerName, [Address], City, [State], ZIPCode, Country, HomePhone)
VALUES(N'2011-01-25', N'29-4586-42', 1, N'Albert Sonny Odonnell', N'12 16th St. S. W.', N'Washington', N'DC', N'20004', N'USA', N'(301) 812-4442'),
      (N'2011-01-25', N'68-3465-86', 1, N'Howie Horace Fallace', N'9337 Cachet St', N'Arlington', N'VA', N'20140', N'USA', N'(703) 554-8724'),
      (N'2011-02-14', N'40-4658-63', 2, N'Mellinda Bridges', N'1336 Philadelphia St.', N'Takoma Park', N'MD', N'20908', N'USA', N'(301) 812-4428'),
      (N'2011-02-20', N'56-8468-59', 3, N'Barry Parrang', N'58 North Assault St.', N'Chantilly', N'VA',	'20102', N'USA', N'(703) 622-6460');
GO
INSERT INTO Management.Customers(AccountNumber, DateCreated, CustomerName, [Address], City, [State], ZIPCode, Country, HomePhone, WorkExtension)
VALUES(N'94-7785-34', N'2011-01-22', N'Ismail Zorbah', N'8252 Eleven Sons Rd', N'Arlington', N'VA', N'20150', N'USA', N'(703) 681-9022', N'1015');
GO
INSERT INTO Management.Customers(AccountNumber, DateCreated, AccountTypeID, CustomerName, [Address], City, [State], ZIPCode, Country, HomePhone)
VALUES(N'37-5764-86', N'2011-01-22', 1, N'Xavier Lenny Hereford', N'992 White Horse Rd', N'Bethesda', N'MD', N'20875', N'USA', N'(301) 631-8228'),
      (N'34-9754-71', N'2011-01-22', 3, N'Marthe Helene Bradley', N'13622 Washington Blvd', N'Laurel', N'MD', N'20707', N'USA', N'(301) 478-6602'),
      (N'72-9375-48', N'2011-01-24', 1, N'Jabouni Cabasco Toussey', N'2991 Justine Ave.', N'Silver Spring', N'MD', N'20912', N'USA', N'(301) 872-8272'),
      (N'37-5490-64', N'2011-02-06', 2, N'Cherrine Leonie Horvath', N'720 Oak Tree Rd.', N'Laurel', N'MD', N'20707', N'USA', N'(301) 549-0002'),
      (N'20-3454-96', N'2011-02-15', 1, N'Ophellie Wyman', N'104 G St. S. E.', N'Washington', N'DC', N'20005', N'USA', N'(202) 622-8674');
GO
INSERT INTO Management.Customers(AccountNumber, DateCreated, AccountTypeID, CustomerName, [Address], City, [State], ZIPCode, Country, HomePhone, WorkExtension)
VALUES(N'76-5475-43', N'2011-02-28', 1, N'Joseph Patrick Honey', N'3832 Great River Rd', N'Vienna', N'VA', N'20171', N'USA', N'(703) 350-2006', N'102');
GO
INSERT INTO Management.Customers(AccountNumber, AccountTypeID, DateCreated, CustomerName, [Address], City, [State], ZIPCode, Country, HomePhone, WorkPhone)
VALUES(N'27-3457-49', 1, N'2011-03-09', N'Robert Daniel Luner', N'802 Lilas Ave', N'Baltimore', N'MD', N'21208', N'USA', N'(410) 321-6730', N'(410) 539-1135');
GO
*/
/*
INSERT INTO Personnel.Employees(EmployeeNumber, FirstName, LastName, Username, EmailAddress, Title, CanCreateNewAccount, WorkPhone, Extension, [Address], City, [State], HourlySalary)
VALUES(N'220-682', N'Matt', N'Nguyen', Personnel.CreateUsername(N'Matt', N'Nguyen'), Personnel.CreateUsername(N'Matt', N'Nguyen') + N'@kolobank.com', N'Head Cashier', 1, N'(410) 653-1309', 228, N'828 John Booker St', N'Baltimore', N'MD', 22.82),
	  (N'462-088', N'Catherine', N'Hannagan', Personnel.CreateUsername(N'Catherine', N'Hannagan'), Personnel.CreateUsername(N'Catherine', N'Hannagan') + N'@kolobank.com', N'Customer Account Manager', 1, N'(410) 653-1309', 145, N'845 Arcadia Ave. #1512', N'Townson', N'MD', 28.55);
GO
INSERT INTO Personnel.Employees(EmployeeNumber, FirstName, LastName, Username, EmailAddress, HourlySalary, Title, WorkPhone, [Address], City)
VALUES(N'195-028', N'Calvin', N'Khone', Personnel.CreateUsername(N'Calvin', N'Khone'), Personnel.CreateUsername(N'Calvin', N'Khone') + N'@kolobank.com', 6.85, N'Cashier', N'(301) 839-4253', N'516 Linden Street Apt D2', N'Silver Spring');
GO
INSERT INTO Personnel.Employees(EmployeeNumber, FirstName, LastName, Username, EmailAddress, Title, WorkPhone, Extension, [Address], City, [State])
VALUES(N'271-799', N'Leonie', N'Vaughs', Personnel.CreateUsername(N'Leonie', N'Vaughs'), Personnel.CreateUsername(N'Leonie', N'Vaughs') + N'@kolobank.com', N'Cashier', N'(410) 653-1309', 616, N'1277 Cecil Maurice Av.', N'Baltimore', N'MD');
GO
INSERT INTO Personnel.Employees(EmployeeNumber, FirstName, LastName, Username, EmailAddress, Title, CanCreateNewAccount, WorkPhone, Extension, [Address], City, [State], HourlySalary)
VALUES(N'195-804', N'Sylvie', N'Stafford', Personnel.CreateUsername(N'Sylvie', N'Stafford'), Personnel.CreateUsername(N'Sylvie', N'Stafford') + N'@kolobank.com', N'Regional Manager', 1, N'(410) 653-1309', 208, N'273 S. Independence Ave.', N'Baltimore', N'MD', 36.22),
      (N'274-284', N'Herbert', N'Jerremies', Personnel.CreateUsername(N'Herbert', N'Jerremies'), Personnel.CreateUsername(N'Herbert', N'Jerremies') + N'@kolobank.com', N'Intern', 1, N'(410) 653-1309', 106, N'8254 12th St. N.E.', N'Washington', N'DC', 4.15);
GO
INSERT INTO Personnel.Employees(EmployeeNumber, FirstName, LastName, Username, EmailAddress, Title, WorkPhone, Extension, [Address], City, [State], HourlySalary)
VALUES(N'662-286', N'Lienev', N'Zbrnitz', Personnel.CreateUsername(N'Lienev', N'Zbrnitz'), Personnel.CreateUsername(N'Lienev', N'Zbrnitz') + N'@kolobank.com', N'Cashier', N'(410) 653-1309', 105, N'15328 Crystal St.', N'Baltimore', N'MD', 15.75),
	  (N'487-525', N'Paulin', N'Guerrero', Personnel.CreateUsername(N'Paulin', N'Guerrero'), Personnel.CreateUsername(N'Paulin', N'Guerrero') + N'@kolobank.com', N'Intern', N'(410) 653-1309', 226, N'4445 Blue Oak St. #6A', N'Baltimore', N'MD', 16.85),
	  (N'395-138', N'Plant', N'Waste', Personnel.CreateUsername(N'Plant', N'Waste'), Personnel.CreateUsername(N'Plant', N'Waste') + N'@kolobank.com', N'Head Teller', N'(410) 653-1309', 222, N'2888 Gwett Richards Av.', N'Glen Burnie', N'MD', 16.75),
	  (N'822-730', N'Steven', N'Chang', Personnel.CreateUsername(N'Steven', N'Chang'), Personnel.CreateUsername(N'Steven', N'Chang') + N'@kolobank.com', N'Accountant', N'(410) 653-1309', 128, N'308 Capitol Blvd', N'Baltimore', N'MD', 24.15),
	  (N'930-717', N'Abedi', N'Kombo', Personnel.CreateUsername(N'Abedi', N'Kombo'), Personnel.CreateUsername(N'Abedi', N'Kombo') + N'@kolobank.com', N'Shift Programmer', N'(410) 653-1309', 144, N'12 16th St. S. W.', N'Washington', N'DC', 10.02);
GO
INSERT INTO Personnel.Employees(EmployeeNumber, FirstName, LastName, Username, EmailAddress, Title, HourlySalary, WorkPhone, [Address], City)
VALUES(N'464-808', N'Mark', N'Georges', Personnel.CreateUsername(N'Mark', N'Georges'), Personnel.CreateUsername(N'Mark', N'Georges') + N'@kolobank.com', N'Intern', 7.12, N'(202) 719-7335', N'1101 Elon Rd', N'Takoma Park');
GO
INSERT INTO Personnel.Employees(EmployeeNumber, FirstName, LastName, Username, EmailAddress, Title, WorkPhone, Extension, [Address], City, [State], HourlySalary)
VALUES(N'119-814', N'Samuel', N'McCain', Personnel.CreateUsername(N'Samuel', N'McCain'), Personnel.CreateUsername(N'Samuel', N'McCain') + N'@kolobank.com', N'Cashier', N'(410) 653-1309', 142, N'9337 Cachet St', N'Baltimore', N'MD', 8.25),
	  (N'924-993', N'Kirsten', N'Roberts', Personnel.CreateUsername(N'Kirsten', N'Roberts'), Personnel.CreateUsername(N'Kirsten', N'Roberts') + N'@kolobank.com', N'Intern', N'(410) 653-1309', 164, N'1336 Philadelphia St.', N'Baltimore', N'MD', 8.05),
	  (N'220-826', N'William', N'Fake-Eye', Personnel.CreateUsername(N'William', N'Fake-Eye'), Personnel.CreateUsername(N'William', N'Fake-Eye') + N'@kolobank.com', N'Manager of Public Relations', N'(410) 653-1309', 152, N'58 North Assault St.', N'Baltimore', N'MD', 17.32),
	  (N'900-026', N'Roger', N'Lhoads', Personnel.CreateUsername(N'Roger', N'Lhoads'), Personnel.CreateUsername(N'Roger', N'Lhoads') + N'@kolobank.com', N'Cashier', N'(410) 653-1309', 174, N'8252 Eleven Sons Rd', N'College Park', N'MD', 10.24),
	  (N'270-707', N'Ada', N'Zeran', Personnel.CreateUsername(N'Ada', N'Zeran'), Personnel.CreateUsername(N'Ada', N'Zeran') + N'@kolobank.com', N'Administrative Assistant', N'(410) 653-1309', 132, N'992 White Horse Rd', N'Baltimore', N'MD', 15.48),
	  (N'272-883', N'Milicien', N'Drudge', Personnel.CreateUsername(N'Milicien', N'Drudge'), Personnel.CreateUsername(N'Milicien', N'Drudge') + N'@kolobank.com', N'Cashier', N'(410) 653-1309', 225, N'13622 Washington Blvd', N'Laurel', N'MD', 15.34);
GO
INSERT INTO Personnel.Employees(EmployeeNumber, FirstName, LastName, Username, EmailAddress, Title, WorkPhone, Extension, [Address], City, [State])
VALUES(N'559-528', N'Jeffrey', N'Rhoades', Personnel.CreateUsername(N'Jeffrey', N'Rhoades'), Personnel.CreateUsername(N'Jeffrey', N'Rhoades') + N'@kolobank.com', N'Cashier', N'(410) 653-1309', 194, N'3832 Great River Rd', N'Baltimore', N'MD');
GO
INSERT INTO Personnel.Employees(EmployeeNumber, FirstName, LastName, Username, EmailAddress, Title, CanCreateNewAccount, WorkPhone, Extension, [Address], City, [State], HourlySalary)
VALUES(N'385-225', N'Aaron', N'Kast', Personnel.CreateUsername(N'Aaron', N'Kast'), Personnel.CreateUsername(N'Aaron', N'Kast') + N'@kolobank.com', N'Accounts Manager', 1, N'(410) 653-1309', 214, N'2991 Justine Ave.', N'Baltimore', N'MD', 20.34),
      (N'717-028', N'Antoine', N'Lourde', Personnel.CreateUsername(N'Antoine', N'Lourde'), Personnel.CreateUsername(N'Antoine', N'Lourde') + N'@kolobank.com', N'Regional Assistant Manager', 1, N'(410) 653-1309', 206, N'720 Oak Tree Rd.', N'Columbia', N'MD', 15.62),
      (N'405-850', N'Lorraine', N'Kirkland', Personnel.CreateUsername(N'Lorraine', N'Kirkland'), Personnel.CreateUsername(N'Lorraine', N'Kirkland') + N'@kolobank.com', N'Assistant Manager', 1, N'(410) 653-1309', 136, N'104 G St. S. E.', N'Washington', N'DC', 22.86);
GO
*/
-- Records for Transactions.Deposits
/*
INSERT INTO Transactions.Deposits(LocationID, EmployeeID, CustomerID, DepositDate, DepositAmount, Notes)
VALUES(1, 1, 1, N'1/12/2011', 250.00, N'New Account'),
      (1, 4, 6, N'1/14/2011', 500.00, N'New Account'),
      (3, 3, 8, N'1/12/2011', 50.00, N'New Account'),
      (5, 6, 2, N'1/15/2011', 740.00, N'New Account'),
      (1, 2, 5, N'1/18/2011', 1350.00, N'New Account');
GO
INSERT INTO Transactions.Deposits(LocationID, EmployeeID, CustomerID, DepositDate, DepositAmount)
VALUES(3, 1, 8, N'1/12/2011', 350.00),
      (1, 3, 3, N'1/13/2011', 125.00),
      (2, 4, 1, N'1/14/2011', 2500.00),
      (3, 7, 1, N'1/22/2011', 200.00);
GO
INSERT INTO Transactions.Deposits(LocationID, EmployeeID, CustomerID, DepositDate, DepositAmount, Notes)
VALUES(2, 5, 4, N'1/18/2011', 750.00, N'New Account');
GO
INSERT INTO Transactions.Deposits(LocationID, EmployeeID, CustomerID, DepositDate, DepositAmount)
VALUES(2, 8, 7, N'1/10/2011', 250.00);
GO
INSERT INTO Transactions.Deposits(LocationID, EmployeeID, CustomerID, DepositDate, DepositAmount, Notes)
VALUES(4, 10, 9, N'1/12/2011', 3200.00, N'New Account'),
      (3, 12, 8, N'1/22/2011', 1850.00, N'New Account');
GO
INSERT INTO Transactions.Deposits(LocationID, EmployeeID, CustomerID, DepositDate, DepositAmount)
VALUES(1, 1, 1, N'1/24/2011', 220.58),
      (2, 4, 3, N'2011-01-24', 1250.0),
      (4, 2, 6, N'2011-01-25', 775.35),
      (1, 1, 5, N'1/25/2011', 3155.00);
GO
*/
-- Records for Transactions.Withdrawals
/*
INSERT INTO Transactions.Withdrawals(LocationID, EmployeeID, CustomerID, WithdrawalDate, WithdrawalAmount)
VALUES(5, 1, 1, N'1/18/2011', 80.00),
      (3, 3, 4, N'2/6/2011', 200.00),
      (2, 4, 6, N'1/26/2011', 80.00),
      (4, 2, 2, N'1/13/2011', 100.00),
      (1, 5, 3, N'1/18/2011', 100.00),
      (1, 4, 6, N'2/12/2011', 20.00),
      (3, 2, 8, N'1/20/2011', 300.00),
      (5, 6, 1, N'2/20/2011', 300.00),
      (6, 1, 5, N'1/24/2011', 200.00),
      (1, 3, 4, N'2/6/2011', 60.00);
GO
*/
-- Records for Transactions.CheckCashing
/*
INSERT INTO Transactions.CheckCashing(LocationID, EmployeeID, CustomerID, CheckNumber,
    Recipient, DateProcessed, CheckAmount)
VALUES(1, 4, 2, 100, N'Washington Electric Company', N'3/15/2011', 124.38);
GO
INSERT INTO Transactions.CheckCashing(LocationID, EmployeeID, CustomerID, CheckNumber,
    Recipient, DateProcessed, CheckAmount)
VALUES(4, 3, 1, 101, N'Barriston Communications', N'3/23/2011', 275.85);
GO
INSERT INTO Transactions.CheckCashing(LocationID, EmployeeID, CustomerID, CheckNumber,
    Recipient, DateProcessed, CheckAmount, HasEnoughFunds, 
    ServiceChargeAmount, Notes)
VALUES(1, 4, 3, 100, N'Route 1 Electronics', N'3/22/2011', 45.65, 0, 25, 
    N'A check was presented with no sufficient funds. A service charge was applied against the account and a post mail was sent to the customer.');
GO
INSERT INTO Transactions.CheckCashing(LocationID, EmployeeID, CustomerID, CheckNumber,
    Recipient, DateProcessed, CheckAmount)
VALUES(4, 2, 6, 122, N'BooksOnline.com', N'3/16/2011', 64.25);
GO
INSERT INTO Transactions.CheckCashing(LocationID, EmployeeID, CustomerID, CheckNumber,
    Recipient, DateProcessed, CheckAmount, HasEnoughFunds, 
    ServiceChargeAmount, Notes)
VALUES(5, 6, 1, 102, N'Self', N'4/10/2011', 92.45, 0, 25.00,
    N'A check came for the account but there were not enough funds. A service charge of $25 was applied to the account and a mail was sent to the customer.');
GO
*/