File organization and introduction of DBMS

Containing File organization in DBMS and introduction of DBMS

1. Database Management System (FYBCA sci.) By, Prof. Vrushali Solanke.

2. Introduction: Why do we need to study DBMS? Database have become integral component of our everyday life. We encounter several activities in our day to day life that evolve interaction with a database. For example: Bank Database,Movie database,Railway database,Supermarket goods database .

3. What is Data? Data is the collection of facts and figures that can be processed to produce information. Data :A set of isolated and unrelated raw facts with an implicit meaning. Data can be anything such as text,number,images,sound,video etc. Example, ‘Pune’, ’10’, ’student’ etc. Information: When data is processed and converted into a meaningful and useful from, it is known as information. Example, ’Amar is 18 years old and he is a student’.

4. What is Database and DBMS? Database system simplifies the task of managing the data and extracting useful information in timely fashion .Database system is an integrated collection of related files,along with the details of the interpretation of the data. A Database Management System (DBMS) is a software or a program that allows access/retrieval of data that contained in database. Objective : The objective of DBMS is to provide a convenient and effective method of defining, storing and retrieving the information stored in the database. Database is the integrated set of programs used to create ans maintain a database .

5. Basic concept of File: File :It is the resource for storing information in a computer system. File is the named collection of related information. A file is sequence of bits,bytes,lines or records whose meaning is defined by the file creator or user. Record :It is the basic unit of information for computer and File . Field :It is the collection of data items.The collection of related field is called a record. Example,Student_Name,Address,Phone_no etc.,are known as fields of STUDENT record. A set of logically related records form or constitute a File. Basic Operation on File :Create , Open,Locate (Find),Read(Get), Write,Close etc.on records available in file to modify or access the file. File system :The structure and logical rules used to manage the groups of information and their name.

6. Types of File:

7. File Organization: Introduction File organization is the method of arranging the record in a file. F ile is stored on secondary storage device called hard disk. The physical arrangement of data in file into records on the disk is known as File Organization. File organization is a logical relationship among various records. This method defines how file records are mapped onto disk blocks. File organization is used to describe the way in which the records are stored in terms of blocks, and the blocks are placed on the storage medium. File access refers to the manner in which the record of the file may be accessed. A file can be accessed by different ways :1)sequential access 2)Direct/Random access 3)Indexed sequential access

8. Objective of file organization It contains an optimal selection of records, i.e., records can be selected as fast as possible. To perform insert, delete or update transaction on the records should be quick and easy. The duplicate records cannot be induced as a result of insert, update or delete. For the minimal cost of storage, records should be stored efficiently.

9. Types of file organization: File organization contains various methods. In the file organization, the programmer decides the best-suited file organization method according to his requirement. 1)Heap File Organization: 2) Sorted File Organization: 3) Indexed File Organization: 4) Hash File Organization:

10. Heap File Organization: It is the simplest and most basic type of organization. It works with data blocks. In heap file organization, the records are inserted at the file's end. When the records are inserted, it doesn't require the sorting and ordering of records. When the data block is full, the new record is stored in some other block. This new data block need not to be the very next data block, but it can select any data block in the memory to store new records. The heap file is also known as an unordered file. In the file, every record has a unique id, and every page in a file is of the same size. It is the DBMS responsibility to store and manage the new records

11. Pros and cons of Heap file organization Pros: i ) It is a very good method of file organization for bulk insertion. If there is a large number of data which needs to load into the database at a time, then this method is best suited. ii)In case of a small database, fetching and retrieving of records is faster than the sequential record Cons: i ) This method is inefficient for the large database because it takes time to search or modify the record. ii)Deletion of many records results in wastage of space. iii)Searching and accessing very slow .

12. Sorted File Organization: In this method, As the name itself suggest whenever a new record has to be inserted, it is always inserted in a sorted (ascending or descending) manner. Sorting of records may be based on any primary key or any other key. Ordering key: It is an attribute or set of attributes which are used to serialize the records. In sorted file ,first record is inserted at the end of the file and they move to its correct location. Records are stored in order of the values of the key field.

13. Pros and cons of Sorted file organization Pros: i )Fast and efficient method for huge amount of data. ii)Simple design. iii)Files can be easily stored in magnetic tapes i.e cheaper storage mechanism. Cons: i ) Time wastage as we cannot jump on a particular record that is required, but we have to move in a sequential manner which takes our time. ii)Sorted file method is inefficient as it takes time and space for sorting records.

14. Indexed File Organization: In Indexed file organization ,the rows are stored either sequentially or randomly. An index is created that allows the application software to locate individual rows. Index: An index is structure that is used to determine the rows in a file that satisfy some condition. Index sequential file:A sequential file that is indexed.Also called Indexed sequential Access Method (ISAM) ISAM method is an advanced sequential file organization. In this method, records are stored in the file using the primary key. An index value is generated for each primary key and mapped with the record. This index contains the address of the record in the file.

15. If any record has to be retrieved based on its index value, then the address of the data block is fetched and the record is retrieved from the memory.

16. Pros and cons of Indexed file organization Pros: i ) In this method, each record has the address of its data block, searching a record in a huge database is quick and easy ii)It is possible to link the record of two files. iii)comparing with other file organization ,it is easy to update file. Cons: i ) This method requires extra space in the disk to store the index value. ii)When the new records are inserted, then these files have to be reconstructed to maintain the sequence. iii)When the record is deleted, then the space used by it needs to be released. Otherwise, the performance of the database will slow down.

17. Hash File Organization: Hashing  is an efficient technique to directly search the location of desired data on the disk without using index structure. Data is stored at the data blocks whose address is generated by using hash function. The memory location where these records are stored is called as data block or data bucket. Hash File Organization : Also called Random file ,Direct file or Relative file. Data bucket –  Data buckets are the memory locations where the records are stored. These buckets are also considered as  Unit Of Storage . Hash Function –  Hash function is a mapping function that maps all the set of search keys to actual record address. Generally, hash function uses primary key to generate the hash index – address of the data block. Hash function can be simple mathematical function to any complex mathematical function. .

18. D iagram clearly depicts how hash function work:

19. There are two type of Hashing: 1) Internal Hashing : It is for internal file,here hashing is implemented through the use of an array of records . 2) External Hashing : This hashing is for disk files. Address space of disk is divided into buckets,Each of which hold multiple records. Pros: i )Records can be directly accessed. File accessing and updating become very easy.It is simple and fast. ii)Random file uses for access of on-line application. Cons: i )Processing speed is very slow. ii)Here expensive resourses are required. iii)It will be not suitable for all storage media,as it can be opened only on direct access devices.

20. Sr.No . Sequential(sorted) Indexed File Hashed/Direct 1. Random retrieval of primary key is impractical Random retrieval of primary key is moderately fast Random retrieval of primary key is very fast 2 Multiple key retrieval in sequential file organization is possible Multiple key retrieval is very fast with multiple indexes. Multiple key retrieval is not possible . 3 Addition of new records requires rewriting the file. Addition of new records is easy and requires maintenance of indexes. Addition of new record is very easy. 4 Deletion of record can create wasted space Deletion of record is easy ,if space can be allocated dynamically. Deletion of record is very easy. 5 There is no wasted space for data. No wasted space for data but there is extra space for Index. Extra space for addition or deletion of record 6 Sequential retrieval on primary is very fast Sequential retrieval on primary is moderately fast. Sequential retrieval on primary is impractical. 7 Updating the records generally requires rewriting file. Updating the records generally requires maintenance of index Updating of record is the easiest one.

21. Introduction to Database Database use to store information useful to an organization/an enterprise. A Database is the collection of information that is organized so that it can easily be access, managed, and updated. Database is the collection of data. Example, Consider the name, telephone number and address of the people. This information stored on diskette or a personal computer. This collection of related data with an implicit meaning called Database. Properties of Database: A Database represent some aspect of real world sometimes called the mini-world. Changes to the real world are reflect in the database. A Database is the logically collection of data with some meaning. A Database is designed, built and populated with data for a specific purpose. Database has some source from which data are derived, some degree of interaction with events in the real world, and audience that is actively interested in the contents of database.

22. Definition of Database: A Database is, “Organized collection of data from which users can efficiently retrieve the desired information”. Database can be defined as, “a Collection of interrelated data.” Database is defined as, ”a collection of logically related data stored together that is designed to meet information requirements of an organization.”

23. Components of database: Database consist of 4 components: Data Items : Is a distinct piece of information. Relationship: represents a correspondence between various data elements. Constraints are the predicates that define correct database states. Schema describes the organization of data and relationship within the database.

24. Introduction to DBMS A Database Management system is the system software that allows user to define, manipulate and process the data in a database, in order to produce meaningful information. It is a software system that allows user to define, create, manipulate and control access to the database. The Basic function of DBMS: To store data in a database. To organize the data. To control access of data. To protect data i.e. provide security.

25. The DBMS is hence a general purpose software system that facilitates the processes of defining constructing and manipulating databases for various applications. Defining: a database involves specifying the data types, structures and constraints for the data to be stored in the database. Constructing the database is the process of storing the data itself on some storage medium that controlled by the DBMS. Manipulating a database includes such functions as querying the database to retrieve specific data, updating the database to reflect changes in the mini-world, and generating reports from the data.

26. So in general, user can write programs or queries; DBMS use the database stored on storage devices and gives meaningful information. The database system is illustrated in the Fig. The main objective of a DBMS is to provide a convenient and effective method of defining, storing, retrieving and manipulating the data contained in the database. There are many DBMS like MySQL, PostgreSQL, Microsoft Access, SQL Server, FileMaker, Oracle, RDBMS, dBASE , Clipper, FoxPro and so on.

27. DBMS acts as an interface between the application program and the data stored in the database,fig

28. FILE SYSTEM VS DBMS File System: In file processing system the records are stored in separate files. Each file is called a flat file. To access the data from these flat files, various programs are written. So, that the system provides fruitful information to the end user. Actually speaking, the work is tedious. Because if user wants simple change in the resultant database, lot of changes are needed in the application programs.

29. Fig. shows an example of a traditional file processing system of an organization. All functional areas in the organization creates, processes and disseminates its own files. The files such as Sales department and Accounting department etc. generate separate files and do not communicate with each other.

30. F ile processing system limitations or disadvantages: 1. Separated Data: To make certain decision, a user might need data from different/separate files. Because of the involvement of system analyst and programmer, files may have different formats. For example, salary of an employee stored as integer in one file and real in other file. Therefore, files were evaluated first and then relationships were determined. After that programs could be written to extract data from files. 2. Isolated Data: Data are scattered in various files, and the files may be in different format, writing new application program to retrieve data is difficult in file system. 3. Data Redundancy: • Suppose same information was stored in more than one file. This repetition of data caused loss of data integrity, (means accurate and consistent data),  For example, Address of an employee was stored in three different files.If changes of address occurs and has been updated in one file only, then mismatch of information gives gives inconsistency to data

31. 4. Difficulty in Data Access: It has been said, files and records were described by specific physical formats by analysts and programmers, it is difficult to access data.  If the format of a certain record. was changed, the code must be immediately updated, system provides incorrect data. 5. Concurrent Access Anomalies: In the file processing system, once the file is opened by a user, it can not be used by another user, till first choses a file.It means sharing a file by various user at the same time is not possible. 6 . Security Problem: Enforcing security constraints in file processing system is very difficult as the application programs are added to the system in an ad-hoc manner. 7. Atomicity Problem : It is difficult to ensure atomicity in file processing system.

32. Database System (DBMS Environment) The DBMS software together with the database is called a database system. In other words, database system can be defined as "an organization of components that define and regulate the collection, storage, management and use of data in a database". A database system is a system whose overall purpose is to record and maintain information. It simple terms, Database + DBMS Software = Database System.  A database system consists of four major components as shown in Fig. 1.6 i.e., Data, Hardware, Software and Users.  A data file is a single disk file that stores related information on a hard disk .

33. Components of database system: 1. Data: • A data is collection of information or real fact which can be recorded and have implicit meaning. The whole data in the database system is stored in a single database and this data in the database are both shared, (sharing of data means individual pieces of data in the database is shared among different users and every user can access the same piece of data but may be for different purposes) and integrated, (integration of data means the database can be function of several distinct files with redundancy controlled among the files) 2. Hardware: The hardware consists of the secondary storage devices like disk, where the database resides together with other devices.  3 . Software:  It is a layer or interface of software exists between the physical database and the users. This layer called the DBMS 4. Users :  The users are the people interacting with the database system in any way. The four types of users interacting with the database are Application Programmers, Online users (naive users) and Database Administrator (DBA)

34. Database system of an organization:

35. Characteristics of DBMS  •A DBMS is a piece of software that is designed to make all above tasks easier. By storing a data in DBMS, rather than in files, we can use the DBMS features to manage the data in a robust and efficient manner. Characteristics of DBMS are given below: 1. Users File Approach: In file processing, each user defines and implements the files needed for a specific application. therefore more storage space is required.  In DBMS, a single database is maintained that is defined once and then is accessed by various users. DBMS software is not written for specific applications.  

36. 2. Self Describing Nature of DBMS System: A DBMS contains a database as well as a complete definition of the database, which is stored in the system catalog. It contains information such as the structure of each file, the type and storage format of each data item and various constraints of the data.  The catalog is used by DBMS software by database users who need information about the database. For example, a company databases, a banking database, a university database. The database definite stored in the catalog. In file processing, data definition is part of the application programs. These program only one specific database unlike DBMS can access diverse databases by extracting the database  In file processing, data definition is part of the application programs. These program only one specific database unlike DBMS can access diverse databases by extracting the database definitions from the catalogue .

37. 3. Isolation between Programs and Data : • DBMS access programs are written independently of any specific files. The su stored in catalog separately from the access program, (program-data independence) In file processing, if any change in structure of file is made then, all pro have to be changed, suppose we want to add any field in record of the file (say birth date in student file), then program has to be changed in file processing. 4. Multiple Views of Data: DBMS supports multiple views of the data, which a file cannot support. For example: one user is only interested for student mark list, other user interested for courses attended by that student, these multi-user view are satisfied by DBMS.

38. Comparison between File System and DBMS Sr.No . File Processing System Database Management system 1. PC based small systems Mini mainframe based large systems 2 Relatively cheap Relatively expensive 3 Less number of files used More number of files used 4 Single user system Multiple user system 5 Data redundancy and inconsistency occur Data is independent and non-redundant 6 Data access is difficult Data access is efficient 7 Data is isolated Data is integrated 8 Concurrent access to a file is not possible. Concurrent access and crash recovery possible 9 Security problems Better security 10 Little preliminary design. Vast preliminary design 11 Examples: C++, COBOL, VB Examples: MS-Access, Oracle, PostgreSQL 12 File system data sharing is not powerful as DBMS. DBMS offers features of data sharing efficiently 13 Transaction concept is not used,(transaction means set of related operations) The concept of transaction is important aspect of DBMS 14 of File system is an abstraction to store, retrieve and update a set of file. DBMS is a collection of data and program to access those data.

39. Level of Data Abstraction Level of Abstraction: 1) Database Management System provides the user with an abstract view of data. That is the database system hides certain details of how data are stored and maintained. 2)Data Abstraction means hiding the implementation details from the end user. 3)With several levels of abstraction, the user’s view of the database is simplified and this leads to the improved understanding of data. 4)There are three levels of abstraction :

40. Physical (Internal) Level: Internal level is the lowest level of abstraction. Internal level described in detail how the raw data is actually stored at the byte level using complex low level data structure. It also described the access paths for the database. The database system hides many of these lowest level of storage and access details from the database programmers and the end users. Logical (Conceptual) Level: Logical level is the next highest level of abstraction. It is used by the database administrators to described what data are stored in the database and relationship between those data. Here entire database is described in terms of relatively simple, easy to understand structure like data tables, keys, indices etc. It described the relationship between the different data stored in the database, the operation possible on those data and any constraints or rule to be imposed on the data.

41. View (External) Level: It is the highest level of abstraction. View level deals with the way a particular user application program views the data from the database. Each view level is used to describe a part of the database that a particular user group is interested in and hide the rest of the database from that user group. Schema and Instances in DBMS: 1) Schemas: The overall description of the database is called as the database schema. Database has 3 level architecture and accordingly there are 3 different types of schemas in the database.These are: i ) Physical schema :It is the lowest level., i.e , at Physical level ii) Logical schema: It is at the next or intermediate level i.e.,at Logical level iii) Sub_schema :It is at the highest level i.e. at the View level.

42. A Schema is defined as, “Outline or a plan that describes the record existing at a particular level”. The schema diagram is used to show the database schema.For example, Student_address Students_Marks A schema diagram displays only some aspects of a schema, like the names of record types and data items and some types of constraints. The description of the database is called as the database schema which is specified during database design and is not expected to change frequently. A displayed schema is called as a schema diagram. Roll_no Name Address Place PIN Roll_no Subject Exam_Date Marks

43. Instance: Database changes over a time when the information inserted or deleted. The collection of information stored in the database at a particular moment is called as an Instance of the database. For example., the instance of students_address table are as shown in following diagram: Students_address Roll_no Name Address Place PIN 1 Amar A-2/7,kondhwa Pune 455123 2 Deepa B-5, Sanket Pune 110014 3 Kiran G-8,Rajguru nagar Pune 110056

44. Schema Instance It is the overall description of the database. It is the collection of information stored in a database at a particular moment. Schema is same for whole database. Data in instances can be changed using addition, deletion, updation. Does not change Frequently. Changes Frequently. Defines the basic structure of the database i.e how the data will be stored in the database. It is the set of Information stored at a particular time. Difference between Instance and Schema

45. What is Data Independence of DBMS? Data Independence is defined as a property of DBMS that helps you to change the Database schema at one level of a database system without requiring to change the schema at the next higher level. Data independence helps you to keep data separated from all programs that make use of it. Data independence means that the application is independent of the storage structure and access strategy of data. Types of Data Independence In DBMS there are two types of data independence Physical data independence Logical data independence.

46. Physical Data Independence Physical data independence helps you to separate conceptual levels from the internal/physical levels. It allows you to provide a logical description of the database without the need to specify physical structures. Compared to Logical Independence, it is easy to achieve physical data independence. With Physical independence, you can easily change the physical storage structures or devices without an effect on the conceptual schema. Any change done would be absorbed by the mapping between the conceptual and internal levels. Physical data independence is achieved by the presence of the internal level of the database and then the transformation from the conceptual level of the database to the internal level.

47. Examples of changes under Physical Data Independence Due to Physical independence, any of the below change will not affect the conceptual layer. Using a new storage device like Hard Drive or Magnetic Tapes Modifying the file organization technique in the Database Switching to different data structures. Changing the access method. Modifying indexes. Changes to compression techniques or hashing algorithms. Change of Location of Database from say C drive to D Drive

48. Logical Data Independence Logical Data Independence is the ability to change the conceptual scheme without changing, External views External API or programs Any change made will be absorbed by the mapping between external and conceptual levels. When compared to Physical Data independence, it is challenging to achieve logical data independence. Examples of changes under Logical Data Independence Due to Logical independence, any of the below change will not affect the external layer. Add/Modify/Delete a new attribute, entity or relationship is possible without a rewrite of existing application programs Merging two records into one Breaking an existing record into two or more records

49. Logica Data Independence Physical Data Independence Logical Data Independence is mainly concerned with the structure or changing the data definition. Mainly concerned with the storage of the data. It is difficult as the retrieving of data is mainly dependent on the logical structure of data. It is easy to retrieve. Compared to Physical independence it is difficult to achieve logical data independence. Compared to Logical Independence it is easy to achieve physical data independence. You need to make changes in the Application program if new fields are added or deleted from the database. A change in the physical level usually does not need change at the Application program level. Modification at the logical levels is significant whenever the logical structures of the database are changed. Modifications made at the internal levels may or may not be needed to improve the performance of the structure. Concerned with conceptual schema Concerned with internal schema Example: Add/Modify/Delete a new attribute Example: change in compression techniques, hashing algorithms , storage devices , etc

50. Structure of DBMS The Basic components of DBMS can be divided into following three subsystems: 1. Design tool : It allows to create database form and report. 2. Runtime Facilities : It process the application created by design tools. 3. DBMS Engine: It works as a translator between design tool and runtime facilities. It divided into two main parts: i )Query Processor components ii)Storage manager components

51. 1. Query Processor :   It interprets the requests (queries) received from end user via an application program into instructions. It also executes the user request which is received from the DML compiler.  Query Processor contains the following components –  DML Compiler –   It processes the DML statements into low level instruction (machine language), so that they can be executed. When query is given i.e. DML statement, it translate that DML statement to low level instruction which query engine understand. DDL Interpreter –   It processes the DDL statements into a set of table containing meta data (data about data) also called Data Dictionary.   Embedded DML Pre-compiler –   It processes DML statements embedded in an application program into procedural calls.    Query Optimizer –   It executes the instruction generated by DML Compiler to fetch the necessary data.  

52. 2. Storage Manager :   Storage Manager is a program that provides an interface between the data stored in the database and the queries received. It is also known as Database Control System. It maintains the consistency and integrity of the database by applying the constraints and executes the DCL statements. It is responsible for updating, storing, deleting, and retrieving data in the database.  It contains the following components –  Authorization Manager –   When a query is submitted to a system , it is first checked for authorized data access. i.e. a person who execute query has right or not.   Integrity Manager –   It checks the integrity constraints when the database is modified.  Transaction Manager –   Despite of system failure, this ensure about the consistency of data. Thus, it ensures that the database remains in the consistent state before and after the execution of a transaction. It also maintain Atomicity. File Manager –   It manages the file space and the data structure used to represent information in the database.  Buffer Manager –   It is responsible for cache memory and the transfer of data between the secondary storage and main memory.   

53. 3. Disk Storage :   It contains the following components –  Data Files –  It stores the data.   Data Dictionary –  It contains the information about the structure of any database object. It is the repository of information that governs the metadata.  Indices –  It provides faster retrieval of data item.  Statistical data : This stores statistical data about data.

54. Users in DBMS • The users of database systems can be classified in the following groups. These groups are on the basis of degree of expertise or the mode of their interactions with DBMS. 1. DBMS users. 2. Database managers. 3. Database administrators. DBMS Users • DBMS provide an environment to store and retrieve information. On the basis of interaction with the system, users are differentiated in four types as explained below: 1. Application Programmer: i ) These are the computer professionals. They are responsible for developing application programs. ii) For development of application programs, these people used a general purpose programming language like C, COBOL, Pascal. iii) Through these program application programmers handle or manipulate the databases.

55. 2. Sophisticated Users (On-Line Users): i ) These people are also computer professional but they do not write programs. To interact with the system, they use query languages like SQL. ii) These are users who may communicate with the database directly via an on-line terminal or indirectly via a user interface and application program. iii)These users are aware of the presence of the database system and have limited interaction with database through application programs. iv)The more sophisticated of these users may also use a data manipulation language to manipulate the database directly. They are also called as on-line users. 3. Specialized Users: i )They are sophisticated users. They write specialized database application which do not fit into traditional data frame work. ii) For example: Computer-Aided Design (CAD) system, knowledge base and expert systems etc that store data with complex data types (For example: Graphics data and audio data) modelling systems. 4. Naive Users (Un-experienced Users): i )There are the users who are not aware of the presence of database systems or any other system. ii)These users are called as unsophisticated users who interact with the system through already written permanent programs. iii)In simple words, naive users are the end users.

56. Database Manager i )Databases require a large amount of storage space, so storing the database requires secondary s torage. ii)It is essential that the database systems, structure the data to minimize the need to move data between disk and main memory. iii) A database manager (DB Manager) provides basic database management functionalities like creation and maintenance of databases. iv) A 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 is responsible for the following tasks/functions: 1. Interaction with File Manager: i ) The raw data is stored on the disk using the file system, which is usually provided by a conventional operating system. ii) The database manager translates the various DML statements into low-level file system commands. Thus, the database manager is responsible for the actual storing, retrieving and updating of data in the database.

57. 2. Integrity Enforcement: i ) The data values stored in the database must satisfy certain types of consistency constraints. ii)For example: the number of hours an employee may work in one week may not exceed than some specific limit (say 80 hrs) otherwise appropriate action is taken by database manager. 3. Security: i ) Security to users is provided by database manager i.e., to access only required data. 4. Backup and Recovery: i ) In case of hard disk crash or power failure or software errors, database manager have responsibility to defect such failures and restore the database. 5. Concurrency Control: i ) Controlling the interaction among the concurrent users is another responsibility of database manager.

58. Database Administrator (DBA) The DBA is a person who must have a good knowledge in data processing. He/She must sufficient technical knowledge to make wise decisions in the organization. • The DBA must manage the staff to ensure orderly development of the database project, to satisfy database users and to plan for future database requirements. DBA is responsible for overall performance of database. Some of the main responsibilities (functions) of DBA are as follows: 1. Monitoring the Performance: DBA is responsible for overall performance of database. 2. Deciding the Internal Schema of Structure of Physical Storage: DBA decides how the data actually stored at physical storage, how data is represented at physical storage. 3. Deciding the Conceptual Schema or Contents of Database: DBA decides the data fields, tables, queries, data types, attributes, relations, entities or we can say that he/she is responsible for overall logical design of database. 4. Deciding User View: DBA decides different views for different users.

59. 5. Deciding Constraints: DBA decides various constraints over database for maintaining consistency and validity in database. 6. Deciding Users: DBA gives permission to user to use database. Without having proper permission, no one can access data from database. 7. Granting of Authorities: DBA gives authorities or rights to data access. User can use only that data on which access rights is granted to him/her. 8. Liaising with Users: Another task of the DBA is to liaising with users and ensure the availability of the data they require and write the necessary external schemas. 9. Removal of Dump and Maintain Free Space: DBA is responsible for removing unnecessary data from storage and maintain enough free space for daily operations. 10. Checks: DBA also decides various security and validation checks over database to ensure consistency. 11. Backup: DBA takes regular backup of database, so that it can be used during system failure. 12. Security: DBA takes various steps to make data more secure against various disasters and unauthorized access of data.

60. Advantages of DBMS • Various advantages of DBMS over file system are listed below: 1. Sharing of Data: In DBMS the data is centrally controlled and can be shared by all authorized users. 2. Improved Data Integrity: In database systems, data integrity means that the data contained in the database is both accurate and consistent. The centralized control of DBMS allow adequate checks can be incorporated to provide data integrity. 3. Data Consistency: In DBMS the problem of inconsistent data is automatically solved by controlling the redundancy. 4. Controlled Redundancy: In DBMS, the duplication of data can be carefully controlled, that means the database system is aware of the redundancy and it assumes the responsibility for propagating updates. 5. Efficient Data Access: The DBMS utilizes different sophisticated techniques to access the stored data very efficiently. 6. Program Data Independence: The DBMS provide an independence between the file system and application program, that allows for changes at one level of the data without affecting others.

61. 7. Enforcement of Standards: In DBMS, data being stored at one central place, standards can easily be enforced by the DBA. This ensures standardised data formats to facilitate data transfers between systems. 8. Improved Security: Database security means protecting the data contained in the database from unauthorized users. In DBMS the DBA ensures that proper access procedure are followed, including proper authentical schemes for access to the database and additional check permitting access to sensitive data. 9. Improved backup and Recovery Facility: Through backup and recovery subsystem of DBMS provides the facilities for recovering from hardware or software failures. 10 Concurrency Control: The DBMS are designed to manage simultaneous (concurrent) access of the database by many users. They also prevents any loss of information (loss of integrity) due to these concurrent accesses.

62. 11.High Data Quality: The quality of data in database systems are very high as compared to traditional file systems. 12. Minimal Program Maintenance : In a traditional file system, high maintenance efforts are required. These are reduced to minimal in database systems due to independence of data and application programs. 13. Economical to Scale or Low Cost: In DBMS, the operational data of an organization is stored in a central database. The application programs that work on this data can be built with very less cost as compared to traditional file system. This reduces overall costs of operation and management of the database that leads to an economical scaling. 14. Increased Programmer Productivity and Reduced Development Time : The DBMS provides many standard functions and these functions allow the programmers to concentrate on the specific functionality required by the users without worrying about the implementation details. This increases the overall productivity of the programmer and also reduced the development time and cost. 15. Good Data Accessibility and Responsiveness: The database systems provide query languages (or report writers) that allow the users to ask ad-hoc queries to obtain the needed information immediately, without the requirement to write application programs. 14. Language Interface: DBMS provide a language support for definition and manipulation of data.