Characteristics of Database Tables

Characteristics of Database Tables: A relational database consists of a collection of tables. Each table has 1) a heading or definition part and 2) a body or content part. The heading part consists of the table name and the column names. For example, a student table may have columns for social security number, name, street address, city, state, zip, class, major and GPA. The body shows the rows of the table. Each row in a student table represents a student enrolled at a university.

Once you have created entities representing the business objects you want to represent in your database, you need to create tables to store the data in the database. Tables are made up of rows and columns.
Each column corresponds to an attribute of the entity to which the table refers. Every row represents an instance of the entity described by the database table. Specific data relating to each attribute is stored in the rows corresponding to each column.

The difference between a relational notation and a database table is like the difference between a blueprint and a building.

1. A Plan is similar to the Relational Notation in the ERD diagram.
2. The implementation is the creation of the database table in the relational database management system.

The following diagram shows a table that contains information about personal computers and points out the components of the table. Each of the red circles highlights each of the attributes.

A relational database management system (RDBMS) is a term used to describe a set of programs for both managing a relational database and communicating with that relational database engine. Sometimes front-end tools and complete management kits are included with relational database packages. Microsoft Access is tightly integrated with programming languages such as VB.net. Both the relational database and front-end development tools used to build input screens, are all packaged within the same piece of software. In other words, an RDBMS represents both the database engine and any other tools that come with it. RDBMS is just another name for a relational database product such as Oracle.

In order for a relational database to function properly, table rows and columns must follow a number of rules grounded in the mathematical discipline of set theory. ^[1] The next few lessons review those rules (in non-mathematical language whenever possible), but for now it is enough to know that the rules exist and that they are practical. Because relational databases are based on set theory, you may encounter the formal terms for database tables and their components in the literature of set theory. Here are the terms you are most likely to see:

1. Database Tables are referred to as relations.^[2]
2. The columns of a table are referred to as attributes^[3] or fields ^[4].
3. Rows are referred to as tuples ^[5](pronounced tup-pulls) or records ^[6].

The term relational database comes from the mathematical term relation (the definition of a two-dimensional table), not the fact that tables can have relationships between them.

There are two basic types of tables in a relational database:

1. base table: A table stored in a database.
   System base tables are the underlying tables that actually store the metadata for a specific database.
2. virtual table: A table stored in the memory of a computer. Virtual tables themselves are not stored in the database, instead the definition of the view is stored and given a name. Users call up that name, and the view is created (from base tables) on the fly.
   When a user closes the view, the virtual table is deleted from memory, only to be recreated the next time its name is invoked.

Base tables are permanent tables you create in the RDBMS (Relational Database Management System)^[7], while virtual tables are temporary tables the RDBMS creates in response to user commands. For example, if you enter a command to find the first name and last name of every customer who has placed an order in the past month, the RDBMS writes those names to a virtual table which it stores in system memory but does not write to disk.
The next lesson lists the rules for table columns.

Before moving to the next lesson, click the Quiz link below to check your understanding of relational constructs and the characteristics of tables.
Relational Table Characteristics - Quiz

[1]set theory: A branch of mathematics that studies how groups of objects interact.

[2]relation: A relation is another word for table within the context of an entity-relationship-diagram. Relations represent entities in database design and data modeling.

[3]attribute: A characteristic of an entity or the data that identifies or describes an entity. An attribute is usually represented as a column in a table. The purpose of attributes is to store data values.

[4]field: The smallest structure in a table; contains data describing the subject of a table.

[5]tuple: Within the context of relational database design, tuples are another word for rows or records.

[6]record: A particular instance of the subject of a table.

[7]Relational Database Management System (RDBMS): A software package that manages and provides access to a database. These packages follow Codd's 12 rules of relational databases and normally use SQL to access data.