User Tools

Site Tools


database:terms

Database terms

Definitions / Rules

  1. Entity ←→ Database Table.
    Naming convention: singular, uppercase
  2. Instance of an entity ←→ Database Row (or Tuple or Record)
  3. Attribute ←→ Database Column (or Field).
    Naming convention: singular, lowercase
  4. Relationship = Link between two entities, represents a business rule and can be expressed as a verb phrase.
  5. Primary key = an attribute that uniquely identify each instance of an entity.

Relationship

  1. “one-to-many” relationship: one instance of the parent entity relates to many instances of the child entity.
  2. “many-to-many” relationship: many instances of one entity relate to many instances of the other entity.
    Need to be resolved (to avoid data redundancy): create an intermediate entity known as a cross-reference (or XREF) entity. The XREF entity is made up of the primary keys from both of the two original entities. Both of the two original entities become parent entities of the XREF entity. Thus, the “many-to-many” relationship becomes resolved as two “one-to-many” relationships.
  3. Relationships are either “identifying” or “non-identifying”. Identifying relationships exist when the primary key of the parent entity is included in the primary key of the child entity. On the other hand, a non-identifying relationship exists when the primary key of the parent entity is included in the child entity but not as part of the child entity's primary key. In addition, non-identifying relationships may be further classified as being either “mandatory” or “non-mandatory”. A mandatory non-identifying relationship exists when the value in the child table cannot be null. On the other hand, a non-mandatory non-identifying relationship exists when the value in the child table can be null.

Keys

  1. Primary key = an attribute that uniquely identifies each instance of an entity.
    - cannot be null
    - immutable: cannot be changed
    - shall contain no semantic meaning
    Note: efficiency (INT vs CHAR)
  2. Super key = Combination of attributes leading to a primary key
  3. Foreign key = primary key of a parent entity in a child entity (reference)
  4. Surrogate key
  5. Candidate key

Referential integrity

A foreign key requires that values must be present in the parent entity before like values may be inserted in the child entity.

Cardinality

  1. Cardinality of the relationship between the child entity and the parent entity. The cardinality of a relationship may be determined by asking the following question: “How many instances of the child entity relate to each instance of the parent entity?”
  2. There are four types of cardinality:
    1. One to zero or more (common cardinality)
    2. One to one or more (P cardinality)
    3. One to zero or one (Z cardinality)
    4. One to exactly N (N cardinality).

Normalization:

  1. minimize data redundancy 
  2. minimize data restructuring 
  3. minimize I/O by reducing transaction sizes
  4. enforce referential integrity

Normal Form:

  1. First normal form (1NF): very basic rules:
    1. Eliminate duplicative columns from the same table.
    2. Create separate tables for each group of related data and identify each row with a unique column or set of columns (the primary key).
  2. Second normal form (2NF): remove duplicative data:
    1. Meet all the requirements of 1NF.
    2. Remove subsets of data that apply to multiple rows of a table and place them in separate tables.
    3. Create relationships between these new tables and their predecessors through the use of foreign keys.
  3. Third normal form (3NF):
    1. Meet all the requirements of the second normal form.
    2. Remove columns that are not dependent upon the primary key.
  4. Boyce-Codd NormalForm (BCNF or 3.5NF):
    1. Meet all the requirements of the 3NF.
    2. Every determinant must be a candidate key.
  5. Fourth NormalForm (4NF):
    1. Meet all the requirements of the 3NF.
    2. Relationships have no multi-valued dependencies.
  6. Fifth normal form (5NF), Project-join normal form (PJ/NF) designed to reduce redundancy in relational databases recording multi-valued facts by isolating semantically related multiple relationships.
    1. Meet all the requirements of the 4NF.
    2. Every join dependency is implied by the candidate keys.

 

Null & 3VL (Three value Logic)

 

 

 

 

database/terms.txt · Last modified: 2015/07/26 14:13 by jaap.dehaan