Relational Databases

We deal with data every day in our day-to-day life. Bank transactions, online purchases, ticket booking, grocery lists, etc. involve dealing with data of some kind. Data has always been part of businesses; until the proliferation of computers, data was processed manually. Because they were stored on paper, retrieving data was difficult, especially if the volume of data was large. In addition, paper documents were not exactly reliable for storing data for long periods of time. The advent of computers on a large scale made it easier to store and retrieve data.

A database is a collection of related data that represents some aspect of the real world. In other words, it is a collection of logically coherent collection of data with some inherent meaning. A random assortment of data cannot correctly be referred to as a database; a database is designed, built, and populated with data for a specific purpose. While a database that contains only one table is called a flat database, a database that contains two or more related tables is called a relational database.

There are many ways of classifying databases – based on their structure, utility and even company. In this article, we will cover the basics of relational databases.

Relational Databases
There are two major types of systems that use databases in medium to large organizations. The first is for transaction processing, also called as online transaction processing or OLTP), that handles the day-to-day operations of an organization. Sales, accounting, manufacturing, human resources—all use OLTP systems. OLTP typically involves inserting, updating, and / or deleting small amounts of data in a database, while maintaining data integrity and effectiveness when dealing with numerous transactions simultaneously. OLTP mainly deals with large numbers of transactions by a large number of users. The second type of system is an analytical processing system, also called as online analytical processing or OLAP). OLAP is a computing method that allows users to easily extract required data and query data in order to analyze it from different points of view. It allows users to analyze database information from multiple database systems at one time.

Relational databases, which form the bulk of databases in use today, were developed for transaction processing. A relation in a relational database is based on a relational schema, which consists of number of attributes. A relational database stores data in 2-dimensional tables. A table is a two-dimensional structure made up of rows (tuples, records) and columns (attributes, fields).The relational database model offers a logical view of data, and the rows and columns for the basic logical constructs of it. The tuples and attributes are implemented as tables in a relational DBMS. On a conceptual level, relational databases separate the physical storage of data from its representation using a mathematical foundation for querying the data. The relational data model also introduces high-level query languages that provide an alternative to programming language interfaces, making it much faster to write new queries.

In order to maintain data integrity, a relational database must have certain identified attributes in relations to uniquely distinguish the tuples. Relational database systems use the concept of Relational Keys to distinguish between different records. Each table in a relational database has a primary key that uniquely identifies tuples in that table. While there are various types of keys, one of the most important keys in relational databases is the Primary key. Each table has a primary key that uniquely identifies tuples in that table, and no part of a primary key may be null. Another important key is the foreign key. A foreign key is a key used to link two tables together, which allow for something called referential integrity. What this means is that if a foreign key contains a value, this value refers to an existing record in the related table. A foreign key must always reference a primary key elsewhere.

Designing a Relational Database
A good database design starts with a list of the data that you want as well as a logical justification of why it should be included in the database. Like most other databases, relational database designs begin by identifying entities. A database entity is anything from a thing to concept whose data needs to be captured and stored in the form of properties, workflow and tables. Entities have data that describe them (their attributes). For example, an entity called ‘Contact’ needs to capture address information that includes properties like the street, city, state, e-mail, phone number, etc. Or a concert entity might be described by a title, date, location, and name of the performer. When entities are represented in a database, we actually store only the attributes. The next step is to determine the relationships between these entities (one-to-one, many-to-one, or many-to-many) and the number of distinct values in a table column, relative to the number of rows in the table. The next step involves organizing data into tables, and specifying primary, foreign and other keys and analyzing relationships. Data normalization is yet another important aspect of database designing. Database normalization is a process to organize the data into database tables in order to reduce redundancy and eliminates undesirable anomalies. It needs to be done on any relational database, where data is stored in tables which are linked to each other. Normalization makes the database efficient and more accurate.

The last step in relational database involves using an appropriate database management system (DBMS). A DBMS is nothing but a computer program designed to manage a large amount of structured data. A DBMS makes it possible for end users to create, protect, read, update and delete data in a database. It also helps to control access to the database. Popular choice of DBMS for a relational database includes MySQL, Oracle, PostgreSQL, Microsoft Access and a bunch of others. Some of them are proprietary; some of them are open source.

We have barely scratched the surface as far as relational databases are concerned; this is but a very, very brief introduction. There are tons of books available for each of the topic we have covered in a line or two – from database concepts, types, database designing, data integrity and normalization to database architecture. Developing databases is an important part of today’s applications, and there is a rewarding career for those who want to focus on database designing and database architecture. For more details about designing databases and learning web development or programming languages, it is best to approach a professional learning institute.