-- ==================================================================
-- Database: 	WattsALoan1
-- Author:	FunctionX
-- Date:	Monday 10 January 2011
-- Description:	This database is used by a fictitious small company
--				that gives personnel, cash, and other types of loans,
--				mostly to individuals, but also to small businesses
-- ==================================================================
USE master
GO
-- Drop the database if it already exists
IF  EXISTS (
	SELECT name 
		FROM sys.databases 
		WHERE name = N'WattsALoan1'
)
DROP DATABASE WattsALoan1
GO
CREATE DATABASE WattsALoan1
GO

USE WattsALoan1
GO
CREATE SCHEMA Management;
GO
CREATE SCHEMA Personnel;
GO
CREATE SCHEMA Loans;
GO
CREATE SCHEMA Accounts;
GO
-- ==================================================================
-- Table:		Employees
-- Description:	This table holds a list of the employees of the company
-- ==================================================================

CREATE TABLE Personnel.Employees
(
    EmployeeID int identity(1,1) NOT NULL,
    EmployeeNumber nchar(10) NULL,
    FirstName nvarchar(20) NULL,
    LastName nvarchar(10),
    FullName AS ((LastName+ ', N') + FirstName),
    Title nvarchar(100),
    HourlySalary money,
    Username nvarchar(20),
    Password nvarchar(20),
    CONSTRAINT PK_Employees PRIMARY KEY(EmployeeID)
)
GO
INSERT INTO Personnel.Employees(EmployeeNumber, FirstName, LastName, Title, HourlySalary)
VALUES(N'293747', N'Jeanne', N'Tryler', N'Accounts Manager', 22.24);
GO
INSERT INTO Personnel.Employees(EmployeeNumber, FirstName, LastName, Title, HourlySalary)
VALUES(N'492947', N'Helene', N'Gustman', N'Accounts Representative', 14.55);
GO
INSERT INTO Personnel.Employees(EmployeeNumber, FirstName, LastName, Title, HourlySalary)
VALUES(N'804685', N'Ernest', N'Thomas', N'Accounts Representative', 12.75);
GO

-- ==================================================================
-- Table:		Types
-- Description:	This table holds a list of loan types.
-- ==================================================================
CREATE TABLE Loans.Types
(
    LoanTypeID int identity(1,1) NOT NULL, 
    LoanType nvarchar(50) NOT NULL, 
    CONSTRAINT PK_Types PRIMARY KEY(LoanTypeID)
);
GO
INSERT INTO Loans.Types(LoanType) VALUES(N'Personal Loan');
GO
INSERT INTO Loans.Types(LoanType) VALUES(N'Car Financing');
GO
INSERT INTO Loans.Types(LoanType) VALUES(N'Credit Card');
GO
INSERT INTO Loans.Types(LoanType) VALUES(N'Furniture Loan');
GO
-- ==================================================================
-- Table:		Customers
-- Description:	This tables holds a list of individuals and 
--				small businesses that request loans from WattsALoan
-- ==================================================================

CREATE TABLE Accounts.Customers
(
    CustomerID int identity(1,1) NOT NULL,
    DateCreated date NULL,
    FullName nvarchar(50) NOT NULL,
    [Address] nvarchar(100),
    City nvarchar(50),
    [State] nvarchar(50),
    ZIPCode nvarchar(10),
    EmailAddress nvarchar(100),
    CONSTRAINT PK_Customers PRIMARY KEY(CustomerID)
)
GO
INSERT INTO Accounts.Customers(DateCreated, FullName,
    [Address], City, [State],
    ZIPCode, EmailAddress)
VALUES(N'2/26/2004', N'Julius Ramse',
       N'927 Feuler Ave', N'Silver Spring', 
       N'MD', N'20904', N'ramses1990@netscape.net');
GO
INSERT INTO Accounts.Customers(DateCreated, FullName,
    [Address], City, [State], ZIPCode)
VALUES(N'06/22/2006', N'Gertrude Vaillant',
       N'10055 Larsenic Rd', N'Takoma Park', 
       N'MD', N'20910');
GO
INSERT INTO Accounts.Customers(DateCreated, FullName,
    [Address],	City, [State], ZIPCode, EmailAddress)
VALUES(N'12/3/2004', N'James Barrouch',
       N'4204 Fallon Drive', N'Silver Spring',
       N'MD', N'20906', N'barrouchj@hotmail.com');
GO
INSERT INTO Accounts.Customers(DateCreated, FullName,
        [Address], City, [State], ZIPCode)
VALUES(N'08/02/2006', N'Christine Rougher',
       N'825 Manning Street', N'Alexandria', 
       N'VA', N'22231');
GO
INSERT INTO Accounts.Customers(DateCreated, FullName,
    [Address], City, [State], ZIPCode, EmailAddress)
VALUES(N'10/08/2006', N'Patrick Heller',
       N'2480 Clarington Drive NW', N'Washington', 
       N'DC', N'20006', N'hellerp@yahooo.com');
GO
-- ==================================================================
-- Table:		Allocations
-- Description:	This table is used to hold records of loans that
--				are given to customers.
--				This table contains fields that perform their own
--				default calculations
-- ==================================================================
CREATE TABLE Loans.Allocations
(
    AllocationID int identity(1,1) NOT NULL,
    DatePrepared date NOT NULL, 
    EmployeeID int NULL
		CONSTRAINT FK_LoanPreparer
		FOREIGN KEY REFERENCES Personnel.Employees(EmployeeID),
    CustomerID int NOT NULL
		CONSTRAINT FK_LoanReceiver
		FOREIGN KEY REFERENCES Accounts.Customers(CustomerID),
    AccountNumber nchar(10),
    LoanTypeID int NOT NULL
		CONSTRAINT FK_LoansTypes
		FOREIGN KEY REFERENCES Loans.Types(LoanTypeID),
    LoanAmount money NOT NULL,
    InterestRate decimal(6,2) NOT NULL,
    Periods decimal(6,2) NOT NULL,
    InterestAmount AS ((LoanAmount*(InterestRate/(100)))*(Periods/(12))),
    FutureValue AS (LoanAmount+(LoanAmount*(InterestRate/(100)))*(Periods/(12))),
    MonthlyPayment AS ((LoanAmount+(LoanAmount*(InterestRate/(100)))*(Periods/(12)))/Periods),
    Notes nvarchar(max),
    CONSTRAINT PK_LoanAllocations PRIMARY KEY(AllocationID)
)
GO
INSERT INTO Loans.Allocations(DatePrepared, EmployeeID,
	CustomerID, AccountNumber, LoanTypeID, LoanAmount,
    InterestRate, Periods, Notes)
VALUES(N'2/26/2004', 2, 1, N'9171394', 4, 6500.00, 12.65, 36, 
       N'The loan will be delivered by our furniture business partner Helios Furnian');
GO
INSERT INTO Loans.Allocations(DatePrepared, EmployeeID,
	CustomerID, AccountNumber, LoanTypeID, LoanAmount,
    InterestRate, Periods, Notes)
VALUES(N'06/22/2007', 2, 2, N'8628064', 2, 16500.00, 10.20, 60, 
       N'For this car loan, our partner Arlington Honda will process and deliver the car.');
GO
INSERT INTO Loans.Allocations(DatePrepared, EmployeeID,
	CustomerID, AccountNumber, LoanTypeID, LoanAmount,
    InterestRate, Periods, Notes)
VALUES(N'12/3/2006', 1, 3, N'8468364', 3, 500.00, 18.65, 48, 
       N'This is a regular credit card.');
GO
INSERT INTO Loans.Allocations(DatePrepared, EmployeeID,
	CustomerID, AccountNumber, LoanTypeID, LoanAmount,
    InterestRate, Periods, Notes)
VALUES(N'08/02/2006', 3, 4, N'2483047', 1, 3500.00, 12.74, 36, 
       N'This is personal/cash loan allocated to a customer who walked in the store and requested it.');
GO
INSERT INTO Loans.Allocations(DatePrepared, EmployeeID,
	CustomerID, AccountNumber, LoanTypeID, LoanAmount,
    InterestRate, Periods, Notes)
VALUES(N'10/08/2006', 2, 5, N'1311804', 4, 22748.36, 12.28, 60, 
       N'This is a regular car financing loan');
GO
-- ==================================================================
-- Table:		Payments
-- Description:	This table holds records of payments that are made
--				or have been made by customers
-- ==================================================================

CREATE TABLE Loans.Payments
(
    PaymentID int identity(1, 1) NOT NULL,
    PaymentDate date NOT NULL, 
    EmployeeID int NULL
		CONSTRAINT FK_Employees
		FOREIGN KEY REFERENCES Personnel.Employees(EmployeeID),
    AllocationID int NOT NULL
		CONSTRAINT FK_LoanAllocations
		FOREIGN KEY REFERENCES Loans.Allocations(AllocationID),
    PaymentAmount money NOT NULL,
    Balance money,
    Notes nvarchar(max),
    CONSTRAINT PK_Payments PRIMARY KEY(PaymentID)
)
GO