Relational Databases

Logical Operators to Query a Database

Database interrogation involves searching the database for information (as explained earlier). Below are some examples of ways in which a DB can be queried using logical operators (think back to Binary Logic... the same rules apply!)

The OR operator

SELECT * FROM Songs WHERE (Artist = 'Rolling Stones') OR (Artist = 'Stone Roses')

The AND operator

SELECT * From Songs WHERE (Artist = 'Led Zepplin') AND (Title = 'Black Dog')

The NOT operator

SELECT * FROM Songs WHERE (Artist = 'Led Zepplin') AND NOT (Title = 'Stairway to Heaven')

Relationships

Relational databases are made up of two or more linked tables.

In order to link two tables together, they must share a common attribute. To do this, the primary key from one table must also appear in the table it is linked to. This attribute is known as a foreign key.

In this example, SongID and PlayListID are both foreign keys which enable the tables Songs and Playlists to be linked together via a third table, SongsOnPlaylist.

Creating relationships allows us to generate queries that can select related data from a number of tables that are all linked together.

This is an important concept because it means that most data (apart from the linked fields) is stored only once. This reduces data redundancy and ensures that users only see the one up-to-date copy of the data.

Example of an Entity Relationship Diagram

The diagram shows the relationships between entities to be incorporated in the database.

Foriegn Key

Input Validation

A database is no use if it contains incorrect data. As far as possible, it is best to avoid inputting invalid data in the first place. Most mistakes can be prevented by setting the software to reject certain values that are unacceptable. This happens at the time of input.

Data validation aims to make sure that data is sensible, reasonable, complete and within acceptable boundaries.

Typical Validation Checks: