-- DATABASE MANAGEMENT SYSTEMS 2019/20
-- LAB EXERCISE 3
-- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~


-- DROP TABLES

-- DROP TABLE Lease
-- DROP TABLE Viewing
-- DROP TABLE Client
-- DROP TABLE Property
-- DROP TABLE [Owner]
-- DROP TABLE BranchManager
-- DROP TABLE Staff
-- DROP TABLE Branch



 -- Temporarily Switch to Master Database
USE [Master]
GO

IF EXISTS (SELECT * FROM sys.sysdatabases WHERE name = 'DreamHome_REPLACE_WITH_YOUR_STUDENT_NUMBER')
BEGIN
	DROP DATABASE DreamHome_REPLACE_WITH_YOUR_STUDENT_NUMBER;
END
GO

CREATE DATABASE DreamHome_REPLACE_WITH_YOUR_STUDENT_NUMBER;
GO


USE DreamHome_REPLACE_WITH_YOUR_STUDENT_NUMBER
GO


-- CREATE DATABASE DreamHome_0001


CREATE TABLE Branch(
	BranchNo		CHAR(5) NOT NULL CONSTRAINT PK_Branch PRIMARY KEY CONSTRAINT CHK_Branch_BranchNo CHECK(BranchNo LIKE 'B[0-9][0-9][0-9][0-9]'),
	StreetAddress	VARCHAR(30) NOT NULL,
	City			VARCHAR(20) NOT NULL,
	Postcode		VARCHAR(8) NOT NULL 
					CONSTRAINT CHK_Branch_Postcode CHECK(Len(Postcode) >= 6),
	PhoneNo			VARCHAR(12) NOT NULL
);


GO

CREATE TABLE Staff(
	StaffNo				CHAR(6) NOT NULL CONSTRAINT PK_Staff PRIMARY KEY,
	FirstName			VARCHAR(30) NOT NULL,
	LastName			VARCHAR(30) NOT NULL,
	Gender				VARCHAR(30) NOT NULL,
	DOB					DATE NOT NULL,
	Position			VARCHAR(20),
	Salary				DECIMAL(12,2),
	StartDate			DATE NOT NULL,
	LeavingDate			DATE,
	BranchNo			CHAR(5) NOT NULL CONSTRAINT FK_Staff_Branch FOREIGN KEY REFERENCES Branch(BranchNo),
	SupervisorStaffNo	CHAR(6) CONSTRAINT FK_Staff_Supervisor FOREIGN KEY REFERENCES Staff(StaffNo),
	Email				VARCHAR(100) NOT NULL,
	Mobile				VARCHAR(12) NOT NULL,
	CONSTRAINT CHK_Staff_LeavingDate CHECK(LeavingDate IS NULL OR LeavingDate > StartDate)
);

GO


CREATE TABLE BranchManager(
	BranchManagerID		INT IDENTITY(1, 1) NOT NULL CONSTRAINT PK_BranchManager PRIMARY KEY,
	BranchNo			CHAR(5) NOT NULL CONSTRAINT FK_BranchManager_Branch FOREIGN KEY REFERENCES Branch(BranchNo),
	StaffNo				CHAR(6) CONSTRAINT FK_BranchManager_Staff FOREIGN KEY REFERENCES Staff(StaffNo),
	StartDate			DATE NOT NULL,
	EndDate				DATE,
	Bonus				DECIMAL(12,2),
	CONSTRAINT CHK_BranchManager_EndDate CHECK(EndDate IS NULL OR EndDate > StartDate)
);

GO


CREATE TABLE Owner(
	OwnerNo				CHAR(7) NOT NULL CONSTRAINT PK_Owner PRIMARY KEY,
	FirstName			VARCHAR(30) NOT NULL,
	LastName			VARCHAR(30) NOT NULL,
	BusinessName		VARCHAR(50),
	BusinessType		VARCHAR(30),
	StreetAddress		VARCHAR(30) NOT NULL,
	City				VARCHAR(20) NOT NULL,
	Postcode			VARCHAR(8) NOT NULL 
						CONSTRAINT CHK_Owner_Postcode CHECK(Len(Postcode) >= 6),
	PhoneNo				VARCHAR(12) NOT NULL,
	Email				VARCHAR(100) NOT NULL,
	[Password]			VARCHAR(16)
);

GO

CREATE TABLE Property(
	PropertyNo			CHAR(7) NOT NULL CONSTRAINT PK_Property PRIMARY KEY,
	OwnerNo				CHAR(7) NOT NULL CONSTRAINT FK_Property_Owner FOREIGN KEY REFERENCES Owner(OwnerNo),
	[Type]				VARCHAR(15) NOT NULL,
	Rooms				TINYINT NOT NULL CONSTRAINT CHK_Property_Rooms CHECK(Rooms BETWEEN 0 AND 25),
	Rent				DECIMAL(12,2) NOT NULL,
	StreetAddress		VARCHAR(30) NOT NULL,
	City				VARCHAR(20) NOT NULL,
	Postcode			VARCHAR(8) NOT NULL 
						CONSTRAINT CHK_Property_Postcode CHECK(Len(Postcode) >= 6),
	BranchNo			CHAR(5) NOT NULL CONSTRAINT FK_Property_Branch FOREIGN KEY REFERENCES Branch(BranchNo),
	ManagedBy			CHAR(6) CONSTRAINT FK_Property_Manager FOREIGN KEY REFERENCES Staff(StaffNo)
);

GO


CREATE TABLE Client(
	ClientNo			CHAR(7) NOT NULL CONSTRAINT PK_Client PRIMARY KEY,
	FirstName			VARCHAR(30) NOT NULL,
	LastName			VARCHAR(30) NOT NULL,
	PhoneNo				VARCHAR(12) NOT NULL,
	Email				VARCHAR(100) NOT NULL,
	PropertyTypePref	VARCHAR(15),
	MaxRent				DECIMAL(12,2),
	BranchNo			CHAR(5) NOT NULL CONSTRAINT FK_Client_Branch FOREIGN KEY REFERENCES Branch(BranchNo),
	RegistrationDate	DATE NOT NULL,
	RegisteredBy		CHAR(6) CONSTRAINT FK_Client_RegisteredBy FOREIGN KEY REFERENCES Staff(StaffNo)
);

GO


CREATE TABLE Viewing(
	PropertyNo			CHAR(7) NOT NULL CONSTRAINT FK_Viewing_Property FOREIGN KEY REFERENCES Property(PropertyNo),
	ClientNo			CHAR(7) NOT NULL CONSTRAINT FK_Viewing_Client FOREIGN KEY REFERENCES Client(ClientNo),
	ViewingDateTime		DATETIME2 NOT NULL,
	StaffNo				CHAR(6) CONSTRAINT FK_Viewing_Staff FOREIGN KEY REFERENCES Staff(StaffNo),
	WishToRent			BIT,
	Comment				VARCHAR(100),
	CONSTRAINT PK_Viewing PRIMARY KEY (PropertyNo, ClientNo, ViewingDateTime)
);


CREATE TABLE Lease(
	LeaseNo				CHAR(6) NOT NULL CONSTRAINT PK_Lease PRIMARY KEY,
	ClientNo			CHAR(7) NOT NULL CONSTRAINT FK_Lease_Client FOREIGN KEY REFERENCES Client(ClientNo),
	PropertyNo			CHAR(7) NOT NULL CONSTRAINT FK_Lease_Property FOREIGN KEY REFERENCES Property(PropertyNo),
	MonthlyRent			DECIMAL(12,2) NOT NULL,
	RentStart			DATE NOT NULL,
	RentFinish			DATE NOT NULL,
	Duration			AS DATEDIFF(mm, RentStart, RentFinish),
	PaymentMethod		VARCHAR(30) NOT NULL,
	DepositPaid			DECIMAL(12,2) CONSTRAINT CHK_Lease_DepositPaid CHECK (DepositPaid IS NULL OR DepositPaid >= 0),
	CONSTRAINT CHK_Lease_RentFinish CHECK (RentFinish > RentStart)
);

GO