Database Fundamentals: Core Concepts
1. Understanding Database Storage Models
What are databases?
Databases are at the core of our modern technology and it’s important to understand exactly what they are and the benefits that they bring to organizing a world of information. A database is a computer file that follows a specific structure and rules in order to allow the input, organization, and most importantly retrieval of data very quickly. It does this by organizing data into tables that could be sorted and filtered in very flexible ways.
So a database is just a structured collection of information.
The DBMS provides three very important tasks.
First, it helps us create the structural rules that our data will adhere to. This helps keep the data organized and consistent and provides a predictable results when it comes time to retrieve the information that we previously stored.
Second, the DBMS helps load data into the framework we’ve already established. Things like writing data to the tables and ensuring that it conforms to the established rules as well as helping users sort through the data to find trends or produce reports.
Finally, the Database Management System provides additional support for the database such as tracking users and providing log-in credentials, performing maintenance and routing backups, as well as a host of additional administrative tasks that protect and secure your data.
Once the database is created and we’ve started placing data into the established structure, we’ll need ways of getting the information back out.
The DBMS accomplishes this in a couple of ways. This process is called queries. They allow you to sort, filter, organize, and summarize your data in nearly endless ways.
Beyond just a collection of data, the DBMS supports a highly structured and efficient storage mechanism that allows you to enter, organize, protect, and retrieve information.
Understanding relational databases
By far, the most common type of database format follows the relational model.
The relational database builds on the organizational principles of the flat file system and the connected nature of the hierarchical system, but adds the ability to connect multiple tables together without restriction on the number of parent and child relationships.
The main idea behind a relational database is that your data gets broken down into common themes, with one table dedicated to describing the records of each theme.
This means that a single wide data table, one with lots of lots of columns, will become several smaller tables with fewer columns in each one.
By using unique identifiers for each record, we can relate one table to another. Using these identifiers for each record, we can relate one table to another. These identifiers are called key fields and they are glue that holds the entire system together.
A properly configured relational database can be a treasure trove of useful information that can be used to help guide business decisions or gain a better understanding of a complex system.
Exploring database fundamentals
Let’s talk about referential integrity. We know that key values are used to tie multiple related tables together. Referential integrity means that if the database is expecting a relationship on a particular field, then the corresponding value must already exist in the parent table before it will allow a change to the child.
In other words, you don’t ever want to be in a situation where a customer ordered a product that doesn’t actually exist in your inventory.
Or, a pay raise is given to an employee ID that was never issued to an actual person.
Referential integrity will protect you from these types of phantom connections by checking the existence of the item you’re referring to.
SQL is the standard language that relational databases use in order to create the data structures, enter and update data, and write queries to ask questions of the data set.
Though the core SQL language is part of the American National Standards Institute or ANSI Standard, each DBFS vendor applies their own tweaks and enhancements to the base language in order to distinguish their product from everyone else’s.
In the case of Microsoft SQL Server, the particular flavor or dialect, is called Transact-SQL or T-SQL.
2. Building Database Servers
Understanding the role of the server
When working with databases, the term “server” gets thrown around quite a bit. It’s important to understand what that means. A database server can either be a dedicated machine or a virtualized machine that is running the database management software. You’ll hear this referred to as an instance of the server and multiple instances or multiple separate installations can be installed on a single machine at the same time. That’s because when installing the server software, you’ll give the instance a unique name so they can function alongside multiple other instances without getting all tangled up.
3. Understanding Data Definition Language (DDL)
Using DDL statements to create database objects
Data Definition Language or DDL is used to define data structures in SQL Server. These statements create and manipulate database objects and use the keywords: USE, CREATE, ALTER, DROP, TRUNCATE, and DELETE.