-- Lambda Square Apartments --
IF EXISTS (SELECT name
FROM sys.databases WHERE name = N'LambdaSquareApartments1')
DROP DATABASE LambdaSquareApartments1;
GO
CREATE DATABASE LambdaSquareApartments1;
GO
USE LambdaSquareApartments1;
GO
CREATE SCHEMA HumanResources;
GO
CREATE SCHEMA Rentals;
GO
CREATE TABLE HumanResources.Employees
(
EmployeeNumber nchar(7) Not Null,
FirstName nvarchar(25) NULL,
LastName nvarchar(25) Not Null,
Employee AS CONCAT(LastName, ', ', FirstName),
Title nvarchar(100),
Constraint PK_Employees Primary Key(EmployeeNumber)
);
GO
CREATE TABLE Rentals.Apartments
(
UnitCode nvarchar(10) not null,
ApartmentNumber nvarchar(5),
Bedrooms int,
Bathrooms float,
MonthlyRent money,
SecurityDeposit money,
OccupancyStatus nvarchar(20),
Constraint PK_Apartments Primary Key(UnitCode)
);
GO
CREATE TABLE Rentals.Registrations
(
RegistrationID int identity(1001, 1),
RegistrationDate date,
EmployeeNumber nchar(7),
TenantCode nvarchar(10),
FirstName nvarchar(25),
LastName nvarchar(25),
MaritalStatus nvarchar(20),
NumberOfChildren smallint,
PhoneNumber nvarchar(20),
EmailAddress nvarchar(50),
TenancyStatus nvarchar(20),
UnitCode nvarchar(10),
RentStartDate date,
Constraint PK_Registrations Primary Key(RegistrationID),
Constraint FK_RegistrationsClerks Foreign Key(EmployeeNumber)
References HumanResources.Employees(EmployeeNumber),
Constraint FK_ApartmentsRegistrations Foreign Key(UnitCode)
References Rentals.Apartments(UnitCode)
);
GO
CREATE TABLE Rentals.Payments
(
PaymentID int identity(1, 1),
PaymentDate date,
EmployeeNumber nchar(7),
RegistrationID int,
AmountPaid money,
Notes nvarchar(max),
Constraint PK_Payments Primary Key(PaymentID),
Constraint FK_PaymentsClerks Foreign Key(EmployeeNumber)
References HumanResources.Employees(EmployeeNumber),
Constraint FK_RegistrationsPayments Foreign Key(RegistrationID)
References Rentals.Registrations(RegistrationID)
);
GO
CREATE PROCEDURE Rentals.GetRegistrations
AS
SELECT regs.RegistrationID [Regist ID],
regs.RegistrationDate [Regist Date],
empls.Employee,
regs.TenantCode [Tenant Code],
regs.MaritalStatus [Marital Status],
regs.TenancyStatus [Tenancy Status],
regs.NumberOfChildren Children,
regs.PhoneNumber [Phone #],
regs.EmailAddress [Email Address],
CONCAT(aprts.UnitCode, N', Unit #: ', aprts.UnitNumber, N', ', aprts.Bedrooms, N' bedrooms, ', aprts.Bathrooms, N' bathrooms, ', aprts.MonthlyRent, N'/month, deposit: ', aprts.SecurityDeposit) Apartment,
regs.RentStartDate [Start Date]
FROM Rentals.Registrations regs
INNER JOIN HumanResources.Employees empls ON regs.EmployeeNumber = empls.EmployeeNumber
INNER JOIN Rentals.Apartments aprts ON regs.UnitCode = aprts.UnitCode
GO
INSERT INTO Rentals.Apartments(UnitCode, UnitNumber, Bedrooms, Bathrooms, monthlyRent, SecurityDeposit, OccupancyStatus)
VALUES(N'399475', '101', 2, 2, 1150, 650, N'Available'),
(N'508293', '102', 1, 1, 950, 500, N'Needs Repair'),
(N'729397', '103', 1, 1, 925, 500, N'Available'),
(N'928364', '104', 3, 2, 1350, 850, N'Available'),
(N'297297', '105', 2, 1, 1150, 550, N'Available'),
(N'492739', '106', 3, 2, 1350, 850, N'Available'),
(N'692797', '107', 3, 2, 1285, 850, N'Not Ready'),
(N'829475', '108', 1, 1, 885, 500, N'Available'),
(N'139749', '109', 2, 2, 1150, 650, N'Available'),
(N'369294', '110', 1, 1, 895, 500, N'Available'),
(N'502084', '111', 2, 2, 1145, 650, N'Available'),
(N'829397', '112', 2, 1, 1085, 600, N'Available'),
(N'292739', '201', 2, 1, 1185, 650, N'Available'),
(N'496055', '202', 1, 1, 895, 500, N'Available'),
(N'939595', '203', 1, 1, 925, 500, N'Available'),
(N'384068', '204', 3, 2, 1250, 850, N'Available'),
(N'824850', '205', 2, 1, 1100, 600, N'Available'),
(N'620485', '206', 3, 2, 1300, 850, N'Available'),
(N'294940', '207', 3, 2, 1350, 850, N'Available'),
(N'602048', '208', 1, 1, 920, 500, N'Available'),
(N'829479', '209', 2, 2, 1150, 650, N'Available'),
(N'280484', '210', 1, 1, 895, 500, N'Available'),
(N'602408', '211', 2, 2, 1175, 650, N'Available'),
(N'384086', '212', 2, 1, 1075, 600, N'Available'),
(N'397493', '301', 2, 2, 1175, 650, N'Available'),
(N'625941', '302', 1, 1, 950, 500, N'Available'),
(N'404950', '303', 1, 1, 925, 500, N'Available'),
(N'304806', '304', 3, 2, 1250, 850, N'Available'),
(N'844850', '305', 2, 1, 1100, 600, N'Needs Repair'),
(N'596305', '306', 3, 2, 1300, 850, N'Available'),
(N'138408', '307', 3, 2, 1350, 850, N'Available'),
(N'305860', '308', 1, 1, 920, 500, N'Available'),
(N'847584', '309', 2, 2, 1150, 650, N'Available'),
(N'746959', '310', 1, 1, 935, 500, N'Available'),
(N'359405', '311', 2, 2, 1175, 650, N'Available'),
(N'308505', '312', 2, 1, 1075, 600, N'Available');
GO
/*
INSERT INTO Rentals.Registrations(RegistrationDate, EmployeeNumber, TenantCode, FirstName, LastName, MaritalStatus, NumberOfChildren, PhoneNumber, EmailAddress, TenancyStatus, UnitCode, RentStartDate)
VALUES(N'20140612', N'38408', N'29-485-05', N'Ann', N'Sanders', N'Married', 1, N'(240) 524 -2831', N'annsanders@emailcity.com', N'Active', N'139749', N'20140701'),
(N'20140615', N'20448', N'83-400-85', N'Mahty', N'Shaoul', N'Married', 2, N'202-729-1574', N'mshaoulman@gmail.com', N'Active', N'928364', N'20140901'),
(N'20140622', N'40685', N'48-602-73', N'Frank', N'Ulm', N'Single', 0, N'(301) 882-0704', N'fulm112244@yaho.com', N'Active', N'729397', N'20140701'),
(N'20140622', N'93947', N'24-385-30', N'Elise', N'Provoski', N'Separated', 1, N'(443) 974-9631', N'eprevalence@yahoo.com', N'Active', N'844850', N'20140801'),
(N'20140723', N'93947', N'92-048-11', N'Grace', N'Curryan', N'Married', 1, N'(240) 927-0993', N'gcarrier@gmail.com', N'Active', N'297297', N'20140901'),
(N'20140725', N'38408', N'51-304-58', N'Tracy', N'Warrens', N'Divorced', 2, N'202-793-6924', N'twarrior12@hotmail.coom', N'Active', N'492739', N'20140801'),
(N'20140801', N'38408', N'72-384-04', N'Paul', N'Yamo', N'Married', 3, N'(410-792-7045', N'pyamo@hr.umd.edu', N'Active', N'384068', N'20141001'),
(N'20140810', N'40685', N'62-405-29', N'Nancy', N'Shermann', N'Single', 1, N'(703) 338-2973', N'nsherre@emailcity.com', N'Active', N'829475', N'20140901'),
(N'20140912', N'20448', N'72-484-04', N'Michael', N'Tiernan', N'Single', 0, N'301-274-9285', N'resdev.globelan.net', N'Active', N'829479', N'20141101'),
(N'20141005', N'38408', N'60-285-83', N'Phillippe', N'Anderson', N'Single', 0, N'202-729-1574', N'philanders@gmail.com', N'Active', N'496055', N'20141101');
GO
*/
/*
INSERT INTO Rentals.Payments(PaymentDate, EmployeeNumber, RegistrationID, AmountPaid, Notes)
VALUES(N'20140612', N'38408', 1001, 650, N'This is the payment for the security deposit.');
GO
INSERT INTO Rentals.Payments(PaymentDate, EmployeeNumber, RegistrationID, AmountPaid)
VALUES(N'20140728', N'38408', 1001, 1150),
(N'20140825', N'38408', 1001, 1150),
(N'20140928', N'20448', 1001, 1150),
(N'20141027', N'93947', 1001, 1150),
(N'20141129', N'93947', 1001, 1150),
(N'20141228', N'20448', 1001, 1150),
(N'20150126', N'38408', 1001, 1150),
(N'20150220', N'20448', 1001, 1150);
GO
INSERT INTO Rentals.Payments(PaymentDate, EmployeeNumber, RegistrationID, AmountPaid, Notes)
VALUES(N'20140814', N'20448', 1002, 850, N'Payment for security deposit');
GO
INSERT INTO Rentals.Payments(PaymentDate, EmployeeNumber, RegistrationID, AmountPaid)
VALUES(N'20140825', N'38408', 1002, 1350),
(N'20140928', N'40685', 1002, 1350),
(N'20141030', N'38408', 1002, 1350),
(N'20141126', N'38408', 1002, 1350),
(N'20141231', N'38408', 1002, 1350),
(N'20150129', N'93947', 1002, 1350),
(N'20150226', N'38408', 1002, 1350)
GO
INSERT INTO Rentals.Payments(PaymentDate, EmployeeNumber, RegistrationID, AmountPaid, Notes)
VALUES(N'20140620', N'40685', 1003, 500, N'Security Deposit');
GO
INSERT INTO Rentals.Payments(PaymentDate, EmployeeNumber, RegistrationID, AmountPaid)
VALUES(N'20140727', N'38408', 1003, 925),
(N'20140826', N'20448', 1003, 925),
(N'20141005', N'38408', 1003, 925),
(N'20141031', N'40685', 1003, 925),
(N'20141130', N'38408', 1003, 925),
(N'20141230', N'20448', 1003, 925),
(N'20150128', N'93947', 1003, 925),
(N'20150303', N'40685', 1003, 925);
GO
INSERT INTO Rentals.Payments(PaymentDate, EmployeeNumber, RegistrationID, AmountPaid, Notes)
VALUES(N'20140813', N'40685', 1004, 600, N'Security Deposit');
GO
INSERT INTO Rentals.Payments(PaymentDate, EmployeeNumber, RegistrationID, AmountPaid)
VALUES(N'20140827', N'40685', 1004, 1100),
(N'20140925', N'20448', 1004, 1100),
(N'20141024', N'38408', 1004, 1100),
(N'20141128', N'20448', 1004, 1100),
(N'20141228', N'38408', 1004, 1100);
GO
INSERT INTO Rentals.Payments(PaymentDate, EmployeeNumber, RegistrationID, AmountPaid, Notes)
VALUES(N'20150210', N'20448', 1004, 100, N'This is a fee for late payment.');
GO
INSERT INTO Rentals.Payments(PaymentDate, EmployeeNumber, RegistrationID, AmountPaid)
VALUES(N'20150210', N'20448', 1004, 1100),
(N'20150301', N'38408', 1004, 1100);
GO
INSERT INTO Rentals.Payments(PaymentDate, EmployeeNumber, RegistrationID, AmountPaid, Notes)
VALUES(N'20140918', N'20448', 1005, 550, N'Security Deposit');
GO
INSERT INTO Rentals.Payments(PaymentDate, EmployeeNumber, RegistrationID, AmountPaid)
VALUES(N'20140928', N'40685', 1005, 1150),
(N'20141024', N'38408', 1005, 1150),
(N'20141128', N'38408', 1005, 1150),
(N'20141228', N'38408', 1005, 1150),
(N'20150123', N'20448', 1005, 1150),
(N'20150225', N'20448', 1005, 1150);
GO
INSERT INTO Rentals.Payments(PaymentDate, EmployeeNumber, RegistrationID, AmountPaid, Notes)
VALUES(N'20140801', N'40685', 1006, 850, N'Security Deposit');
GO
INSERT INTO Rentals.Payments(PaymentDate, EmployeeNumber, RegistrationID, AmountPaid)
VALUES(N'20140830', N'38408', 1006, 1350),
(N'20140925', N'20448', 1006, 1350),
(N'20141025', N'40685', 1006, 1350),
(N'20141127', N'38408', 1006, 1350),
(N'20141225', N'38408', 1006, 1350);
GO
INSERT INTO Rentals.Payments(PaymentDate, EmployeeNumber, RegistrationID, AmountPaid, Notes)
VALUES(N'20140808', N'40685', 1007, 850, N'Security Deposit');
GO
INSERT INTO Rentals.Payments(PaymentDate, EmployeeNumber, RegistrationID, AmountPaid)
VALUES(N'20141025', N'40685', 1007, 1250),
(N'20141128', N'38408', 1007, 1250),
(N'20141225', N'38408', 1007, 1250);
GO
INSERT INTO Rentals.Payments(PaymentDate, EmployeeNumber, RegistrationID, AmountPaid, Notes)
VALUES(N'20140808', N'40685', 1008, 500, N'Security Deposit');
GO
INSERT INTO Rentals.Payments(PaymentDate, EmployeeNumber, RegistrationID, AmountPaid)
VALUES(N'20140925', N'20448', 1008, 885),
(N'20141029', N'93947', 1008, 885),
(N'20141126', N'38408', 1008, 885),
(N'20141231', N'20448', 1008, 885);
GO
INSERT INTO Rentals.Payments(PaymentDate, EmployeeNumber, RegistrationID, AmountPaid, Notes)
VALUES(N'20140917', N'40685', 1009, 650, N'Security Deposit');
GO
INSERT INTO Rentals.Payments(PaymentDate, EmployeeNumber, RegistrationID, AmountPaid)
VALUES(N'20141130', N'20448', 1009, 1150),
(N'20141227', N'20448', 1009, 1150);
GO
INSERT INTO Rentals.Payments(PaymentDate, EmployeeNumber, RegistrationID, AmountPaid, Notes)
VALUES(N'20141008', N'40685', 1010, 500, N'Security Deposit');
GO
INSERT INTO Rentals.Payments(PaymentDate, EmployeeNumber, RegistrationID, AmountPaid)
VALUES(N'20141130', N'20448', 1010, 895),
(N'20141228', N'38408', 1010, 895);
GO
*/