OtherPapers.com - Other Term Papers and Free Essays
Search

Dbms Subqueries

Essay by   •  March 21, 2017  •  Coursework  •  391 Words (2 Pages)  •  1,121 Views

Essay Preview: Dbms Subqueries

Report this essay
Page 1 of 2
  1. Find Carriers who have not delivered to more than 1 city (5 pts)

SELECT C.CARRIER_NAME

FROM CARRIER C

WHERE EXISTS (

SELECT s1.carrier_name,

count (DISTINCT s1.DESTINATION_CITY) CNT

FROM shipment s1

WHERE S1.CARRIER_NAME = C.CARRIER_NAME

GROUP BY s1.carrier_name

HAVING CNT <= 1

);

[pic 1]

  1. Find businesses who have never shipped to Seattle (5 pts)

SELECT b1.business_name

FROM business b1

WHERE b1.business_name NOT IN (

SELECT b.business_name

FROM Business b,

Shipment S

WHERE b.business_id = s.business_id AND

s.destination_city = "Seattle"

);

[pic 2]

  1. Find Carrier(s) with the most number of packages shipped (5 pts)

SELECT MAX(mycount),

s.carrier_name

FROM (

SELECT s.carrier_name,

count(s.carrier_name) mycount

FROM shipment s

GROUP BY s.carrier_name

);

[pic 3]

  1. Find total employee count of businesses who have shipped at least once to Jacksonville (5 pts)

SELECT sum(b.num_employees) Total_employees

FROM business b

WHERE EXISTS (

SELECT s.business_id

FROM shipment s

WHERE s.destination_city = "Jacksonville" AND

s.business_id = b.business_id

);

[pic 4]

        

  1. Find businesses who work with carriers who have never delivered to Santa Clara (5 pts)

SELECT b.business_name

FROM business b

WHERE b.business_id IN (

SELECT DISTINCT (s1.business_id)

FROM shipment s1

WHERE s1.carrier_name IN (

SELECT c.carrier_name

FROM carrier c,

shipment s

WHERE c.carrier_name NOT IN (

SELECT s.carrier_name

FROM shipment s

WHERE s.destination_city = "Santa Clara"

)

)

);

[pic 5]

                                   PART –II

Provide 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 movie

Cust_Id

Favourite Movie

Fav_snack_during_movie

401

Inception

Pop Corn

401

Hateful Eight

Pop Corn

401

Inception

Cheese Ball

401

Hateful Eight

Cheese Ball

The 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).

...

...

Download as:   txt (3.1 Kb)   pdf (297.8 Kb)   docx (448.9 Kb)  
Continue for 1 more page »
Only available on OtherPapers.com