Skip to main content

Important Terminology of DBMS (Database Management System)

Database: Database is a collection of inter-related data which helps in efficient retrieval, insertion and deletion of data from database and organizes the data in the form of tables, views, schemas, reports etc. For Example, university database organizes the data about students, faculty, and admin staff etc. which helps in efficient retrieval, insertion and deletion of data from it.

DDL is short name of Data Definition Language, which deals with database schemas and descriptions, of how the data should reside in the database.

  • CREATE: to create a database and its objects like (table, index, views, store procedure, function, and triggers)
  • ALTER: alters the structure of the existing database
  • DROP: delete objects from the database
  • TRUNCATE: remove all records from a table, including all spaces allocated for the records are removed
  • COMMENT: add comments to the data dictionary
  • RENAME: rename an object

    DML is short name of Data Manipulation Language which deals with data manipulation and includes most common SQL statements such SELECT, INSERT, UPDATE, DELETE, etc., and it is used to store, modify, retrieve, delete and update data in a database.

  • SELECT: retrieve data from a database
  • INSERT: insert data into a table
  • UPDATE: updates existing data within a table
  • DELETE: Delete all records from a database table
  • MERGE: UPSERT operation (insert or update)
  • CALL: call a PL/SQL or Java subprogram
  • EXPLAIN PLAN: interpretation of the data access path
  • LOCK TABLE: concurrency Control

    Database Management System: The software which is used to manage database is called Database Management System (DBMS). For Example, MySQL, Oracle etc. are popular commercial DBMS used in different applications. DBMS allows users the following tasks:

  • Data Definition: It helps in creation, modification and removal of definitions that define the organization of data in database.

    Data Updation: It helps in insertion, modification and deletion of the actual data in the database.

    Data Retrieval: It helps in retrieval of data from the database which can be used by applications for various purposes.

    User Administration: It helps in registering and monitoring users, enforcing data security, monitoring performance, maintaining data integrity, dealing with concurrency control and recovering information corrupted by unexpected failure.

    Paradigm Shift from File System to DBMS

     File System manages data using files in hard disk. Users are allowed to create, delete, and update the files according to their requirement. Let us consider the example of file based University Management System. Data of students is available to their respective Departments, Academics Section, Result Section, Accounts Section, Hostel Office etc. Some of the data is common for all sections like Roll No, Name, Father Name, Address and Phone number of students but some data is available to a particular section only like Hostel allotment number which is a part of hostel office. Let us discuss the issues with this system:

    • Redundancy of data: Data is said to be redundant if same data is copied at many places. If a student wants to change Phone number, he has to get it updated at various sections. Similarly, old records must be deleted from all sections representing that student.
    • Inconsistency of Data: Data is said to be inconsistent if multiple copies of same data does not match with each other. If Phone number is different in Accounts Section and Academics Section, it will be inconsistent. Inconsistency may be because of typing errors or not updating all copies of same data.
    • Difficult Data Access: A user should know the exact location of file to access data, so the process is very cumbersome and tedious. If user wants to search student hostel allotment number of a student from 10000 unsorted students’ records, how difficult it can be.
    • Unauthorized Access: File System may lead to unauthorized access to data. If a student gets access to file having his marks, he can change it in unauthorized way.
    • No Concurrent Access: The access of same data by multiple users at same time is known as concurrency. File system does not allow concurrency as data can be accessed by only one user at a time.
    • No Backup and Recovery: File system does not incorporate any backup and recovery of data if a file is lost or corrupted


    • Anurag Rana

    Comments

    Post a Comment

    Popular posts from this blog

    Standard and Formatted Input / Output in C++

    The C++ standard libraries provide an extensive set of input/output capabilities which we will see in subsequent chapters. This chapter will discuss very basic and most common I/O operations required for C++ programming. C++ I/O occurs in streams, which are sequences of bytes. If bytes flow from a device like a keyboard, a disk drive, or a network connection etc. to main memory, this is called   input operation   and if bytes flow from main memory to a device like a display screen, a printer, a disk drive, or a network connection, etc., this is called   output operation . Standard Input and Output in C++ is done through the use of  streams . Streams are generic places to send or receive data. In C++, I/O is done through classes and objects defined in the header file  <iostream> .  iostream  stands for standard input-output stream. This header file contains definitions to objects like  cin ,  cout , etc. /O Library Header Files There are...
    Post a Comment
    Read more

    Genetic Algorithm: Population, Fitness Function, Parent Selection, Cross over, Mutation

    Genetic Algo Population Population is a subset of solutions in the current generation. It can also be defined as a set of chromosomes. There are several things to be kept in mind when dealing with GA population − The diversity of the population should be maintained otherwise it might lead to premature convergence. The population size should not be kept very large as it can cause a GA to slow down, while a smaller population might not be enough for a good mating pool. Therefore, an optimal population size needs to be decided by trial and error. The population is usually defined as a two dimensional array of –  size population, size x, chromosome size . Population Initialization There are two primary methods to initialize a population in a GA. They are − Random Initialization  − Populate the initial population with completely random solutions. Heuristic initialization  − Populate the initial population using a known heuristic for the problem. It has been observed that the e...
    Post a Comment
    Read more

    Normalization in DBMS: 1NF, 2NF, 3NF and BCNF in Database

    Normalization   is a process of organizing the data in database to avoid data redundancy, insertion anomaly, update anomaly & deletion anomaly.  Anomalies in DBMS There are three types of anomalies that occur when the database is not normalized. These are – Insertion, update and deletion anomaly. Let’s take an example to understand this. Example : Suppose a manufacturing company stores the employee details in a table named employee that has four attributes: emp_id for storing employee’s id, emp_name for storing employee’s name, emp_address for storing employee’s address and emp_dept for storing the department details in which the employee works. At some point of time the table looks like this: emp_id emp_name emp_address emp_dept 101 Nikhil Kangra D001 101 Nikhil Kangra D002 123 Ashish Shimla D890 166 Rahul Pathankot D900 166 Rahul Pathankot D004 The above table is not normalized.  Update anomaly : In the above table we have two rows for employee Nikhil as he belongs ...
    Post a Comment
    Read more