How a Database works

PhD Lecturer at “School of Computer Science for Business Management” – Romanian-American University

)

Introduction

A Database is a collection of data on a computer, it isn’t just a whole bunch of data packing to a file or randomly dimmed to your computer, its organized and very specific ; it’s actually made up of  “tables”. Each table is going to store similar information.

Sounds confusing? Let’s go ahead and take a look at a quick example:

Let’s say you are making the website YouTube. YouTube stores a bunch of information like usernames, passwords, links to videos, people comments, favorites, they don’t just want to store all this information in random files, so what they decided to do it’s better organize this in tables, instead of taking all the information they need and throw it randomly into a file, they broke it up into three tables: Users Table, Videos Table and Comments Table. So as we can see we can use tables to organize similar information, so now all the Users will go into Users Table, all the Videos will go to Videos Table and all the comments in a separate Table. Now that you are separating specific in similar information it’s just helps you organize your Database better.

Untitled 300x136 How a Database works

So now that isn’t the only way that we break up to organize data, we actually break up this tables in a very specific way as well. So each individual table it’s made up of columns and row, now the columns are like categories and the rows are like each separate entry.

Name Age Profession
Johnny 25 Doctor
Andrew 22 Personal Driver
Bobby 20 Unemployed
Christian 19 Student

Now let’s take a look at the table up here, let’s say I want to make a table to organize all of my friends, so I’ll have each column with a specific category for example the first column will be Name, I’ll have another column or category which will be Age in this case and my last category will be Profession. As you can see columns are like kind of categories, now the rows are a little bit different because each individual entry goes on a separate row, the first entry I have its Johnny who’s 25 and his profession is Doctor, now on the second one like I  said each row it’s like an individual entry so for the second entry will be Andrew the name, Age 22, Profession Personal Driver so as you can see once we have each column with a separate category and each row it’s a separate entry, it could really help you identify and organize a bunch of data very specifically, because than you going to say “How old is Bobby?” well you know to go in each category and you know to scroll down for “Bobby” and when the two meet, that’s the answer you looking for. So instead of just scrolling to random data, breaking the database in table and then into columns and rows could really help organize your data in very productive manner.

So as we can see let’s say we want to make a huge Database that it’s going to store all of the people from Romania, whenever we are making a Database say we organize it like this: “Database look for a person named Johnny” and I will say “Okay, I did not find one there are actually six hundred people in Romania named Johnny” so we try again “What about a person Bobby who’s unemployed” so think about it for a second how many people are named Bobby and they are unemployed in Romania, probably five hundred I’m guessing, well this isn’t really an effective way to make a table because you can have a lot of duplicates entries even you can have Bobby who’s 20 and unemployed, they are probably a couple hundred of those.

So instead of making a table like this, what you need to use it’s a Primary Key and remember this whenever you are making a table in a Database you need to a have a Primary Key and that is a column that’s guaranteed to be one hundred percent unique, so basically no entries, or no rows can have the same Primary Keys, so let’s go and take a look at another table with Primary Keys.

ID Name Age Profession
1 Johnny 25 Doctor
2 Andrew 22 Personal Driver
3 Bobby 20 Unemployed
4 Christian 19 Student

So whenever you are making a website, a site where you let the user have a username, which will be maybe the Primary Keys, it’s always to assign them and ID number or our Primary Keys in Romania is our Personal Identification Number (CNP) no one else has the same Personal Identification Number. So basically whenever I’m saying “Find all the Johnny’s” well will may return six hundred but if we say “ Return the person with Primary Key of one” it’s only going to return one result, and you can say to your Database “Find all the Bobby’s who are 20 and Unemployed” well that may gave you five hundred results but we are only looking for the Bobby from the table up there, so instead we will say” Find the person with the ID of three” and that is why you use a Primary Key because is guaranteed the only return will be one result, so say this Bobby died and we need it to remove him from the Database, instead of saying “Remove Bobby from Database” which may remove five hundred people, we just say “Remove the person with the ID of three” and we are good to go, we didn’t screw up our Database.

Now before I end this I want to cover one more thing “What is SQL?” because this tutorial is going to cover MySQL.

SQL is the language that Databases use, now we just can’t say to our computer “Hey Database delete Bobby” it doesn’t understand English it needs to use a special language and we will look at that a bit later, that’s called SQL.

Now what MySQL is? Is basically a program that understand SQL, now whenever we learn this SQL we just can’t open notepad or Microsoft offices and start typing, it’s not going to know what are we speaking about instead MySQL is the program that understands SQL and also there is other software aside for MySQL that can understand this, but MySQL is the best, it’s free and it’s fast.

So anyways there you go here is you background on databases, tables in SQL so I will see you guys with another tutorial.

Conclusion

A database is a collection of data made up of “tables”, tables store similar information. Each table is made of columns and rows, columns are categories and each entry goes on a separate row. All tables should have Primary Keys that helps us by making columns that are 100% unique, rows have the same Primary Keys.

Article reviewed and approved by: Garais Gabriel

PhD Lecturer at “School of Computer Science for Business Management” – Romanian-American University


Article Autor/s:

Ionut Smau

Leave a Reply