2.4f

Understanding Tables in Databases

Table Structure

• A table consists of:

  • Columns: Each column has a name and a data type.

  • Rows: An unnamed sequence of values, where each value corresponds to a column's data type.

  • Cells: A single column of a single row in the table.

• At least one column is mandatory, but there can be multiple rows.

• An empty table has no rows.

Rules for Table Construction

1. No Duplicate Column Names:

   • Duplicate column names are allowed in different tables but not within the same table.

2. No Duplicate Rows:

   • No two rows may have the exact same values in all columns.

3. Row Order:

   • Rows are not ordered. The organization of rows in storage does not affect query results.

   • This principle is known as data independence which allows for improved query performance by modifying the structure of data storage without affecting the outcomes of queries.

   • Commonly, many databases may allow duplicate rows temporarily, violating rule 2.

Creating a Table

• CREATE TABLE Statement:

  • Used to define a new table by stating:

    • Table name

    • Column names

    • Data types for columns, such as:

      • INT/INTEGER for integers

      • VARCHAR for variable-length character strings (up to n characters)

      • DATE for date values

      • DECIMAL for numeric values (with precision of M digits total, with D digits after the decimal).

Managing Tables

• Table Existence Management:

  • CREATE TABLE fails if the table already exists.

  • DROP TABLE Statement:

    • Used to delete a table along with all its rows.

    • To avoid failures, use optional clauses:

      • IF NOT EXISTS with CREATE TABLE to ensure no error is raised if the table exists.

      • IF EXISTS with DROP TABLE to safely drop a table that may or may not exist.

Modifying Tables

• ALTER TABLE Statement:

  • Used to add, delete, or modify columns in an existing table.

  • Requires specifying the table name and the clause for alteration.