Databases Revision Cards

A Relational Database is a database consisting of two tables linked together by key fields.

In the example on Card No.1 is a relational database. Two tables from a cinema's database linked together by 'FilmID'.

The tables are linked together by Primary & Foreign Keys.

Primary Key is a key linking directly from the table you are currently viewing to another table. For example, if you are viewing Table 1, the primary key is 'FilmID' in Table 1.

Foreign Key is a key linking from another table you are not currently viewing to the table you are currently viewing. For example, if you are currently viewing Table 1, the foreign key will be 'FilmID' in Table2.

A Database is a persistent store of information. This can be computerised or non-computerised.

Examples of non-computerised databases include;

• Telephone books
• Written school registers

An example of a database is seen above. It shows a relational database of a Cinema's film listings.

A Logical Operator are SQL commands used to interrogate a database. There are three main types of operators:

• Boolean - AND, NOT, OR
• Arithmetic - *, +. /
  
• Comparison - =, <, >

SELECT * FROM Table 2 - Selects all information from Table 2.

SELECT 'Film' OR 'FilmID' FROM Table 1 - selects all data under 'Film' or 'FilmID' from Table 1.

SELECT 'Film' AND 'Genre' FROM Table 1 - selects all data under 'Film' and 'Genre' from Table 1.

A Database Management System (DBMS) is a program used to create, edit, mantain and interrogate a database.

The most common example of a DBMS is Microsoft Office Access.

Although it is costly, it is a sophisticated piece of software designed by intellectual software engineers. It has lots of options and features available to the user, and it is also very user-friendly.

Record - A row of data about one thing. For example, in the database below, the green collumn is a record

Entity - Something about which a database contains information. For example, in the above database, the 'Film' collumn is an entity.

Attribute - A feature of an entity. For example, in the above database, 'Captain America: Civil War' is an attribute of the 'Film' entity.

Query - The interrogation of a database for a certain piece of information. This is usually carried out using Structured Query Language (SQL).

              - SELECT 'Film' FROM Table 1 - this will show all data under the Film entity.

Report - A user-friendly way of representing data in a database.

MODULE - A package of code utilised by developers to add extra functionality to a relational database.

SECURITY - A DBMS must ensure data is kept safe from malicious intent.

CREATION - A DBMS must allow administrators to create data and enter this into the system.

MAINTENANCE - A DBMS must allow administrators to create more, edit, and delete data.

INTEGRITY - A DBMS must ensure data entered is correct, reliable and not redundant.

QUERYING - A DBMS must allow users to search the database using software or SQL etc.

LOGGING CHANGES - A DBMS must maintain a log file of all the changes made to the database.

DATA VALIDATION - A DBMS must ensure the data input is within the set parameters.

These are the main features and purposes of a Database Management System (DBMS).

Data Redundancy occurs when two identical pieces of data occur in two separate tables in a relational database.

As can be seen in the above example, the entire entity 'Genre' has been entered on both tables, resulting in the same data being replicated twice.

To avoid this, we can remove the 'Genre' collumn from any one of the tables (in this case Table 2 would be more logical from the Cinema's point of view).

Data Redundancy is not an advantage for a database. It takes up storage space and makes the database look messy.

A Validation Check is used to ensure data entered is within the parameters set by the administrator. For example, if the minimum letter count is '8', you cannot enter less than 8 letters.

Data Validation does NOT check if data is correct. It only checks if it is the correct format.

TYPE CHECKS - This makes sure a user does not enter an incorrect format. For example, if a user inputs a real value instead of an integer, the type check will flag this an invalid.

PRESENCE CHECK - This makes sure there is something entered into a box.

CHECK DIGITS - A number added at the end of a value in order to detect errors in an identification number.

LENGTH CHECK - This makes sure that a string is not below the minimum amount of characters set, or above the maximum. For example, if a password requires 8 characters as the minimum, the length check will ensure the string entered is 8+ characters.

RANGE CHECKS - This makes sure data input is between the parameters set. For example, for a birth date, the input value cannot be below 1 or above 31, as there are 31 days max in a month.

INPUT MASKS - This makes sure that if there is a mixture of formats available to be entered (e.g. alphabetical and numerical values), these are the only two entered.