-- DATABASE SYSTEMS 2019/20
-- LAB EXERCISE 4
-- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

-- INSERT QUERIES
-- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

-- Using the branch number B0010, add a new branch office at an address of your choice



-- Add yourself as a new member of staff with staff number S29030 at the branch created in the last step.



-- Using the Owner number, CO19055 (Charlie, Oscar, 19055), create a new property owner.
-- Make yourself the new owner's account manager



-- Using the customer number CR19044, add a new customer with the name John O'Neill, registered with the branch created earlier.



-- TABLE ALTERATIONS
-- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
-- Check the constraint on the Property rooms column – make sure it allows 0 rooms (for a studio property).
-- If not delete the constraint and recreate it to allow 0 rooms.
-- Syntax:
--		ALTER TABLE tableName DROP CONSTRAINT constraint_name
--		ALTER TABLE tableName ADD CONSTRAINT constraint_name CHECK (…)



-- Dream Home will like to add a new field to the Booking table to record any amount returned to the customer if they 
-- cancel their booking within the allowed cancellation period.
-- Add a new field called CancellationRefund in the Booking table for this new requirement.
-- Add a constraint to ensure that the cancellation refund is not more than the Rental Price



-- Make an alteration to the Staff table with the following CHECK constraint (if you do not have it already)
-- Check that the Leaving Date for a Staff Member is either NULL OR it is AFTER the start date


-- Dream Home recently needed to contact a member of staff's next of kin urgently, but realised they do not currently store next of kin details.
-- You have been asked to modify the staff table to enable them to capture Next of kin details (a name, relationship and phone number) going forwards



-- Add a new member of staff with staff number S29031 at branch B0010 and include sample next of kin details