What is a Relational Database?

A database is an application that can store and retrieve data very rapidly. The relational bit refers to how the data is stored in the database and how it is organized. When we talk about database, we mean a relational database, in fact an RDBMS - Relational Database Management System.

In a relational database, all data is stored in tables. These have the same structure repeated in each row (like a spreadsheet) and it is the relations between the tables that make it a "relational" table.

Before relational databases were invented (in the 1970s), other types of database such as hierarchical databases were used. However relational databases have been very successful for companies like Oracle, IBM and Microsoft. The open source world also has RDBMS.

Commercial Databases

• Oracle
• IBM DB 2
• Microsoft SQL Server
• Ingres. The first commercial RDBMS.

Free/Open Source Databases

• MySQL
• PostgresSQL
• SQLite

Strictly these are not relational databases but RDBMS. They provide security, encryption, user access and can process SQL queries.

Codd was a computer scientist who devised the laws of normalization in 1970. This was a mathematical way of describing the properties of a relational database using tables. He came up with 12 laws that describe what a relational database and a RDBMS does and several laws of normalization that describe the properties of relational data. Only data that had been normalized could be considered relational.

Consider a spreadsheet of client records that is to be put into a relational database. Some clients have the same information, say different branches of the same company with the same billing address. In a spreadsheet this address is on multiple rows.

In turning the spreadsheet into a table, all the client's text addresses must be moved into another table and each assigned a unique ID- say the values 0,1,2. These values are stored in the main client table so all rows use the ID not the text. A SQL statement can extract the text for a given ID.

Think of it as being like a rectangular spreadsheet made up of rows and columns. Each column specifies the type of data stored (numbers, strings or binary data - such as images).

Unlike a spreadsheet where the user is free to have different data on each row, in a database table, every row can only contain the types of data that were specified.

In C and C++, this is like an array of structs, where one struct holds the data for one row.

• For more information see Normalizing a database in the Database Design part of databases.about.com.

There are two ways:

• Via a Database Server.
• Via a Database File.

Database Server

Generally these require the following details.

• IP or Domain name of the server. If it is the on the same PC as you, use 127.0.0.1 or localhost as the dns name.
• Server Port For MySQL this is usually 3306, 1433 for Microsoft SQL Server.
• User Name and Password]
• Name of the Database

There are many client applications that can talk to a database server. Microsoft SQL Server has Enterprise Manager to create databases, set security, run maintenance jobs, queries and of course design and modify database tables.

SQL is short for Structured Query Language and is a simple language that provides instructions for building and modifying the structure of databases and for modifying the data stored in the tables. The main commands used to modify and retrieve data are:

• Select - Fetches data.
• Insert - Inserts one or more rows of data.
• Update - Modifies existing row(s) of data
• Delete - Deletes rows of data.

Any non trivial application can use a database and a SQL based database is a good place to start. Once you have mastered the configuration and administering of the database then you have to learn SQL to make it work well.

The speed at which a database can retrieve data is astonishing and modern RDBMS are complex and highly optimized applications.

Open source databases like MySQL are fast approaching the power and usability of the commercial rivals and drive many databases on websites.

• To learn more about databases, visit Databases on About.com

Programmatically, there are various APIs that provide access to database servers. Under Windows these include ODBC and Microsoft ADO. [h3[Using ADO[/link] So long as there is a provider- software that interfaces a database to ADO, then the database can be accessed. Windows from 2000 has this built in.

Try the following. It should work on Windows XP, and on Windows 2000 if you've ever installed MDAC. If you haven't and want to try this, visit Microsoft.com, do a search for "MDAC Download" and download any version, 2.6 or higher.

Create an empty file called test.udl. Right click in Windows Explorer on the file and do "open with", you should see Microsoft Data Access - OLE DB Core Services". This dialog lets you connect to any database with an installed provider, even excel spreadsheets!

Select the first tab (Provider) as opens by default at the the Connection tab. Select a provider then click Next. The data source name shows the different types of device available. After filling in user name and password, click the "Test Connection" button. After you press the ok button, you can open the test.udl with file with Wordpad. It should contain text like this.

[oledb]
; Everything after this line is an OLE DB initstring
Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Initial Catalog=dhbtest;Data Source=127.0.0.1

The third line is the important one, it contains the configuration details. If your database has a password, it will be shown here, so this is not a secure method! This string can be built into applications that use ADO and will let them connect to the specified database.

For more see

• About ADO.
• What is SQL?

Using ODBC