The first part requires a ER Diagram

ERD Problem (30 points) A database needs to be developed for an apartment rental agency in a big city. The agency rents out properties to tenants on behalf of the landlords. 1. The company employs staff. Every member of staff has a unique staff ID. In addition they have a name, a function (like secretary, accountant, etc.), and a monthly salary. 2. In order to locate properties, the agency has split the city into different areas. Each area has a name, by which it can be uniquely identified. For each area, there is a member of staff who is responsible for the properties in the area. 3. For each property that the agency manages there is a property ID by which it can be identified. Moreover, the agency wants to record the address, the type of the property (like flat, house, garage, etc.), the monthly rate, and the deposit that has to be paid by a tenant. Each property is located in some area. 4. Each property is of a specific type, for instance, a flat, a house, or a garage. The possible types are specified by the company. Each possible type needs to be recorded, also if there is no property of that type in the database. 5. Each property is owned by a single landlord. A landlord, however, may own more than one property. A landlord is registered with name, address, and phone number. Each landlord can be uniquely identified by their name and address. 6. A tenant is registered with a tenant ID, a current address, and a phone number 7. A tenant rents a property by signing a lease. The lease specifies the start date, i.e., the time when the contract begins, and the duration of the lease. A lease is always related to a specific property. For a given property, there cannot be two leases with the same start date. If you make any assumptions in your design, please write them down. Assumptions, however, must not contradict the requirements. SQL Exercises (20 points) Consider the bank database above, where the primary keys are underlined. Construct the following SQL queries for this relational database. branch(branchname, branch city, assets) customer (customername, customer street, customer city) loan (loannumber, branch name, amount) borrower (customername, loannumber) account (accountnumber, branchname, balance) depositor (customername, accountnumber) 1. Find all customers of the bank who have an account but not a loan. 2. Find the names of all customers who live on the same street and in the same city as "Smith". 3. Find the names of all branches with customers who have an account in the bank and who live in "Harrison". 4. Find all customers who have an account at all the branches located in "Brooklyn". 5. Find out the total sum of all loan amounts in the bank 6. Find the names of all branches that have assets greater than those of at least one branch located in Brooklyn