Database design and development

Functional Dependency

Means an attribute or column depends on exactly one other unique attribute or set of attributes. The unique attribute is a single column natural key chosen as a primary key, while the unique set of attributes is a compound natural key, likewise chosen as a primary key. You write the functional dependency: A → B Columns that have a mandatory reliance on another column or set of columns are typically a non-key column or set of columns on a primary key found in the current row. A foreign key column is also functionally dependent on a primary key in the current table or other table.

Feild

Describes a column in a table. It comes from file systems, which predate databases. A field is either a positionally fixed or delimited element in a list of values. Fields are always found but may be null or empty values.

Attribute

Describes a column in a table. It comes from relational algebra, where a column is an attribute and a row is a tuple.

Record

Describes a horizontal element in a table. It comes from file systems, which predate databases. A record is a row of data, or an instance of a defined data structure. As such, it is row inside a file.

Row

Describes a horizontal element in a table. It comes from spreadsheets, where a row defines the horizontal axis of data. A row is also an instance of the data structure defined by a table definition, and the nested array of a structure inside an ordinary array.

tuple

Describes a row in a table. It comes from relational algebra, where a column is an attribute and a row is a tuple.

Column

Describes a vertical element in a table. It comes from spreadsheets, where a column defines the vertical axis of data. A column is a single element of a data structure that is found in every row. Columns always have a value in a data structure when the structure constrains its creation to demand one. Database let you allow or disallow a null value when you create a table (or structure).

Data Structure

Describes the definition of a type of data, like an integer or string and the collection of a group of various pre-defined data types into a group. The latter is the best corollary to a record in a file system, or a row in a database. You can make a numerically indexed array of any base data type (often described as scalar or primitive), or compound data type, which effectively creates a 2-dimensional structure known as a database table.

File System

Describes the use of files as a data repository, where each file contains rows of data organized as data structures. Procedural programming languages access the files based the programmer's knowledge of their definitions, which is normally maintained in a definitional file or document.

Primary Key

A candidate key that you chose to serve as the primary key.

unique key

A column or set of columns that uniquely identifies a row of data.

non-key

A column that contains a descriptive value that doesn't identify or help identify a row as unique but provides a characteristic to a row of data. All non-key columns should have a full functional dependency on the natural key, or primary key.

Key

A column that contains a value that identifies or helps in conjunction with other key columns to identify a row as unique.

transitive dependancy

A column that depends on another column before relying on the primary key of the table. It may exist in tables with three or more columns that are in second normal form.

user defined key

A data type defined by the user in a schema (Oracle) or database (MySQL and Microsoft SQL Server).

Anomaly

A deviation from the common rule, type, arrangement, or form; or an incongruity or inconsistency.

Superkey

A key that identifies a set of rows, like a gender column that lets you identify male or females in your data model.

surrogate key

A key that identifies uniqueness for rows, like an automatic numbering sequence. It is superior solution to a natural key because you create indexes by using the surrogate key column followed by the primary key column(s). If you discover more about the domain later and need to add a column to the natural key, you need only drop the index and recreate with the new list of columns.

Compound Key

A key that is made up of two or more columns. It is possible that this term can be applied to many different keys, and that it is interchangeable with a composite key. This is typically the more widely used word.

Composite Key

A key that is made up of two or more columns. It is possible that this term can be applied to many different keys, and that it is interchangeable with a compound key. You will see compound key used more frequently.

Foreign Key

A key that maps to a value in a primary key list, where the list exists in the same or another table.

non-specific relationship

A logical reciprocal set of relations between two things where no row in either set has a possible intersection with the other. A non-specific relationship is also known as a binary many-to-many relationship. These are logical relationships that convert to two physical relationships known as specific relationships. Specific relationships are either one-to-one or one-to-many binary relationships. Non-specific relationships are resolved by two one-to-many relationships and an association set. The association set holds rows of foreign keys that point respectively to both sets. Each row in the association table lets you resolve the relationship between a row in one and a row in the other through an INNER JOIN.

N-ary Relationship

A non-specific relationship between three or more tables, where one row in one table may have a many-to-many-to-many relationship between one or both of the other tables. You map these three or more tables by using another table that holds a foreign key from all of them in the same row. The other table is known as an association or translation table. Typically, all of the original tables have a one-to-many relationship to the association table, and all relationships resolve through the association table.

Many-to-many

A non-specific relationship between two tables, where one row in one table may map to one to many rows in the other and vice versa. You map these two tables by using a third table that holds a foreign key from both in the same row. The third table is known as an association or translation table. Both of the original tables have a one to many relationship to the association table, and both relationships resolve through the association table.

Partial Dependency

A partial dependency exists when the primary key is a compound key of two or more columns, and one or more columns depends on less than all of the columns in the compound primary key.

Specific relationship

A reciprocal set of relations between two things where one row in a result set finds one row in another result set. Another example is where one row in a result set finds many matches in another result set. These binary relationships are respectively one-to-one and one-to-many. Specified relationships have equijoin or non-equijoin resolution. The first matches values, like the process in a nested loop, and the second matches values through a range or inequality relationship. Equijoins typically have a primary and foreign key, and the one-side holds the primary key while the many side holds a foreign key. In the specialized case of a one-to-one relationship, you must choose which table holds the primary key that becomes a functional dependency as a foreign key in the other.

Binary Relationship

A reciprocal set of relations between two things, in databases the two things are tables, views (semi-permanent result sets), or temporary result sets..

one-to-one

A specific relationship between two table, where one row in one table maps to one and only one row in another table. You map these two tables by using the primary key of one table as a foreign key in the other. This makes the table that holds the foreign key functionally dependent on the primary key in the other table. While a one-to-one relationship allows you to choose either as the independent row, it is important that you identify the business relationship of the two tables and make the primary task element the independent row. The independent row donates a copy of its primary key to the dependent row.

One-to-many

A specific relationship between two table, where one row in one table maps to one to many rows in another table. You map these two tables by using the primary key of one table as a foreign key in the other. This makes the table that holds the foreign key functionally dependent on the primary key in the other table. The one side of the relationship is always the independent row, and it always donates a copy of its primary key to the dependent row.

Natural Key

A unique key that identifies a row of data, or instance of data. A natural key is automatically a candidate key that you may choose as a primary key. All other columns in the table should enjoy a direct and full functional dependency on the natural key. If you adopt a surrogate key for joins, the surrogate key plus the natural key should become a unique index to speed searches through the table.

Nominated key

A unique key that you may choose as a primary key, and it is also known as a candidate key. The only subtle difference that I've found is that some people use nominated to indicate the candidate key they've tentatively chosen before making a final decision.

Candidate key

A unique key that you may choose as a primary key.

Object instance

An object instance is a data set inserted into a defined object type. This can occur at runtime, or in the context of databases through an INSERT statement. An Oracle database may contain nested object instances when a column relies on an object type, which are known as standalone objects.

Object type

An object type in the context of a database is a data structure, or the definition of a table. Definitions of tables are stored in the database catalog and built upon pre-existing data types. Some databases support User-Defined Types (UDT). Where UDTs are available the data structure may use them when they're defined before the object type. Object types are a generalization of tables user-defined types in an Oracle database.