Data Basics
Understanding databases has to start at the surface. A database is simply a structured collection of data. The data is typically ordered into tables similar to a table you may see in Microsoft Excel - only much larger! Each table has columns and rows and a piece of data housed in a specific column. A row is referred to as a record. Each piece of data has a data type, such as an integer, date, or time, that helps the computer ''expect'' and define what type of data is allowed in that column. The data doesn't just sit there in cyberspace somewhere, though; the tables and data housed in a database can have a number of different uses and relationships.
A table has what's called a primary key that helps relate it to other tables. A primary key is usually a critical and identifying piece of data. For instance, in a table of customer names, addresses, and phone numbers, the column containing the name would most likely be the primary key because it holds the most significance.
A foreign key works in conjunction with a primary key. A foreign key tells two tables that they are housing similar columns. In our customer example, we might have another table that holds customer names also but additionally, stores their order number and invoice number. A foreign key on the customer name column would help tie this table to our first table, assuming this is a pool of the same customers. Data within a database can have several different types of relationships, which we will explore further.
Database Management
If you're with me so far, we're doing great! We've navigated the first street in our database journey, but now, it's time to look at how a database is accessed and managed. A database is accessed through a DBMS or database management system. This is a specialized software that handles the storage, updating, and retrieval of data that is housed within a database. A DBMS allows you to query a database, which is the process that involves writing a snippet of simple code that displays or alters data in a database, matching the requirements given by the user. This is a quick way of accessing only the data you need without having to look through tables that potentially contain millions of entities.
SQL, or structured query language, is the programming language that's standard for querying a database. An SQL query might contain such words as ''update'', ''insert'', or ''delete'', which are considered to be DML or database manipulation language because queries containing these words are making changes to the database, not simply accessing information. Terms such as ''create'', ''alter'', and ''drop'' are considered DDL, or database definition language, because they can either create, modify, or remove entire tables within a database. A query containing the word ''select'' that simply displays data to the user is commonly referred to as a select statement.
Relationships Between Tables
Tables within a database can have three different types of relationships: one-to-one, one-to-many, and many-to-many. Understanding each of these relationships can really help a database to feel less foreign because it maps tables to one another and shows how the data is all interconnected.
One-to-many relationships are the most commonly occurring relationship in a database. Using our example where there's a table with customers' names, addresses, and phone numbers and another table with customers' names, order number, and invoice number, we can see potential one-to-many relationships. One customer can be associated with multiple orders, but an order can only be associated with one customer. This is the essence of a one-to-many relationship.
Many-to-many relationships follow the same pattern but get a little messier. In a many-to-many relationship, an item in Table A can have many matching items in Table B, and an item in Table B can have many matching items in Table A. This can get confusing very quickly, but something called a junction table can help. A junction table holds information from both tables and has a primary key that is a combination of the foreign keys from both tables that it is relating to. This might sound like it complicates things a bit further, but the image provided may clarify it a bit. The junction table actually mediates the many-to-many relationship by creating multiple one-to-many relationships instead, which is much more manageable!
The table in the middle serves as a junction table.
 |
The final relationship is a one-to-one relationship and is the most simple to understand. A one-to-one relationship usually occurs between two primary key fields. If Table A contains husbands and Table B contains wives, those two columns share a one-to-one relationship. One wife cannot be matched to more than one husband, and one husband cannot be matched to more than one wife.
Lesson Summary
Okay, let's take a moment or two to review what we've learned about database terminology.
We learned that the concept of a database is simple: a large, organized collection of information. We also learned about primary keys, which are critical and identifying pieces of data that help relate them to other tables and foreign keys, which work in conjunction with primary keys and tell two tables that they're housing similar columns. We also looked at database management systems, which are specialized software that handle the storage, updating, and retrieval of data and SQL, or structured query language.
We also looked at database manipulation language, in which queries containing certain words are making changes to the database, not simply accessing information, database definition language, which can either create, modify, or remove entire tables within a database, and select statements, which are queries containing the word 'select' that simply display data to the user.
To top it all off, we looked at junction tables, which hold information from both tables and have primary keys that are combinations of the foreign keys from both tables that they're relating to.
Throw in all these concepts we looked at and the simplicity might look a little like chaos. Databases are intimidating if you don't know how to approach them, but now that you're armed with the terminology needed to navigate even the largest and trickiest database, you are poised for success!
