NB. Relational Database Definitions

first =: {.
rest =: }.
open =: >
box =: <
atop =: @
head =: open atop first
rank =: "
bond =: &
link =: ;
copy =: #
insert =: /
and =: *.
append =: ,
not =: -.

NB. A relation is a collection of tuples which we represent as a table.

csg =: ('course';'studentid';'grade');('cs101';12345;'A');('cs101';67890;'B');('ee200';12345;'C');('ee200';22222;'B+');('cs101';33333;'A-');box('ph100';67890;'C+')

NB. In the definition of the relation csg we have 
NB. incorporated additional information attributes (a set of column names called
NB. scheme) at the beginning of our representation for a relation.

head csg NB. course_studentid_grade scheme

scheme =: head

rest csg NB. course_studentid_grade relation

relation =: rest

NB. The order of tuples in a relation is not significant.
NB. The order of columns in a relation is not significant.
NB. We do assume that column names are distinct.

NB. access column of a relation by name
NB. relation col 'column_name'

col =: dyad define '((scheme x.) i. box y.) bond from rank 1 open relation x.'

NB. access row of a relation by row_index
NB. relation row row_index

row =: dyad define 'open y. from rest x.'

NB. Some other sample relations.

snap =: ('studentid';'name';'address';'phone');(12345;'C. Brown';'12 Apple St.';'555-1234');(67890;'L. Van Pelt';'34 Pear Ave.';'555-5678');box(22222;'P. Patty';'56 Grape Blvd';'555-9999')

cp =: ('course';'prerequisite');('cs101';'cs100');('ee200';'ee005');('ee200';'cs100');('cs120';'cs101');('cs121';'cs120');('cs205';'cs101');('cs206';'cs121');box('cs206';'cs205')

cdh =: ('course';'day';'hour');('cs101';'M';'9AM');('cs101';'W';'9AM');('cs101';'F';'9AM');('ee200';'Tu';'10AM');('ee200';'W';'1PM');box('ee200';'W';'10AM')

cr =: ('course';'room');('cs101';'Turing Aud.');('ee200';'25 Ohm Hall');box('ph100';'Newton Lab.')

NB. A database is a collection of relations

NB. Problem 1.  Implement relation insert_tuple tuple

insert_tuple =: dyad define 'x. append box y.'

NB. example:

open csg insert_tuple 'ph100';12345;'B'

NB. A preliminary for problems 2 and 3.

NB. select_significant_scheme search_pattern
NB. where '*' in a search_pattern means match anything.
select_significant_scheme =: not atop ((box '*') bond match) rank 0

NB. example:

select_significant_scheme 'cs101';'*';'A'

NB. relation row_mask search_pattern
NB. where '*' in a search_pattern means match anything.

row_mask =: dyad define script
t =. select_significant_scheme y.
and insert rank 1 (t copy y.) = rank 1 t copy rank 1 open relation x.
)

NB. examples:

csg row_mask 'cs101';'*';'A' 
csg row_mask '*';'*';'*'
csg row_mask '*';12345;'*'
csg row_mask 'cs101';'*';'*'
csg row_mask 'cs101';12345;'*'

NB. Problem 2.  Implement relation delete search_pattern
NB.             where '*' in a search_pattern means match anything.

delete =: dyad define '(scheme x.) link (not x. row_mask y.) copy relation x.'

NB. example:

open csg delete '*';12345;'*'


NB. Problem 3.  Implement relation lookup search_pattern
NB.             where '*' in a search_pattern means match anything.

lookup =: dyad define '(scheme x.) link (x. row_mask y.) copy relation x.'

NB. example:

open csg lookup 'cs101';'*';'*'  

NB. Problem 4.  Devise a solution to the kind of problem "What grade did C. Brown
NB.             receive in cs101?"

snap lookup '*' ; 'C. Brown' ; '*' ; '*'

(snap lookup '*' ; 'C. Brown' ; '*' ; '*') col 'studentid'

csg lookup 'cs101' ; (open (snap lookup '*' ; 'C. Brown' ; '*' ; '*') col 'studentid') ; '*'
csg lookup 'cs101' ; ((snap lookup '*' ; 'C. Brown' ; '*' ; '*') col 'studentid') , box '*'

NB. Many database relations can be considered functions from one set of
NB. attributes to the remaining attributes.  Consider csg,
NB. csg might be considered a function defined on the domain ('course' ; 'studentid')
NB. whose range is 'grade' .

NB. A set of attributes which can serve as the domain of a function is called a key.
NB. Formally, a key for a relation is a set of one or more attributes such that
NB. under no circumstances will the relation have two tuples whose values agree in
NB. each column headed by a key attribute.

NB. An Algebra of Relations
NB. We wish to define the operations union, intersection and difference.
NB. For these three functions we assume the arguments each have the same scheme (attributes).

NB. Problems 5., 6., 7. Define dyads for union, intersection and difference.

NB. The selection operator
NB. This dyad takes a left argument of a relation and a right argument of a
NB. selection criteria of the form attribute fn value and returns a relation
NB. having the same scheme as x. which meets the selection criteria.

NB. For example csg select 'course' ; '=' ; 'cs101' would produce the same
NB. result as csg lookup 'cs101' ; '*' ; '*'.

NB. Problem 8. Implement the selection operator.

NB. The projection operator
NB. The project operator is a dyad whose left argument x. is a relation
NB. and right argument is a subset of attributes.  A new relation is formed
NB. having just these attributes after removing duplicate tuples.

NB. Problem 9. Implement the projection operator.

NB. The join operator
NB. The join operator is a dyad whose left argument x. contains two relations
NB. and the right argument contains two attributes; one each from the two
NB. respective relations.  The tuples in the join relation contain all
NB. which are common to both attributes.

NB. Problem 10. Implement the join operator.

NB. example:

NB. crdh =: (cr ; cdh) join 'course' ; 'course'

NB. This should produce a relation crdh having 6 tuples.