# Database: Terms

Useful database terms. Most aren't defined here, but you should know these.

## Common DBMSs

Access | A popular desktop DBMS by Microsoft.
DB2 | A major DBMS from IBM.
MySQL | A popular, open-source, RDBMS.
Oracle | Perhaps the most extensive (and expensive) (OR)DBMS.
PostgreSQL | A popular, open-source, ORDBMS.
SQL Server | A RDBMS by Microsoft for servers.

## Types of DBMSs

DBMS | DataBase Management System
hierarchical DBMS |
network DBMS |
ODBMS | Object-Oriented DBMS (also OODBMS).
OODBMS | Object-Oriented DBMS (also ODBMS).
ORDBMS | Object-Relational DBMS. A hybrid of the two technologies.
RDBMS | Relational DBMS. This is the principle type of DBMS in use today.

## Modeling, Diagramming, and Notation

crow's foot notation | A system of symbols for indicating multiplicity in relational diagrams.
ERD | Entity Relation Diagram.
cardinality | Same as multiplicity.
connectivity | Same as cardinality or multiplicity. A few authors distinguish between these terms, but many don't.
many-to-many | Many-to-many relationships can not be directly represented in a relational database, but are replaced by a bridging table.
multiplicity | Same as cardinality.
one-to-many | The most common type of relation where many foreign key values can reference a primary key.
one-to-one | For each primary key there is at most one corresponding foreign key in a referencing table. Commonly used to implement subset relationships or lookup tables.
participation | optional or mandatory.
recursive | A relationship between a table and itself. Same as self-referential. Commonly used for a part-of relationship or a tree hierarchy.
relational diagram | Same as relational schema.
relational schema | Same as relational diagram.

## Structure

attribute | Same as column.
bridge table | A table needed to implement a M:N relationship between two other tables in a RDBMS. Typically has a composite key using the primary keys of the two other tables.
calculated attribute | Same as derived attribute
composite key | A key composed of more than one field.
data type | Same as domain.
derived attribute | Same as calculated attribute
domain | The possible values that an attribute may have, eg, INTEGER. Same as data type.
entity | Entity has two interpretations. It can mean a record or a table. It has the table meaning in Entity Relation Diagram and bridge entity.
file | Same as table.
foreign key | A field which links to the primary key.
index | A file used internally by the database to speed access to records. An index is usually generated automatically for primary and foreign keys. A user may request other fields that are searched to be indexed.
key | A value which is used to link two or more tables.
NULL | Value often used to indicate optional, missing, or unknown value.
primary key | The unique value which distinguishes every record.
record | Same as row, tuple.
row | Same as record, tuple.
table | Same as file.
tuple | Same as record, row.

## Searching, Joining, Manipulating, etc

Cartesian product | Same as cross product.
cross product | Table formed by joining all possible combinations of the rows of two other tables. This is the basis for most joins (default or inner join). Same as Cartesian product.
DDL | Data Definition Language - a language used to define tables, etc. SQL is an example of a DDL.
DML | Data Manipulation Language - a language used to access a database. SQL is an example of a DML.
join | To combine two or more tables.
query | A search of a database. In SQL the SELECT command is used for queries.
SQL | Structured Query Language. Pronounced either ess-queue-ell or sequel

## Misc

DBA | DataBase Administrator
data dictionary | The metadata about a database. This is, of course, kept in a database, often in the system catalog.
metadata | Data about a database, eg the names of the tables and attributes, the attribute domains, etc. This information is typically in the data dictionary.
referential integrity | For every foreign key value there must be a corresponding primary key value.
system catalog | Metadata database which has information about all databases in DBMS.