SHARE

Databases are used everywhere. Facebook, YouTube, your local library airport check-in desks, your bank, the list goes on and on.

What Is A Database?

A database is an easily accessible, manageable and uneatable collection of information. Large structured sets of “persistent” data that is usually associated with software for search and retrieval.

Prior To The Usage of Databases..

When we first started writing software, all applications with tailor-made. So, every application stored their own files, often in different formats only understood by the application that created it.

Problems With This

Having each application storing and creating data in it’s own format makes it very difficult for other pieces of software to access the same data. Mainly because each software will have its own format.

Additionally, if we wanted all software to access the same data store, we wouldn’t be able to. Every application would have to store the same data but in it’s own format, in its only data store. But lets say one program want’s to delete some data? How do you reflect this in the other programs? It would take a tonne of error-checking and constant updating, which is inefficient.

So Let Every Program Access The Same Data-files

This is great, provided all the applications know how to read the format of the files then we’re in business.

But next, there’s a few more problems. Namely with concurrency, security and manipulating the data files.

  • Concurrency is the ability to make multiple simultaneous changes.
  • Security is an issue because we don’t want everyone to have access to the data.
  • Updating or changing the data format could mean you need a full software rewrite too!

So Make A Management System

Putting a program in the middle of the software and database (a management system) will make the system much more effective.

  • You can prevent simultaneous access problems
    • People trying to edit the same data at the same time
  • Also provides an extra layer of security and integrity
    • You can run checks .etc

So now, the software requesting said data will simply connect to the management system. The only issue here is that the software needs to know how to communicate with the management system.

The First Databases

Databases were originally organised by the developers. A few issues with this approach were:

  • Functions; specifically created; non-reusable
  • Queries; adding new queries was complicated
  • Standards; a general lack of these; were database specific
  • Data; problems with duplication & dependencies

Just so you know, each program would have to be programmed to contain each function so it could communicate it with the database management system. Again, not really future proof.

Relational Databases

E.F.Codd introduced the relational database model in 1970. In a relational database, data is stored as records in tables (relations). Based on sound mathematics, this stuff tends to work better.

The benefits of this model: structure, integrity and manipulation. Without breaking everything.

Introduction Of “ANSI / SPARC” Architecture

It’s not really that complex, just sounds it.

ANSI / SPARC: a set of standards that all databases will adhere to. ANSI = American national standards institute. SPARC = Standards Planning requirement Committee.

3 Tier Architecture

The framework for database management systems, proposed by the ANSI/SPARC guys above, proposed 3 levels.

  1. The internal level; used by system designers
  2. The conceptual level; used by database designers
  3. The external level; use by database users

Basically, everyone needs to be accounted for.

The Internal Level

This level deals with the storing of data. So structuring of the records in files, pages and blocks, and the indexing and ordering of records.

I mentioned before that this level is used by database system programmers. They use Internal Schema to allocate data.

The Conceptual Level

At this level, we organise the entirety of a databases content.

DBAs and application programmers do this stuff. Abstractions are used to remove unnecessary details used in the internal level.

Theres also metadata involved at this level; data about data. Structuring information for tables and things such as primary and foreign keys.

sql query conceptual level
Creating a table with with Conceptual Schema

The External Level

This level provides the view determined by the user. Data might be hidden, presented in a suitable format or used by applications and users.

conceptual schema creating a view
Creating a view with External Schema

Modern Database Management Systems

There are tonnes, but to name a few:

  • Oracle
  • DB2
  • MySQL
  • Ingres
  • PostgreSQL
  • MS SQL Server

What Do They Allow Users To Do?

Almost all modern DBMS will allow there users to store, manage, organise, retrieve and secure their data. Additionally, they can recover from errors and avoid concurrency problems when multiple-users try to do something simultaneously. Another nicety is getting informed when something finally does break.

DBMS Functions

To put the above into a nicer list format:

  • Data storage, retrieval and update
  • User accessible catalog
  • Transaction support
  • Concurrency control
  • Recovery services
  • Authorisation services
  • Support communication software
  • Integrity services
  • Promote data independence
  • Utility services (import/export, monitoring and logs, statistical analysis, consolidate files/indexes, reporting tools,…)

DBMS Languages

These are usually composed of three parts:

  1. DDL; Data Definition Language; specifies the database format
  2. DML; Data Manipulation Language; specifies and retrieve database content
  3. DCL; Data Control Language; specifies access controls

These three parts are usually rolled into one language, such as SQL.

DBMS Components

In a DBMS there are 5 main components:

  1. Data dictionary (aka. System Catalogue); store info about objects in the database
  2. Query processor; intrprets and optimises queries sent to the DBMS by the user or application
  3. DML pre-processor; converts DML statements into standard function calls; interacts with the query processor (2).
  4. DDL compiler; produces the data dictionary, converts DDL statements into tables–for example.
  5. Catalogue manager; this manages access rights and maintains the data dictionary (1).

There are a few more detailed DBMS components, which you can see in the image below.

DBMS components detailed

Examples of How DBMS is Used

dbms layout example
On the left is the general layout. On the right is an example of a online store that sells CDs.

I hope that this post has offered you a nice introduction to databases and database management systems.