Overview of data storage and sql commands

Data acquired from various sources

Bhanuchander Shyamala

Data Analyst

Before Databases, data had to be recorded on paper. we had millions of records like that and stored in the racks. when it was necessary to access one of these records finding the files and searching for specific information is the laborious task. There were problems ranging from misplaced records to fires that wiped out entire archives and destroyed the history of societies, Organizations and government.

File Based: In the late 1960's file based database were introduced. In the file based databases, data was maintained in the flat file.

Hierarchical data model: It was IBM's first DBMS and called the information management system.

Network model: It was first DBMS model at Honeywell called Integrated Data Store.

Relational Database: It is the era of relational database and database management. In 1970's, the relational model was proposed by E.F Codd.

Cloud Database: Cloud datastore facilitates us to store, manage and retrieve their data from cloud platforms. The cloud databases are also called as Database as a service(DBaaS)

NoSQL Database: A NoSQL database is an approach to design such databases that can accomodate a wide variety of data models. For eg: Mongo DB

Object Oriented Database: Here data contains in the form of objects and classes.Objects are the real-world entity.An object oriented database is a combination of relational data model features with object oriented principles.

Graph Databases: It is a graphical representation of data.It contains nodes and edges.A node represents entity and edge represents a relationship between two edges.

ACID Properties

DBMS is the management of data that should remain integrated when any changes are done in it.It is because if the inetegrity of the data is affected, whole data will get disturbed and corrupted.Therefore, to maintain the integrity of the data, there are four properties described in the database management system, which are known as ACID properties.

Atomicity:

The term atomicity defines that the data remains atomic.It means if any operation is performed on the data, either it should be performed or executed completely or should not be executed at all.

Consistency:

The word consistency means that the value should remain preserved always.In DBMS, the integrity of the data should be maintained, which means if a change in the database is made,it should remain preserved always

Isolation:

The term 'Isolation' means separation. Isolation is the property of a database where no data should affect the other one and may occur concurrently.

Durability:

Durability ensures the permanency of something. In DBMS, the term durability ensures that the data after the sucessful execution of the operation becomes permanent in the database.

Therefore, the ACID property of DBMS plays a vital role in maintaining the consistency and availability of data in the database.

Keys

  • Keys play an important role in the relational database.
  • It uniquely identifies any record from the table.It is also used to identify and establish a relationship between tables.

    • Primary key
  • It uniquely identifies instance of an entity.An entity can contain multiple keys.
  • For each entity, selection of primary key is based on the requirement and developers

    • Candidate key
  • It is an attribute or set of attributes which can uniquely identify a record.

    • Foreign key
  • They are the column of the table which is used to point to the primary key of another table.

    • Super key
  • Super key is a set of one or more keys that can be used to identify a record uniquely from a table.

    • Unique key
  • It is like primary key but it can accept only one null value and it cannot have duplicate value.

    • Alternate key
  • If a table has more than one candidate key which is not part of primary key and rest of all are called alternate keys.

    • SQL

    It is a language to operate databases, it includes data creation, deletion, retrieving, modifying the data

    Types of sql commands

  • DDL
  • DML
  • DQL
  • DCL
  • TCL

    • DDL(Data Definition Language)

    DDL deals with the structure of the table like creating a table, deleting a table, altering a table, etc.

    Few DDL commands are CREATE, ALTER, DROP, TRUNCATE

    CREATE:

    It is used to create a new table in the database

    SYNTAX: CREATE TABLE TABLE_NAME (COLUMN_NAME DATA TYPE,…...COLUMN N)

    EG: CREATE TABLE STUDENT(NAME VARCHAR2(20) ,Email VARCHAR2(100),DOB DATE)

    DROP:

    It is used to delete both the strucuture and record of table

    SYNTAX: DROP TABLE TABLE_NAME

    EG: DROP TABLE STUDENT;

    ALTER:

    It is used to alter the structures of the table.

    SYNTAX: ALTER TABLE TABLE_NAME ADD (COLUMN_NAME COLUMN-DEF…..N)

    EG:ALTER TABLE STUDENT ADD(ADDRESS VARCHAR2(20));

    TRUNCATE:

    It is used to delete all the rows from the table and free the space containing the table.

    SYNTAX: TRUNCATE TABLE TABLE_NAME

    EG:TRUNCATE TABLE STUDENT

    DML(Data Manipulation Language)

    DML commands are used to modify the database.It is responsible for all forms of changes in the database.

    Few DML commands: INSERT, UPDATE, DELETE

    INSERT:

    Insert is used to insert data into the row of a table.

    SYNTAX: INSERT INTO TABLE_NAME (Col1,Col2...Coln) VALUES (Value1,Value2...ValueN)

    EG: INSERT INTO STUDENT(Author,Subject) VALUES(“Bhanu”,”SQL”);

    UPDATE:

    This command is used to update or modify the value of a column in the table.

    SYNTAX: UPDATE TABLE_NAME SET COL1=VALUE,....COLN-VALUEN [WHERE CONDITION]

    EG: UPDATE STUDENTS SET User=’Peter’ where Student_id=’2’;

    DELETE:

    It is used to remove one or more rows from a table.

    SYNTAX: DELETE FROM TABLE_NAME [WHERE CONDITION];

    EG:DELETE FROM STUDENT WHERE Student=’Bhanu’

    DQL(Data Query Language)

    DQL is used to fetch the data from the database.

    SELECT is the only command to retrieve the data.

    DCL(Data Control Language)

    These commands are used to grant and take back authority from any database user.

    GRANT: It is used to give user access privileges to a database.

    REVOKE: It is used to remove the access privileges from the user

    TCL(Transaction Control Language)

    These commands can only be used with commands like INSERT, DELETE and UPDATE only.

    COMMIT: Used to save the changes made to the table permanently

    ROLLBACK: Used to get back to the previous permanent status of the table. Similar to the UNDO command.

    To be continued....

    Comments

    Post a Comment