Database Management Systems. (Please give all necessary things). Thanks

Question 1: Group by and Aggregates: (read instructions very carefully, understand what is being asked for) (think if asked How many students are in the ICS 311 class? what you would do to get that answer) (do not include more tables then needed, in order to answer the question) (test your code thoroughly, think beyond just what is given to you)

Write SQL statements to answer the following questions using Assignment 3s schema (tables from part 1).

1- Find how many branches had have loans over $2000.00.

2- For each branch, find the most expensive loan. Your output should include Branch Id, loan amount for the highest loan for that Branch.

3- Find how many accounts there are for each customer. The output should include customer id and number of accounts for that customer.

4- Find the total balance amount of all accounts by each Branch. The output should be a list of Branch Id and for each Branch Id, the total balance of accounts in that Branch.

5- Find Customer ID, Customer name and the number of loans for each Customer.

6- Find Customer ID, Customer name for all accounts, sorted by Customer name.

7- Find Loan number and Branch Id of the loan with the lowest amount.

8- Create a view called MPL_Branch_V that contains Branch Id, Branch name, and number of loans for each Branch that is in the city of Minneapolis.

9- For each Customer in Hopkins, find the total amount of all their loans.

10- Find how many different accounts each customer has at each Branch. The output should be a list of Customer ID and for each Customer ID, the number of accounts for this customer by Branch ID.

Question 2: ERD Analysis

Given the following ERD for a Restaurant Inc.:

2.1) Identify (write down) all relationships and their cardinality and write the corresponding business rules.

2.2) Show the relational schema for the database. Make sure to clearly indicate primary keys and foreign keys (be careful on this part).

Question 3: ERD design

Although you always wanted to be a dog trainer, you ended up being an expert on databases because you love to work with data and logic. Your old love is still there, however, so you set up a database company, DogTraining, that builds a product for dog kennels. The core of this product is a database with a schema that captures all the information that kennels need to maintain. Kennels keep information about dog trainers, their names (which are unique), birthplaces, age, and style of training. For each dog, the trainer, the year it was born, its unique name, the type of dog (e.g., Brittany, German Shepard, Collie, etc.), and its price must be stored. Dogs are also classified into groups of various kinds, for example, hunting, guard, working; a given dog may belong to more than one group. Each group is identified by a name (like those just given) that describes the group. Finally, kennels keep information about customers. For each customer, kennels keep that persons unique name, address, total amount of dollars spent at the kennel (very important!), and the trainers and groups (not types) of dogs that the customer tends to like.

3.1) Draw the ER diagram for the database.

3.2) Identify the relational schema for the database. Make sure to clearly indicate primary keys and foreign keys.

Rst ID meRst sales mp_First Name Emp Last_Nam mp Contact In ormation Emp Id Employee Employed By Restaurant Owned By Works Shift Corporation ComD Corp name Shift Job Shift ID Shift Hours Rst ID meRst sales mp_First Name Emp Last_Nam mp Contact In ormation Emp Id Employee Employed By Restaurant Owned By Works Shift Corporation ComD Corp name Shift Job Shift ID Shift Hours