An Introduction to Database Admin Systems (DBMS)
An introduction to database admin systems, including the main types, their advantages, and disadvantages, with reference to the Python programming language.
Database management systems are crucial to the functioning of the internet and to most of the processes in the modern world. Our dependence on database software for everything from booking a flight to ordering shoes on the internet is the reason we feel something like panic every time our corner of the internet goes down.
As a developer you will have to interface with these systems whether you love, hate, or are indifferent to these systems. If you are planning a project you will need to know something about the different types, their pros and cons, and of course, if you are smart enough to have chosen Python as your main development tool, how they will relate to this incredibly intuitive and efficient language.
What is a database admin system?
A database is any organized repository of information. They have been around probably since at least the Neolithic revolution some 100,000 years ago when homo sapiens transitioned from hunter-gatherers into farmers. Once you begin planning for the coming year and storing food supplies, it makes sense to have an inventory of everything you have.
When this activity exactly began, we can’t say. Perhaps the first database admin system was a neolithic farmer’s brain, but what we can say is that when the Pharoah need information about Egypt’s grain stores, someone had to have that information to hand. That person was the Database Administrator (DBA) of their time.
Fast forward to today and everything is stored in software programs and accessed via other software interfaces. The database is the software that contains the information, think customers, citizens, flights on planes, etc, and the database administration system is the frontend software that allows a human being or other computer to insert, extract, examine, or otherwise manipulate this data.
DBMS = Data + Database + Users
DBMS: Main components
There are between about 4 and 6 main components to any DBMS depending on who you talk to. For completeness’ sake, we will discuss what we consider to be the 6 main components here.
- Hardware – the actual computer or computers your data is stored on and which your software runs on.
- Software – the software that interfaces between the data stored on the disk and the users who need it.
- Data – the useful information you have stored in the system.
- Database Access Language – the language used to run commands in the software that interfaces with the database.
- Procedures – these are all of the guidance and control processes to use, manage and protect the DBMS. These include setup and deployment, login/logout protocols, backup procedures, redundancy strategies, and report generation.
- Users – people who need the data varying from online end users to Database Administrators and everyone in between.
The main types of DBMS, their advantages and disadvantages
There are seven main types of DBMS and each has its strengths and weaknesses depending on your project needs.
NoSQL
This is an older form of database created in the 1960s before the needs of messy, complicated, and generally massive, big data came along. These are non-relational and not based on the well-known table relationship found in most database structures.
Advantages:
- Excellent in highly volatile and rapidly changing environments.
- Robust and well-tested over decades of real-time use
- Relatively simple to design
Disadvantages:
- Not good for handling the diverse nature of big data
- Do not represent complex relationships between records well
ER or Entity Relationship Model
This is a widely used, relatively simple, and intuitive model that represents an object or ‘entity’ with one or many attributes. The relationship between the entities and their attributes is established with a principal key identifier. They are commonly used for high-level, conceptual database development before implementation, and usually employ the Relational Model.
Advantages:
- Excellent as a design tool for both simple and more databases
- Platform independent design tool
- Good for expressing relationships between database entities
Disadvantages:
- Must be translated to the specific DBMS software being used
- Is a theoretical design tool, not a practical implementation tool
- Not ideal for the expression of ultra-complex relationships
- Not ideal for the needs of big data
The Relational Model
This model also creates rows and columns with key identifiers. However, the data is then further differentiated into tables that cross reference and relationships with each other. These can be established by inserting key identifiers from one table into related tables as ‘foreign key’ identifiers. For example, one table contains owners, the other contains cars. The owners’ table will have unique IDs for each car owner. The cars table will have unique IDs, e.g. license plates, for each of the cars but also a foreign key containing the car owner’s unique IDs.
Advantages:
- Excellent for expressing complex relationships between tables
- Widely used and implemented in many brands of software
- Popular with DBAs and industry IT departments
Disadvantages:
- Not ideal to express non-hierarchical relationships between database entities
- Too rigid in many cases for use in big data scenarios
Document Databases
These are non-relational databases that enable stored documents to evolve over time as changes are made to the information they contain. These are used mainly with catalogs, content management systems, and user profiles.
Advantages:
- Excellent for catalogs, CMS, systems, and user profiles.
- The ideal choice for the handling of documents in database format
Disadvantages:
- Not ideal for many of the standard applications of DBMS software
- Narrow in its application to very specific industry uses
Network Databases
This is a non-hierarchical database structure that represents relationships between records as a web rather than as a top-down hierarchy. The goal is to store, use and represent the relationships between entities more accurately. This avoids imposing an artificial parent-child relationship on the contents of the database in which each data member can only have a single parent record.
Advantages:
- The most accurate model for expressing complex relationships between database entities or nodes.
- Ideal for the non-hierarchical network of interrelationships typical of non-homogenous data types brought together over the internet
- Handles many-to-many relationships better than other models
Disadvantages:
- Can slow down performance where data is purely hierarchical and complex relationship expression is not required.
Graph Database
A graph database stores data as nodes and edges, the relationships between them, rather than as tables or documents. The relational model requires JOIN operations to explore relationships between database entities. These can be costly, slow, and rigid. In a graph database, JOINs are not required because relationships are stored alongside the nodes or data elements, making them more flexible and efficient.
Advantages:
- Faster execution for some actions versus relational databases.
- More flexible and efficient particularly when applied to the diverse nature of operations on modern big data
Disadvantages:
- Not ideal for strictly hierarchical data structures or databases designed to hold documents that are frequently updated
Hierarchical Database
This model is based on a tree-like structure where each parent record can have multiple child records, but each child can only have one parent. The model is easy and intuitive to design but excludes the one-to-many and many-to-many relationships that records can have in a relational database model.
This is widely used in telecommunications, banking, and other sectors where the data is essentially hierarchical in nature.
Advantages:
- Intuitive design model.
- Excellent for the strictly hierarchical structure of data in telecommunications and banking.
- Faster execution than more complex databases on some data structures
Disadvantages:
- Not ideal for expressing complex relationships between database entities
- Poor and potentially inaccurate performance when applied to big data
What is SQL?
SQL stands for Structured Query Language. It is the fundamental language of data professionals to interact with databases. Whether you want to add or delete records, create, delete, modify or extract information from a database, SQL will most likely be the language you use.
Compared to most languages it is relatively simple and easy to master. As a Python developer, you will need some knowledge of this language to make your code run with a DBMS.
Database software solutions
There is a dizzying number of DBMS software solutions on the market today. Most share common features and knowledge of one will prepare you for use with another, so it is worth familiarizing yourself with one or two of the most popular. Here are some of the most widely used solutions today:
Many of these can be learned for free (MySQL, SQLite, PostgreSQL), and others provide paid certification courses (Oracle DBMS, Microsoft SQL Server).
Python and database admin systems
There are many reasons to choose Python if your code is going to be interfacing with relational databases using SQL. Here are just a few.
- Python is portable. If your database is distributed across multiple platforms Python makes it easy to handle this.
- It’s easier to learn than most other languages
- It runs faster and more efficiently than most alternatives
- Python is platform-independent. No matter where your database goes, Python can follow.
- Python supports SQL cursors. Crucial for interfacing with relational databases.
- Python automatically handles open and closed database connections. Not all languages do this.
- Full support for relational database management systems.
- The Python database APIs are fully compatible with a wide range of databases making it relatively simple to port database APIs between systems.
DBMS software and systems are essential to the modern world and to the functioning of the internet. There are many models to choose from, each with their own applications depending upon the nature of the data stored.
Python is an excellent programming language to interface with any of these systems due to its intuitive nature, portability, platform independence, and built-in functionality to work with SQL databases.
