-- Lambda Square: Apartment Business
-- Lambda Square: Apartment Business
-- ===============================================================
-- Database: LambdaSquare1
-- Author: FunctionX
-- Date Created: Saturday, June 02, 2012
-- 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'LambdaSquare1'
)
DROP DATABASE LambdaSquare1;
GO
CREATE DATABASE LambdaSquare1;
GO
USE LambdaSquare1;
GO
CREATE SCHEMA Presentation;
GO
CREATE SCHEMA Rentals;
GO
CREATE SCHEMA Personnel;
GO
CREATE TABLE Personnel.Employees
(
EmployeeNumber nvarchar(10) not null unique,
FirstName nvarchar(24),
LastName nvarchar(24),
EmployeeName AS LastName + N', ' + FirstName,
Title nvarchar(50),
CanProcessPayment bit,
Notes nvarchar(max),
Constraint PK_Employees Primary Key(EmployeeNumber)
);
GO
CREATE TABLE Rentals.MaritalsStatus
(
MaritalStatus nvarchar(25) not null,
StatusDescription nvarchar(max),
Constraint PK_MaritalsStatus Primary Key(MaritalStatus)
);
GO
CREATE TABLE Rentals.Tenants
(
TenantCode nvarchar(10) not null unique,
FirstName nvarchar(24),
LastName nvarchar(24),
TenantName AS LastName + N', ' + FirstName,
MaritalStatus nvarchar(25),
NumberOfChildren tinyint,
PhoneNumber nvarchar(20),
EmailAddress nvarchar(40),
Notes nvarchar(max),
Constraint PK_Tenants Primary Key(TenantCode),
Constraint FK_MaritalsStatus Foreign Key(MaritalStatus)
References Rentals.MaritalsStatus(MaritalStatus)
);
GO
CREATE TABLE Rentals.OccupanciesStatus
(
OccupancyStatus nvarchar(25) not null,
StatusDescription nvarchar(max),
Constraint PK_OccupanciesStatus Primary Key(OccupancyStatus)
);
GO
CREATE TABLE Presentation.Units
(
UnitNumber nvarchar(10) not null unique,
Bedrooms int,
Bathrooms real,
MonthlyPrice money,
SecurityDeposit money,
OccupancyStatus nvarchar(25),
Constraint PK_Units Primary Key(UnitNumber),
Constraint FK_OccupanciesStatus Foreign Key(OccupancyStatus)
References Rentals.OccupanciesStatus(OccupancyStatus)
);
GO
CREATE TABLE Rentals.Registrations
(
RegistrationID int identity(1001, 1) not null,
RegistrationDate Date,
EmployeeNumber nvarchar(10), -- Processed By
TenantCode nvarchar(10), -- Processed For
UnitNumber nvarchar(10) not null,
RentStartDate date,
Constraint PK_Registrations Primary Key(RegistrationID),
Constraint FK_Registrants Foreign Key(EmployeeNumber) References Personnel.Employees(EmployeeNumber),
Constraint FK_Tenants Foreign Key(TenantCode) References Rentals.Tenants(TenantCode),
Constraint FK_Units Foreign Key(UnitNumber) References Presentation.Units(UnitNumber)
);
GO
CREATE TABLE Rentals.Payments
(
ReceiptNumber int identity(100001, 1) not null,
PaymentDate date,
EmployeeNumber nvarchar(10), -- Processed By
RegistrationID int,
AmountPaid money,
Constraint PK_Payments Primary Key(ReceiptNumber),
Constraint FK_PaymentReceiver Foreign Key(EmployeeNumber) References Personnel.Employees(EmployeeNumber),
Constraint FK_Registrations Foreign Key(RegistrationID) References Rentals.Registrations(RegistrationID)
);
GO
/*
INSERT INTO Personnel.Employees -- (EmployeeNumber, 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 -- (EmployeeNumber, 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(EmployeeNumber, 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
*/