What is a Relational Database?
Uploaded by learningsqlserver on 04.01.2011
Transcript:
Hi. I’m Jason Roth, and I work at Microsoft as a Senior Programming Writer.
The goal of this video series is to help you learn SQL Server.
With that said, it would be a mistake to start with the features or even the installation
of SQL Server.
There are two important questions to cover first: What is a database? And What does it
do for you?
If you already have answers to these questions, feel free to skip this video. Otherwise, I’ll
try to explain.
To put it extremely simply, a database is just a location to store and retrieve data.
If you’ve ever used Excel, you might be thinking that a spreadsheet is an excellent
place to store and retrieve data. And you’d be right.
So at what point does it make sense to consider SQL Server?
To answer that question, let’s look at a fictional Excel workbook that contains experiment
data. Each row in this spreadsheet contains temperature readings from a particular experiment
run.
One immediate problem you’ll notice is the redundancy of data. For example, the experiment
name and description are repeated for each run.
Any changes to the experiment name or description involve multiple edits to keep the data consistent.
Also, there is nothing keeping the user from misspellings that unintentionally create a
new experiment.
A relational database solves these problems and provides other services as well.
Let’s learn more about relational databases by viewing this data in SQL Server 2008 R2
Express. SQL Server Express is a free version of Microsoft’s database engine.
We’re going to use SQL Server Management Studio.
When prompted, I’ll provide the default instance of SQL Server Express, ".\SQLEXPRESS".
I’m then going to use the Object Explorer to navigate to my ExperimentData database.
Instead of diving into how to use SQL Server, let’s just look at the database and compare
it to Excel.
Database tables are like spreadsheets in that they have rows and columns. Let’s look at
the Experiments table.
You can immediately see that I’ve factored out Experiment information into its own table.
In fact, I’ve separated the run values from the Runs.
Placing separate entities into their own tables is part of a process called Normalization.
It reduces redundant data.
If I want to update an experiment name, I can make the change in one place.
One of the key abilities of a relational database is the ability to manage the relationships
between these tables.
Let me try to explain.
This is a database diagram I created earlier. The lines show the relationships between the
three tables.
You can see that each run points to an experiment by Id. And each RunValue points to an associated
run.
These relationships do more than just describe the data. The database actually enforces them.
Let’s try to delete an experiment in the experiments table. We get a long error that
basically means we’re not allowed.
There are rows in the Runs table that refer to the experiments in the Experiments table.
In the same way, I could not add a row in the Runs table without referencing an existing
row in the Experiments table.
A well-designed database enforces relationships that keep the data consistent and accurate.
Now I’d like to quickly talk about two other benefits of a relational database.
Databases allow you to create queries, which are really just questions.
The query here asks the question, “For each experiment, what were the number of runs and
the minimum, maximum, and average temperatures for those runs?”
Queries like this will be explored more in future videos.
Databases also help with application development. Development tools, like Visual Studio, provide
multiple ways of accessing data in a database.
This simple web site dynamically shows the experiment runs when we change the experiment.
Creating this type of application to run against our original Excel spreadsheet would be slower
and more difficult to write.
So, with this information, should you use a database?
And should you learn more about a database engine like SQL Server?
Here are a few practical questions.
Are you working with a large amount of data? Do you want to enforce the accuracy of your
data? Do you want to reduce the amount of redundant
data? Will you create web or client applications
for the data?
Databases are ideal for any of these requirements.
And these are just the ones we had time to cover in this video.
Database also provide many other services such as security, backup, and replication.
I hope I’ve helped explain what a database is and why you might want one for your data.
If you do decide to use a database, SQL Server is a great place to start.
For more information and resources go to http://learningsqlserver.wordpress.com.
In future “Learning SQL Server” videos, I will download, install, and start using
SQL Server with you.
Thanks for watching!!
The goal of this video series is to help you learn SQL Server.
With that said, it would be a mistake to start with the features or even the installation
of SQL Server.
There are two important questions to cover first: What is a database? And What does it
do for you?
If you already have answers to these questions, feel free to skip this video. Otherwise, I’ll
try to explain.
To put it extremely simply, a database is just a location to store and retrieve data.
If you’ve ever used Excel, you might be thinking that a spreadsheet is an excellent
place to store and retrieve data. And you’d be right.
So at what point does it make sense to consider SQL Server?
To answer that question, let’s look at a fictional Excel workbook that contains experiment
data. Each row in this spreadsheet contains temperature readings from a particular experiment
run.
One immediate problem you’ll notice is the redundancy of data. For example, the experiment
name and description are repeated for each run.
Any changes to the experiment name or description involve multiple edits to keep the data consistent.
Also, there is nothing keeping the user from misspellings that unintentionally create a
new experiment.
A relational database solves these problems and provides other services as well.
Let’s learn more about relational databases by viewing this data in SQL Server 2008 R2
Express. SQL Server Express is a free version of Microsoft’s database engine.
We’re going to use SQL Server Management Studio.
When prompted, I’ll provide the default instance of SQL Server Express, ".\SQLEXPRESS".
I’m then going to use the Object Explorer to navigate to my ExperimentData database.
Instead of diving into how to use SQL Server, let’s just look at the database and compare
it to Excel.
Database tables are like spreadsheets in that they have rows and columns. Let’s look at
the Experiments table.
You can immediately see that I’ve factored out Experiment information into its own table.
In fact, I’ve separated the run values from the Runs.
Placing separate entities into their own tables is part of a process called Normalization.
It reduces redundant data.
If I want to update an experiment name, I can make the change in one place.
One of the key abilities of a relational database is the ability to manage the relationships
between these tables.
Let me try to explain.
This is a database diagram I created earlier. The lines show the relationships between the
three tables.
You can see that each run points to an experiment by Id. And each RunValue points to an associated
run.
These relationships do more than just describe the data. The database actually enforces them.
Let’s try to delete an experiment in the experiments table. We get a long error that
basically means we’re not allowed.
There are rows in the Runs table that refer to the experiments in the Experiments table.
In the same way, I could not add a row in the Runs table without referencing an existing
row in the Experiments table.
A well-designed database enforces relationships that keep the data consistent and accurate.
Now I’d like to quickly talk about two other benefits of a relational database.
Databases allow you to create queries, which are really just questions.
The query here asks the question, “For each experiment, what were the number of runs and
the minimum, maximum, and average temperatures for those runs?”
Queries like this will be explored more in future videos.
Databases also help with application development. Development tools, like Visual Studio, provide
multiple ways of accessing data in a database.
This simple web site dynamically shows the experiment runs when we change the experiment.
Creating this type of application to run against our original Excel spreadsheet would be slower
and more difficult to write.
So, with this information, should you use a database?
And should you learn more about a database engine like SQL Server?
Here are a few practical questions.
Are you working with a large amount of data? Do you want to enforce the accuracy of your
data? Do you want to reduce the amount of redundant
data? Will you create web or client applications
for the data?
Databases are ideal for any of these requirements.
And these are just the ones we had time to cover in this video.
Database also provide many other services such as security, backup, and replication.
I hope I’ve helped explain what a database is and why you might want one for your data.
If you do decide to use a database, SQL Server is a great place to start.
For more information and resources go to http://learningsqlserver.wordpress.com.
In future “Learning SQL Server” videos, I will download, install, and start using
SQL Server with you.
Thanks for watching!!