This series is designed to be a database reference guide that’s all about databases from the very beginning. We’re calling it Database 101. There will be eight sections in this series.
Part 1: Database Glossary of Terms
Let’s start with some background for people that know nothing about databases. If you already know this stuff you might be bored, or it may be good information for a junior DBA or tech person. These are some basic terms you should know if you want to speak databases to people. This is in no way an all-inclusive or in any way advanced topic list.
- Database – This is an electronically organized collection of data. Technically, without adding the “electronic”, a Rolodex is a database (if you are old enough to know what that is). https://en.wikipedia.org/wiki/Database (bonus: https://en.wikipedia.org/wiki/Rolodex)
- Relational Database – This is still an organized collection of data, but the organization has defined relationships between the data allowing it to be used in different ways. https://en.wikipedia.org/wiki/Relational_database
- RDBMS – This is an initialism for Relational Database Management System: it’s a Relational Database, usually in the form of a software package.
- Engine – Sometimes used as “Database Engine.” Each vendor has their own, it’s the software package that manipulates that data that you want to put into or pull out of the database. https://en.wikipedia.org/wiki/Database_engine
- SQL – Another acronym, this time for Structured Query Language. Think of it as the language you use to work with an RDBMS. But, you need to be careful what SQL you are talking about since each vendor makes their own tweaks or extensions to it. Some basic examples of SQL statements would be things like “select …”, “Insert …”, “update …”, “delete …” https://en.wikipedia.org/wiki/SQL
- ANSI SQL – Or ANSI Database SQL, this is the standard defined for SQL, that again, not all vendors exactly meet for various reasons. Just know that not all SQL is portable between vendor database platforms, you most likely need to tweak it and in some cases rewrite it. https://en.wikipedia.org/wiki/SQL#Interoperability_and_standardization
- Query – This is how you ask a database a question using SQL, actually, it’s the actual question. How you ask (execute) it will be different (and how you write it due to the above-mentioned SQL differences) for each database vendor.
- Table – this is an organized set of related data stored within a database and is made up of columns and rows. An example might be all the names or addresses in an address book database. https://en.wikipedia.org/wiki/Table_(database)
- Row – This is a set of grouped related data in a table in a relational database. Like your entry in an address book, your name, address, telephone number, and email address in the address book table. Also referred to as a “tuple.” https://en.wikipedia.org/wiki/Row_(database)
- Column – This is a set of data values representing the same item across multiple rows in a table of a relational database. So, in the address book example, where we had your name, address, telephone number, and email address being a row in a table, all the email addresses across all the “address book” rows would be considered a column. https://en.wikipedia.org/wiki/Column_(database)
- Schema – This is the definition of the database you are working with, this defines all the tables, rows, columns, and other mysterious things that make up the database. https://en.wikipedia.org/wiki/Database_schema
- Index – This is a feature of most database engines that provides you a shortcut way (think “faster”) to find your data in the tables. Think about it in the address book example again, to find someone you would need to go row by row. With an index, you can jump to all the names that start with an “S.” https://en.wikipedia.org/wiki/Database_index
- Encryption – Same as with your mobile phone (you have your phone encrypted, right?), except it encrypts stuff in the database to make it harder for people to see or steal, but allows you to still work with the data when you need to do so. https://en.wikipedia.org/wiki/Database_encryption
- NULL – this is nothing, literally. When talking databases and the value of something, it’s not true or false, it’s not a positive, negative or zero number, it’s not a zero-length string of characters. It’s nothing, nada, the value is nonexistent. https://en.wikipedia.org/wiki/Null_(SQL)
- OLTP – An acronym for Online Transaction Processing. This is usually used to define how a database is used. If the database processes mostly transactions (units of work, like credit card charge records) it’s considered OLTP. https://en.wikipedia.org/wiki/Online_transaction_processing
- OLAP – An acronym for Online Analytical Processing, which usually defines a database system that is more designed to process data for reporting as opposed to doing transactions. These are the kind of database all those TPS reports run against. https://en.wikipedia.org/wiki/Online_analytical_processing
- NoSQL – Basically a non-relational database where the “relationship” aspect of organizing data is not used. It’s newer technology than relational and there are many reasons why you would want one. If you have a lot of different types of data you want to store together, like images, PDF files, or videos where the structure of the data isn’t just numbers and strings of letters, a NOSQL database is probably a better choice. https://en.wikipedia.org/wiki/NoSQL
- Backup/Archive – How do you archive your database, keep it safe so that you can get it back (“restore” it) if there is an issue and avoid any loss of data. You should always, and I mean always, have a backup and make sure you not only know how to restore it, but test the process regularly. https://en.wikipedia.org/wiki/Backup
- Replication – At the basic level related to a database, replication is storing your data in more than one spot. Most database engines have a native method to put another copy on another server for reporting, redundancy, or other reasons. https://en.wikipedia.org/wiki/Replication_(computing)#Database_replication
- Disaster Recovery – Otherwise known as DR, this encompasses all the tools and procedures involved with protecting your database investment. In terms of a database, it’s how you keep it running when there is a software problem, the computer crashes, or a disk drive goes bad. You have a database disaster, so how do you recover from it? You should always have a DR Plan. https://en.wikipedia.org/wiki/Disaster_recovery
- High Availability – Very often abbreviated as just HA, it covers the qualities of a database (or other) system that allows it to be resilient and keep providing service to its users. This could involve redundant hardware and/or redundant copies of the data (replication). Think about having the ability to have your regular processing failover to another place so it can keep going. https://en.wikipedia.org/wiki/High_availability
- Administrator – Otherwise known as a DBA (Database Administrator), that’s the person that takes care of your database, they make sure it’s backed up, try to keep it tuned so it runs fast, and resilient so it’s always available. That’s what we do for people like you. https://en.wikipedia.org/wiki/Database_administrator
- Tuning – Tuning is something that the XTIVIA team excels at and, usually, something DBAs take care of for you. It’s making sure the database runs as fast as it can. Very often, when you ask the database a question (query) the database needs some help, maybe an “index” or some other tweaking to make the answer come back really fast. Think: what they used to do to car engines before they all got onboard computers. https://en.wikipedia.org/wiki/Database_tuning
- Maintenance – For a database, maintenance is taking care of it by doing all the stuff a DBA does. In a car, this is changing the oil, filters, and checking the tire pressure of a car when the car has no “check engine” light and you can’t see the tires. With a database, with no maintenance, it might be slow, you might lose data, or it might just not work. We do this for clients all the time. https://en.wikipedia.org/wiki/Database_tuning#Database_maintenance
- RTO – Another acronym that is for Recovery Time Objective. When used in context of a database it basically means how long a business can afford to have a database not functioning if it stops working and stay in business. https://en.wikipedia.org/wiki/Recovery_time_objective
- RPO – Acronym for Recovery Point Objectives, and when talking databases refers to the amount of data, as a period of time, a business can afford to lose if there is a problem. Is it OK if we lose a day’s worth of data (think credit card transactions), even if it’s only for a few hours or a few seconds? https://en.wikipedia.org/wiki/Recovery_point_objective
– Bonus SQL tidbit: You might want to know how to pronounce “SQL”. Do you say the letters “S-Q-L” or do you say “Sequel”? There is plenty of debate, you can Google it and have a boatload of reading material, but it pretty much comes down to saying it however you like. The ISO Standard describing the language says you say the letters “S-Q-L”. Depending on which software vendor you choose, they might go either way. There actually used to be a “SEQUEL” which was shortened to “SQL” once lawyers got involved, which is why people still pronounce it with all the extra vowels.
So, you need a database or have a database, but what you need is an expert that knows how to wrangle that database. Maybe you have a team of DBAs and they need an extra set of hands or you need someone to be on call when your staff is off-duty. We can help you set it up right from the start and manage it going forward. You need to spend time managing your business, not learning how to tune or troubleshoot a database—that’s where XTIVIA comes in to be part of your team.
See part 2 of this series, Popular Databases, here.