USE master
GO
-- =============================================
-- Database:     KoloBank2
-- Author:       FunctionX
-- Date Created: Saturday 08 January 2011
-- =============================================
-- 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
CREATE TABLE Management.Locations
(
    LocationID int Identity(1,1) NOT NULL, 
    LocationCode nvarchar(10) NOT NULL,
    Address nvarchar(120),
    City nvarchar(50),
    State nvarchar(50),
    Notes text NULL, 
    CONSTRAINT PK_Locations PRIMARY KEY (LocationID)
);
GO
CREATE TABLE Management.AccountTypes
(
    AccountTypeID int Identity(1,1) NOT NULL, 
    AccountType nvarchar(40) NOT NULL, 
    Notes text NULL, 
    CONSTRAINT PK_AccountTypes PRIMARY KEY (AccountTypeID)
);
GO

CREATE TABLE Personnel.Employees
(
    EmployeeID int identity(1,1) NOT NULL, 
    EmployeeNumber nchar(10),
    FirstName nvarchar(32),
    LastName nvarchar(32) NOT NULL,
    Title nvarchar(50),
    CanCreateNewAccount bit,
    HourlySalary Money,
    EmailAddress nvarchar(100),
    Username nvarchar(20),
    Password nvarchar(20),
    Notes text,
    CONSTRAINT PK_Employees PRIMARY KEY (EmployeeID)
);
GO

CREATE TABLE Accounting.TimeSheets
(
    TimeSheetID int identity(1, 1) NOT NULL,
    EmplNumber nchar(10),
    StartDate date,
    TimeSheetCode nvarchar(15),
    Week1Monday nvarchar(6),
    Week1Tuesday nvarchar(6),
    Week1Wednesday nvarchar(6),
    Week1Thursday nvarchar(6),
    Week1Friday nvarchar(6),
    Week1Saturday nvarchar(6),
    Week1Sunday nvarchar(6),
    Week2Monday nvarchar(6),
    Week2Tuesday nvarchar(6),
    Week2Wednesday nvarchar(6),
    Week2Thursday nvarchar(6),
    Week2Friday nvarchar(6),
    Week2Saturday nvarchar(6),
    Week2Sunday nvarchar(6),
    Notes text, 
    CONSTRAINT PK_TimeSheets PRIMARY KEY (TimeSheetID)
);
GO

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) NOT NULL,
    DateUpdated date,
    Notes text, 
    CONSTRAINT PK_Customers PRIMARY KEY (CustomerID)
);
GO

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_Depositor 
		References Management.Customers(CustomerID) NOT NULL,
    DepositDate date NOT NULL,
    DepositAmount money NOT NULL,
    Notes text, 
    CONSTRAINT PK_Deposits PRIMARY KEY (DepositID)
);
GO

CREATE TABLE Transactions.Withdrawals
(
    WithdrawalID int identity(1, 1) NOT NULL,
    LocationID int Constraint FK_WithdrawlLocation 
		References Management.Locations(LocationID) NOT NULL,
    EmployeeID int Constraint FK_ProcessedBy 
		References Personnel.Employees(EmployeeID),
    CustomerID int Constraint FK_CustomerAccount 
		References Management.Customers(CustomerID) NOT NULL,
    WithdrawalDate date NOT NULL,
    WithdrawalAmount money NOT NULL,
    WithdrawalSuccessful bit NOT NULL,
    Notes text, 
    CONSTRAINT PK_Withdrawals PRIMARY KEY (WithdrawalID)
);

CREATE TABLE Transactions.CheckCashing
(
    CheckCashingID int identity(1, 1) NOT NULL,
    LocationID int Constraint FK_BranchLocations 
		References Management.Locations(LocationID) NOT NULL,
    EmployeeID int Constraint FK_Employees 
		References Personnel.Employees(EmployeeID),
    CustomerID int Constraint FK_Customers 
		References Management.Customers(CustomerID) NOT NULL,
    CheckCashingDate date NOT NULL,
    CheckCashingAmount money NOT NULL,
    CheckCashingSuccessful bit NOT NULL,
    Notes text, 
    CONSTRAINT PK_CheckCashing PRIMARY KEY(CheckCashingID)
);
GO