Table of Contents
Database access is a very important feature for almost any application in these times. Java provides database access in a very portable way, using JDBC (Java DataBase Connectivity).
Relational Databases
Relational databases implement the Relational Model formulated by E.F. Codd in his seminal paper “A Relational Model of Data for Large Shared Data Banks”, Communications of the ACM, 1970.
The Relational Model
The major features of the Relational Model are:
- Data is represented as a set of relations, or tables,
- How data is stored in a database is independent of the relationships between the data. (the logical schema and the storage schema are independent of each other)
- The model provides a mathematical basis for operations on the data.
Relational Terminology
Here is some terminology commonly used in relational databases:
- Table (relation) : The basic logical organizational unit for data.
Data in a table is organized in:- Rows (sometimes known as tuples)
- Columns (sometimes known as attributes), which have names.
For example, imagine a database which stores information about employees for a company.
An Employees table might look like the following:
| EmployeeID | Name | Department | Age | Salary |
|---|---|---|---|---|
| 10063 | Alfred J Prufrock | 10 | 45 | 120000 |
| 204567 | Angela Parfitt | 30 | 36 | 78000 |
| 345890 | Michael Solistes | 20 | 28 | 90000 |
| 89567 | Charles Schultz | 20 | 56 | 230000 |
| 56435 | Barbara Smith | 30 | 48 | 134000 |
| … | ||||
| … | ||||
| … |
and a Departments table might look like:
| DepartmentID | Name | Manager |
|---|---|---|
| 10 | Accounting | 10063 |
| 20 | Sales | 89567 |
| 30 | Information Technology | 56435 |
| … | ||
| … | ||
| … |
Note that, in the above example, there are two columns which refer to rows in the other table:
- The Department column in the Employees table contains DepartmentIDs to refer to the appropriate row in the Departments table.
- The Manager column in the Departments table contains EmployeeIDs to refer to the appropriate row in the Employees table.
This is done in the relational model to avoid duplication of data, and to improve the data structure/organization.
Relational Data Types
Each column in a relational database table has a data type. The Relational Model emphasizes that each column’s data type must be atomic; that is, a data type is indivisible and must be treated as a unit. Some common datatypes are:
- INTEGER
- REAL
- CHARACTER(n)
- CHARACTER VARYING(n)
Most relational databases today provide more complex types such as Binary Large Objects (BLOBs) and Character Large Objects (CLOBs), which may in fact be considered divisble by the user (although perhaps not by the database itself).
The value of a particular data type can:
- Have a value of that data type, or:
- Be NULL, which indicates no value, or unknown value.
Relational Operations
The Relational Model defines a number of operations that may be performed on tables, on rows, and on individual data elements.
Selection
The most important operation is selection. This is the operation of selecting one or more rows from a table which satisfy one or more predicates. A predicate is a statement about the truth of something (for example, whether the employee ID is equal to 1234, or whether his/her salary is higher than $20000.) The rows in the table are filtered on the basis of whether they satisfy the predicate(s).
Join
A common relational operation is that of a join. A join involves one or more tables, and involves combining rows and columns from those tables into a new set of data which also forms a (temporary) table with a new set of columns and rows. For example, if you wished to list all the departments and their managers in the above employees example, you would have to perform a combination of a join operation and a selection operation to come up with a table that looks something like:
| Department | Manager |
|---|---|
| Accounting | Alfred J Prufrock |
| Sales | Charles Schultz |
| Information Technology | Barbara Smith |
| … | |
| … |
Union
It sometimes happens that two or more tables have the same structure of columns and data types. If you wished to produce a report that combined the data from the two tables, you would perform a union operation to accomplish this. For example, you might have a Customers table which could contain similar information to the Employees table (except of course for the Employee ID and Department columns). You could use a combination of selection, join and union to come up with a table that contains rows of data from both the Employees and the Customers tables.
Closure
A very important aspect of the relational model is the concept of closure, which says that the result of any operation on one or more relations is also a relation.
The Transaction Model
Most relational databases support the concept of a transaction. A transaction is a unit of work performed against a database which must either completely succeed or completely not succeed. For example, imagine that you work for a bank, and you wish to transfer money from one account to another account. To accomplish this, you would have to perform the following steps in the following sequence:
- Withdraw the desired amount from the first account
- Deposit that amount into the second account
But what if something went wrong after step 1) that prevented you from completing step 2)? Without the concept of a transaction, you would likely lose track of the money withdrawn, and the banks books would no longer balance. If you perform the two steps within a transaction, the database takes care to ensure that one of the following occurs:
- (Eventually) both actions are completed,
or:
- (Eventually) neither action is completed.
Commit vs Rollback
When a transaction is ready to complete all its operations, it asks the system to commit those actions.
When a transaction discovers difficulty with performing its actions, and decides that all those actions cannot be performed, then it asks the system to rollback any actions that it has (provisionally) performed.
The ACID Properties
It is generally agreed that a database transaction must have the following “ACID” properties:
- Atomic: A transaction should either complete all its actions, or none of them should be performed. (For example, a money transfer is guaranteed either to completely succeed, or to have no effects on the system.)
- Consistent: A transaction should only perform correct transformations between valid system states (For example, An employee should always have an employee ID and an associated department; A department should always have a Manager assigned, etc.)
- Isolated: While a transaction is performing changes in a system’s state, the data may at various points be inconsistent. Such inconsistency should not be visible to other transactions. The system must give a transation the illusion that it is running in isolation.
- Durable: Once a transaction has committed, the changes it has made to the database must be preserved, even if the system crashes (due to either hardware or software failure).
Structured Query Language (SQL)
The most common language used with relational databases is Structured Query Language (SQL, often pronounced “Sequel”), which was invented by IBM in the 1970s. It became an ANSI (Americal National Standards Institute) standard in 1986 (SQL-86), and this was followed by an updated standard in 1989 (SQL-89). In 1992, ANSI and ISO (International Standards Organization) published an updated standard for SQL, called SQL-92.
SQL is rather an unwieldy language, and it is (especially as specified in more recent standards) an extremely large language. Unfortunately, despite the standardization efforts, and because of the dominance of a small number of database vendors who have proprietary implementations of the SQL language, with their own extensions and quirks, a true, practical, SQL standard is still not a reality.
SQL is a Set-Oriented Language
It is important to realize that SQL is a set-oriented language. This means that most operations performed using SQL statements do not operate on a single row in a table, but instead operate on multiple rows in a table.
Categories of SQL Statements
SQL statements may be categorized into the following groups:
- Data Manipulation Language (DML) statements:
- SELECT statements
- INSERT, DELETE and UPDATE statements
- Data Definition Language (DDL) statements:
- CREATE … statements
- ALTER… statements
- GRANT statements
- Transaction Management statements
- SET TRANSACTION statements
- COMMIT statements
- ROLLBACK statements
References
SQL is a very complex language, and I cannot hope to give it more than introductory coverage here. There are quite a few books that do a much more comprehensive job than I can of describing the SQL language, including the following:
- Understanding the New SQL : A Complete Guide (The Morgan Kaufmann Series in Data Management Systems) by Jim Melton & Alan R. Simon (March 1993) Morgan Kaufmann Publishers; ISBN: 1558602453
- Understanding the New SQL : A Complete Guide by Jim Melton & Alan Simon 2nd edition (to be published December 2000) Morgan Kaufmann Publishers; ISBN: 1558604561
- A Guide to the SQL Standard : A User’s Guide to the Standard Database Language SQL by Hugh Darwen & C. J. Date, 4th edition (April 1997) Addison-Wesley Pub Co; ISBN: 0201964260
For the classic book on databases, take a look at:
- An Introduction to Database Systems by C. J. Date 7th edition (October 1999) Addison-Wesley Pub Co; ISBN: 0201385902
(but note that it’s not exactly bedtime reading…)
DML Statements
DML stands for Data Manipulation Language
Categories of DML Statements
Here is a summary of the categories of SQL statements covered in this section:
- If you wish to read data from tables, you use a SELECT statement
- To enter one or more new rows of data into a table, you use an INSERT statement
- To remove one or more rows of data from a table, you use a DELETE statement
- To change one or more rows of data in a table, you use an UPDATE statement
SELECT Statements
The most commonly used statement in SQL is the SELECT statement. It is used to read data from one or more tables.
SELECT Statements vs Query Expressions
There are actually two contexts in which a SELECT may be executed:
- To initiate a SELECT statement
- To define or return a table-valued expression (often called a query expression)
The difference is that a table-valued expression has no concept of ordering, while a SELECT statement may return rows in a specified order (specified in an ORDER BY clause). If no ORDER BY clause is specified, then the results of a SELECT statement may be returned in any order.
Simple SELECT Statements
Here is an example of the simplest kind of SELECT statement:
SELECT name FROM employees;
which, when given the table Employees:
| Employee ID | Name | Department | Age | Salary |
|---|---|---|---|---|
| 10063 | Alfred J Prufrock | 10 | 45 | 120000 |
| 204567 | Angela Parfitt | 30 | 36 | 78000 |
| 345890 | Michael Solistes | 20 | 28 | 90000 |
| 89567 | Charles Schultz | 20 | 56 | 230000 |
| 56435 | Barbara Smith | 30 | 48 | 134000 |
| … | ||||
| … | ||||
| … |
would give the results:
Alfred J Prufrock Angela Parfitt Michael Solistes Charles Schultz Barbara Smith ...
Note that the result of this SELECT statement is a table-valued expression, which conforms to the same rules as a table, so I could represent this as a table.
Note: SQL is a language case insensitive language. Thus, SELECT, select, Select, and seLect are all equivalent. However, for the purposes of distinguishing between SQL keywords and user-defined names, it is often useful to use a convention. Here, I use the convention that SQL keywords are uppercased, while user-defined names are lowercased.
Specifying Multiple Columns
We could form a slightly more complex SELECT statement by returning more than one column from the table:
SELECT name, age, salary FROM employees;
which produces the following table-valued expression:
| Name | Age | Salary |
|---|---|---|
| Alfred J Prufrock | 45 | 120000 |
| Angela Parfitt | 36 | 78000 |
| Michael Solistes | 28 | 90000 |
| Charles Schultz | 56 | 230000 |
| Barbara Smith | 48 | 134000 |
| … | ||
| … | ||
| … |
Specifying the Order of Rows Returned by a SELECT Statement
If we are executing a SELECT statement (as opposed to a query expression), we may specify the order in which the rows are returned:
SELECT name, age, salary FROM employees ORDER BY salary;
this will result in the same set of rows being returned as in the above example, except that the order in which those rows are returned is based on the values in the row’s Salary column.
Note that it is possible to specify the order even if the ordered column is not returned:
SELECT name, age FROM employees ORDER BY salary;
The WHERE clause
If you wish to filter the results of a SELECT statement by some criteria (called predicates), you can specify those predicates in a WHERE clause:
SELECT name, age, salary FROM employees WHERE salary > 100000;
You may use a WHERE clause in a SELECT statement and also in a query expression.
In a SELECT statement, you can combine the WHERE and ORDER BY clauses:
SELECT name, age, salary FROM employees WHERE salary > 100000 ORDER BY salary;
Using Multiple Predicates
You may combine predicates in a WHERE clause using AND, OR and NOT operators:
SELECT name, age, salary FROM employees WHERE salary > 100000 AND age < 50;
Performing Joins
If you wish to perform a join across one or more tables, you can list more than one table in the FROM clause:
SELECT employees.name, departments.name, age, salary FROM employees, departments;
(Note that because both the Employees table and Departments table have a column called Name, we have to explicitly specify which one we want by explicit qualification.)
However, if you do not use a WHERE clause, this produces a table-valued expression with a very large cardinality (row count), because it matches every row in the employees table with every row in the departments table. So, if you have, say, 1000 employees and a 100 departments, this SELECT would return 1000 * 100 = 100,000 rows. (This is called the cartesian product of the two tables.) Rarely is this useful, and it consumes large amounts of resources to compute the result!
Instead, when you use multiple tables in a SELECT, you almost always should also use a WHERE clause to restrict the number of rows returned.
For example, if you want to list all employees names, followed by the name of their department, followed by their age and salary, you would use the following SELECT statement:
SELECT employees.name, departments.name, age, salary FROM employees, departments WHERE department = departmentID;
This kind of join is called a natural equi-join, because it selects rows from the two tables that have equal values in the relevant columns.
Other Kinds of Joins
There are various other kinds of joins that can be accomplished using a SELECT statement, such as outer joins and unions, but these are more advanced features that we won’t cover in this introduction.
Take a look at the references noted earlier for more details.
INSERT Statements
If you wish to enter new rows of data into a table, you use an INSERT statement. For example:
INSERT INTO employees (employeeID, name, department, age, salary) VALUES (234134, 'Frodo Benitas', 20, 23, 34000);
will enter a new employee into the Employees table. In this example, each column name is specified explicitly by name in the first list. However, if you wish to enter a value for every column, and rely on the “natural order” of the columns in the table, you may omit the first list entirely:
INSERT INTO employees VALUES (234134, 'Frodo Benitas', 20, 23, 34000);
However, this requires you to know the order of the column names, and to supply a value for every column in the row. Sometimes, you don’t know the value for a column, and would prefer to leave it blank. If you do this:
INSERT INTO employees (employeeID, name, department, salary) VALUES (234134, 'Frodo Benitas', 20, 34000);
where the age was omitted, then a null value will be placed in the age column for that row. However, it would be clearer if you were explicit about the assignment of a null value:
INSERT INTO employees (employeeID, name, department, age, salary) VALUES (234134, 'Frodo Benitas', 20, NULL, 34000);
DELETE Statements
To remove one or more rows of data from a table, you use the DELETE statement. For example:
DELETE FROM employees WHERE name = 'Frodo Benitas';
will remove the row for Frodo Benitas from the Employees table. If there is more than one employee called Frodo Benitas, it will delete the others, also.
Beware! The statement:
DELETE FROM employees;
will cause all rows in the Employees table to be deleted!
UPDATE Statements
To change the data in one or more rows of a table, use the UPDATE statement. For example:
UPDATE employees
SET name = 'Fred Benitas',
age = 34
WHERE employeeID = 234134;
will change the name and age of an existing row in the Employees table. Presumably, the employeeID is unique; if it is not, then all employees with employeeID 234134 will have their name and age changed.
DDL Statements
DDL stands for Data Definition Language.
Schemas and Schema Objects
A database consists of a set of schemas, each of which contains, or owns, a set of schema objects, such as tables, etc.
Each schema object is created using a CREATE statement.
Creating Schema Objects
Each schema object typically has an associated CREATE statement which is used to create an instance of that object.
Creating a Table
To create a table, you use the CREATE TABLE statement. For example:
CREATE TABLE employees
{
employeeID INTEGER,
name CHARACTER(40),
department INTEGER,
age INTEGER,
salary DECIMAL(7,2)
};
which creates the Employees table specified earlier. Each column is specified using a name and a data type.
SQL Data Types
Here are some of the valid SQL data types:
| Data Type | Description |
|---|---|
| CHARACTER(n) | A character field containing exactly n characters |
| CHARACTER VARYING(n) | A character field containing up to n characters |
| INTEGER | An integer field |
| SMALLINT | An integer field of smaller size than INTEGER |
| NUMERIC(p, s) | A numeric field specifying precision p, and scale s |
| DECIMAL(p, s) | A decimal field specifying precision p, and scale s |
| REAL | A single precision floating point value |
| DOUBLE PRECISION | A double precision floating point value |
| FLOAT(p) | A floating point value with precision p |
| DATE | Year, month and day values of a date. |
| TIME | Hour, minute, second values of a time |
| TIMESTAMP | Year, month, day, hour, minute and second values |
Changing Schema Objects
Once a schema object has been created, it may be possible to change its attributes (depending on the type of object, and the support from the database vendor), using an ALTER statement.
Changing the Attributes of a Table
You can change some (usually not all) of the attributes of a table using the ALTER TABLE statement:
ALTER TABLE employees ADD COLUMN last_review DATE;
ALTER TABLE departments DROP COLUMN manager;
Removing Schema Objects
You can use a DROP statement to remove an existing schema object.
Removing a Table
You use the DROP TABLE statement to remove an existing table:
DROP TABLE employees;
Note: Do not confuse the DROP statement with the DELETE statement!
Transaction Statements
Transaction statements have to do with Transaction Management.
Starting a Transaction
In SQL, there is no explicit statement that starts a transaction. A transaction will be started implicitly (if one isn’t already started) whenever you execute a SQL statement that requires a transaction context. If you don’t tell the system differently, the transaction will have the following default characteristics:
- It will permit both read and write (update) operations
- It will have the maximum possible isolation from other concurrent transactions
If you want to change these characteristics, you must use the SET TRANSACTION statement to explicitly set them:
SET TRANSACTION READ ONLY, ISOLATION LEVEL READ UNCOMMITTED
The SET TRANSACTION statement must be the first statement executed in a transaction.
Committing a Transaction
Once all the operations in a transaction have successfully completed, you can commit those changes by using the COMMIT statement:
COMMIT;
Rolling Back a Transaction
If you decide that a transaction’s actions should not be (or cannot be) completed, you can roll those actions back by using the ROLLBACK statement:
ROLLBACK;
Mixing DML and DDL in a Transaction
Some database systems allow you to mix DML and DDL in a single transaction. For example:
- Update a row in the Employees table, then
- Create a new table, Customers, then
- Delete a row from the Departments table
- etc.
Other database systems to not allow you do mix the two. If you attempt to mix them, you may encounter errors, or you may find that a single DDL statement execution will cause the transaction to commit implicitly.
If you are writing code that should run against several database systems, you would be well advised to avoid such mixing of DML and DDL.
AutoCommit Mode
Some systems (such as ODBC and JDBC) have a mode called autocommit mode, which causes an implicit and automatic commit to occur after every SQL statement execution. For anything other than a trivial transaction, this is not an appropriate choice.