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:
a/b= πx(a) - πx((πx(a) * b) - )
thus if convert
statement 1
per statement 2
then
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
Post a Comment