Relational Database Management System and MySQL Tutorial
Chapter 1: Getting Started
Hello and welcome to MySQL Training. I’m Ayumilove and I am going to be taking you from absolute beginner level to a mastery of MySQL where you will be able design and develop your own complex Database Management Systems. I don’t expect you to know anything about database at this point or about SQL (Structured Query Language). We will be going through all those things in the following chapters. However, I do expect you to know your way around the computer and to have MySQL installed on a particular operating system that you are running on your machine. In this chapter, we are going to take a look at the basic things you need in order to use MySQL and develop databases. We are going to have a look at your choice of development environment which is something that you need to be very comfortable with. We are going to look at the MySQL Monitor versus various GUI (Graphical User Interface) and web based administration. We are going to look all of those things in the next chapter.
Chapter 2: Database Basics
In this chapter, we are going to examine some basic database concepts.
We are going to look at what is a database is.
We are going to introduce Relational Database Management System (RDBMS) such as MySQL.
And we are going to take a look at the structure of a typical database.
To begin with, we need to ask a question “What is a database?”
What is a database?
The answer is that a database can be many things. It is simply a collection of data.
Whatever the data is depends on the kind of database that we are looking at.
The example of databases that we use in our everyday lives could be:
The place where we keep our personal information about our friends.
A card index
A simple method of keeping track of data
Files in a Directory Tree
If you use Windows OS, you will have directories in your C drive that structures how files are stored.
Your bank account details stored in bank systems, which are more complex kind of database systems.
Confusion between Database Terms
Another point of confusion arises because people use the word database to mean 2 quite different things.
One is as we just found out, “a collection of data”, and it could also mean system that manages the data.
However in a strict sense:
When we talk about the data (a collection of data) we call that as a database.
When we talk about the software, we call that database management system.
Analogy ‘database’ = library , ‘database management system’ = librarian
As an analogy, we can look at database as library whereas the database management system is the librarian who when we ask the librarian for a certain piece of data, it can go through the library and locate the particular piece of data that we need.
Examples of Database Management System (DBMS)
Popular DBMS: Oracle, SQL Server, Access, mSQL, PostreSQL, MySQL. [SQL pronounce as S.Q.L or Sequel]
Examples of Database Management Systems, otherwise known as DBMSs, are Oracle, Microsoft SQL Server, Microsoft Access, mSQL, PostreSQL and I’m sure you are familiar with since you are in this course: MySQL.
All of these software, packages and more, have their own individual merits. It’s not really worth as to looking the differences between the different database management systems. Its suffice to say that they all have their own peculiarities. MySQL being particularly flexible and fully-featured, very powerful example of a database management system that nevertheless would not break your back. In the next chapter, we are going to take a look at the differences between the relational database management system and any other kind of database.
Chapter 3: RDBMS
Before we get into looking at the structure of databases,
We need to differentiate between the kind of database management systems.
RDBMs stands for “Relational Database Management Systems.
This pose a question… “What is a RDBMS?”
Simple Example of RDBMS
Let’s take a look at a practical example of a shop, where customers are allowed to use credit for purchases.
At the end of the month or whenever we need to settle the credit, we know exactly what they have bought.
We are storing these data in some kind of table form. The table has 2 columns (2 fields)
The first field is for Customer (to store their names) and the later is for Product (the purchase)
“Haruhi” comes in and buy “kobe beef”
“Momoko” pops in and buy “salmon”
“Akira” drops by and buy “tofu”
Now our system at the moment is similar to a “Flat File Database System”.
Essentially, there is nothing in this system that couldn’t be done by a card index.
Each of the column stores all the data.
So when “Haruhi” comes in to buy some bread, we need to write her name again.
Now, there is obvious disadvantage to this when we are applying to the computer,
that we are wasting space on the hard disk by entering the name “Haruhi” twice.
This may not look very serious when we have a list of 4 or 5 customers.
But when we are dealing with a lot more of high end applications, then the implications is more severe.
However, this is not the main problem with the system.
The main problem is that it allows all kind of errors to completely screw up our data.
For instance, if when “Haruhi” enters the shop, and a new person is working behind the counter…
They might not know “Haruhi”, they might have to ask her, her name.
They might mishear it, instead of “Haruhi”, they hear it as “Haruhee”
Later on, when we try to run a query to find out what products that “Haruhi” has bought from us.
And the only thing that comes out would be “beef” because the computer is not intelligent enough
to recognize that “Haruhee” is just a misspelling of “Haruhi”.
Computers are very literal and they can’t find any kind of rational correlation between things.
They only look at values being identical or otherwise. So our system is seriously flawed.
If we replace this with a relational scenario, then we have a much different and
more flexible system that has much less strength for that kind of error.
What we do is to separate the customers into 1 table, and the Orders into the another.
When each other is placed, use a relationship between the
Order table and and the Customer table to determine which of the customers made the order.
We will look at exactly how this happens much later on.
For now, the important thing is that the data for the Customer is stored once.
And when the shop attendant goes to write down the details of the order,
he or she will look up the name of the customer from the customer table,
instead of write down the name of the customer from being told by the customer at that particular point.
So Haruhi bought the kobe beef, Momoko bought salmon and Akira bought tofu.
And then because the shop attendant is not writing down the name but simply referring to the entry already in the customer table, there is no chance for error. “Haruhi” is connected with the bread order as well.
So we can see that this is a much more effective system than the flat file system that we examined before.
In our next chapter, we are going to take a look at the ways in which a database is actually structured.
RDBMS is similar to a Flat File System but it consists of multiple tables referencing to one another. This offer some advantages such as saving hard disk space by reducing redundancy of customer details within the same table and avoid human input error (misspelling) like Haruhi vs Haruhee.
Chapter 4: Database Structure
So far we have look at the difference between Relational Database Management System (RDBMS) and other forms of database management system in very abstract way. In this chapter, we are going to take a look at the reality of how this works and how our databases are structured. We are just going to look at the absolute basics here. We will get into more details as we progress through this course.
Here (the diagram above) is one the most basic units of a database, that is a table.
a table: ‘artist’ – This whole thing is known as a table. And the Table’s name is “Artist”.
Within the table, data is stored according to different kinds of data types such as
we may have a numeric data type (ID Column) or alpha-numeric data type (Name Column)
What we want is, for each record within the table to be similar, so that all entries
can be treated in the similar way. This is why we split the table into columns.
a column : ‘Artist.ID’ – This column name is ID.
The ID within MySQL is a way of referring to the ID column of the Artist’s Table, is Artist.ID
but never-mind of the technicalities for now, we are just looking at the general concepts.
a column : ‘Artist.Name’ – Here is another column called Name, Artist.Name to be precise.
All the data within the table is stored within the column.
This column stores the name of my favorite Newgrounds Audio Artist.
We are going to use this table in conjunction of another table
to access information about my musical collection. A very simple database application.
These are known as rows. (See the pic above to know what is a row)
Now, different database management systems would have different jargon connecting with them.
A long time ago, columns were known as fields and rows are known as records.
For the sake of consistency, all the way through this course, I’m going to call them as columns and rows.
A row is a distinct unit of data. So, each row is an entry within our table.
Now let’s take a look at the other table that we are going to be using in our hypothetical database application
This table is a Songs Table, and is set up in a very similar way to the Artist Table.
It has unique ID column.
It has a song Title column.
It has another column called Artist, and I will explain the reason for this in a second.
You notice that the numbers within the Artist Column (in the Songs Table)
match up to the numbers in the ID column of the Artist Table.
This is the way of forming a relationship between the Songs Table and Artist Table.
Avoid Redundancy and Easier Maintenance
This allow us to avoid storing information more than once.
So, for instance, “Operation Evolution, Twilight Techno and Jetstream” are all tracks by Dimrain47.
But there is no need for us to store the name of the artist 3 times
or as how many times we have songs within the Song Table.
That is redundant and can cause all kind of errors as we saw in the last chapter with the misspelling name.
Furthermore if we realize that we need to change that piece of data for what ever reason,
all we have to do under this system would be to change the name in the Artist Table.
When we want to retrieve data, the Artist Column will refer us to the entry in the Artist Table
and essentially the data will be corrected for all the songs in effect.
This relationship between Songs and Artist Table is known as 1 to many relationship.
This is how we write it (1:N = 1 to Many) in the official database diagram.
1 means 1 entry while N means indefinite number.
Each 1 artist may produce 1 or many songs.
A unique song is written by 1 artist.
Of course, there are limitations to this to this model, and if we are really implementing this to my musical collection I find that I would have collaboration tracks that would require me to use more complex relationship known as many to many relationship. However, for the sake of our example, this is a simple 1 to many relationship which links these 2 tables.
Relationship and Key Concepts
Now we are looking at relationships, I can now introduce another very important concept about the database structure and that is keys. Some columns within our tables are known as keys. These 2 for instance, the ID columns from both tables are the primary keys which essentially provide unique identifier by which we can identify each entry within the table. So instead of having to say “ParagonX9”, we could just say Artist ID 1. This means that when we go, for instance, make a relationship between the 2 tables, we don’t have to use fillable data such as typing in the full name of the artist. For instance, its not even the question of spelling mistake necessarily but someone might type in just “paragon”, someone might in all lower case, someone else might capitalize the first letter P for Paragonx9 but not the rest. We get rid of all these complications by using a simple numeric primary key.
The other primary key is the foreign key.
The reason is called foreign key is because it refers to a unique entry within a different table
than the table in which the foreign key is installed.
In this case, the foreign key is within the Songs Table
but the data refers to the ID of the entries within the Artist Table.
So the data within the Artist Column for instance,
on the first row here, “A New Energy”. We will look at it – the group is 4.
So a quick scan at our Artist table, will find Artist ID #4 is “F-777”
So the computer is able to quickly and efficiently locate the relevant Artist for the song.
Without the foreign keys, I should say here that MySQL does not support the official implementation of foreign keys, that is to say although we can include foreign keys within our specification for creating a table for instance, it won’t actually do anything.
Other database management systems will only allow us to enter data
into a foreign key column when entries exist within the other table.
So, for instance if we try to add in a new song and we try to put the number 6 in the artist column as an entry, it wouldn’t allow us to do that within a different database system. However, MySQL does not support foreign keys in this sense and its not quite strict about it.
This is the limitation in the way that MySQL implements the relational database management system model. However, there is always have a trade-off with this things. With MySQL, you get a significant performance boost because MySQL is not worrying about enforcing referential integrity, controlling, cascading, update, delete and so on. However, again for the trade off for that is extra programming work when we come to administer our database. So there is plus and minus to this kind of peculiarity of MySQL implementation of the relational databse management system.
Retrieve Data using Query
The way in which we get data from our tables is we have to write a query and that is where
“Structured Query Language” (SQL) comes in. SQL is very easy programming language.
It’s what known as fourth (4th) generation language which means it would be
a lot easier than any programming language you encountered otherwise.
For instance, if you have used C, PHP, or you have used Visual Basic, you wil find SQL absolutely essentially very easy to pick up. And even if you haven’t ever programmed before, you will find that because Structured Query Language closely follows the syntax of real English, you will find it very easy to pick up.
Here is an example of an SQL statement.
SELECT title FROM song, artist WHERE song.artist = artist.ID AND artist.name = “dimrain47”
You may look at this and notice some words are capitalize and some words are not, and say how can you possibly say its easy. This is very obscured syntax. But we are going to be looking at what each of these word means in the next few chapter, and we are going to see exactly what these queries can do.
This kind of SQL statement would become very familiar to you through using this course.
You will find and pick it up very easily. You will find SQL is very powerful and flexible way to
query your data and administer changes to data and retrieve a specific kind of data using SQL query.
For instance this query, put into even more plain English than its already, simply retrieves song titles where the song is written by dimrain47. However, we will be looking into SQL in a more greater detail in the following chapter.
Chapter 5: SQL Basics
In this chapter, we are going to look at the fundamentals of using SQL.
SQL is pronounce by some people as just letters S.Q.L or Sequel.
It doesn’t matter which way you say it, its your personal choice,
but it stands for “Structured Query Langauge”.
SQL is a 4th generation language which means that in comparison with languages such as C++ or Java,
it is a lot easier to read to the untrained eye and its a lot more similar to normal human syntax.
All the keywords are English and mean pretty much what they do when we use them in the English language.
The first thing we are going to look at in this chapter is how to create a new database using SQL and then how to create tables within that database. We are going to look at different kinds of columns we can create within a table and we are going to examine keys and indexes in tables within our MySQL database.
We are also going to look at different ways to manipulate data.
We are going to be inserting, retrieving, updating and deleting different elements of data within our database.
Finally, we are going to alter the structure of one of our tables that we have created.
Before we do any of this though, its a good idea to get familiar with the MySQL Monitor.
I have already created a shortcut to the MySQL Monitor on my desktop.
I’m running Microsoft Windows. (to be continue…)