 |
Yugo National Bank
|
|
-- =============================================
-- Database: YugoNationalBank1
-- Author: FunctionX
-- Date Created: Monday 09 April 2007
-- =============================================
USE master
GO
-- Drop the database if it already exists
IF EXISTS (
SELECT name
FROM sys.databases
WHERE name = N'YugoNationalBank1'
)
DROP DATABASE YugoNationalBank1
GO
CREATE DATABASE YugoNationalBank1
GO
-- =========================================
-- Database: YugoNationalBank1
-- Table: Locations
-- =========================================
USE YugoNationalBank1
GO
IF OBJECT_ID('dbo.Locations', 'U') IS NOT NULL
DROP TABLE dbo.Locations
GO
CREATE TABLE Locations
(
LocationID int Identity(1,1) NOT NULL,
LocationCode varchar(10) NOT NULL,
Address varchar(120),
City varchar(50),
State varchar(50),
ZIPCode varchar(12),
Country varchar(100) default 'USA',
Notes text NULL,
CONSTRAINT PK_Locations PRIMARY KEY (LocationID)
);
GO
INSERT INTO Locations(LocationCode, Address, City, State, ZIPCode)
VALUES('826005', '3925 Euler Ave', 'Silver Spring', 'MD', '20904');
GO
INSERT INTO Locations(LocationCode, City, State, ZIPCode)
VALUES('249615', 'Alexandria', 'VA', '22132');
GO
INSERT INTO Locations(LocationCode, City, State, ZIPCode)
VALUES('936486', 'Washington', 'DC', '20008');
GO
INSERT INTO Locations(LocationCode, City, State, ZIPCode)
VALUES('824405', 'Silver Spring', 'MD', '20906');
GO
INSERT INTO Locations(LocationCode, City, State, ZIPCode)
VALUES('429025', 'Rockville', 'MD', '20854');
GO
INSERT INTO Locations(LocationCode, City, State)
VALUES('703648', 'Washington', 'DC');
GO
-- =========================================
-- Database: YugoNationalBank1
-- Table: AccountTypes
-- =========================================
USE YugoNationalBank1
GO
IF OBJECT_ID('dbo.AccountTypes', 'U') IS NOT NULL
DROP TABLE dbo.AccountTypes
GO
CREATE TABLE AccountTypes
(
AccountTypeID int Identity(1,1) NOT NULL,
AccountType varchar(40) NOT NULL,
Notes text NULL,
CONSTRAINT PK_AccountTypes PRIMARY KEY (AccountTypeID)
);
GO
INSERT INTO AccountTypes (AccountType, Notes)
VALUES('Checking', 'Used to regularly deposit and withdraw money at will');
INSERT INTO AccountTypes (AccountType, Notes)
VALUES('Saving', 'Used to deposit money to save it but hardly withdraw it');
INSERT INTO AccountTypes (AccountType, Notes)
VALUES('CD', 'Certificate of Deposit');
GO
-- =========================================
-- Database: YugoNationalBank1
-- Table: Employees
-- =========================================
IF OBJECT_ID('dbo.Employees', 'U') IS NOT NULL
DROP TABLE dbo.Employees
GO
CREATE TABLE Employees
(
EmployeeID int identity(1,1) NOT NULL,
EmployeeNumber char(6),
FirstName varchar(32),
LastName varchar(32) NOT NULL,
Title varchar(50),
CanCreateNewAccount bit,
EmailAddress varchar(50),
WorkPhone varchar(20),
Extension smallint,
Address varchar(120),
City varchar(40),
State varchar(40),
ZIPCode varchar(12),
Country varchar(50),
HomePhone varchar(20),
HourlySalary smallmoney,
Notes text,
CONSTRAINT PK_Employees PRIMARY KEY (EmployeeID)
);
GO
INSERT INTO Employees(EmployeeNumber, FirstName, LastName, Title, CanCreateNewAccount, WorkPhone, Extension, Address, City, State, Country, HourlySalary)
VALUES('22082', 'Matt', 'Yuen', 'Head Cashier', 1, '(410) 653-1309', 228, '828 John Booker St', 'Baltimore', 'MD', 'USA', 22.82);
INSERT INTO Employees(EmployeeNumber, FirstName, LastName, Title, CanCreateNewAccount, WorkPhone, Extension, Address, City, State, Country, HourlySalary)
VALUES('46288', 'Catherine', 'Marconi', 'Customer Account Manager', 1, '(410) 653-1309', 145, '845 Arcadia Ave. #1512', 'Townson', 'MD', 'USA', 22.55);
INSERT INTO Employees(EmployeeNumber, FirstName, LastName, Title, WorkPhone, Extension, Address, City, State, Country, HourlySalary)
VALUES('27199', 'Leonie', 'Ankoma', 'Cashier', '(410) 653-1309', 616, '1277 Cecil Maurice Av.', 'Baltimore', 'MD', 'USA', 14.88);
INSERT INTO Employees(EmployeeNumber, FirstName, LastName, Title, CanCreateNewAccount, WorkPhone, Extension, Address, City, State, Country, HourlySalary)
VALUES('19804', 'Sylvie', 'Young', 'Regional Manager', 1, '(410) 653-1309', 208, '273 S. Independence Ave.', 'Baltimore', 'MD', 'USA', 16.22);
INSERT INTO Employees(EmployeeNumber, FirstName, LastName, Title, CanCreateNewAccount, WorkPhone, Extension, Address, City, State, Country, HourlySalary)
VALUES('27284', 'Andy', 'Holland', 'Assistant Manager', 1, '(410) 653-1309', 106, '8254 12th St. N.E.', 'Washington', 'DC', 'USA', 24.12);
INSERT INTO Employees(EmployeeNumber, FirstName, LastName, Title, WorkPhone, Extension, Address, City, State, Country, HourlySalary)
VALUES('66286', 'Lienev', 'Zbrnitz', 'Cashier', '(410) 653-1309', 105, '15328 Crystal St.', 'Baltimore', 'MD', 'USA', 15.75);
INSERT INTO Employees(EmployeeNumber, FirstName, LastName, Title, WorkPhone, Extension, Address, City, State, Country, HourlySalary)
VALUES('48725', 'Paulin', 'Santiago', 'Intern', '(410) 653-1309', 226, '4445 Blue Oak St. #6A', 'Baltimore', 'MD', 'USA', 16.35);
INSERT INTO Employees(EmployeeNumber, FirstName, LastName, Title, WorkPhone, Extension, Address, City, State, Country, HourlySalary)
VALUES('39538', 'Plant', 'Waste', 'Head Teller', '(410) 653-1309', 222, '2888 Gwett Richards Av.', 'Glen Burnie', 'MD', 'USA', 16.75);
INSERT INTO Employees(EmployeeNumber, FirstName, LastName, Title, WorkPhone, Extension, Address, City, State, Country, HourlySalary)
VALUES('82730', 'Steven', 'Chang', 'Accountant', '(410) 653-1309', 128, '308 Capitol Blvd', 'Baltimore', 'MD', 'USA', 16.15);
INSERT INTO Employees(EmployeeNumber, FirstName, LastName, Title, WorkPhone, Extension, Address, City, State, Country, HourlySalary)
VALUES('93077', 'Abedi', 'Kombo', 'Shift Programmer', '(410) 653-1309', 144, '12 16th St. S. W.', 'Washington','DC', 'USA', 10.56);
INSERT INTO Employees(EmployeeNumber, FirstName, LastName, Title, WorkPhone, Extension, Address, City, State, Country, HourlySalary)
VALUES('11914','Samuel', 'McCain','Cashier', '(410) 653-1309', 142,'9337 Cachet St', 'Baltimore', 'MD','USA', 15.25);
INSERT INTO Employees(EmployeeNumber, FirstName, LastName, Title, WorkPhone, Extension, Address, City, State, Country, HourlySalary)
VALUES('92493', 'Kirsten', 'Roberts', 'Cashier', '(410) 653-1309', 164, '1336 Philadelphia St.', 'Baltimore', 'MD', 'USA', 18.05);
INSERT INTO Employees(EmployeeNumber, FirstName, LastName, Title, WorkPhone, Extension, Address, City, State, Country, HourlySalary)
VALUES('22086', 'William', 'Fake-Eye', 'Public Relations', '(410) 653-1309', 152, '58 North Assault St.', 'Baltimore', 'MD', 'USA',16.32);
INSERT INTO Employees(EmployeeNumber, FirstName, LastName, Title, WorkPhone, Extension, Address, City, State, Country, HourlySalary)
VALUES('90026', 'Roger', 'Lamy', 'Cashier', '(410) 653-1309', 174, '8252 Eleven Sons Rd', 'College Park', 'MD', 'USA', 10.24);
INSERT INTO Employees(EmployeeNumber, FirstName, LastName, Title, WorkPhone, Extension, Address, City, State, Country, HourlySalary)
VALUES('27707', 'Ada', 'Zeran', 'Administrative Assistant', '(410) 653-1309', 132, '992 White Horse Rd', 'Baltimore', 'MD', 'USA', 15.48);
INSERT INTO Employees(EmployeeNumber, FirstName, LastName, Title, WorkPhone, Extension, Address, City, State, Country, HourlySalary)
VALUES('27283', 'Milicien', 'Drudge', 'Cashier', '(410) 653-1309', 225, '13622 Washington Blvd', 'Laurel', 'MD', 'USA', 18.34);
INSERT INTO Employees(EmployeeNumber, FirstName, LastName, Title, CanCreateNewAccount, WorkPhone, Extension, Address, City, State, Country, HourlySalary)
VALUES('38525', 'Aaron', 'Kast', 'Accounts Manager', 1, '(410) 653-1309', 214, '2991 Justine Ave.', 'Baltimore', 'MD', 'USA', 12.34);
INSERT INTO Employees(EmployeeNumber, FirstName, LastName, Title, CanCreateNewAccount, WorkPhone, Extension, Address, City, State, Country, HourlySalary)
VALUES('71728', 'Antoine', 'Lourde', 'Regional Assistant Manager', 1, '(410) 653-1309', 206, '720 Oak Tree Rd.', 'Columbia', 'MD', 'USA', 15.62);
INSERT INTO Employees(EmployeeNumber, FirstName, LastName, Title, CanCreateNewAccount, WorkPhone, Extension, Address, City, State, Country, HourlySalary)
VALUES('40550', 'Lorraine', 'Kirkland','Assistant Manager', 1, '(410) 653-1309', 136,'104 G St. S. E.', 'Washington', 'DC','USA', 12.86);
INSERT INTO Employees(EmployeeNumber, FirstName, LastName, Title, WorkPhone, Extension, Address, City, State, Country, HourlySalary)
VALUES('55528','Jeffrey', 'Salomons','Cashier', '(410) 653-1309', 194,'3832 Great River Rd', 'Baltimore', 'MD','USA', 24.52
);
GO
-- =========================================
-- Database: YugoNationalBank1
-- Table: Customers
-- =========================================
IF OBJECT_ID('dbo.Customers', 'U') IS NOT NULL
DROP TABLE dbo.Customers
GO
CREATE TABLE Customers
(
CustomerID int Identity(1,1) NOT NULL,
DateCreated datetime,
AccountNumber varchar(20),
AccountTypeID int Constraint FK_TypeOfAccount References AccountTypes(AccountTypeID),
CustomerName varchar(50) NOT NULL,
Address varchar(120) NOT NULL,
City varchar(40) NOT NULL,
State varchar(40) NOT NULL,
ZIPCode varchar(12) NOT NULL,
Country varchar(50) DEFAULT('USA'),
EmailAddress varchar(50),
HomePhone varchar(20) NOT NULL,
WorkPhone varchar(20),
WorkExtension varchar(6),
DateUpdated smallDateTime,
Notes text,
CONSTRAINT PK_Customers PRIMARY KEY (CustomerID)
);
GO
INSERT INTO Customers(AccountNumber, CustomerName, Address, City, State, ZIPCode, Country, HomePhone, WorkPhone)
VALUES('28-3782-84', 'James Carlton Brokeridge', '1022 Arlington Rd', 'Arlington', 'VA', '20164', 'USA', '(703) 645-1492', '(703) 450-5577');
INSERT INTO Customers(AccountNumber, CustomerName, Address, City, State, ZIPCode, Country, HomePhone, WorkPhone)
VALUES('92-3782-43', 'Chrissy Arlene McMahon', '845 Arcadia Ave. #1512', 'Rockville', 'MD', '20872', 'USA', '(301) 684-2828', '(301) 723-1882' );
INSERT INTO Customers(AccountNumber, CustomerName, Address, City, State, ZIPCode, Country, HomePhone)
VALUES('38-4227-52', 'James Norris', '1277 Cecil Maurice Av.', 'Chevy Chase', 'MD', '20870', 'USA', '(301) 768-4024');
INSERT INTO Customers(AccountNumber, CustomerName, Address, City, State, ZIPCode, Country, HomePhone)
VALUES('68-6434-56', 'Eldridge Powers', '273 S. Independence Ave.', 'Alexandria', 'VA', '20185', 'USA', '(703) 622-7188');
INSERT INTO Customers(AccountNumber, CustomerName, Address, City, State, ZIPCode, Country, HomePhone, WorkPhone, WorkExtension)
VALUES('83-4654-77', 'Hobert Umbro Spampinato', '8254 12th St. N.E.', 'Washington', 'DC', '20008', 'USA', '(202) 927-1040', '(301) 726-8426', '116');
INSERT INTO Customers(AccountNumber, CustomerName, Address, City, State, ZIPCode, Country, HomePhone)
VALUES('47-4783-25', 'Gloria Aline Wright', '15328 Crystal St.', 'Hyattsville', 'MD', '20782', 'USA', '(301) 723-5656');
INSERT INTO Customers(AccountNumber, CustomerName, Address, City, State, ZIPCode, Country, HomePhone, WorkExtension)
VALUES('82-3763-24', 'Liliana Wellie Ortez', '4445 Blue Oak St. #6A', 'Chevy Chase', 'MD', '20875', 'USA', '(301) 821-4990', '2220');
INSERT INTO Customers(AccountNumber, CustomerName, Address, City, State, ZIPCode, Country, HomePhone, WorkPhone, WorkExtension)
VALUES('72-3474-24', 'Ornella Maiwand', '2888 Gwett Richards Av.', 'Rockville', 'MD', '20815', 'USA', '(301) 478-8244', '(301) 726-8224', '100');
INSERT INTO Customers(AccountNumber, CustomerName, Address, City, State, ZIPCode, Country, HomePhone, WorkExtension)
VALUES('34-5458-49', 'Leonel James Harbor', '308 Capitol Blvd', 'Washington', 'DC', '20010', 'USA', '(202) 439-2864', '114');
INSERT INTO Customers(AccountNumber, CustomerName, Address, City, State, ZIPCode, Country, HomePhone)
VALUES('29-4586-42', 'Albert Sonny Odonnell', '12 16th St. S. W.', 'Washington', 'DC', '20004', 'USA', '(301) 812-4442');
INSERT INTO Customers(AccountNumber, CustomerName, Address, City, State, ZIPCode, Country, HomePhone)
VALUES('68-3465-86', 'Howie Horace Fallace', '9337 Cachet St', 'Arlington', 'VA', '20140', 'USA', '(703) 554-8724');
INSERT INTO Customers(AccountNumber, CustomerName, Address, City, State, ZIPCode, Country, HomePhone)
VALUES('40-4658-63', 'Mellinda Bridges', '1336 Philadelphia St.', 'Takoma Park', 'MD', '20908', 'USA', '(301) 812-4428');
INSERT INTO Customers(AccountNumber, CustomerName, Address, City, State, ZIPCode, Country, HomePhone)
VALUES('56-8468-59', 'Barry Parrang', '58 North Assault St.', 'Chantilly', 'VA', '20102', 'USA', '(703) 622-6460');
INSERT INTO Customers(AccountNumber, CustomerName, Address, City, State, ZIPCode, Country, HomePhone, WorkExtension)
VALUES('94-7785-34', 'Ismail Zorbah', '8252 Eleven Sons Rd', 'Arlington', 'VA', '20150', 'USA', '(703) 681-9022', '1015');
INSERT INTO Customers(AccountNumber, CustomerName, Address, City, State, ZIPCode, Country, HomePhone)
VALUES('37-5764-86', 'Xavier Lenny Hereford', '992 White Horse Rd', 'Bethesda', 'MD', '20875', 'USA', '(301) 631-8228');
INSERT INTO Customers(AccountNumber, CustomerName, Address, City, State, ZIPCode, Country, HomePhone)
VALUES('34-9754-71', 'Marthe Helene Bradley', '13622 Washington Blvd', 'Laurel', 'MD', '20707', 'USA', '(301) 478-6602');
INSERT INTO Customers(AccountNumber, CustomerName, Address, City, State, ZIPCode, Country, HomePhone)
VALUES('72-9375-48', 'Jabouni Cabasco Toussey', '2991 Justine Ave.', 'Silver Spring', 'MD', '20912', 'USA', '(301) 872-8272');
INSERT INTO Customers(AccountNumber, CustomerName, Address, City, State, ZIPCode, Country, HomePhone)
VALUES('37-5490-64', 'Cherrine Leonie Horvath', '720 Oak Tree Rd.', 'Laurel', 'MD', '20707', 'USA', '(301) 549-0002');
INSERT INTO Customers(AccountNumber, CustomerName, Address, City, State, ZIPCode, Country, HomePhone)
VALUES('20-3454-96', 'Ophellie Wyman', '104 G St. S. E.', 'Washington', 'DC', '20005', 'USA', '(202) 622-8674');
INSERT INTO Customers(AccountNumber, CustomerName, Address, City, State, ZIPCode, Country, HomePhone, WorkExtension)
VALUES('76-5475-43', 'Joseph Patrick Honey', '3832 Great River Rd', 'Vienna', 'VA', '20171', 'USA', '(703) 350-2006', '102');
INSERT INTO Customers(AccountNumber, CustomerName, Address, City, State, ZIPCode, Country, HomePhone, WorkPhone)
VALUES('27-3457-49', 'Robert Daniel Luner', '802 Lilas Ave', 'Baltimore', 'MD', '21208', 'USA', '(410) 321-6730', '(410) 539-1135');
GO
-- =========================================
-- Database: YugoNationalBank1
-- Table: Deposits
-- =========================================
IF OBJECT_ID('dbo.Deposits', 'U') IS NOT NULL
DROP TABLE dbo.Deposits
GO
CREATE TABLE Deposits
(
DepositID int identity(1, 1) NOT NULL,
LocationID int Constraint FK_DepositLocation References Locations(LocationID) NOT NULL,
EmployeeID int Constraint FK_Clerk References Employees(EmployeeID),
CustomerID int Constraint FK_CustomerAccount References Customers(CustomerID) NOT NULL,
DepositDate smalldatetime NOT NULL,
DepositAmount smallmoney NOT NULL,
Notes text,
CONSTRAINT PK_Deposits PRIMARY KEY (DepositID)
);
GO
INSERT INTO Deposits(LocationID, EmployeeID, CustomerID,
DepositDate, DepositAmount, Notes)
VALUES(1, 1, 1, '1/2/2007', 250.00, 'New Account')
GO
INSERT INTO Deposits(LocationID, EmployeeID, CustomerID,
DepositDate, DepositAmount, Notes)
VALUES(1, 4, 6, '1/5/2007', 500.00, 'New Account')
GO
INSERT INTO Deposits(LocationID, EmployeeID, CustomerID,
DepositDate, DepositAmount, Notes)
VALUES(3, 3, 8, '1/2/2007', 50.00, 'New Account')
GO
INSERT INTO Deposits(LocationID, EmployeeID, CustomerID,
DepositDate, DepositAmount, Notes)
VALUES(5, 6, 2, '1/3/2007', 740.00, 'New Account')
GO
INSERT INTO Deposits(LocationID, EmployeeID, CustomerID,
DepositDate, DepositAmount, Notes)
VALUES(1, 2, 5, '1/4/2007', 1350.00, 'New Account')
GO
INSERT INTO Deposits(LocationID, EmployeeID, CustomerID,
DepositDate, DepositAmount)
VALUES(1, 3, 3, '1/8/2007', 125.00)
GO
INSERT INTO Deposits(LocationID, EmployeeID, CustomerID,
DepositDate, DepositAmount)
VALUES(3, 7, 1, '1/22/2007', 200.00)
GO
INSERT INTO Deposits(LocationID, EmployeeID, CustomerID,
DepositDate, DepositAmount, Notes)
VALUES(2, 5, 4, '1/4/2007', 750.00, 'New Account')
GO
INSERT INTO Deposits(LocationID, EmployeeID, CustomerID,
DepositDate, DepositAmount)
VALUES(2, 8, 7, '1/10/2007', 250.00)
GO
INSERT INTO Deposits(LocationID, EmployeeID, CustomerID,
DepositDate, DepositAmount, Notes)
VALUES(4, 10, 9, '1/2/2007', 3200.00, 'New Account')
GO
INSERT INTO Deposits(LocationID, EmployeeID, CustomerID,
DepositDate, DepositAmount, Notes)
VALUES(3, 12, 8, '1/22/2007', 1850.00, 'New Account')
GO
-- =========================================
-- Database: YugoNationalBank1
-- Table: Withdrawals
-- =========================================
IF OBJECT_ID('dbo.Withdrawals', 'U') IS NOT NULL
DROP TABLE dbo.Withdrawals
GO
CREATE TABLE Withdrawals
(
WithdrawalD int identity(1, 1) NOT NULL,
LocationID int Constraint FK_WithdrawlLocation References Locations(LocationID) NOT NULL,
EmployeeID int Constraint FK_Employee References Employees(EmployeeID),
CustomerID int Constraint FK_AccountNumber References Customers(CustomerID) NOT NULL,
WithdrawalDate smalldatetime NOT NULL,
WithdrawalAmount smallmoney NOT NULL,
ServiceChargeApplied smallmoney default 0.00,
Notes text,
CONSTRAINT PK_Withdrawals PRIMARY KEY (WithdrawalD)
);
GO
INSERT INTO Withdrawals(LocationID, EmployeeID, CustomerID,
WithdrawalDate, WithdrawalAmount)
VALUES(5, 1, 1, '1/4/2007', 80.00);
GO
INSERT INTO Withdrawals(LocationID, EmployeeID, CustomerID,
WithdrawalDate, WithdrawalAmount)
VALUES(3, 3, 4, '2/6/2007', 200.00);
GO
INSERT INTO Withdrawals(LocationID, EmployeeID, CustomerID,
WithdrawalDate, WithdrawalAmount)
VALUES(2, 4, 6, '1/26/2007', 80.00);
GO
INSERT INTO Withdrawals(LocationID, EmployeeID, CustomerID,
WithdrawalDate, WithdrawalAmount)
VALUES(4, 2, 2, '1/13/2007', 100.00);
GO
INSERT INTO Withdrawals(LocationID, EmployeeID, CustomerID,
WithdrawalDate, WithdrawalAmount)
VALUES(1, 5, 3, '1/18/2007', 100.00);
GO
INSERT INTO Withdrawals(LocationID, EmployeeID, CustomerID,
WithdrawalDate, WithdrawalAmount)
VALUES(1, 4, 6, '2/12/2007', 20.00);
GO
INSERT INTO Withdrawals(LocationID, EmployeeID, CustomerID,
WithdrawalDate, WithdrawalAmount)
VALUES(3, 2, 8, '1/20/2007', 300.00);
GO
INSERT INTO Withdrawals(LocationID, EmployeeID, CustomerID,
WithdrawalDate, WithdrawalAmount)
VALUES(5, 6, 1, '2/20/2007', 300);
GO
INSERT INTO Withdrawals(LocationID, EmployeeID, CustomerID,
WithdrawalDate, WithdrawalAmount)
VALUES(6, 1, 5, '1/24/2007', 200.00);
GO
INSERT INTO Withdrawals(LocationID, EmployeeID, CustomerID,
WithdrawalDate, WithdrawalAmount)
VALUES(1, 3, 4, '2/6/2007', 60);
GO
-- =========================================
-- Database: YugoNationalBank1
-- Table: CheckCashing
-- =========================================
IF OBJECT_ID('dbo.CheckCashing', 'U') IS NOT NULL
DROP TABLE dbo.CheckCashing
GO
CREATE TABLE CheckCashing
(
CheckCashingID int identity(1, 1) NOT NULL,
LocationID int Constraint FK_CashingLocation References Locations(LocationID) NOT NULL,
EmployeeID int Constraint FK_CashingEmployee References Employees(EmployeeID),
CustomerID int Constraint FK_CashingCustomer References Customers(CustomerID) NOT NULL,
CheckNumber int,
Recipient varchar(120) NOT NULL,
DateProcessed smalldatetime NOT NULL,
CheckAmount smallmoney NOT NULL,
HasEnoughFunds bit Default 1,
ServiceChargeAmount smallmoney default 0.00,
Notes text,
CONSTRAINT PK_CheckCashing PRIMARY KEY (CheckCashingID)
);
GO
INSERT INTO CheckCashing(LocationID, EmployeeID, CustomerID, CheckNumber,
Recipient, DateProcessed, CheckAmount)
VALUES(1, 4, 2, 100, 'Washington Electric Company', '3/15/2007', 124.38);
GO
INSERT INTO CheckCashing(LocationID, EmployeeID, CustomerID, CheckNumber,
Recipient, DateProcessed, CheckAmount)
VALUES(4, 3, 1, 101, 'Barriston Communications', '3/23/2007', 275.85);
GO
INSERT INTO CheckCashing(LocationID, EmployeeID, CustomerID, CheckNumber,
Recipient, DateProcessed, CheckAmount, HasEnoughFunds,
ServiceChargeAmount, Notes)
VALUES(1, 4, 3, 100, 'Route 1 Electronics', '3/22/2007', 45.65, 0, 25,
'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 CheckCashing(LocationID, EmployeeID, CustomerID, CheckNumber,
Recipient, DateProcessed, CheckAmount)
VALUES(4, 2, 6, 122, 'BooksOnline.com', '3/16/2007', 64.25);
GO
INSERT INTO CheckCashing(LocationID, EmployeeID, CustomerID, CheckNumber,
Recipient, DateProcessed, CheckAmount, HasEnoughFunds,
ServiceChargeAmount, Notes)
VALUES(5, 6, 1, 102, 'Self', '4/10/2007', 92.45, 0, 25.00,
'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
-- =========================================
-- Database: YugoNationalBank1
-- Table: TimeSheets
-- =========================================
IF OBJECT_ID('dbo.TimeSheets', 'U') IS NOT NULL
DROP TABLE dbo.TimeSheets
GO
CREATE TABLE TimeSheets
(
TimeSheetID int identity(1, 1) NOT NULL,
EmployeeID int Constraint FK_EmplTimeSheet References Employees(EmployeeID) NOT NULL,
StartDate datetime,
TimeSheetCode varchar(15),
Week1Monday varchar(6),
Week1Tuesday varchar(6),
Week1Wednesday varchar(6),
Week1Thursday varchar(6),
Week1Friday varchar(6),
Week1Saturday varchar(6),
Week1Sunday varchar(6),
Week2Monday varchar(6),
Week2Tuesday varchar(6),
Week2Wednesday varchar(6),
Week2Thursday varchar(6),
Week2Friday varchar(6),
Week2Saturday varchar(6),
Week2Sunday varchar(6),
Notes text,
CONSTRAINT PK_TimeSheets PRIMARY KEY (TimeSheetID)
);
GO
INSERT INTO TimeSheets(EmployeeID, StartDate, TimeSheetCode,
Week1Monday, Week1Tuesday, Week1Wednesday, Week1Thursday, Week1Friday,
Week1Saturday, Week1Sunday, Week2Monday, Week2Tuesday, Week2Wednesday,
Week2Thursday, Week2Friday, Week2Saturday, Week2Sunday)
VALUES(2, '1/1/2007', '4628801012007',
0.00, 8.50, 9.50, 8.50, 9.00, 0.00, 0.00,
10.00, 9.50, 8.50, 10.50, 9.00, 0.00, 0.00);
GO
INSERT INTO TimeSheets(EmployeeID, StartDate, TimeSheetCode,
Week1Monday, Week1Tuesday, Week1Wednesday, Week1Thursday, Week1Friday,
Week1Saturday, Week1Sunday, Week2Monday, Week2Tuesday, Week2Wednesday,
Week2Thursday, Week2Friday, Week2Saturday, Week2Sunday)
VALUES(6, '01/01/2007', '6628601012007',
0.00, 8.50, 6.50, 5.50, 6.50, 0.00, 0.00,
4.00, 6.00, 6.50, 6.00, 5.50, 0.00, 0.00);
GO
INSERT INTO TimeSheets(EmployeeID, StartDate, TimeSheetCode,
Week1Monday, Week1Tuesday, Week1Wednesday, Week1Thursday, Week1Friday,
Week1Saturday, Week1Sunday, Week2Monday, Week2Tuesday, Week2Wednesday,
Week2Thursday, Week2Friday, Week2Saturday, Week2Sunday)
VALUES(12, '1/1/2007', '9249301012007',
0.00, 8.00, 9.00, 8.50, 9.50, 0.00, 0.00,
5.50, 6.50, 4.50, 6.00, 4.00, 0.00, 0.00);
GO
INSERT INTO TimeSheets(EmployeeID, StartDate, TimeSheetCode,
Week1Monday, Week1Tuesday, Week1Wednesday, Week1Thursday, Week1Friday,
Week1Saturday, Week1Sunday, Week2Monday, Week2Tuesday, Week2Wednesday,
Week2Thursday, Week2Friday, Week2Saturday, Week2Sunday)
VALUES(3, '1/15/2007', '2719901152007',
6.00, 8.50, 0.00, 4.00, 6.50, 0.00, 0.00,
4.00, 0.00, 6.00, 4.00, 0.00, 0.00, 0.00);
GO
INSERT INTO TimeSheets(EmployeeID, StartDate, TimeSheetCode,
Week1Monday, Week1Tuesday, Week1Wednesday, Week1Thursday, Week1Friday,
Week1Saturday, Week1Sunday, Week2Monday, Week2Tuesday, Week2Wednesday,
Week2Thursday, Week2Friday, Week2Saturday, Week2Sunday, Notes)
VALUES(8, '1/15/2007', '3953801152007',
8.00, 8.00, 6.00, 8.50, 6.00, 0.00, 0.00,
9.50, 10.50, 8.00, 8.00, 8.50, 0.00, 0.00,
'There were a few missing times in the time sheet. ' +
'They have been recorded.');
GO
INSERT INTO TimeSheets(EmployeeID, StartDate, TimeSheetCode,
Week1Monday, Week1Tuesday, Week1Wednesday, Week1Thursday, Week1Friday,
Week1Saturday, Week1Sunday, Week2Monday, Week2Tuesday, Week2Wednesday,
Week2Thursday, Week2Friday, Week2Saturday, Week2Sunday)
VALUES(19, '1/15/2007','4055001152007',
8.50, 8.00, 0.00, 8.50, 0.00, 0.00, 0.00,
6.00, 6.50, 6.50, 0.00, 4.00, 0.00, 0.00);
GO
INSERT INTO TimeSheets(EmployeeID, StartDate, TimeSheetCode,
Week1Monday, Week1Tuesday, Week1Wednesday, Week1Thursday, Week1Friday,
Week1Saturday, Week1Sunday, Week2Monday, Week2Tuesday, Week2Wednesday,
Week2Thursday, Week2Friday, Week2Saturday, Week2Sunday)
VALUES(6, '1/29/2007', '6628601292007',
8.00, 6.50, 9.50, 8.00, 7.50, 0.00, 0.00,
10.50, 9.50, 8.50, 8.00, 10.00, 0.00, 0.00);
GO
INSERT INTO TimeSheets(EmployeeID, StartDate, TimeSheetCode,
Week1Monday, Week1Tuesday, Week1Wednesday, Week1Thursday, Week1Friday,
Week1Saturday, Week1Sunday, Week2Monday, Week2Tuesday, Week2Wednesday,
Week2Thursday, Week2Friday, Week2Saturday, Week2Sunday)
VALUES(14, '2/12/2007', '9002602122007',
8.50, 6.50, 8.00, 8.00, 9.50, 0.00, 0.00,
9.50, 8.00, 8.50, 8.00, 8.00, 0.00, 0.00);
GO
INSERT INTO TimeSheets(EmployeeID, StartDate, TimeSheetCode,
Week1Monday, Week1Tuesday, Week1Wednesday, Week1Thursday, Week1Friday,
Week1Saturday, Week1Sunday, Week2Monday, Week2Tuesday, Week2Wednesday,
Week2Thursday, Week2Friday, Week2Saturday, Week2Sunday)
VALUES(12, '2/12/2007', '9249302122007',
4.00, 6.50, 5.50, 8.00, 6.50, 0.00, 0.00,
8.00, 8.00, 8.00, 6.00, 8.00, 0.00, 0.00);
GO
/*---- This is a table from an old version of this database
---- We intend to remove it
-- =========================================
-- Database: YugoNationalBank1
-- Table: TransactionTypes
-- =========================================
IF OBJECT_ID('dbo.TransactionTypes', 'U') IS NOT NULL
DROP TABLE dbo.TransactionTypes
GO
CREATE TABLE TransactionTypes
(
TransactionTypeID int identity(1, 1) NOT NULL,
TransactionType varchar(50) NOT NULL,
Notes text,
CONSTRAINT PK_TransactionTypes PRIMARY KEY (TransactionTypeID)
);
GO
INSERT INTO TransactionTypes (TransactionType, Notes)
VALUES('Deposit', 'Used if a customer is depositing money, regardless of the type of account');
INSERT INTO TransactionTypes (TransactionType, Notes)
VALUES('Withdrawal', 'Specifies that a customer is receiving money. This also applies when a check is cashed from the customer account');
INSERT INTO TransactionTypes (TransactionType, Notes)
VALUES('Fund Transfer', 'This applies to an operation that consists of transferring money from one account to another');
INSERT INTO TransactionTypes (TransactionType, Notes)
VALUES('Money Order', 'This is selected if a person is purchasing a money order from this bank');
INSERT INTO TransactionTypes (TransactionType, Notes)
VALUES('Service Charge', 'There are various types of service charges. This category applies to all of them, regardless of the reason, as long as the Bank Management decides to withdraw money from the customer''s account as a fee or a penalty');
GO
---- This is a table from an old version of this database
---- We intend to remove it
---- Instead, we will create a view that can show a summary
---- of the transactions related to each account
GO
-- =========================================
-- Database: YugoNationalBank1
-- Table: AccountTransactions
-- =========================================
IF OBJECT_ID('dbo.AccountTransactions', 'U') IS NOT NULL
DROP TABLE dbo.AccountTransactions
GO
CREATE TABLE AccountTransactions
(
TransactionID int Identity(1, 1) NOT NULL,
EmployeeID int Constraint FK_Employees References Employees(EmployeeID) NOT NULL,
CustomerID int Constraint FK_Customers References Customers(CustomerID) NOT NULL,
TransactionTypeID int Constraint FK_TransactionTypes References TransactionTypes(TransactionTypeID) NOT NULL,
TransactionDate DateTime NOT NULL,
TransactionNumber int NOT NULL,
DepositAmount money,
WithdrawalAmount money,
ServiceCharge smallmoney,
ChargeReasonID int Constraint FK_ChargeReasons References ChargeReasons(ChargeReasonID),
Notes text,
CONSTRAINT PK_Transactions PRIMARY KEY (TransactionID)
);
GO*/
Home