--Assignment 1

Use Master
Go
if exists
	(Select [name] 
	from sys.databases 
	where [name]='aptManagement')
Begin
Drop database AptManagementb
End 
go
Create Database AptManagementb
go
use AptManagementb
go
Create table Tenant
(
	TenantID char(10) primary key,
	Firstname varchar(20),
	Lastname varchar(30)not null,
	Phone char(10)
)
go
Create table Apartment
(
	AptNumber char(3) Primary Key,
	Bedrooms smallint not null,
	AptView bit
)
go
Create table Lease
(
	LeaseNumber char(10) primary key,
	StartDate datetime not null,
	EndDate Datetime not null,
	DepositAmount money not null,
	RentAmount money not null,
	TenantID char(10) foreign key references Tenant(tenantID),
	AptNumber char(3) foreign key references Apartment(aptNumber)
)
go
Create table RentHistory
(
	LeaseNumber char(10),
	PaymentDate DateTime,
	Constraint PK_RentHistory Primary key(Leasenumber, paymentdate),
	Constraint FK_LeaseNumber Foreign Key(Leasenumber) 
		references Lease(Leasenumber),
	PaymentAmount money
)
go
Create table MaintenanceTask
(
	MaintenanceTaskID int Identity(1,1) Primary Key,
	TaskName varchar(25) not null,
	TaskDescription varchar(200)
)
go
Create table MaintenanceRequest
(
	RequestID int identity(1,1) primary key,
	RequestDate Datetime not null,
	LeaseNumber char(10) Foreign Key references Lease(LeaseNumber)
)
go
Create table MaintenanceReqDetail
(
	MaintDetID int identity(1,1) primary key,
	MaintenanceTaskID int foreign Key references Maintenancetask(MaintenanceTaskID),
	RequestID int foreign key references MaintenanceRequest(RequestID),
	DateResolved datetime null,
	Notes text null
)

--activities for assignement two


Alter Table Apartment
Add Constraint chk_bedrooms check
	(bedrooms between 1 and 3)

Alter Table MaintenanceReqDetail
Add DateInspected DateTime


Create table TenantRoomates
(
	RoommateID int identity(1,1) Primary key,
	LeaseNumber char(10) Foreign Key references Lease(LeaseNumber),
	RMLastName varchar(30) not null,
	RMFirstName varchar(20),
	BeginDate DateTime not null,
	MoveOutDate varchar(30)
)

Create index ix_leaseTenant on Lease(tenantID)
Create index ix_leaseAptNumber on Lease(aptnumber)
Create index ix_RentHistoryLease on RentHistory(LeaseNumber)
Create index ix_RequestLease on MaintenanceRequest(LeaseNumber)
Create index ix_MRequestDetRequest on MaintenanceReqDetail(RequestID)
Create index ix_MRequestDetTask on MaintenanceReqDetail(MaintenanceTaskID)
Create index ix_TenantRoomateLease on TenantRoomates(LeaseNumber)

--first floor
Insert into Apartment(AptNumber, Bedrooms, AptView)
Values('101',1,0)
Insert into Apartment(AptNumber, Bedrooms, AptView)
Values('102',3,0)
Insert into Apartment(AptNumber, Bedrooms, AptView)
Values('103',2,0)
Insert into Apartment(AptNumber, Bedrooms, AptView)
Values('104',1,0)
Insert into Apartment(AptNumber, Bedrooms, AptView)
Values('105',3,0)
Insert into Apartment(AptNumber, Bedrooms, AptView)
Values('106',2,0)

--second floor
Insert into Apartment(AptNumber, Bedrooms, AptView)
Values('201',1,0)
Insert into Apartment(AptNumber, Bedrooms, AptView)
Values('202',3,0)
Insert into Apartment(AptNumber, Bedrooms, AptView)
Values('203',2,0)
Insert into Apartment(AptNumber, Bedrooms, AptView)
Values('204',1,0)
Insert into Apartment(AptNumber, Bedrooms, AptView)
Values('205',3,0)
Insert into Apartment(AptNumber, Bedrooms, AptView)
Values('206',2,0)

--3rd floor
Insert into Apartment(AptNumber, Bedrooms, AptView)
Values('301',1,1)
Insert into Apartment(AptNumber, Bedrooms, AptView)
Values('302',3,1)
Insert into Apartment(AptNumber, Bedrooms, AptView)
Values('303',2,1)
Insert into Apartment(AptNumber, Bedrooms, AptView)
Values('304',1,0)
Insert into Apartment(AptNumber, Bedrooms, AptView)
Values('305',3,0)
Insert into Apartment(AptNumber, Bedrooms, AptView)
Values('306',2,0)

--tenant
Insert into tenant(TenantID, Firstname, lastname, phone)
Values('tmartins01','Thomas', 'Martins', '2065551234')

Insert into Lease(LeaseNumber,startdate, enddate, rentamount, tenantID,Aptnumber, DepositAmount)
Values('10506201','1/05/2006','7/05/2006',850,'tmartins01','201',1150)




--inserts into tenant

Insert into tenant (TenantID, FirstName, LastName, Phone)
values ('jsanders01', 'Jason', 'Sanders', '2065554343')

Insert into tenant (TenantID, FirstName, LastName, Phone)
values ('hmohn01', 'Heather', 'Mohn', '2065551098')

Insert into tenant (TenantID, FirstName, LastName, Phone)
values ('tjohnson01', 'Tom', 'Johnson', '2065555678')

Insert into tenant (TenantID, FirstName, LastName, Phone)
values ('mbanner01', 'Mary', 'Banner', '2065558787')

Insert into tenant (TenantID, FirstName, LastName, Phone)
values ('sdaniels', 'Steven', 'Daniels', '2065553409')

Insert into tenant (TenantID, FirstName, LastName, Phone)
values ('skim02', 'Susan', 'Kim', '2065550077')

Insert into tenant (TenantID, FirstName, LastName, Phone)
values ('skim01', 'Samuel', 'Kim', '2065551456')

Insert into tenant (TenantID, FirstName, LastName, Phone)
values ('lcarrol01', 'Larry', 'Carrol', '2065554765')

Insert into tenant (TenantID, FirstName, LastName, Phone)
values ('tnickles01', 'Tara', 'Nichols', '2065552219')

Insert into tenant (TenantID, FirstName, LastName, Phone)
values ('msanders01', 'Martin', 'Sanders', '206554001')

Insert into tenant (TenantID, FirstName, LastName, Phone)
values ('bunderhi01', 'Brenda', 'UnderHill', '206559753')

Insert into tenant (TenantID, FirstName, LastName, Phone)
values ('tanders01', 'Tracy', 'Anderson', '2065550125')

Insert into tenant (TenantID, FirstName, LastName, Phone)
values ('ladison01', 'Lester', 'Adison', '206557823')

Insert into tenant (TenantID, FirstName, LastName, Phone)
values ('ccarter01', 'Cecilia', 'Carter', '206552983')

Insert into tenant (TenantID, FirstName, LastName, Phone)
values ('rhill01', 'Ryan', 'Hill', '2065556677')


--insert into Lease
Insert into Lease (LeaseNumber, AptNumber, TenantID, StartDate, EndDate, DepositAmount, RentAmount)
values('60205201','201','jsanders01', '6/02/2005', '12/30/2005', 1150.00,850)

Insert into Lease (LeaseNumber, AptNumber, TenantID, StartDate, EndDate, DepositAmount, RentAmount)
values('30106103','103','bunderhi01', '3/01/2006', '3/01/2007', 1450.00,1150)

Insert into Lease (LeaseNumber, AptNumber, TenantID, StartDate, EndDate, DepositAmount, RentAmount)
values('10106104','104','ccarter01', '2/01/2006', '7/30/2005', 1150.00,850)

Insert into Lease (LeaseNumber, AptNumber, TenantID, StartDate, EndDate, DepositAmount, RentAmount)
values('70106301','301','hmohn01', '7/01/2005', '7/01/2006', 1250.00,950)

Insert into Lease (LeaseNumber, AptNumber, TenantID, StartDate, EndDate, DepositAmount, RentAmount)
values('30106205','205','ladison01', '3/01/2006', '8/30/2006', 1700.00,1400)

Insert into Lease (LeaseNumber, AptNumber, TenantID, StartDate, EndDate, DepositAmount, RentAmount)
values('30106203','203','lcarrol01', '3/01/2006', '3/01/2007', 1450.00,1150)

Insert into Lease (LeaseNumber, AptNumber, TenantID, StartDate, EndDate, DepositAmount, RentAmount)
values('20106102','102','mbanner01', '2/01/2006', '7/30/2006', 1700.00,1400)

Insert into Lease (LeaseNumber, AptNumber, TenantID, StartDate, EndDate, DepositAmount, RentAmount)
values('30206304','304','msanders01', '3/02/2006', '2/28/2007', 1150.00,850)

Insert into Lease (LeaseNumber, AptNumber, TenantID, StartDate, EndDate, DepositAmount, RentAmount)
values('10106106','106','rhill01', '1/01/2006', '12/30/2007', 1450.00,1150)

Insert into Lease (LeaseNumber, AptNumber, TenantID, StartDate, EndDate, DepositAmount, RentAmount)
values('8012005','201','sdaniels', '8/01/2005', '7/30/2006', 1550.00,1250)

Insert into Lease (LeaseNumber, AptNumber, TenantID, StartDate, EndDate, DepositAmount, RentAmount)
values('50504105','105','skim01', '5/05/2004', '4/30/2005', 1700.00,1400)

Insert into Lease (LeaseNumber, AptNumber, TenantID, StartDate, EndDate, DepositAmount, RentAmount)
values('10106202','202','skim02', '1/01/2006', '5/30/2006', 1700.00,1400)

Insert into Lease (LeaseNumber, AptNumber, TenantID, StartDate, EndDate, DepositAmount, RentAmount)
values('30106306','306','tanders01', '3/01/2006', '2/28/2007', 1350.00,1150)

Insert into Lease (LeaseNumber, AptNumber, TenantID, StartDate, EndDate, DepositAmount, RentAmount)
values('401206206','206','tjohnson01', '4/01/2006', '3/30/2007', 1450.00,1150)

Insert into Lease (LeaseNumber, AptNumber, TenantID, StartDate, EndDate, DepositAmount, RentAmount)
values('20106204','204','tnickles01', '2/01/2006', '7/31/2006', 1150.00,850)

--Insert into RentHistory

Insert into RentHistory(LeaseNumber, PaymentDate, PaymentAmount)
Values ('10106104','3/1/2006', 850)

Insert into RentHistory(LeaseNumber, PaymentDate, PaymentAmount)
Values ('10106104','4/1/2006', 850)

Insert into RentHistory(LeaseNumber, PaymentDate, PaymentAmount)
Values ('10106106','1/1/2006', 1150)

Insert into RentHistory(LeaseNumber, PaymentDate, PaymentAmount)
Values ('10106106','2/1/2006', 1150)

Insert into RentHistory(LeaseNumber, PaymentDate, PaymentAmount)
Values ('10106106','3/1/2006', 1150)

Insert into RentHistory(LeaseNumber, PaymentDate, PaymentAmount)
Values ('10106106','4/1/2006', 1150)

Insert into RentHistory(LeaseNumber, PaymentDate, PaymentAmount)
Values ('10106202','1/1/2006', 1400)

Insert into RentHistory(LeaseNumber, PaymentDate, PaymentAmount)
Values ('10106202','2/1/2006', 1400)

Insert into RentHistory(LeaseNumber, PaymentDate, PaymentAmount)
Values ('10106202','3/1/2006', 1400)

Insert into RentHistory(LeaseNumber, PaymentDate, PaymentAmount)
Values ('10106202','4/1/2006', 1400)

Insert into RentHistory(LeaseNumber, PaymentDate, PaymentAmount)
Values ('10506201','1/5/2006', 850)

Insert into RentHistory(LeaseNumber, PaymentDate, PaymentAmount)
Values ('10506201','2/1/2006', 850)

Insert into RentHistory(LeaseNumber, PaymentDate, PaymentAmount)
Values ('10506201','3/10/2006', 850)

Insert into RentHistory(LeaseNumber, PaymentDate, PaymentAmount)
Values ('10506201','4/1/2006', 850)

Insert into RentHistory(LeaseNumber, PaymentDate, PaymentAmount)
Values ('20106102','2/1/2006', 1400)

Insert into RentHistory(LeaseNumber, PaymentDate, PaymentAmount)
Values ('20106102','3/1/2006', 1400)

Insert into RentHistory(LeaseNumber, PaymentDate, PaymentAmount)
Values ('20106102','4/1/2006', 1400)

Insert into RentHistory(LeaseNumber, PaymentDate, PaymentAmount)
Values ('20106204','2/1/2006', 850)

Insert into RentHistory(LeaseNumber, PaymentDate, PaymentAmount)
Values ('20106204','3/1/2006', 850)

Insert into RentHistory(LeaseNumber, PaymentDate, PaymentAmount)
Values ('20106204','4/1/2006', 850)

Insert into RentHistory(LeaseNumber, PaymentDate, PaymentAmount)
Values ('30106103','3/1/2006', 1150)

Insert into RentHistory(LeaseNumber, PaymentDate, PaymentAmount)
Values ('30106103','4/1/2006', 1150)

Insert into RentHistory(LeaseNumber, PaymentDate, PaymentAmount)
Values ('30106203','3/1/2006', 1150)

Insert into RentHistory(LeaseNumber, PaymentDate, PaymentAmount)
Values ('30106205','3/1/2006', 1400)

Insert into RentHistory(LeaseNumber, PaymentDate, PaymentAmount)
Values ('30106205','4/1/2006', 1400)

Insert into RentHistory(LeaseNumber, PaymentDate, PaymentAmount)
Values ('30106306','3/1/2006', 1150)

Insert into RentHistory(LeaseNumber, PaymentDate, PaymentAmount)
Values ('30106306','3/1/2006', 1000)

Insert into RentHistory(LeaseNumber, PaymentDate, PaymentAmount)
Values ('30206304','3/2/2006', 850)

Insert into RentHistory(LeaseNumber, PaymentDate, PaymentAmount)
Values ('30206304','4/1/2006', 850)

Insert into RentHistory(LeaseNumber, PaymentDate, PaymentAmount)
Values ('401206206','4/1/2006', 1150)

Insert into RentHistory(LeaseNumber, PaymentDate, PaymentAmount)
Values ('50504105','5/1/2004', 1400)

Insert into RentHistory(LeaseNumber, PaymentDate, PaymentAmount)
Values ('50504105','6/1/2004', 1400)

Insert into RentHistory(LeaseNumber, PaymentDate, PaymentAmount)
Values ('50504105','7/1/2004', 1400)

Insert into RentHistory(LeaseNumber, PaymentDate, PaymentAmount)
Values ('50504105','8/1/2004', 1400)

Insert into RentHistory(LeaseNumber, PaymentDate, PaymentAmount)
Values ('50504105','9/1/2004', 1400)

Insert into RentHistory(LeaseNumber, PaymentDate, PaymentAmount)
Values ('50504105','10/1/2004', 1400)

Insert into RentHistory(LeaseNumber, PaymentDate, PaymentAmount)
Values ('50504105','12/1/2004', 1400)

Insert into RentHistory(LeaseNumber, PaymentDate, PaymentAmount)
Values ('50504105','1/1/2005', 1400)

Insert into RentHistory(LeaseNumber, PaymentDate, PaymentAmount)
Values ('50504105','2/1/2005', 1400)

Insert into RentHistory(LeaseNumber, PaymentDate, PaymentAmount)
Values ('50504105','3/1/2005', 1400)

Insert into RentHistory(LeaseNumber, PaymentDate, PaymentAmount)
Values ('50504105','4/1/2005', 1400)

Insert into RentHistory(LeaseNumber, PaymentDate, PaymentAmount)
Values ('60205201','6/1/2005', 850)

Insert into RentHistory(LeaseNumber, PaymentDate, PaymentAmount)
Values ('60205201','7/1/2005', 850)

Insert into RentHistory(LeaseNumber, PaymentDate, PaymentAmount)
Values ('60205201','8/1/2005', 850)

Insert into RentHistory(LeaseNumber, PaymentDate, PaymentAmount)
Values ('60205201','9/1/2005', 850)

Insert into RentHistory(LeaseNumber, PaymentDate, PaymentAmount)
Values ('60205201','10/1/2005', 850)

Insert into RentHistory(LeaseNumber, PaymentDate, PaymentAmount)
Values ('60205201','11/1/2005', 850)

Insert into RentHistory(LeaseNumber, PaymentDate, PaymentAmount)
Values ('60205201','12/1/2005', 850)

Insert into RentHistory(LeaseNumber, PaymentDate, PaymentAmount)
Values ('60205201','1/1/2006', 850)

Insert into RentHistory(LeaseNumber, PaymentDate, PaymentAmount)
Values ('60205201','2/1/2006', 700)

Insert into RentHistory(LeaseNumber, PaymentDate, PaymentAmount)
Values ('60205201','3/1/2006', 1000)

Insert into RentHistory(LeaseNumber, PaymentDate, PaymentAmount)
Values ('60205201','4/1/2006', 850)

Insert into RentHistory(LeaseNumber, PaymentDate, PaymentAmount)
Values ('70106301','7/1/2005', 950)

Insert into RentHistory(LeaseNumber, PaymentDate, PaymentAmount)
Values ('70106301','8/1/2005', 950)

Insert into RentHistory(LeaseNumber, PaymentDate, PaymentAmount)
Values ('70106301','9/1/2005', 950)

Insert into RentHistory(LeaseNumber, PaymentDate, PaymentAmount)
Values ('70106301','10/1/2005', 950)

Insert into RentHistory(LeaseNumber, PaymentDate, PaymentAmount)
Values ('70106301','11/1/2005', 950)

Insert into RentHistory(LeaseNumber, PaymentDate, PaymentAmount)
Values ('70106301','12/1/2005', 950)

Insert into RentHistory(LeaseNumber, PaymentDate, PaymentAmount)
Values ('70106301','1/1/2006', 950)

Insert into RentHistory(LeaseNumber, PaymentDate, PaymentAmount)
Values ('70106301','2/1/2006', 950)

Insert into RentHistory(LeaseNumber, PaymentDate, PaymentAmount)
Values ('70106301','3/1/2006', 950)

Insert into RentHistory(LeaseNumber, PaymentDate, PaymentAmount)
Values ('70106301','4/1/2006', 950)

Insert into RentHistory(LeaseNumber, PaymentDate, PaymentAmount)
Values ('8012005','8/1/2005', 1250)

Insert into RentHistory(LeaseNumber, PaymentDate, PaymentAmount)
Values ('8012005','9/1/2005', 1250)

Insert into RentHistory(LeaseNumber, PaymentDate, PaymentAmount)
Values ('8012005','10/1/2005', 1250)

Insert into RentHistory(LeaseNumber, PaymentDate, PaymentAmount)
Values ('8012005','11/1/2005', 1250)

Insert into RentHistory(LeaseNumber, PaymentDate, PaymentAmount)
Values ('8012005','12/1/2005', 1250)

Insert into RentHistory(LeaseNumber, PaymentDate, PaymentAmount)
Values ('8012005','1/1/2006', 1250)

Insert into RentHistory(LeaseNumber, PaymentDate, PaymentAmount)
Values ('8012005','2/1/2006', 1250)

Insert into RentHistory(LeaseNumber, PaymentDate, PaymentAmount)
Values ('8012005','3/1/2006', 1250)

Insert into RentHistory(LeaseNumber, PaymentDate, PaymentAmount)
Values ('8012005','4/1/2006', 1250)


--Insert into MaintenanceTask
--assumes int Identity as key

Insert into MaintenanceTask (TaskName, TaskDescription)
Values('Electrical', null)

Insert into MaintenanceTask (TaskName, TaskDescription)
Values('Plumming', null)

Insert into MaintenanceTask (TaskName, TaskDescription)
Values('Stove', null)

Insert into MaintenanceTask (TaskName, TaskDescription)
Values('Heat', null)

Insert into MaintenanceTask (TaskName, TaskDescription)
Values('WaterDamage', null)

Insert into MaintenanceTask (TaskName, TaskDescription)
Values('Roofleak', null)

Insert into MaintenanceTask (TaskName, TaskDescription)
Values('BrokenWindow', null)

Insert into MaintenanceTask (TaskName, TaskDescription)
Values('Carpet', 'Carpet Damaged or needs replaced')

Insert into MaintenanceTask (TaskName, TaskDescription)
Values('Misc', 'Any task not otherwise catagorized')

--Insert into tenantroomMates

Insert into tenantRoomates (LeaseNumber, RmLastName, RmFirstName, BeginDate, MoveOutDate)
Values('20106102','Banner', 'Jeff', '2/01/2006',null)

Insert into tenantRoomates (LeaseNumber, RmLastName, RmFirstName, BeginDate, MoveOutDate)
Values('30106103','Jackson', 'Sue', '3/01/2006',null)

Insert into tenantRoomates (LeaseNumber, RmLastName, RmFirstName, BeginDate, MoveOutDate)
Values('30106205','Adison', 'Tammy', '3/01/2006',null)

Insert into tenantRoomates (LeaseNumber, RmLastName, RmFirstName, BeginDate, MoveOutDate)
Values('30106205','Adison', 'Amy', '3/01/2006',null)

Insert into tenantRoomates (LeaseNumber, RmLastName, RmFirstName, BeginDate, MoveOutDate)
Values('30106203','Patterson', 'Sam', '3/01/2006',null)

Insert into tenantRoomates (LeaseNumber, RmLastName, RmFirstName, BeginDate, MoveOutDate)
Values('10106202','Kim', 'Minee', '1/01/2006',null)

Insert into tenantRoomates (LeaseNumber, RmLastName, RmFirstName, BeginDate, MoveOutDate)
Values('10106202','Kim', 'Daniel', '1/01/2006',null)

--Insert into MaintenanceRequest and MaintenanceReqDetail

Insert into MaintenanceRequest (leaseNumber, RequestDate)
Values('10106104', '2/15/2006')

Insert into MaintenanceReqDetail (RequestID, MaintenanceTaskID, DateInspected,DateResolved, Notes)
Values(1,2,'2/17/2006','2/19/2006','There is a crack in the back of the toilet, ordered a new fixture, installed on the 19th')

Insert into MaintenanceRequest (leaseNumber, RequestDate)
Values('20106102', '2/16/2006')

Insert into MaintenanceReqDetail (RequestID, MaintenanceTaskID, DateInspected, DateResolved, Notes)
Values(2,1,'2/20/2006','2/21/2006','Light fixture in kitchen broken. fixed Next day')

Insert into MaintenanceReqDetail (RequestID, MaintenanceTaskID, DateInspected, DateResolved, Notes)
Values(2,3,'2/20/2006',null,'Left Burner out')

Insert into MaintenanceRequest (leaseNumber, RequestDate)
Values('30106306', '3/05/2006')

Insert into MaintenanceReqDetail (RequestID, MaintenanceTaskID, DateInspected, DateResolved, Notes)
Values(3,7,'3/05/2006','3/05/2006','Window cracked: replaced pane')

Insert into MaintenanceRequest (leaseNumber, RequestDate)
Values('10106202', '3/15/2006')

Insert into MaintenanceReqDetail (RequestID, MaintenanceTaskID, DateInspected, DateResolved, Notes)
Values(4,1,'3/18/2006',null,'Living room outlet dead. Need to call an electrition')

Insert into MaintenanceRequest (leaseNumber, RequestDate)
Values('50504105', '3/20/2006')

Insert into MaintenanceReqDetail (RequestID, MaintenanceTaskID, DateInspected, DateResolved, Notes)
Values(5,2,'3/22/2006',null,'Motor on Garbage Disposal burnt out. Ordered new part')