-- DATABASE MANAGEMENT SYSTEMS 2020
-- LAB EXERCISE week 4
-- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~


/*
	Run the CREATE DATABASE COMMANDs first to create your database (use your own STUDENT NUMBER)
	Once created, comment out the lines shown below above ~~~~~~~~~~~~~~~~~~~~~~~~~~~~

	Switch the SQL Query Window to your newly created database from the drop-down item in the menu currently showing master

	NOTE
	If you make a mistake and need to delete the database and start again using these lines of code, uncomment and run the commands

	PLEASE NOTE THE TABLES IN THE ORIGINAL FILE ARE NOT IN THE CORRECT ORDER - YOU WILL NEED TO RE-ARRANGE THEM (A Parent is born before a child)
 */

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

-- If your database already exists delete it so that you can recreate it
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


-- COMMAND To CREATE DATABASE
-- Database Name:	DreamHome_REPLACE_WITH_YOUR_STUDENT_NUMBER;


GO


-- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~



-- Switch to your database
USE DreamHome_REPLACE_WITH_YOUR_STUDENT_NUMBER
GO
	



/* Creates the Branch Table in the case study */
CREATE TABLE Branch(
	BranchNo		,
	StreetAddress		,
	City			,
	Postcode		,
	PhoneNo			
);



/* Creates the Property Table */
CREATE TABLE Property(
	PropertyNo			,
	OwnerNo				,
	[Type]				,
	Rooms				,
	Rent				,
	StreetAddress			,
	City				,
	Postcode			,
	BranchNo			,
	ManagedBy			
);



/* Creates the Branch Manager Table */
CREATE TABLE BranchManager(
	BranchManagerID			,
	BranchNo			,
	StaffNo				,
	StartDate			,
	EndDate				,
	Bonus				
	
);



/* Creates the Owner Table */
CREATE TABLE Owner(
	OwnerNo				,
	FirstName			,
	LastName			,
	BusinessName			,
	BusinessType			,
	StreetAddress			,
	City				,
	Postcode			,
	PhoneNo				,
	Email				,
	[Password]			
);



/* Creates the Staff Table */
CREATE TABLE Staff(
	StaffNo				,
	FirstName			,
	LastName			,
	Gender				,
	DOB				,
	Position			,
	Salary				,
	StartDate			,
	LeavingDate			,
	BranchNo			,
	SupervisorStaffNo		,
	Email				,
	Mobile				
	
);



/* Creates the Viewing Table */
CREATE TABLE Viewing(
	PropertyNo			,
	ClientNo			,
	ViewingDateTime			,
	StaffNo				,
	WishToRent			,
	Comment				

);



/* Creates the Lease Table */
CREATE TABLE Lease(
	LeaseNo				,
	ClientNo			,
	PropertyNo			,
	MonthlyRent			,
	RentStart			,
	RentFinish			,
	Duration			,
	PaymentMethod			,
	DepositPaid			

);


/* Creates the Lease Table */
CREATE TABLE Client(
	ClientNo			,
	FirstName			,
	LastName			,
	PhoneNo				,
	Email				,
	PropertyTypePref		,
	MaxRent				,
	BranchNo			,
	RegistrationDate		,
	RegisteredBy		
);