Case Study $-$ DreamHome Case Study The first branch office of DreamHome was opened in $1992$ in a city called Glasgow in the UK$.$ Since then, the Company has grown steadily and now has several offices in most of the main cities of the UK$.$ However, the Company is now so large that more and more administrative staff are being employed to cope with the ever$-$increasing amount of paperwork. Furthermore, the communication and sharing of information between offices, even in the same city, is poor. The Director of the Company, Sally Mellweadows feels that too many mistakes are being made and that the success of the Company will be short$-$lived if she does not do something to remedy the situation. She knows that a database could help in part to solve the problem and requests that a database application be developed to support the running of DreamHome. The Director has provided the following brief description of how DreamHome currently operates. DreamHome specializes in property management, by taking an intermediate role between owners who wish to rent out their furnished property and clients of DreamHome who require to rent furnished property for a fixed period. DreamHome currently has about $2000$ staff working in $100$ branches. Each branch has an appropriate number and type of staff including a Manager, Supervisors, and Assistants. The Manager is responsible for the day$-$to$-$day running of a branch and each Supervisor is responsible for supervising a group of staff called Assistants. Each branch office offers a range of properties for rent. To offer property through DreamHome, a property owner normally contacts the DreamHome branch office nearest to the property for rent. The owner provides the details of the property and agrees an appropriate rent for the property with the branch Manager. Once a property is registered, DreamHome provides services to ensure that the property is rented out for maximum return for both the property owner and of course, DreamHome. These services include interviewing prospective renters $($called clients$),$ organizing viewings of the property by clients, advertising the property in the local or national newspapers $($when necessary$),$ and negotiating the lease. Once rented, DreamHome assumes responsibility for the property including the collection of rent. Members of the public interested in renting out property must first contact their nearest DreamHome branch office to register as clients of DreamHome. However, before registration is accepted, a prospective client is normally interviewed to record personal details and preferences of the client in terms of property requirements. Once registration is complete, clients are provided with weekly reports that list properties currently available for rent. Clients may request to view one or more properties from the list and after viewing will normally provide a comment on the suitability of the property. Properties that prove difficult to rent out are normally advertised in local and national newspapers. Once a client has identified a suitable property, a member of staff draws up a lease. At the end of a rental period a client may request that the rental be continued, however this requires that a new lease be drawn up$.$ Alternatively, a client may request to view alternative properties for the purposes of renting.

$4.$ Write SQL statements that can be used to accomplish the following in MS SQL: $($a$)$ Enter the details of a new branch $($such as branch B$003$ in Glasgow$).($b$)$ Enter the details of a new member of staff at a branch $($such as Ann Beech at branch B$003).($c$)$ Update the details of a member of staff at a branch. You can use any member as an example. $($d$)$ Delete the details of a given lease at a given branch. You can use any lease as an example. $($e$)$ Identify the total number of branches in each city. $($f$)$ List the name, position, and salary of staff at a given branch, ordered by staff name. $($g$)$ Identify the total number of staff and the sum of their salaries. $($h$)$ List the details of properties for rent managed by a named member of staff. $($i$)$ Identify flats with at least three rooms and with a monthly rent no higher than $350$ in Aberdeen.