Page 1 :
SHIVA CLASSES CS, COMPUTER SCIENCE, DATA BASE MANAGEMENT SYSTEMS & WEB TECHNOLOGY, UNIT-1 INTRODUCTORY CONCEPTS: Database system concepts and architecture,, Database System Applications, Database systems versus File system, View of data, Data, Models, Database Languages(Data definition language and Data Manipulation Language),, Database Users and Administrators(Database Users and User Interface),Database, Administrator, Database System Structure(Storage Manager, Query Processor), Application, Architectures, History of Database Systems., Database system concepts:, What is Data?, The raw facts are called as data. The word “raw” indicates that they have not been processed., Ex: For example 89 is the data., What is information?, The processed data is known as information. Ex: Marks: 89; then it becomes information., What is Knowledge?, 1. Knowledge refers to the practical use of information. 2. Knowledge necessarily involves a, personal experience., Database, • A repository of logically related and similar data., • An organized collection of related information so that it can easily be accessed, managed and, updated., E.g.: Dictionary Airline Database Student Database Library Railways Timetable YouTube (All, songs of Rahul Vaidya), Examples / Applications of Database Systems:, The following are the various kinds of applications/organizations uses databases for their, business processing activities in their day-to-day life. They are:, 1.Banking: For customer information, accounts, and loans, and banking transactions.
Page 2 :
SHIVA CLASSES CS, 2. Airlines: For reservations and schedule information. Airlines were among the first to use, databases in a geographically distributed manner—terminals situated around the world, accessed the central database system through phone lines and other data networks., 3.Universities: For student information, course registrations, and grades., 4.Credit Card Transactions: For purchases on credit cards and generation of monthly, statements., 5. Telecommunication: For keeping records of calls made, generating monthly bills,, maintaining balances on prepaid calling cards, and storing information about the, communication networks., 6. Finance: For storing information about holdings, sales, and purchases of financial, instruments such as stocks and bonds., 7. Sales: For customer, product, and purchase information., 8. Manufacturing: For management of supply chain and for tracking production of items in, factories, inventories of items in warehouses/stores, and orders for items., 9. Human resources: For information about employees, salaries, payroll taxes and benefits,, and for generation of paychecks., 10. Railway Reservation Systems: For reservations and schedule information., 11. Web: For access the Back accounts and to get the balance amount., 12. E –Commerce: For Buying a book or music CD and browse for things like watches,, mobiles from the Internet., DBMS stands for Database Management System., DBMS is a software system for creating, organizing and managing the database., It provides an environment to the user to perform operations on the database for creation,, insertion, deletion, updating and retrieval of data., Goals of DBMS:, The primary goal of a DBMS is to provide a way to store and retrieve database information, that is both convenient and efficient, 1.Manage large bodies of information, 2. Provide convenient and efficient ways to store and access information, 3. Secure information against system failure or tampering, 4. Permit data to be shared among multiple users, Properties of DBMS:
Page 3 :
SHIVA CLASSES CS, 1.A Database represents some aspect of the real world. Changes to the real world reflected in, the database., 2.A Database is a logically coherent collection of data with some inherent meaning., 3.A Database is designed and populated with data for a specific purpose., Need of DBMS:, 1. Before the advent of DBMS, organizations typically stored information using a “File, Processing Systems”. Example of such systems is File Handling in High Level Languages like, C, Basic and COBOL etc., these systems have Major disadvantages to perform the Data, Manipulation. So to overcome those drawbacks now we are using the DBMS., 2.Database systems are designed to manage large bodies of information., 3. In addition to that the database system must ensure the safety of the information stored,, despite system crashes or attempts at unauthorized access. If data are to be shared among, several users, the system must avoid possible anomalous results., DISADVANTAGES OF DBMS:, Danger of an Overkill: For small and simple applications for single users a database system is, often not advisable., Complexity: A database system creates additional complexity and requirements. The supply, and operation of a database management system with several users and databases is quite, costly and demanding., Qualified Personnel: `The professional operation of a database system requires appropriately, trained staff. Without a qualified database administrator nothing will work for long. Costs:, Through the use of a database system new costs are generated for the system itself but also for, additional hardware and the more complex handling of the system., Lower Efficiency: A database system is a multi-use software which is often less efficient than, specialized software which is produced and optimized exactly for one problem., DBMS Architecture, ● The DBMS design depends upon its architecture. The basic client/server architecture is, used to deal with a large number of PCs, web servers, database servers and other, components that are connected with networks., ● The client/server architecture consists of many PCs and a workstation which are, connected via the network.
Page 4 :
SHIVA CLASSES CS, ● DBMS architecture depends upon how users are connected to the database to get their, request done., Types of DBMS Architecture, , Database architecture can be seen as a single tier or multi-tier. But logically, database, architecture is of two types like: 2-tier architecture and 3-tier architecture., 1-Tier Architecture, ● In this architecture, the database is directly available to the user. It means the user can, directly sit on the DBMS and uses it., ● Any changes done here will directly be done on the database itself. It doesn't provide a, handy tool for end users., ● The 1-Tier architecture is used for development of the local application, where, programmers can directly communicate with the database for the quick response., 2-Tier Architecture
Page 5 :
SHIVA CLASSES CS, ● The 2-Tier architecture is same as basic client-server. In the two-tier architecture,, applications on the client end can directly communicate with the database at the server, side. For this interaction, API's like: ODBC, JDBC are used., ● The user interfaces and application programs are run on the client-side., ● The server side is responsible to provide the functionalities like: query processing and, transaction management., ● To communicate with the DBMS, client-side application establishes a connection with, the server side., , Fig: 2-tier Architecture, 3-Tier Architecture, ● The 3-Tier architecture contains another layer between the client and server. In this, architecture, client can't directly communicate with the server., ● The application on the client-end interacts with an application server which further, communicates with the database system.
Page 6 :
SHIVA CLASSES CS, ● End user has no idea about the existence of the database beyond the application server., The database also has no idea about any other user beyond the application., ● The 3-Tier architecture is used in case of large web application., , Fig: 3-tier Architecture, Three schema Architecture, ● The three schema architecture is also called ANSI/SPARC architecture or three-level, architecture., ● This framework is used to describe the structure of a specific database system., ● The three schema architecture is also used to separate the user applications and physical, database., ● The three schema architecture contains three-levels. It breaks the database down into, three different categories.
Page 7 :
SHIVA CLASSES CS, The three-schema architecture is as follows:, , In the above diagram:, ● It shows the DBMS architecture., ● Mapping is used to transform the request and response between various database levels, of architecture., ● Mapping is not good for small DBMS because it takes more time., ● In External / Conceptual mapping, it is necessary to transform the request from external, level to conceptual schema., ● In Conceptual / Internal mapping, DBMS transform the request from the conceptual to, internal level., Objectives of Three schema Architecture
Page 8 :
SHIVA CLASSES CS, The main objective of three level architecture is to enable multiple users to access the same, data with a personalized view while storing the underlying data only once. Thus it separates, the user's view from the physical structure of the database. This separation is desirable for the, following reasons:, ● Different users need different views of the same data., ● The approach in which a particular user needs to see the data may change over time., ● The users of the database should not worry about the physical implementation and, internal workings of the database such as data compression and encryption techniques,, hashing, optimization of the internal structures etc., ● All users should be able to access the same data according to their requirements., ● DBA should be able to change the conceptual structure of the database without, affecting the user's, ● Internal structure of the database should be unaffected by changes to physical aspects of, the storage., 1. Internal Level, , ● The internal level has an internal schema which describes the physical storage structure, of the database., ● The internal schema is also known as a physical schema., ● It uses the physical data model. It is used to define that how the data will be stored in a, block., ● The physical level is used to describe complex low-level data structures in detail.
Page 9 :
SHIVA CLASSES CS, The internal level is generally is concerned with the following activities:, Features of Java - Javatpoint, ● Storage space allocations., For Example: B-Trees, Hashing etc., ● Access paths., For Example: Specification of primary and secondary keys, indexes, pointers and, sequencing., ● Data compression and encryption techniques., ● Optimization of internal structures., ● Representation of stored fields., 2. Conceptual Level, , ● The conceptual schema describes the design of a database at the conceptual level., Conceptual level is also known as logical level., ● The conceptual schema describes the structure of the whole database., ● The conceptual level describes what data are to be stored in the database and also, describes what relationship exists among those data., ● In the conceptual level, internal details such as an implementation of the data structure, are hidden., ● Programmers and database administrators work at this level., 3. External Level
Page 10 :
SHIVA CLASSES CS, , ● At the external level, a database contains several schemas that sometimes called as, subschema. The subschema is used to describe the different view of the database., ● An external schema is also known as view schema., ● Each view schema describes the database part that a particular user group is interested, and hides the remaining database from that user group., ● The view schema describes the end user interaction with database systems., Mapping between Views, The three levels of DBMS architecture don't exist independently of each other. There must be, correspondence between the three levels i.e. how they actually correspond with each other., DBMS is responsible for correspondence between the three types of schema. This, correspondence is called Mapping., There are basically two types of mapping in the database architecture:, ● Conceptual/ Internal Mapping, ● External / Conceptual Mapping, Conceptual/ Internal Mapping, The Conceptual/ Internal Mapping lies between the conceptual level and the internal level. Its, role is to define the correspondence between the records and fields of the conceptual level and, files and data structures of the internal level., External/ Conceptual Mapping, The external/Conceptual Mapping lies between the external level and the Conceptual level. Its, role is to define the correspondence between a particular external and the conceptual view., DBMS vs. File System, File System Approach
Page 11 :
SHIVA CLASSES CS, File based systems were an early attempt to computerize the manual system. It is also called a, traditional based approach in which a decentralized approach was taken where each, department stored and controlled its own data with the help of a data processing specialist., The main role of a data processing specialist was to create the necessary computer file, structures, and also manage the data within structures and design some application programs, that create reports based on file data., , In the above figure:, Consider an example of a student's file system. The student file will contain information, regarding the student (i.e. roll no, student name, course etc.). Similarly, we have a subject file, that contains information about the subject and the result file which contains the information, regarding the result., Some fields are duplicated in more than one file, which leads to data redundancy. So to, overcome this problem, we need to create a centralized system, i.e. DBMS approach., 30.4M, 681, Exception Handling in Java - Javatpoint
Page 12 :
SHIVA CLASSES CS, Next, Stay, DBMS:, A database approach is a well-organized collection of data that are related in a meaningful, way which can be accessed by different users but stored only once in a system. The various, operations performed by the DBMS system are: Insertion, deletion, selection, sorting etc., , In the above figure,, In the above figure, duplication of data is reduced due to centralization of data., There are the following differences between DBMS and File systems:, Basis, , DBMS Approach, , File System Approach
Page 13 :
SHIVA CLASSES CS, Meaning, , DBMS is a collection of data. In The file system is a collection of, DBMS, the user is not required to data. In this system, the user has, write the procedures., to write the procedures for, managing the database., , Sharing of data, , Due to the centralized approach, Data is distributed in many files,, data sharing is easy., and it may be of different formats,, so it isn't easy to share data., , Data, Abstraction, , DBMS gives an abstract view of The file system provides the detail, data that hides the details., of the data representation and, storage of data., , Security, and DBMS provides a good protection It isn't easy to protect a file under, Protection, mechanism., the file system., Recovery, Mechanism, , DBMS provides a crash recovery The file system doesn't have a, mechanism, i.e., DBMS protects the crash mechanism, i.e., if the, user from system failure., system crashes while entering, some data, then the content of the, file will be lost., , Manipulation, Techniques, , DBMS contains a wide variety of The file system can't efficiently, sophisticated techniques to store store and retrieve the data., and retrieve the data., , Concurrency, Problems, , DBMS takes care of Concurrent In the File system, concurrent, access of data using some form of access has many problems like, locking., redirecting the file while deleting, some information or updating, some information.
Page 14 :
SHIVA CLASSES CS, Where to use, , Database approach used in large File system approach used in large, systems which interrelate many systems which interrelate many, files., files., , Cost, , The database system is expensive to The file system approach is, design., cheaper to design., , Data, Redundancy, and, Inconsistency, , Due to the centralization of the, database, the problems of data, redundancy and inconsistency are, controlled., , Structure, , The database structure is complex The file system approach has a, to design., simple structure., , Data, Independence, , In this system, Data Independence In the File system approach, there, exists, and it can be of two types., exists no Data Independence., , In this, the files and application, programs are created by different, programmers so that there exists a, lot of duplication of data which, may lead to inconsistency., , ● Logical Data Independence, ● Physical Data Independence, , Integrity, Constraints, , Integrity Constraints are easy to Integrity Constraints are difficult, apply., to implement in file system., , Data Models, , In the database approach, 3 types of In the file system approach, there, data models exist:, is no concept of data models, exists., ● Hierarchal data models, ● Network data models, ● Relational data models
Page 15 :
SHIVA CLASSES CS, Flexibility, , Changes are often a necessity to the The flexibility of the system is, content of the data stored in any less as compared to the DBMS, system, and these changes are more approach., easily with a database approach., , Examples, , Oracle, SQL Server, Sybase etc., , Cobol, C++ etc., , View of Data in DBMS, Data Abstraction, Data abstraction is hiding the complex data structure in order to simplify the user’s, interface of the system. It is done because many of the users interacting with the database, system are not that much computer trained to understand the complex data structures of the, database system., To achieve data abstraction, we will discuss a Three-Schema architecture which abstracts, the database at three levels discussed below:, Three-Schema Architecture:, The main objective of this architecture is to have an effective separation between the user, interface and the physical database. So, the user never has to be concerned regarding the, internal storage of the database and it has a simplified interaction with the database system., The three-schema architecture defines the view of data at three levels:, 1. Physical level (internal level), 2. Logical level (conceptual level), 3. View level (external level), 1. Physical Level/ Internal Level
Page 16 :
SHIVA CLASSES CS, The physical or the internal level schema describes how the data is stored in the hardware., It also describes how the data can be accessed. The physical level shows the data abstraction, at the lowest level and it has complex data structures. Only the database administrator, operates at this level., 2. Logical Level/ Conceptual Level, It is a level above the physical level. Here, the data is stored in the form of the entity set,, entities, their data types, the relationship among the entity sets, user operations performed, to retrieve or modify the data and certain constraints on the data. Well adding constraints to, the view of data adds the security. As users are restricted to access some particular parts of the, database., It is the developer and database administrator who operates at the logical or the conceptual, level., 3. View Level/ User level/ External level, It is the highest level of data abstraction and exhibits only a part of the whole database. It, exhibits the data in which the user is interested. The view level can describe many views of, the same data. Here, the user retrieves the information using different application from the, database., The figure below describes the three-schema architecture of the database:
Page 17 :
SHIVA CLASSES CS, , In the figure above you can clearly distinguish between the three levels of abstraction. To, understand it more clearly let us take an example:, We have to create a database of a college. Now, what entity sets would be involved? Student,, Lecturer, Department, Course and so on…, Now, the entity sets Student, Lecturer, Department, Course will be stored in the storage as the, consecutive blocks of the memory location. This is the physical or internal level and is, hidden from the programmers but the database administrator is it aware of it., At the logical level, the programmers define the entity sets and relationship among these, entity sets using a programming language like SQL. So, the programmers work at the logical, level and even the database administrator also operates at this level.
Page 18 :
SHIVA CLASSES CS, At the view level, the users have the set of applications which they use to retrieve the data, they are interested in., Data Independence, Data independence defines the extent to which the data schema can be changed at one level, without modifying the data schema at the next level. Data independence can be classified as, shown below:, Logical Data Independence:, Logical data independence describes the degree up to which the logical or conceptual schema, can be changed without modifying the external schema. Now, a question arises what is the, need to change the data schema at a logical or conceptual level?, Well, the changes to data schema at the logical level are made either to enlarge or reduce the, database by adding or deleting more entities, entity sets, or changing the constraints on data., Physical Data Independence:, Physical data independence defines the extent up to which the data schema can be changed at, the physical or internal level without modifying the data schema at logical and view level., Well, the physical schema is changed if we add additional storage to the system or we, reorganize some files to enhance the retrieval speed of the records., Instances and Schemas, What is an instance?, We can define an instance as the information stored in the database at a particular point of, time., Data Models, Data Model is the modeling of the data description, data semantics, and consistency, constraints of the data. It provides the conceptual tools for describing the design of a database
Page 19 :
SHIVA CLASSES CS, at each level of data abstraction. Therefore, there are following four data models used for, understanding the structure of the database:, , 1) Relational Data Model: This type of model designs the data in the form of rows and, columns within a table. Thus, a relational model uses tables for representing data and, in-between relationships. Tables are also called relations. This model was initially described, by Edgar F. Codd, in 1969. The relational data model is the widely used model which is, primarily used by commercial data processing applications., 2) Entity-Relationship Data Model: An ER model is the logical representation of data as, objects and relationships among them. These objects are known as entities, and relationship is, an association among these entities. This model was designed by Peter Chen and published in, 1976 papers. It was widely used in database designing. A set of attributes describe the entities., For example, student_name, student_id describes the 'student' entity. A set of the same type of, entities is known as an 'Entity set', and the set of the same type of relationships is known as, 'relationship set'., 3) Object-based Data Model: An extension of the ER model with notions of functions,, encapsulation, and object identity, as well. This model supports a rich type system that, includes structured and collection types. Thus, in 1980s, various database systems following
Page 20 :
SHIVA CLASSES CS, the object-oriented approach were developed. Here, the objects are nothing but the data, carrying its properties., Java Try Catch, 4) Semistructured Data Model: This type of data model is different from the other three data, models (explained above). The semistructured data model allows the data specifications at, places where the individual data items of the same type may have different attributes sets. The, Extensible Markup Language, also known as XML, is widely used for representing the, semistructured data. Although XML was initially designed for including the markup, information to the text document, it gains importance because of its application in the, exchange of data., Data model Schema and Instance, ● The data which is stored in the database at a particular moment of time is called an, instance of the database., ● The overall design of a database is called schema., ● A database schema is the skeleton structure of the database. It represents the logical, view of the entire database., ● A schema contains schema objects like table, foreign key, primary key, views, columns,, data types, stored procedure, etc., ● 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., A schema diagram can display only some aspects of a schema like the name of record type,, data type, and constraints. Other aspects can't be specified through the schema diagram. For, example, the given figure neither show the data type of each data item nor the relationship, among various files.
Page 21 :
SHIVA CLASSES CS, In the database, actual data changes quite frequently. For example, in the given figure, the, database changes whenever we add a new grade or add a student. The data at a particular, moment of time is called the instance of the database., Database Language, ● A DBMS has appropriate languages and interfaces to express database queries and, updates., ● Database languages can be used to read, store and update the data in the database., Types of Database Language, , 1. Data Definition Language, ● DDL stands for Data Definition Language. It is used to define database structure or, pattern., ● It is used to create schema, tables, indexes, constraints, etc. in the database., ● Using the DDL statements, you can create the skeleton of the database., ● Data definition language is used to store the information of metadata like the number of, tables and schemas, their names, indexes, columns in each table, constraints, etc., Here are some tasks that come under DDL:, ● Create: It is used to create objects in the database., ● Alter: It is used to alter the structure of the database., ● Drop: It is used to delete objects from the database., ● Truncate: It is used to remove all records from a table., ● Rename: It is used to rename an object., ● Comment: It is used to comment on the data dictionary., These commands are used to update the database schema that's why they come under Data, definition language.
Page 22 :
SHIVA CLASSES CS, 2. Data Manipulation Language, DML stands for Data Manipulation Language. It is used for accessing and manipulating data, in a database. It handles user requests., Here are some tasks that come under DML:, ● Select: It is used to retrieve data from a database., ● Insert: It is used to insert data into a table., ● Update: It is used to update existing data within a table., ● Delete: It is used to delete all records from a table., ● Merge: It performs UPSERT operation, i.e., insert or update operations., ● Call: It is used to call a structured query language or a Java subprogram., ● Explain Plan: It has the parameter of explaining data., ● Lock Table: It controls concurrency., 3. Data Control Language, ● DCL stands for Data Control Language. It is used to retrieve the stored or saved data., ● The DCL execution is transactional. It also has rollback parameters., (But in Oracle database, the execution of data control language does not have the feature of, rolling back.), Here are some tasks that come under DCL:, ● Grant: It is used to give user access privileges to a database., ● Revoke: It is used to take back permissions from the user., There are the following operations which have the authorization of Revoke:, CONNECT, INSERT, USAGE, EXECUTE, DELETE, UPDATE and SELECT., 4. Transaction Control Language
Page 23 :
SHIVA CLASSES CS, TCL is used to run the changes made by the DML statement. TCL can be grouped into a, logical transaction., Here are some tasks that come under TCL:, ● Commit: It is used to save the transaction on the database., ● Rollback: It is used to restore the database to original since the last Commit, , Database Users, Database users are the ones who really use and take the benefits of the database. There will be, different types of users depending on their needs and way of accessing the database.
Page 24 :
SHIVA CLASSES CS, 1. Application Programmers – They are the developers who interact with the, database by means of DML queries. These DML queries are written in the, application programs like C, C++, JAVA, Pascal, etc. These queries are converted, into object code to communicate with the database. For example, writing a C, program to generate the report of employees who are working in a particular, department will involve a query to fetch the data from the database. It will include, an embedded SQL query in the C Program., 2. Sophisticated Users – They are database developers, who write SQL queries to, select/insert/delete/update data. They do not use any application or programs to, request the database. They directly interact with the database by means of a query, language like SQL. These users will be scientists, engineers, analysts who, thoroughly study SQL and DBMS to apply the concepts in their requirements. In, short, we can say this category includes designers and developers of DBMS and, SQL., 3. Specialized Users – These are also sophisticated users, but they write special, database application programs. They are the developers who develop the complex, programs to the requirement., 4. Stand-alone Users – These users will have a stand-alone database for their, personal use. These kinds of the database will have readymade database packages, which will have menus and graphical interfaces., 5. Native Users – these are the users who use the existing application to interact, with the database. For example, online library system, ticket booking systems,, ATMs etc which has existing application and users use them to interact with the, database to fulfill their requests., , Database Administrators, The life cycle of a database starts from designing, implementing to the administration of it. A, database for any kind of requirement needs to be designed perfectly so that it should work, without any issues. Once all the design is complete, it needs to be installed. Once this step is, complete, users start using the database. The database grows as the data grows in the database., When the database becomes huge, its performance comes down. Also accessing the data from, the database becomes a challenge. There will be unused memory in the database, making the
Page 25 :
SHIVA CLASSES CS, memory inevitably huge. This administration and maintenance of the database are taken care, of by the database Administrator – DBA., A DBA has many responsibilities. A good-performing database is in the hands of DBA., ● Installing and upgrading the DBMS Servers: – DBA is responsible for, installing a new DBMS server for the new projects. He is also responsible for, upgrading these servers as there are new versions that come into the market or, requirement. If there is any failure in the up-gradation of the existing servers, he, should be able to revert the new changes back to the older version, thus, maintaining the DBMS working. He is also responsible for updating the service, packs/ hotfixes/ patches to the DBMS servers., ● Design and implementation: – Designing the database and implementing is also, DBA’s responsibility. He should be able to decide on proper memory, management, file organizations, error handling, log maintenance, etc for the, database., ● Performance tuning: – Since the database is huge and it will have lots of tables,, data, constraints, and indices, there will be variations in the performance from, time to time. Also, because of some designing issues or data growth, the database, will not work as expected. It is the responsibility of the DBA to tune the database, performance. He is responsible to make sure all the queries and programs work in, a fraction of seconds., ● Migrate database servers: – Sometimes, users using oracle would like to shift to, SQL server or Netezza. It is the responsibility of DBA to make sure that, migration happens without any failure, and there is no data loss., ● Backup and Recovery: – Proper backup and recovery programs needs to be, developed by DBA and has to be maintained him. This is one of the main, responsibilities of DBA. Data/objects should be backed up regularly so that if, there is any crash, it should be recovered without much effort and data loss., ● Security: – DBA is responsible for creating various database users and roles, and, giving them different levels of access rights., ● Documentation: – DBA should be properly documenting all his activities so that, if he quits or any new DBA comes in, he should be able to understand the, database without any effort. He should basically maintain all his installation,
Page 26 :
SHIVA CLASSES CS, backup, recovery, security methods. He should keep various reports about, database performance., , Types of DBA, There are different kinds of DBA depending on the responsibility that he owns., ● Administrative DBA – This DBA is mainly concerned with installing, and, maintaining DBMS servers. His prime tasks are installing, backups, recovery,, security, replications, memory management, configurations, and tuning. He is, mainly responsible for all administrative tasks of a database., ● Development DBA – He is responsible for creating queries and procedures for, the requirement. Basically, his task is similar to any database developer., ● Database Architect – Database architect is responsible for creating and, maintaining the users, roles, access rights, tables, views, constraints, and indexes., He is mainly responsible for designing the structure of the database depending on, the requirement. These structures will be used by developers and development, DBA to code., ● Data Warehouse DBA –DBA should be able to maintain the data and procedures, from various sources in the data warehouse. These sources can be files, COBOL,, or any other programs. Here data and programs will be from different sources. A, good DBA should be able to keep the performance and function levels from these, sources at the same pace to make the data warehouse work., ● Application DBA –He acts like a bridge between the application program and the, database. He makes sure all the application program is optimized to interact with, the database. He ensures all the activities from installing, upgrading, and patching,, maintaining, backup, recovery to executing the records work without any issues., ● OLAP DBA – He is responsible for installing and maintaining the database in, OLAP systems. He maintains only OLAP databases., , 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
Page 27 :
SHIVA CLASSES CS, and retrieval) on the database. The components of DBMS perform these requested operations, on the database and provide necessary data to the users. The various components of DBMS, are shown below: -, , Fig. 2.1 Structure Of DBMS, , 1. DDL Compiler - Data Description Language compiler processes schema definitions, specified in the DDL. It includes metadata information such as the name of the files, data, items, storage details of each file, mapping information and constraints etc., 2. DML Compiler and Query optimizer - The DML commands such as insert, update,, delete, retrieve from the application program are sent to the DML compiler for compilation, into object code for database access. The object code is then optimized in the best way to, execute a query by the query optimizer and then send to the data manager.
Page 28 :
SHIVA CLASSES CS, 3. Data Manager - The Data Manager is the central software component of the DBMS also, knows as Database Control System., The Main Functions Of Data Manager Are: –, • Convert operations in user's Queries coming from the application programs or combination, of DML Compiler and Query optimizer which is known as Query Processor from user's, logical view to physical file system., • Controls DBMS information access that is stored on disk., • It also controls handling buffers in main memory., • It also enforces constraints to maintain consistency and integrity of the data., • It also synchronizes the simultaneous operations performed by the concurrent users., • It also controls the backup and recovery operations., 4. Data Dictionary - Data Dictionary is a repository of description of data in the database. It, contains information about, • Data - names of the tables, names of attributes of each table, length of attributes, and number, of rows in each table., • Relationships between database transactions and data items referenced by them which is, useful in determining which transactions are affected when certain data definitions are, changed., • Constraints on data i.e. range of values permitted., • Detailed information on physical database design such as storage structure, access paths,, files and record sizes., • Access Authorization - is the Description of database users their responsibilities and their, access rights., • Usage statistics such as frequency of query and transactions., Data dictionary is used to actually control the data integrity, database operation and accuracy., It may be used as a important part of the DBMS.
Page 29 :
SHIVA CLASSES CS, Importance of Data Dictionary Data Dictionary is necessary in the databases due to following reasons:, • It improves the control of DBA over the information system and user's understanding of use, of the system., • It helps in documentating the database design process by storing documentation of the result, of every design phase and design decisions., • It helps in searching the views on the database definitions of those views., • It provides great assistance in producing a report of which data elements (i.e. data values) are, used in all the programs., • It promotes data independence i.e. by addition or modifications of structures in the database, application program are not effected., 5. Data Files - It contains the data portion of the database., 6. Compiled DML - The DML complier converts the high level Queries into low level file, access commands known as compiled DML., Database System Structure are partitioned into modules for different functions. Some, functions (e.g. file systems) may be provided by the operating system. Components include:, • File Manager manages allocation of disk space and data structures used to represent, information on disk., • Database Manager: The interface between low-level data and application programs and, queries., • Query Processor translates statements in a query language into low-level instructions the, database manager understands. (May also attempt to find an equivalent but more efficient, form.) The Query Processor simplifies and facilitates access to data. The Query processor, includes the following component., DDL Interpreter, DML Compiler, Query Evaluation Engine
Page 30 :
SHIVA CLASSES CS, The DDL interpreter interprets DDL statements and records the definition in the data, dictionary. 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., • DML Precompiled converts DML statements embedded in an application program to, normal procedure calls in a host language. The precompiled interacts with the query, processor., • DDL Compiler converts DDL statements to a set of tables containing metadata stored in a, data dictionary., In addition, several data structures are required for physical system implementation:, • Data Files: store the database itself., • Data Dictionary: stores information about the structure of the database. It is used heavily., Great emphasis should be placed on developing a good design and efficient implementation, of the dictionary., • Indices: provide fast access to data items holding particular values., Storage Manager, The storage manager is important because database typically require a large amount of, storage space. So it is very important efficient use of storage, and to minimize the movement, of data to and from disk ., A storage manager is a program module that provides the interface between the low-level, data stored in the database and the application programs and the queries submitted to the, system. The Storage manager is responsible for the interaction with the file manager., The Storage manager translates the various DML statements into low level file system, commands. Thus the storage manager is responsible for storing, retrieving, and updating data, in the database. The storage manager components include the following., Authorization and Integrity Manager, Transaction Manger, File Manager
Page 31 :
SHIVA CLASSES CS, Buffer Manger, Authorization and Integrity Manger tests for the satisfaction of integrity constraints and, checks the authority of users to access data. Transaction manager ensures that the database, remains in a consistent state and allowing concurrent transactions to proceed without, conflicting., The file manager manages the allocation of space on disk storage and the data structures used, to represent information stored on disk. The Buffer manager is responsible for fetching the, data from disk storage into main memory and deciding what data to cache in main memory., The storage manager implements the following data structures as part of the physical system, implementation. Data File, Data Dictionary, Indices. Data files stores the database itself. The, Data dictionary stores Meta data about the structure of database, in particular the schema of, the database. Indices provide fast access to data items., History of Database Systems:, , 1950s and early 1960s: Magnetic tapes were developed for data storage Data processing, tasks such as payroll were automated, with data stored on tapes. Data could also be input, from punched card decks, and output to printers., , Late 1960s and 1970s: The use of hard disks in the late 1960s changed the scenario for data, processing greatly, since hard disks allowed direct access to data. With disks, network and, hierarchical databases could be created that allowed data structures such as lists and trees to be, stored on disk. Programmers could construct and manipulate these data structures. With disks,, network and hierarchical databases could be created that allowed data structures such as lists, and trees to be stored on disk. Programmers could construct and manipulate these data, structures., , In the 1970’s the EF CODD defined the Relational Model.
Page 32 :
SHIVA CLASSES CS, In the 1980’s: Initial commercial relational database systems, such as IBM DB2, Oracle,, Ingress, and DEC Rdb, played a major role in advancing techniques for efficient processing of, declarative queries., , In the early 1980s, relational databases had become competitive with network and, hierarchical database systems even in the area of performance. The 1980s also saw much, research on parallel and distributed databases, as well as initial work on object-oriented, databases., , Early 1990s: The SQL language was designed primarily in the 1990’s. And this is used for, the transaction processing applications. Decision support and querying re-emerged as a major, application area for databases., , Database vendors also began to add object-relational support to their databases., , Late 1990s: The major event was the explosive growth of the World Wide Web. Databases, were deployed much more extensively than ever before. Database systems now had to support, very high transaction processing rates, as well as very high reliability and 24 * 7 availability, (availability 24 hours a day, 7 days a week, meaning no downtime for scheduled maintenance, activities). Database systems also had to support Web interfaces to data.
Page 33 :
SHIVA CLASSES CS
