Step your data up to the next level, by moving it from a spreadsheet to a database! Gain the power, security and power of a full database engine. Learn how to plan the migration, design the database, cleanse the data and then make the move to SQL Azure. Together, we'll build a police and crime database that would serve any CSI team well!
You can't just copy and paste the data from your spreadsheet - however well it works - into a SQL database. It just won't work.
In this course, we teach you the right way, including topics such as
- Planning your database
- Normalizing your data
- Cleansing your data
- Creating a SQL database in Azure
- Creating tables
- Understanding and creating relationships
- Creating indexes
Migrating Data From Excel to a Database
Introduction to relational databases
Welcome along! Let's take a look at where we're going with this course, including the key steps, and a sneak preview of where we're going to arrive at the end.
If you simply want your data to exist in a database, then it's possible to just dump it from one place to another. This is a simple solution, but there are big reasons why it's not a good idea...
At the heart of this course is a plan to create a new relational database which will hold your existing data, and then move your data into it. But before we can do that, we need to take a step back and make sure we're completely clear on the fundamental question of what a relational database actually is.
If you're planning to move to a relational database, which tool should you use? There are a number of options just from Microsoft, so in this lesson we'll consider each of these.
Planning a relational database
Let's take a look at what we're going to cover in this unit of the course.
In this lesson, we'll take a look at how you figure out what will be in your database, including who needs to be involved in the discussions, and the sorts of questions you need to ask.
What's your database going to track? Parts in a manufacturing process? Pupils in a school? Staff in your organisation? Answering this question fully is your first step in building your database design
You'll need to know certain facts about each of the "things" you identified in the last lesson. In this lesson, we'll start to fill in these details, making sure that your database will be able to hold everything you need.
You need to identify everything in your database - in a way which is guaranteed to be accurate. In this lesson, we'll take a look at how you do that. We'll also look at why some ways of identifying this will work, whereas others will cause you endless pain down the road...
Now it's your turn to apply what you've learned to the data you've got in your spreadsheets.
Obviously, it's not possible for me to look at every scenario that you'll have. But in the review, I'll take a look at a few different examples of data, and we can see what we can come up with as early designs.
We've already covered quite a lot of ground. So let's take a look back at what we've done before pressing ahead with the next stage.
Why is your design flawed? Almost always, it's because there's information about more than one thing mixed up. This leads to BAD DATA - and will cause you endless problems, as you'll see....
In this lesson, we'll introduce the concept of normalization, and give you the first rule to apply to your data. We'll look at an example together, and see what the rule means in practice.
In the next stage of normalization, we'll look at how to make sure your table doesn't contain horses with wheels, or cars with legs. And we'll look at why this matters (if that's not already obvious...)
More about ensuring that each of your database tables has information about one thing - and one thing only...
We've looked at normalization together - now it's your turn to apply what you've learned to your own data.
Let's take a look together at another example of normalization
Let's take a look back at what we've covered in this unit.
Let's take a look at why relationships matter, and at what we'll be covering in this unit.
One-to-many relationships are far and away the most common type of relationship. In this lesson, we'll look at what we mean by a one-to-many relationship, and some scenarios where you might come across them
When we talk about database design, and particularly about relationships, it's often useful to create diagrams of our design. In this bonus lesson, we'll look at three different tools that you can use to create database diagrams, and I'll share my personal recommendation.
In this lesson, we'll take a look at the next type of relationship - the one-to-one relationship.
Logically, there's only one kind of relationship left - the many-to-many relationship. In this lesson, we'll take a look at this type of relationship - and at why it's bad news!
We're going to finish this unit by taking one last look at normalization in the light of what we've learned about relationships.
Over to you now to apply what you've learned in this unit, and refine the structure of your database.
In this review, we'll apply all that we've learned in this unit to refine the design of our sample database.
Let's take a look at what we've learned in this unit
In this lesson, we'll take a look at how to create a new database in SQL, in MS Access and in Azure. By covering all three technologies together, you can compare and contrast the task at hand.
Once you've got a database, you'll need to start creating tables. This is a pretty simple process in essence, but I'll give you some tips to save you a lot of heartache - no matter where you're building your database.
An absolutely crucial decision to get right from day one is the data type for each column in your database. In this lesson we'll look at the main data types available to you, and I'll give you some recommendations for best practice in choosing between similar options.
We're at the point where you're going to actually build a database. It's time to choose whether you're going to use MS Access or SQL... at least for this course. Here's why you should use MS Access.
We're at the point where you're going to actually build a database. It's time to choose whether you're going to use MS Access or SQL... at least for this course. Here's why you should use SQL Server.
Creating your tables in MS Access
Let's see where we're heading in this unit of the course.
In this lesson, we'll put together everything we've learned so far, as we build a couple of tables, including key fields, appropriate data types and apply the tips I shared in the previous unit.
Now you've got a couple of tables, you'll want to create a relationship between them. In this lesson, we'll look at how you do that - and what might stop that from working!
We mentioned indexes in our introduction to this section of the course - but how do you create an index on an Access table? In this lesson, we'll reveal all!
Dealing with NULL values in Access is a key factor in creating a robust design - and one where the Access developers at Microsoft haven't been as helpful as they perhaps might have been.
Now it's your turn to apply what you've learned and actually create the tables you need in your database. Once you've created them, you can set some key properties, and then relate them together.
Let's tackle this together, as we add the tables we need to our crime database.
Let's take a look back over this key unit - where we finally got to build ourselves a database for real!
Creating your tables in SQL
There are two essential ways of creating a table in SQL - you might think of them as "the easy way" and "the hard way". Let's start with the easy way - by using the table design tool
In this unit we're going to build a database in SQL. Let's see what's involved...
If that last way was "the easy way" then this must be "the hard way", right? Well yes, but there are good reasons to know how to create tables this way, and it isn't really THAT hard...
As we go through this course, we're going to link SQL and Access together and draw on the strengths of each. But before we do that, we're going to have to address a BIG problem...
As with creating tables in SQL, a relationship can be created either through a Graphical User Interface, or through code. In this lesson, we'll take a look at the GUI method.
Now it's time to create a relationship between two tables by using SQL code.
We talked in the unit introduction about the need for indexes - now it's time to actually create a few indexes on our SQL tables.
You've seen how it's done - now it's time to do it for yourself. In this exercise, you'll create tables in your SQL database.
Let's go through the exercise together, by creating tables in our crime database.
One big reason for using SQL Server to manage your data is security. But, if you want security, you'll have to manage that. That's the topic for this unit!
There are some key things to know about allowing people to access your database - in this lesson, we'll give you an overview of the issues involved.
Although there are more than two ways to do this, in this lesson we're going to look at the two most common ways of providing security to your database - authentication via SQL, or via Windows.
Before people can access your database, you'll need to allow them access to the server as a whole.
Once you've granted the permission for someone to connect to your server, you'll then want them to access your database - in other words, they will become USERS of your database. Let's see how to do it.
It's not easy keeping track of lots of people, who have a habit of arriving, changing jobs, leaving and then coming back. So rather than working with individuals, it's more common to put people into groups. Step forward the idea of a database role....
Now we've got people (or roles) who can access our database, it's time to decide what we'll let them do.
What have we learned in this part of the course? Let's take a look back...
Having thought long and hard about it, and made your decision to use one tool or the other, why would you now re-think it and use both? Let me explain...
If I go to a client, and they've made this mistake, I KNOW I'm dealing with people who don't know what they're doing. It's an easy mistake to fix, and an ESSENTIAL one if you want your database to work reliably.
Let's look at how you link one access database to another.
Let's see the steps involved in linking a SQL database to an Access database - including a REALLY quick example of why you should!
In addition to all the security measures that you'll see in SQL Server in your office, there's one more feature to be aware of if you use SQL Azure - the firewall.
Introduction: How and where to cleanse your data
Excel has some great tools for cleansing your data. But before we start along the journey, we need to be clear about what the end goal is.
Do you have customers living at "1 North Road"? How about at "1 North Rd"? Or "1, North Rd.". Which of these is correct? This is what we mean by inconsistent data. Let's look at how we tackle this issue.
There's more than one tool that can fix inconsistent data. Let's see how Excel stacks up against Access.
Your number one cleansing tool
At the end of the last unit, we saw that Access can be a fast way to cleanse your data. But it offers a number of benefits over Excel as we look to move our data into a database. Let's see what these benefits may be...
Your number one tool for working with data in Access is the query. Let's take a look at the essentials of creating queries in Access
Now you know the essentials of creating a query in Access, it's time to take a closer look at some of the tools at your disposal, including selecting the right columns and sorting the data
There's a whole heap of ways in which you can filter data using a query. Let's take a look at the basics - filtering based multiple criteria using AND and OR.
Given that we know our data may not be perfect, we need to understand how to find North Rd, North Rd. (with a dot) and North Road. Step in the wildcard...
This is where things get really interesting. What if you could write one query and have it do multiple jobs? Well, guess what...?
You've seen how it's done - now it's your turn. I'll give you a series of questions, and you need to use queries to come up with the answers.
How did you do? Let's tackle the questions together and see if we come up with the same answers!
Let's take a look back at what we've learned about creating queries in Access
Calculating new values in queries
Whether you want to stick first names to last names, or separate names out, total multiple rows of values or multiply values in two columns, anything is possible if you know how. And after this unit of the course, you will!
In this lesson, we'll take a look at how you add a calculated value to each row of data in a query. The days of writing a formula and "filling down" in Excel may finally be over...
Just as in Excel, we can concatenate text in our database if we need to. Here's how...
Just as in Excel, there will be times when you want to stick two bits of text together to form a new column. Let's look at just how we do it.
Just as in Excel, there are lots of functions within Access. In this lesson, we'll take a look at how you can use them to get at the data you need.
So far, we've looked at adding new columns to our results, based on various calculations. But we can also calculate across rows, using the aggregate functions that will be familiar to you from Excel - SUM, MAX, MIN and so on. In this lesson, we'll explore how it's done.
Time for you to have a go! Put your new skills into practice with a series of challenges.
Let's tackle those challenges together, and see if you came up with the same answers - and whether you got there in the same way as I did!
You've now got a really useful set of skills under your belt. Let's take a look back and see what we've picked up.
Putting your queries to work
So far, your queries provide lots of ways of seeing your data, including summarising it and calculating it in various ways. Now it's time to make your queries do some real work.
Your data is in Excel. You want it in a database. But there's more than one way of achieving that. Let's take a look at the benefits and drawbacks of your two main options.
It may be no surprise to hear that the "Make table" query is designed to, well, make tables. Let's see how it works.
You'll often need to make changes to your data once it's in the database. Much faster than using find-and-replace, update queries are a powerful and flexible way to do this.
Your Excel data is likely to be in a one or more large spreadsheets, and will need to be moved into the various tables you've created. In this lesson, we look at how append queries can do this for you.
Once your data is in the right place, you're likely to want to remove it from any "wrong" places. Step in the delete query....
It's time to put into practice all the skills you've learned about during this part of the course.
Let's take a look at that last challenge and go through the exercise together.
We've learned about some very powerful tools in this unit of the course. Before we actually use them to perform the migration of our data, let's just look back and make sure we're comfortable with each of them.
There are a number of steps in performing your migration - and you may need to take more than one go at the job. Let's start by being clear about what we need to achieve.
You may have one spreadsheet of data or a dozen. You may have 2 tables or 22. So where do you begin with the migration. In this lesson, we'll make a plan.
Documenting the import process isn't a "nice to have". You'll often find that you need to re-start the process as you find things that didn't work the first time, and having documentation of all your queries is essential. In this lesson, we look at how to document the queries that you're running in each step of the process.
You've made a plan as to where to start. Before you move your data, let's use our new tools to see if we have any data which needs to be cleaned up.
We've cleansed the data, we've got the tools at the ready.... Let's FINALLY move the data into its correct table.
By linking the data you've put into its final table to the data that's still in its raw form, you can get to the newly created IDs. This is a key step in importing data into subsequent tables. Let's see how to do it.
Now you have all that you need to import the data from your original spreadsheets into the remaining tables. Let's see what that process looks like
This isn't so much an exercise as you actually doing the job, on your own data, with your own database. Over to you!
In this unit you've achieved what you set out to do. You've now got your data into your database. Congratulations! Let's look back at the steps you took to get there.
We've achieved what we set out to do, and your data now lives in a database, rather than a set of unmanaged spreadsheets. So what's left for you to do now?
A database is the perfect place to hold your data. But that doesn't mean that Excel doesn't have a real use in analysing, charting and working on your data. In this lesson, we'll look at the ways in which you can get your data - or some part of it - back into Excel.
One of the benefits of having a database rather than a spreadsheet is that you can build a full user interface. Although that's beyond the scope of this course, in this lesson we'll take a sneak peek at the some of the first steps you'll take to do this.
Where are you likely to go next in this process? We'll take a look at the steps needed to build your database up into a fully-featured application which can be used by people throughout your organisation and beyond.
Well done! You've made it to the end. Let's take a look back at what you've achieved.
About the teacher
Andrew Richards is the Managing Director of TheIT Service. His background in training goes back to 2000, and he was involved in IT networking and support beyond that. Now he spends his time training cyber-security awareness courses (fun!) and building databases (and training others how to do so too). When not engaged in such important things, he can be found attempting to train his various sheep, chickens and cats.