1 Centralized Data Management

 There are many advantages of database management system. Some of the advantages are listed later: 1. Centralized data management. 2. Data Independence. 3. System Integration.

Centralized Data Management In DBMS all files are integrated into one system thus reducing redundancies and making data management more efficient. 1.11.2 Data Independence Data independence means that programs are isolated from changes in the way the data are structured and stored. In a database system, the database management system provides the interface between the application programs and the data. Physical data independence means the applications need not worry about how the data are physically structured and stored. Applications should work with a logical data model and declarative query language. If major changes were to be made to the data, the application programs may need to be rewritten. When changes are made to the data representation, the data maintained by the DBMS is changed but the DBMS continues to provide data to application programs in the previously used way. Data independence is the immunity of application programs to changes in storage structures and access techniques. For example if we add a new attribute, change index structure then in traditional file processing system, the applications are affected. But in a DBMS environment these changes are reflected in the catalog, as a result the applications are not affected. Data independence can be physical data independence or logical data independence. Physical data independence is the ability to modify physical schema without causing the conceptual schema or application programs to be rewritten. Logical data independence is the ability to modify the conceptual schema without having to change the external schemas or application programs. 1.11.3 Data Inconsistency Data inconsistency means different copies of the same data will have different values. For example, consider a person working in a branch of an organization. The details of the person will be stored both in the branch office as well as in the main office. If that particular person changes his address, then the “change of address” has to be maintained in the main as well as the branch office. 1.12 Ansi/Spark Data Model 11 For example the “change of address” is maintained in the branch office but not in the main office, then the data about that person is inconsistent. DBMS is designed to have data consistency. Some of the qualities achieved in DBMS are: 1. Data redundancy −→ Reduced in DBMS. 2. Data independence −→ Activated in DBMS. 3. Data inconsistency −→ Avoided in DBMS. 4. Centralizing the data −→ Achieved in DBMS. 5. Data integrity −→ Necessary for efficient Transaction. 6. Support for multiple views −→ Necessary for security reasons. – Data redundancy means duplication of data. Data redundancy will occupy more space hence it is not desirable. – Data independence means independence between application program and the data. The advantage is that when the data representation changes, it is not necessary to change the application program. – Data inconsistency means different copies of the same data will have different values. – Centralizing the data means data can be easily shared between the users but the main concern is data security. – The main threat to data integrity comes from several different users attempting to update the same data at the same time. For example, “The number of booking made is larger than the capacity of the aircraft/train.” – Support for multiple views means DBMS allows different users to see different “views” of the database, according to the perspective each one requires. This concept is used to enhance the security of the database. 1.12 Ansi/Spark Data Model (American National Standard Institute/ Standards Planning and Requirements Committee) The distinction between the logical and physical representation of data were recognized in 1978 when ANSI/SPARK committee proposed a generalized framework for database systems. This framework provided a three-level architecture, three levels of abstraction at which the database could be viewed. 1.12.1 Need for Abstraction The main objective of DBMS is to store and retrieve information efficiently; all the users should be able to access same data. The designers use complex data structure to represent the data, so that data can be efficiently stored and retrieved, but it is not necessary for the users to know physical database storage details. The developers hide the complexity from users through several levels of abstraction. 12 1 Overview of Database Management System 1.12.2 Data Independence Data independence means the internal structure of database should be unaffected by changes to physical aspects of storage. Because of data independence, the Database administrator can change the database storage structures without affecting the users view. The different levels of data abstraction are: 1. Physical level or internal level 2. Logical level or conceptual level 3. View level or external level Physical Level It is concerned with the physical storage of the information. It provides the internal view of the actual physical storage of data. The physical level describes complex low-level data structures in detail. Logical Level Logical level describes what data are stored in the database and what relationships exist among those data. Logical level describes the entire database in terms of a small number of simple structures. The implementation of simple structure of the logical level may involve complex physical level structures; the user of the logical level does not need to be aware of this complexity. Database administrator use the logical level of abstraction. View Level View level is the highest level of abstraction. It is the view that the individual user of the database has. There can be many view level abstractions of the same data. The different levels of data abstraction are shown in Fig. 1.6. Database Instances Database change over time as information is inserted and deleted. The collection of information stored in the database at a particular moment is called an instance of the database. Database Schema The overall design of the database is called the database schema. A schema is a collection of named objects. Schemas provide a logical classification of objects in the database. A schema can contain tables, views, triggers, functions, packages, and other objects.

REATE SCHEMA statement with the current user recorded as the schema owner. It can also be implicitly created when another object is created, provided the user has IMPLICIT SCHEMA authority. Data base schemas Physical schema Logical schema Subschema Describes the Database Design at the Physical level Describes the database design at the logical level Describes different views of the database 1.13 Data Models Data model is collection of conceptual tools for describing data, relationship between data, and consistency constraints. Data models help in describing the structure of data at the logical level. Data model describe the structure of the database. A data model is the set of conceptual constructs available for defining a schema. The data model is a language for describing the data and database, it may consist of abstract concepts, which must be translated by the 14 1 Overview of Database Management System designer into the constructs of the data definition interface, or it may consist of constructs, which are directly supported by the data definition interface. The constructs of the data model may be defined at many levels of abstraction. Data model Conceptual data model Physical data model Object based Logical model Record based model ∗ E-R model ∗ Relational model (Entity-Relationship model) ∗ Object-oriented model ∗ Network model ∗ Functional data model ∗ Hierarchical data model 1.13.1 Early Data Models Three historically important data models are the hierarchical, network, and relational models. These models are relevant for their contributions in establishing the theory of data modeling and because they were all used as the basis of working and widely used database systems. Together they are often referred to as the “basic” data models. The hierarchical and network models, developed in the 1960s and 1970s, were based on organizing the primitive data structures in which the data were stored in the computer by adding connections or links between the structures. As such they were useful in presenting the user with a well-defined structure, but they were still highly coupled to the underlying physical representation of the data. Although they did much to assist in the efficient access of data, the principle of data independence was poorly supported. 1.14 Components and Interfaces of Database Management System A database management system involves five major components: data, hardware, software, procedure, and users. These components and the interface between the components are shown in Fig. 1.7. 1.14.1 Hardware The hardware can range from a single personal computer, to a single mainframe, to a network of computers. The particular hardware depends on the 1.14 Components and Interfaces of Database Management System 15 Forms Data Files Application Front ends DML Interface DDL Query Evaluation Engine File and Access Methods Buffer Manager Disk Space Manager Transaction And Lock Manager Recovery Manager DDL Compiler Indexes System Catalog Fig. 1.7. Database management system components and interfaces requirements of the organization and the DBMS used. Some DBMSs run only on particular operating systems, while others run on a wide variety of operating systems. A DBMS requires a minimum amount of main memory and disk space to run, but this minimum configuration may not necessarily give acceptable performance. 1.14.2 Software The software includes the DBMS software, application programs together with the operating systems including the network software if the DBMS is being used over a network. The application programs are written in third-generation programming languages like “C,” COBOL, FORTRAN, Ada, Pascal, etc. or using fourth-generation language such as SQL, embedded in a third-generation language. The target DBMS may have its own fourth-generation tools which allow development of applications through the provision of nonprocedural query languages, report generators, graphics generators, and application generators. The use of fourth-generation tools can improve productivity significantly and produce programs that are easier to maintain. 16 1 Overview of Database Management System 1.14.3 Data A database is a repository for data which, in general, is both integrated and shared. Integration means that the database may be thought of as a unification of several otherwise distinct files, with any redundancy among those files partially or wholly eliminated. The sharing of a database refers to the sharing of data by different users, in the sense that each of those users may have access to the same piece of data and may use it for different purposes. Any given user will normally be concerned with only a subset of the whole database. The main features of the data in the database are listed later: 1. The data in the database is well organized (structured) 2. The data in the database is related 3. The data are accessible in different orders without great difficulty The data in the database is persistent, integrated, structured, and shared. Integrated Data A data can be considered to be a unification of several distinct data files and when any redundancy among those files is eliminated, the data are said to be integrated data. Shared Data A database contains data that can be shared by different users for different application simultaneously. It is important to note that in this way of sharing of data, the redundancy of data are reduced, since repetitions are avoided, the possibility of inconsistencies is reduced. Persistent Data Persistent data are one, which cannot be removed from the database as a side effect of some other process. Persistent data have a life span that is not limited to single execution of the programs that use them. 1.14.4 Procedure Procedures are the rules that govern the design and the use of database. The procedure may contain information on how to log on to the DBMS, start and stop the DBMS, procedure on how to identify the failed component, how to recover the database, change the structure of the table, and improve the performance. 1.14.5 People Interacting with Database Here people refers to the people who manages the database, database administrator, people who design the application program, database designer and the people who interacts with the database, database users. 1.14 Components and Interfaces of Database Management System 17 A DBMS is typically run as a back-end server in a local or global network, offering services to clients directly or to Application Servers. People interacting with database Database Administrator Database Designer Database manager Database user Application Programmer Enduser ∗ Sophisticated ∗ Naïve. ∗ Specialized. Database Administrator Database Administrator is a person having central control over data and programs accessing that data. The database administrator is a manager whose responsibilities are focused on management of technical aspects of the database system. The objectives of database administrator are given as follows: 1. To control the database environment 2. To standardize the use of database and associated software 3. To support the development and maintenance of database application projects 4. To ensure all documentation related to standards and implementation is up-to-date The summarized objectives of database administrator are shown in Fig. 1.8. The control of the database environment should exist from the planning right through to the maintenance stage. During application development the database administrator should carry out the tasks that ensure proper control of the database when an application becomes operational. This includes review of each design stage to see if it is feasible from the database point of view. The database administrator should be responsible for developing standards to apply to development projects. In particular these standards apply to system analysis, design, and application programming for projects which are going to use the database. These standards will then be used as a basis for training systems analysts and programmers to use the database management system efficiently. Responsibilities of Database Administrator (DBA) The responsibility of the database administrator is to maintain the integrity, security, and availability of data. A database must be protected from 18 1 Overview of Database Management System Control Document Standardize Support Applications DBMS Database Fig. 1.8. Objectives of database administration accidents, such as input or programming errors, from malicious use of the database and from hardware or software failures that corrupt data. Protection from accidents that cause data inaccuracy is a part of maintaining data integrity. Protecting the database from unauthorized or malicious use is termed as database security. The responsibilities of the database administrator are summarized as follows: 1. Authorizing access to the database. 2. Coordinating and monitoring its use. 3. Acquiring hardware and software resources as needed. 4. Backup and recovery. DBA has to ensure regular backup of database, incase of damage, suitable recovery procedure are used to bring the database up with little downtime as possible. Database Designer Database designer can be either logical database designer or physical database designer. Logical database designer is concerned with identifying the data, the relationships between the data, and the constraints on the data that is to be stored in the database. The logical database designer must have thorough understanding of the organizations data and its business rule. The physical database designer takes the logical data model and decides the way in which it can be physically implemented. The logical database designer is responsible for mapping the logical data model into a set of tables and integrity constraints, selecting specific storage structure, and designing 1.14 Components and Interfaces of Database Management System 19 security measures required on the data. In a nutshell, the database designer is responsible for: 1. Identifying the data to be stored in the database. 2. Choosing appropriate structure to represent and store the data. Database Manager Database manager is a program module which provides the interface between the low level data stored in the database and the application programs and queries submitted to the system: – The database manager would translate DML statement into low level file system commands for storing, retrieving, and updating data in the database. – Integrity enforcement. Database manager enforces integrity by checking consistency constraints like the bank balance of customer must be maintained to a minimum of Rs. 300, etc. – Security enforcement. Unauthorized users are prohibited to view the information stored in the data base. – Backup and recovery. Backup and recovery of database is necessary to ensure that the database must remain consistent despite the fact of failures. Database Users Database users are the people who need information from the database to carry out their business responsibility. The database users can be broadly classified into two categories like application programmers and end users. Database users Application programmers End users Application programmers write application programs and interacts with the data base through host Language like Pascal, C and Cobol ∗ Sophisticated end users ∗ Specialized end users ∗ Naïve end users Sophisticated End Users Sophisticated end users interact with the system without writing programs. They form requests by writing queries in a database query language. These are submitted to query processor. Analysts who submit queries to explore data in the database fall in this category. 20 1 Overview of Database Management System Specialized End Users Specialized end users write specialized database application that does not fit into data-processing frame work. Application involves knowledge base and expert system, environment modeling system, etc. Naive End Users Na¨ıve end user interact with the system by using permanent application program Example: Query made by the student, namely number of books borrowed in library database. System Analysts System analysts determine the requirements of end user, and develop specification for canned transaction that meets this requirement. Canned Transaction Ready made programs through which na¨ıve end users interact with the database is called canned transaction. 1.14.6 Data Dictionary A data dictionary, also known as a “system catalog,” is a centralized store of information about the database. It contains information about the tables, the fields the tables contain, data types, primary keys, indexes, the joins which have been established between those tables, referential integrity, cascades update, cascade delete, etc. This information stored in the data dictionary is called the “Metadata.” Thus a data dictionary can be considered as a file that stores Metadata. Data dictionary is a tool for recording and processing information about the data that an organization uses. The data dictionary is a central catalog for Metadata. The data dictionary can be integrated within the DBMS or separate. Data dictionary may be referenced during system design, programming, and by actively-executing programs. One of the major functions of a true data dictionary is to enforce the constraints placed upon the database by the designer, such as referential integrity and cascade delete. Metadata The information (data) about the data in a database is called Metadata. The Metadata are available for query and manipulation, just as other data in the database.The functional components of database system structure are: 1. Storage manager. 2. Query processor. Storage Manager Storage manager is responsible for storing, retrieving, and updating data in the database. Storage manager components are: 1. Authorization and integrity manager. 2. Transaction manager. 3. File manager. 4. Buffer manager. Transaction Management – A transaction is a collection of operations that performs a single logical function in a database application. – Transaction-management component ensures that the database remains in a consistent state despite system failures and transaction failure. – Concurrency control manager controls the interaction among the concurrent transactions, to ensure the consistency of the database. Authorization and Integrity Manager Checks the integrity constraints and authority of users to access data. Transaction Manager It ensures that the database remains in a consistent state despite system failures. The transaction manager manages the execution of database manipulation requests. The transaction manager function is to ensure that concurrent access to data does not result in conflict. File Manager File manager manages the allocation of space on disk storage. Files are used to store collections of similar data. A file management system manages independent files, helping to enter and retrieve information records. File manager establishes and maintains the list of structure and indexes defined in the internal schema. The file manager can: – Create a file – Delete a file – Update the record in the file – Retrieve a record from a file 22 1 Overview of Database Management System Buffer The area into which a block from the file is read is termed a buffer. The management of buffers has the objective of maximizing the performance or the utilization of the secondary storage systems, while at the same time keeping the demand on CPU resources tolerably low. The use of two or more buffers for a file allows the transfer of data to be overlapped with the processing of data. Buffer Manager Buffer manager is responsible for fetching data from disk storage into main memory. Programs call on the buffer manager when they need a block from disk. The requesting program is given the address of the block in main memory, if it is already present in the buffer. If the block is not in the buffer, the buffer manager allocates space in the buffer for the block, replacing some other block, if required, to make space for new block. Once space is allocated in the buffer, the buffer manager reads in the block from the disk to the buffer, and passes the address of the block in main memory to the requester. Indices Indices provide fast access to data items that hold particular values. An index is a list of numerical values which gives the order of the records when they are sorted on a particular field or column of the table. 1.15 Database Architecture Database architecture essentially describes the location of all the pieces of information that make up the database application. The database architecture can be broadly classified into two-, three-, and multitier architecture. 1.15.1 Two-Tier Architecture The two-tier architecture is a client–server architecture in which the client contains the presentation code and the SQL statements for data access. The database server processes the SQL statements and sends query results back to the client. The two-tier architecture is shown in Fig. 1.9. Two-tier client/server provides a basic separation of tasks. The client, or first tier, is primarily responsible for the presentation of data to the user and the “server,” or second tier, is primarily responsible for supplying data services to the client. 1.15 Database Architecture 23 First Tier: Tasks/Services • User Interface • Data services • Presentation services • Application services • Application services • Business services Client Second Tier: Data Server Tasks/Services Fig. 1.9. Two-tier client–server architecture Presentation Services “Presentation services” refers to the portion of the application which presents data to the user. In addition, it also provides for the mechanisms in which the user will interact with the data. More simply put, presentation logic defines and interacts with the user interface. The presentation of the data should generally not contain any validation rules. Business Services/objects “Business services” are a category of application services. Business services encapsulate an organizations business processes and requirements. These rules are derived from the steps necessary to carry out day-today business in an organization. These rules can be validation rules, used to be sure that the incoming information is of a valid type and format, or they can be process rules, which ensure that the proper business process is followed in order to complete an operation. Application Services “Application services” provide other functions necessary for the application. Data Services “Data services” provide access to data independent of their location. The data can come from legacy mainframe, SQL RDBMS, or proprietary data access systems. Once again, the data services provide a standard interface for accessing data.

_______________________________________________________________________

Advantages of Two-tier Architecture The two-tier architecture is a good approach for systems with stable requirements and a moderate number of clients. The two-tier architecture is the simplest to implement, due to the number of good commercial development environments. Drawbacks of Two-tier Architecture Software maintenance can be difficult because PC clients contain a mixture of presentation, validation, and business logic code. To make a significant change in the business logic, code must be modified on many PC clients. Moreover the performance of two-tier architecture can be poor when a large number of clients submit requests because the database server may be overwhelmed with managing messages. With a large number of simultaneous clients, three-tier architecture may be necessary. 1.15.2 Three-tier Architecture A “Multitier,” often referred to as “three-tier” or “N-tier,” architecture provides greater application scalability, lower maintenance, and increased reuse of components. Three-tier architecture offers a technology neutral method of building client/server applications with vendors who employ standard interfaces which provide services for each logical “tier.” The three-tier architecture is shown in Fig. 1.10. From this figure, it is clear that in order to improve the performance a second-tier is included between the client and the server. Through standard tiered interfaces, services are made available to the application. A single application can employ many different services which may reside on dissimilar platforms or are developed and maintained with different tools. This approach allows a developer to leverage investments in existing systems while creating new application which can utilize existing resources. Although the three-tier architecture addresses performance degradations of the two-tier architecture, it does not address division-of-processing concerns. The PC clients and the database server still contain the same division of code although the tasks of the database server are reduced. Multiple-tier architectures provide more flexibility on division of processing. 1.15.3 Multitier Architecture A multi-tier, three-tier, or N-tier implementation employs a three-tier logical architecture superimposed on a distributed physical model. Application Servers can access other application servers in order to supply services to the client application as well as to other Application Servers. The multiple-tier architecture is the most general client–server architecture. It can be most difficult to implement because of its generality. However, a good design and 1.15 Database Architecture 25 Third Tier: Data Server First Tier: Client Business Object/Component Business Object/Component Business Object/Component Tasks/Services • User Interface • Presentation Services Tasks/Services • Application services • Business services/objects Tasks/Services • Data services • Data validation Second Tier: Application Server Fig. 1.10. Three-tier client–server architecture Multi-Tier Architecture Client Legacy Data Server Application Server Application Server Client Client Fig. 1.11. Multiple-tier architecture implementation of multiple-tier architecture can provide the most benefits in terms of scalability, interoperability, and flexibility. For example, in the diagram shown in Fig. 1.11, the client application looks to Application Server #1 to supply data from a mainframe-based application. Application Server #1 has no direct access to the mainframe application, but it does know, through the development of application services, that 26 1 Overview of Database Management System Application Server #2 provides a service to access the data from the mainframe application which satisfies the client request. Application Server #1 then invokes the appropriate service on Application Server #2 and receives the requested data which is then passed on to the client. Application Servers can take many forms. An Application Server may be anything from custom application services, Transaction Processing Monitors, Database Middleware, Message Queue to a CORBA/COM based solution. 1.16 Situations where DBMS is not Necessary It is also necessary to specify situations where it is not necessary to use a DBMS. If traditional file processing system is working well, and if it takes more money and time to design a database, it is better not to go for the DBMS. Moreover if only one person maintains the data and that person is not skilled in designing a database as well as not comfortable in using the DBMS then it is not advisable to go for DBMS. DBMS is undesirable under following situations: – DBMS is undesirable if the application is simple, well-defined, and not expected to change. – Runtime overheads are not feasible because of real-time requirements. – Multiple accesses to data are not required. Compared with file systems, databases have some disadvantages: 1. High cost of DBMS which includes: – Higher hardware costs – Higher programming costs – High conversion costs 2. Slower processing of some applications 3. Increased vulnerability 4. More difficult recovery 1.17 DBMS Vendors and their Products Some of the popular DBMS vendors and their corresponding products are given Table 1.1. Summary The main objective of database management system is to store and manipulate the data in an efficient manner. A database is an organized collection of related data. All the data will not give useful information. Only processed data gives useful information, which helps an organization to take important Review Questions 27 Table 1.1. DBMS vendors and their products vendor product IBM –DB2/MVS –DB2/UDB –DB2/400 –Informix Dynamic Server (IDS) Microsoft –Access –SQL Server –DesktopEdition(MSDE) Open Source –MySQL –PostgreSQL Oracle –Oracle DBMS –RDB Sybase –Adaptive Server Enterprise (ASE) –Adaptive Server Anywhere (ASA) –Watcom decisions. Before DBMS, computer file processing systems were used to store, manipulate, and retrieve large files of data. Computer file processing systems have limitations such as data duplications, limited data sharing, and no program data independence. In order to overcome these limitations database approach was developed. The main advantages of DBMS approach are program-data independence, improved data sharing, and minimal data redundancy. In this chapter we have seen the evolution of DBMS and broad introduction to DBMS. The responsibilities of Database administrator, ANSI/SPARK, two-tier, three-tier architecture were analyzed in this chapter. Review Questions 1.1. What are the drawbacks of file processing system? The drawbacks of file processing system are: – Duplication of data, which leads to wastage of storage space and data inconsistency. – Separation and isolation of data, because of which data cannot be used together. – No program data independence. 1.2. What is meant by Metadata? Metadata are data about data but not the actual data. 28 1 Overview of Database Management System 1.3. Define the term data dictionary? Data dictionary is a file that contains Metadata. 1.4. What are the responsibilities of database administrator? 1.5. Mention three situations where it is not desirable to use DBMS? The situations where it is not desirable to use DBMS are: – The database and applications are not expected to change. – Data are not accessed by multiple users. 1.6. What is meant by data independence? Data independence renders application programs (e.g., SQL scripts) immune to changes in the logical and physical organization of data in the system. Logical organization refers to changes in the Schema. Example adding a column or tuples does not stop queries from working. Physical organization refers to changes in indices, file organizations, etc. 1.7. What is meant by Physical and Logical data independence? In logical data independence, the conceptual schema can be changed without changing the external schema. In physical data independence, the internal schema can be changed without changing the conceptual schema. 1.8. What are some disadvantages of using a DBMS over flat file system? – DBMS initially costs more than flat file system – DBMS requires skilled staff 1.9. What are the steps to design a good database? – First find out the requirements of the user – Design a view for each important application – Integrate the views giving the conceptual schema, which is the union of all views – Map to the data model provided by the DBMS (usually relational) – Design external views – Choose physical structures (indexes, etc.) 1.10. What is Database? Give an example. A Database is a collection of related data. Here, the term “data” means that known facts that can be record. Examples of database are library information system, bus, railway, and airline reservation system, etc. Review Questions 29 1.11. Define – DBMS. DBMS is a collection of programs that enables users to create and maintain a database. 1.12. Mention various types of databases? The different types of databases are: – Multimedia database – Spatial database (Geographical Information System Database) – Real-time or Active Database – Data Warehouse or On-line Analytical Processing Database 1.13. Mention the advantages of using DBMS? The advantages of using DBMS are: – Controlling Redundancy – Enforcing Integrity Constraints so as to maintain the consistency of the database – Providing Backup and recovery facilities – Restricting unauthorized access – Providing multiple user interfaces – Providing persistent storage of program objects and datastructures 1.14. What is “Snapshot” or “Database State”? The data in the database at a particular moment is known as “Database State” or “Snapshot” of the Database. 1.15. Define Data Model. It is a collection of concepts that can be used to describe the structure of a database. The datamodel provides necessary means to achieve the abstraction i.e., hiding the details of data storage. 1.16. Mention the various categories of Data Model. The various categories of datamodel are: – High Level or Conceptual Data Model (Example: ER model) – Low Level or Physical Data Model – Representational or Implementational Data Model – Relational Data Model – Network and Hierarchal Data Model – Record-based Data Model – Object-based Data Model 30 1 Overview of Database Management System 1.17. Define the concept of “database schema.” Describe the types of schemas that exist in a database complying with the three levels ANSI/SPARC architecture. Database schema is nothing but description of the database. The types of schemas that exist in a database complying with three levels of ANSI/SPARC architecture are: – External schema – Conceptual schema – Internal schema