Define the maintenance and security components necessary for the data models created in the following Individual assignments:-Week One DreamHome Case Study-Week Two Object Oriented Data Model and SQL
Appendix A, "User Requirements Specification for DreamHome Case Study" of Database Systems: A Practical Approach to Design, Implementation and Management:
Data Requirements
Staff
The data required on members of staff includes staff number, name (first and last name), position, gender, date of birth (DOB), and name of the Supervisor (where appropriate). Members of staff in the position of Supervisor supervise an allocated group of staff (up to a maximum of 10 at any one time).
Properties for rent
The data stored on property for rent includes property number, address (street, city, and postcode), type, number of rooms, monthly rent, and the details of the property owner. The monthly rent for a property is reviewed annually. Most of the properties rented out by DreamHome are apartments (or flats). The management of a property is assigned to a member of staff whenever it is rented out or ready to be rented out. A member of staff may manage a maximum of 100 properties for rent at any one time.
Property owners
There are two main types of property owner: private owners and business owners. The data stored on private owners includes owner number, name (first and last name), address, telephone number, email, and password. The data stored on business owners includes owner number, name of business, business type, address, telephone number, email, password, and contact name.
Clients
When a prospective client registers with DreamHome, the data stored includes the client number, name (first and last name), telephone number, email, and some data on the desired property, including the preferred type of accommodation and the maximum rent that the client is prepared to pay. Also stored is the name of the member of staff who registered the new client.
Property viewings
Clients may request to view property. The data stored includes client number, name and telephone number, property number and address, date the client viewed the property, and any comments made by the client regarding the suitability of the property. A client may view the same property only once on a given date.
Leases
Once a client finds a suitable property, a lease is drawn up. The information on the lease includes lease number, client number and name, property number, address, type and number of rooms, monthly rent, method of payment, deposit (calculated as twice the monthly rent), whether the deposit is paid, the start and end dates of the rental period, and the duration of the lease. The lease number is unique across all DreamHome branches. A client may hold a lease associated with a given property from a minimum of three months to a maximum of 1 year.
A.2.2 Transaction Requirements (Sample)
Data entry
Enter the details for a new property and the owner (such as details of property number PG4 in Glasgow owned by Tina Murphy).
Enter the details of a new client (such as details of Mike Ritchie).
Enter the details of a client viewing a property (such as client Mike Ritchie viewing property number PG4 in Glasgow on the 06-May-12).
Enter the details of a lease between a client and property (such as client Mike Ritchie renting out property number PG4 from the 10-May-12 to 9-May-13).
Data update/deletion
Update/delete the details of a property.
Update/delete the details of a property owner.
Update/delete the details of a client.
Update/delete the details of a property viewing by a client.
Update/delete the details of a lease.
Data queries
Examples of queries required by the Staff user views:
(a) List details of staff supervised by a named Supervisor at the branch.
(b) List details of all Assistants alphabetically by name at the branch.
(c) List the details of property (including the rental deposit) available for rent at the branch, along with the owner’s details.
(d) List the details of properties managed by a named member of staff at the branch.
(e) List the clients registering at the branch and the names of the members of staff who registered the clients.
(f) Identify properties located in Glasgow with rents no higher than £450.
(g) Identify the name and telephone number of an owner of a given property.
(h) List the details of comments made by clients viewing a given property.
(i) Display the names and phone numbers of clients who have viewed a given property but not supplied comments.
(j) Display the details of a lease between a named client and a given property.
(k) Identify the leases due to expire next month at the branch.
(l) List the details of properties that have not been rented out for more than three months.
(m) Produce a list of clients whose preferences match a particular property.
The distributed database created in the Week One Individual Assignment, "DreamHome Case Study"
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
Gender
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
country
Manager
PK Manager_no
Manager_startdate
Bonus
FK brach_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),
country VARCHAR(50),
);
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,
gender VARCHAR (8) ,
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
