Architecture of DBMS

Architecture of DBMS:

The Architecture of most of commercial dbms are available today is mostly based on this ANSI-SPARC database architecture. In this architecture the database schemas can be defined at three levels. The 3-tier architecture separates its tiers from each other based on the complexity of the users and how they use the data present in the database. The 3-tier provides data abstraction; which hides the low level complexities from end users.

There are following three levels or layers of DBMS architecture:

·        External Level

·        Conceptual Level

·        Internal Level

The above diagram describes three-tier architecture. Mapping is the process of transforming request response between various database levels of architecture. In External / Conceptual mapping, DBMS transforms a request on an external schema against the conceptual schema. In Conceptual / Internal mapping, it is necessary to transform the request from the conceptual to internal levels. The objective of three-level architecture is to separate each user's view of the database from the way the database is physically represented.

·        External Level or View level:

At the external level, a database contains several schemas that sometimes called as subschema. The subschema is used to describe the different user’s view of the database. External level is the one which is closest to the end users. This level deals with the way in which individual users view data. Individual users are given different views according to the user's requirement. Each view describes the database part that a particular user group is interested and hides the remaining database from that user group.

For Example, FACULTY of a college is interested in looking course details of students, STUDENTS are interested in looking all details related to academics, accounts, courses and hostel details as well.

·        Conceptual Level or Logical level:

This level describes what data is stored in the database and the relationships among the data. The middle level in the three level architecture is the conceptual level. This level contains the logical structure of the entire database as seen by the DBA. It is a complete view of the data requirements of the organization that is independent of any storage considerations. The conceptual level represents: All entities, their attributes, and their relationships.

For example, in student database the entity is student. An attribute is a characteristic of interest about an entity. For example, in case of student database Roll No, Name, Class, Address etc. are attributes of entity student.

·        Internal level or Storage level:

It is the physical representation of the database on the computer. This level describes how the data is stored in the database. The internal level is the one that concerns the way the data are physically stored on the hardware. It covers the data structures and file organizations used to store data on storage devices. It interfaces with the operating system access methods to place the data on the storage devices, build the indexes, retrieve the data, and so· on.

The internal level is concerned with storage space allocation for data and indexes, record descriptions for storage (with stored sizes for data items), record placement, data compression and data encryption techniques.

Advantages of Three-tier Architecture:

· The main objective of it is to provide data abstraction.

·  Same data can be accessed by different users with different customized views.

· The user is not concerned about the physical data storage details.

· Physical storage structure can be changed without requiring changes in internal structure of the database as well as users view.

· Conceptual structure of the database can be changed without affecting end users.

-profshardulp.patil@gmail.com
x

Data Abstraction

Data Abstraction:

Database systems are made-up of complex data structures. Database is full of data and records. What we see in rows and columns is quite different when it reaches the memory. What we see is the actual data. But when they are stored in the memory like disks or tapes, they are stored in the form of bits. But any users will not understand these bits. User needs to see the actual data to understand. But all the details about the data stored in the memory are not necessary for the users. User needs only little information that he is interested or wants to work with. To ease the user interaction with database, the developers hide internal irrelevant details from users. This process of hiding irrelevant details from user is called data abstraction.

Data abstraction is a process of representing the essential features without including implementation details. Many database-systems users are not computer trained, developers hide the complexity from users through several levels of abstraction, to simplify users’ interactions with the system.

There are mainly three levels of data abstraction:

Physical Level / Internal Level:

Logical Level / Conceptual Level:

View Level / External Level:

Physical Level / Internal Level:

This is the lowest level in data abstraction. This level describes how the data is actually stored in the physical memory like magnetic tapes, hard disks etc. In this level the file organization methods like hashing, sequential, B+ tree and access methods like sequential or random access are comes into picture. Usability, size of memory, and the number of times the records are factors which we need to know while designing the database.

Logical Level / Conceptual Level:

This is the middle level of 3-level data abstraction. It describes the actual data stored in the database in the form of tables and relates them by means of mapping. This level will not have any information on what a user views at external level. This level will have all the data in the database.

View Level / External Level:

The highest level of abstraction describes only part of the entire database. The variety of information stored in a large database. Many users of the database system do not need all this information; instead, they need to access only a part of the database. The view level of abstraction exists to simplify their interaction with the system. The users will have different views here, based on their levels of access rights. For example, student will not have access to see Lecturers salary details, one employee will not have access to see other employees details, unless he is a manager.

Example: Let’s say we are storing customer information in a customer table. At physical level these records can be described as blocks of storage (bytes, gigabytes, terabytes etc.) in memory. These details are often hidden from the programmers.

At the logical level these records can be described as fields and attributes along with their data types, their relationship among each other can be logically implemented. The programmers generally work at this level because they are aware of such things about database systems.

At view level, user just interact with system with the help of GUI and enter the details at the screen, they are not aware of how the data is stored and what data is stored; such details are hidden from them.

-profshardulp.patil@gmail.com

Structure of DBMS

Structure of DBMS:

DBMS (Database Management System) acts as an interface between the user and the database. The user requests the DBMS to perform various operations (insert, delete, update and retrieval) on the database. The components of DBMS perform these requested operations on the database and provide necessary data to the users. A DBMS is partitioned into modules that deal with each of the responsibilities of the overall system. The functional components of a database system can be broadly divided into the storage manager and the query processor components.

1.      Query Processor:

Query Processor translates statements in a query language into low-level instructions the database manager understands. The Query Processor simplifies and facilitates access to data. The Query processor includes the following component.

· DML Pre-compiler: DML pre-compiler converts DML statements embedded in an application program to normal procedure calls in a host language.  It also attempts to transform user's request into an equivalent query language but more efficient form. The Pre-compiler must interact with the DML compiler to generate the appropriate code.

· DML Compiler: The DML compiler translates DML statements in a query language into an evaluation plan consisting of low-level instructions that the query evaluation engine understands. The DML compiler also performs query optimization, which is it picks the lowest cost evaluation plan from among the alternatives. Query evaluation engine executes low level instructions generated by the DML compiler.

· DDL Interpreter: It interprets the DDL statements and records them in a set of tables containing meta data or data dictionary. It interprets DDL statements and records the definitions in the data dictionary.

·       · Query Evaluation Engine: It executes low-level instructions generated by the DML compiler.

2.      Storage Manager :

Storage Manager provides the interface between the low-level data stored in the database and application programs and queries submitted to the system. The storage manager is responsible for the interaction with the file manager. The raw data are stored on the disk using the file system, which is usually provided by a conventional operating system. The storage manager is responsible for storing, retrieving, and updating data in the database.

· Authorization and Integrity Manager: It tests for the satisfaction of integrity constraints checks the authority of users to access data.

· Transaction Manager: It ensures that the database remains in a consistent state despite the system failures and that concurrent transaction execution proceeds without conflicting.

· File Manager: It manages the allocation of space on disk storage and the data structures used to represent information stored on disk.

· Buffer Manager: It is responsible for fetching data from disk storage into main memory and deciding what data to cache in memory.

3.      Data Structure / Storage :

Following data structures are required as a part of the physical system implementation.

· Data Files: It stores the database.

· Data Dictionary: It stores metadata (data about data) about the structure of the database.

· Indices: Provide fast access to data items that hold particular values.

·Statistical Data: It stores statistical information about the data in the database. This information is used by query processor to select efficient ways to execute query.

-profshardulp.patil@gmail.com

Services of DBMS

Services of DBMS:

There are the following important services provided by a DBMS:

1.      Transaction Management:

A transaction is a series of database operations, carried out by a single user or application program, which accesses or changes the contents of the database. Therefore, a DBMS must provide a mechanism to ensure either that all the updates corresponding to a given transaction are made or that none of them is made.

2.      Concurrency Control:

Since DBMSs support sharing of data among multiple users, they must provide a mechanism for managing concurrent access to the database. DBMSs ensure that the database kept in consistent state and that integrity of the data is preserved.

3.      Recovery Management:

The DBMS provides mechanisms for backing up data periodically and recovering from different types of failures. This prevents the loss of data.

4.      Security Management:

The DBMS protects the database against unauthorized access, either international or accidental. It furnishes mechanism to ensure that only authorized users an access the database.

5.      Language Interface:

All DBMS provide interface to enable applications to use DBMS services. They provide data access via Structured Query Language (SQL). The DBMS query language contains two components: (a) a Data Definition Language (DDL) and (b) a Data Manipulation Language (DML).

6.      Storage Management:

It provides a mechanism for management of permanent storage of the data. The internal schema defines how the data should be stored by the storage management mechanism and the storage manager interfaces with the operating system to access the physical storage.

7.      Data Catalog Management:

The DBMS provides a data dictionary or system catalog function in which descriptions of data items are stored and which is accessible to users.

Advantages & Disadvantages of DBMS

Features / Advantages of DBMS:

1.      Self-Describing Nature:

A database system is referred to as self-describing because it not only contains the database itself, but also metadata which defines and describes the data and relationships between tables in the database. This information is used by the DBMS software or database users if needed. This separation of data and information about the data makes a database system totally different from the traditional file-based system in which the data definition is part of the application programs.

2.      Redundancy Control:

In non-database systems (traditional computer file processing), each application program has its own files. In this case, the duplicated copies of the same data are created at many places. In DBMS, all the data of an organization is integrated into a single database. The data is recorded at only one place in the database and it is not duplicated.

3.      Elimination of inconsistency:

When the same data is duplicated and changes are made at one site, which is not propagated to the other site, it gives rise to inconsistency and the two entries regarding the same data will not agree. At such times the data is said to be inconsistent.

By controlling the data redundancy, the data inconsistency is eliminated. If a data item appears only once, any update to its value has to be performed only once and the updated value (new value of item) is immediately available to all users. 

4.      Sharing of Data:

In DBMS, data can be shared by authorized users of the organization. The database administrator manages the data and gives rights to users to access the data. Many users can be authorized to access the same piece of information simultaneously. The remote users can also share same data. Similarly, the data of same database can be shared between different application programs.

5.      Maintenance of Integrity:

Since DBMS is a central system, so standard can be enforced easily may be at Company level, Department level, National level or International level. The standardized data is very helpful during migration or interchanging of data.

6.      Control over Security:

Data security is the protection of the database from unauthorized users. Only the authorized persons are allowed to access the database. Some of the users may be allowed to access only a part of database i.e., the data that is related to them or related to their department.

DBMS allows different levels of access to different users based on their roles. In the school database, individual students will have access to their data alone, while their teachers will have access to all the students whom they are teaching and for the subjects that they are teaching. Class teacher will be able to see the reports of all the students in that class, but not other classes.

7.      Transaction Control / Data Atomicity:

DBMS makes sure either the transaction is fully complete or it is rolled back to the previous committed state. It does not allow the system to be in a partially committed state.

A transaction in commercial databases is referred to as atomic unit of work. For example, when you purchase something from a point of sale (POS) terminal, a number of tasks are performed such as;

·        Company stock is updated.

·        Amount is added in company's account.

·        Sales person's commission increases etc.

All these tasks collectively are called an atomic unit of work or transaction. These tasks must be completed in all; otherwise partially completed tasks are rolled back.

8.      Data Independence:

The separation of data structure of database from the application program that is used to access data from database is called data independence. In DBMS, database and application programs are separated from each other. The DBMS sits in between them. You can easily change the structure of database without modifying the application program.

9.      Concurrency Control:

DBMS provide access to multiple users to access the database at the same time. It has its own mechanism to have concurrency accesses and hence avoid any incorrect data in the system. For example, if both users attempt to perform update operation on the same record, then one may overwrite the values recorded by the other.

10.   Backup and Recovery:

In a computer file-based system, the user creates the backup of data regularly to protect the valuable data from damaging due to failures to the computer system or application program. It is a time consuming method, if volume of data is large. Most of the DBMSs provide the 'backup and recovery' sub-systems that automatically create the backup of data and restore data if required.

Disadvantages of DBMS:

1.      Complexity:

The provision of the functionality that is expected of a good DBMS makes the DBMS an extremely complex piece of software. Database designers, developers, database administrators and end-users must understand this functionality to take full advantage of it. Failure to understand the system can lead to bad design decisions, which can have serious consequences for an organization.

2.      Size:

The complexity and breadth of functionality makes the DBMS an extremely large piece of software, occupying many megabytes of disk space and requiring substantial amounts of memory to run efficiently.

3.      Higher impact of a failure:

The centralization of resources increases the vulnerability of the system. Since all users and applications rely on the availability of the DBMS, the failure of any component can bring operations to a halt.

4.      Cost of DBMS:

The cost of DBMS varies significantly, depending on the environment and functionality provided. There is also the recurrent annual maintenance cost.

5.      Additional Hardware costs:

The disk storage requirements for the DBMS and the database may necessitate the purchase of additional storage space. Furthermore, to achieve the required performance it may be necessary to purchase a larger machine, perhaps even a machine dedicated to running the DBMS. The procurement of additional hardware results in further expenditure.

6.      Cost of Conversion:

       In some situations, the cost of the DBMS and extra hardware may be insignificant compared with the cost of converting existing applications to run on the new DBMS and hardware. This cost is one of the main reasons why some organizations feel tied to their current systems and cannot switch to modern database technology.

-profshardulp.patil@gmail.com

x

Functions of DBMS

Functions of DBMS: 

DBMS performs several important functions that guarantee the integrity and consistency of the data in the database. The most important functions of Database Management System are:

1.      Data Dictionary Management:

DBMS stores definitions of the data elements and their relationships (metadata) in a data dictionary. The DBMS uses the data dictionary to look up the required data component structures and relationships which relieves you from coding such complex relationships in each program. Additionally, any changes made in a database structure are automatically recorded in the data dictionary, thereby freeing you from having to modify all of the programs that access the changed structure.

2.      Data Storage Management:

The DBMS creates and manages the complex structures required for data storage, thus relieving you from the difficult task of defining and programming the physical data characteristics. It provides a mechanism for management of permanent storage of the data. The internal schema defines how the data should be stored by the storage management mechanism and the storage manager interfaces with the operating system to access the physical storage.

3.      Data Transformation and Presentation:

The DBMS transforms entered data in to required data structures. By using the data transformation and presentation function the DBMS can determine the difference between logical and physical data formats. That is, the DBMS formats the physically retrieved data to make it conform to the user’s logical expectations.

For example, imagine an enterprise database used by a multinational company. An end user in England would expect to enter data such as July 11, 2009, as “11/07/2009.” In contrast, the same date would be entered in the United States as “07/11/2009.” Regardless of the data presentation format, the DBMS system must manage the date in the proper format for each country.

4.      Security Management:

Security Management is another important function of the Database Management System. The DBMS creates a security system that enforces user security and data privacy. Security rules determine which users can access the database, which data items each user can access, and which data operations (read, add, delete, or modify) the user can perform. This is especially important in multiuser database systems.

5.      Multiuser Access Control:

To provide data integrity and data consistency, the DBMS uses sophisticated algorithms to ensure that multiple users can access the database concurrently without compromising the integrity of the database. Multiuser access control is a very useful tool in a DBMS, it enables multiple users to access the database simultaneously without affecting the integrity of the database.

6.      Backup and Recovery Management

The DBMS provides backup and data recovery to ensure data safety and integrity. Current DBMS systems provide special utilities that allow the DBA to perform routine and special backup and restore procedures.

Recovery management deals with the recovery of the database after a failure, such as a bad sector in the disk or a power failure. Such capability is critical to preserving the database’s integrity.

7.      Data Integrity Management

The DBMS promotes and enforces integrity rules, thus minimizing data redundancy and maximizing data consistency. The data relationships stored in the data dictionary are used to enforce data integrity. Ensuring data integrity is especially important in transaction-oriented database systems.

8.      Database Access Languages and Application Programming Interfaces:

The DBMS provides data access through a query language. A query language is a non-procedural language—one that lets the user specify what must be done without having to specify how it is to be done. Structured Query Language (SQL) is the default query language and data access standard supported by the majority of DBMS vendors.

9.      Database Communication interfaces:

This refers to how a DBMS can accept different end user requests through different network environments. For example, the DBMS might provide access to the database via the Internet through the use of Web browsers such as Mozilla Firefox or Microsoft Internet Explorer. In this environment, communications can be accomplished in several ways.


-profshardulp.patil@gmail.com

File Processing System

File Processing System:

File processing systems was an early attempt to computerize the manual filing system. A file system is a method for storing and organizing computer files and the data they contain to make it easy to find and access them. Before DBMS was invented, Information was stored using File Processing System. In this System, data is stored in permanent system files (secondary Storage). Different application programs are written to extract data from these files and to add record to these files. File systems may use a storage device such as a hard disk or CD-ROM and involve maintaining the physical location of the files.

Here is the list of some important characteristics of file processing system:

§  It is a group of files storing data of an organization.

§  Each file is independent from one another.

§  Each file is called a flat file.

§  Each file contained and processed information for one specific function, such as accounting or inventory.

§  Files are designed by using programs written in programming languages such as COBOL, C, C++.

§   The physical implementation and access procedures are written into database application; therefore, physical changes resulted in intensive rework on the part of the programmer.

§  As systems became more complex, file processing systems offered little flexibility, presented many limitations, and were difficult to maintain.

Disadvantages of File System:

1. Data Redundancy and Inconsistency:

It is possible that the same information may be duplicated in different files. This results in data redundancy and inconsistency.

Consider following two data files:

Saving account data file: Stores information about customer. {acc_no, name, social_security, addr, teleph_no};

Checking account data file: Stores information about customer. {acc_no, name, social_security, addr, teleph_no};

Fields  {name, social_security, addr, teleph_no} are same in both the files i.e. Duplication of data is there which results data redundancy.

Data Redundancy increases the cost of storing & retrieving the data. Various copies of same data may contain different values then it results in Inconstancy of data. It may create a risk of out dated values of data.

For Example: If you change customer name in saving account data file then his name should be changed in all other files related to customer.

2. Difficulty in Accessing the Data:

File processing system doesn't allow needed data to be retrieved in a convenient and efficient manner. If a user wants information in a specific manner then he requires creating a program for it.

For Example, consider a data file, Saving account data file with fields {acc_no, name, social_security, addr, balance}. Application programs to access the data are written. But if user wants to display only those records for which balance is greater than Rs:10,000 and is that program is not written, then it’s difficult to access that data.

3. Data Isolation:

Because data are scattered in various files, and files may be in different formats, it is difficult to write new application programs to retrieve the appropriate data. If you want to extract data from two file then you are required to which part of the file is needed and how they are related to each other.

4. Integrity Problems:

A collection of data is integrated if it meets certain consistency constraints. A programmer always puts these constraints in the programs by adding some codes. In File Processing System, poor data integrity often arises and it becomes very difficult to add new constraints at that time.

For Example: The maximum marks of the student can never be more than 100.

5. Atomicity problem:

Atomicity is required to save the data values, it means that information is completely entered or canceled at all. Any system may fail at any time and at that time it is desired that data should be in a consistent state. A computer system is subject failure. In many applications, it is crucial to ensure that, once a failure has occurred and has been detected, the data are stored to the consistent state that existed prior to the failure. It is difficult to ensure this property in a conventional file-processing system.

For Example: If you are buying a ticket from railway and you are in the process of money transaction. Suddenly, your internet got disconnected then you may or may not have paid for the ticket. If you have paid then your ticket will be booked and if not then you will not be charged anything. That is called consistent state, means you have paid or not.

6. Concurrent-access anomalies:

If multiple users are updating the same data simultaneously it will result in inconsistent data state. In file processing system it is very difficult to handle this using program code. This results in concurrent access anomalies.

For example, a student wants to borrow a book from the library. He searches for the book in the library file and sees that only one copy is available. At the same time another student also, wants to borrow same book and checks that one copy available. First student opt for borrow and gets the book. But it is still not updated to zero copy in the file and the second student also opt for borrow! But there are no books available. This is the problem of concurrent access in the file system.

7. Security Problems:

Poor data security is the most threatening problem in File Processing System. There is very less security in File Processing System as anyone can easily modify and change the data stored in the files. All the users must have some restriction of accessing data up to a level.

For Example: If a student can access his data in the college library then he can easily change books issued date. Also he can change his fine detains to zero.

Comparison of File Management and DBMS:

-profshardulp.patil@gmail.com