In the late 1970s, the relational database model which originated in the academic research community became available in commercial implementations such as IBM DB2 and Oracle. The relational data model specifies data stored in relations that have some relationships among them (hence the name relational).
In relational databases such as Sybase, Oracle, DB2 and MS Access, data is stored in tables made up of one or more columns (Access calls a column a field). The data stored in each column must be of a single data type such as Character, Number or Date. A collection of values from each column of a table is called a record or a row in the table.
Different tables can have the same column in common. This feature is used to explicitly specify a relationship between two tables. Values appearing in column A in one table are shared with another table.
Below are two examples of tables in a relational database for a local bank:
Customer Table
| CustomerID | Name | Address | City | State | Zip |
|---|---|---|---|---|---|
| Number | Character | Character | Character | Character | Character |
| 1001 | Mr. Smith | 123 Lexington | Smithville | KY | 91232 |
| 1002 | Mrs. Jones | 12 Davis Ave. | Smithville | KY | 91232 |
| 1003 | Mr. Axe | 443 Grinder Ln. | Broadville | GA | 81992 |
| 1004 | Mr. & Mrs. Builder | 661 Parker Rd. | Streetville | GA | 81990 |
Accounts Table
| CustomerID | AccountNumber | AccountType | DateOpened | Balance |
|---|---|---|---|---|
| Number | Number | Character | Date | Number |
| 1001 | 9987 | Checking | 10/12/1989 | 4000.00 |
| 1001 | 9980 | Savings | 10/12/1989 | 2000.00 |
| 1002 | 8811 | Savings | 01/05/1992 | 1000.00 |
| 1003 | 4422 | Checking | 12/01/1994 | 6000.00 |
| 1003 | 4433 | Savings | 12/01/1994 | 9000.00 |
| 1004 | 3322 | Savings | 08/22/1994 | 500.00 |
| 1004 | 1122 | Checking | 11/13/1988 | 800.00 |
The Customer table has 6 columns (CustomerID, Name, Address, City, State and Zip) and 4 rows (or records) of data. The Accounts table has 5 columns (CustomerID, AccountNumber, AccountType, DateOpened and Balance) with 7 rows of data.
Each of the columns conforms to one of three basic data types: Character, Number or Date. The data type for a column indicates the type of data values that may be stored in that column.
In some database imploementations other data types exist such as Images (for pictures). However, the above three data types are most commonly used.
Notice that the two tables share the column CustomerID and that the values of the CustomerID column in the Customer table are the same the values in the CustomerID column in the Accounts table. This relationship allows us to specify that the Customer Mr. Axe has both a Checking and a Savings account that were both opened on the same day: December 1, 1994.
Another name given to such a relationship is Master/Detail. In a master/detail relationship, a single master record (such as Customer 1003, Mr. Axe) can have many details records (the two accounts) associated with it.
In a Master/Detail relationship, it is possible for a Master record to exist without any Details. However, it is impossible to have a Detail record without a matching Master record. For example, a Customer may not necessarily have any account information at all. However, any account information must be associated with a single Customer.
Each table also must have a special column called the Key that is used to uniquely identify rows or records in the table. Values in a key column (or columns) may never be duplicated. In the above tables, the CustomerID is the key for the Customer table while the AccountNumber is the key for the Accounts table.