Relational Databases

A relational database is a system for storing and accessing data organized into relations.

A relation is a bag of tuples. Each tuple is an ordered sequence of attributes. Each attribute is a data value belonging to some data type. All of the tuples in a relation have the same number of attributes. In addition, the relation has a schema that is imposed on each tuple in the relation, specifying what the data type each attribute in each tuple will have. For example, the relation’s schema might specify that the first attribute in each tuple is an integer.

The relation’s schema also gives a name to each attribute. The attribute names give us a convenient way of referring to tuple attributes without having to say “the first attribute”, “the fourth attribute”, etc.

Typical relational databases support numeric and text data types as tuple attributes. Many relational databases also support “BLOBs” (Binary Large OBjects) as attributes. A BLOB is a large, uninterpreted chunk of data. BLOBs are useful for storing files, images, and other large chunks of data in a relational database.

Example relation

Let’s say we are going to use a relational database to store information about books. We might define a relation called books to store information about each book:

author_lastname	author_firstname	title	ISBN
Smallfinger	F.G.	A History of Hats	0-651-65165-4
Whittlbey	W.H.J.	Guide to Impossible Buildings	82-234-5475-0
Earwig	Lettice	First Flights in Witchcraft	5-9672-6521-X
Lightly	W.E.	Habits of the Wolves	91-33-65168-X
Tacticus	Callus	Sieges and Survival	99921-58-10-7
Tacticus	Callus	VENI VIDI VICI: A Soldier’s Life	84-15978-99-5

In this relation, there are four attributes called author_lastname, author_firstname, title, and ISBN. Each attribute is a text string.

Databases with multiple relations

Databases will typically have many relations. One motivation for allowing multiple relations in a database is to avoid storing redundant information. For example, in the relation above, there are two tuples representing books by the same author, Callus Tacticus. Because the author name is represented twice, there is the possibility that this information might not be recorded consistently if the relation were modified.

We can avoid this redundancy by splitting the database into two relations, books and authors:

The books relation:

book_id	author_id	title	ISBN
1	1	A History of Hats	0-651-65165-4
2	2	Guide to Impossible Buildings	82-234-5475-0
3	3	First Flights in Witchcraft	5-9672-6521-X
4	4	Habits of the Wolves	91-33-65168-X
5	5	Sieges and Survival	99921-58-10-7
6	5	VENI VIDI VICI: A Soldier’s Life	84-15978-99-5

The authors relation:

author_id	author_lastname	author_firstname
1	Smallfinger	F.G.
2	Whittlbey	W.H.J.
3	Earwig	Lettice
4	Lightly	W.E.
5	Tacticus	Callus

The books relation has been changed so that the author of each book is represented by a unique integer identifier, the author_id attribute. This attribute also exists in the authors relation. So, the author of each book tuple in the books relation is represented indirectly, by reference to a matching author tuple in the authors relation.

Queries, SQL

A query is a request to retrieve information from a database.

SQL, the Structured Query Language, is a standard language for describing queries in relational databases. SQL is an interesting language because it is declarative: it describes what information is desired, but does not specify how that information is to be retrieved. It is the job of the database to figure out how to find the information requested by a query.

Example: let’s say we want to find the titles of all books written by F.G. Smallfinger. In our original database, in which only the books relation exists, we could express that query as follows:

select title
    from books
    where author_lastname = 'Smallfinger' and author_firstname = 'F.G.'

A SQL select statement specifies a query, and has three parts:

• Which attribute values to retrieve. In the example above, only the title attribute is requested.
• Which relations are involved in the query. In the example above, only the books relation is queried.
• A condition describing which tuples contain the desired information. In the example above, the condition requires that tuples were author_lastname = ‘Smallfinger’ and author_firstname = ‘F.G.’ are desired.

This query will match a single tuple in the books relation, and return a single title value, A History of Hats.

Joins

A join is a query which retrieves information from multiple relations. Joins are a powerful way to exploint associations between tuples in different relations. The idea is that a query retrieving information from multiple relations will specify a join condition which links attribute values in tuples of two relations.

Let’s consider how to find the titles of all books by F.G. Smallfinger in the second version of the database, where we have two relations, books and authors. We will need to do a join of both relations in order to connect the author name and book title, which are now stored in different relations:

select books.title
    from books, authors
    where books.author_id = authors.author_id
        and authors.author_lastname = 'Smallfinger' and authors.author_firstname = 'F.G.'

Note several interesting details of this query:

• We are selecting books.title as the attribute to retrieve, explicitly noting that this attribute exists in the books relation
• The from clause now specifies two relations, books and authors
• The first part of the where clause, books.author_id = authors.author_id, is the join condition. It states that when considering pairs of tuples in the books and authors relations as candidates for retrieval, each tuple must contain the same value for the respective author_id attributes.
• In the where clause, each attribute is qualified by the name of the relation it is a part of. This is especially important when two relations have identically-named attributes, as is the case with the author_id attributes of the books and authors relations.

Indices

Obviously, a database system must be able to answer all queries by returning the requested information.

An important additional characteristic that database systems should have is that queries are answered efficiently, even if the database contains a large amount of data.

An index is an auxiliary data structure associated with a relation to increase the efficiency of queries on that relation. Specifically, an index is an auxiliary data structure which, given an attribute value or range of attribute values, quickly locates the tuple or tuples which match that value or range. An index may be applied to a single attribute, or to multiple attributes.

The idea is that an index helps the database focus the search for data matching a query by narrowing the number of tuples that must be checked.

Sequential scans

Consider queries involving a single relation. Any such query can be answered by performing a sequential scan over the tuples in the relation: checking each tuple to see if it matches the condition(s) specified in the where clause, and if so, returning the values of the selected attributes.

For very large relations, a sequential scan may do much more work than necessary. In particular, the where clause may have sub-conditions that are met by only a very small number of tuples: we say that a condition matched by only a small number of tuples has high selectivity.

To answer queries as efficiently as possible, the database should limit its scan to as small a subset of tuples as possible. This can be done using an index.

For example: let’s say that in the book database, we will frequently need to search for authors by last name. To make those queries more efficient, we can build an index on the author_lastname attribute. Queries such as

select author_id from authors where author_lastname = 'Smith';

can be answered efficiently because the index on author_lastname will allow the database to ignore tuples in which the value of that attribute is not ‘Smith’.

JDBC

Another very useful way to interact with a database is from a program that you write. Programs which communicate with a database are often referred to as database applications.

JDBC is the standard Java API for writing database applications. It provides a common set of functions for accessing and modifying data in SQL databases.

Here are some of the basic JDBC concepts you will employ.

Drivers

A JDBC driver is a code library which implements the JDBC API for a particular back-end database. Most popular relational databases include a JDBC driver.

Generally, to add support for a particular database implementation to your application, you will include its JDBC driver in your project. Your application will need to load the driver by instantiating its main class.

Connections

A JDBC connection is an object that allows your application to communicate with a particular instance of the database.

Connections are specified as URL strings. The URL string encodes both the driver to be used and also the location of the database.

The JDBC DriverManager class creates connection objects.

PreparedStatement

A prepared statement object represents a particular query or insert/update/delete statement to be executed.

A prepared statement may contain placeholders for information that will be “filled in” when the statement is executed. This allows the same statement to be executed many times, supplying different data each time.

For example: here is a query that searches for books matching a particular title:

select authors.author_lastname, authors.author_firstname, books.title, books.isbn
    from authors, books
    where authors.author_id = books.author_id
          and books.title = ?

The question mark (?) is a placeholder for a particular book title. So, this query is a general “template” for finding books given a title. If we make this query a prepared statement, we can use it as many times as desired to find books matching various titles. Each time we execute the prepared statement, we will substitute a specific title for the placeholder.

ResultSet

A result set is returned as the result of executing a prepared statement. The result set is essentially an iterator for the tuples returned as the result of the query.

The data types of the returned attribute values are mapped onto Java data types. Typical mappings include:

SQL type	Java type
VARCHAR	java.lang.String
INTEGER	java.lang.Integer
DATE	java.sql.Date