A relational database consists of a collection of tables. Each table has two parts: a heading (table name and column names) and a body (rows of data). For example, a student table might include columns for social security number, name, street address, city, state, zip, class, major, and GPA. Each row represents a specific student enrolled at a university.
Tables store data about business entities, with each column corresponding to an attribute (or field) of the entity, and each row representing a record (or tuple). In database design, tables are also called relations, a term rooted in set theory, which provides the mathematical foundation for relational databases.

• Relational Notation vs. Database Tables: Relational notation, as used in an Entity-Relationship Diagram (ERD), is like a blueprint, defining the structure of a relation. Creating a database table in a Relational Database Management System (RDBMS) is the implementation, like constructing a building from that blueprint.

The following diagram illustrates a table storing information about personal computers, highlighting its components (attributes).

In a relational database, there are two types of tables:

1. Base Table: A permanent table stored in the database, holding the actual data. System base tables store metadata for the database itself.
2. Virtual Table: A temporary table created in memory from a query, also called a view. The definition of a view is stored, not the data itself. When a user accesses a view (e.g., querying customer names for recent orders), the RDBMS generates the virtual table on the fly and discards it when no longer needed.

For example, a query to list customer names for orders placed in the past month creates a virtual table in memory, not stored on disk. Base tables, like the Computer Parts Table above, are permanent and managed by the RDBMS.

The database approach, enabled by an RDBMS, offers significant advantages over traditional flat-file processing, where each application maintains its own files. For example, in a flat-file system, a grade reporting office might keep student grade files, while an accounting office tracks student fees, leading to redundant data and separate programs to manage each file.
In contrast, the database approach uses a single repository where data is defined once and accessed by multiple users or applications. This reduces redundancy and ensures consistency. Key advantages include:

1. Self-describing nature: The database contains metadata describing its structure.
2. Data abstraction: Programs are insulated from changes in data structure, simplifying maintenance.
3. Multiple views: Different users can access tailored subsets of data via views.
4. Multiuser transaction processing: Supports simultaneous data access with consistency.

Unlike flat files, where data elements are named independently, database relations use consistent names for queries and applications, enhancing efficiency.

To design effective database tables, you must follow rules rooted in set theory. These rules ensure tables function correctly within an RDBMS, such as Oracle or Microsoft Access, which may include front-end tools for building input screens. Common terms include:

1. Relations: Another term for tables, representing entities in database design.
2. Attributes or Fields: Columns storing data values for an entity.
3. Tuples or Records: Rows containing data for one instance of an entity.

These terms derive from set theory but are explained here in practical, non-mathematical language for clarity.

Before moving to the next lesson on table column rules, test your understanding of relational constructs and table characteristics with the quiz below:
Relational Table Characteristics - Quiz