RA

RA/DRC/SQL/

==============================================================

RA> project[pname](select[price<20](parts));

temp1(PNAME:VARCHAR)

Number of tuples = 6

Land Before Time I:

Land Before Time II:

Land Before Time III:

Land Before Time IV:

When Harry Met Sally:

Dirty Harry:

project[ename,city](

(((select[price>50](parts)

join

odetails)

join orders)

join employees) join zipcodes

);

Number of tuples = 0

RA> project[cno1,cno2](

select[zip1=zip2 and cno1<>cno2](

(rename[cno1,zip1](project[cno,zip](customers))

times

rename[cno2,zip2](project[cno,zip](customers))

)

);

RA> temp6(CNO1:INTEGER,CNO2:INTEGER)

Number of tuples = 2

1111:2222:

2222:1111:

d(?)

RA> project[ename](

(((select[city='Wichita'](zipcodes)

join

employees)

join orders)

join customers)

);

RA> temp4(ENAME:VARCHAR)

Number of tuples = 1

Jones:

The question was a little vague, so I did this question in two versions:

1. The customer names for those who has bought parts less than 20

project[cname,pname,price]

(

select[price<20](parts)

join odetails

join orders

join customers

);

RA> temp4(CNAME:VARCHAR)

Number of tuples = 3

Charles:

Bertram:

Barbara:

2. The customer(s) who purchased everything under 20

RA> project[cname](

(customers

join

(project[cno](customers)

minus

project[cno]((

(project[cno](customers)

times

project[pno](select[price<20](parts))

)

Minus

project[cno,pno](orders join odetails)

)))));

RA> temp11(CNAME:VARCHAR)

Number of tuples = 0

project[cname](

project[cname](customers)

minus

project[cname]

(

customers join orders

)

);

temp4(CNAME:VARCHAR)

Number of tuples = 0

Project[cname](

Rename[cno,cname](project[cno1,cname1](

Select[cno1=cno2 and ono1<>ono2](

(

(rename[cno1,cname1,ono1](Project[cno,cname,ono](orders join customers)))

times

(rename[cno2,cname2,ono2](Project[cno,cname,ono](orders join customers)))

)

);

RA> temp10(CNAME:VARCHAR)

Number of tuples = 1

Charles:

===================================================================================

DRC:

DRC> { b | (exists a,c,d,e) (parts(a,b,c,d,e) and d<20)}

ANSWER(B:VARCHAR)

Number of tuples = 6

Land Before Time I:

Land Before Time II:

Land Before Time III:

Land Before Time IV:

When Harry Met Sally:

Dirty Harry:

DRC> { m,q | (exists a,b,c,d,e,f,g,I,j,l,n,o,p) (zipcodes(n,q) and employees(l,m,n,o) and orders(f,g,l,I,j) and odetails(f,a,p) and parts(a,b,c,d,e) and d>50 )}

ANSWER(M:VARCHAR,Q:VARCHAR)

Number of tuples = 0

DRC> { a,f | (exists c,b,e,d,g,I,l) (customers(a,b,c,d,e) and customers(f,g,i,d,l) and not (customers(a,b,c,d,e) and customers(a,g,i,d,l)))}

ANSWER(A:INTEGER,F:INTEGER)

Number of tuples = 2

1111:2222:

2222:1111:

{ d | (exists a,c,f,l,g,o,p,q,r,s,t)(

Zipcodes(a,’Wichita’) and

Employees(c,d,a,f ) and orders(l,g,c,o,p) and customers(g,q,r,s,t)) }

ANSWER(D:VARCHAR)

Number of tuples = 1

Jones:

e. { g | (exists a,b,c,d,e,f,h,I,j,k,m,n,o,q) (customers(f,g,h,I,j) and orders(k,f,m,n,o) and odetails(k,a,q) and parts(a,b,c,d,e) and d<20)}

ANSWER(G:VARCHAR)

Number of tuples = 3

Charles:

Bertram:

Barbara:

{ b | (exists a,c,d,e,t,m,n,o)(

customers(a,b,c,d,e) and

not ((exists m,n,o,t)(orders(t,a,m,n,o))) ) }

DRC> ANSWER(B:VARCHAR)

Number of tuples = 0

DRC> { b | (exists a,c,d,e,g,h,I,k,l,m,f,j) (customers(a,b,c,d,e) and orders(f,a,g,h,I) and orders(j,a,k,l,m) and j<>f )}

ANSWER(B:VARCHAR)

Number of tuples = 1

Charles:

================================================================================

SQL

drop table odetails;

drop table orders;

drop table employees;

drop table zipcodes;

drop table parts;

drop table customers;

create table customers (

cno number(10) not null,

cname varchar(20),

caddress varchar(100),

zip number(10),

phone varchar(50),

primary key (cno));

create table parts (

pno number(10) not null,

pname varchar(50),

qoh number(10),

price decimal(10),

olevel number(10),

primary key (pno));

create table zipcodes (

zip number(10) not null,

city varchar(20),

primary key (zip));

create table employees (

eno number(10) not null,

ename varchar(20),

zip number(10) not null,

edate varchar(50),

primary key (eno),

foreign key (zip) references zipcodes);

create table orders (

ono number(10) not null,

cno number(10) not null,

eno number(10) not null,

received varchar(30),

shipped varchar(30),

primary key(ono),

foreign key (cno) references customers,

foreign key (eno) references employees);

create table odetails (

ono number(10) not null,

pno number(10) not null,

qty number(5) not null,

foreign key (ono) references orders,

foreign key (pno) references parts);

insert into customers values (1111,'Charles','123 Main St.',67226,'316-636-5555');

insert into customers values (2222,'Bertram','237 Ash Avenue',67226,'316-689-5555');

insert into customers values (3333,'Barbara','111 Inwood St.',60606,'316-111-1234');

insert into customers values (4444,'Xiaoyuan','111 Outwood St.',61111,'316-111-1234');

insert into customers values (5555,'Suo_noOrder','222 Outwood St.',66002,'316-111-1234');

insert into customers values (6666,'Suo_all20','333 wood St.',54444,'316-111-1234');

insert into parts values (10506,'Land Before Time I',200,19.99,20);

insert into parts values (10507,'Land Before Time II',156,19.99,20);

insert into parts values (10508,'Land Before Time III',190,19.99,20);

insert into parts values (10509,'Land Before Time IV',60,19.99,20);

insert into parts values (10601,'Sleeping Beauty',300,24.99,20);

insert into parts values (10701,'When Harry Met Sally',120,19.99,30);

insert into parts values (10800,'Dirty Harry',140,14.99,30);

insert into parts values (10900,'Dr. Zhivago',100,24.99,30);

insert into parts values (11000,'Suo_more_50',100,51.99,30);

insert into zipcodes values (67226,'Wichita');

insert into zipcodes values (60606,'Fort Dodge');

insert into zipcodes values (50302,'Kansas City');

insert into zipcodes values (54444,'Columbia');

insert into zipcodes values (66002,'Liberal');

insert into zipcodes values (61111,'Fort Hays');

insert into employees values (1000,'Jones',67226,'12-DEC-95');

insert into employees values (1001,'Smith',60606,'01-JAN-92');

insert into employees values (1002,'Brown',50302,'01-SEP-94');

insert into orders values (1020,1111,1000,'10-DEC-94','12-DEC-94');

insert into orders values (1021,1111,1000,'12-JAN-95','15-JAN-95');

insert into orders values (1022,2222,1001,'13-FEB-95','20-FEB-95');

insert into orders values (1023,3333,1000,'20-JUN-97','22-JUN-97');

insert into orders values (1024,4444,1000,'31-JAN-07','31-JAN-07');

insert into orders values (1025,6666,1002,'14-FEB-95','20-FEB-95');

insert into orders values (1026,6666,1001,'14-FEB-95','20-FEB-95');

insert into odetails values (1020,10506,1);

insert into odetails values (1020,10507,1);

insert into odetails values (1020,10508,2);

insert into odetails values (1020,10509,3);

insert into odetails values (1021,10601,4);

insert into odetails values (1022,10601,1);

insert into odetails values (1022,10701,1);

insert into odetails values (1023,10800,1);

insert into odetails values (1023,10900,1);

insert into odetails values (1024,11000,1);

insert into odetails values (1025,10506,1);

insert into odetails values (1025,10507,1);

insert into odetails values (1025,10508,1);

insert into odetails values (1025,10509,1);

insert into odetails values (1025,10701,1);

insert into odetails values (1026,10800,1);