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!)
SELECT * FROM Songs WHERE (Artist = 'Rolling Stones') OR (Artist = 'Stone Roses')
SELECT * From Songs WHERE (Artist = 'Led Zepplin') AND (Title = 'Black Dog')
SELECT * FROM Songs WHERE (Artist = 'Led Zepplin') AND NOT (Title = 'Stairway to Heaven')
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.
The diagram shows the relationships between entities to be incorporated in the database.
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:
|Type Check||Ensures that data entered is of the correct type||Rejecting non-numeric characters when entering a persons age.|
|Range Check||Ensures that data entered falls between a specific range||Rejecting numbers that are not between 0 and 100 when entering a percentage.|
|Length Check||Ensures that data entered has a specific number of characters||Checking that all digits of a telephone number or post code have been entered.|
|Presence Check||Ensures that some data has been entered (field cannot be left blank)||Checking that an email address has been entered when completing a contact form.|
|Format Check||Ensures that data matches a specific format||e.g. postcode would have the format: LL## #LL (Letters & # numbers)|