Week 7 - Databases

The original file approach was slow and cumbersome with risk of error high, often resulting in duplicated information. The next database management system (DBMS) was developed using a centralised approach. Data is stored once and centrally administrated.

The DBMS acts as the interface between the users and the data. It provides improved security via the Administrator who can grant or revoke rights. It provides a comprehensive collection of data instead of multiple files and also offers support via recovery routines and centralised back-up

The Relational Data Model consists of two dimensional rows and columns (columns sometimes called field or attributes) one of these is generally assigned the primary key and no two records can contain the same info.

Most DBMSs can automatically generate a numerical primary key thereby avoiding problems such as same surname. Tables can be related to each other. In referencing the primary key in another table, this is then called the foreign key. This acts as an index.
The most common language used for DBMS is SQL (Structured Query Language) Here’s a simple example of a table depicting the structure of our business within the National Policing Improvement Agency (NPIA), ‘created’ in SQL.

Create table Business Units (Bus_Unit_No int primary key, Bus_ Name char [32], Bus_Dir char[32], Location char [16] ) ;

Insert into Business Unit value (1, ‘Training’, ‘Professional Development’, ‘London’) ;

SELECT statement begins the query:
SELECT * (selecting all table)
FROM Business Units
WHERE Bus_Unit = 1
AND Location = London

Various comparison operators can be used with WHERE, =, <, > etc.
% operates like a wild card eg WHERE Bus_Unit LIKE ‘C%’ would search for all units beginning with C. Logical operators can also be used such as: and, or, not

DBMS becomes much more powerful when joining tables. It’s important to match primary keys to foreign keys to avoid useless rows of information.