What is a Table in SQL?

Data System Architecture

About

A table is:

A query is also a relation but does not store any data

The name comes from the counting table (abacus) that was found in the medieval counting houses.

Counting Table

Type

In the data model, tables may have different purpose such as:

  • Staging tables to store temporary data
  • Auxiliary tables to store metadata

Management

How to list them?

You can list the tables of your database via the information schema.

Example with

  • Sql Server
SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE'
  • Sqlite, the information schema is the sqlite_master table.
SELECT name FROM sqlite_master WHERE type='table';

Creation

You can create tables via for database:

  • without SQL, via their library API, they should have a create function
  • with SQL support,
    • with the create table statement to have only the structure
    • with a create as statement to create a table from a query (ie values)

Example:

CREATE TABLE Employees (
    EmployeeID INT,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    Department VARCHAR(50),
    HireDate DATE
);

Insertion

You insert data into database table:

  • without SQL, via their library API, they should have a insert function
  • with SQL support, via the insert statement and the values statement.

Cardinality

The cardinality of a table is the size of a table, the name comes from the set theory.

Split For Data Loading

Even if it's possible to get all data in one table that has the same grain (unique key), you may split the columns that are not functionally related into 2 different tables

Why?

  • because you get minder dependency. You can change your table and your process independently. For instance, the test data set doesn't need to be updated to reflect the changes of a second functional process.
  • You can then set columns NULLABLE. If a process updates columns after another one (such as total calculation), the value of this column is unknown for the first process and the columns must be set as not nullable.
  • You can add audit columns by process (Update and create (date|user))

Why not?

  • You may get consistency problems. There may be not a one-to-one relationship. But as the two functional processes are independent, it may be not always needed.
  • Performance: You add a join cost

1)

Model as stream

A table is a stream of:

  • data manipulation:
    • DDL (create, alter, …)
    • DML (insert, update, …)
  • with an infinite windows
  • that you will find persisted in a write-ahead log (queue)





Discover More
Relational Data Model
(Relation|Table) - Tabular data

A Relation is a logical data structure composed of tuple (row) attribute (column, field) The following data structure are a relation: a table, a materialized view (query) (store data) a query,...
Data System Architecture
(Relation|Table) - Tabular data

This section is based on the relation data structure must well known under the term of table. The system that manages this structure are called Relational databases (or RDMS) . They are founded on Set...
Data System Architecture
A [[https://combostrap.com/frontmatter|frontmatter]] title shown on the Search Engine Result Pages

A [[https://combostrap.com/frontmatter|frontmatter]] description shown on the Search Engine Result Pages
Venn Diagram
Collection - Set

A set is: a data structure of the set theory a collection ofdistinct objects (then without duplicate) an unordered collection of objects The objects element of the set have the same type (the type...
Star Schema
Dimensional Data Modeling - OLAP Cube

An OLAP cube is a dimensional data structure that answer dimensional query. It store data in a multidimensional way. You can a cube as an extension of the two dimensions relational table. A cube is...
Card Puncher Data Processing
IO - unit of storage

Unit of storage of data. See also : The smallest unit of computer storage is a byte, which represents 8 bits. Then a block contains byte Then, the following data storage structure are made...
Data System Architecture
Logical Data Modeling - Scope

The scope is a (depth|level) of a hierarchical namespace. As namespace, the scope groups name but at a hierarchical level. the global scope of a linux file system is / the local scope for a file...
Data System Architecture
Multidimensional Data (Cube)

A cube can be thought of as extensions to the two-dimensional array of a spreadsheet. A cube is also known as: a multidimensional cube or a hypercube It consists of numeric facts called measures...
Data System Architecture
Relational Data Modeling

This section is data Modeling applied to a relational structure (mostly table). Relational databases normalize data, removing as much duplication as possible. A relational database’s approach to...
Data System Architecture
Relational Data Modeling - (Xref|Cross-reference|Lookup) table

A lookup table is a table that is also known as: XREF or cross-reference table A lookup is when a query joins the base table and the lookup table to obtain the values of the lookup table for each...



Share this page:
Follow us:
Task Runner