Relational Databases and Querying using Relational Algebra

Relational databases organize data in tables. Each table in the database contains information about objects of concern and relationships that objects have with other objects. So, one can associate a first-order logic predicate with each table.

Student Enrollment Database

STUDENT
SIDNAME
1111Jones
2222Smith
3333Blake
4444Damon
5555Bell
COURSE
CNOTITLE
CSc2310Java
CSc2510Theory
CSc4710Database
ENROLL
SIDCNOGRADE
1111CSc2310F
1111CSc2510F
2222CSc2510A
2222CSc4710B
3333CSc2310A
3333CSc2510A
3333CSc4710F

We can associate predicates student, course, and enroll with the three tables. The arities of the relations correspond to the number of columns in the table.

Queries using Relational Algebra

The basic relational algebra operations are:
  1. Select
  2. Project
  3. Join
  4. Rename
  5. Union
  6. Minus

To use the system run the following 2 commands the first time you sign in: 

cp ~raj/.cshrc .
source .cshrc
and to run the query system, execute the following command: 
java edu.gsu.cs.ra.RA ~raj/enrollDB
You will see a RA> prompt where you can enter a query. To exit the query system, enter the exit; command.

Now, some query examples:

  1. Get names of students enrolled in CSc2510.

    project[NAME](student join select[CNO='CSc2510'](enroll));

  2. Get CNO and TITLE of courses in which Smith is enrolled.

    project[CNO,TITLE](select[NAME='Smith'](student) join course join enroll);

  3. Get names of students who have enrolled in at least 2 courses.

    project[NAME](select[C1<>C2](student join rename[SID,C1,G1](enroll) join rename[SID,C2,G2](enroll)));

  4. Get names of students who have an "A" grade in CSc2510.

    project[NAME](student join select[CNO='CSc2510' and GRADE='A'](enroll));

  5. Get names of students who have have not enrolled in any class.

    project[NAME](student join (project[SID](student) minus project[SID](enroll)));

  6. Get names of students who have have enrolled in CSc2310 or CSc 2510.

    project[NAME](student join (project[SID](select[CNO='CSc2310'](enroll)) union project[SID](select[CNO='CSc2310'](enroll)) ) );

To see the contents of tables, you can enter the name of the relation followed by a semi-colon.

Here is a screen capture of running the above queries on the system: 

[~][9:03pm] java edu.gsu.cs.ra.RA ~raj/enrollDB
RA> student;
STUDENT(SID:INTEGER,NAME:VARCHAR)

Number of tuples = 5
1111:Jones:
2222:Smith:
3333:Blake:
4444:Damon:
5555:Bell:

RA> course;
COURSE(CNO:VARCHAR,TITLE:VARCHAR)

Number of tuples = 3
CSc2310:Java:
CSc2510:Theory:
CSc4710:Database:

RA> enroll;
ENROLL(SID:INTEGER,CNO:VARCHAR,GRADE:VARCHAR)

Number of tuples = 7
1111:CSc2310:F:
1111:CSc2510:F:
2222:CSc2510:A:
2222:CSc4710:B:
3333:CSc2310:A:
3333:CSc2510:A:
3333:CSc4710:F:

RA> project[NAME](student join select[CNO='CSc2510'](enroll));
temp2(NAME:VARCHAR)

Number of tuples = 3
Jones:
Smith:
Blake:

RA> project[CNO,TITLE](select[NAME='Smith'](student) join course join enroll);
temp3(CNO:VARCHAR,TITLE:VARCHAR)

Number of tuples = 2
CSc2510:Theory:
CSc4710:Database:

RA> project[NAME](select[C1<>C2](student join rename[SID,C1,G1](enroll) join rename[SID,C2,G2](enroll)));
temp5(NAME:VARCHAR)

Number of tuples = 3
Jones:
Smith:
Blake:

RA> project[NAME](student join select[CNO='CSc2510' and GRADE='A'](enroll));
temp2(NAME:VARCHAR)

Number of tuples = 2
Smith:
Blake:

RA> project[NAME](student join (project[SID](student) minus project[SID](enroll)));

temp4(NAME:VARCHAR)

Number of tuples = 2
Damon:
Bell:

RA> project[NAME](student join
RA> (project[SID](select[CNO='CSc2310'](enroll))  union 
RA>  project[SID](select[CNO='CSc2510'](enroll)) ));

temp6(NAME:VARCHAR)

Number of tuples = 3
Jones:
Smith:
Blake:

RA> exit;
[~][9:04pm]