The Elements of a Database

What elements comprise a database? This article deals mainly with the objects that comprise a database. Several concepts are worthy of coverage within the scope of the database as it relates to database design. As you work with data and databases, you will see how the origination of business information and databases is formulated into database elements. The intent here is to provide a brief coverage of basic database elements to provide you with a basic understanding of the elements found in a database.

Several topics are discussed in the following sections. These topics include:

The database schema

Schema objects

Indexes

Tables

Fields and columns

Records and rows

Keys

Relationships

Data types

Database Schema

A schema is quite simply a group of related objects in a database. Within a schema, objects that are related have relationships to one another, as discussed earlier. There is one owner of a schema, who has access to manipulate the structure of any object in the schema. A schema does not represent a person, although the schema is associated with a user account that resides in the database.

The three models associated with a schema are as follows:

The conceptual model, also called the logical model, is the basic database model, which deals with organizational structures that are used to define database structures such as tables and constraints.

The internal model, also called the physical model, deals with the physical storage of the database, as well as access to the data, such as through data storage in tables and the use of indexes to expedite data access. The internal model separates the physical requirements of the hardware and the operating system from the data model.

The external model, or application interface, deals with methods through which users may access the schema, such as through the use of a data input form. The external model allows relationships to be created between the user application and the data model. Figure 1 depicts a schema in a relational database.

A schema object is an object that resides within a schema. The most typical object found in a database is a table. Other types of objects can reside in a schema, such as indexes, constraints, views, and procedures.

The table is the most fundamental element found in a database schema. Columns and rows are associated with tables. Tables, columns, and rows are discussed in the following subsections.

Table

A table is the primary unit of physical storage for data in a database. When a user accesses the database, a table is usually referenced for the desired data. Multiple tables might comprise a database, therefore a relationship might exist between tables. Because tables store data, a table requires physical storage on the host computer for the database.

Figure 2 illustrates tables in a schema. Each table in the figure is related to at least one other table. Some tables are related to multiple tables.

Figure 2 - Database tables and their relationships.

Four types of tables are commonly used:

Data tables store most of the data found in a database.

Join tables are tables used to create a relationship between two tables that would otherwise be unrelated.

Subset tables contain a subset of data from a data table.

Validation tables, often referred to as code tables, are used to validate data entered into other database tables.

Tables are used to store the data that the user needs to access. Tables might also have constraints attached to them, which control the data allowed to be entered into the table. An entity from the business model is eventually converted into a database table.

Columns

A column, or field, is a specific category of information that exists in a table. A column is to a table what an attribute is to an entity. In other words, when a business model is converted into a database model, entities become tables and attributes become columns. A column represents one related part of a table and is the smallest logical structure of storage in a database. Each column in a table is assigned a data type. The assigned data type determines what type of values that can populate a column. When visualizing a table, a column is a vertical structure in the table that contains values for every row of data associated with a particular column.

In Figure 3, columns within the Customers table are shown. Each column is a specific category of information. All of the data in a table associated with a field is called a column.

Figure 3 -Columns in a database table.

Rows

A row of data is the collection of all the columns in a table associated with a single occurrence. Simply speaking, a row of data is a single record in a table. For example, if there are 25,000 book titles with which a bookstore deals, there will be 25,000 records, or rows of data, in the book titles table once the table is populated. The number of rows within the table will obviously change as books' titles are added and removed. See Figure 4 for an illustration of a row of data in a table.

Figure 4 - Row of data in a database table.

Data Types

A data type determines the type of data that can be stored in a database column.

Although many data types are available, three of the most commonly used data types are

Alphanumeric

Numeric

Date and time

Alphanumeric data types are used to store characters, numbers, special characters, or nearly any combination. If a numeric value is stored in an alphanumeric field, the value is treated as a character, not a number. In other words, you should not attempt to perform arithmetic functions on numeric values stored in alphanumeric fields. Design techniques such as this will be discussed in more detail throughout the book. Numeric data types are used to store only numeric values. Date and time data types are used to store date and time values, which widely vary depending on the relational database management system (RDBMS) being used.