Data Storage
6.1.Data Storage
Once data is collected, they need to be entered and
stored in some storage system. A proper storage of data ensures safekeeping for
future uses and references. Data can be stored in various electronical and
non-electronical ways. In current times electronic storage system are more
durable and thus trustable to store our data in safety for a long period of
time. Various sources such as cloud, hard disk, local drive, pen drive, etc…
are available to store data.[1]
6.1.Data
Warehouse
‘Data warehouse is a form of database which contains structured
heterogeneous data (data of different kinds). The heterogenous data are linked
up with each other with some relationships between them collected from
different sources. For example, customer data of a shop, product sales data of
the same shop are two heterogeneous data but linked up with a common thing
called products.’ [2]
6.2.Database
‘Database is a systematic way of storing data. Data
collected from various sources are arranged in an organized manner in
databases, where data can be easily retrieved (draw) and processed. Data are information about a particular
individual, object, place etc. This information is stored in a structured form
in databases. Databases are electronic storage place of data. There are various
types of databases. Example: telephone directory contains details of the
individual in possession of the particular telephone number.’ [3]
6.3.Database Management System
The management system which manages these databases is
called DATABASE MANAGEMENT SYSTEM. It enables the user to interact with the
database. It enables the user to retrieve the needed information and make
necessary changes or updating the stored data. It keeps the data secured. It
manages the database according to the requirement of the creator or the one who
stored it in the database.
6.4.Database
Management System Languages
“Database Management System has some special languages
called DMBS languages-
6.4.1.
DDL
(Data Definition Language)- It enables us to create a database or data tables
etc. within a database and also allow us to make changes in the basic structure
of it. The actions allowed by this language are-
a.
Create-
Create statement is used to create a database. To define the structure of the
database, it basically defines it.
b.
Drop-
Drop statement is used to drop/ remove the databases.
c.
Alter-
Alter allows us to change the structure of the databases.
i.
Add-
The add command under alter statement enables us to add new columns.
ii.
Modify-
Modify command enables us to change datatype of the columns.
iii.
Drop-
The drop command within alter enables us to drop column.
d.
Rename-
Rename command help us to rename the table.
e.
Truncate-
Truncate command helps us to remove table.
6.4.2.
DML
(Data Manipulating Language)- It enables us to manipulate the data within the
database.
a.
Insert-
Insert statement enables us to insert data in a database/table in a database.
b.
Update-
It helps us to update or modify the value in table.
c.
Delete-
It is used to delete one or more rows from a table.
6.4.3. DQL (Data Query Language)- It is used to solve queries or retrieve data according to a query from the entire dataset.
a. select- select statement is used to retrieve data from database/table.
6.4.5.
DCL
(Data Control Language)- It enables us to control the privacy of data and also
the accesses.
a.
Grant-
It gives the users access to specified privileges in the database.
b.
Revoke-
it withdraws the user’s access from the specified granted privileges to the
database.
6.4.6.
TCL
(Transactional Control Language)- It enables us to control the already
performed transactions in the database.
a.
Commit-
It commits/saves the transaction.
b.
Rollback-
It rollbacks/undo the changes.
c.
Savepoint-
Sets a savepoint/bookmark within the transaction”[4].
6.5.
[5]“Difference between DELETE, TRUNCATE AND DROP
|
Delete |
Truncate |
Drop |
|
It belongs to DML |
It belongs to DDL |
It belongs to DDL |
|
It removes specific rows/table but no the structure. |
It deletes all rows in table. |
It drops entire table structure. |
|
It can be rolled back. |
It can’t be rolled back. |
It can’t be rolled back. |
|
Where clause can be used. |
Where clause can’t be used. |
Where clause can’t be used. |
|
It is slow |
It is fastest |
It is faster |
6.6.
Types of
Database Management System
“There are many types of DBMS. Some of them are-
1.
‘Distributed
Database: A distributed database is a database system where multiple databases
are logically interconnected through a network. These databases are located
into different locations but their interconnection makes it appear as a single
database.
2.
Centralized
Database: A centralized database is a single database system located in one
server and can be accessed from different sites. Unlike distributed database,
the data is not spread out at different locations.
3.
Relational
Database: In relational database, the data are stored in a two-dimensional
array i.e., in tabular form consisting of rows and columns. Where, columns
consist different attributes of the unique ID i.e., key in each row.
4.
No
SQL Database: No SQL database, the unstructured and semi-structured data in
non-tabular form. Such database is very useful to deal with big data. It
consists of several databases- table, graphs, documents etc.
5.
Cloud
Database: In cloud database, data are stored in a virtual platform and executes
over cloud computing. It stores semi-structured, unstructured and structured
data.
6.
Object-Oriented
Database: In this database, data are stored as object, which are instances of
classes. Classes are groups consisting of objects of same type consisting same
properties.
7.
Hierarchical
Database: It stores data in hierarchical
basis i.e., like a tree like structure. It depicts parent child type of
relationship. Data are linked through nodes. It depicts one to many
relationships.
8.
Network
Database: It stores data in hierarchical basis i.e., like a tree like
structure. It depicts parent child type of relationship. Data are linked
through nodes. It depicts one to many, many to one and one to one
relationships.’[6]
6.7.
Relational Database Management System
‘In
relational database system, data are stored in a two-dimensional array i.e., in
tabular form. Where, each row has a unique key ID whose attributes are provided
along columns. The values in columns are related to corresponding row key ID.
These key Ids are known as primary key which connects with another table
through foreign key (primary key of other table). The database languages are
used to interact and perform various operations within the system.’[7]
Example of Relational Database
|
ID |
Name |
Joining Date |
Position |
WFO/WFH |
|
1 |
M. Roy |
1/1/1996 |
Manager |
WFO |
|
2 |
K. Shah |
1/2/1999 |
Manager |
WFO |
|
3 |
R. Kapoor |
2/5/2010 |
Asst. Manager |
WFO |
|
4 |
S. Das |
3/1/2019 |
Associate |
WFH |
Here, ID is the unique key.
6.7.1.
Constraints in Relational Database
Management System
‘Error can happen also in data entry state. So, it is
essential to check validity at each state in order to maintain reliability and
consistency of the database. Constraints are used to prevent entry of invalid
data. It ensures the accuracy and reliability of the data. Constraints can be
at both column and table level. These constraints are-
1.
Not
Null- Not null constraint is applicable only on column level and not table
level. It doesn’t accept null values but accepts duplicate values.
2.
Unique-
Unique constraint is applicable both on column and table level. It does accept
null values but not duplicate values.
3.
Primary
Key- Primary Key constraint is a combination of both not null key and unique
key. It does not accept null or duplicate values. It is applicable on both
column and table value. Every table has only one primary key. On column level,
only one primary key. On table level, we can have a combination of columns as
primary key.
4.
Foreign
key- In database, when we want to set relation between two tables, then we need
to identify a column present in both the tables which contains common data and
is of same data type. This column is foreign key of one table but primary key
of another table. It connects both the tables as a common column.
5.
Check-
Check constraint is applicable in both the column and table level. It is used
to ensure that the column takes values which specifies a logical condition.
6.
Default-
Default constraint sets a default value for a column in case no value is
specified.
7.
Create
Index- Create index constraint is used to create indexes which is used to
retrieve data from databases easily.’[8]
6.7.2.
Joins in
Relational Database Management System
‘In relational database management system, joins are
used to combine tables. It enables us to combine two or more related tables and
draw valuable information from them, using the constraint foreign key. It uses
a join condition based on which the combination is created.
Types of joins are-
2.
Left Join- Left Join returns all the selected columns from the left joined table. It matches the values of the common column. If the left joined table’s primary key does not have some values from the right joined table’s foreign key, then it returns the attributes from left table as null. And if right joined table’s foreign key does not have some values from the left joined table’s primary key, then those key IDs won’t be included in the joined table.
3.
from right table as null. And if the left joined table’s primary key does not have some values from the right joined table’s foreign key, then those key IDs won’t be included in the joined table.
4.
Full Outer Join- Full outer join combines the result of both left and right joins. It returns all the rows of both the rows of both the tables. In case of non-matching data in common column, it would return null value.
6.
Self-Join-
Self join joins the table with itself based on join condition. The other table
can use an alias name in order to avoid confusion. It enables to extract
queries regarding the table.’[9]
Example of joins:
Suppose we have two tables
Table1: Employee table
|
ID |
Name |
Joining Date |
Position |
WFO/WFH |
|
1 |
M. Roy |
1/1/1996 |
Manager |
WFO |
|
2 |
K. Shah |
1/2/1999 |
Manager |
WFO |
|
3 |
R. Kapoor |
2/5/2010 |
Asst. Manager |
WFO |
|
4 |
S. Das |
3/1/2019 |
Associate |
WFH |
Table2: Salary table
|
ID |
Salary per month |
|
1 |
1,00,000 |
|
2 |
80,000 |
|
5 |
50,000 |
|
6 |
25,000 |
Selected columns for join- Name, position, salary
Inner join:
|
ID |
Name |
Position |
Salary |
|
1 |
M. Roy |
Manager |
1,00,000 |
|
2 |
K. Shah |
Manager |
80,000 |
Left Join:
|
ID |
Name |
Position |
Salary |
|
1 |
M. Roy |
Manager |
1,00,000 |
|
2 |
K. Shah |
Manager |
80,000 |
|
3 |
R. Kapoor |
Asst. Manager |
Null |
|
4 |
S. Das |
Associate |
Null |
Right Join:
|
ID |
Name |
Position |
Salary |
|
1 |
M. Roy |
Manager |
1,00,000 |
|
2 |
K. Shah |
Manager |
80,000 |
|
5 |
Null |
Null |
50,000 |
|
6 |
Null |
Null |
25,000 |
Outer Join:
|
ID |
Name |
Position |
Salary |
|
1 |
M. Roy |
Manager |
1,00,000 |
|
2 |
K. Shah |
Manager |
80,000 |
|
3 |
R. Kapoor |
Asst. Manager |
Null |
|
4 |
S. Das |
Associate |
Null |
|
5 |
Null |
Null |
50,000 |
|
6 |
Null |
Null |
25,000 |
[1] https://en.wikipedia.org/wiki/Data_storage
[2] https://www.tutorialspoint.com/dwh/dwh_data_warehousing.htm#:~:text=A%20data%20warehouse%20is%20constructed,data%20integration%2C%20and%20data%20consolidations.
[3] https://www.oracle.com/in/database/what-is-database/
[4] https://www.indeed.com/career-advice/career-development/database-languages
[5] https://www.scaler.com/topics/difference-between-delete-drop-and-truncate/
[6] https://www.javatpoint.com/types-of-databases
[7] https://www.javatpoint.com/dbms-relational-model-concept
[8] https://www.w3schools.com/sql/sql_constraints.asp
[9] https://www.devart.com/dbforge/sql/sqlcomplete/sql-join-statements.html#:~:text=JOINS%20fundamentals,table%20based%20on%20some%20condition.
Comments
Post a Comment
If you have any doubt or suggestion kindly let me know. Happy learning!