Introduction to Database Management System (Part 2) PDF

INTRODUCTION TO DATABASE INTRODUCTION TO DATABASE MANAGEMENT SYSTEM(PART 2) Learning Outcome: Basic Definitions and Concepts: ▪ Component of DBMS ▪ Traditional File System ▪ Disadvantages of Traditional File System ▪ Component of Database System ▪ Advantages of Database System ▪ Disadvantages of Da...

INTRODUCTION TO DATABASE INTRODUCTION TO DATABASE MANAGEMENT SYSTEM(PART 2) Learning Outcome: Basic Definitions and Concepts: ▪ Component of DBMS ▪ Traditional File System ▪ Disadvantages of Traditional File System ▪ Component of Database System ▪ Advantages of Database System ▪ Disadvantages of Database System ▪ DBMS Users ▪ Responsibilities of DBA 1.Components of DBMS A DBMS has three main components. These are Data Definition Language (DDL), Data Manipulation Language and Query Facilities (DML/SQL) and software for controlled access of Database as shown in Figure 1 and are defined as follows : 1.Components of DBMS 1. Data Definition Language (DDL): It allows the users to define the database, specify the data types, data structures and the constraints on the data to be stored in the database. 2. Data Manipulation Language (DML) and Query Language: DML allows users to insert, update, delete and retrieve data from the database. SQL provides general query facility. 3. Software for Controlled Access of Database: This software provides the facility of controlled access of the database by the users, concurrency control to allow shared access of the database and a recovery control system to restore the database in case of hardware or software failure. Traditional File System Conventionally, the data were stored and processed using traditional file processing systems. In these traditional file systems, each file is independent of other file, and data in different files can be integrated only by writing individual program for each application. The data and the application programs that uses the data are so arranged that any change to the data requires modifying all the programs that uses the data. This is because each file is hard-coded with specific information like data type, data size etc. Some time it is even not possible to identify all the programs using that data and is identified on a trial-and-error basis. Traditional File System Different between File System & DBMS Different between File System & DBMS 3. Disadvantages of Traditional File System ▪ Data Redundancy: Since each application has its own data file, the same data may have to berecorded and stored in many files. For example, personal file and payroll file, both contain data on employee name, designation etc. The result is unnecessary duplicate or redundant data items. This redundancy requires additional or higher storage space, costs extra time and money, and requires additional efforts to keep all files up-to-date. 3. Disadvantages of Traditional File System ▪ Data Inconsistency: Data redundancy leads to data inconsistency especially when data is to be updated. Data inconsistency occurs due to the same data items that appear in more than one file do not get updated simultaneously in each and every file. For example, an employee is promoted from Clerk to Superintendent and the same is immediately updated in the payroll file may not necessarily be updated in provident fund file. 3. Disadvantages of Traditional File System ▪ Lack of Data Integration: Since independent data file exists, users face difficulty in getting information on any ad hoc query that requires accessing the data stored in many files. In such a case complicated programs have to be developed to retrieve data from every file or the users have to manually collect the required information. 3. Disadvantages of Traditional File System Program Dependence: The reports produced by the file processing system are program dependent, which means if any change in the format or structure of data and records in the file is to be made, the programs have to modified correspondingly. Also, a new program will have to be developed to produce a new report. 3. Disadvantages of Traditional File System Data Dependence: The Applications/programs in file processing system are data dependent i.e., the file organization, its physical location and retrieval from the storage media are dictated by the requirements of the particular application. For example, in payroll application, the file may be organised on employee records sorted on their last name, which implies that accessing of any employee’s record has to be through the last name only. 3. Disadvantages of Traditional File System Limited Data Sharing: There is limited data sharing possibilities with the traditional file system. Each application has its own private files and users have little choice to share the data outside their own applications. Complex programs required to be written to obtain data from several incompatible files. 3. Disadvantages of Traditional File System Poor Data Control: There was no centralised control at the data element level, hence a traditional file system is decentralised in nature. It could be possible that the data field may have multiple names defined by the different departments of an organization and depending on the file it was in. This situation leads to different meaning of a data field in different context or same meaning for different fields. This causes poor data control. 3. Disadvantages of Traditional File System Problem of Security: It is very difficult to enforce security checks and access rights in a traditional file system, since application programs are added in an adhoc manner. Data Manipulation Capability is Inadequate: The data manipulation capability is very limited in traditional file systems since they do not provide strong relationships between data in different files. 3. Disadvantages of Traditional File System Needs Excessive Programming: An excessive programming effort was needed to develop a new application program due to very high interdependence between program and data in a file system. Each new application requires that the developers start from the scratch by designing new file formats and descriptions and then write the file access logic for each new file. Database Systems or Database System Environment The DBMS software together with the Database is called a database system. In other words, it can be defined as an organization of components that define and regulate the collection, storage, management and use of data in a database. Furthermore, it is a system whose overall purpose is to record and maintain information. 4. Components of Database Systems A database system consists of four major components as shown in Figure 1. Data 2. Hardware 3. Software 4. Users 4.1 Data The whole data in the system is stored in a single database. This data in the database are both shared and integrated. 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. Integration of data means the database can be function of several distinct files with redundancycontrolled among the files. 4.2 Hardware The hardware consists of the secondary storage devices like disks, drums and so on, where the database resides together with other devices. There is two types of hardware: The first one, i.e., processor and main memory that supports in running the DBMS. The second one is the secondary storage devices, i.e., hard disk, magnetic disk etc., that are used to hold the stored data. 4.3 Software A layer or interface of software exists between the physical database and the users. This layer is called the DBMS. All requests from the users to access the database are handled by the DBMS. Thus, the DBMS shields the database users from hardware details. Furthermore, the DBMS provides the other facilities like accessing and updating the data in the files and adding and deleting files itself. 4.4 Users The users are the people interacting with the database system in any way. There are four types of users interacting with the database systems. These are Application Programmers, online users, end users or naive users and finally the Database Administrator (DBA). 5. Advantages of Database Systems 1. Controlled redundancy: In a traditional file system, each application program has its own data, which causes duplication of common data items in more than one file. This duplication/redundancy requires multiple update for a single transaction and wastes a lot of storage space. We cannot eliminate all redundancy due to technical reasons. But in a database, this duplication can be carefully controlled, that means the database system is aware of the redundancy and it assumes the responsibility for propagating updates. 5. Advantages of Database Systems 2. Data consistency: The problem of updating multiple files in traditional file system leads to inaccurate data as different files may contain different information of the same data item at a given point of time. This causes incorrect or contradictory information to its users. In database systems, this problem of inconsistent data is automatically solved by controlling the redundancy. 5. Advantages of Database Systems 3. Program data independence: The traditional file systems are generally data dependent, which implies that the data organization and access strategies are dictated by the needs of the specific application and the application programs are developed accordingly. However, the database systems provide an independence between the file system and application program, that allows for changes at one level of the data without affecting others. This property of database systems allow to change data without changing the application programs that process the data. 5. Advantages of Database Systems 4. Improved data sharing: The DBMS helps create an environment in which end users have better access to more and better-managed data. Such access makes it possible for end users to respond quickly to changes in their environment. 5. Improved data security: The more users access the data, the greater the risks of data security breaches. Corporations invest considerable amounts of time, effort, and money to ensure that corporate data is used properly. A DBMS provides a framework for better enforcement of data privacy and security policies. 5. Advantages of Database Systems 6. Better data integration: Wider access to well-managed data promotes an integrated view of the organization’s operations and a clearer view of the big picture. It becomes much easier to see how actions in one segment of the company affect other segments. 7. Improved decision making: Better-managed data and improved data access make it possible to generate better-quality information, on which better decisions are based. 8. Increased end-user productivity: The availability of data, combined with the tools that transform data into usable information, empowers end users to make quick, informed decisions that can make the difference between success and failure in the global economy. 6. Disadvantages of Database Systems 1. Complexity increases: The data structure may become more complex because of the centralised database supporting many applications in an organization. This may lead to difficulties in its management and may require professionals for management. 2. Requirement of more disk space: The wide functionality and more complexity increase the size of DBMS. Thus, it requires much more space to store and run than the traditional file system. 6. Disadvantages of Database Systems 3. Additional cost of hardware: The cost of database system’s installation is much more. It depends on environment and functionality, size of the hardware and maintenance costs of hardware. 4. Cost of conversion: The cost of conversion from old file-system to new database system is very high. In some cases the cost of conversion is so high that the cost of DBMS and extra hardware becomes insignificant. It also includes the cost of training manpower and hiring the specialized manpower to convert and run the system. 6. Disadvantages of Database Systems 5. Need of additional and specialized manpower: Any organization having database systems, need to be hire and train its manpower on regular basis to design and implement databases and to provide database administration services. 6. Need for backup and recovery: For a database system to be accurate and available all times, a procedure is required to be developed and used for providing backup copies to all its users when damage occurs. 6. Disadvantages of Database Systems 7. Organizational conflict: A centralised and shared database system requires a consensus on data definitions and ownership as well as responsibilities for accurate data maintenance. 8. More installation and management cost: The big and complete database systems are more costly. They require trained manpower to operate the system and has additional annual maintenance and support costs. 7. DBMS Users The users of a database system can be classified into various categories depending upon their interaction and degree of expertise of the DBMS. There are four types of users interacting with the database systems. These are: 1. End users or naive users 2. Online users 3. Application Programmers 4. Database Administrator (DBA) 7.1 End Users or Naive Users The end users or naive users use the database system through a menu-oriented application program, where the type and range of response is always displayed on the screen. The user need not be aware of the presence of the database system and is instructed through each step. A user of an ATM falls in this category. 7.2 Online Users These type of users communicate with the database directly through an online terminal or indirectly through an application program and user interface. They know about the existence of the database system and may have some knowledge about the limited interaction they are permitted. 7.3 Application Programmers These are the professional programmers or software developers who develop the application programs or user interfaces for the naive and online users. These programmers must have the knowledge of programming languages such as Assembly, C, C++, Java, or SQL, etc., since the application programs are written in these languages. 7.4 Database Administrator Database Administrator (DBA) is a person who have complete control over database of any enterprise. DBA is responsible for overall performance of database. He is free to take decisions for database and provides technical support. He is concerned with the Back-End of any project. 8. Responsibilities of DBA 1. Deciding the conceptual schema or contents of database: DBA decides the data fields, tables, queries, data types, attributes, relations, entities or you can say that he is responsible for overall logical design of database. 2. Deciding the internal schema of structure of physical storage: DBA decides how the data is actually stored at physical storage, how data is represented at physical storage. 8. Responsibilities of DBA 3. Deciding users: DBA gives permission to users to use database. Without having proper permission, no one can access data from database. 4. Deciding user view: DBA decides different views for different users. 5. Granting of authorities: DBA decides which user can use which portion of database. DBA gives authorities or rights to data access. User can use only that data on which access right is granted to him. 8. Responsibilities of DBA 6. Deciding constraints: DBA decides various constraints over database for maintaining consistency and validity in database. 7. Security: Security is the major concern in database. DBA takes various steps to make data more secure against various disasters and unauthorized access of data. 8. Monitoring the performance: DBA is responsible for overall performance of database. DBA regularly monitors the database to maintain its performance and try to improve it. 8. Responsibilities of DBA 9. Backup: DBA takes regular backup of database, so that it can be used during system failure. Backup is also used for checking data for consistency. 10. Removal of dump and maintain free space: DBA is responsible for removing unnecessary data from storage and maintain enough free space for daily operations. He can also increase storage capacity when necessary. 8. Responsibilities of DBA 11. Checks: DBA also decides various security and validation checks over database to ensure consistency. 12. 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.