Archive for July, 2008

h1

SQL – Understanding Relational Databases

July 20, 2008

I know that SQL isn’t “networking,” as this blog is supposed to be about, but with responsibilities in my new job (as a glorified “business analyst”), I wanted to put some information about SQL (and forthcoming, SQL Server 2005) into this blog. So, having said that, off we go!

SQL, or Structured Query Language, supports the creation and maintenance of the relational database and the management of data within that database. The term database has been used to refer to anything from a collection of names and addresses to a complex system of data retrieval and storage that relies on user interfaces and a network of client computers and servers. There are as many definitions for the word database than there are books and articles about them. Despite the lack of an absolute meaning, most sources agree that a database, at the very least, is a collection of data organized in a structured format that is defined by metadata that describes the structure. You can think of metadata as data about the data being stored – it defines how the data is stored within the database.

Over the years, a number of database models have been implemented to store and manage data. Several of the more common models include:

Hierarchical – This model has a parent-child structure that is similar to an inverted tree, which is what forms the hierarchy. A parent table can have many child tables, but a child table can have only one parent table. Although the model has been highly implemented, it is often considered unsuitable for many applications because of its inflexible structure and lack of support for complex relationships. Still, many implementations have introduced functionality that works around these limitations.

Network – This model addresses some of the limitations of the hierarchical model. It still uses an inverted tree structure, but the tables are organized into a set structure that relates pairs of tables into owners and members. Any one table can participate in any set with other tables in the database, which supports more complex queries than are possible in the hierarchical model. Still, the network model has its limitations. You have to be very familiar with the database to work through the set structures, and it’s difficult to change the structure without affecting applications that interact with the database.

Relational – This model addresses many of the limitations of both the hierarchical and network models. In a hierarchical or network database, the application relies on a defined implementation of the database, which is hard-coded into the application. If you add a new attribute to the database, you must modify the application, even if it doesn’t use the attribute. However, a relational database is independent of the application; you can modify the structure without impacting the application. In addition, the structure of the relational database is based on the relation, or table, which provides the ability to define complex relationships between these relations. Each relation can be thought of as an entity in its own right, without the cumbersome limitations of a hierarchical or owner/member model that restricts how relationships can be defined between tables.

Hierarchical and network databases are found in legacy systems and are still used in many organizations. However, the relational model has replaced many of these systems and is the model most extensively implemented by modern database products, and it is the relational model that provides the foundation for SQL.

Advertisements