Database: Design: Relational Database Terminology

Taken from http://www.fredosaurus.com/notes-db/design/terminology.html

A relational database requires that all data be represented in rectangular tables. This rigid structure will be natural for some problems and awkward for others, but after learning a few techniques for representing typical structures, it will feel quite natural. There are usually many ways to represent data in tables, some of which work better than others. The following explains some of the basic terminology, but not how to use it.

Tables

• Terminology: Tables are also known as files, entity sets, or relations, depending on the writer. However, the most popular term remains table.
• Tables - You need to represent all of your data in tables. Each table represents a set of only one kind of thing, an entity), with its (non-repeating) attributes. A common way to name each table is with a plural noun name, eg, Customers, Products, Aircraft, ....
• Rectangular. Tables are rectangular, each row representing one entity, and each column representing one attribute.
• Like spreadsheet. You can think of each table as like a spreadsheet, but there are some important differences.

Rows

• Terminology. Rows are also known as records, entities, or tuples.
• Unique. Each row must be unique. You must not have two rows that are identical.
• Unordered. The order of the rows is irrelevant to the functioning of the database. However, they may be ordered for display.

Columns

• Terminology: Columns are also known as attributes or fields. Each field has a name, ie, they are not numbered.
• Data associated with an entity is stored in the fields of a table row. However, some of the data associated with an entity may be stored in separate tables with a way to connect them. Because tables are a fixed rectangular size, the most obvious type of data that doesn't fit is repeating fields. These can not be stored in the same table as the rest of the entity attributes. There is a simple way of solving this problem, which we'll look at in a bit.
• Naming. Column names should be descriptive. They are optionally prefixed with the name of the table (in the singular) followed by an underscore (_). Where possible, foreign keys should have the same name as the primary key they refer to (not possible if there are two foreign keys which reference the same primary key).

Some columns are keys

• Primary key. Every record typically has a field, or group of fields forming a composite key, that uniquely identifies it. This is called the primary key. Typically all tables have a primary key. The primary key is how that record is identified by foreign keys in records in other tables. Primary keys must be unique and non-null. To guarantee uniqueness, they are often arbitrary numbers. Primary keys are usually indexed, which is simply a physical database implementation to make it quick to find a record given a key. Primary keys are sometimes marked with "PK" in relational schema diagrams.

  A typical name for a key is the table name followed by "_id" if it is an arbitrary number or "_code" if it is human readable. Other names are possible.

• Foreign key. A foreign key is a field in one record that has the value of a primary key in another record, usually in another table. This corresponds to a pointer or reference in programming languages. Foreign keys are sometimes marked with "FK" in relational schema diagrams.

A column has a domain (datatype)

• Terminology: A datatype is also called an attribute domain. The domain often includes the possible values in addition to what is commonly thought of as a datatype.
• Atomic value. Each field should have one data value that is an atomic value, ie, one that has no subparts that are used by the database. This is an ideal, but of course there may be times when the subparts of a string may be referenced.
• Datatypes. Databases support many predefined datatypes (integer, string, floating-point, date, boolean, ...). All values in a column have the same datatype.