-- Watts A Loan --
USE master;
GO
IF EXISTS (
SELECT *
FROM sys.databases
WHERE name = N'WattsALoan1'
)
DROP DATABASE WattsALoan1
GO
CREATE DATABASE WattsALoan1;
GO
USE WattsALoan1;
GO
CREATE SCHEMA Loans;
GO
CREATE SCHEMA Personnel;
GO
CREATE SCHEMA Accounts;
GO
CREATE TABLE Personnel.Employees
(
EmployeeNumber nchar(7) Not Null,
FirstName nvarchar(25) NULL,
LastName nvarchar(25) Not Null,
FullName AS CONCAT(LastName, ', ', FirstName),
Title nvarchar(100),
WorkPhone nvarchar(20),
Address nvarchar(100),
City nvarchar(50),
State nvarchar(50),
ZIPCode nvarchar(10),
Constraint PK_Employees Primary Key(EmployeeNumber)
);
GO
CREATE TABLE Accounts.Customers
(
AccountNumber nchar(12) Not Null,
AccountDate nvarchar(50),
FirstName nvarchar(24) NULL,
MiddleName nvarchar(24),
LastName nvarchar(25) Not Null,
CustomerName nvarchar(75),
WorkPhone nvarchar(20),
HomePhone nvarchar(20),
Address nvarchar(100),
City nvarchar(50),
State nvarchar(50),
ZIPCode nvarchar(10),
EmailAddress nvarchar(100),
Constraint PK_Customers Primary Key(AccountNumber)
);
GO
CREATE TABLE Loans.Types
(
LoanType nvarchar(40),
TypeDescription nvarchar(max),
Constraint PK_LoansTypes Primary Key(LoanType)
);
GO
CREATE TABLE Loans.Allocations
(
LoanNumber int identity(100001, 1) Not Null,
DateAllocated date,
EmployeeNumber nchar(7),
AccountNumber nchar(12),
LoanType nvarchar(40),
LoanAmount money Not Null,
InterestRate decimal(6, 2) Not Null,
Periods decimal(6,2) Not Null,
InterestAmount money, -- AS ((LoanAmount * (InterestRate / 100)) * (Periods / 12)),
FutureValue money, -- AS (LoanAmount + (LoanAmount * (InterestRate / 100)) * (Periods / 12)),
MonthlyPayment money, -- AS ((LoanAmount + (LoanAmount * (InterestRate / 100)) * (Periods / 12)) / Periods),
Notes nvarchar(max),
Constraint PK_LoanAllocations Primary Key(LoanNumber),
Constraint FK_AllocationsClerks Foreign Key(EmployeeNumber) References Personnel.Employees(EmployeeNumber),
Constraint FK_Customers Foreign Key(AccountNumber) References Accounts.Customers(AccountNumber),
Constraint FK_LoanTypes Foreign Key(LoanType) References Loans.Types(LoanType)
);
GO
CREATE TABLE Loans.Payments
(
PaymentID int identity(1001, 1) Not Null,
PaymentDate date Not Null,
EmployeeNumber nchar(7),
LoanNumber int Not Null,
PaymentAmount money Not Null,
Balance money,
Notes nvarchar(max),
Constraint PK_Payments Primary Key(PaymentID),
Constraint FK_PaymentsClerks Foreign Key(EmployeeNumber) References Personnel.Employees(EmployeeNumber),
Constraint FK_LoanAllocations Foreign Key(LoanNumber) References Loans.Allocations(LoanNumber)
);
GO
INSERT INTO Loans.Types(LoanType, TypeDescription)
VALUES(N'Personal Loan', N'This is loan given as a cashier check to a customer who wants a cash loan'),
(N'Car Financing', N'This loan will be processed by our partners as car dealers');
GO
INSERT INTO Loans.Types(LoanType)
VALUES(N'Boat Purchase'),(N'Furniture');
GO
INSERT INTO Loans.Types(LoanType, TypeDescription)
VALUES(N'Musical Instrument', N'We have some partnerships in musical instruments stores. This is the type of loan we will make available to the customers they find for us');
GO
INSERT INTO Personnel.Employees(EmployeeNumber, FirstName, LastName, Title, State)
VALUES(N'836-486', N'Ernest', N'Thomas', N'Accounts Representative', N'MD');
GO
INSERT INTO Personnel.Employees(EmployeeNumber, FirstName, LastName, Title, WorkPhone, Address, City, State)
VALUES(N'492-947', N'Sandrine', N'Ethridge', N'Assistant Manager', N'(410) 781-6238', N'408 Dorsey Rd', N'Columbia', N'MD');
GO
INSERT INTO Personnel.Employees(EmployeeNumber, FirstName, LastName, Title, State) VALUES(N'240-750', N'Helene', N'Gustman', N'Accounts Representative', N'MD');
GO
INSERT INTO Personnel.Employees(EmployeeNumber, FirstName, LastName, Title, WorkPhone, City, State)
VALUES(N'804-685', N'Melissa', N'Browns', N'Customer Accounts Representative', N'(410) 781-6238', N'Owings Mills', N'MD');
GO
INSERT INTO Personnel.Employees(EmployeeNumber, FirstName, LastName, Title, WorkPhone, City, State, ZIPCode)
VALUES(N'429-374', N'Jake', N'Leighton', N'Accounts Manager', N'(410) 781-6238', N'Laurel', N'MD', N'20707');
GO
INSERT INTO Personnel.Employees(EmployeeNumber, FirstName, LastName, Title, WorkPhone, Address, City, State, ZIPCode)
VALUES(N'293-747', N'Catherine', N'Watts', N'Owner', N'(410) 781-6238', N'8012 New Castle Blvd', N'Baltimore', N'MD', N'21212');
GO
INSERT INTO Accounts.Customers(AccountNumber, AccountDate, FirstName, MiddleName, LastName, CustomerName, Address, City, State, ZIPCode, WorkPhone, HomePhone, EmailAddress)
VALUES(N'29-750024-82', N'20140216', N'Joanne', N'Lucille', N'Sinton', N'Sinton, Joanne Lucille', N'482 Bayless Rd', N'Timonium', 'MD', N'21212', N'(410) 789-8000', N'(410) 647-8000', N'sintonj@umd.edu');
GO
INSERT INTO Accounts.Customers(AccountNumber, AccountDate, FirstName, MiddleName, LastName, CustomerName, Address, City, State, ZIPCode, WorkPhone, HomePhone)
VALUES(N'64-827095-07', N'20140422', N'Helene', N'Felicia', N'Fuchs', N'Fuchs, Helene Felicia', N'2208 Hiss Ave', N'Towson', 'MD', N'21208', N'(301) 621-1001', N'(410) 665-8118');
GO
INSERT INTO Accounts.Customers(AccountNumber, AccountDate, FirstName, LastName, CustomerName, Address, City, State, ZIPCode, HomePhone, EmailAddress)
VALUES(N'83-401857-88', N'20140424', N'Alexander', N'Lozanski', N'Lozanski, Alexander', N'7382 Lilas Drive', N'Baltimore', 'MD', N'21202', N'(410) 740-0074)', N'lozanfriend@yahoo.com');
GO
INSERT INTO Accounts.Customers(AccountNumber, AccountDate, FirstName, MiddleName, LastName, CustomerName, Address, City, State, ZIPCode, HomePhone)
VALUES(N'92-738024-35', N'20140615', N'Gerard', N'Lowell', N'Valleys', N'Valleys, Gerard Lowell', N'308 Avondale Drive', N'Baltimore', 'MD', N'21204', N'(410) 296-1072');
GO
CREATE VIEW Loans.ShowAllocations
AS
SELECT la.LoanNumber AS [Loan #],
la.DateAllocated AS [Date Prepared],
clerks.EmployeeNumber + N': ' + clerks.LastName + N', ' + clerks.FirstName AS [Processed By],
clients.AccountNumber AS [Processed For],
clients.CustomerName AS [Customer Name],
categories.LoanType AS [Loan Type],
la.LoanAmount AS Amount,
la.InterestRate AS [Interest Rate],
la.Periods AS [Loan Length],
la.InterestAmount AS [Interest Amount],
la.FutureValue AS [Future Value],
la.MonthlyPayment AS [Monthly Payment],
la.Notes
FROM Loans.Allocations AS la
INNER JOIN Personnel.Employees AS clerks
ON la.EmployeeNumber = clerks.EmployeeNumber
INNER JOIN Loans.Types AS categories
ON la.LoanType = categories.LoanType
INNER JOIN Accounts.Customers AS clients
ON la.AccountNumber = clients.AccountNumber;
GO
CREATE VIEW Loans.ShowPayments
AS
SELECT pmts.PaymentID,
pmts.PaymentDate AS [Payment Date],
clerks.EmployeeNumber + N' - ' + clerks.FullName AS [Processed By],
pmts.LoanNumber AS [Loan #],
la.AccountNumber + N' - ' + clients.CustomerName AS [Loan For Customer],
pmts.PaymentAmount AS [Pmt Amount],
pmts.Balance,
pmts.Notes
FROM Loans.Payments AS pmts
INNER JOIN Personnel.Employees AS clerks
ON pmts.EmployeeNumber = clerks.EmployeeNumber
INNER JOIN Loans.Allocations AS la
ON pmts.LoanNumber = la.LoanNumber AND pmts.LoanNumber = la.LoanNumber
INNER JOIN Accounts.Customers AS clients
ON la.AccountNumber = clients.AccountNumber;
GO