-- Watts A Loan --
-- ==================================================================
-- Database: WattsALoan1
-- Author: FunctionX
-- Date Started: Monday 10 January 2011
-- Update: Thursday 27 December 2012, 07:15
-- 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
-- Data Definition Language (DDL)
CREATE SCHEMA Management;
GO
CREATE SCHEMA HumanResources;
GO
CREATE SCHEMA Loans;
GO
CREATE SCHEMA Accounts;
GO
CREATE TABLE HumanResources.Employees
(
EmployeeNumber nvarchar(10) NOT 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(EmployeeNumber)
)
GO
CREATE TABLE Loans.Types
(
LoanType nvarchar(25) NOT NULL,
TypeDesctiption nvarchar(max),
CONSTRAINT PK_Types PRIMARY KEY(LoanType)
);
GO
CREATE TABLE Accounts.Customers
(
AccountNumber nvarchar(10) NOT NULL,
DateCreated date NULL,
FirstName nvarchar(25),
LastName nvarchar(25) NOT NULL,
[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.Allocations
(
AllocationID int identity(1,1) NOT NULL,
DatePrepared date NOT NULL,
EmployeeNumber nvarchar(10),
AccountNumber nvarchar(10),
LoanType nvarchar(25) NOT NULL,
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),
CONSTRAINT FK_LoanPreparer FOREIGN KEY(EmployeeNumber) REFERENCES HumanResources.Employees(EmployeeNumber),
CONSTRAINT FK_LoanReceiver FOREIGN KEY(AccountNumber) REFERENCES Accounts.Customers(AccountNumber),
CONSTRAINT FK_LoansTypes FOREIGN KEY(LoanType) REFERENCES Loans.Types(LoanType)
)
GO
CREATE TABLE Loans.Payments
(
PaymentID int identity(100001, 1) NOT NULL,
PaymentDate date NOT NULL,
EmployeeNumber nvarchar(10) NULL,
AllocationID int NOT NULL,
PaymentAmount money NOT NULL,
Balance money,
Notes nvarchar(max),
CONSTRAINT PK_Payments PRIMARY KEY(PaymentID),
CONSTRAINT FK_PaymentReceiver FOREIGN KEY(EmployeeNumber) REFERENCES HumanResources.Employees(EmployeeNumber),
CONSTRAINT FK_LoanAllocations FOREIGN KEY(AllocationID) REFERENCES Loans.Allocations(AllocationID)
)
GO
-- Data Manipulation Language (DML)
INSERT INTO HumanResources.Employees(EmployeeNumber, FirstName, LastName, Title, HourlySalary)
VALUES(N'29747', N'Jeanne', N'Tryler', N'Accounts Manager', 22.24),
(N'42949', N'Daniel', N'Gustman', N'Accounts Representative', 14.55),
(N'68220', N'Françoise', N'Ngaleu', N'Loan Specialist', 20.48),
(N'84685', N'Ernest', N'Thomas', N'Accounts Representative', 12.75);
GO
INSERT INTO Loans.Types
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.'),
(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 Loans.Types(LoanType)
VALUES(N'Credit Card'), (N'Furniture Loan');
GO
INSERT INTO Accounts.Customers(AccountNumber, DateCreated, FirstName, LastName, [Address], City, [State], ZIPCode, EmailAddress)
VALUES(N'92704930', N'20130226', N'Julius', N'Ramse', N'927 Feuler Ave', N'Silver Spring', N'MD', N'20904', N'ramses1990@netscape.net');
GO
INSERT INTO Accounts.Customers(AccountNumber, DateCreated, FirstName, LastName, [Address], City, [State], ZIPCode)
VALUES(N'20824070', N'20130622', N'Gertrude', N'Vaillant', N'10055 Larsenic Rd', N'Takoma Park', N'MD', N'20910');
GO
INSERT INTO Accounts.Customers(AccountNumber, DateCreated, FirstName, LastName, [Address], City, [State], ZIPCode, EmailAddress)
VALUES(N'51392416', N'20131203', N'James', N'Barrouch', N'4204 Fallon Drive', N'Silver Spring', N'MD', N'20906', N'barrouchj@hotmail.com');
GO
INSERT INTO Accounts.Customers(AccountNumber, DateCreated, FirstName, LastName, [Address], City, [State], ZIPCode)
VALUES(N'29307971', N'08/02/2006', N'Christine', N'Rougher', N'825 Manning Street', N'Alexandria', N'VA', N'22231');
GO
INSERT INTO Accounts.Customers(AccountNumber, DateCreated, FirstName, LastName, [Address], City, [State], ZIPCode, EmailAddress)
VALUES(N'96130420', N'10/08/2006', N'Patrick', N'Heller', N'2480 Clarington Drive NW', N'Washington', N'DC', N'20006', N'hellerp@yahooo.com');
GO
INSERT INTO Loans.Allocations(DatePrepared, EmployeeNumber, AccountNumber, LoanType, LoanAmount, InterestRate, Periods, Notes)
VALUES(N'20130226', N'42949', N'92704930', N'Furniture Loan', 6500.00, 12.65, 36, N'The loan will be delivered by our furniture business partner Helios Furnian'),
(N'20130622', N'42949', N'20824070', N'Car Financing', 16500.00, 10.20, 60, N'For this car loan, our partner Arlington Honda will process and deliver the car.'),
(N'20130802', N'29747', N'51392416', N'Credit Card', 500.00, 18.65, 48, N'This is a regular credit card.'),
(N'20130808', N'84685', N'29307971', N'Personal Loan', 3500.00, 12.74, 36, N'This is personal/cash loan allocated to a customer who walked in the store and requested it.'),
(N'20131008', N'42949', N'96130420', N'Furniture Loan', 22748.36, 12.28, 60, N'This is a regular car financing loan');
GO