SQL Statement and ER Diagram for Insurance Company Assume User A created a sells (bar, beer, and 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
SQL Statement and ER Diagram for Insurance Company
- Consider the following relations about auto parts and their suppliers.
Part (pid, name, color)
Supplier (sid, name, city)
Supplies (sid, pid, price)
Express the following queries in SQL. (Only standard 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 NewYork.
Find the names of parts that are sold by exactly two suppliers.
SQL Statement and ER Diagram for Insurance Company
Find the names of parts that are sold by all suppliers.
Find the name of a supplier who sells the cheapest wheel and lists their price of wheels.
- Find the average price of wheels sold in each city.
- Find the name of the part that is sold by the most number of suppliers.
- Find the names of suppliers whose average price for the parts they sell is over $100.
- Find the name of a supplier who sells the most number of different colors of parts and lists the colors of parts that this supplier sells.
- 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.
SQL Statement and ER Diagram for Insurance Company
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, and description of the accident. All vehicles must be insured. Each insurance policy covers one or more vehicles and has one premium payment 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.
- Construct an E-R diagram to capture this information.
- Translate the E-R diagram into relations. Indicate the Primary Key of each table.
