column (data element) contains data of one and the same kind, for example the column postcode.
A compound key
is a key
that consists of multiple columns, because one
column is not sufficiently unique.
Example of a compound key: a Dutch postcode & house number jointly identify an address in the Netherlands.
A database is a collection of tables, with related data.
A foreign key
is the linking pin between two tables.
Given a value from a row in one
you can access another table to find the right
with related data.
So, one table gives the key for the other, 'foreign' table.
A high frequency function is a function that is used very often, hundreds or even thousands times per day.
An index in a database resembles an index at the back of a book.
Using a keyword you can find the right page number of
With a join
to one big temporary result table.
For instance, make an overview of readers, with the name of the newspapers that they are subscribed to.
A key consists of one or more
If you know the right key value you can find the right
with the desired information.
The key is like a real world key, that gives you access to the right row.
A key is unique.
A key value can not occur twice in one
With a key you can find at most one row.
Many to many relations are everywhere.
One newspaper has many readers for example, and one reader reads many newspapers.
A many-to-many relation is unclear.
Many-to-many relations often are a sign that further analysis is required.
Most of the time the relation can be made a lot clearer, by adding a link table, at the 'top' or the 'bottom'.
For a free house-to-house magazine the link to the reader will be the area, at the top.
For a paid newspaper, the link is the subsription, at the bottom.
A Many to One relation is the same as
but from a different viewpoint.
- Many readers live in one area.
- Many subscriptions can be of one and the same reader.
- Many subscriptions are for one and the same newspaper.
Most relations between tables are one-to-many.
- One area can be the habitat of many readers.
- One reader can have many subscriptions.
- One newspaper can have many subscriptions.
relation is exception in databases.
It can occur, but it's often a sign that the database design has opportunities for improvement.
Relationshipal DataBase Management System
is software that
- enables you to
- Guarantees the
- Updates the indexes automatically.
- Interprets an SQL query and combines information from various tables.
Storing data twice, redundantly to make the system faster.
Which data to store twice is a subject in
the chapter Denormalise.
Referential Integrity makes sure that a
value always points to an existing
A "dead" link as shown in the picture can not happen anymore.
A number of rules can enforce referential integrity.
In addition the database designer chooses an action for delete:
- When creating or updating a row the system checks whether the foreign keys have valid values.
- It's only possible to delete a row in the one-table when there a no more related many-rows.
- When deleting a row the RDBMS
automatically deletes the related data in the many table.
This is called a cascaded delete.
- When deleting the last 'many' the RDBMS
automatically deletes the related 'one' row.
(= tuple, entry)
is a group of related data, for example the data of one subscription.
One row can not contain lists.
A table is a matrix with data.
A table in a database looks like a simple spreadsheet.
The example above has 3
Looking for other database terms?
Have a look at the following web sites: