Session 7: relational databases

INM348 - Digital Information Technologies and Architectures
 
Databases are an efficient way for organisations to store data in a central repository so that it can be used by a variety of applications and users. The relational database stores this data in a set of tables, linked by 'keys', which can then be manipulated and interrogated by database management software (DBMS).

The database approach removes the need for different teams to store similar sets of data about the same things - client data, for example. Using a central database means the data can be managed and updated consistently, saving time and money.

Many relational databases use structured query language (SQL) to manage the DBMS. SQL uses standard commands to create database tables and we interrogate them using 'queries' to get the information we need.

An example: my company employs a team of account managers to manage long-term contracts with large public sector clients. We don't have a structured way to manage this information, so contact with our clients is often inconsistent. A simple relational database could help us match account managers to major clients, and record the services we provide to them.

So we could set up two tables. Our 'account_managers' table gives each Account Manager an ID number ('am_id'), which is the primary key for this table (Table 1 below).

Table 1. Account managers

am_id	name	job_title	email	location
01	Jim Boardman	Technical Director (Rail)	jb@mp.com	Manchester
02	Kate Robinson	Head of Business Development, (Energy)	kr@mp.com	London
03	Iqbal Shah	Commercial Director (Government Services)	iqs@mp.com	Woking
04	Jeena Keeth	Development Director (Education)	jk@mp.com	Woking
05	Bob Sabberton	Business Development Manager (Utilities)	bbs@mp.com	Liverpool

The primary key from the account_managers table is used as the 'foreign key' in the 'clients table' to identify which account manager handles each client (Table 2 below):

Table 2. Clients

client_id	client_name	account_manager
NWR	Network Rail	01
HWY	Highways Agency	02
HCC	Hertforshire County Council	03
OCC	Oxfordshire County Council	03
ISA	Islington Technical Academy	04
SCW	Scottish Water	05
THW	Thames Water	05

To get a list of which account managers handle which clients, we can use this query:

SELECT client.client_name, account_managers.am_id, account_managers.name, account_managers.job title, account_managers_location FROM clients, account managers WHERE account_manager = am_id;

Fig 5. Example query from database tables. 

The result can help us judge whether the right people in our company are managing the right clients, and we can adjust our business strategy accordingly.

Word count, excluding figures, tables and captions: 283 words.