Relational Databases and Querying

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 predicate logic

A query using predicate logic is of the form

{ X1,...,Xn | P(X1,...,Xn) }

where P(X1,...,Xn) is a wff in predicate logic with free variables X1,...,Xn.

Query Examples

Note: We will use a slightly different syntax for well-formed formulas to be able to use the Query system on the server. 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.drc.DRC ~raj/enrollDB
You will see a DRC> 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.

    { N | (exists S,G)(student(S,N) and enroll(S,'CSc2510',G)) }

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

    { C,T | (exists S,G)(course(C,T) and enroll(S,C,G) and student(S,'Smith')) }

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

    { N | (exists S,C1,G1,C2,G2)(student(S,N) and enroll(S,C1,G1) and enroll(S,C2,G2) and C1 <> C2) }

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

    { N | (exists S)(student(S,N) and enroll(S,'CSc2510','A')) }

To see the contents of tables, you can run the following queries:
  1. Display content of student table:

    {S,N | student(S,N) }

  2. Display content of course table:

    {C,T | course(C,T) }

  3. Display content of student table:

    {S,C,G | enroll(S,C,G) }

Here is a screen capture of running the above queries on the system: 
[~][6:12am] java edu.gsu.cs.drc.DRC ~raj/enrollDB
DRC> {S,N | student(S,N) }
ANSWER(S:INTEGER,N:VARCHAR)

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

DRC> {C,T | course(C,T) }
ANSWER(C:VARCHAR,T:VARCHAR)

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

DRC> {S,C,G | enroll(S,C,G) }
ANSWER(S:INTEGER,C:VARCHAR,G: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:

DRC> { N | (exists S,G)(student(S,N) and enroll(S,'CSc2510',G)) }
ANSWER(N:VARCHAR)

Number of tuples = 3
Jones:
Smith:
Blake:

DRC> { C,T | (exists S,G)(course(C,T) and enroll(S,C,G) and student(S,'Smith')) }
ANSWER(C:VARCHAR,T:VARCHAR)

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

DRC> { N | (exists S,C1,G1,C2,G2)(student(S,N) and enroll(S,C1,G1) and enroll(S,C2 ,G2) and C1 <> C2) } 
ANSWER(N:VARCHAR)

Number of tuples = 3
Jones:
Smith:
Blake:

DRC> { N | (exists S)(student(S,N) and enroll(S,'CSc2510','A')) }
ANSWER(N:VARCHAR)

Number of tuples = 2
Smith:
Blake:

DRC> exit;
[~][6:14am]