DBMS – Basics

DBMS stands for Database management system is software than help users to access database efficiently. In this lesson you will learn – DBMS Basics

Before DBMS, data was stored in operating system files and each file had its own rules and constraints that required specific applications to access the data.

If the data organization were to change with in the files, then application logic must also change. Sometimes you must write a new application to access the modified data files.

Storing and retrieving of data was very difficult with operating system files. The major disadvantages of these data files are as follows.

Redundancy and Inconsistency

Redundancy means duplicate information. Programmers insert new data or update the data files which leads to redundancy for two reasons.

1. Same information in different files in different format can cause redundancy.
2. Multiple copies of same file is also redundant.

The second problem with the data file is inconsistency. The files are stored in different locations in a different format for different applications, this leads to inconsistency. Updating files concurrently is another reason for the inconsistency.

The cost of accessing the data is also high because applications are written in many programming languages.

Difficulty accessing data

If a user wants some information extracted from data files, we cannot query the flat files directly. As a result, the information must be gathered manually or we have to write an application for that.

There is always a delay associated with the process or retrieving valuable information from data files.

Constraints

Data stored in files must follow some constraints so that it is consistent everywhere in the system.

Suppose, you are working with customer bank accounts and the constraint is to keep the account above $1000. The application has a piece of code that understand this constraint.

If the constraints change then entire application logic must change because the piece of code with the constraint is part of a bigger software program.

Atomicity Problem

If the system fails to record certain data information due to application failure, the database will be inconsistent. Then it is necessary to – Do complete transaction or no transaction at all. This is principle of Atomicity.

If the atomicity is not maintained, then the database will be in an inconsistent state and atomicity is not possible in file-processing systems.

Concurrent Access

There is no system in file-based data storage to maintain concurrent access. If two application users access the same data file – then the update from each concurrent user will not be recorded properly, if both of them write data file at the same time. There is no concurrency control mechanism.

Security Problem

There is no way to access only the relevant part of database files. The application will access all the information it is allowed to access regardless of what it is going to process.

The DBMS on the other hand, load relevant part of the database only.

DBMS Basics – Database system

We know that the DBMS does some amazing things and it is a software product. The core functions of a DBMS can be generalized into following.

1. Database Design
2. Data Analysis
3. Concurrency Control

Complex data structures are hidden and an abstract view of data is presented to users to simplify communication with the database. This is because the technical expertise of users are different.

There are 3 level of data abstraction

1. Physical – At this level the system administrator is user who decide and maintain the physical storage for DBMS.
2. Conceptual – The Database administrator is user at logical level and design, maintain the organization database.
3. External or View – The user at this level at normal users who use the system for other purposes.

At each level, the user does not need to know the complexity at the level below and this is known as data independence.

Database Schema

Overall design of database is called the database schema. The database schema is very important during the design process. There is a relation between the level of abstraction and database schema.

For example,

• Physical Schema at physical level.
• Logical schema at Logical level
• Schema at view level is sub-schema.

The information stored in database at a particular moment is called instance of database. The instance of database contains collections of records.

Data Model

A data model describe a way to describe the database at physical, logical and view levels. It helps user to store data in terms of data model. These are the main type of data model used in DBMS.

1. Relational Model
2. E-R Model
3. Object-Based data Model
4. Semi-structured Data model

Database Languages

Data-Manipulation Language or DML

DML is the query language and performs some data manipulations. You can divide the DML into two part – Procedural and Non-procedural DML.

The common tasks performed by DML are

1. Insert
2. Delete
3. Update
4. Query

Procedural DML you can define a procedure or function. It defines what to query and how to query. In case of procedural DML, you many require a procedural language. They are hard to write because you need expertise in two languages.

Non-procedural DML also known as Declarative DMLs only define what to query.

e.g,

select * from department;

Data-Definition Language or DDL

Storage structure and access methods in DDL and stored in data dictionary contains metadata information – (data about data.)

e.g. create table, alter table, change a field name or type, etc.

create table department (deptid number (2), deptname varchar2 (15));

DDL for constraints

• Domain constraints – integer, float, etc.
• Referential integrity – attribute in a table must appear in another table (referential integrity). Any change that break this constraint is denied.
• Assertion – a condition that the database must satisfy all the time.

Relational Database

The relational database is made of tables and each table has rows and columns. The tables are also known as relations. The rows are called the tuples and the columns are attributes or fields.

Department Relation

DeptID	DeptName	Location
12	Finance	London
34	Operations	Tokyo

In the DBMS Basics – example above, the columns – DeptID, DeptName and Location are fields or attributes of a Department.

The row with DeptID 12 and 34 are tuples or records of Department table.

References

Avi Silberschatz, Henry F. Korth, and S. Sudarshan. 27-Jan-2010. Database System Concepts. McGraw-Hill Education.

Ramakrishnan, Johannes Gehrke, and Raghu. 1996. Database Management Systems. McGraw Hill Education; Third edition (1 July 2014).