Database Concepts

Databases have been in use since the earliest days of electronic computing, but the vast majority of these were custom programs written to access custom databases. Unlike modern systems which can be applied to widely different databases and needs, these systems were tightly linked to the database in order to gain speed at the price of flexibility.

In 1960, Charles Bachman developed the first database management system (DBMS) which two key data models arose: the network model (developed by CODASYL) followed by the hierarchical model (as implemented in IMS). These were later usurped by the relational model, which was contemporary with the so-called flat model designed for very small tasks. Another contemporary of the relational model is the object-oriented database (OODB).

Flat model

The flat (or table) model consists of a single, two-dimensional array of data elements, where all members of a given column are assumed to be similar values, and all members of a row are assumed to be related to one another. For instance, columns for name and password might be used as a part of a system security database. Each row would have the specific password associated with a specific user. Columns of the table often have a type associated with them, defining them as character data, date or time information, integers, or floating point numbers. This model is the basis of the spreadsheet.

Relational Database Model

Dr. Edgar F. Codd worked at IBM in San Jose, California, were he worked primarily in the development of hard disk systems. He was unhappy with the navigational model of the Codasyl approach, notably the lack of a "search" facility which was becoming increasingly useful when the database was stored on disk instead of tape. In 1970, he wrote a number of papers that outlined a new approach to database construction and eventually culminated in the groundbreaking paper called "A Relational Model of Data for Large Shared Data Banks".

In this paper he described a new system for storing and working with large databases. Instead of records being stored in some sort of linked list of free-form records as in Codasyl, Codd's idea was to use a "table" of fixed-length records. A linked-list system would be very inefficient when storing "sparse" databases where some of the data for any one record could be left empty. The relational model solved this by splitting the data into a series of tables, with optional elements being moved out of the main table to where they would take up room only if needed.

In 1985, Dr. Codd published a set of 13 rules, also called Codd's Law, used to determine if a Database Management System (DBMS) can be considered a Relational DBMS (RDBMS). This list of rules became a standard way of evaluating a relational system. These rules have since been expanded by others, and after publishing the original article, Codd stated that there are no systems that will totally satisfy every rule.

When E.F. Codd developed the model, it was thought to be hopelessly impractical, as the machines of the time could not cope with the overhead necessary to maintain the model. Of course, hardware since then has come on in huge strides, so that today even the most basic of PC's can run sophisticated relational database management systems.

A database can be understood as a collection of related files. How those files are related depends on the model used. Early models included the hierarchical model (where files are related in a parent/child manner, with each child file having at most one parent file), and the network model (where files are related as owners and members, similar to the network model except that each member file can have more than one owner).

The relational database model was a huge step forward, as it allowed files to be related by means of a common field. In order to relate any two files, they simply need to have a common field, which makes the model extremely flexible

Relationships

Each relationship will have a Primary (parent) table and a Related (child) table as previously described. An easy way to determine the Primary table in the relationship is to note the Primary key. Typically the Primary table is the table that holds the Primary key field in the relationship. There are three types of Relationships:

* One-to-One relationship exists when the primary record will have only one related record. Another determining factor is both fields used the relationship are Primary Key fields.

* One-to-Many relationship is the most common type of relationship. A one-to-many relationship exists when the primary record can have many related records.

* Many-to-Many relationship is used to relate many records in the parent table with many records in the child table. A record in the parent table can have many matching records in the child table, and a record in the child table can have many matching records in the parents table. Many-to-Many is the hardest relationship to understand and it is not correct. By breaking it into two one-to-many relationships and creating a new (junction/link) table to stand between the two existing tables will enable correct and appropriate relationship setting.

Hierarchical Database Model

Hierarchical database is a kind of database management system that links records together in a tree data structure such that each record type has only one owner, e.g. an order is owned by only one