Introduction to MySQL Relational Database :
A conceptually relational database is nothing but maintaining require relationship in between multiple tables by using some primary, unique or foreign key concept. Any database which practically follows this approach and maintaining a proper relationship between all the created tables then that database can be considered as relational database always. MySQL relational database is also following the same relational structure, so there has no doubt that my SQL also be considered as a server relational database whereas ‘relation’ term did not mention in MySQL documents or not. Core database which does not have any concept of a relational database, every table contains a lot of data including transactional and master both, understanding the logical binding of those data will be very difficult without knowing proper business logic. Relational Databases ensures this approach.
Relation Control System of MySQL relational database :
From the specific definition of the relational database, people are thinking it’s too critical for maintaining and will create confusion. But that is not a true statement, relational database’s main agenda to avoid any kind of confusion or complication in between multiple tables involved with that specific database, where it helps a developer to maintain and verify easily without any struggle. If we consider any relational design which is very common that maintain a strict relationship between all the related tables defined in the database. Suppose we can considering one Inventory Controlling System – in that case, we have to maintain one very strict relationship between all the tables who involved with given more description related to inventory. Also, it needs to maintain a more strict relationship with those child tables which additionally impacted for any kind of data manipulation activity like inserting to or deleting from that specific inventory. As examples, we are going to consider three tables who are involved for inventory controlling system, assume three are interrelated.
Table Name: Inventory
| ID (Primary Key) | Description | Price | Stock |
Table Name: Sales_Staff
| ID (Primary Key) | Name | contact |
Table Name: Invoice
| ID (Primary Key) | SalesStaff_ID( Foreign Key of Sales_Staff Primary Key) | Inventory_ID (Foreign Key of Inventory Primary Key) | Quantity | Price | comment |
Now considering the above three tables, we can planning relationship between multiple tables by using primary key and foreign key constraint. In the above example, Invoice is the main transactional table, where all the transactional data has been stored successfully for every invoice generation on individual customer or end user, it actually stored all the invoice data successfully for any kind of reference. Now Invoice should generate from some inventory details where a quantity of entire inquiry has been stored for one entire shop or organization. Now considering two key master tables like Inventory and Sales_Staff, both the tables maintaining main store details of any specific item in that shop or organization, whereas Sales_Staff maintained all the staff details who are working on that shop or organization. Rather than maintaining the same staff or specific item every time in inventory transactional details, it actually holds one specific reference of those master tables which are maintained by some administrator of the shop or organization. So by this specific approach, we can easily avoid data redundancy or data repetition, which always helps to fetch data based on a maintained relationship between multiple tables. This example given one key characteristic of any relational database like MySQL relational database, that is assuming one invoice data always hold the reference of specific inventory and sales staff, but inventory or sales staff never able to change or update anything in the created invoice.
So here it actually maintained one to many relationships where one inventory data can exist in Invoice multiple times, and the same one sales staff data can exist in Invoice for multiple times. This relationship helping developer for fetching data smoothly with specific joining condition and also understanding or designing any ER diagram will be very easy for them. Here also one key point which has to mention, suppose any sales person trying to sell something which is in the stock, which is also ensured by maintaining this kind of relationship. As whenever any inventory will be added in the invoice it automatically subtracts stock from the original inventory, so it will always provide proper validation message whenever sales person trying to create any kind of invoice for specific inventory. If we look closely on those table relationship then Inventory have one primary key name is Id, and Sales_Staff have one primary key name is ID, but Invoice has two foreign key which is actually maintained the relationship with Inventory and Sales_Staff tables. It also ensures that anything can be inserted on Invoice table which is actually exist in the Inventory or Sales_Staff table, without the existence of any specific data, it cannot be possible for making one entry in the Invoice table. As Invoice table have one specific foreign key relationship with both of these tables, so anything existing one those tables only can able to make the entry in the Invoice table. So it always helps a developer in case of making some wrong insert without maintaining those data on child tables.
Guidance of installing and downloading My SQL Relational Database:
There have many kinds of version available in internet for installing MySQL in your local PC, but MySQL Organization comes with one very interesting feature with free sample of relational database, have ability to varieties methods which are more advanced than other free samples of relational database, it can give opportunity to store movie data, one of the key point is harmless user experience, means suppose one user somehow do some mistake that’s why database entirely not working, in that case, it is very easy or can say 2 minute task to restore that database from the source.
1. Download the MySQL relational database from below link:
- http://downloads.mysql.com/docs/sakila-db.tar.gz
2. Executing below script for unpacking the archival package:
- tar –xzf xxxx-db.tar.gz
3. After unpacking the same, it will create 3 directories like below:
- Xxxx/sakila-db.sql
- Sakila-schema.sql
- Sakila.mwb
4. Now run MySQL basic command:
- Mysql –p (password)
5. Now just follow the instructions mention in sakila-db.sql and sakila-schema.sql.
6. If all the instructions followed properly then one new database will be created with the name “sakila”, which will be automatically displayed in the MySQL relational database list.
Recommended Articles
This article has been a guide to MySQL relational database. Here we have discussed Relation Control System and Guidance to installing the MySQL relational database. You may also look at the following article to learn more
