Premium Payments

1. Assume User A created a sells(bar, beer, price) table and granted SELECT and INSERT privileges with GRANT option to User B. User B then granted SELECT privilege on attributes bar and beer to User C. Can User C create a view as follows and can User C insert a new tuple into the view? Explain your answer.

CREATE VIEW v AS SELECT bar, beer FROM sells

2. Consider the following relations about auto parts and their suppliers.

part (pid, pname, color)

supplier (sid, sname, city)

supplies (sid, pid, price)

express the following queries in SQL. (Only stan-dard SQL syntax is allowed. Each query should be answered in a single SQL statement.)

• Find the names of suppliers and the names of parts they sell. List result in descending order of suppliers’ names.
• Find the names of suppliers that are located in Lowell.
• Find the names of parts that are sold by exactly two suppliers.
• Find the names of parts that are sold by all suppliers.
• Find the name of supplier who sells the cheapest wheel and list their price of wheels.

6. Find the average price of wheels sold in each city.

7. Find the name of part that is sold by most number of suppliers.

8. Find the names of suppliers whose average price for the parts they sell is over $100.

9. Find the name of supplier who sells the most number of different colors of parts and list the colors of parts that this supplier sells.

10. For the suppliers who are located in a city that the city has the lowest average price of wheels, list the names of suppliers, their price of wheels, and the city they are located in.

Construct an E-R diagram for an insurance company. A person may own one or more vehicles. A vehicle may have one or more owners. The types of vehicles are: motorcycles, passenger cars, trucks, vans and buses. Owners are the only drivers of motorcycles and passenger cars; trucks, vans and buses may have drivers who are not the owner of the vehicle. Vehicles may be involved in accidents. If there is an accident, the information of the vehicle and the driver will be recorded as well as time, place, description of the accident. All vehicles must be insured. Each insurance policy covers one or more vehicles, and has one premium payments associated with the policy. Each payment is for a particular period of time, and has an associated due date, and the date when the payment was received. If a policy is terminated, payments associated with it should be stopped.

1. Construct an E-R diagram to capture these information.

2. Translate the E-R diagram into relations. Indicate the Primary Key of each table.