oracle - How to represent relational division(basic algebra expression) in terms of SQL -


query: find names of sailors have reserved boats

this can represented in relation algebra as:
1. πsname ( ((σsid,bid reserves) / (σbid boats)) ⋈ sailors)

as per relational algebra, division can represented using basic algebra operator follows:

  1. a/b= πx(a) - πx((πx(a) * b) - )

thus if convert statement 1 per statement 2 then

  1. reserves/boats= πsid(reserves) - πsid(( πbid(reserves) * boats) - reserves )

how can represent statement 3 in terms of sql in same way in relation algebra (i.e without using operator other minus/except(-) , cross join(*)).
trying achieve without use of not exists , exists condition.

schema of tables follows:
sailors(sid: integer, sname: string, rating: integer, age: real)
boats(bid: integer, bname: string, color: string)
reserves(sid: integer, bid: integer, day: date)

given ddl tables corresponding relevant relations:

create table boats(   bid int,   bname varchar(50),   color varchar(50) );  create table reserves(   sid int,   bid int,   day date ); 

you can transliterate division formula (3) oracle sql syntax straightforwardly, though it's verbose:

-- sailors reserved @ least 1 boat select distinct sid reserves  minus   -- sailors reserved @ least 1 boat, not of them select sid (   -- combinations of sailor reserved boat boat   -- available reserved:   select reserves.sid, boats.bid       reserves     cross join     boats    minus    -- combinations of sailor , boat actual reservations made   select sid, bid   reserves ) sids 

as specified, uses cross join , minus operations, correspond directly relational algebra formula. in real-world database application, however, 1 surely obtain same result via altogether different query.

note sql databases can , violate principle of formal relational algebra relations not contain duplicate tuples. reason select distinct in first subquery. distinct selection applied strategically elsewhere in query might make more efficient, not alter result.


Comments

Popular posts from this blog

java - WARN : org.springframework.web.servlet.PageNotFound - No mapping found for HTTP request with URI [/board/] in DispatcherServlet with name 'appServlet' -

html - Outlook 2010 Anchor (url/address/link) -

android - How to create dynamically Fragment pager adapter -