-- =============================================
-- Database:     KoloBank2
-- 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'KoloBank2'
)
DROP DATABASE KoloBank2
GO
CREATE DATABASE KoloBank2
GO

USE KoloBank2
GO
CREATE SCHEMA Management;
GO
CREATE SCHEMA Accounting;
GO
CREATE SCHEMA Personnel;
GO
CREATE SCHEMA Transactions;
GO
-- ================================================================================
-- Database:	KoloBank2
-- 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

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'936486', N'Washington', N'DC', N'20008'),
	  (N'824405', N'Silver Spring', N'MD', N'20906'),
	  (N'429025', N'Rockville', N'MD', N'20854');
GO
INSERT INTO Management.Locations(LocationCode, City, [State])
VALUES(N'703648', N'Washington', N'DC');
GO

-- ================================================================================
-- Database:    KoloBank2
-- 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
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

-- ================================================================================
-- Database:	KoloBank2
-- 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
);
INSERT INTO Personnel.StartingSalaries
VALUES(N'Base', 10.00),
	  (N'Intern', 12.35),
	  (N'Regular', 14.50),
	  (N'Manager', 20.00);
GO
-- ================================================================================
-- Database:	KoloBank2
-- 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
-- ================================================================================
-- Database: KoloBank2
-- 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
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

-- ================================================================================
-- Database: KoloBank2
-- Table:    Customers
-- ================================================================================
CREATE TABLE Management.Customers
(
    CustomerID int Identity(1, 1) NOT NULL,
    DateCreated date,
    AccountNumber nvarchar(20),
    AccountTypeID int,
    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) NOT NULL,
    WorkPhone nvarchar(20),
    WorkExtension nvarchar(6),
    DateUpdated date,
    Notes nvarchar(max),
    Constraint FK_TypeOfAccount Foreign Key(AccountTypeID) References Management.AccountTypes(AccountTypeID),
    Constraint PK_Customers PRIMARY KEY (CustomerID)
);
GO

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

-- ================================================================================
-- Database: KoloBank2
-- Table:    Transactions.Deposits
-- ================================================================================
CREATE TABLE Transactions.Deposits
(
    DepositID int identity(1, 1) NOT NULL,
    LocationID int NOT NULL,
    EmployeeID int,
    CustomerID int NOT NULL,
    DepositDate date NOT NULL,
    DepositAmount money NOT NULL,
    Notes nvarchar(max),
    Constraint FK_DepositLocation Foreign Key(LocationID) References Management.Locations(LocationID),
    Constraint FK_Clerk Foreign Key(EmployeeID) References Personnel.Employees(EmployeeID),
    Constraint FK_CustomerAccount Foreign Key(CustomerID) References Management.Customers(CustomerID),
    CONSTRAINT PK_Deposits PRIMARY KEY (DepositID)
);
GO
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
-- ================================================================================
-- Database: KoloBank2
-- Table:    Transactions.Withdrawals
-- ================================================================================
CREATE TABLE Transactions.Withdrawals
(
    WithdrawalD int identity(1, 1) NOT NULL,
    LocationID int NOT NULL,
    EmployeeID int,
    CustomerID int NOT NULL,
    WithdrawalDate date NOT NULL,
    WithdrawalAmount money NOT NULL,
    ServiceChargeApplied money default 0.00,
    Notes nvarchar(max),
    Constraint FK_WithdrawlLocation Foreign Key(LocationID) References Management.Locations(LocationID),
    Constraint FK_Employee Foreign Key(EmployeeID) References Personnel.Employees(EmployeeID),
    Constraint FK_AccountNumber Foreign Key(CustomerID) References Management.Customers(CustomerID),
    CONSTRAINT PK_Withdrawals PRIMARY KEY(WithdrawalD)
);
GO
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
-- ================================================================================
-- Database: KoloBank2
-- Table:    Transactions.CheckCashing
-- ================================================================================
CREATE TABLE Transactions.CheckCashing
(
    CheckCashingID int identity(1, 1) NOT NULL,
    LocationID int NOT NULL,
    EmployeeID int,
    CustomerID int 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 FK_CashingLocation Foreign Key(LocationID) References Management.Locations(LocationID),
    Constraint FK_CashingEmployee Foreign Key(EmployeeID) References Personnel.Employees(EmployeeID),
    Constraint FK_CashingCustomer Foreign Key(CustomerID) References Management.Customers(CustomerID),
    CONSTRAINT PK_CheckCashing PRIMARY KEY (CheckCashingID)
);
GO
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