Let’s take the entities we created last time and turn them into tables. Once we do this we’ll create these tables in a REALbasic database using SQL.

Mapping entities to tables is fairly straightforward. Typically each entity becomes a table and each attribute becomes a column. You may have to do a few adjustments of table and column names to meet any restrictions of the specific database you are using. Also keep in mind that not every database has the exact same datatypes so some changes may be necessary there as well.

Creating the SQL

Refer to the entities we created last time:

There’s nothing fancy in the team entity (except that we’ll use varchar instead of string), so it’s table create statement looks like this:

CREATE TABLE team (KEY INTEGER PRIMARY KEY NOT NULL, name TEXT NOT NULL, coach TEXT)

The key column is what is called an artificial primary key. It is used in place of the team name so that we can more easily allow for changes to the team name after it has been used in other relationships.

The location entity is also simple, here is its create table statement:

CREATE TABLE location (KEY INTEGER PRIMARY KEY NOT NULL, 
FIELD TEXT NOT NULL, lights TEXT, address TEXT)

The game table is a bit more complicated because of the relationships. Each of the relationships translates to a column in the game table. These columns are referred to as a foreign keys.

CREATE TABLE game (KEY INTEGER PRIMARY KEY NOT NULL, 
hometeam INTEGER, awayteam INTEGER, location INTEGER, gamedate DATE, gametime TIME)

I have used gamedate and gametime in place of date and time because most database systems do not like it when you use a reserved word as a column name.

Creating the Database

You have the option of creating the actual database and the tables using the REALbasic IDE, but we’re here to learn SQL so we’ll going to actually use the CREATE TABLE commands from above. Unfortunately, REALbasic’s built in database tool only allows for interactive SELECT statements and not CREATE TABLE or any of the other SQL commands. So for these examples we will use the free SQLite Database Browser, an open-source browser that works on Windows, Mac OS X and Linux.

Once you have started SQLite Database Browser, click on the leftmost icon to create a new database file and enter a name for the database. For this example we’ll use Softball.rsd. Click Cancel on the Create Table window that appears. We’ll be creating our tables using SQL.

Now click the Execute SQL tab. You can enter the CREATE TABLE commands from above (one at a time) and press the Execute query button. Each time you successfully create a table it will appear in the table list on the left.

After you have created the 3 tables, it’s time to add some sample data to them. We’ll use the SQL INSERT command to do this. These commands add data to the team table. Enter one at a time into the Execute SQL tab:

INSERT INTO team (name, coach) VALUES ("Ducks", "Paul" ) 
INSERT INTO team (name, coach) VALUES ("Bears", "David" ) 
INSERT INTO team (name, coach) VALUES ("Lions", "Vin" ) 
INSERT INTO team (name, coach) VALUES ("Tigers", "Julie" ) 
INSERT INTO team (name, coach) VALUES ("Dogs", "Laura" ) 
INSERT INTO team (name, coach) VALUES ("Cats", "Jim" )

We are not inserting any key values because we are letting SQLite handle that for us automatically. Now let’s add some data to the location table:

INSERT INTO location (FIELD, lights, address) VALUES ("Hadlock Field", "Yes", "Downtown" ) 
INSERT INTO location (FIELD, lights, address) VALUES ("Payson Park", "No", "Back Cove" )

And finally let’s add some games. This first command creates a game between the Ducks (Key = 1) and the Bears (Key = 2) at Hadlock Field (Key = 1):

INSERT INTO game (hometeam, awayteam, location, gamedate, gametime) 
VALUES (1, 2, 1, "2003-06-01", "05:00:00" )

This command creates a game between the Tigers (Key = 4) and the Dogs (Key = 5) at Payson Park (Key = 2):

INSERT INTO game (hometeam, awayteam, location, gamedate, gametime) 
VALUES (4, 5, 2, "2003-06-01", "05:00:00" )

To view the data, switch to the Browse Data tab and select a table from the drop-down list.

To see the games we just entered in a nicer format, try this SQL:

SELECT hteam.name, 'vs.', ateam.name, 'at', location.FIELD 
FROM team ateam, game, location, team hteam 
WHERE hteam.KEY = game.hometeam 
AND ateam.KEY = game.awayteam 
AND game.location = location.KEY

Download Softball SQLite database

(No Ratings Yet)

 Loading ...