Leveraging Synergy Between Database and Programming Language Courses

Brian Howard
DePauw University

This work was supported by the 2008–11 Donald E. Town Faculty Fellowship from DePauw University.

Motivation and Overview

Syntax-Directed SQL Translation

Grammar for a subset of SQL

Translation Functions

Example Translation

What is the difference between a HAVING and a WHERE condition when there is no GROUP BY?

Comprehension Syntax

Generalized for loop, based on set builder notation

Scala Example

val mentorPairs = for {
  mentor <- students
  other <- students
  if mentor.year < other.year &&
     mentor.major == other.major
} yield (mentor, other)

This is equivalent to

val mentors = students.flatMap(mentor =>
  students.withFilter(other =>
    mentor.year < other.year &&
    mentor.major == other.major
  ).map(other =>
    (mentor, other)
  )
)

C# LINQ Equivalent

var mentors =
  from mentor in students
  from other in students
  where mentor.year < other.year
     && mentor.major == other.major
  select new {a = mentor, b = other};

SQL Equivalent

SELECT mentor.ID as a, other.ID as b
FROM Student mentor, Student other
WHERE mentor.Year < other.Year
  AND mentor.Major = other.Major;

Object-Relational Mapping

Java Database Connectivity (JDBC)

List mentors = new ArrayList();
Statement statement = connection.createStatement();
String query =
  "SELECT mentor.ID as a, other.ID as b " +
  "FROM Student mentor, Student other " +
  "WHERE mentor.Year < other.Year " +
  "  AND mentor.Major = other.Major;";
  
ResultSet results = statement.executeQuery(query);
while (results.next()) {
  String mentorID = results.getString("a");
  String otherID = results.getString("b");
  mentors.add(new MIDPair(mentorID, otherID));
}
results.close();

Java Persistence API (JPA)

@Entity
@Table(name="Student")
public class Student {
  @Id @Column(name="ID")
  private String id; // Primary key
  
  @Column(name="Year")
  private int year;
  
  @ManyToOne @JoinColumn(name="Major")
  private Department major; // Foreign key
  
  // usual constructors, accessors, etc. go here
}

Java Persistence Query Language (JPQL)

List mentors = new ArrayList();
String queryString =
  "select mentor, other " +
  "from Student mentor, Student other " +
  "where mentor.year < other.year " +
  "  and mentor.major = other.major";
  
Query query = entityMgr.createQuery(queryString);
for (Object result : query.getResultList()) {
  Object[] pair = (Object[]) result;
  Student mentor = (Student) pair[0];
  Student other = (Student) pair[1];
  mentors.add(new MPair(mentor, other));
}

C# LINQ to Entities

var context = ...;
var query =
  from mentor in context.students
  from other in context.students
  where mentor.year < other.year
     && mentor.major == other.major
  select new {a = mentor, b = other};
var mentors = query.ToList();

Transactional Memory

class Fork { val inUse = Ref(false) }

def meal(left: Fork, right: Fork) {
  // thinking

  atomic { implicit txn =>
    if (left.inUse() || right.inUse())
      retry // forks are not both ready, wait
    left.inUse() = true
    right.inUse() = true
  }

  // eating

  atomic { implicit txn =>
    left.inUse() = false
    right.inUse() = false
  }
}

Example from ScalaSTM library documentation

Document-Oriented Databases

JavaScript Object Notation (JSON)

{
  "ID": "12-34567",
  "Name": "Ann O'Nemus",
  "Year": 2015,
  "Major": "Computer Science",
  "Home Address": {
    "Street": "123 Main",
    "City": "Springfield",
    "State": "AK",
    "ZIP": 98765
  },
  "Phones": [
    {"Type": "Home", "Number": "555-555-1234"},
    {"Type": "Cell", "Number": "555-555-5678"}
  ]
}

MapReduce

Example in MongoDB: count number of students per major/year

var map = function() {
  emit({"Major": this.Major,
        "Year": this.Year}, 1)
}

var reduce = function(key, values) {
  var total = 0;
  for (index in values) total += values[index];
  return total;
}

db.runCommand({
  "mapreduce": "students", // source collection
  "map": map,
  "reduce": reduce,
  "out": "graduates" // output collection
})
← →

/