DATABASE MANAGEMENT SYSTEM

                                  

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


There are four types of Data Languages


Data Definition Language (DDL)

Data Manipulation Language(DML)

Data Control Language(DCL)

Transactional Control Language(TCL)


DDL is the short name for 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 (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 that are removed

COMMENT: add comments to the data 

RENAME: rename an object

DML is the short name for Data Manipulation Language which deals with data manipulation and includes most common SQL statements such as 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

DCL is short for Data Control Language which acts as an access specifier to the database. (basically to grant and revoke permissions to users in the database



GRANT: grant permissions to the user for running DML(SELECT, INSERT, DELETE,…) commands on the table

REVOKE: revoke permissions to the user for running DML(SELECT, INSERT, DELETE,…) command on the specified table

TCL is short for Transactional Control Language which acts as a manager for all types of transactional data and all transactions. Some of the commands of TCL are


Rollback: Used to cancel  or Undo changes made in the database 

Commit: It is used to apply or save changes in the database

Save Point: It is used to save the data temporarily in the database

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


Data Definition: It helps create, modify, and remove definitions that define the organization of data in the database. 

Data Updating: It helps insert, modify, and delete the actual data in the database. 

Data Retrieval: It helps in the 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 on a hard disk. Users are allowed to create, delete, and update the files according to their requirements. Let us consider the example of a 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 the Hostel allotment number which is a part of the hostel office. Let us discuss the issues with this system:


Redundancy of data: Data is said to be redundant if the same data is copied at many places. If a student wants to change their Phone number, he or has to get it updated in 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 the same data do not match each other. If the 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 the same data.

Difficult Data Access: A user should know the exact location of the file to access data, so the process is very cumbersome and tedious. If the user wants to search the student hostel allotment number of a student from 10000 unsorted students’ records, how difficult it can be.

Unauthorized Access: File Systems may lead to unauthorized access to data. If a student gets access to a file having his marks, he can change it in an unauthorized way.

No Concurrent Access: The access of the same data by multiple users at the same time is known as concurrency. The file system does not allow concurrency as data can be accessed by only one user at a time.

No Backup and Recovery: The file system does not incorporate any backup and recovery of data if a file is lost or corrupted.

Database Management System – Introduction | Set 1


DBMS 3-tier Architecture


DBMS 3-tier architecture divides the complete system into three inter-related but independent modules as shown below:

DBMS-3-tier-architecture


Physical Level: At the physical level, the information about the location of database objects in the data store is kept. Various users of DBMS are unaware of the locations of these objects. In simple terms, the physical level of a database describes how the data is being stored in secondary storage devices like disks and tapes and also gives insights into additional storage details.

Conceptual Level: At the conceptual level, data is represented in the form of various database tables. For Example, the STUDENT database may contain STUDENT and COURSE tables which will be visible to users but users are unaware of their storage. Also referred to as logical schema, it describes what kind of data is to be stored in the database.

External Level:  An external level specifies a view of the data in terms of conceptual level tables.  Each external level view is used to cater to the needs of a particular category of users. For Example, the FACULTY of a university is interested in looking at course details of students, STUDENTS are interested in looking at all details related to academics, accounts, courses, and hostel details as well. So, different views can be generated for different users. The main focus of the external level is data abstraction.

Data Independence


Data independence means a change of data at one level should not affect another level. Two types of data independence are present in this architecture:


Physical Data Independence: Any change in the physical location of tables and indexes should not affect the conceptual level or external view of data. This data independence is easy to achieve and implemented by most DBMSs.

Conceptual Data Independence: The data at the conceptual level schema and external level schema must be independent. This means a change in conceptual schema should not affect external schema. e.g.; Adding or deleting attributes of a table should not affect the user’s view of the table. But this type of independence is difficult to achieve as compared to physical data independence because the changes in conceptual schema are reflected in the user’s view.

Phases of database design

Database designing for a real-world application starts from capturing the requirements to physical implementation using DBMS software which consists of the following steps shown below:

phases-of-DB


Conceptual Design: The requirements of the database are captured using the high-level conceptual data model. For Example, the ER model is used for the conceptual design of the database.


Logical Design: Logical Design represents data in the form of a relational model. ER diagram produced in the conceptual design phase is used to convert the data into the Relational Model.


Physical Design: In physical design, data in a relational model is implemented using commercial DBMS like Oracle, and DB2.


Advantages of DBMS


 DBMS helps in the efficient organization of data in a database which has the following advantages over a typical file system:


Minimized redundancy and data inconsistency: Data is normalized in DBMS to minimize redundancy which helps in keeping data consistent. For Example, student information can be kept in one place in DBMS and accessed by different users. This minimized redundancy is due to the primary key and foreign keys

Simplified Data Access: A user needs only the name of the relation, not the exact location to access data, so the process is very simple.

Multiple data views: Different views of the same data can be created to cater to the needs of different users. For Example, faculty salary information can be hidden from the student view of data but shown in the admin view.

Data Security: Only authorized users are allowed to access the data in DBMS. Also, data can be encrypted by DBMS which makes it secure.

Concurrent access to data: Data can be accessed concurrently by different users at the same time in DBMS.


                                                                                                                          --Sandeep Kasturi