Basic Terms
A database is a collection of tables.
Each table contains records, which are the horizontal rows in the table. These are also called tuples.
Each record contains fields, which are the vertical columns of the table. These are also called attributes.
A key is a logical way to access a record in a table. A key that uniquely identifies a record is called a primary key.
An index is a physical mechanism that improves the performance of a database. Indexes are often confused with keys. However, strictly speaking they are part of the physical structure, while keys are part of the logical structure.
A view is a virtual table made up of a subset of the actual tables.
A one-to-one (1:1) relationship occurs where, for each instance of table A, only one instance of table B exists, and vice-versa. For example, each vehicle registration is associated with only one engine number, and vice-versa.
A one-to-many (1:m) relationship is where, for each instance of table A, many instances of the table B exist, but for each instance of table B, only once instance of table A exists. For example, for each artist, there are many paintings. Since it is a one-to-many relationship, and not many-to-many, in this case each painting can only have been painted by one artist.
A many to many (m:n) relationship occurs where, for each instance of table A, there are many instances of table B, and for each instance of table B, there are many instances of the table A. For example, a poetry anthology can have many authors, and each author can appear in many poetry anthologies.
A mandatory relationship exists where, for each instance of table A, one or more instances of table B must exist.
An optional relationship is where, for each instance of table A, there may exist instances of table B.
Data integrity describes the accuracy, validity and consistency of data.
Database normalization is a technique that helps us to reduce the occurrence of data anomalies and poor data integrity.
A candidate key is any field, or combination of fields, that uniquely identifies a record.
A primary key is the candidate key that has been chosen to identify unique records in a particular table.
Foreign keys allow us to ensure what is called referential integrity. This means that if a foreign key contains a value, the value must refer to an existing record in the related table. Foreign keys also allow what are called cascading deletes and updates.
The delete “cascades” through the relevant tables, removing all relevant records. Foreign keys can contain null values if the relationship is optional, which indicates that no relationship exists. If the relationship is mandatory, the foreign key cannot contain nulls.
Views are often used for security purposes.
From http://www.databasejournal.com/sqletc/article.php/26861_1469521_1
