This question is related to the subject of databaseConsider the System Description as in the attached picture. you are advised to reply the following queries, keeping in view the above mention system description title as “Dream Home branch offices case study”. only the query is required.List the details of branches in a given city.Identify the total number of branches in each city.List the name, position, and salary of staff at a given branch, ordered by staff name.Identify the total number of staff and the sum of their salaries.Identify the total number of staff in each position at branches in Glasgow.List the names of staff supervised by a named Supervisor.List the property number, address, type, and rent of all properties in Glasgow, ordered by rental amount.Create a branch and staff relationship in view of joint perspective.List the details of properties for rent managed by a named member of staff.Identify the total number of properties assigned to each member of staff at a given branch.List the details of properties provided by business owners at a given branch.Identify the total number of properties of each type at all branches.Identify the details of private property owners that provide more than one property for rent.Identify flats with at least three rooms and with a monthly rent no higher than £500 in Aberdeen. List the details of leases due to expire next month at a given branch.List the total number of leases with rental periods that are less than one year at branches in London.List the total possible daily rental for property at each branch, ordered by branch number. System Description:Branches Dream Home has branch offices in cities throughout the United Kingdom. Each branchoffice is allocated members of staff, including a Manager, who manages the operations of theoffice. 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 ofstaff who currently manages the office. Additional data is held on each Manager, which includesthe date that the Manager assumed his or her position at the current branch office, and a monthlybonus payment based upon his or her performance in the property for rent market. StaffMembers of staff with the role of Supervisor are responsible for the day-to-day activities of anallocated group of staff called Assistants (up to a maximum of 10, at any one time). Not allmembers of staff are assigned to a Supervisor. The data stored regarding each member of staffincludes staff number, name, address, position, salary, name of Supervisor (where applicable), andthe details of the branch office at which a member of staff is currently working. The staff numberis unique across all branches of Dream Home. Properties for rent each branch office offers a rangeof properties for rent. The data stored for each property includes property number, address(street, city, and postcode), type, and number of rooms, monthly rent, and the details of theproperty owner. The property number is unique across all branch offices. The management of aproperty is assigned to a member of staff whenever it is rented out or requires to be rented out. Amember of staff may manage a maximum of 100 properties for rent at any one time. When agiven property is available for rent, the property details will be displayed on the Dream HomeWeb site and, when necessary, as advertisements in local and national newspapers. Propertyowners the details of property owners are also stored. There are two main types of propertyowner: private owners and business owners. The data stored for private owners includes ownernumber, name, address, telephone number, email, and password. The data stored on businessowners 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 Dream Home databaseusing the Web. Clients Dream Home refers to members of the public interested in renting propertyas clients. To become a client, a person must first register at a branch office of Dream Home. Thedata stored on clients includes client number, name, telephone number, email, preferred type ofaccommodation, and the maximum rent that the client is prepared to pay. Also stored is the nameof the member of staff who processed the registration, the date the client joined, and some detailson the branch office at which the client registered. The client number is unique across all DreamHome branches. Leases when a property is rented out, a lease is drawn up between the client andthe property. The data listed in detail on the lease includes lease number, client number, nameand address, property number and address, monthly rent, method of payment, an indication ofwhether 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. Newspapers when required, the details ofproperties for rent are advertised in local and national newspapers. The data stored includes theproperty number, address, type, number of rooms, rent, the date advertised, the name of thenewspaper, and the cost to advertise. The data stored on each newspaper includes the newspapername, address, telephone number, and contact name. Data entry Enter the details of a newbranch (such as branch B003 in Glasgow). Enter the details of a new member of staff at a branch(such as Ann Beech at branch B003). 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).Enter the details of a property advertised in a newspaper (such as property number PG4advertised in the Glasgow Daily newspaper on the 06-May-12).

Question

This question is related to the subject of databaseConsider the System Description as in the attached picture. you are advised to reply the following queries, keeping in view the above mention system description title as “Dream Home branch offices case study”. only the query is required.List the details of branches in a given city.Identify the total number of branches in each city.List the name, position, and salary of staff at a given branch, ordered by staff name.Identify the total number of staff and the sum of their salaries.Identify the total number of staff in each position at branches in Glasgow.List the names of staff supervised by a named Supervisor.List the property number, address, type, and rent of all properties in Glasgow, ordered by rental amount.Create a branch and staff relationship in view of joint perspective.List the details of properties for rent managed by a named member of staff.Identify the total number of properties assigned to each member of staff at a given branch.List the details of properties provided by business owners at a given branch.Identify the total number of properties of each type at all branches.Identify the details of private property owners that provide more than one property for rent.Identify flats with at least three rooms and with a monthly rent no higher than £500 in Aberdeen. List the details of leases due to expire next month at a given branch.List the total number of leases with rental periods that are less than one year at branches in London.List the total possible daily rental for property at each branch, ordered by branch number. System Description:Branches Dream Home has branch offices in cities throughout the United Kingdom. Each branchoffice is allocated members of staff, including a Manager, who manages the operations of theoffice. 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 ofstaff who currently manages the office. Additional data is held on each Manager, which includesthe date that the Manager assumed his or her position at the current branch office, and a monthlybonus payment based upon his or her performance in the property for rent market. StaffMembers of staff with the role of Supervisor are responsible for the day-to-day activities of anallocated group of staff called Assistants (up to a maximum of 10, at any one time). Not allmembers of staff are assigned to a Supervisor. The data stored regarding each member of staffincludes staff number, name, address, position, salary, name of Supervisor (where applicable), andthe details of the branch office at which a member of staff is currently working. The staff numberis unique across all branches of Dream Home. Properties for rent each branch office offers a rangeof properties for rent. The data stored for each property includes property number, address(street, city, and postcode), type, and number of rooms, monthly rent, and the details of theproperty owner. The property number is unique across all branch offices. The management of aproperty is assigned to a member of staff whenever it is rented out or requires to be rented out. Amember of staff may manage a maximum of 100 properties for rent at any one time. When agiven property is available for rent, the property details will be displayed on the Dream HomeWeb site and, when necessary, as advertisements in local and national newspapers. Propertyowners the details of property owners are also stored. There are two main types of propertyowner: private owners and business owners. The data stored for private owners includes ownernumber, name, address, telephone number, email, and password. The data stored on businessowners 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 Dream Home databaseusing the Web. Clients Dream Home refers to members of the public interested in renting propertyas clients. To become a client, a person must first register at a branch office of Dream Home. Thedata stored on clients includes client number, name, telephone number, email, preferred type ofaccommodation, and the maximum rent that the client is prepared to pay. Also stored is the nameof the member of staff who processed the registration, the date the client joined, and some detailson the branch office at which the client registered. The client number is unique across all DreamHome branches. Leases when a property is rented out, a lease is drawn up between the client andthe property. The data listed in detail on the lease includes lease number, client number, nameand address, property number and address, monthly rent, method of payment, an indication ofwhether 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. Newspapers when required, the details ofproperties for rent are advertised in local and national newspapers. The data stored includes theproperty number, address, type, number of rooms, rent, the date advertised, the name of thenewspaper, and the cost to advertise. The data stored on each newspaper includes the newspapername, address, telephone number, and contact name. Data entry Enter the details of a newbranch (such as branch B003 in Glasgow). Enter the details of a new member of staff at a branch(such as Ann Beech at branch B003). 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).Enter the details of a property advertised in a newspaper (such as property number PG4advertised in the Glasgow Daily newspaper on the 06-May-12).

Accepted Answer

note as per guidelines multiple question can't be answered so kindly repost the remaining questions…