RelationalDBDesign  Tweet

Database Analysis   «Prev   Next»
Lesson 4 Characteristics of Database Tables
Objective Describe the characteristics of Relational Database Management Tables

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.
Relational notation in contrast to concept of database tables:
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 interactive graphic shows a table that contains information about personal computers and points out the components of the table.
Mouse over each of the red circles to read about each of the attributes.
1 2 3
Table characteristics
Relational Database Characteristics

Designing Database Tables
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. 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. The columns of a table are referred to as attributes or fields .
  3. Rows are referred to as tuples (pronounced tup-pulls ) or records.
    record: A particular instance of the subject of a table.
  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.
Advice regarding RDBMS
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.

Types of Tables
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; rather, 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), 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.
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.
The next lesson lists the rules for table columns.
Relational Table Characteristics - Quiz
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

To learn more about database design, visit one of the images below