The Theory

Answer table - The set of records that is produced after a query (find) request.

Atomic Data - A table where every row has single valued attributes. There is no possibility of a particular attribute such as a movie in a film database containing details of two movies in one particular table row.
Such a table is always found after 1NF is applied.

Atomic Key - A primary key consisting of a single attribute.

Attribute - A property of an object storing a single piece of information about that object.
In a table holding personal employee details items such as forename and surname would be examples of attributes.

Candidate Key - An attribute or set of attributes that meet the criteria for a primary key. Every relation has at least one candidate key (the primary key); some have more than one candidate key.

Cardinality - Describes the nature of relationships that exist between tables in a database system.
These relationships will be in the nature:
One to one (1: 1)
One to Many (1:M)
Many to Many (M:N) - the N is used to show that this instance of many does not have to be the same number as the many represented in the first part of the relationship.

A database that has been normalised to first normal form will have removed the possibility of a many to many relationship being in existence.

Compound Key - A primary key consisting of more than one attribute.

Data Dictionary - A database that defines the basic organisation of a database. It will contain a list of all files in the database, the names and types of each field and validation checks that apply to fields.

Data Domain - In a database, the set of allowed values for a table column (field), for example all positive integers.

Data Redundancy - Redundant data is data that is needlessly stored many times in a database.
Redundant data can increase the storage space of a database and increase the possibility of typographical errors occurring in data entry.
Further problems can occur in adding, deleting, modifying and finding records.

Entity - A thing of significance about which information needs to be stored.
In a databases is a place where similar data is collected and stored.
Often referred to in database terms as a Table.

Entity Integrity - To ensure consistency in a database, it is vital that each table has a primary key which is both unique and non-null. This is entity integrity.

Entity Occurence Modelling - A method of identifying the cardinality of relationships that exist between different tables in a database.

Entity occurrence modelling is only as effective as the data that is available. Insufficient data can hide the whole picture and lead to inaccurate conclusions regarding relationships.
The diagram shown indicates a many to many relationship as each value in the left hand box can have many matches on the right.
The same situation exists from the left hand box to the right.

Entity Relationship Diagram - A graphical representation of the entities and the relationships between them. Entity relationship diagrams are a useful medium to achieve a common understanding of data among users and application developers.

First Normal Form (1NF) - Where multi valued attributes (also known as multi-valued attributes) are removed from an un-normalised entity to a new entity, along with a copy of the primary key.

Foreign Key - A foreign key is an attribute that exists in one table in a database and is the primary key in another related table of the same database.

Multi Valued Attribute - A multi-valued attribute is one which can take more than one value in a single row in a database.
The tabular example below shows 2 rows of a table.
The attributes puppy name, puppy ID, sex and cost can all have many values in any particular row.
Multi-valued attributes can be problematic in producing an efficient, manageable database solution.

Non-Null Primary Key - A non-null primary key is a key which is not permitted to contain a blank value. Where a record exists, the key must have a value.

Normalisation - A series of steps followed to obtain a database design allowing for efficient access and storage of data in a relational database.
These steps reduce data redundancy and the chances of data becoming inconsistent.
There are several different levels of normalisation. In practical terms, it is usual to consider the levels known as First Normal Form (1NF), Second Normal Form (2NF) and Third Normal Form (3NF).

Primary Key - A unique identifier for each row in a database table.
It is usually best if this is an arbitrary identifier for the row, no matter what kind of data is stored.
Using a meaningful identifier, such as a person’s initials, e.g. AS, can provide problems:
Consider a new entry where a new person also has the initials AS.
What if AS changes their names (a woman getting married for instance)?

Referential Integrity - No value of a foreign key attribute can exist unless the subsequent value already exists in a related table where the attribute is the primary key.

Second Normal Form (2NF) - Where partial dependancies are removed to a new entity along with the part of the key upon which the partial dependancy is based.
A partial dependancy is where knowing a single part of a primary key will provide definitive information about other attributes in a database table.

Surrogate Key - A surrogate key is a single attribute, established identifier for an entity.

it is not derived from any data in the database and its only significance is to act as the primary key.

The table here shows a surrogate key, ID, introduced to replace the compound key of first name, initial and surname.

Third Normal Form (3NF) - Where non-key dependancies are removed to a new entity.
A non-key dependancy is where knowing the value of an attribute that does not form a part of the primary key of a databse table will provide definitive information about other attributes in a database table.

Comments?

Any content, missing or wrong, drop us a note. Or if you just want to be nice, feel free to chat!

blog comments powered by Disqus