-- ===============================================================
-- Database:	 LamdaSquare1
-- Author:		 FunctionX
-- Date Created: Sunday, January 30, 2011
-- Description:	 This database represents an apartment building.
--				 This part of the datbase is used to present the
--				 apartments to a customer and select the right one
-- ===============================================================
USE master
GO

-- Drop the database if it already exists
IF  EXISTS (
	SELECT name 
		FROM sys.databases 
		WHERE name = N'LamdaSquare2'
)
DROP DATABASE LamdaSquare2;
GO

CREATE DATABASE LamdaSquare2;
GO

USE LamdaSquare2;
GO

CREATE SCHEMA Presentation;
GO
CREATE SCHEMA Rentals;
GO
CREATE SCHEMA Personnel;
GO

CREATE TABLE Personnel.Employees
(
	EmplNbr nchar(8) not null unique,
	FirstName nvarchar(20),
	LastName nvarchar(20),
	Title nvarchar(50),
	CanProcessPayment bit default 0,
	Constraint PK_Employees Primary Key(EmplNbr)
);
GO
CREATE TABLE Rentals.MaritalStatus
(
	StatusID int identity(1, 1) not null,
	[Status] nvarchar(50),
	Constraint PK_Status Primary Key(StatusID)
);
GO
CREATE TABLE Rentals.Tenants
(
	TenantCode nchar(8) not null unique,
	FullName nvarchar(50),
	MaritalStatusID int null
		Constraint FK_Tenants References Rentals.MaritalStatus(StatusID),
	PhoneNumber nvarchar(20),
	EmailAddress nvarchar(40),
	Constraint PK_Tenants Primary Key(TenantCode)
);
GO
CREATE TABLE Presentation.Units
(
	UnitNumber nchar(8) not null unique,
	Bedrooms int,
	Bathrooms real,
	Price money,
	Deposit money,
	OccupancyStatus nvarchar(20),
	Constraint PK_Units Primary Key(UnitNumber)
);
GO

CREATE TABLE Rentals.Allocations
(
	AllocationCode int identity(100000, 1) not null,
	EmplNbr nchar(8) not null
		Constraint FK_Employees References Personnel.Employees(EmplNbr), -- Processed By
	DateAllocated date,
	UnitNumber nchar(8) not null
		Constraint FK_Units References Presentation.Units(UnitNumber),
	TenantCode nchar(8) not null
		Constraint FK_Allocations References Rentals.Tenants(TenantCode), -- Processed For
	AmountDeposited money,
	RentStartDate date,
	MonthlyPayment money,
	Constraint PK_Allocations Primary Key(AllocationCode)
);
GO
CREATE TABLE Rentals.Payments
(
	PaymentID int identity(1, 1) not null,
	EmplNbr nchar(8) not null
		Constraint FK_PaymentClerk References Personnel.Employees(EmplNbr), -- Processed By
	DatePaid date,
	AllocationCode int not null
		Constraint FK_AllocatedPayment References Rentals.Allocations(AllocationCode),
	AmountPaid money,
	Constraint PK_Payments Primary Key(PaymentID)
);
GO

INSERT INTO Personnel.Employees -- (EmplNbr, FirstName, LastName, Title, CanProcessPayment)
VALUES(N'48509', N'Mark', N'Walters', N'Owner', 1),
      (N'26847', N'Charles', N'Hackel', N'Manager', 1),
      (N'83795', N'Lynda', N'Wosniak', N'Clerk', 1),
      (N'68038', N'Christopher', N'Polans', N'Maintenance Personnel', 0),
      (N'47582', N'Anne', N'Meleniak', N'Maintenance Personnel', 0);
GO
INSERT INTO Rentals.MaritalStatus([Status])
VALUES(N'Single no Children'),(N'Single With Children'),(N'Married With 1 Child'),
      (N'Married With More Than 1 Child');
GO
INSERT INTO Rentals.Tenants -- (TenantCode,	FullName, MaritalStatusID, PhoneNumber,	EmailAddress)
VALUES(N'794805', N'Margareth Sullivan', 1, N'(301) 881-6688', N'msullivan@emailcity.com'),
      (N'495006', N'Peter Norbert Evans', 1, NULL, NULL),
      (N'803841', N'Bernard H. Roberts', 1, N'(301) 652-2300', N'broberts1970@yahoo.pl'),
      (N'279485', N'Harriet Farrell', 1, N'(202) 466-5151', NULL),
      (N'402950', N'Shawn Daniel Winston', 1, N'(703) 550-7300', N'swinston@hotmail.como'),
      (N'408516', N'Melanie Gurvitz', 1, NULL, N'lilianna1258@mdinc.com'),
      (N'204977', N'Kenneth Archer', 1, N'(202) 223-6500', N'karcher@presidentialcorp.com'),
      (N'927925', N'Helen Gabrielle Myers', 1, NULL, N'hgmyers@somsomno.com');
GO
INSERT Presentation.Units
VALUES(N'101', 0, 1.00, 845.00,  200.00, NULL),
      (N'102', 1, 1.00, 895.00,  250.00, N'Occupied'),
      (N'103', 1, 1.00, 925.00,  275.00, N'Available'),
      (N'104', 2, 1.00, 1050.00, 300.00, N'Occupied'),
      (N'105', 1, 1.00, 885.00,  250.00, N'Available'),
      (N'106', 3, 2.00, 1350.00, 425.00, N'Available'),
      (N'107', 2, 2.00, 1185.00, 400.00, N'Occupied'),
      (N'108', 0, 1.00, 865.00,  225.00, N'Available'),
      (N'109', 2, 1.00, 1050.00, 350.00, N'Available'),
      (N'110', 1, 1.00, 895.00,  250.00, NULL),
      (N'111', 1, 1.00, 895.00,  250.00, NULL),
      (N'112', 0, 1.00, 805.00,  200.00, N'Available'),
      (N'201', 0, 1.00, 825.00,  200.00, N'Available'),
      (N'202', 1, 1.00, 950.00,  325.00, N'Occupied'),
      (N'203', 1, 1.00, 885.00,  250.00, N'Available'),
      (N'204', 2, 2.00, 1125.00, 425.00, N'Available'),
      (N'205', 1, 1.00, 1055.00, 350.00, NULL),
      (N'206', 2, 1.00, 1165.00, 400.00, N'Available'),
      (N'207', 1, 1.00, 895.00,  250.00, N'Occupied'),
      (N'208', 0, 1.00, 815.00,  200.00, N'Available'),
      (N'210', 1, 1.00, 895.00,  350.00, N'Available'),
      (N'211', 2, 2.00, 925.00,  400.00, N'Available'),
      (N'212', 3, 2.00, 1280.00, 500.00, N'Occupied'),
      (N'301', 0, 1.00, 845.00,  200.00, NULL),
      (N'302', 1, 1.00, 925.00,  250.00, N'Occupied'),
      (N'303', 2, 1.00, 985.00,  275.00, N'Occupied'),
      (N'304', 2, 2.00, 1250.00, 300.00, NULL),
      (N'305', 1, 1.00, 945.00,  250.00, N'Available'),
      (N'306', 3, 2.00, 1350.00, 425.00, N'Available'),
      (N'307', 2, 2.00, 1285.00, 400.00, N'Occupied'),
      (N'308', 0, 1.00, 875.00,  225.00, N'Available'),
      (N'309', 2, 1.00, 1150.00, 350.00, N'Available'),
      (N'310', 1, 1.00, 955.00,  250.00, NULL),
      (N'311', 3, 2.00, 1325.00, 500.00, N'Occupied'),
      (N'312', 0, 1.00, 825.00,  200.00, N'Available');
GO
INSERT INTO Rentals.Allocations -- (EmplNbr, DateAllocated, UnitNumber, TenantCode,	AmountDeposited, RentStartDate, MonthlyPayment)
VALUES(N'26847', CAST(Year(SYSDATETIME()) AS NCHAR(4)) + N'-01-02', N'102', N'794805', 250.00, CAST(Year(SYSDATETIME()) AS NCHAR(4)) + N'-02-01', 895.00),
      (N'83795', CAST(Year(SYSDATETIME()) AS NCHAR(4)) + N'-01-14', N'104', N'803841', 300.00, CAST(Year(SYSDATETIME()) AS NCHAR(4)) + N'-02-01', 1050.00),
      (N'83795', CAST(Year(SYSDATETIME()) AS NCHAR(4)) + N'-02-05', N'107', N'279485', 400.00, CAST(Year(SYSDATETIME()) AS NCHAR(4)) + N'-04-01', 1185.00),
      (N'26847', CAST(Year(SYSDATETIME()) AS NCHAR(4)) + N'-02-18', N'202', N'402950', 325.00, CAST(Year(SYSDATETIME()) AS NCHAR(4)) + N'-06-01', 950.00),
      (N'48509', CAST(Year(SYSDATETIME()) AS NCHAR(4)) + N'-05-22', N'307', N'408516', 400.00, CAST(Year(SYSDATETIME()) AS NCHAR(4)) + N'-07-01', 1285.00),
      (N'26847', CAST(Year(SYSDATETIME()) AS NCHAR(4)) + N'-07-20', N'311', N'204977', 500.00, CAST(Year(SYSDATETIME()) AS NCHAR(4)) + N'-08-01', 1325.00);
GO
INSERT INTO Rentals.Payments(EmplNbr, DatePaid,	AllocationCode, AmountPaid)
VALUES(N'83795', CAST(Year(SYSDATETIME()) AS NCHAR(4)) + N'-02-28', 100000, 895.00),
      (N'83795', CAST(Year(SYSDATETIME()) AS NCHAR(4)) + N'-03-02', 100001, 1050.00),
      (N'26847', CAST(Year(SYSDATETIME()) AS NCHAR(4)) + N'-03-30', 100000, 895.00),
      (N'83795', CAST(Year(SYSDATETIME()) AS NCHAR(4)) + N'-03-31', 100001, 1050.00),
      (N'26847', CAST(Year(SYSDATETIME()) AS NCHAR(4)) + N'-04-25', 100000, 895.00),
      (N'26847', CAST(Year(SYSDATETIME()) AS NCHAR(4)) + N'-04-30', 100002, 1185.00),
      (N'48509', CAST(Year(SYSDATETIME()) AS NCHAR(4)) + N'-04-30', 100001, 1050.00),
      (N'83795', CAST(Year(SYSDATETIME()) AS NCHAR(4)) + N'-05-28', 100002, 1185.00),
      (N'26847', CAST(Year(SYSDATETIME()) AS NCHAR(4)) + N'-05-29', 100001, 1050.00),
      (N'83795', CAST(Year(SYSDATETIME()) AS NCHAR(4)) + N'-05-30', 100000, 895.00),
      (N'83795', CAST(Year(SYSDATETIME()) AS NCHAR(4)) + N'-06-26', 100001, 1050.00),
      (N'83795', CAST(Year(SYSDATETIME()) AS NCHAR(4)) + N'-06-30', 100002, 1185.00),
      (N'26847', CAST(Year(SYSDATETIME()) AS NCHAR(4)) + N'-07-01', 100000, 895.00),
      (N'83795', CAST(Year(SYSDATETIME()) AS NCHAR(4)) + N'-07-03', 100003, 950.00),
      (N'26847', CAST(Year(SYSDATETIME()) AS NCHAR(4)) + N'-07-26', 100000, 895.00),
      (N'83795', CAST(Year(SYSDATETIME()) AS NCHAR(4)) + N'-07-28', 100002, 1185.00),
      (N'26847', CAST(Year(SYSDATETIME()) AS NCHAR(4)) + N'-07-30', 100000, 895.00),
      (N'26847', CAST(Year(SYSDATETIME()) AS NCHAR(4)) + N'-07-31', 100004, 1285.00),
      (N'83795', CAST(Year(SYSDATETIME()) AS NCHAR(4)) + N'-07-31', 100003, 950.00);
GO