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 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.
- Same information in different files in different format can cause redundancy.
- Multiple copies of same file is also redundant.
The second problem with data file is inconsistency. The files are stored in different locations in different format for different applications, this leads to inconsistency. Updating files concurrently is another reason for 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.
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 constraint is part of bigger software program.
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.
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.
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 what it is going to process.
The DBMS on the other hand, load relevant part of 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 generalized into following.
- Database Design
- Data Analysis
- 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
- Physical – At this level the system administrator is user who decide and maintain the physical storage for DBMS.
- Conceptual – The Database administrator is user at logical level and design, maintain the organization database.
- 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.
Overall design of database is called the database schema. The database schema are very important during design process. There is a relation between the level of abstraction and database schema.
- 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.
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.
- Relational Model
- E-R Model
- Object-Based data Model
- Semi-structured Data model
Data-Manipulation Language or DML
DML is the query language and perform some data manipulations. You can divide the DML into two part – Procedural and Non-procedural DML.
The common tasks performed by DML are
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.
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.
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.
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.
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).