Assignment on Database Modeling - Writing an essay with 800 words

Question1:

Part A (ER Diagram):

Part B (ERD Mapping):

DDL:

create table Client(

ClientCode int,

ClientName varchar(50),

DateOfBirth Date,

Gender varchar(20),

Constraint ClientPK primary key(ClientCode)

)

create table ClientAddress(

ClientCode int,

[Address] varchar(50),

Constraint ClientCodeFK foreign key (ClientCode) references Client(ClientCode),

constraint ClientAddressPK primary key(ClientCode,[Address])

)

create table ClientEmail(

ClientCode int,

Email varchar(50),

Constraint ClientCodeEmailFK foreign key (ClientCode) references Client(ClientCode),

constraint ClientEmailPK primary key(ClientCode,Email)

)

create table ClientContactNumber(

ClientCode int,

ContactNUmber varchar(50),

Constraint ClientContactNumberFK foreign key (ClientCode) references Client(ClientCode),

constraint ClientContactNumberPK primary key(ClientCode,ContactNumber)

)

create table Property(

PropertyID int,

Name varchar(30),

[Address] varchar(30),

Location varchar(30),

PropertyDescription varchar(100),

AccomodationDetails varchar(100),

Price int,

[Type] varchar(30),

NumberOfRooms int,

OwnerId int,

Constraint OwnerFK foreign key (OwnerID) references Client(ClientCode),

constraint PropertyPK primary key (PropertyID)

)

create table PropertyRental(

PropertyID int,

TenantID int,

LandlordID int,

RentPerMonth int,

AdvancePaid varchar(20),

commission int,

StartDate date,

EndDate date

constraint PropertyRentalFK foreign key (PropertyID) references Property(PropertyID),

constraint PropertyRentalTenantFK foreign key (TenantID) references Client(ClientCode),

constraint ProperyRentalLandlordFK foreign key (TenantID) references Client(ClientCode),

constraint PropertyRentalPK primary key (PropertyID,TenantID,LandlordID)

)

create table PropertySale(

PropertyID int,

BuyerID int,

SellerID int,

SellingPrice int,

StampDuty varchar(20),

Commission int,

constraint PropertySaleFK foreign key (PropertyID) references Property(PropertyID),

constraint PropertySaleBuyerFK foreign key (BuyerID) references Client(ClientCode),

constraint ProperySaleSellerFK foreign key (SellerID) references Client(ClientCode),

constraint PropertySalePK primary key (PropertyID,BuyerID,SellerID)

)

create table Branch(

BranchCode int,

[Address] varchar(100),

ContactNumber varchar(20),

Email varchar(100),

Manager varchar(50),

Constraint BranchPK primary key (BranchCode)

)

create table EstateAgent(

AgentCode int,

Name varchar(40),

HeadQuarter varchar(40),

RegisteredAddress varchar(30),

ContactNumber varchar(20),

Website varchar(40),

Email varchar(60),

BranchCode int,

constraint EstateAgentPK Primary key (AgentCode),

constraint EstateAgentBranchFK foreign key (BranchCode) references Branch(BranchCode)

)

create table Viewing(

PropertyID int,

ClientID int,

AgentID int,

[DateTime] DateTime,

Comments varchar(100),

constraint PropertyViewingFK foreign key (PropertyID) references Property(PropertyID),

constraint PropertyViewingClientFK foreign key (ClientID) references Client(ClientCode),

constraint ProperyViewingAgentFK foreign key (AgentID) references EstateAgent(AgentCode),

constraint PropertyViewingPK primary key (PropertyID,ClientID,AgentID)

)

create table AgentStaff(

StaffID int,

Name varchar(30),

[Address] varchar(100),

ContactDetails varchar(100),

AgentCode int,

constraint AgentStaffPK primary key (StaffID),

constraint AgentStaffFK foreign key (AgentCode) references EstateAgent(AgentCode)

)

DML(Insertion):

INSERT [dbo].[Client] ([ClientCode], [ClientName], [DateOfBirth], [Gender]) VALUES (1, N'Micheal', CAST(N'1995-03-03' AS Date), N'Male')

INSERT [dbo].[Client] ([ClientCode], [ClientName], [DateOfBirth], [Gender]) VALUES (2, N'Julia', CAST(N'1994-03-14' AS Date), N'Female')

INSERT [dbo].[Client] ([ClientCode], [ClientName], [DateOfBirth], [Gender]) VALUES (3, N'Enrique', CAST(N'1980-05-15' AS Date), N'Male')

INSERT [dbo].[Client] ([ClientCode], [ClientName], [DateOfBirth], [Gender]) VALUES (4, N'Atif', CAST(N'1990-10-10' AS Date), N'Male')

INSERT [dbo].[Client] ([ClientCode], [ClientName], [DateOfBirth], [Gender]) VALUES (5, N'Arjit', CAST(N'1995-04-20' AS Date), N'Male')

INSERT [dbo].[ClientAddress] ([ClientCode], [Address]) VALUES (1, N'California')

INSERT [dbo].[ClientAddress] ([ClientCode], [Address]) VALUES (2, N'Las Angeles')

INSERT [dbo].[ClientAddress] ([ClientCode], [Address]) VALUES (3, N'San Francisco')

INSERT [dbo].[ClientAddress] ([ClientCode], [Address]) VALUES (4, N'Mexico')

INSERT [dbo].[ClientAddress] ([ClientCode], [Address]) VALUES (5, N'New York')

INSERT [dbo].[ClientEmail] ([ClientCode], [Email]) VALUES (1, N'[email protected]')

INSERT [dbo].[ClientEmail] ([ClientCode], [Email]) VALUES (2, N'[email protected]')

INSERT [dbo].[ClientEmail] ([ClientCode], [Email]) VALUES (3, N'[email protected]')

INSERT [dbo].[ClientEmail] ([ClientCode], [Email]) VALUES (4, N'[email protected]')

INSERT [dbo].[ClientEmail] ([ClientCode], [Email]) VALUES (5, N'[email protected]')

INSERT [dbo].[ClientContactNumber] ([ClientCode], [ContactNUmber]) VALUES (1, N'+92301-9876543')

INSERT [dbo].[ClientContactNumber] ([ClientCode], [ContactNUmber]) VALUES (2, N'+92305-1234678')

INSERT [dbo].[ClientContactNumber] ([ClientCode], [ContactNUmber]) VALUES (3, N'+92333-4531268')

INSERT [dbo].[ClientContactNumber] ([ClientCode], [ContactNUmber]) VALUES (4, N'+92345-9871234')

INSERT [dbo].[ClientContactNumber] ([ClientCode], [ContactNUmber]) VALUES (5, N'+92398-4040125')

INSERT [dbo].[Property] ([PropertyID], [Name], [Address], [Location], [PropertyDescription], [AccomodationDetails], [Price], [Type], [NumberOfRooms], [OwnerId]) VALUES (1, N'Magical Gardens', N'Jesmond area', N'Newcastle', N'Sell', N'New', 158000, N'Semi Detached', 5, 1)

INSERT [dbo].[Property] ([PropertyID], [Name], [Address], [Location], [PropertyDescription], [AccomodationDetails], [Price], [Type], [NumberOfRooms], [OwnerId]) VALUES (2, N'House of Cards', N'Street05,Galmento, NewCastle', N'Denmark', N'Rental', N'Old', 333000, N'Detached', 10, 2)

INSERT [dbo].[Property] ([PropertyID], [Name], [Address], [Location], [PropertyDescription], [AccomodationDetails], [Price], [Type], [NumberOfRooms], [OwnerId]) VALUES (3, N'Templar Villa', N'Street10,GardentTown, NewYrok', N'Lahore', N'Sell', N'Old', 253000, N'Semi Detached', 4, 3)

INSERT [dbo].[Property] ([PropertyID], [Name], [Address], [Location], [PropertyDescription], [AccomodationDetails], [Price], [Type], [NumberOfRooms], [OwnerId]) VALUES (4, N'Dancer''s Stage', N'Street05, ModelTown, Lahore', N'Karachi', N'Rental', N'New', 1113000, N'Semi Detached', 5, 4)

INSERT [dbo].[Property] ([PropertyID], [Name], [Address], [Location], [PropertyDescription], [AccomodationDetails], [Price], [Type], [NumberOfRooms], [OwnerId]) VALUES (5, N'Templar''s Mansion', N'Street10,JoharTwon', N'Lahore', N'Rental', N'New', 123456, N'Semi Detached', 5, 5)

INSERT [dbo].[PropertyRental] ([PropertyID], [TenantID], [LandlordID], [RentPerMonth], [AdvancePaid], [commission], [StartDate], [EndDate]) VALUES (1, 1, 1, 20000, N'Yes', 2000, CAST(N'2017-02-02' AS Date), CAST(N'2017-05-01' AS Date))

INSERT [dbo].[PropertyRental] ([PropertyID], [TenantID], [LandlordID], [RentPerMonth], [AdvancePaid], [commission], [StartDate], [EndDate]) VALUES (1, 5, 5, 2000, N'Yes', 2500, CAST(N'2018-01-01' AS Date), CAST(N'2019-01-01' AS Date))

INSERT [dbo].[PropertyRental] ([PropertyID], [TenantID], [LandlordID], [RentPerMonth], [AdvancePaid], [commission], [StartDate], [EndDate]) VALUES (2, 2, 2, 10000, N'No', 1000, CAST(N'2017-04-01' AS Date), CAST(N'2018-02-01' AS Date))

INSERT [dbo].[PropertyRental] ([PropertyID], [TenantID], [LandlordID], [RentPerMonth], [AdvancePaid], [commission], [StartDate], [EndDate]) VALUES (3, 3, 3, 30000, N'No', 3000, CAST(N'2016-01-01' AS Date), CAST(N'2017-01-01' AS Date))

INSERT [dbo].[PropertyRental] ([PropertyID], [TenantID], [LandlordID], [RentPerMonth], [AdvancePaid], [commission], [StartDate], [EndDate]) VALUES (4, 4, 4, 25000, N'Yes', 2500, CAST(N'2017-01-01' AS Date), CAST(N'2018-01-01' AS Date))

INSERT [dbo].[PropertySale] ([PropertyID], [BuyerID], [SellerID], [SellingPrice], [StampDuty], [Commission]) VALUES (1, 1, 2, 158000, N'Yes', 10000)

INSERT [dbo].[PropertySale] ([PropertyID], [BuyerID], [SellerID], [SellingPrice], [StampDuty], [Commission]) VALUES (2, 2, 3, 123000, N'No', 10000)

INSERT [dbo].[PropertySale] ([PropertyID], [BuyerID], [SellerID], [SellingPrice], [StampDuty], [Commission]) VALUES (3, 3, 4, 111111, N'No', 10000)

INSERT [dbo].[PropertySale] ([PropertyID], [BuyerID], [SellerID], [SellingPrice], [StampDuty], [Commission]) VALUES (4, 4, 5, 80000, N'No', 5000)

INSERT [dbo].[PropertySale] ([PropertyID], [BuyerID], [SellerID], [SellingPrice], [StampDuty], [Commission]) VALUES (5, 5, 1, 500000, N'Yes', 50000)

INSERT [dbo].[Branch] ([BranchCode], [Address], [ContactNumber], [Email], [Manager]) VALUES (1, N'Los Angeles', N'+92321-8527411', N'[email protected]', N'James')

INSERT [dbo].[Branch] ([BranchCode], [Address], [ContactNumber], [Email], [Manager]) VALUES (2, N'New York', N'+92301-4544123', N'[email protected]', N'Hitman')

INSERT [dbo].[Branch] ([BranchCode], [Address], [ContactNumber], [Email], [Manager]) VALUES (3, N'Sanfrancisc', N'+92444-4524524', N'[email protected]', N'Hitler')

INSERT [dbo].[Branch] ([BranchCode], [Address], [ContactNumber], [Email], [Manager]) VALUES (4, N'Mexico', N'+92363-6655454', N'[email protected]', N'Adolf')

INSERT [dbo].[Branch] ([BranchCode], [Address], [ContactNumber], [Email], [Manager]) VALUES (5, N'California', N'+92333-4141414', N'[email protected]', N'Saladin')

INSERT [dbo].[EstateAgent] ([AgentCode], [Name], [HeadQuarter], [RegisteredAddress], [ContactNumber], [Website], [Email], [BranchCode]) VALUES (1, N'Bill', N'Lahore', N'Punjab', N'+92302-7197278', N'www.Bill.com', N'[email protected]', 1)

INSERT [dbo].[EstateAgent] ([AgentCode], [Name], [HeadQuarter], [RegisteredAddress], [ContactNumber], [Website], [Email], [BranchCode]) VALUES (2, N'Gates', N'California', N'United States', N'+92555-4545658', N'www.Gates.com', N'[email protected]', 2)

INSERT [dbo].[EstateAgent] ([AgentCode], [Name], [HeadQuarter], [RegisteredAddress], [ContactNumber], [Website], [Email], [BranchCode]) VALUES (3, N'Hammer', N'London', N'United Kingdom', N'+92354-4567896', N'www.Hammer.com', N'[email protected]', 3)

INSERT [dbo].[EstateAgent] ([AgentCode], [Name], [HeadQuarter], [RegisteredAddress], [ContactNumber], [Website], [Email], [BranchCode]) VALUES (4, N'Adeel ', N'Denmark', N'India', N'+92123-8585965', N'www.Adeel.com', N'[email protected]', 4)

INSERT [dbo].[EstateAgent] ([AgentCode], [Name], [HeadQuarter], [RegisteredAddress], [ContactNumber], [Website], [Email], [BranchCode]) VALUES (5, N'Jackie', N'San Francisco', N'Finland', N'+92333-4563666', N'www.Jackie.com', N'[email protected]', 5)

INSERT [dbo].[Viewing] ([PropertyID], [ClientID], [AgentID], [DateTime], [Comments]) VALUES (1, 1, 1, NULL, N'Liked')

INSERT [dbo].[Viewing] ([PropertyID], [ClientID], [AgentID], [DateTime], [Comments]) VALUES (2, 2, 2, NULL, N'Liked')

INSERT [dbo].[Viewing] ([PropertyID], [ClientID], [AgentID], [DateTime], [Comments]) VALUES (3, 3, 3, NULL, N'Disliked')

INSERT [dbo].[Viewing] ([PropertyID], [ClientID], [AgentID], [DateTime], [Comments]) VALUES (4, 4, 4, NULL, N'Liked')

INSERT [dbo].[Viewing] ([PropertyID], [ClientID], [AgentID], [DateTime], [Comments]) VALUES (5, 5, 5, NULL, N'Liked')

INSERT [dbo].[AgentStaff] ([StaffID], [Name], [Address], [ContactDetails], [AgentCode]) VALUES (1, N'Napolean', N'Russia', N'+92333-7485965', 1)

INSERT [dbo].[AgentStaff] ([StaffID], [Name], [Address], [ContactDetails], [AgentCode]) VALUES (2, N'Aristocra', N'India', N'+92345-4585858', 2)

INSERT [dbo].[AgentStaff] ([StaffID], [Name], [Address], [ContactDetails], [AgentCode]) VALUES (3, N'Aristotle', N'Germany', N'+92312-1221222', 3)

INSERT [dbo].[AgentStaff] ([StaffID], [Name], [Address], [ContactDetails], [AgentCode]) VALUES (4, N'Aristotle', N'Turkey', N'+92345-1444445', 4)

INSERT [dbo].[AgentStaff] ([StaffID], [Name], [Address], [ContactDetails], [AgentCode]) VALUES (5, N'Newton', N'Iceland', N'+92378-1666654', 5)

DML(Retreival):

select *

from Property

where [Address] = 'Jesmond area' and Location= 'Newcastle'

select *

from property

where (Location ='Newcastle' or Location = 'SunderLand' or Location = 'Gateshead'

or Location='Durham') and PropertyID in (select PropertyID

from PropertySale

where sellingPrice > 157000 and SellingPrice < 279000)