BestHome specializes in property management company, taking an intermediate role between owners who wish to rent out their furnished property and clients of BestHome who require to rent furnished property for a fixed period. BestHome currently has about 2000 staff working in 100 branches.
BestHome has branch offices in cities throughout the United Kingdom. Each branch office is allocated members of staff, including a Manager, who manages the operations of the office. The data describing a branch office includes a unique branch number, address (street, city, and postcode), telephone numbers (up to a maximum of three), and the name of the member of staff who currently manges the office. Additional data is held on each Manager, which includes the date that the Manager assumed his or her position at the current branch office, and a monthly bonus payment based upon his or her performance in the property for rent market.
Members of staff with the role of Supervisor are responsible for the day-to-day activities of an allocated group of staff called Assistants (up to a maximum of10,atany one time). Not all members of staff are assigned to a Supervisor. The data stored regarding each member of staff includes staffnumber,name,address,position,salary,name of Supervisor (where applicable), and the details of the branch office at which a member of staff is currently working. The staff number is unique across all branchesof BestHome.
Properties for rent
Each branch office offers a range of properties for rent. The data stored for each property includes property number, address (street, city, postcode), type, number of
rooms, monthly rent, and the details of the property owner. The property number is unique across all branch offices. The management of a property is assigned to a member of staff whenever it is rented out or requires to be rented out. A member of staff may manage a maximum of 100 properties for rent at any one time.
The details of property owners are also stored. There are two main types of property owner: private owners and business owners. The data stored for private owners includes owner number, name, address, telephone number, email, and password. The data stored on business owners includes name of business, type of business, address, telephone number, email, password, and contact name. The password will allow owners access to parts of the BestHome database using the Web.
BestHome refers to members of the public interested in renting property as clients. To become a client, a person must first register at a branch office of BestHome. The data stored on clients includes client number, name, telephone number, email, 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 processed the registration, the date the client joined, and some details on the branch office at which the client registered. The client number is unique across all BestHome branches.
When a property is rented out, a lease is drawn up between the client and the property. The data listed in detail on the lease includes lease number, client number, name and address, property number and address, monthly rent, method of payment, an indication of whether the deposit has been paid deposit (calculated as twice the monthly rent), duration of lease, and the start and end dates of the lease period.
1. Draw an Entity Relationship Diagram for the BestHome database based on the descriptions provided above.
2. The relational schema provided above is incomplete. Add the missing entities, attributes and relationships based onthe description of data requirements.
3. Using Azure Data Studio Create a blank MS SQL database. Use your surname as the database name.
4. Write SQL queries to create the BestHome rental database tables and relationships
5. Write SQL queries to insert the data shown in the above figures.
6. Write a query to insert your personal details to the staff database (as a new staff).
7. Write SQL queries to provide following information:
a) Identify the total number of staff and the sum of their salaries.
b) Identify the total number of staff in each position at branches in Glasgow.
c) List the name of each Manager at each branch, ordered by branch address.
d) List the property number, address, type, and rent of all properties in Glasgow, ordered by rental amount.
e) List the details of properties provided by business owners at a given branch.
f) Identify the total number of properties of each type at all branches.
g) Identify the details of private property owners that provide more than one
h) property for rent.
i) Identify flats with at least three rooms and with a monthly rent no higher than £400 in Aberdeen.
j) Identify the properties that have been advertised more than the average number of times.
k) List the details of leases due to expire next month at a given branch.
8. Create Stored Functions to:
l) Identify the total number of properties assigned to each member of staff at a given branch (input: branch code)
m) List the number, name, and telephone number of clients and their property preferences at a given branch (input: branch code)
9. Create a stored procedure that increases the salary of employees by 10%. Use transaction management features of SQL (COMMIT, ROLLBACK).
10. Produce a report for the Director of the company outlining the ethical issues that need to be considered and make any recommendations that you think are
appropriate. Also, list the potential security threats that could occur and propose countermeasures to overcome them ( 400-500 words).