Database, Schema, Tables

Simple overview on Database, Schema and Tables

Hello to all visitors, Good day. We can read a lot about the database and schema throughout a day. But, I skimmed the concept for our easy understanding. To get more ideas, you can access other websites where plenty of explanations are available.  

 Database is a collection of organized data which can be related to each other.

The description of a database is called schema. Database is like a main folder or a big container which contains all the schemas in it and schemas are like subfolders within a database folder which are mainly used to group objects together logically where we can easily reach and fetch specific data from the vast amount of stored data. Here object refers to a table, views etc. 

 We can also give permissions to specific users to access the data (say read only or read, write) in the same schema.

Tables are the most significant component of a relational database and there are other components such as views where it can be used mostly to view the particular data pulled from some tables. For example, the viewers will be given selected names of customers and their passwords will not be provided or displayed in views due to security reasons. Views are temporary stuffs where there is no memory is allocated for it in a database.

Inside schemas, there may be one or more tables that comprises of rows and columns. Rows can also be called as records and columns can also be called as attributes.

Thank you for reading the article.

Delete, Truncate, Drop

SQL Fundamentals

Hello readers. I am going to explain the SQL concepts in story form. Let us understand the SQL concepts in a funny way.

Imagine our body as a database and consider cells and microbes as tables contained in that database. The cell table has both good cells and bad cells such as cancer cells. Similarly, the microbes table has friendly microbes as well as killer microbes such as viruses.

Three nanobots named DELETE, TRUNCATE and DROP got into a man’s body with a mission of killing the deadliest HIV viruses and cancer cells. The DELETE nanobot is programmed to find and kill certain specific set of viruses and cancer cells. If we are careless and fail to predefine who are all need to be killed, then he would kill the entire friendly microbes along with the fatal viruses. Therefore, to spot and kill the targeted viruses, the DELETE has to be accompanied by WHERE condition.

Our next nanobot TRUNCATE also works alike DELETE nanobot with a slight difference in operation. Instead of deleting the targeted items, he will remove all the contents of a table except the skeleton (fields or structure) of the table. Therefore, all the table contents no matter how good or bad, all stuffs will get deleted. In addition, TRUNCATE eliminates empty spaces in a table. After using TRUNCATE command, we cannot ROLL BACK the deleted items. i.e. dead records cannot be resurrected.

Here it comes the last nanobot DROP who is a devourer of tables and databases. Once the DROP nanobot is activated, he will destroy the entire table or a database based on what purpose he is deployed for.

Thank you for reading this story. Hope you enjoyed it while catching the concept.

Further Reading

Variance between Delete, Drop and Truncate

            These are known as killers of objects and records of a table. Each one of them has its own uniqueness in removing data. For example, the DELETE command which comes under the classification of DML commands deletes rows of a table. If we are careless, this command would delete the entire records. So this query should be accompanied with WHERE clause to specify which record needs to be removed from a table.

Here is the syntax for delete query:

DELETE from Tablename

WHERE condition

            On the other hand, the TRUNCATE command which comes under the category of DDL commands delete all the rows as well as the free space that occupies the table.

Here comes the syntax for Drop command:

                                    TRUNCATE Table Tablename

            Next, the heavy weight boxer named DROP query whose single punch knocks out the entire table (object) from a database. This guy creates all sorts of chaos in the database if we use him unknowingly. The syntax for him is:

DROP Table Tablename