SQL: Getting Started With Relational Databases

Name: SQL: Getting Started With Relational Databases
Author: Eric Grose

Learn how to design and implement an optimized relational database schema

Preview the first lesson free — get full access to all 4 lessons.

Course: On-Demand

Hello, my name is Eric and in these lessons you'll learn how to get started with relational databases. In this lesson we'll cover the basics of data tables, their structure, and how to make sense of them. Understanding data tables in a relational database is a crucial skill for anyone who wants to work with data. Now there's a lot of information in this lesson, but please don't be concerned as we'll cover all these topics in detail throughout the course. First, let's define what is a data table. A data table is a collection of data organized in rows and columns, similar to an Excel spreadsheet. As you can see here, we have our columns and their associated rows. In this class, we'll be using XAMP console to turn on our Apache and MySQL services so we can use our relational database environment. I've provided you with detailed instructions on how to install this software, set up your environment, and all the SQL statements we execute during this course. In a relational database, data tables are used to store and manage data. So I will navigate to this test database that I've designed for this course, expand this out, and I'll navigate to our table called Students. Similar to our Excel spreadsheet, you can see that each row in the table represents a single record, and each column represents a specific attribute about that record. Now let's go ahead and look at the structure of this data table. Repetition is the key to all learning, so again, a data table is made up of rows and columns. The columns are also known as fields, and the rows are also known as records. So let's drill into this data table to understand the structure better. Consider a scenario where you want to store and eventually analyze data about how students are performing in this class. This table has columns labeled Student ID, Name, Age, Gender, Address, Phone, Email, and the class that they're in. You can see that each column has a name and it gives us a good description about the data field. Also, each table has a unique identifier known as a primary key. The primary key is used to identify a specific row in the table that will allow you to easily identify a specific row or set of rows. Oftentimes, this type of unique number is an ID, so Student ID would be the best primary key for this table. If we go back to our table, we can see that each row in this data table represents a specific student and it contains their information. Now that we understand the structure of a data table, let's move on how to make sense of all this. The first thing to consider is the column name. Column name should be descriptive and provide useful information about the data contained in the column. As we can see here, all these column names make sense and provide us with the accurate information. This will make it much easier to understand what the data is and how it should be used. The next thing to consider is the data itself. Data should always be consistent, accurate, and relevant. Now we don't have any dates in this data table, but most inconsistencies come from the date formatting, where certain date might be listed as month, month, day, day, year, year, year, or any other combination of those dates. So it will be our job to identify and clean up any issues within our data. Inconsistencies in the data and irrelevant data can make it harder to understand the data table. To make it easier to work with data tables, we will use a variety of tools and techniques which we will cover in this course. Thankfully, all the tools and techniques we'll be using in this course are open source, and that means they are free for you to learn and explore as much as you want. Some of the techniques we will learn include sorting, filtering, and grouping data. Sorting data allows you to arrange data in a specific order based on a specific column. So I'm going to go to our bookmark query here, and we're going to click on lesson one order by. In this query here, we are sorting by the age column, and if we look at our display down here, we can now see that the ages are going up in ascending order. Filtering data allows you to select a subset of the data based on specific criteria, allowing you to filter a table to only show students of a specific class or maybe gender. Let's take a look at that query. I'm going to go to lesson one where, hit go, and you can see here we're saying where gender equals F for female. And in our display down here, we see that only our female students are being displayed. Grouping data allows you to group data based on specific columns. In this case, you could group the students based on their class, age, or gender. So let's go ahead and take a look at that query by going to our group by, okay? In this query, I'm grouping by gender, and I'm getting a count. So in this scenario, I want to know how many students of each gender are in our class, and we can see that there is two of each. Another important technique for working with data tables is joining data tables. Joining data tables allow you to combine data from two or more tables based on a common field. So I have created another table here called classes, and we can see that it has a similar column called class, but also provides us a more detailed class description. So if I want to join my students table with my class table, let's take a look at what that will look like. Okay, so again, I'm joining on that light column, and now we can see that it has our name, age, and as well as the available information from our newly joined class table. This allows us to see which students are in which class, and also a description of that class. I know I threw a lot of information at you, but we will go over all these topics in detail throughout the course. Data tables are organized collections of data in columns and rows. The structure of a data table includes column names, where the primary key is, and consistent as well as accurate data. Tools and techniques such as sorting, filtering, grouping, and joining tables can make it much easier to work with data tables. Thanks for watching. Stay tuned for the next lesson where I show you how to create data tables and the relationships while you design your very own relational database.

Beginner Provider : Eric Grose 4 Lessons · 22m in English

Course Description

Gaining experience with database design, documentation, and implementation is important for any technical portfolio. In these lessons, we’ll help you gain a solid understanding of how data is organized, stored, and optimized for querying—all important skills for anyone looking to work with databases. By learning the core concepts of database design, you’ll be able to build a database schema, which is essentially a blueprint of a database that outlines its structure, relationships between data tables, and data types. Understanding how to effectively build a database schema will enable you to optimize data storage and retrieval, ensuring that you can quickly and efficiently query large data sets.

You’ll also learn about the role of the database administrator, who is responsible for creating, maintaining, and updating an organization's data infrastructure. Database administrators are in high demand and play a critical role in ensuring that an organization's data is secure, accurate, and accessible to those who need it. By understanding the role of a database administrator, you will gain insights into the broader context of database management and be better prepared to work collaboratively with IT teams.

What You'll Learn

Design a relational database from core database design concepts
Draft documentation detailing the specifications for a relational database
Build a database schema using SQL and the phpMyAdmin GUI
Organize and structure data using tables and relational database structures
Optimize data storage and retrieval to query large data sets efficiently
Explain the role of the database administrator in maintaining an organization's data infrastructure

Key Takeaways

A database schema is a blueprint that outlines a database's structure, the relationships between data tables, and data types.
Understanding database design lets you optimize data storage and retrieval so large data sets can be queried quickly and efficiently.
A database administrator is responsible for creating, maintaining, and updating an organization's data infrastructure.
Database administrators are in high demand and help ensure an organization's data is secure, accurate, and accessible to those who need it.
Schemas can be built using both SQL and the GUI in phpMyAdmin.

Frequently Asked Questions

Who is this course for?

It is for anyone looking to work with databases who wants a solid understanding of how data is organized, stored, and optimized for querying, and skills valuable for a technical portfolio.

What will I learn to build?

You'll learn to build a database schema, the blueprint that outlines a database's structure, relationships between data tables, and data types, using SQL and the phpMyAdmin GUI.

What topics do the lessons cover?

The lessons cover Understanding Tables, Creating Tables, Relational Database Structure, and Relational Database Schema.

What skills does this course help develop?

It helps develop skills in database diagramming, entity relationship models, and relational database design.

Does the course explain the database administrator role?

Yes. It covers the database administrator's role in creating, maintaining, and updating an organization's data infrastructure and keeping data secure, accurate, and accessible.