Databases: The foundation of business intelligence

Introduction

Gone are the days when organizations had to keep large cabinets in their offices and sometimes separate storage facilities just keep their various records in a bunch of files. This method of keeping a record is challenging in terms of maintenance, safety and in multiple ways. They are even time-consuming when trying to retrieve any record. Sometimes those records get destroyed if, for instance, disaster happens such as a natural disaster. Today’s world is a world of databases where records are kept electronically, using computing technology. Files are now safer, easier to maintain and hardly get destroyed, thanks to database technology. In this article, therefore, we are going to examine databases, how they are formed, and we are going to discuss the software systems, database management systems (DBMS), that support databases. If you are ready, let’s begin.

What is a database?

Before discussing what a database is, let’s understand data as it is used in this context. Data here means facts about an object, a person or place. For example, for a person, data could be first name, second name, age, etcetera. And in terms of an object, say a car, data could be the colour of the car, type, manufacturer and so on. Finally, if data is about a place, say city, these facts could be, the city’s name, zip code and many other things that can help to describe the city.

Techopedia has this to say about data in the context of a database. “…data refers to all the single items that are stored in a database, either individually or as a set. Data in a database is primarily stored in database tables, which are organized into columns that dictate the data types stored therein. So, if the “Customers” table has a column titled “Telephone Number,” whose data type is defined as “Number,” then only numerals can be stored in that column”.

Database, on the other hand, is simply a collection of data that contains records on people, places, or things in an organized passion for easy retrieval, manipulation such as updating and much more. 

According to oracle.com, a database is an organized collection of structured information, or data, typically stored electronically in a computer system. A database is usually controlled by a database management system (DBMS). Altogether, the data and the DBMS, along with the applications that are associated with them, are referred to as a database system, often shortened to a just database.

Data within the most common types of databases in operation today is typically modelled in rows and columns in a series of tables to make processing and data querying efficient. The data can then be easily accessed, managed, modified, updated, controlled, and organized. Most databases use structured query language (SQL) for writing and querying data.

Types of databases model

Databases are modelled in different ways based upon the purpose on which they are created. These are some of the models:

1. Centralized database
2. Relational database
3. Distributed database
4. Personal database
5. Operational database
6. Cloud database
7. End-user database
8. Graph database
9. Commercial database
10. NoSQL database
11. Object-oriented database

The above are some of the database approaches we have. Among all these models of databases, the relational database is the most popular in use today. You can check this for more about these different approaches to a database.

Relational database as the widest approach to database

The relational database is the most common database you can find anywhere today. In relational databases, data are organized into two-dimensional tables (called relations) with columns (attributes) and rows (records). In each table, there is data on an entity and its attributes. Mostly, each business entity has a table of its own. Therefore, for a basic level, one would find one table for say each part of the inventory, one table for a customer, another separate table for an employee, a table for supplier and sales transactions as well.

An entity in the relational database

Before designing a database, one must think of why the database is needed and for what purpose in the first place. Is it a hospital that patients information needs to be kept and some other information related to the hospital that will help in the smooth running of the hospital? Or, is it a supermarket where a lot of information is needed for things like inventories, customers, employees, supplies and more in order to ensure smooth and organized operation? Upon recognizing the need of the database and the kinds of data needed, the next is to figure out individual entities. Therefore, an entity is a single table that represents a generalized category about one thing, person or place like a customer, or inventory, or supplier and so on and so forth.

Below is a real-life example of an entity:

The above table or entity represents a customer entity in a typical database of sales management systems.

An attribute in the relational database

When we talk about attributes, we are talking about the characteristics or features of each and every table or entity in a database. As discussed above, a table represents a single entity, and in every entity, there are more than one attributes that help to give each item (record or tuple) in a table a unique outlook, for example in the above ‘’Customer’’ entity, there are attributes like CustomerID, CustomerFirstName, CustomerAddresss and so on. So, a combination of all the attributes regarding a particular item will give a specific record (row) a distinct look from all other records that are within a single entity. Example in the Customer entity, we may have a lot of customers and each with their records.

Important to note:

Record (tuple in more professional term): Refers to actual information about a single item in a table. A record contains in a single row of a table, let say we have ten rows in an entity, then it means we have ten different records in that particular table.

Field: Refers to a section of a record. Fields are attributes in a table. Mostly several fields make up a record. Example, the information on the field of “CustomerAddress” under record “1” in the Customer table is, “123 Dove Street.” Take a look at the below example.

So, a record represents a row, while a field represents a column in a database table. Again, for emphasis, a record is the same thing as tuple or row. And the field is the same thing as an attribute.

The primary key in an entity

The primary key is a single attribute that is set aside among attributes in a table that is used to uniquely identify each record in an entity. No two records can have the same item in their primary key field in the same table. Every record has its own primary key that can only be used by it and no one else in that relation.

Foreign key

Foreign key refers to a primary key of another table used in different table in order to get access to the record associated with that key. For example, when you want to have access to a particular customer in a Customer table through another different table, say “Sale” table, you can only do that by using its primary key. See example below:

Entity-relationship diagram (ER Diagram)

An entity-relationship diagram is used to show tables relationships in a relational database. The most important information provided by ER Diagram is providing the manner in which tables are related to each other. Tables can have a one-to-one, one-to-many or many-to-many relationship in a relational database. 

For instance, an HR department database will have one table having basic information about an employee, such as name, age, address and another table containing confidential information, with information such as salary, retirement benefit and so on in it. So, in these two tables (employee basic information table and employee confidential information table) there exists a one-to-one relationship because each record (employee) will have one salary and retirement benefit information, not two associated with their names in the confidential table and vice versa.

But in our example of customer and sale tables, there exists a one-to-many relationship. Each customer can have more than one sale records in the sale table while on the other hand, one sale record is related to only one customer in the customer table. 

In a many-to-many relationship, there exist more than one records that are related to another table and vice versa. An example would be, say we have the “order” table where all orders are recorded and another table called “part” where all parts (maybe car engine parts) are recorded. In one order you may have more than one part, and so also one part can be ordered in many different occasions. Therefore, this creates a many-to-many relationship between the order table and the part table. 

Here is an example of how relationships are represented.

Example of the relationship as it is used in Microsoft Access. It shows a one-to-many relationship.

Referential integrity rule

Referential integrity refers to the fact that a foreign key can only be used in a table when there is an existing table that is pointing back at it. If a record does not exist, there is no way another record would carry its foreign key. For example, in the table customer, we only have a record until primary key 5. So, when using these primary keys in another table as foreign keys, you can’t have a “6” point back at customer table, because there is no such record.

Database management systems (DBMS)

After discussing the database and at least one of the approaches to a database, we are going to look at what database management systems or DBMS are. DBMS are special software systems that are designed to manage databases.

Technically, DBMS is a software system that uses a standard method of cataloguing, retrieving, and running queries on data. The DBMS manages incoming data, organizes it, and provides ways for the data to be modified or extracted by users or other programs. Christensson, P. (2006).

Laudon, K.C and Laudon, J. P (2011) said, “a database management system (DBMS) is a specific type of software for creating, storing, organizing, and accessing data from a database.” 

We have different kinds of software that are for different databases need; for instance, Microsoft Access is a DBMS for desktop systems that are found on all Windows desktops. And DBMS for large mainframes and midrange computers, we have DB2, Oracle Database, and Microsoft SQL Server and more. For open-source DBMS, my MySQL is one of the most popular and Oracle Database Lite is a DBMS for small handheld computing devices. Important to note, all the above-mentioned software (products) are relational DBMS that supports a relational database.

The DBMS software separate physical and logical views of the database. The advantage is that it saves the end user the headache of going through the physical view, which in most cases are not so friendly. While for a developer working on the database, he/she can view the physical view to know the storage and the structure of the database and if there’s need to make some adjustment or update that can be done properly. And another advantage is that the logical view can be viewed in different ways depending on need. Example, the sales department view is not going to be the same as the finance department. Take a look below:

Logical view example of a database in Microsoft Access

Physical view example of a database in Microsoft Access

The three basic operations of relational DBMS

There are basically three operations that one can perform in relational database management systems. Those operations can allow different views of the database for different purposes. They are: 

1. Select: Using select operation allows selecting only rows in a particular table that meet certain criteria. Example of how this operation is performed, let’s use the sale table above. Select from a sale where customerID equals to 2. This statement will create another table or a subset of the sale table, and the information that met the stated criteria will appear. Therefore, the new table will have only two rows, number 1 and 5. 
2. Join: Join operation joins relational tables in order to have more information that otherwise couldn’t have been gotten in one table. An example could be joining a customer table and sale table 
3. Project: To perform project operation, certain attributes (columns) will be selected from a table, then a subset of this table will be created with information that meets the stated criteria. For instance, from the customer table, you can create another table that consists of say customerFirstName, customerLastName and customerAddress. Therefore, in this new table, only these three selected columns will appear. 

These are just a tip of the iceberg regarding what DBMS can do; there’s certainly more than a hundred other things that you can do with these software systems for business and managerial performance. 

Things I would like you to explore on DBMS quickly are capabilities of DBMS like data mining, data warehousing data mart and so on.

Finally, this is all we have on databases and database management systems. Let me hear what you think in the comments section below.

References

Christensson, P. (2006). DBMS Definition. Retrieved from https://techterms.com/definition/dbms

Laudon, K.C and Laudon, J.P (2011). Essentials of management information systems. Ninth Edition. Upper Saddle River, New Jersey. Pearson Education, Inc., 

Oracle.com (n.d). What is a database? Retrieved from https://oracle.com/database/what-is-database.html

Sam, S. (July 2018). Types of Databases. Retrieved from https://tutorialspoint.com/Types-of-databases