ITEC200 Database and Information Management

Instructions

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).

11. Record a presentation:

o Use Zoom to record a short video presentation (3-4 minutes). In your presentation turn on your

webcam (to include your face in the presentation) and share your screen (to show your SQL code

in Azure Data Studio. Run all queries you’ve written for questions 7 ,8 and 9 in order. Also, explain

the commands/functions used for question 8 and 9 and how the query works

Refer & earn

Just refer 5 friends to earn more than $2000.