Users of DBMS

Users of DBMS:

Database users are the one who really use and take the benefits of database. There will be different types of users depending on their need and way of accessing the database.

1.      Database Administrator (DBA)

2.      Application programmers

3.      System Analyst

4.      End users

1.      Database Administrator (DBA):

Database Administrator (DBA) is the person or a group of persons who makes the strategic and policy decisions regarding the data of the enterprise, and who provide the necessary technical support for implementing these decisions. Therefore, DBA is responsible for overall control of the system at a technical level. In database environment, the primary resource is the database itself and the secondary resource is the DBMS and related software administering these resources is the responsibility of the Database Administrator (DBA).

2.      Application programmers:

As its name shows, application programmers are the one who writes application programs that uses the database. These users write application programs to interact with the database. Application programs can be written in some programming language such a C++,VB, ASP, .Net, JAVA or some higher level fourth generation language. Such programs access the database by issuing the appropriate request, typically a SQL statement to DBMS. These programs meet the user requirement and made according to user requirements. Retrieving information, creating new information and changing existing information is done by these application programs.

They interact with DBMS through DML (Data manipulation language) calls. And all these functions are performed by generating a request to the DBMS. If application programmers are not there then there will be no creativity in the whole team of Database.

3.      System Analyst:

System Analyst determines the requirement of end users, especially naive and parametric end users and develops specifications for transactions that meet these requirements. System Analyst gathers information according to user requirements needed to develop a solution on it. System Analyst plays a major role in database design, its properties; the structure prepares the system requirement statement, which involves the feasibility aspect, economic aspect, technical aspect etc. of the system.

4.      End users:

End users are those who access the database from the terminal end. End users are the users, who use the applications developed. End users need not know about the working, database design, the access mechanism etc. They just use the system to get their task done. There are basically two types of end users:

A.    Casual User:

These users have great knowledge of query language. Casual users access data by entering different queries from the terminal end. They do not write programs but they can interact with the system by writing queries.

      B.     Naive User:

Any user who does not have any knowledge about database can be in this category. Their task is to just use the developed application and get the desired results.     

-profshardulp.patil@gmail.com

Schema, Subschema and Instance

Schema:

Design of a database is called the schema. A database schema is the skeleton structure that represents the logical view of the entire database. It defines how the data is organized and how the relations among them are associated. It is the collection of named objects. The schema contains names of tables, columns of each table, datatype, triggers, functions, views, stored procedures and other objects are included in the schema. A database schema can be represented by using the visual diagram. That diagram shows the database objects and relationship with each other. A database schema is designed by the database designers to help programmers whose software will interact with the database. The process of database creation is called data modeling. The changes in a schema are not applied so frequently, but occasionally changes need to be applied as the requirements of application changes.

         For Example: In the following diagram, we have a schema that shows the relationship between three tables: Course, Student and Section. The diagram only shows the design of the database, it doesn’t show the data present in those tables. Schema is only a structural view (design) of a database as shown in the diagram below.

Schema is of three types: Physical schema, logical schema and view schema.

              The design of a database at physical level is called physical schema. It describes how the data stored on the disk or the physical storage.

           Design of database at logical level is called logical schema, programmers and database administrator work at this level. At this level data can be described as certain types of data records gets stored in data structures.

               Design of database at view level is called view schema. This generally describes end user interaction with database systems.

Subschema:

            A subschema provides a view of the database as seen by an application program. This view is often a subset of the complete schema definition. A subschema is used at run time to provide the DBMS with a description of those portions of the database that are accessible to the application program. The subschema allows the user to view only that part of the database that is of interest to him. The subschema defines the portion of the database as seen by the application programs and the application programs can have different view of data stored in the database.

              The subschema identifies the areas, records, elements, and sets which are accessible. The subschema identifies the Data Manipulation Language (DML) functions which can be performed. The different application programs can change their respective subschema without affecting other's subschema or view.

Instance:

         The data stored in database at a particular moment of time is called instance of database. It is a snapshot where the current state or occurrence of a database is framed at that moment. Database schema defines the variable declarations in tables that belong to a particular database; the value of these variables at a moment of time is called the instance of that database. Instance changes very frequently, whenever data is removed or added in the database.

              For example: We have a table student in the database, today the table has 10 records, so today the instance of the database has 10 records. We are going to add another 10 records in this table by tomorrow so the instance of database tomorrow will have 20 records in table. In short, at a particular moment the data stored in database is called the instance, that changes over time when we add or delete data from the database.

Difference between Schema and Instance:

• A schema is the design representation of a database whereas instance is the snapshot of a database at a particular moment.
• Instance changes very frequently, whenever data is removed or added in the database. As against, the changes in schema occurs rarely.

-profshardulp.patil@gmail.com

Data Independence

Data Independence:

A major objective for three-level architecture is to provide data independence. Data independence refers characteristic of being able to modify the schema at one level of the database system without affecting the schema at the next higher level. It saves time and cost required when the database is modified or altered.

Types of Data independence

The data independence is classified as two types and they are as below:

· Logical Data Independence:

Logical data independence indicates that the conceptual schema can be changed without affecting the existing external schema. Logical data independence is used to separate the external level from the conceptual view. The user view of the data would not be affected by any changes to the conceptual view of the data. These changes may include insertion or deletion of attributes, altering table structures entities or relationships to the logical schema etc. The change would be absorbed by the mapping between the external and conceptual levels. Logical data independence also insulates application programs from operations such as combining two records into one or splitting an existing record into two or more records.

· Physical Data Independence:

Physical data independence can be defined as the capacity to change the internal schema without having to change the conceptual schema. Modifications at the internal levels are occasionally necessary to improve performance. If we do any changes in the storage size of the database system server, then the Conceptual structure of the database will not be affected. Physical data independence is used to separate conceptual levels from the internal levels. The change would be absorbed by the mapping between the conceptual and internal levels.

x

Advantages of Data independence

1.      Ability of improving performance

2.      Alterations in data structure does not requires alterations in application programs

3.      Implementation details can be hidden from the users

4.      Reduction of incongruity

5.      Tractability in improvement of system

6.      Affordable prices of maintaining system

7.      Providing the best services to the users

8.      Permit users to focus on general structure

9.      Enforcement of standards

10.   Improvement of security

1    11.  The state of being undamaged or undivided can be improved

-profshardulp.patil@gmail.com

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