【正文】 avarage salary of the emp table. 30. Branch(branchname, branchcity, assets)Account(accountnumber, branchname,balance)Depositer(customername, accountnumber)Customer(customername, customerstreet, customercity)Loan(loannumber, branchname, amount)Borrower(customername, loannumber)Database schema is given above.A. Find all customers who have both an account and a loan in the bank.B. Find all customers who have a loan at the bank but do not have an account at the bank.C. Find all customers who have both an account and a loan at the Perryridge branch.D. Find all branches that have greater assets than some branches located in Brooklyn. E. Find all loan numbers which appear in the loan relation with null values for amount.31. You have been asked to design a database for the university administration, which records the following information:1. All students necessarily have a unique student ID, a name, and a university address. Each student is also either an undergraduate or a graduate student.2. Each graduate student has an advisor.3. Each undergraduate student has a major.4. Students take courses. A student may take one course, multiple courses, or no courses.5. Each course has a course number, course name, and days of the week the course is scheduled.6. Each course has exactly one head TA, who is a graduate student.7. Every head TA has an office where he or she holds office hours.A. Draw an ER diagram for this application. Be sure to mark the multiplicity of each relationship of the diagram. Decide the key attributes and identify them on the diagram. Please state all assumptions you make in your answers. B. Translate your ER diagram into a relational schema. Select approaches that yield the fewest number of relations。 merge relations where appropriate. Specify the key of each relation in your schema. 32. Consider a relation R with five attributes A, B, C, D, and E. The following dependencies are given:AB174。 C, BC 174。 D, CD 174。 E, DE 174。 A.A. List all keys for R. Do not list superkeys that are not a key.B. Is R in 3NF? Briefly explain why.C. Is R in BCNF? If yes, please explain why. Otherwise, depose R into relations that are in BCNF. 33. The following questions refer to the database schema below: Product(pid, price, color), Order(cid, pid, quantity), Customer(cid, name, age).A. Write a query, in relational algebra, to return the names of customers who order at least one product with color “Red.”B. Write an SQL query, to return the total quantity of products ordered by customers with age greater than 70. C. Write an SQL query, to return the pid(s) of the most ordered product(s) (. the product(s) with the highest total ordered quantities). 34. Consider “drinker” database with the following relations.Drinker(drinkerName, street, age)Bar(barName, owner, street)Frequent(drinkerName, barName)We ask you to write queries. Please write simple and nonredundant queries – Note that we will really check if your answers are unnecessarily plex.A. In relational algebra, write a query to return the bars that Sally frequents. B. In relational algebra, write a query to return each drinker who frequents only bars on the same street that he lives. C. In SQL, write a query to return the bars whose frequent drinkers are “young”– in particular, with average age below 37.35. Consider the following relational schema:Account(accountNumber, branchName, balance)Branch(branchName, street, city, assets)Customer(customerSSN, street, city)Deposit(customerSSN, accountNumber,Amount)A. List all the attributes (in the four tables) that are foreign keys and indicate what attributes they are referencing.B. Define a view BigBranch that gives for each branch its branchName, city, and assets. The branch should have more than 50 accounts and the total balance of all accounts is greater than $1,000,000. C. Suppose we want to check that, for each branch, the total balance of all accounts is less than or equal to the assets of the branch. Complete the following SQL statement, by specifying _condition_. Note, by definition, such an “assertion” statement will enforce the _condition_ to hold true at all times.CREATE ASSERTION BalanceCheck CHECK _condition_36. Convert the following unnormalized tables into First Normal Form (1NF) relations :(a) R(A, B, {C, D})(b) R(A, B, {C, D, {E, F}})37. Convert ER diagrams A and B below into relations. AB