Database is the reason you survive today!
Very basic question start with what is a database? All the structured stored data is connected to more than one system/ nodes/ computers, from where data is fetched and updated. This is known as a database.
What is data? Don’t worry we will clear the basic first.
Data is nothing but a processed information, now what do I mean by process information? Process information is something that on the raw or incomplete information you work on, you make it fool proof, and insert/store that data in the form of rows and columns in a table. This is a basic structure of the database.
Now, what is the benefit of this database?
Imagine all this data is getting generated every day of social media sites, all the tweets you do, all the posts you post on a Facebook page and all this data is not getting store!! Imagine the horror if somebody posted something about you and you don’t get to see it, as the data is not saved in the database!!
This is a very basic example, but you can imagine how database plays very vital and most crucial role in your day to day life.
Consider below Ice-cream Table
As you can see in above table there is certain naming convention for writing a column name. It can start with letter or underscore[_] and lettername, there shouldn’t be any space between two words , you can either give hyphen [-] or underscore.
Now almost every table has a primary key. Primary key is something which is unique and separates every other row from all the other existing rows in a table. In short, it should be unique.
In our case ice-cream_no is a primary key.
In the ice cream detail table we can see the ingredients used to make that ice cream. Now in this table ice-crem_no is foreign key.
What is foreign key ? Foreign key is something primary key in one tables becomes a foreign key in another table.
In above table ice-cream_no 1 states that, it is a coffee icecream with ingredients chocolate, coffee and milk.
What are constraints? Constraints are nothing but a limitation on the table. When you insert data into a table certain types of limitations are applied.
Types of constraints:
- Primary key
- NOT NULL
- Foreign key
We have already seen on the previous topic what is primary and foreign key.
Let’s go to NOT NULL constraint, Null means nothing, what it states that while inserting data into a table that field shouldn’t be empty.
Unique constraint means value entered in the column shouldn’t be exact as respect to any existing values in the column.
when the value is entered in the value you can enter certain condition , and that value should pass that condition, then only you can insert value .
When adding value for ice cream price in the ice-cream table, condition can be price > = 50.
so while inserting value for ice cream price if the ice cream price is greater than or equal to 50 then only it can be inserted in the table.
NOW how to apply all these constraints to a table? Let’s see below example.
Create table ice-cream
ice-cream_no int(20) constraint no_ice primary key,
ice-cream_name varchar 255 constraint ice_nm unique,
Ice-cream_topping varchar 500 constraint ice_topping NOT NULL,
price int(20) constraint ice_price check >=50 ;
This is how we can apply constraint while creating a table.
Now you can apply constraint table level and column level. On the above example of creating ice-cream table we have applied constraints on row level.
Example : Applying constraints on column level
Create table ice-cream-detail
PRIMARY KEY (no), FOREIGN KEY (ice-cream_no) references ice-crem(ice-cream_no) ;
This is how we can create tables in database, and database is made up of tables.
Database plays very vital part in day-to-day life. Understanding and implementing in real life is very important.
By using this everybody will be benefited and mostly you.