Yugo National Bank
-- ============================================= -- DATABASE: YugoNationalBank -- ============================================= IF EXISTS (SELECT * FROM master..sysdatabases WHERE name = N'YugoNationalBank') DROP DATABASE YugoNationalBank GO CREATE DATABASE YugoNationalBank; GO
-- =============================================
-- Table: AccountTypes
-- =============================================
IF EXISTS(SELECT name
FROM sysobjects
WHERE name = N'AccountTypes'
AND type = 'U')
DROP TABLE AccountTypes
GO
CREATE TABLE AccountTypes (
AccountTypeID int Primary Key Identity(1,1) NOT NULL,
AccountType varchar(50) NOT NULL,
Notes text)
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
-- =============================================
-- Table: Employees
-- =============================================
IF EXISTS (SELECT name
FROM sysobjects
WHERE name = N'Employees'
AND type = 'U')
DROP TABLE Employees
GO
create table Employees (
EmployeeID int Primary Key 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),
Salary smallmoney,
Notes text)
GO
INSERT INTO Employees(EmployeeNumber, FirstName, LastName, Title, CanCreateNewAccount, WorkPhone, Extension, Address, City, State, Country, Salary)
VALUES('22-082', '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, Salary)
VALUES('46-288', '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, Salary)
VALUES('27-199', '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, Salary)
VALUES('19-804', '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, Salary)
VALUES('27-284', '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, Salary)
VALUES('66-286', '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, Salary)
VALUES('48-725', '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, Salary)
VALUES('39-538', '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, Salary)
VALUES('82-730', '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, Salary)
VALUES('93-077', '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, Salary)
VALUES('11-914','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, Salary)
VALUES('92-493', '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, Salary)
VALUES('22-086', '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, Salary)
VALUES('90-026', '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, Salary)
VALUES('27-707', '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, Salary)
VALUES('27-283', '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, Salary)
VALUES('38-525', '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, Salary)
VALUES('71-728', '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, Salary)
VALUES('40-550', '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, Salary)
VALUES('55-528','Jeffrey', 'Salomons','Cashier', '(410) 653-1309', 194,'3832 Great River Rd', 'Baltimore', 'MD','USA', 24.52);
GO
-- =============================================
-- Table: Customers
-- =============================================
IF EXISTS (SELECT name
FROM sysobjects
WHERE name = N'Customers'
AND type = 'U')
DROP TABLE Customers
GO
CREATE TABLE Customers (
CustomerID int Primary Key Identity(1,1) NOT NULL,
DateCreated datetime,
AccountNumber varchar(20),
AccountTypeID int REFERENCES AccountTypes(AccountTypeID),
AccountName 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)
GO
INSERT INTO Customers(AccountNumber, AccountName, 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, AccountName, 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, AccountName, 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, AccountName, 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, AccountName, 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, AccountName, 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, AccountName, 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, AccountName, 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, AccountName, 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, AccountName, 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, AccountName, 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, AccountName, 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, AccountName, 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, AccountName, 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, AccountName, 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, AccountName, 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, AccountName, 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, AccountName, 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, AccountName, 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, AccountName, 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, AccountName, 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
-- =============================================
-- Table: TransactionTypes
-- =============================================
IF EXISTS (SELECT name
FROM sysobjects
WHERE name = N'TransactionTypes'
AND type = 'U')
DROP TABLE TransactionTypes
GO
create table TransactionTypes (
TransactionTypeID int Primary Key IDENTITY(1, 1) NOT NULL,
TransactionType varchar(50) NOT NULL,
Notes text)
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
-- =============================================
-- Table: ChargeReasons
-- =============================================
IF EXISTS (SELECT name
FROM sysobjects
WHERE name = N'ChargeReasons'
AND type = 'U')
DROP TABLE ChargeReasons
GO
create table ChargeReasons (
ChargeReasonID int IDENTITY(1, 1) Primary Key NOT NULL,
ChargeReason varchar(50) NOT NULL,
Notes text)
GO
INSERT INTO ChargeReasons(ChargeReason, Notes)
VALUES('Monthly Charge', 'Applied every month to all accounts');
INSERT INTO ChargeReasons(ChargeReason, Notes)
VALUES('Overdraft', 'Applied if a customer''s account remains negative for 72 hours');
GO
-- =============================================
-- Table: AccountTransactions
-- =============================================
IF EXISTS (SELECT name
FROM sysobjects
WHERE name = N'AccountTransactions'
AND type = 'U')
DROP TABLE AccountTransactions
GO
create table AccountTransactions (
TransactionID int IDENTITY(1, 1) Primary Key NOT NULL,
EmployeeID int REFERENCES Employees(EmployeeID) NOT NULL,
CustomerID int References Customers(CustomerID) NOT NULL,
TransactionTypeID int References TransactionTypes(TransactionTypeID) NOT NULL,
TransactionDate DateTime NOT NULL,
TransactionNumber int NOT NULL,
DepositAmount money,
WithdrawalAmount money,
ServiceCharge smallmoney,
ChargeReasonID int references ChargeReasons(ChargeReasonID),
Notes text)
GO