The Elements of a Database

Developer.com content and product recommendations are editorially independent. We may make money when you click on links to our partners. Learn More.

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.

Learn more about Databases at TechRepublic Academy!

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.

Figure 1 – Collection of objects that comprise a database schema.

NOTE

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.

Keys

The integrity of the information stored in a database is controlled by keys. A key is a column value in a table that is used to either uniquely identify a row of data in a table, or establish a relationship with another table. A key is normally correlated with one column in table, although it might be associated with multiple columns. There are two types of keys: primary and foreign.

Primary Keys

A primary key is the combination of one or more column values in a table that make a row of data unique within the table. Primary keys are typically used to join related tables. Even if a table has no child table, a primary key can be used to disallow the entry of duplicate records into a table. For example, an employee’s social security number is sometimes considered a primary key candidate because all SSNs are unique.

Foreign Keys

A foreign key is the combination of one or more column values in a table that reference a primary key in another table. Foreign keys are defined in child tables. A foreign key ensures that a parent record has been created before a child record. Conversely, a foreign key also ensures that the child record is deleted before the parent record.

Figure 5 illustrates how a foreign key constraint is related to a primary key constraint. The Auth_id column in the Booklist table references the Auth_id in the Authors table. Authors is the parent table and Booklist is the child table. If a record for Auth_id=3 needs to be created, the record must be inserted into the Authors table first. If an author needs to be removed from the database, all references to the author must first be deleted in Booklist, the child table.

Figure 5 – Referential integrity, or parent/child relationships.

Relationships

Most databases are divided into many tables, most of which are related to one another. In most modern databases, such as the relational database, relationships are established through the use of primary and foreign keys. The purpose of separating data into tables and establishing table relationships is to reduce data redundancy. The process of reducing data redundancy in a relational database is called normalization and is discussed in detail in Chapter 8 Of our book, Database Design.

Three types of table relationships that can be derived are as follows:

• One-to-one–One record in a table is related to only one
  record in another table.
• One-to-many–One record in a table can be related to many
  records in another table.
• Many-to-many–One record in a table can be related to one or
  more records in another table, and one or more records in the second table
  can be related to one or more records in the first table.

Figure 6 briefly illustrates table relationships in a relational database. A relational database allows parent tables to have many child tables, and child tables to have many parent tables. The figure shows two tables. Table 1 has an ID column (primary key) and Table 2 has an FK column (foreign key). In the one-to-one relationship example, notice that for every ID in Table 1, there is only one ID in Table 2. In the one-to-many relationships example, notice that the ID of 1 has many occurrences in Table 2. In the many-to-many relationship example, notice that the ID in Table 1 might occur multiple times in Table 2 as a foreign key, and the ID in Table 2 might occur multiple times in Table 1.

Figure 6 – Available table relationships in the relational model.

Conclusion

The intent of this article was to provide a brief coverage of basic database elements to provide you with a basic understanding of the elements found in a database. While it may not seem like a lot has been covered, you have actually now been exposed to the key topics involved in working with databases. As you continue to work with databases, you will find that you will continually be confronted with the above terms and concepts, including schemas, tables, columns, rows, and keys.

About the Authors

Ryan K. Stephens and Ronald R. Plew are the authors of Database Design (ISBN: 0-672-31758-3), a book by Sams Publishing. Ryan is president and CEO of Perpetual
Technologies, Inc. a database company specializing in Oracle consulting and
training. Ron is vice president and CIO of Perpetual Technologies, Inc. Both
have numerous years of experience training and consulting at the DBA level.

This article is based on techniques presented in Database Design (ISBN: 0-672-31758-3), which is available
by clicking on the title.

&copy Copyright Sams Publishing, All Rights Reserved