Essay About Select C.Carrier And Find Businesses

Dbms Subqueries
Essay Preview: Dbms Subqueries
Report this essay
Find Carriers who have not delivered to more than 1 city (5 pts)SELECT C.CARRIER_NAMEFROM CARRIER CWHERE EXISTS (SELECT s1.carrier_name,count (DISTINCT s1.DESTINATION_CITY) CNTFROM shipment s1WHERE S1.CARRIER_NAME = C.CARRIER_NAMEGROUP BY s1.carrier_nameHAVING CNT <= 1);[pic 1]Find businesses who have never shipped to Seattle (5 pts)SELECT b1.business_nameFROM business b1WHERE b1.business_name NOT IN (SELECT b.business_nameFROM Business b,Shipment SWHERE b.business_id = s.business_id AND s.destination_city = "Seattle");[pic 2]Find Carrier(s) with the most number of packages shipped (5 pts)SELECT MAX(mycount),s.carrier_nameFROM (SELECT s.carrier_name,count(s.carrier_name) mycountFROM shipment sGROUP BY s.carrier_name);[pic 3]Find total employee count of businesses who have shipped at least once to Jacksonville (5 pts)SELECT sum(b.num_employees) Total_employeesFROM business bWHERE EXISTS (SELECT s.business_idFROM shipment sWHERE s.destination_city = "Jacksonville" AND s.business_id = b.business_id);[pic 4]        Find businesses who work with carriers who have never delivered to Santa Clara (5 pts)SELECT b.business_nameFROM business bWHERE b.business_id IN (SELECT DISTINCT (s1.business_id) FROM shipment s1WHERE s1.carrier_name IN (SELECT c.carrier_nameFROM carrier c,shipment sWHERE c.carrier_name NOT IN (SELECT s.carrier_nameFROM shipment sWHERE s.destination_city = "Santa Clara")));[pic 5]                                   PART –IIProvide a real life example of a schema that demonstrates a multi-valued dependency. Do not use the example presented in the class or your text book. Show how you convert this schema to 4th Normal form to eliminate this multi-valued dependency. (5 pts)Ans: A simple example of MVD(Multi Valued Dependency) can be of Favourite Movie  and Favourite snack during movieCust_IdFavourite MovieFav_snack_during_movie401InceptionPop Corn401Hateful EightPop Corn401InceptionCheese Ball401Hateful EightCheese BallThe above Database shows that Favourite Movie and Favourite snack are independent multi-valued facts for the same customer(Cust_id ) so it has MVD(Multi Valued Dependency).