Tables, Columns , and Rows

The table is the fundamental organizing unit in a database. There are several terminologies used, but generally we describe a table as having columns and rows. Let's take as an example a table designed to hold information about the CDs in our collection. There are a number of different pieces of information we would like to keep about each CD, such as artist, title, and release date. We call each of these a column , and we define them when we create the table.

As we start to enter data into our table, each item that we enter—in our example, the information for each CD—is called a row in the table.

Sometimes the columns in a table are called fields , and sometimes the rows are called records . In other words, for each CD, we will have a record in the table, and each record will have fields identifying the artist, the album title, etc. In general, we'll stick to the row and column terminology.

One of the requirements of a relational database is that it must have a high-level relational language. In practice, this language is SQL (Structured Query Language). The main purpose of SQL is to define and manipulate data through statements that apply to tables, rows, and sets of rows. Each SQL statement is independent of all others, and each one is executed, effectively, all at once. There is no provision in standard SQL (at least, as it is now most commonly implemented) for step-by-step, conditional processing. If we need to do that, we need to use a host procedural language, such as Java, that allows us to execute SQL statements sequentially, making decisions as we go, based on conditional expressions in constructs such as if statements and while loops .

Because SQL was intended to be hosted by another language, it has datatypes that are intended to be mapped to the datatypes of typical procedural languages. Perhaps because of this intention to be so generally useful, these mappings are not particularly ideal for any language. To complicate things further, different database vendors support the SQL standard types in different ways. Oracle, specifically , has a small set of native types that map to a larger set of standard SQL types. As a matter of principle, we should try to do things in as standard a way as possible, but in this case, we'll use the Oracle datatypes, rather than add an additional layer of complexity by struggling to maintain the illusion that we are using the standard SQL types.