JaVa
   

Relational Databases

Up until now, we have been looking mainly at how to create database structures and do simple data manipulation within them. However, there are many useful ideas and theories that make databases even more useful to us. For a definition of a relational database, please see the previous chapter. Let's now look at what sort of structure we would want for a relational database. Think of a database that related the players in a game to one another (for example, to determine who was a friend of each player and who was an enemy of each player). First let's create a table to store the data for each of the players. Notice the addition of a primary key, which allows you to rely on the fact that all rows have a unique field that can be used as a reference. Note also that every row of data in the primary key must be unique to one another. Here is the statement required to create our database (called gamedata) and our playerdata table.

mysql> CREATE DATABASE gamedata;
mysql> USE gamedata;
mysql> CREATE TABLE playerdata (
 -> username CHAR(255) UNIQUE NOT NULL,
 -> password CHAR(255),
 -> age INT,
 -> datecreated TIMESTAMP,
 -> PRIMARY KEY(username));


Notice here how we set the username column to UNIQUE and also NOT NULL. In simple terms, this means that it must contain a value, and that value must not be the same as any other username in any other record in the table. Note also that we have set the primary key of the table to be the username field, as we will be mainly searching on this field, which you will see in a moment. In addition to this information, we also need some way to store friends and enemies. This is done by means of a link table. A link table is really just a normal database table, but its main purpose is to relate data in some way or another to conserve space and optimize the way the database accesses the information. Let's create two link tables, one for relating friends and one for relating enemies to each other. Following are the statements that are required to accomplish this.

Note 

If link tables are used, the optimized way is to store INT values there and have an ID field with auto_increment in the playerdata table as a primary key. It is not as readable when you perform a SELECT, but it is faster from within your apps. When updating a player's name, it does not break the integrity of the database. Also, link tables are often structures to be used only when there are n amount of relations from one row to other rows. If there is always only one relation (one friend or enemy), a direct link should be used.

mysql> CREATE TABLE relatefriends (
 -> player CHAR(255),
 -> friend CHAR(255));


Also:

mysql> CREATE TABLE relateenemies (
 -> player CHAR(255),
 -> enemy CHAR(255));


If we now show the tables in the database with the following command:

mysql> SHOW TABLES;


...we can see from the following figure that our database now contains three different tables—our playerdata table and the two link tables.

Java Click To expand
Screenshot-46: Our three tables in the gamedata database

Let's now add a sample of data to the player data table, so we can experiment with the link tables and understand the logic of how to use them effectively. Here is the statement required to add our sample data to the playerdata table:

mysql> INSERT INTO playerdata VALUES
 -> ('Andrew', 'qwerty', 20, NULL),
 -> ('Henry', 'letmein', 34, NULL),
 -> ('Sandra', 'dra33', 19, NULL),
 -> ('John', 'j12d', 23, NULL),
 -> ('Jenny', 'jen123', 34, NULL);


If we select all the information from the playerdata table now using the following command:

mysql> SELECT * FROM playerdata


...we can see from the following figure that all of our data is now in the playerdata table.

Java Click To expand
Screenshot-47: Our data in the playerdata table

Now that we have some sample data, let's try to create some relations between the players in the database. First add to the relatefriends link table the fact that Henry is friends with Sandra. Here is the statement required to add this to the link table:

mysql> INSERT INTO relatefriends VALUES
 -> ('Henry', 'Sandra');


If we now show all of the data from the relatefriends link table, the following will be visible in the MySQL console:

Java Click To expand
Screenshot-48: Our data in the relatefriends table

Let's now add some more sample data into both the relatefriends and relateenemies link tables and see how we can manipulate the data. The two statements required to add in the sample data are below:

Note 

To prevent being a friend and enemy at the same time, one relation table could be used. Just add a field "enemy" flag, and if it is set, it means that they are enemies; otherwise they are friends.

mysql> INSERT INTO relatefriends VALUES
 -> ('Andrew', 'Henry'),
 -> ('Andrew', 'John'),
 -> ('Andrew', 'Jenny'),
 -> ('Sandra', 'Jenny');


And also:

mysql> INSERT INTO relateenemies VALUES
 -> ('Andrew', 'Sandra'),
 -> ('Henry', 'Jenny'),
 -> ('Henry', 'John');


Now that we have all of our sample data, let's see if we can find out who Andrew is friends with by using the following statement:

mysql> SELECT friend FROM relatefriends WHERE player = 'Andrew';


When we execute this statement, the console displays a list of all the players that Andrew is friends with. Here is a screen shot of the expected console output:

Java Click To expand
Screenshot-49: Finding out a player's friend list

When we start implementing databases in Java in the next chapter, we will use this data to find out more information about each of Andrew's friends. Again, we can do exactly the same with the relateenemies link table. For example, we could find out all of Henry's enemies with the following statement:

mysql> SELECT enemy FROM relateenemies WHERE player = 'Henry'


When we execute this statement, the following console output can be expected.

Java Click To expand
Screenshot-50: Finding out a player's enemy list

With this data, if we then wanted to find out more information about Henry's enemy that has the username of Jenny, we would use the following statement:

mysql> SELECT * FROM playerdata WHERE username = 'Jenny';


Here is a screen shot of our expected console output:

Java Click To expand
Screenshot-51: Finding more data about an enemy

This may seem a rather pointless exercise at the moment, but rest assured that it has many uses, as you will find out in the next chapter, "Using the JDBC."

JaVa
   
Comments