Concepts of Relational database system
To understand the basic concepts of RDBMS,the following information is provided to you.
Table is the place where you can save all your data in the relational model(RDBMS). Data is organized in terms of Rows and columns in a table. For Example, see the information we have here in employee table.
EMPLOYEE TABLE : (With out Relational Model)
-------------------------------------------------------------------------------------------------
EMPNO ENAME DEPTNO DNAME LOC SAL COMM
-------- ---------------------------------------------------------------------------------------
7000 KING 10 MARKETING DALLAS 5000.00 300.00
7001 ALEX 20 DEVELOPMENT NY 3500 100
7002 CHRIS 10 MARKETING DALLAS 4000 100
7003 STEVE 20 DEVELOPMENT NY 4000 200
7004 KELLY 30 HR LA 2000 50
7005 JONES 30 HR LA 1700 40
In the above example,we have 6 employee records. 2 employees for each department.Take a look at it and see how many times we have the same information ie duplicate information.The departments are occuring twice and so more memory space is occupied. To calculate this,
MARKETING 9 BYTES
DEVELOPMENT 10 BYTES
HUMAN RESOURCES 9 BYTES
DALLAS 6 BYTES
NY 2 BYTES
LA 2 BYTES
TOTAL 28 BYTES
For 6 records we wasted 26 bytes, Imagine having 5000 employees working in thecompany! Then we are wasting 28 * 5000 bytes = 140000bytes,which is around 13 MB of memory.
Advantages of the new system(Relational model):
If you split the above shown data into two different tables then it becomes easy to maintain the data, For example:- If a particular department moved from Dallas to Atlanta,we need to change the city table for deptno 10 in the new system where as in the previous system we have to update all the employee records whose department is deptno 10.
removes the duplicate information (Data Redundancy) instead of duplicating the Department name and Location for every employee record as seen in the previous system,You can see Department name and Location only once in the Department table based on the common column in the new system .
Reduces the Memory space in the database
Employee Table
EMPNO ENAME DEPTNO SAL COMM
7000 KING 10 5000 300
7001 ALEX 20 3500 100
7002 CHRIS 10 4000 100
7003 STEVE 20 4000 200
7004 KELLY 30 2000 50
7005 JONES 30 1700 40
Department Table
DEPTNO DNAME LOC
10 MARKETING DALLAS
20 DEVELOPMENT NY
30 HUMAN RESOURCES LA
We have the same information now as before but the difference is we have two tables instead of one table.To relate the two tables, we need a common column between the tables. In the above example, there is a common column (DEPTNO).