Database Terms

#

 * 0:0
 * zero-to-zero cardinality - LINK - This is a 0:0 optional relationship basically stating that a person can occupy one parking space, that I don't need a person to have a space and I don't need a space to have a person. Although the concept is fairly simple, a database can't express it directly. You would need to nominate one entity to become the dominant table and use triggers or programs to limit the number of related records in the other table.


 * 0:M (Mandatory on the many side)
 * zero-to-many cardinality - Characteristic - This is a 0:M relationship that is mandatory on the many side. It indicates that a person must have at least one name, but possibly many names, and that a name might be assigned to a person (might not) but at most to one person. In a database you would have the the name table with a nullable foreign key to the person table and triggers or programs to force a person to have at least one name.


 * 0:M (Optional on both sides)
 * zero-to-many cardinality - Possession - This is a 0:M (zero to many) optional relationship indicating that a person might have no phone, one phone or lots of phones, and that a phone might be un-owned, but can only be owned by a maximum of one person. This is implemented in a database as a nullable foreign key column in the phone table that references the person table.


 * 1:0
 * one-to-zero cardinality - SubType - This is a 1:0 relationship; optional only on one side. This would indicate that a person might be a programmer, but a programmer must be a person. It is assumed that the mandatory side of the relationship is the dominant. Again, triggers or programs must be used to control the database.


 * 1:1
 * one-to-one cardinality - Physical Segment - This is a 1:1 mandatory relationship and demonstrates a segmentation denormalization. A person must have one and only one DNA pattern and that pattern must apply to one and only one person. This is difficult to implement in a database, since declarative referential integrity will get caught in a "Chicken and the Egg" situation. Basically, this is a single entity.


 * 1:M (mandatory on both sides)
 * one-to-many cardinality - Paradox - This is a 1:M relationship mandatory on both sides. As with the physical segment situation, the "Chicken and the Egg" is involved since you have to have a person to have citizenship, but citizenship to have a person.


 * 1:M (mandatory on the 1 side)
 * one-to-many cardinality - Child - This is a 1:M mandatory relationship, the most common one seen in databases. A person might be a member or might not, but could be found multiple times (if the member entity represents membership in multiple clubs, for instance). A member must be a person, no questions asked. The foreign key in the member table would be mandatory, or not-null.


 * 1NF First Normal Form
 * Eliminate repeating groups - Make a separate table for each set of related attributes, and give each table a primary key. - see Normalization


 * 2NF Second Normal Form
 * Eliminate redundant data - If an attribute depends on only part of a multi-valued key, remove it to a separate table. - see Normalization


 * 3NF Third Normal Form
 * Eliminate columns not dependent on key - If attributes do not contribute to a description of the key, remove them to a separate table. - see Normalization


 * 4NF Fourth Normal Form
 * Isolate independent multiple relationships - see Normalization


 * 5NF Fifth Normal Form
 * Isolate independent multiple relationships - No table may contain two or more 1:M or M:M relationships that are not directly related. - see Normalization

A-B-C

 * Alternate Key
 * Isolate Semantically Related Multiple Relationships - There may be practical constrains on information that justify separating logically related M:M relationships.


 * Analysis and Design
 * The goal of Systems Analysis is to capture an accurate representation of business processes and user's perceptions of them. Logical design of the information system including identification of databases (tables, columns, keys, indexes) that will store required data and applications (forms, reports, menus) that will operate on the database.


 * Artificial Key
 * An artificial key is one that has no meaning to the business or organization. Artificial keys are used when 1) no attribute has all the primary key properties, or 2) the primary key is large and complex, or 3) all candidate keys are subject to updates


 * Associative Table
 * An associative table is not allowed without one or more Main Tables.


 * Attribute
 * Each column of a table represents an attribute. Attributes are data items that describe an entity. An attribute instance is a single value of an attribute for an instance of an entity.


 * BCNF Boyce-Codd Normal Form
 * If there are non-trivial dependencies between candidate key attributes, separate them out into distinct tables.Falls between 3NF and 4NF - see Normalization


 * Candidate Key
 * A candidate key is a minimal set of columns necessary to identify a row, this is also called a minimal superkey


 * Cardinality
 * numeric realtionship between entities, for example one-to-one


 * Compound Key
 * a compound key (also called a composite key or concatenated key) is a key that consists of 2 or more attributes


 * Conceptual Data Model
 * High level business model showing entities and relationships without keys, cardinality, or attributes.

D-E-F

 * Data Integrity Rules


 * Data Objects and Relationships
 * Identifying Data Objects and Relationships during requirements analysis


 * DBM Database Management Systems


 * Entity
 * Represented by a main table


 * E-R
 * Entity-Relationship


 * ERD
 * E-R Diagram


 * FK
 * see Foreign Key


 * Flexible Database Model


 * Foreign Key
 * - A foreign key in one table refers to the primary key (PK) of another table.
 * - A foreign key is an attribute that completes a relationship by identifying the parent entity. Foreign keys provide a method for maintaining integrity in the data (called referential integrity) and for navigating between different instances of an entity. Every relationship in the model must be supported by a foreign key.

G-H-I

 * Generalization
 * Process of categorizing entities by their similarities and differences


 * Generalization Hierarchy
 * A structured grouping of entities that share common attributes. It is a powerful and widely used method for representing common characteristics among entities while preserving their differences. It is the relationship between an entity and one or more refined versions. The entity being refined is called the supertype and each refined version is called the subtype.
 * In an overlapping hierarchy an entity instance can be part of multiple subtypes.
 * In a disjoint hierarchy, an entity instance can be in only one subtype.
 * Subtypes may be the parent entity in a relationship but not the child. If this were allowed, the subtype would inherit two primary keys.


 * Glossary
 * Other database design glossarys

J-K-L

 * Key
 * A value used to lookup a record, may be unique or non-unique


 * Logical Data Model
 * An extension of the Conceptual Data Model. Normalized entity relationship model with main tables, associative tables, keys, cardinality, business contraints, and main attributes.

M-N-O

 * M:M
 * many-to-many cardinality - Association - This is a M:M (many to many) optional relationship. Conceptually, it means that a person might or might not work for an employer, but could certainly moonlight for multiple companies. An employer might have no employees, but could have any number of them. Again, not hard to visualize, but hard to implement. Most solutions of this situation involve creating a third "Associative Entity" to resolve the M:M into two 0:M relationships. This might be an entity called employee because it does link the person to the employer the person works for.


 * Main Table
 * an entity - records can exist in a main table without the presence of other tables, unless a foreign key is required.


 * Name conventions
 * Table and attribute names are all lower case. Words are separated by hyphens, not spaces or underscores. Associative table names are formed by linking table names with hyphens, for example animal-person.


 * Natural Key
 * a key that has a real world value, like social security number


 * Normalization
 * Rules of data normalization - see references


 * NULL
 * Unknown value


 * ORM Object Role Modeling

P-Q-R

 * Physical Data Model
 * De-normalized version of the Logical Data Model with all required attributes and attribute domains needed to implement in the target database engine. May or may not include all the entities defined in the Logical Data Model.


 * PK
 * see Primary Key


 * Primary Key
 * A value that can be used to identify a unique row in a table
 * The primary key is an attribute or a set of attributes that uniquely identify a specific instance of an entity. Every entity in the data model must have a primary key whose values uniquely identify instances of the entity.


 * Primary Key Migration
 * Dependent entities, entities that depend on the existence of another entity for their identification, inherit the entire primary key from the parent entity. Every entity within a generalization hierarchy inherits the primary key of the root generic entity.


 * Record
 * One row in a table and associated rows in other tables.


 * Recursive relationship
 * A recursive relationship is an entity associated with itself. Example - An employee may manage many employees and each employee is managed by one employee.


 * Referential Integrity
 * Referential integrity means that if a row in a table has a pointer to a row in another table, the row in the table that is pointed at, must be sound (exist). Said another way, you should not remove a row which contains information that other row(s) depend on.


 * Relational Data Manipulation
 * Union, Difference, Intersection, Product, Projection, Selection, Join, Division


 * Relationships and Keys
 * see diagram illustration

S-T-U

 * Superkey
 * a superkey is a set of columns within a table whose values can be used to uniquely identify a row


 * Tuple
 * In a relational database, a tuple is one record (one row in a table)


 * Unique Key
 * a unique key refers to the attribute which is unique for that column of the table - a unique value is often an integer provided by the database but can be a natural key such as SSN.

Internal links

 * Database