Securing Higher Grades Costing Your Pocket? Book Your Assignment at The Lowest Price Now!
loader
Add File

Error goes here

Files Missing!

Please upload all relevant files for quick & complete assistance.

Guaranteed Higher Grade!

Stuck on Your Question?

Get 24x7 live help from our Top Tutors. All subjects covered.

loader
250 words

Error goes here

Files Missing!

Please upload all relevant files for quick & complete assistance.

Students Who Viewed This Also Studied

18 Pages
IMAT5167 Data Warehouse Design and OLAP

Task: Learning Outcomes A comprehensive understanding of the concepts, purposes, architectures, evolution and benefits of DW. A systematic knowledge of how to apply ERD and Star Schema to desig ...

Course

IMAT5167

Type

Programming

Subject

Database

University

De Montfort University

Season

Fall

6 Pages
COMP9771 Advanced Database Applications

In this assignment you are asked to investigate and produce  paper on any areaof your choice as long as it adheres to the following rules:•It must discuss a future database technology or a c ...

Course

COMP9771

Type

Thesis

Subject

Database

University

Australian Catholic University

28 Pages
CO2701 Database Systems

Question: If you have not yet made the university aware of any disability, specific learning difficulty, long-term health or mental health condition, please complete a Disclosure Form.  The ...

Course

CO2701

Type

Programming

Subject

Database

University

University of Central Lancashire

16 Pages
IT242 Software Engineering

Questions: Instructions:This Case study must be submitted on Blackboard via the allocated folder Email submission will not be accepted Students must work as groups in which each group has up to four ...

Course

IT242

Type

Programming

Subject

Database

University

Saudi Electronic University

ITEC200 Data and Information Management

Question

Answered

Questions:

Context

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. 

Data Requirements: 

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. 

Staff 

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.

Property owners 

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. 

Clients 

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. 

Leases 

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

ITEC200 Data and Information Management

Answer in Detail

Solved by qualified expert

Get Access to This Answer

Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Hac habitasse platea dictumst vestibulum rhoncus est pellentesque. Amet dictum sit amet justo donec enim diam vulputate ut. Neque convallis a cras semper auctor neque vitae. Elit at imperdiet dui accumsan. Nisl condimentum id venenatis a condimentum vitae sapien pellentesque. Imperdiet massa tincidunt nunc pulvinar sapien et ligula. Malesuada fames ac turpis egestas maecenas pharetra convallis posuere. Et ultrices neque ornare aenean euismod. Suscipit tellus mauris a diam maecenas sed enim. Potenti nullam ac tortor vitae purus faucibus ornare. Morbi tristique senectus et netus et malesuada. Morbi tristique senectus et netus et malesuada. Tellus pellentesque eu tincidunt tortor aliquam. Sit amet purus gravida quis blandit. Nec feugiat in fermentum posuere urna. Vel orci porta non pulvinar neque laoreet suspendisse interdum. Ultricies tristique nulla aliquet enim tortor at auctor urna. Orci sagittis eu volutpat odio facilisis mauris sit amet.

Tellus molestie nunc non blandit massa enim nec dui. Tellus molestie nunc non blandit massa enim nec dui. Ac tortor vitae purus faucibus ornare suspendisse sed nisi. Pharetra et ultrices neque ornare aenean euismod. Pretium viverra suspendisse potenti nullam ac tortor vitae. Morbi quis commodo odio aenean sed. At consectetur lorem donec massa sapien faucibus et. Nisi quis eleifend quam adipiscing vitae proin sagittis nisl rhoncus. Duis at tellus at urna condimentum mattis pellentesque. Vivamus at augue eget arcu dictum varius duis at. Justo donec enim diam vulputate ut. Blandit libero volutpat sed cras ornare arcu. Ac felis donec et odio pellentesque diam volutpat commodo. Convallis a cras semper auctor neque. Tempus iaculis urna id volutpat lacus. Tortor consequat id porta nibh.

Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Hac habitasse platea dictumst vestibulum rhoncus est pellentesque. Amet dictum sit amet justo donec enim diam vulputate ut. Neque convallis a cras semper auctor neque vitae. Elit at imperdiet dui accumsan. Nisl condimentum id venenatis a condimentum vitae sapien pellentesque. Imperdiet massa tincidunt nunc pulvinar sapien et ligula. Malesuada fames ac turpis egestas maecenas pharetra convallis posuere. Et ultrices neque ornare aenean euismod. Suscipit tellus mauris a diam maecenas sed enim. Potenti nullam ac tortor vitae purus faucibus ornare. Morbi tristique senectus et netus et malesuada. Morbi tristique senectus et netus et malesuada. Tellus pellentesque eu tincidunt tortor aliquam. Sit amet purus gravida quis blandit. Nec feugiat in fermentum posuere urna. Vel orci porta non pulvinar neque laoreet suspendisse interdum. Ultricies tristique nulla aliquet enim tortor at auctor urna. Orci sagittis eu volutpat odio facilisis mauris sit amet.

Tellus molestie nunc non blandit massa enim nec dui. Tellus molestie nunc non blandit massa enim nec dui. Ac tortor vitae purus faucibus ornare suspendisse sed nisi. Pharetra et ultrices neque ornare aenean euismod. Pretium viverra suspendisse potenti nullam ac tortor vitae. Morbi quis commodo odio aenean sed. At consectetur lorem donec massa sapien faucibus et. Nisi quis eleifend quam adipiscing vitae proin sagittis nisl rhoncus. Duis at tellus at urna condimentum mattis pellentesque. Vivamus at augue eget arcu dictum varius duis at. Justo donec enim diam vulputate ut. Blandit libero volutpat sed cras ornare arcu. Ac felis donec et odio pellentesque diam volutpat commodo. Convallis a cras semper auctor neque. Tempus iaculis urna id volutpat lacus. Tortor consequat id porta nibh.

Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Hac habitasse platea dictumst vestibulum rhoncus est pellentesque. Amet dictum sit amet justo donec enim diam vulputate ut. Neque convallis a cras semper auctor neque vitae. Elit at imperdiet dui accumsan. Nisl condimentum id venenatis a condimentum vitae sapien pellentesque. Imperdiet massa tincidunt nunc pulvinar sapien et ligula. Malesuada fames ac turpis egestas maecenas pharetra convallis posuere. Et ultrices neque ornare aenean euismod. Suscipit tellus mauris a diam maecenas sed enim. Potenti nullam ac tortor vitae purus faucibus ornare. Morbi tristique senectus et netus et malesuada. Morbi tristique senectus et netus et malesuada. Tellus pellentesque eu tincidunt tortor aliquam. Sit amet purus gravida quis blandit. Nec feugiat in fermentum posuere urna. Vel orci porta non pulvinar neque laoreet suspendisse interdum. Ultricies tristique nulla aliquet enim tortor at auctor urna. Orci sagittis eu volutpat odio facilisis mauris sit amet.

Tellus molestie nunc non blandit massa enim nec dui. Tellus molestie nunc non blandit massa enim nec dui. Ac tortor vitae purus faucibus ornare suspendisse sed nisi. Pharetra et ultrices neque ornare aenean euismod. Pretium viverra suspendisse potenti nullam ac tortor vitae. Morbi quis commodo odio aenean sed. At consectetur lorem donec massa sapien faucibus et. Nisi quis eleifend quam adipiscing vitae proin sagittis nisl rhoncus. Duis at tellus at urna condimentum mattis pellentesque. Vivamus at augue eget arcu dictum varius duis at. Justo donec enim diam vulputate ut. Blandit libero volutpat sed cras ornare arcu. Ac felis donec et odio pellentesque diam volutpat commodo. Convallis a cras semper auctor neque. Tempus iaculis urna id volutpat lacus. Tortor consequat id porta nibh.

26 More Pages to Come in This Document. Get access to the complete answer.

MyAssignmenthelp.com has achieved the status of the best essay writing service because of an unbreakable record of delivering high-class service. Our efficient team of essay writers are based in different countries including the USA, UK and Australia. We have build teams of native essay writers to deliver country-specific, universities' requirement based custom essay help. Other than making sure that students get customised solutions. We deliver requirement based solutions and ensure that the price remains reasonable so that students can readily access our services. Because of our flexible approach, students who search the internet with search terms like write my essay at a cheap rate, find our service most in sync with their demands.

More ITEC200 ITEC200 Data and Information Management: Questions & Answers

Q
icon

We aren't endorsed by this University

IMAT5167 Data Warehouse Design and OLAP

Task: Learning Outcomes A comprehensive understanding of the concepts, purposes, architectures, evolution and benefits of DW. A systematic knowledge of how to apply ERD and Star Schema to design databases, DW or data mart. The ability to design appropriate data extraction, transformation and ...

View Answer
Q
icon

We aren't endorsed by this University

COMP9771 Advanced Database Applications

In this assignment you are asked to investigate and produce  paper on any areaof your choice as long as it adheres to the following rules:•It must discuss a future database technology or a current database or data analytics technology currently being enhanced for future use. If you are goi ...

View Answer
Q
icon

We aren't endorsed by this University

CO2701 Database Systems

Question: If you have not yet made the university aware of any disability, specific learning difficulty, long-term health or mental health condition, please complete a Disclosure Form.  The Inclusive Support team will then contact to discuss reasonable adjustments and support relating to a ...

View Answer
Q
icon

We aren't endorsed by this University

IT242 Software Engineering

Questions: Instructions:This Case study must be submitted on Blackboard via the allocated folder Email submission will not be accepted Students must work as groups in which each group has up to four students.You are advised to make your work clear and well-presented, marks will be reduced for poor ...

View Answer

Content Removal Request

If you are the original writer of this content and no longer wish to have your work published on Myassignmenthelp.com then please raise the content removal request.

Choose Our Best Expert to Help You

icon

5% Cashback

On APP - grab it while it lasts!

Download app now (or) Scan the QR code

*Offer eligible for first 3 orders ordered through app!

screener
ribbon
callback request mobile
Have any Query?