Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
Designing Your Schema and Creating Some Tables
The schema I’ll be using is simple and models a computer manufacturer. Listing 1 illustrates the Java code that creates the basic embedded schema. The PrincipalTable symbol is just a public String object with the value "ASSEMBLY_TABLE".
Listing 1 The embedded schema
/* Create two tables */
s.execute(
"CREATE TABLE " + PrincipalTable + "(" +
"MACHINE varchar(20) default ’’ NOT NULL PRIMARY KEY, " +
"VENDOR varchar(20), MODEL varchar(20), " +
"COMPONENT_ID int)");
s.execute(
"CREATE TABLE " + ComponentTable + "(" +
"COMPONENT_ID int, " +
"COMPONENT1 varchar(20), COMPONENT2 varchar(20), " +
"COMPONENT3 varchar(20), COMPONENT4 varchar(20))");
The object "s" in Listing 1 is instantiated by the following code:
Statement s = conn.createStatement();
The documentation that comes with the Derby distribution also contains plenty of information about getting going with Derby. Looking at Listing 1, you’ll notice that it’s just SQL code. Most database management system products include a console utility that allows for commands to be manually run.
This can be invaluable for situations where you need to quickly look at one or more tables in an operational database without having to use the application. Derby is no exception to this and also includes a console facility.
Remember that many products feature a database schema that exists as a standalone script file? Well, the SQL code in Listing 1 could follow the same pattern and be implemented as a script file. Then, when the script file had to be run, it could be piped into the console utility (the reference documentation includes examples of how to do this).
The fact that the SQL code in Listing 1 is implemented in Java illustrates what I mean by an embedded schema: the schema sits inside the application code rather than inside a script file.
The schema in Listing 1 is very simple. It consists of two tables, one called ASSEMBLY_TABLE, and the other called COMPONENT_TABLE. The first of these tables contains just four columns. The table ASSEMBLY_TABLE has the following columns:
- MACHINE varchar(20) default ’’ NOT NULL PRIMARY KEY
- VENDOR varchar(20)
- MODEL varchar(20)
- COMPONENT_ID int FOREIGN KEY
Notice the primary key called MACHINE that specifies a default value and the NOT NULL constraint. The latter is important when you want to ensure that a column always contains a valid value.
The problem with NULL is that NULL values require special handling in SQL queries (that is, NULL represents an unknown value). The difficulty is that NULL values are a little like the value of infinity in mathematics (infinity is not a number); similarly, NULL is never equal to NULL!
So, to avoid the problem, you can use the NOT NULL constraint because it means that the associated column cannot have a null value.
The second column in ASSEMBLY_TABLE is VENDOR, which is simply the name of the manufacturer.
The third column is MODEL, which describes the computer system model.
The fourth and the last column in ASSEMBLY_TABLE is COMPONENT_ID, which is an entity called a foreign key, which is simply a primary key from another table.
The COMPONENT_ID column allows for a row in the ASSEMBLY_TABLE table to include all the data in an associated table without any duplication (beyond the COMPONENT_ID column itself).
The table COMPONENT_TABLE has the following five columns:
- COMPONENT_ID int NOT NULL PRIMARY KEY
- COMPONENT1 varchar(20)
- COMPONENT2 varchar(20)
- COMPONENT3 varchar(20)
- COMPONENT4 varchar(20)
The first column in COMPONENT_TABLE is the COMPONENT_ID column, which is referenced from the ASSEMBLY_TABLE. The other columns in COMPONENT_TABLE are simply components that go into the associated computer.
We’ve now got our schema defined, and the database has been created along with two tables. Let’s now populate the two tables: ASSEMBLY_TABLE and COMPONENT_TABLE.