Database Applications Using JDBC and SQL

This module introduces the Java Database Connectivity (JDBC) API that can be used on any tupe of RDBMS that provides a connector implementation. It covers the JDBC APIs, and how to use logic developed in both Java and SQL to implement appliations

Ths module makes use of the Apache Derby RDBMS, which provides a full-featured system that can be embedded in a single application, or run as a stand-alone server. Derby implements nearly all the features of SQL 11 and is useful for small- to medium-scale projects, and is widely used where a stand-alone application uses an RDBMS to store its data. One of the main benefits of Derby is the ease of installation and configuration.

Database Design and Normalization

In this lecture, we will look at the Java Database Connectivity (JDBC) API and how to perform common operations, including data defintion (DDL) and data manipulaton (DML). The example used is the Wikipedia database that includes a Employee table and a Dept table.

Readings

Class presentation

Tutorial readings

Keys and Constraints

In this lecture, we will look at one way to populate the publication database introduced in Lecture 5 and Assignment 3 by reading a data file and using JDBC insert statements. We will also see how to verify the data using SQL queries, and how to view the database using the Eclise and the Data Tools platform.

Next we will learn about how SQL enables us to specify various kinds of relational integrity constraints, which we learned about in Lecture 3, including

  • key constraints to identify fields that act as keys for a table based on the the ER diagram for the data model,
  • domain constraints to identify constraints on the value of attributes in a table, and
  • referential integrity constraints that enable the database to maintain constraints among key values across database tables

Finally, we will see how the application of constraints in a database can simplify initializing and updating the database by eliminating the need for applications that insert and modify data to implement these constraints within the application logic.

Readings

Class presentation

Tutorial readings

Additional readings

Foreign Key Constraints, Triggers and Stored Procedures

Foreign key constraints enable the database to perform simple actions as table rows are added, altered and removed. Foreign key constraints are useful for ensuring that tables involving the foreign keys remain consistent as key values are changed or rows in tables with the primary key are deleted.

Triggers are a conditional mechanism that enable some action to be taken when a certain condition or set of conditions to occur. Triggers can be used to enforce integrity constraints. For example, if a row in a table is removed, then a dependent row in another table should also be removed. Triggers can happen either before or after the even occurs, allowing either a pre-condition or a post-condition to be enforced.

The final conditional mechanism we will study is the stored procedure, which allows new functions to be defined and stored in the database that can be called while executing queries. We will look at how to create and use stored procedures within Derby using the Java programming language.

Readings

Class presentation

Tutorial readings

Apache Derby

Stored Procedures

The final conditional mechanism we will study is the stored procedure, which allows new functions to be defined and stored in the database that can be called while executing queries. We will look at how to create and use stored procedures within Derby using the Java programming language.

Readings

Class presentation

Tutorial readings

Apache Derby MySQL/MariaDB

Transactions

A transaction is another conditional mechanism tha tensures that a set of operations occurs as a single operation, and that either all the operations in the transaction occurs, or all the operations are rolled back as though they never occured. Changes within a transaction are visible while the transaction is in progress, but those changes are not visible outside the transaction until the transaction commits its results. We will look at several practical ways to use the transaction managmement facilities of a database.

Readings

Class presentation

Tutorial readings

  • DUE DATE: Friday May 24 by 11:59:59pm

Create a database 'publication' and a set of tables that represent the entities Journal, Publisher, Article, and Author, and the associations among them as shown and described below. Create a JDBC Java class Assignment3 whose main() method creates a Derby database 'publication' and creates the tables in the database.

For convenience, include code to drop the tables initially if they exist and recreate them each time the program is run. You need not specify constraints or indexes for this assignment. We will cover these in class during Lecture 6.

Entities

Publisher

  • Name: a string of upto 32 characters
  • City: a string of upto 16 characters

Journal

  • Title: a string of upto 32 characters
  • ISSN: An 4-byte int representation of the journal International Standard Serial Number (ISSN) that uses a hexadecimal encoding: 1234-467X -> 0x1234567a. Note that SQL int representation is signed, so ISSNs between 0000-0001 and 7999-999X will be interpreted as positive numbers, while ISSNs between 8000-0000 and 9999-999X will be interpreted as negative numbers.

Article

  • Title: a string of upto 32 characters
  • DOI: A Digital Object Identifier (DOI) string that identifies the article. Although there is no restriction on length, this schema allows 64 characters.

Author

  • GivenName: The author's given (first) name as a string of 16 characters.
  • FamilyName: The author's family (last) name as a string of 16 characters.
  • ORCID: A ORCID persistent author identifier of 16 decimal digits represented as a numerical BigInt in the database schema and a long in Java.

Relationships

PublishedBy

A one-to-many relationship between Publisher and Journal. A publisher has one or more journals, but each journal has only one publisher.

PublishedIn

A one-to-many relationship between Journal and Article. An article appears in just one journal, but a journal has many articles.

WrittenBy

A many-to-many relationship between Article and Author. An article has at least one author, and an author has written one or more articles.

  • DUE DATE: Wednesday, May 29 by 11:59:59pm

Modify the tables you developed for Assignment 3 to add the following constraints that were presented in Lecture 6. You are also welcome to use either version of the instructor's solution as a starting point if you would rather. You will also need to modify the appropriate version of the instructor's test program shown in during Lecture 6. They are checked in to GitHub in the repository "assignment-3-pgust".

  • Add key constraints by declaring appropriate primary keys for all entity and relation tables. Remember that the primary key may include multiple columns.
  • Add referential integrity constraints by declaring foreign key constraints among rows in related entity and relation tables.
  • Add domain constraints to ensure that all field are initialized with values for each new row in every table.
  • Apply domain constraints to the Journal ISSN field and the Author ORCID fields to ensure that both fields are positive, and that each field is limited to the number of digits shown in the ER diagrams for those entities.
  • Modify the appropriate test program to eliminate the queries that check for the existance of a row before inserting a new one, relying instead on the uniqueness constraints imposed on the primary key fields.
  • DUE DATE: Monday, June 3 by 11:59:59pm

Foreign Key Constraints and Triggers

Modify the tables you developed for Assignment 4 to add foreign key constraints and triggers that ensure the following takes place:

  • When an Article is deleted, ensure that the entry in the PublishedIn relation table (if your schema has one) is also deleted.
  • If the last article by an Author is deleted, ensure that the Author entry and its entry in the WrittenBy relation is also deleted.
  • If a Journal is deleted, ensure that the entry in the PublishedBy relation table (if your schema has one) is also deleted.
  • Also ensure that all the Articles for that Journal are also deleted.
  • If a Publisher is deleted, ensure that the entry in the PublishedBy relation table (if your schema has one) is also deleted
  • Also ensure that all Journals for that Publisher are also deleted.
  • DUE DATE: Friday, June 7 by 11:59:59pm

Stored Functions

The bibliographic utility class Biblio includes static functions for validating, parsing and converting bibliographic types to strings. These include issn, orcid, and doi.

Your asssignment is to enhance either your solution to assignment 5 or the instructor's to use these functions.

  1. In your Assignment6 or Assignment6a class, create stored functions for all of the functions in Biblo.
  2. Add or replace the constraint checks for issn, orcid, and doi values in the schema definitions with calls to the respective predicate functions.
  3. In your TestAssignment6 or TestAssignment6a class, before the code that adds values to the database, create tests that invoke each of the stored functions using a values query (e.g. "values parseIssn('1234-567X')") with representative values from the data file, as well as with invalid values.
  4. Modify the INSERT prepared statements to expect a string representation of the issn and orcid, and to call the appropriate parse stored function on the values. For example, for journal, use "values (?, parseIssn(?))" instead of "values (?, ?)".
  5. Modify the code that uses these prepared statements to use string parameters for issn and orcid, and to pass the strings from the data files rather than the parsed values. The modified INSERT statements will take care of parsing the values using the parse functions you specified in the previous step.