Introduction to Databases for the Web: Pt. 1 - Page 10

Creating Databases

Okay, as we have said before, it would be too difficult for us to cover how to install and configure all of the myriad of relational databases around, so it is your job to get something installed on your local system or to arrange with your systems administrator to give you access to an existing database system.

However, for the purposes of example, I am going to use Microsoft Access as an example of an SQL database. Microsoft Access comes with Microsoft Office and is a good database to practice with since it is available for Mac, Windows and UNIX and is a relational-based database that understands SQL.

Of course, I wouldn't necessarily use Access for a web application, because...well...because it is a Microsoft product. But it is pretty useful for practicing and demonstrating SQL since it is pretty ubiquitous. Regardless, in these days of mature software applications, whichever database you use, will have a process that is similar to the one I will describe for Access.

Specifically, to get a database working, you will 1) install the application on the host computer (insert disk A, click install.exe), 2) create a database according to the instructions of the database application you install, 3) populate your database with tables, and 4) populate your tables with data.

In the case of Access and many other database systems around these days, the process of creating databases and tables is pretty much handled by Wizards and GUI tools. Thus, you rarely need to use SQL for such operations. More likely, you would choose something like "FILE|NEW DATABASE" from the main menu.

Below is an example of the Wizard used by Microsoft Access. In this case, we have chosen "File|New Database" from the main menu at the top of the application window and then we double click "Blank Database". Of course, although Access and other database systems offer template databases for your convenience, we will create our own for practice.

In Access, as will be the case in most databases, once you create a database, you will then be asked to define the database structure.

Specifically, you will be asked to define some tables in your database (and perhaps other more advanced tools like Macros, Indexes, Views, Forms, Queries, etc). Below is a screen shot from Access that offers a series of database definition tabs.

To create a table, simply choose "New" from the "Tables" tab and follow instructions for defining your fields. Nothing could be simpler. Hopefully, now that you understand what the database is doing in the background, you will be easily able to understand what you need to do to get it working.

However, even though GUIs are pretty swank these days, it is probably a good idea to learn the SQL to which is being used in the background to create the database. Specifically, you use the CREATE command to create a database such as in the following example

CREATE DATABASE DATABASE_NAME;

We might use the following code to create a database called MY_COMPANY.

CREATE DATABASE MY_COMPANY;

Creating Tables

Once you have created your database you can then start populating it with tables. In the case of Access, as you saw on the last page, creating tables is as easy as clicking "New" in the table tab of the "Database" tab.

However, you should know that in the background, Access, and other GUI database systems are using the CREATE TABLE command to create a new table.

This command looks like the following:

CREATE TABLE TABLE_NAME (COLUMN_NAME DATA_TYPE,
                         COLUMN_NAME DATA_TYPE,
                         COLUMN_NAME DATA_TYPE) 
       IN DATABASE DATABASE_NAME

For example, you might see the following SQL code to create a table called PRODUCTS with three columns in the MY_COMPANY database we just created. Note that the three columns would be P_NUM which would be an integer value and could not be null, the P_QUANTITY which would also accept integers as values, and the P_PRICE column which would accept decimal numbers with 8 digits before and 2 digits after the decimal point.

CREATE TABLE PRODUCTS (P_NUM INT NOT NULL,
                       P_QUANTITY INT,
                       P_PRICE DECIMAL(8,2))
       IN DATABASE MY_COMPANY;

Notice that as we mentioned before, when you create a table, you must specify the data type for each column. Notice also that you may use the "NOT NULL" keyword to tell the database that it should not allow any NULL values to be added to the column.

As a final note, I would like to mention that you can also typically create Views, Indexes, and Synonyms, however, those topics are beyond the scope of this tutorial since you will most likely not be doing database administration types of activities. For most web development work, it is simply enough to define some tables.