-- Kolo Bank 1 --
-- Kolo Bank 1 --
-- =============================================
-- Database: KoloBank1
-- Author: FunctionX
-- Date Created: Sunday 22 December 2010
-- Updated: Wednesday 22 December 2012
-- =============================================
USE master
GO
-- Drop the database if it already exists
IF EXISTS (
SELECT name
FROM sys.databases
WHERE name = N'KoloBank1'
)
DROP DATABASE KoloBank1
GO
CREATE DATABASE KoloBank1
GO
USE KoloBank1
GO
CREATE SCHEMA Management;
GO
CREATE SCHEMA Accounts;
GO
CREATE SCHEMA Accounting;
GO
CREATE SCHEMA Transactions;
GO
-- ================================================================================
-- Database: KoloBank1
-- Table: Management.Locations
-- Description: A location is a branch where the banks operations can be performed
-- ================================================================================
CREATE TABLE Management.Locations
(
LocationCode nvarchar(10) UNIQUE NOT NULL,
Location nvarchar(50) NOT NULL,
[Address] nvarchar(50),
City nvarchar(50),
[State] nvarchar(50),
ZIPCode nvarchar(12),
Country nvarchar(100) default N'USA',
Notes nvarchar(max) NULL,
CONSTRAINT PK_Locations PRIMARY KEY (LocationCode)
);
GO
CREATE TABLE Management.Employees
(
EmployeeNumber nvarchar(10) not null,
FirstName nvarchar(25),
MiddleName nvarchar(25),
LastName nvarchar(25) NOT NULL,
LocationCode nvarchar(10),
Title nvarchar(50),
CanCreateAccount bit,
EmailAddress nvarchar(50),
WorkPhone nvarchar(20),
Extension smallint,
[Address] nvarchar(50),
City nvarchar(40),
[State] nvarchar(40),
ZIPCode nvarchar(12),
Country nvarchar(50) default N'USA',
PhoneNumber nvarchar(20),
HourlySalary money,
Notes nvarchar(max),
CONSTRAINT PK_Employees PRIMARY KEY (EmployeeNumber),
CONSTRAINT FK_EmployeesLocations FOREIGN KEY(LocationCode) REFERENCES Management.Locations(LocationCode)
);
GO
-- ================================================================================
-- Database: KoloBank1
-- Table: Management.AccountTypes
-- Description: The account types are checking, saving or certificate of deposit
-- ================================================================================
CREATE TABLE Management.AccountTypes
(
AccountType nvarchar(25) NOT NULL,
Notes nvarchar(max) NULL,
CONSTRAINT PK_AccountTypes PRIMARY KEY (AccountType)
);
GO
CREATE TABLE Accounts.Customers
(
AccountNumber nvarchar(20) not null,
DateCreated date,
EmployeeNumber nvarchar(10),
AccountType nvarchar(25),
FirstName nvarchar(25),
MiddleName nvarchar(25),
LastName nvarchar(25) NOT NULL,
[Address] nvarchar(50) NOT NULL,
City nvarchar(40) NOT NULL,
[State] nvarchar(40) NOT NULL,
ZIPCode nvarchar(12) NOT NULL,
PhoneNumber nvarchar(20) NOT NULL,
WorkPhone nvarchar(20),
DateUpdated date,
AccountStatus nvarchar(25),
Notes nvarchar(max),
Constraint PK_Customers PRIMARY KEY (AccountNumber),
Constraint FK_AccountCreator Foreign Key(EmployeeNumber) References Management.Employees(EmployeeNumber),
Constraint FK_TypeOfAccount Foreign Key(AccountType) References Management.AccountTypes(AccountType)
);
GO
CREATE TABLE Transactions.Deposits
(
DepositID int identity(1, 1) not null,
LocationCode nvarchar(10) not null,
EmployeeNumber nvarchar(10),
AccountNumber nvarchar(20) not null,
DepositDate date not null,
DepositAmount money not null,
Notes nvarchar(max),
CONSTRAINT PK_Deposits PRIMARY KEY (DepositID),
Constraint FK_DepositLocation Foreign Key(LocationCode) References Management.Locations(LocationCode),
Constraint FK_Clerk Foreign Key(EmployeeNumber) References Management.Employees(EmployeeNumber),
Constraint FK_CustomerAccount Foreign Key(AccountNumber) References Accounts.Customers(AccountNumber)
);
GO
CREATE TABLE Transactions.Withdrawals
(
WithdrawalD int identity(1, 1) NOT NULL,
LocationCode nvarchar(10) NOT NULL,
EmployeeNumber nvarchar(10),
AccountNumber nvarchar(20) NOT NULL,
WithdrawalDate date NOT NULL,
WithdrawalAmount money NOT NULL,
ServiceChargeApplied money,
Notes nvarchar(max),
CONSTRAINT PK_Withdrawals PRIMARY KEY(WithdrawalD),
Constraint FK_WithdrawlLocation Foreign Key(LocationCode) References Management.Locations(LocationCode),
Constraint FK_Employee Foreign Key(EmployeeNumber) References Management.Employees(EmployeeNumber),
Constraint FK_AccountNumber Foreign Key(AccountNumber) References Accounts.Customers(AccountNumber)
);
GO
CREATE TABLE Transactions.CheckCashing
(
CheckCashingID int identity(1, 1) NOT NULL,
LocationCode nvarchar(10) not null,
EmployeeNumber nvarchar(10),
AccountNumber nvarchar(20) 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(LocationCode) References Management.Locations(LocationCode),
Constraint FK_CashingEmployee Foreign Key(EmployeeNumber) References Management.Employees(EmployeeNumber),
Constraint FK_CashingCustomer Foreign Key(AccountNumber) References Accounts.Customers(AccountNumber),
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
INSERT INTO Management.Locations(LocationCode, Location, City, [State], ZIPCode)
VALUES(N'0ALXML', N'ATM Alexandria Mall West', N'Alexandria', N'VA', N'22314'),
(N'0CLGPK', N'ATM UMD College Park - Math Building', N'College Park', N'MD', N'20314');
GO
INSERT INTO Management.Locations(LocationCode, Location, City, [State], ZIPCode, Notes)
VALUES(N'0GRNML', N'ATM Greenbelt Mall', N'Greenbelt', N'MD', N'20770', N'ATM in Greenbelt Mall');
GO
INSERT INTO Management.Locations(LocationCode, Location, City, [State], ZIPCode)
VALUES(N'0LNFPL', N'ATM L''Enfant Plaza', N'Washington', N'DC', N'20024'),
(N'0MNGML', N'ATM Montgomery Mall - North Entrance', N'Bethesda', N'MD', N'20817'),
(N'0SSCTPL', N'ATM Silver Spring City Place', N'Silver Spring', N'MD', N'20910'),
(N'0SSMTR', N'ATM Silver Spring Metro Station', N'Silver Spring', N'MD', N'20910');
GO
INSERT INTO Management.Locations(LocationCode, Location, City, [State], ZIPCode, Notes)
VALUES(N'0UNSDC', N'ATM Union Station', N'Washington', N'DC', N'20002', N'ATM inside Union Station in DC'),
(N'0WPLZM', N'ATM Wheaton Plaza Mall', N'Silver Spring', N'MD', N'20906', N'ATM inside Wheaton Plaza Mall');
GO
INSERT INTO Management.Locations(LocationCode, Location, City, [State], ZIPCode)
VALUES(N'0WTFML', N'ATM White Flint Mall', N'Rockville', N'MD', N'20895');
GO
INSERT INTO Management.Locations(LocationCode, Location, [Address], City, [State], ZIPCode)
VALUES(N'ALXJPZ', N'Junino Plaza', N'3382 Junino Ave', N'Alexandria', N'VA', N'22132'),
(N'CPKUMD', N'University of Maryland', N'808 Vieira Drive', N'College Park', N'MD', N'20707'),
(N'DCK10S', N'Kennedy & 10th Street', N'10368 Sesame Lolane Rd N.E.', N'Washington', N'DC', N'20014'),
(N'GTWMST', N'Georgetown M Street', N'2602 M Street N.W.', N'Washington', N'DC', N'20008'),
(N'SLVSSL', N'Seven Lock Corner', N'3925 Euler Ave', N'Silver Spring', N'MD', N'20904'),
(N'WHTFLT', N'White Flint Mall', N'12044 Rte 255', N'Rockville', N'MD', N'20854');
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
INSERT INTO Management.Employees(EmployeeNumber, FirstName, LastName)
VALUES(N'000-100', N'ATM', N'Teller Machine');
GO
INSERT INTO Management.Employees(EmployeeNumber, FirstName, LastName, Title)
VALUES(N'000-200', N'Automatic', N'Transaction', N'Automatic Computer Transaction'),
(N'111-111', N'Online', N'Web', N'Online Transaction');
GO
INSERT INTO Management.Employees(EmployeeNumber, FirstName, MiddleName, LastName, LocationCode, Title, [Address], City, [State], ZIPCode, HourlySalary)
VALUES(N'482-799', N'Annette', N'Joan', N'Roberts', N'GTWMST', N'Cashier', N'1277 Cecil Maurice Av. NW', N'Washington', N'DC', N'20004', 14.88);
GO
INSERT INTO Management.Employees(EmployeeNumber, FirstName, LastName, LocationCode, Title, CanCreateAccount, [Address], City, [State], ZIPCode)
VALUES(N'715-204', N'Adam', N'Ramirez', N'DCK10S', N'Branch Manager', 1, N'4606 Eulaw Ave. N.W.', N'Washington', N'DC', N'20008');
GO
INSERT INTO Management.Employees(EmployeeNumber, FirstName, MiddleName, LastName, LocationCode, Title, [Address], City, [State], ZIPCode, HourlySalary)
VALUES(N'722-286', N'Donald', N'Anthony', N'Fisher', N'SLVSSL', N'Public Relations Manager', N'58 North Assault St.', N'Takoma Park', N'MD', N'20910', 25.32);
GO
INSERT INTO Management.Employees(EmployeeNumber, FirstName, LastName, LocationCode, Title, CanCreateAccount, [Address], City, [State], ZIPCode, HourlySalary)
VALUES(N'928-495', N'Ryan', N'Duck', N'SLVSSL', N'General Manager', 1, N'10244 Webster Rd', N'Rockville', N'MD', N'20854', 42.74);
GO
INSERT INTO Management.Employees(EmployeeNumber, FirstName, LastName, LocationCode, Title, [Address], City, [State], ZIPCode, HourlySalary)
VALUES(N'279-377', N'Michael', N'Dobmeyer', N'SLVSSL', N'Shift Programmer', N'12 16th St. S. W.', N'Washington', N'DC', N'20008', 20.56);
GO
INSERT INTO Management.Employees(EmployeeNumber, FirstName, LastName, LocationCode, Title, CanCreateAccount, [Address], City, [State], ZIPCode, HourlySalary)
VALUES(N'507-728', N'Timothy', N'Wray', N'GTWMST', N'Branch Manager', 1, N'720 Oak Tree Rd.', N'Columbia', N'MD', N'21044', 35.62);
GO
INSERT INTO Management.Employees(EmployeeNumber, FirstName, MiddleName, LastName, LocationCode, Title, [Address], City, [State], ZIPCode, HourlySalary)
VALUES(N'927-395', N'Luisa', N'Judie', N'Vanecek', N'GTWMST', N'Cashier', N'8082 16th St N.W.', N'Washington', N'DC', N'20002', 16.75),
(N'736-626', N'Annabelle', N'Jeannette', N'Coen', N'SLVSSL', N'Cashier', N'15328 Crystal St.', N'Frederick', N'MD', N'21701', 15.75);
GO
INSERT INTO Management.Employees(EmployeeNumber, FirstName, LastName, LocationCode, Title, CanCreateAccount, [Address], City, [State], ZIPCode, HourlySalary)
VALUES(N'952-846', N'John', N'Possemato', N'SLVSSL', N'Assistant Manager', 1, N'8254 12th St. N.E.', N'Washington', N'DC', N'20004', 24.12);
GO
INSERT INTO Management.Employees(EmployeeNumber, FirstName, LastName, LocationCode, Title, [Address], City, [State], ZIPCode)
VALUES(N'500-284', N'Alexa', N'Charles', N'DCK10S', N'Cashier', N'914 Jamieson St N.W.', N'Washington', N'DC', N'20012');
GO
INSERT INTO Management.Employees(EmployeeNumber, FirstName, LastName, LocationCode, Title, CanCreateAccount, [Address], City, [State], ZIPCode, HourlySalary)
VALUES(N'294-075', N'Luis', N'Velker', N'ALXJPZ', N'Branch Manager', 1, N'9502 Copher Ave', N'Alexandria', N'VA', N'22312', 28.75);
GO
INSERT INTO Management.Employees(EmployeeNumber, FirstName, LastName, LocationCode, Title, CanCreateAccount, [Address], City, [State], ZIPCode)
VALUES(N'461-842', N'Dorrin', N'Vive', N'CPKUMD', N'Branch Manager', 1, N'273 S. Independence Ave.', N'College Park', N'MD', N'20747');
GO
INSERT INTO Management.Employees(EmployeeNumber, FirstName, MiddleName, LastName, LocationCode, Title, [Address], City, [State], ZIPCode, HourlySalary)
VALUES(N'284-725', N'Wanda', N'Mary', N'Dundon', N'ALXJPZ', N'Cashier', N'614 Simpson Ave', N'Takoma Park', N'MD', N'20912', 15.95),
(N'481-114', N'Samuel', N'Arthur', N'Lansing', N'CPKUMD', N'Cashier', N'9337 Cachet St', N'Beltsville', N'MD', N'20705', 15.25),
(N'624-825', N'Luke', N'Andrew', N'Parkinson', N'SLVSSL', N'Intern', N'4445 Blue Oak St. #6A', N'Silver Spring', N'MD', N'20906', 14.35);
GO
INSERT INTO Management.Employees(EmployeeNumber, FirstName, MiddleName, LastName, LocationCode, Title, CanCreateAccount, [Address], City, [State], ZIPCode, HourlySalary)
VALUES(N'552-884', N'Joy', N'Maureen', N'Donovan', N'ALXJPZ', N'Customer Accounts Manager', 1, N'845 Arcadia Ave. #1512', N'Alexandria', N'VA', N'22314', 29.55);
GO
INSERT INTO Management.Employees(EmployeeNumber, FirstName, LastName, LocationCode, Title, CanCreateAccount, [Address], City, [State], ZIPCode, HourlySalary)
VALUES(N'492-081', N'Jeffrey', N'Kilborne', N'GTWMST', N'Accounts Manager', 1, N'308 Capitol Blvd', N'McLean', N'VA', N'22101', 26.15);
GO
INSERT INTO Management.Employees(EmployeeNumber, FirstName, MiddleName, LastName, LocationCode, Title, [Address], City, [State], ZIPCode, HourlySalary)
VALUES(N'248-552', N'Michael', N'William', N'Olney', N'CPKUMD', N'Cashier', N'3832 Great River Rd', N'Silver Spring', N'MD', N'20906', 17.52),
(N'503-938', N'Michelle', N'Deborah', N'Stephenson', N'SLVSSL', N'Head Teller', N'2888 Gwett Richards Av.', N'Glen Burnie', N'MD', N'21060', 21.75),
(N'284-005', N'Anne', N'Laura', N'Wine', N'SLVSSL', N'Cashier', N'4137 Chivas Crt', N'Silver Spring', N'MD', N'20910', 16.68);
GO
INSERT INTO Management.Employees(EmployeeNumber, FirstName, MiddleName, LastName, LocationCode, Title, CanCreateAccount, [Address], City, [State], ZIPCode, HourlySalary)
VALUES(N'533-825', N'Aaron', N'Robert', N'Kast', N'WHTFLT', N'Accounts Manager', 1, N'2991 Justine Ave.', N'Rockville', N'MD', N'20854', 24.34);
GO
INSERT INTO Management.Employees(EmployeeNumber, FirstName, MiddleName, LastName, LocationCode, Title, [Address], City, [State], ZIPCode, HourlySalary)
VALUES(N'712-083', N'Wendy', N'Noella', N'Huntsmann', N'WHTFLT', N'Cashier', N'13622 Washington Blvd', N'Laurel', N'MD', N'20707', 18.34);
GO
INSERT INTO Management.Employees(EmployeeNumber, FirstName, MiddleName, LastName, LocationCode, Title, CanCreateAccount, [Address], City, [State], ZIPCode, HourlySalary)
VALUES(N'209-400', N'Krista', N'Daniela', N'Cole', N'SLVSSL', N'Branch Manager', 1, N'1026 Jacktown St', N'Baltimore', N'MD', N'21205', 28.55);
GO
INSERT INTO Management.Employees(EmployeeNumber, FirstName, LastName, LocationCode, Title, [Address], City, [State], ZIPCode, HourlySalary)
VALUES(N'660-026', N'Lucas', N'Frieddle', N'SLVSSL', N'Cashier', N'8252 Eleven Sons Rd', N'College Park', N'MD', N'20740', 16.24);
GO
INSERT INTO Management.Employees(EmployeeNumber, FirstName, MiddleName, LastName, LocationCode, Title, CanCreateAccount, [Address], City, [State], ZIPCode, HourlySalary)
VALUES(N'280-082', N'Geoffrey', N'Robert', N'Coleman', N'SLVSSL', N'Head Cashier', 1, N'828 John Booker St', N'Chevy Chase', N'MD', N'20851', 22.82),
(N'484-050', N'Marianne', N'Becky', N'Oslin', N'WHTFLT', N'Assistant Manager', 1, N'104 G St. S. E.', N'Washington', N'DC', N'20012', 26.86);
GO
INSERT INTO Management.Employees(EmployeeNumber, FirstName, LastName, LocationCode, Title, [Address], City, [State], ZIPCode, HourlySalary)
VALUES(N'462-777', N'Ada', N'Zeran', N'SLVSSL', N'Administrative Assistant', N'992 White Horse Rd', N'Hyattsville', N'MD', N'20782', 15.48);
GO
INSERT INTO Management.Employees(EmployeeNumber, FirstName, MiddleName, LastName, LocationCode, Title, [Address], City, [State], ZIPCode, HourlySalary)
VALUES(N'829-313', N'Simon', N'Eddy', N'Michaels', N'CPKUMD', N'Cashier', N'1336 Philadelphia St.', N'Baltimore', N'MD', N'21206', 18.05);
GO