DBMS_ALL.pdf

## Database Management System ### 1. Data Data are raw or isolated facts from which the required information is produced. Data are distinct pieces of information, usually formatted in a special way. Examples of data: | In Employer's Mind | In Sales Person's View | |---|---| | ID | Customer_name...

## Database Management System ### 1. Data Data are raw or isolated facts from which the required information is produced. Data are distinct pieces of information, usually formatted in a special way. Examples of data: | In Employer's Mind | In Sales Person's View | |---|---| | ID | Customer_name | | Emp-name | Customer_Account | | Department | Address | | DOB | City | | Qualification | Ph_Number | | | State | ### 1.1.1 Three-layer Data Architecture Data is organized in the following layered structure: - Operational data - Reconciled data - Derived data Operational data: Operational data are stored in various operating system throughout the organization (both internal and external) system. Reconciled data: Reconciled data are stored in the organization data warehouse and in operational data store. They are detailed and current data, which is intended as the single, authoritative source for all decision support application. Derived data: Derived data are stored in each of the data mart. Derived data are selected, formatted and aggregated for end-user decision support application. ### 1.2 Information Data and information are closely related and are often used interchangeably. Information is processed, organized or summarised data. It may be defined as a collection of related data that when put together, communicate meaningful and useful message to a recipient who uses it, to make decision or to interpret the data to get the meaning. Data are processed to create information, which is meaningful to the recipient. For example, from the salesperson's view, we might want to know the current balance of a customer M/s Waterhouse Ltd. or perhaps we might ask for the average current balance of all the customers in India. The answers to such questions are information. Thus, information involves the communication and reception of knowledge or intelligence. It reduces uncertainty reveals additional alternatives or helps in eliminating irrelevant or poor ones, influences individuals and simulates them into action. ### 1.3 Data Warehouse Data warehouse is a collection of data designed to support management in the decision making process. It is a subject oriented, integrated, time-varient, non-updatable collection of data used in support of management decision-making processes and business intelligence. It contains a wide variety of data that present a coherent picture of business condition at a single point of time. It is a unique kind of database which focuses on business intelligence, external data and time-varient data. Data warehousing is the process, where organization extract meaning and information decision making from their information assets through the use of data warehouses. ### 1.4 Data Dictionary Data dictionary are mini database management systems that manages metadata. It is a repository of information about a database that documents data elements of a database. The data dictionary is an integral part of the database management systems and stores metadata or information about the database, attribute names and definitions for each table in the database. Data dictionary is usually a part of the system catalog that is generated for each database. A useful data dictionary system usually stores and manages the following types of information: - Descriptions of the schema of the database. - Detailed information on physical database design, such as storage structures, access paths and file and record sizes. - Description of the database users, their responsibilities and access rights. - High-level descriptions of the database transactions & applications and of the relationships of users to translations. - The relationship between database transactions and the data items referenced by them. This is useful in determining which transactions are affected when certain data definitions are changed. ### 1.5 Records A record is a collection of logically related fields or data items, with each field processing a fixed number of bytes and having a fixed data types. A record consists of values for each field. The grouping of data items can be achieved through different ways to form different records for different purposes. These records are retrieved or updated using programs. ### 1.6 Files A file is a collection of related sequence of records. In many cases, all records in a file are of the same record type (each record having an identical format). If every record in the file has exactly the same size in bytes, the file is said to be made up of fixed-length records. If different records in the file have different sizes, the file is said to be made of variable-length records. ### 1.7 Database A database is defined as a collection of logically related data stored together that is designed to meet the information needs of an organization. Database can further be defined as, it: - is a collection of interrelated data stored together without harmful or unnecessary redundancy. - serves multiple applications in which each user has his own view of data. This data is protected from unauthorized access by security mechanism and concurrent access to data is provided with recovery mechanism. - stores data independent of programs and changes in data storage structure or access strategy do not require changes in accessing programs or queries. A database consists of the following four components as shown in fig. - Data item - Constraints - Relationships - Schema ### 1.8 Database Management System A Database Management system is a collection of interrelated data and a set of programs to access those data. ### 1.8.1 Applications of Database System These are following applications of the database. - Banking - Airlines - University - Railways - Finance - Sales - Telecommunications - Pay Roll System - Manufacturing - Human Resources ### 1.8.2 Functions and Services of DBMS A DBMS performs several important functions that guarantee integrity and consistency of data in the database. - Data Storage Management: The DBMS creates the complex structures required for data storage in the physical database. It provides a mechanism for management of permanent storage of the data. - Transaction Management: A transaction is a series of database operations, carried out by a application program, which access or changes the contents of the database. Therfore, 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. - Integrity Services: Database integrity refers to the correctness and consistency of stored data and is specially important in transaction oriented database system. Therefore, a DBMS must provide to ensure that both the data in database and changes to the data follow certain rules. This minimises data redundancy and maximises data consistency. The data relationship stored in the data dictionary are used to enforce data integrity. Various types of integrity mechanisms and constraints may be supported to help ensure that the data values within the database are valid, that the operations performed on those values are valid and that the database remains in a consistent state. - Backup and Recovery Management: The DBMS provides mechanisms for different types of failures. This prevents the loss of data. The recovery mechanisms of DBMS, make sure that the database is returned to a consistent state after a transaction fails or aborts due to a system crash, media failure, hardware or software errors, power failure, and so on. - Concurrency Control Services: Since DBMS support sharing of data among multiple users, they must provide a mechanism for managing concurrent access to the database. DBMS's ensure that the database is kept in consistent state and that the integrity of the data is preserved. It ensures that the database is updated correctly when multiple users are updating the database concurrently. - Data Manipulation Management: DBMS furnishes users with the ability to retrieve, updata and delete existing data in the database or to add new data to the database. It includes DML processor component to deal with the data manipulation language (DML). - Data Dictionary/System 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. System catalog or data dictionary is a system database, which is a repository of information describing the data in the database. It is the data about the data or metadata. For example, the DBMS will consult the system catalog to verify that a requested table exists and that the user issuing the request has the necessary access privileges. - Authorisation/Security Management: The DBMS protects the database against unauthorized access, either intentional or accidental. It furnishes mechanism to ensure that only authorized users can access the database. It creates a security system that enforces user security and data privacy within the database. Security rules determine which users can access the database, which data items each user may access and which data operations (add, delete, and modify) the user may perform. - Utility Services : The DBMS provides a set of utility services used by the DBA and the database designer to create, implement, monitor and maintain the database. These utility services help the DBA to administer the database effectively. - Database Access and Application Programming Interfaces: All DBMSs provides language to enable applications to use DBMS services. They provide data access via structured query language (SQL). The DBMS query language contains two components : - A data definition language (DDL) - A data manipulation language (DML) DDL defines the structure in which the data are stored and the DML allows end user to extract the data from the database. The DBMS also provides data access to application programmers via procedural languages such as C, C++, Java and others. - Data Independence Services: The DBMS must support the independence of programs from the actual structure of the database. - Data Definition Services : The DBMS accepts the data definitions such as external schema, the conceptual schema, the internal schema, and all the associated mapping in source form. It converts them to the appropriate object form using a DDL processor component for each of the various data definition languages (DDLs). ### 1.8.3 Database Vs. File Systems A file is a sequence of records. - All records in a file are of the same record type. - File-processing system is supported by a conventional operating system. The system stores permanent records in various files, and it needs different application program to extract records from the appropriate files and add record to appropriate files. ### 1.8.4 Advantage of File Processing System Although the file-processing system is now largely obsolete, following are the advantages of file processing system. - It provides a useful historical perspective on, how to handle data. - The characteristics of a file-based system helps in an overall understanding of design complexity of database system. - Understanding the problems and knowledge of limitation inherent in the file based system helps avoid these same problems when designing database systems and thereby resulting in smooth transition. ### 1.8.5 Disadvantages of File Processing Systems - Excessive programming Effort: A new application program often required an entirely new set of file definition. Even though an existing file may contain some of the data needed, the application often required a number of other data items. As a result, the programmer had to recode the definitions of needed data items from the existing file as well as definitions of all new data items. Thus in file-oritented systems, there was a heavy interdependence between programs and data. - Data Inconsistency: Data Redundancy also leads to data inconsistency, since either the data formats may be inconsistent or data values may no longer agree or both. - Limited data sharing: There is limited data sharing opportunities with the traditional file oriented system. Each application has its own private file and users have little opportunity to share data outside their own applications. To obtain data from several incompatible files in separate systems will require a major programming effort. - Poor data control: A file-oriented system being decentralised in nature, there was no centralised control at the data element (field) level. It could be very common for the data field to have multiple names defined by the various departments of an organisation and depending on the file it was in. This could lead to different meaning of a data field in different context, and conversely, same meaning for different fields. This leads to a poor data control, resulting in a big confusion. - Inadequate data manipulation capabilities: Since file-oriented system do not provide strong connections between data in different files and therefore its data manipulation capability is very limited. - Data Redundancy (or duplication): Application are developed independently in file processing systems Icading to unplanned duplicate files. Duplication is wasteful as it requires additional storage space and changes in one file must be made manually in all files. This also results in loss of data integrity. It is also possible that the same data item may have different names in different files, or the same name may be used for different data items in different files. - Atomicity problems: Atomicity means either all operations of the transactions are reflected properly in the database or none are, i.e., if everything works correctly without any errors, then everything gets committed to the database. If any one part of the transaction fails, the entire transaction gets rolled back. The funds transfer must be atomic it must happen in its entire or not at all. It is difficult to ensure atomicity in a conventional file processing system. - Security problems: The problm of security in file processing is unauthrorized person can retrieve, modify, delete, or insert data in file system. But this is not possible in DBMS. - Integrity problems: The data values stored in the database must satisfy certain types of consistency constraints. Developers enforce these constraints in the system by adding appropriate code in the various application program. When new constraints are added, it is difficult to change the program to enforce them. The problem is compounded when constraints involves several data items for different files. - Program Data Dependance: File descriptions (physical structure, storage of the data files and records) are defined within each application program that accesses a given file. - Data isolation: Because data are scattered in various files, and files may be in different formats, writing new application program to retrieve the appropriate data is difficult. - Difficulty in accessing data: The conventional file processing environments do not allow needed data to be retrieved in a convenient and efficient manner like DBMS. Better data retrieval system must be developed for general use. - Concurrent access anomalies: In order to improve the overall performance of the system and obtain a faster respone time, many system allow multiple users to update the data simultaneously. In such an environment, interaction of concurrent updates may result in inconsistent data. ### 1.8.6 Advantages of DBMS - Controlling the data redundancy: The data redundancy, storing the same data multiple times leads to several problems. - First, storage space is wasted when the same data is stored repeatedly. - Second, files that represent the same data may become inconsistent. This may happen because an update is applied to some of the files but not to others. Most DBMS provide a facilities for controlling the data redundancy using normalization and keys concepts. - Restricting unauthorized access: When multiple users access a database therefore some users will not be authorized to access all informations in the database. A DBMS should provide a security and authorization subsystem, which the Data Base Administor (DBA) specify the restrictions. The DBMS should then enforce these restrictions automatically. For example, the Banking data are often considered confidential, and hence only authorized persons are allowed to access such data. ### 1.8.7 Disadvantages of DBMS There are the following disadvantages of DBMS. - Complexity of Backup and Recovery: For a centralised shared database to be accurate and available all times, a comprehensive procedure is required to be developed and used for providing backup copies of data and for restoring a database when damage occurs. A modern DBMS normally automates many more of the backup and recovery tasks than a file oriented system. - Increased installation and management cost: The large and complex DBMS software has a high initial cost. It requires trained manpower to install and operate and also has substantial annual maintenance and support cost. Additional database software may be needed to provide security and to ensure proper concurrent updating of shared data. - Additional hardware cost: The cost of DBMS installation varies significantly, depending on the environment and functionality, size of the hardware and the recurring annual maintenance cost of hardware and software. - Requirement of new and specialized manpower: Because of rapid changes in database technology and organization's business needs, the organization's need to hire, retrain its manpower on regular basis to design and implement databases, provide database administration services and manage a staff of new people. Therefore, an organization needs to maintain specialized skilled manpower. - Increased complexity: A multi-user DBMS becomes an extremely complex piece of software due to expected functionality from it. It becomes necessary for database designers, developers, database adminstrators and end-users to understand this functionality to full advantage of it. - Problems associated with centralization: Centralization means that the data is accessible from a single source called database. ### 1.8.8 Functions and Services of DBMS A DBMS performs several important functions that guarantee integrity and consistency of data in the database. - Data Storage Management: The DBMS creates the complex structures required for data storage in the physical database. It provides a mechanism for management of permanent storage of the data. - Transaction Management: A transaction is a series of database operations, carried out by a application program, which access or changes the contents of the database. Therfore, 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. - Integrity Services: Database integrity refers to the correctness and consistency of stored data and is specially important in transaction oriented database system. Therefore, a DBMS must provide to ensure that both the data in database and changes to the data follow certain rules. This minimises data redundancy and maximises data consistency. The data relationship stored in the data dictionary are used to enforce data integrity. Various types of integrity mechanisms and constraints may be supported to help ensure that the data values within the database are valid, that the operations performed on those values are valid and that the database remains in a consistent state. - Backup and Recovery Management: The DBMS provides mechanisms for different types of failures. This prevents the loss of data. The recovery mechanisms of DBMS, make sure that the database is returned to a consistent state after a transaction fails or aborts due to a system crash, media failure, hardware or software errors, power failure, and so on. - Concurrency Control Services: Since DBMS support sharing of data among multiple users, they must provide a mechanism for managing concurrent access to the database. DBMS's ensure that the database is kept in consistent state and that the integrity of the data is preserved. It ensures that the database is updated correctly when multiple users are updating the database concurrently. - Data Manipulation Management: DBMS furnishes users with the ability to retrieve, updata and delete existing data in the database or to add new data to the database. It includes DML processor component to deal with the data manipulation language (DML). - Data Dictionary/System 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. System catalog or data dictionary is a system database, which is a repository of information describing the data in the database. It is the data about the data or metadata. For example, the DBMS will consult the system catalog to verify that a requested table exists and that the user issuing the request has the necessary access privileges. - Authorisation/Security Management: The DBMS protects the database against unauthorized access, either intentional or accidental. It furnishes mechanism to ensure that only authorized users can access the database. It creates a security system that enforces user security and data privacy within the database. Security rules determine which users can access the database, which data items each user may access and which data operations (add, delete, and modify) the user may perform. - Utility Services : The DBMS provides a set of utility services used by the DBA and the database designer to create, implement, monitor and maintain the database. These utility services help the DBA to administer the database effectively. - Database Access and Application Programming Interfaces: All DBMSs provides language to enable applications to use DBMS services. They provide data access via structured query language (SQL). The DBMS query language contains two components : - A data definition language (DDL) - A data manipulation language (DML) DDL defines the structure in which the data are stored and the DML allows end user to extract the data from the database. The DBMS also provides data access to application programmers via procedural languages such as C, C++, Java and others. - Data Independence Services: The DBMS must support the independence of programs from the actual structure of the database. - Data Definition Services : The DBMS accepts the data definitions such as external schema, the conceptual schema, the internal schema, and all the associated mapping in source form. It converts them to the appropriate object form using a DDL processor component for each of the various data definition languages (DDLs). ### 1.8.3 Database Vs. File Systems A file is a sequence of records. - All records in a file are of the same record type. - File-processing system is supported by a conventional operating system. The system stores permanent records in various files, and it needs different application program to extract records from the appropriate files and add record to appropriate files. ### 1.8.4 Advantage of File Processing System Although the file-processing system is now largely obsolete, following are the advantages of file processing system. - It provides a useful historical perspective on, how to handle data. - The characteristics of a file-based system helps in an overall understanding of design complexity of database system. - Understanding the problems and knowledge of limitation inherent in the file based system helps avoid these same problems when designing database systems and thereby resulting in smooth transition. ### 1.8.5 Disadvantages of File Processing Systems - Excessive programming Effort: A new application program often required an entirely new set of file definition. Even though an existing file may contain some of the data needed, the application often required a number of other data items. As a result, the programmer had to recode the definitions of needed data items from the existing file as well as definitions of all new data items. Thus in file-oritented systems, there was a heavy interdependence between programs and data. - Data Inconsistency: Data Redundancy also leads to data inconsistency, since either the data formats may be inconsistent or data values may no longer agree or both. - Limited data sharing: There is limited data sharing opportunities with the traditional file oriented system. Each application has its own private file and users have little opportunity to share data outside their own applications. To obtain data from several incompatible files in separate systems will require a major programming effort. - Poor data control: A file-oriented system being decentralised in nature, there was no centralised control at the data element (field) level. It could be very common for the data field to have multiple names defined by the various departments of an organisation and depending on the file it was in. This could lead to different meaning of a data field in different context, and conversely, same meaning for different fields. This leads to a poor data control, resulting in a big confusion. - Inadequate data manipulation capabilities: Since file-oriented system do not provide strong connections between data in different files and therefore its data manipulation capability is very limited. - Data Redundancy (or duplication): Application are developed independently in file processing systems Icading to unplanned duplicate files. Duplication is wasteful as it requires additional storage space and changes in one file must be made manually in all files. This also results in loss of data integrity. It is also possible that the same data item may have different names in different files, or the same name may be used for different data items in different files. - Atomicity problems: Atomicity means either all operations of the transactions are reflected properly in the database or none are, i.e., if everything works correctly without any errors, then everything gets committed to the database. If any one part of the transaction fails, the entire transaction gets rolled back. The funds transfer must be atomic it must happen in its entire or not at all. It is difficult to ensure atomicity in a conventional file processing system. - Security problems: The problm of security in file processing is unauthrorized person can retrieve, modify, delete, or insert data in file system. But this is not possible in DBMS. - Integrity problems: The data values stored in the database must satisfy certain types of consistency constraints. Developers enforce these constraints in the system by adding appropriate code in the various application program. When new constraints are added, it is difficult to change the program to enforce them. The problem is compounded when constraints involves several data items for different files. - Program Data Dependance: File descriptions (physical structure, storage of the data files and records) are defined within each application program that accesses a given file. - Data isolation: Because data are scattered in various files, and files may be in different formats, writing new application program to retrieve the appropriate data is difficult. - Difficulty in accessing data: The conventional file processing environments do not allow needed data to be retrieved in a convenient and efficient manner like DBMS. Better data retrieval system must be developed for general use. - Concurrent access anomalies: In order to improve the overall performance of the system and obtain a faster respone time, many system allow multiple users to update the data simultaneously. In such an environment, interaction of concurrent updates may result in inconsistent data. ### 1.8.6 Advantages of DBMS - Controlling the data redundancy: The data redundancy, storing the same data multiple times leads to several problems. - First, storage space is wasted when the same data is stored repeatedly. - Second, files that represent the same data may become inconsistent. This may happen because an update is applied to some of the files but not to others. Most DBMS provide a facilities for controlling the data redundancy using normalization and keys concepts. - Restricting unauthorized access: When multiple users access a database therefore some users will not be authorized to access all informations in the database. A DBMS should provide a security and authorization subsystem, which the Data Base Administor (DBA) specify the restrictions. The DBMS should then enforce these restrictions automatically. For example, the Banking data are often considered confidential, and hence only authorized persons are allowed to access such data. ### 1.8.7 Disadvantages of DBMS There are the following disadvantages of DBMS. - Complexity of Backup and Recovery: For a centralised shared database to be accurate and available all times, a comprehensive procedure is required to be developed and used for providing backup copies of data and for restoring a database when damage occurs. A modern DBMS normally automates many more of the backup and recovery tasks than a file oriented system. - Increased installation and management cost: The large and complex DBMS software has a high initial cost. It requires trained manpower to install and operate and also has substantial annual maintenance and support cost. Additional database software may be needed to provide security and to ensure proper concurrent updating of shared data. - Additional hardware cost: The cost of DBMS installation varies significantly, depending on the environment and functionality, size of the hardware and the recurring annual maintenance cost of hardware and software. - Requirement of new and specialized manpower: Because of rapid changes in database technology and organization's business needs, the organization's need to hire, retrain its manpower on regular basis to design and implement databases, provide database administration services and manage a staff of new people. Therefore, an organization needs to maintain specialized skilled manpower. - Increased complexity: A multi-user DBMS becomes an extremely complex piece of software due to expected functionality from it. It becomes necessary for database designers, developers, database adminstrators and end-users to understand this functionality to full advantage of it. - Problems associated with centralization: Centralization means that the data is accessible from a single source called database. ### 1.8.8 Functions and Services of DBMS A DBMS performs several important functions that guarantee integrity and consistency of data in the database. - Data Storage Management: The DBMS creates the complex structures required for data storage in the physical database. It provides a mechanism for management of permanent storage of the data. - Transaction Management: A transaction is a series of database operations, carried out by a application program, which access or changes the contents of the database. Therfore, 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. - Integrity Services: Database integrity refers to the correctness and consistency of stored data and is specially important in transaction oriented database system. Therefore, a DBMS must provide to ensure that both the data in database and changes to the data follow certain rules. This minimises data redundancy and maximises data consistency. The data relationship stored in the data dictionary are used to enforce data integrity. Various types of integrity mechanisms and constraints may be supported to help ensure that the data values within the database are valid, that the operations performed on those values are valid and that the database remains in a consistent state. - Backup and Recovery Management: The DBMS provides mechanisms for different types of failures. This prevents the loss of data. The recovery mechanisms of DBMS, make sure that the database is returned to a consistent state after a transaction fails or aborts due to a system crash, media failure, hardware or software errors, power failure, and so on. - Concurrency Control Services: Since DBMS support sharing of data among multiple users, they must provide a mechanism for managing concurrent access to the database. DBMS's ensure that the database is kept in consistent state and that the integrity of the data is preserved. It ensures that the database is updated correctly when multiple users are updating the database concurrently. - Data Manipulation Management: DBMS furnishes users with the ability to retrieve, updata and delete existing data in the database or to add new data to the database. It includes DML processor component to deal with the data manipulation language (DML). - Data Dictionary/System 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. System catalog or data dictionary is a system database, which is a repository of information describing the data in the database. It is the data about the data or metadata. For example, the DBMS will consult the system catalog to verify that a requested table exists and that the user issuing the request has the necessary access privileges. - Authorisation/Security Management: The DBMS protects the database against unauthorized access, either intentional or accidental. It furnishes mechanism to ensure that only authorized users can access the database. It creates a security system that enforces user security and data privacy within the database. Security rules determine which users can access the database, which data items each user may access and which data operations (add, delete, and modify) the user may perform. - Utility Services : The DBMS provides a set of utility services used by the DBA and the database designer to create, implement, monitor and maintain the database. These utility services help the DBA to administer the database effectively. - Database Access and Application Programming Interfaces: All DBMSs provides language to enable applications to use DBMS services. They provide data access via structured query language (SQL). The DBMS query language contains two components : - A data definition language (DDL) - A data manipulation language (DML) DDL defines the structure in which the data are stored and the DML allows end user to extract the data from the database. The DBMS also provides data access to application programmers via procedural languages such as C, C++, Java and others. - Data Independence Services: The DBMS must support the independence of programs from the actual structure of the database. - Data Definition Services : The DBMS accepts the data definitions such as external schema, the conceptual schema, the internal schema, and all the associated mapping in source form. It converts them to the appropriate object form using a DDL processor component for each of the various data definition languages (DDLs). ### 1.8.3 Database Vs. File Systems A file is a sequence of records. - All records in a file are of the same record type. - File-processing system is supported by a conventional operating system. The system stores permanent records in various files, and it needs different application program to extract records from the appropriate files and add record to appropriate files. ### 1.8.4 Advantage of File Processing System Although the file-processing system is now largely obsolete, following are the advantages of file processing system. - It provides a useful historical perspective on, how to handle data. - The characteristics of a file-based system helps in an overall understanding of design complexity of database system. - Understanding the problems and knowledge of limitation inherent in the file based system helps avoid these same problems when designing database systems and thereby resulting in smooth transition. ### 1.8.5 Disadvantages of File Processing Systems - Excessive programming Effort: A new application program often required an entirely new set of file definition. Even though an existing file may contain some of the data needed, the application often required a number of other data items. As a result, the programmer had to recode the definitions of needed data items from the existing file as well as definitions of all new data items. Thus in file-oritented systems, there was a heavy interdependence between programs and data. - Data Inconsistency: Data Redundancy also leads to data inconsistency, since either the data formats may be inconsistent or data values may no longer agree or both. - Limited data sharing: There is limited data sharing opportunities with the traditional file oriented system. Each application has its own private file and users have little opportunity to share data outside their own applications. To obtain data from several incompatible files in separate systems will require a major programming effort. - Poor data control: A file-oriented system being decentralised in nature, there was no centralised control at the data element (field) level. It could be very common for the data field to have multiple names defined by the various departments of an organisation and depending on the file it was in. This could lead to different meaning of a data field in different context, and conversely, same meaning for different fields. This leads to a poor data control, resulting in a big confusion. - Inadequate data manipulation capabilities: Since file-oriented system do not provide strong connections between data in different files and therefore its data manipulation capability is very limited. - Data Redundancy (or duplication): Application are developed independently in file processing systems Icading to unplanned duplicate files. Duplication is wasteful as it requires additional storage space and changes in one file must be made manually in all files. This also results in loss of data integrity. It is also possible that the same data item may have different names in different files, or the same name may be used for different data items in different files. - Atomicity problems: Atomicity means either all operations of the transactions are reflected properly in the database or none are, i.e., if everything works correctly without any errors, then everything gets committed to the database. If any one part of the transaction fails, the entire transaction gets rolled back. The funds transfer must be atomic it must happen in its entire or not at all. It is difficult to ensure atomicity in a conventional file processing system. - Security problems: The problm of security in file processing is unauthrorized person can retrieve, modify, delete, or insert data in file system. But this is not possible in DBMS. - Integrity problems: The data values stored in the database must satisfy certain types of consistency constraints. Developers enforce these constraints in the system by adding appropriate code in the various application program. When new constraints are added, it is difficult to change the program to enforce them. The problem is compounded when constraints involves several data items for different files. - Program Data Dependance: File descriptions (physical structure, storage of the data files and records) are defined within each application program that accesses a given file. - Data isolation: Because data are scattered in various files, and files may be in different formats, writing new application program to retrieve the appropriate data is difficult. - Difficulty in accessing data: The conventional file processing environments do not allow needed data to be retrieved in a convenient and efficient manner like DBMS. Better data retrieval system must be developed for general use. - Concurrent access anomalies: In order to improve the overall performance of the system and obtain a faster respone time, many system allow multiple users to update the data simultaneously. In such an environment, interaction of concurrent updates may result in inconsistent data. ### 1.8.6 Advantages of DBMS - Controlling the data redundancy: The data redundancy, storing the same data multiple times leads to several problems. - First, storage space is wasted when the same data is stored repeatedly. - Second, files that represent the same data may become inconsistent. This may happen because an update is applied to some of the files but not to others. Most DBMS provide a facilities for controlling the data redundancy using normalization and keys concepts. - Restricting unauthorized access: When multiple users access a database therefore some users will not be authorized to access all informations in the database. A DBMS should provide a security and authorization subsystem, which the Data Base Administor (DBA) specify the restrictions. The DBMS should then enforce these restrictions automatically. For example, the Banking data are often considered confidential, and hence only authorized persons are allowed to access such data. ### 1.8.7 Disadvantages of DBMS There are the following disadvantages of DBMS. - Complexity of Backup and Recovery: For a centralised shared database to be accurate and available all times, a comprehensive procedure is required to be developed and used for providing backup copies of data and for restoring a database when damage occurs. A modern DBMS normally automates many more of the backup and recovery tasks than a file oriented system. - Increased installation and management cost: The large and complex DBMS software has a high initial cost. It requires trained manpower to install and operate and also has substantial annual maintenance and support cost. Additional database software may be needed to provide security and to ensure proper concurrent updating of shared data. - Additional hardware cost: The cost of DBMS installation varies significantly, depending on the environment and functionality, size of the hardware and the recurring annual maintenance cost of hardware and software. - Requirement of new and specialized manpower: Because of rapid changes in database technology and organization's business needs, the organization's need to hire, retrain its manpower on regular basis to design and implement databases, provide database administration services and manage a staff of new people. Therefore, an organization needs to maintain specialized skilled manpower. - Increased complexity: A multi-user DBMS becomes an extremely complex piece of software due to expected functionality from it. It becomes necessary for database designers, developers, database adminstrators and end-users to understand this functionality to full advantage of it. - Problems associated with centralization: Centralization means that the data is accessible from a single source called database. ### 1.8.8 Functions and Services of DBMS A DBMS performs several important functions that guarantee integrity and consistency of data in the database. - Data Storage Management: The DBMS creates the complex structures required for data storage in the physical database. It provides a mechanism for management of permanent storage of the data.