Basic Database Index


    Basics

  1. Introduction

    Relational Databases

  2. RDBMS Definition

    Object Databases

  3. ODBMS Definition
  4. Features
  5. Standards

    Object Relational Databases

  6. ORDBMS Definition

    Appendices

  7. Terms
  8. Credits

RDBMS Definition

Relational databases store data in tables (relations) that are two dimensional. The tables have rows (records or objects) and columns (fields or attributes). Data items at an intersection of a row and a column are called a cell and consist of attribute values. Data stored is simple data such as integers, real numbers or string values. Multiple values may not be stored in one cell. Relational database tables are "normalized" so data is not repeated more often than necessary. All table columns depend on a primary key (a unique value in the column) to identify the column. Once the specific column is identified, data from one or more rows associated with that column may be obtained or changed.

Relational databases are sets of tables. One table file is not a relational database. A relational database server is not the same as a relational database. A relational database can be a file with sets of tables. The relational database server includes the ability to service requestesto get or change data from remote clients.

Relational database servers use Structured Query Language (SQL), as a data manipulation language to interface between itself and the clients. SQL is the standard for getting and storing data in an RDBMS. For information about SQL, see the "Beginner's SQL Guide".

Relational database servers provide:

  • Data Management
  • Transaction processing
  • Data integrity - Provides for multiple access at the same time (concurrency) between multiple processes/users. This is done so data is not displayed nor saved in a fashion where one change is lost. Various locking mechanisms are used to support this.
  • Data backup and recovery.
  • Data security - Provides for user authentication, and levels of data access privileges.

Primary Keys

In relational databases, the data is not arranged in any particular order in tables. The data in tables requires keys for identification of rows. Each table has rows and columns. Sets of values in a row may describe a particular item such as customers. Consider the following table:

Customers

Customer IDNameStreet AddressCityZip CodePhonee-mail
112304John Brown123 Straight LaneParadiseMI49555johnb@heaven.net
134056Tim Smith321 Curved AveHollywoodMD10255tims@eastnet.net
234902Sue Jones213 Hill RoadKnoxvilleTN23555suej@nicenet.net
092387George Abernathy555 North RoadSmalltownFL67555georgea@mynet.net
187462Tom Jenkins666 South LaneColdMN55555tomj@networks.com
108976Kim Adams456 Peachtree LaneAtlantaGA54555kima@bestnet.net
059385Chris Christopher859 East RoadNicetownMS45555chrisc@bignet.com
140763Jim Thompson307 West AveBigcityND65555jimt@fastnet.net

Columns include Customer ID, Name, Street Address, City and so forth. Each column has a different set of information about all customers such as their phone number. This information could be considered customer characteristics or attributes. Each row has all information about one particular customer. Each row must have a unique means of identification. We could have used the customer name to identify the customer, but it is possible that more than one customer may have the same name. Therefore we are using unique customer identifiers. The Customer ID is the key used to identify individual customers. The column to be used as the key must be identified to the relational database. This is called the primary key.

Foreign Keys

Let's say we're running a store with the customers listed above. The first table below lists items for sale. The second table lists orders that have been placed in the store, and the third table lists items ordered for an individual order. If we want to print out what items customer 234902 ordered we do the following:

  1. Go to the orders table and get all orders customer 234902 ordered.
  2. For each order found (1 order #190389575). The order number is considered to be a foreign key to another table and column which is to table "Individual Order 190389575" and Item ID.
  3. Go to the "Individual Order 190389575" table and get each item ID that was ordered. Each item ID is a foreign key to the "Store Items" table and Item ID column.
  4. Go to the store items table, find each item ID and print out each item attribute such as price, description, and quantity purchased (from the individual order table).

Store Items

Item IDDescriptionPrice
000234567Small Flower Pot2.36
000018901Potting Soil5.97
001034654Geranium Seed1.50

Orders

Order IDCustomer IDSubtotalTaxTotal
19038957523490216.160.8216.98
1097482301874622.370.122.49
208949023059385104.235.22109.45
10379203418746240.002.0042.00
12304893813405610.000.5010.50

Individual Order 190389575

Item IDQuantity
0002345672
0000189012
0010346541

Therefore foreign keys are used to access data in related tables.