Consider any relevant feedback from your Week One Individual Assignment titled, "DreamHome Case Study" that will support the completion of this week's assignment.Create a data model for an object-orie
Conceptual database design
Owner
PK Owner_no
firstname
lastname
staff_manager
business_type
branch_registration
contact_name
address
phone
password
Staff
PK Staff_no
First_name
Last_name
Dob
Street
City
Postcode
Position
Salary
Supervisor_name
Supervisor_no
Branch_no
Advertisements
Advert_no
Date_advertised
Cost
FK property_no
FK newspaper_no
NewsPapers
Newspaper_no
Name
Phone
Address
Contact_name
Property_viewing
PK View_no
View_date
Comments
FK Client_no
FK Property_no
RentalProperties
PK Property_no
Street
City
Postcode
Phone1
Phone2
Phone3
Type
No_of_rooms
Rent
FK owner_no
FK staff_no
Leases
PK Lease_no
Payment_method
Advance_deposit
Lease_duration
Start_date
Ebd_date
FK Client_no
FK Property_no
Clients
PK Client_no
First_name
Last_name
Phone
Accommodation_pref
Max_rent
Join_date
FK Branch_no
FK Staff_no
Branch
PK Branch_no
Street
City
Postcode
Phone1
Phone2
Phone3
Manager
PK Manager_no
Manager_startdate
Bonus
FK branch_no
Physical database design
TABLE Branch(
branchNo VARCHAR(5) NOT NULL PRIMARY KEY,
street VARCHAR(75) NOT NULL,
city VARCHAR(30) NOT NULL,
postcode VARCHAR(10) NOT NULL,
phone1 VARCHAR(30),
phone2 VARCHAR(30),
phone3 VARCHAR(30),
);
TABLE Staff(
staffNo VARCHAR(5) NOT NULL PRIMARY KEY,
first_name VARCHAR (20) NOT NULL,
last_name VARCHAR(20) NOT NULL,
position VARCHAR(25) NOT NULL,
DOB DATE,
Salary NUMERIC(9,2) NOT NULL,
branchNo VARCHAR(5) NOT NULL,
FOREIGN KEY (branchNo)
FOREIGN KEY (supervisorNo)
REFRENCES Branch(BranchNo)
);
TABLE Manager(
managerNo VARCHAR(5) NOT NULL PRIMARY KEY,
manager_startdate DATE,
bonus NUMERIC(9,2) NOT NULL,
branchNo VARCHAR(5) NOT NULL,
FOREIGN KEY (branchNo)
);
TABLE Owner(
ownerNo VARCHAR(10) NOT NULL PRIMARY KEY,
first_name VARCHAR (20) NOT NULL,
last_name VARCHAR (20) NOT NULL,
staff_manager VARCHAR (50) NOT NULL,
business_type VARCHAR(25),
branch_regostration VARCHAR (25),
contact_name VARCHAR(50),
address VARCHAR(30),
phone1 VARCHAR (20),
phone2 VARCHAR (20),
phone3 VARCHAR (20),
FOREIGN KEY (propertyNo)
FOREIGN KEY (clientNo)
);
TABLE RentalProperty(
propertyNo VARCHAR(8) NOT NULL PRIMARY KEY,
street VARCHAR (25) NOT NULL,
city VARCHAR (25) NOT NULL,
postcode VARCHAR (8) NOT NULL,
propertyType VARCHAR (20) NOT NULL,
rooms SMALLINT NOT NULL,
rent NUMERIC(5,1) NOT NULL,
ownerNo VARCHAR(10) NOT NULL,
staffNo VARCHAR(5) ,
branchNo VARCHAR(5),
FOREIGN KEY (ownerNo)
REFERENCES PrivateOwner(ownerNo),
FOREIGN KEY(staffNo)
REFERENCES Staff(staffNo),
FOREIGN KEY(branchNo)
REFERENCES Branch(branchNo)
);
TABLE Clients(
clientNo VARCHAR(10) NOT NULL PRIMARY KEY,
first_name VARCHAR(20) NOT NULL,
last_name VARCHAR (20) NOT NULL,
Phone VARCHAR(10),
Email VARCHAR(50),
Accommodation_pref VARCHAR(50),
Max_rent NUMERIC(9,2),
Join_date DATE,
FOREIGN KEY (BranchNo)
REFERENCES Branch(BranchNo),
FOREIGN KEY (StaffNo)
REFERENCES Staff(StaffNo)
);
TABLE Leases(
leaseNo VARCHAR(10) NOT NULL PRIMARY KEY,
payment_method VARCHAR (20),
advance_deposit NUMERIC(9,2),
lease_duration INTEGER (10),
start_date DATE NOT NULL,
end_date DATE NOT NULL,
clientNo VARCHAR(5),
propertyNo VARCHAR (5),
FOREIGN KEY (clientNo)
FOREIGN KEY (propertyNo)
);
TABLE PropertyViewing(
viewNo VARCHAR(10) NOT NULL PRIMARY KEY,
viewDate DATE,
comments VARCHAR (255),
clientNo VARCHAR(10),
propertyNo VARCHAR(10),
FOREIGN KEY (clientNo)
FOREIGN KEY (propertyNo)
);
TABLE NewsPaper(
newspaperNo VARCHAR(10) NOT NULL,
name VARCHAR(100),
phone VARCHAR(20),
street VARCHAR(50),
city VARCHAR(50),
postcode VARCHAR(10),
contact_name VARCHAR(50)
);
TABLE Advertisements(
advertNo VARCHAR(10) NOT NULL PRIMARY KEY,
startdate_advertise DATE NOT NULL,
enddate_advertise DATE NOT NULL,
cost NUMERIC(9,2) NOT NULL,
propertyNo VARCHAR(10),
newspaperNo VARCHAR(10),
FOREIGN KEY (propertyNo)
FOREIGN KEY (newspaperNo)
);
Index
PK – Primary Key
FK – Foreign Key
