Introduction to Database Systems: Concepts, History, and Evolution, Study notes of Database Management Systems (DBMS)

Download Introduction to Database Systems: Concepts, History, and Evolution and more Study notes Database Management Systems (DBMS) in PDF only on Docsity!

DBMS COMPLETE NOTES

• UNIT -

DIFFERENCE BETWEEN DATA AND INFORMATION:

DATA INFORMATION

1.Raw facts 1.Processed data

2. It is in unorganized form 2. It is in organized form
3. Data doesn’t help in decision
   3. Information helps in decision making process making process FILE ORIENTED APPROACH: The earliest business computer systems were used to process business records and produce information. They were generally faster and more accurate than equivalent manual systems. These systems stored groups of records in separate files, and so they were called file processing systems.
4. File system is a collection of data. Any management with the file system, user has to write the procedures
5. File system gives the details of the data representation and Storage of data.
6. In File system storing and retrieving of data cannot be done efficiently.
7. Concurrent access to the data in the file system has many problems like a Reading the file while other deleting some information, updating some information
8. File system doesn’t provide crash recovery mechanism. Eg. While we are entering some data into the file if System crashes then content of the file is lost.
9. Protecting a file under file system is very difficult. The typical file-oriented system is supported by a conventional operating system. Permanent records are stored in various files and a number of different application programs are written to extract records from and add records to the appropriate files.

DISADVANTAGES OF FILE-ORIENTED SYSTEM:

The following are the disadvantages of File-Oriented System: Data Redundancy and Inconsistency: Since files and application programs are created by different programmers over a long period of time, the files are likely to be having different formats and the programs may be written in several programming languages. Moreover, the same piece of information may be duplicated in several places. This redundancy leads to higher storage and access cost. In addition, it may lead to data inconsistency. Difficulty in Accessing Data: The conventional file processing environments do not allow needed data to be retrieved in a convenient and efficient manner. Better data retrieval system must be developed for general use. Data Isolation: Since data is scattered in various files, and files may be in different formats, it is difficult to write new application programs to retrieve the appropriate data. Concurrent Access Anomalies: In order to improve the overall performance of the system and obtain a faster response time, many systems allow multiple users to update the data simultaneously. In such an environment, interaction of concurrent updates may result in inconsistent data. Security Problems: Not every user of the database system should be able to access all the data. For example, in banking system, payroll personnel need only that part of the database that has information about various bank employees. They do not need access to information about customer accounts. It is difficult to enforce such security constraints. Integrity Problems: The data values stored in the database must satisfy certain types of consistency constraints. For example, the balance of a bank account may never fall below a prescribed amount. These constraints are enforced in the system by adding appropriate code in the various

1. Data Redundancy and Inconsistency: The address and telephone number of a particular customer may appear in a file that consists of savings-account records and in a file that consists of checking-account records. This redundancy leads to higher storage and access cost. In, it may lead to data inconsistency; that is, the various copies of the same data may no longer agree. For example, a changed customer address may be reflected in savings-account records but not elsewhere in the system. 2. Difficulty in Accessing Data: Suppose that one of the bank officers needs to find out the names of all customers who live within a particular postal-code area. The officer asks the data-processing department to generate such a list. Because there is no application program to generate that. The bank officer has now two choices: either obtain the list of all customers and extract the needed information manually or ask a system programmer to write the necessary application program. Both alternatives are obviously unsatisfactory. 3. Data Isolation: Because data are scattered in various files and files may be in different formats, writing new application programs to retrieve the appropriate data is difficult. 4. Integrity Problems: The balance of a bank account may never fall below a prescribed amount (say, $25). Developers enforce these constraints in the system by adding appropriate code in the various application programs. However, when new constraints are added, it is difficult to change the programs to enforce them. The problem is compounded when constraints involve several data items from different files. 5. Atomicity Problems: A computer system, like any other mechanical or electrical device, is subject to failure. In many applications, it is crucial that, if a failure occurs, the data be restored to the consistent state that existed prior to the failure. Consider a program to transfer $50 from account A to account B. If a system failure occurs during the execution of the program, it is possible that the $50 was removed from account A but was not credited to account B , resulting in an inconsistent database state. Clearly, it is essential to database consistency that either both the credit and debit occur, or that neither occur. That is, the funds transfer must be atomic —it must happen in its entirety or not at all. It is difficult to ensure atomicity in a conventional file-processing system.

6. Concurrent-Access Anomalies: For the sake of overall performance of the system and faster response, many systems allow multiple users to update the data simultaneously. In such an environment, interaction of concurrent updates may result in inconsistent data. Consider bank account A , containing $500. If two customers withdraw funds (say $50 and $100 respectively) from account A at about the same time, the result of the concurrent executions may leave the account in an incorrect (or inconsistent) state. Suppose that the programs executing on behalf of each withdrawal read the old balance, reduce that value by the amount being withdrawn, and write the result back. If the two programs run concurrently, they may both read the value $500, and write back $450 and $400, respectively. Depending on which one writes the value last, the account may contain $450 or $400, rather than the correct value of $350. To guard against this possibility, the system must maintain some form of supervision. But supervision is difficult to provide because data may be accessed by many different application programs that have not been coordinated previously. 7. Security Problems: Not every user of the database system should be able to access all the data. For example, in a banking system, payroll personnel need to see only that part of the database that has information about the various bank employees. They do not need access to information about customer accounts. But, since application programs are added to the system in an ad hoc manner, enforcing such security constraints is difficult. These difficulties, among others, prompted the development of database systems.

 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. The Evolution of Database systems: The Evolution of Database systems are as follows:

1. File Management System
2. Hierarchical database System
3. Network Database System
4. Relational Database System File Management System: The file management system also called as FMS in short is one in which all data is stored on a single large file. The main disadvantage in this system is searching a record or data takes a long time. This lead to the introduction of the concept, of indexing in this system. Then also the FMS system had lot of drawbacks to name a few like updating or modifications to the data cannot be handled easily, sorting the records took long time and so on. All these drawbacks led to the introduction of the Hierarchical Database System. Hierarchical Database System: The previous system FMS drawback of accessing records and sorting records which took a long time was removed in this by the introduction of parent-child relationship between records in database. The origin of the data is called the root from which several branches have data at different levels and the last level is called the

leaf. The main drawback in this was if there is any modification or addition made to the structure then the whole structure needed alteration which made the task a tedious one. In order to avoid this next system took its origin which is called as the Network Database System. Fig: Hierarchical Database System Network Database System : In this the main concept of many-many relationships got introduced. But this also followed the same technology of pointers to define relationships with a difference in this made in the introduction if grouping of data items as sets.

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. CHARACTERISTICS OF DATABASE: The database approach has some very characteristic features which are discussed in detail below: Structured and Described Data: Fundamental feature of the database approach is that the database system does not only contain the data but also the complete definition and description of these data. These descriptions are basically details about the extent, the structure, the type and the format of all data and, additionally, the relationship between the data. This kind of stored data is called metadata ("data about data").

Separation of Data and Applications: Application software does not need any knowledge about the physical data storage like encoding, format, storage place, etc. It only communicates with the management system of a database (DBMS) via a standardized interface with the help of a standardized language like SQL. The access to the data and the metadata is entirely done by the DBMS. In this way all the applications can be totally separated from the data. Data Integrity: Data integrity is a byword for the quality and the reliability of the data of a database system. In a broader sense data integrity includes also the protection of the database from unauthorized access (confidentiality) and unauthorized changes. Data reflect facts of the real world. Transactions: A transaction is a bundle of actions which are done within a database to bring it from one consistent state to a new consistent state. In between the data are inevitable inconsistent_. A_ transaction is atomic what means that it cannot be divided up any further. Within a transaction all or none of the actions need to be carried out. Doing only a part of the actions would lead to an inconsistent database state. Example : One example of a transaction is the transfer of an amount of money from one bank account to another. Data Persistence: Data persistence means that in a DBMS all data is maintained as long as it is not deleted explicitly. The life span of data needs to be determined directly or indirectly be the user and must not be dependent on system features. Additionally data once stored in a database must not be lost. Changes of a database which are done by a transaction are persistent. When a transaction is finished even a system crash cannot put the data in danger

Centralized Database : It is a database that is located, stored, and maintained in a single location. This location is most often a central computer or database system, for example a desktop or server CPU, or a mainframe computer. In most cases, a centralized database would be used by an organization (e.g. a business company) or an institution (e.g. a university.) Distributed Database: A distributed database is a database in which storage devices are not all attached to a common CPU. It may be stored in multiple computers located in the same physical location, or may be dispersed over a network of interconnected computers.

INTRODUCTION TO DATABASE-MANAGEMENT SYSTEM:

Database Management System: A database-management system (DBMS) is a collection of interrelated data and a set of programs to access those data. The DBMS is a general purpose software system that facilitates the process of defining constructing and manipulating databases for various applications. 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: 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. ADVANTAGES OF A DBMS OVER FILE SYSTEM: Using a DBMS to manage data has many advantages: Data Independence: Application programs should be as independent as possible from details of data representation and storage. The DBMS can provide an abstract view of the data to insulate application code from such details. Efficient Data Access: A DBMS utilizes a variety of sophisticated techniques to store and retrieve data efficiently. This feature is especially important if the data is stored on external storage devices.

DISADVANTAGES OF DBMS:

Danger of a 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. DATABASE USERS & DATABASE ADMINISTRATORS: People who work with a database can be categorized as database users or database administrators. Database Users: There are four different types of database-system users, differentiated by the way they expect to interact with the system. Naive users: Naive users are unsophisticated users who interact with the system by invoking one of the application programs that have been written previously.

For example, a bank teller who needs to transfer $50 from account A to account B invokes a program called transfer. This program asks the teller for the amount of money to be transferred, the account from which the money is to be transferred, and the account to which the money is to be transferred. Application programmers: Application programmers are computer professionals who write application programs. Application programmers can choose from many tools to develop user interfaces. Rapid application development (RAD) tools are tools that enable an application programmer to construct forms and reports without writing a program. Sophisticated users: Sophisticated users interact with the system without writing programs. Instead, they form their requests in a database query language. They submit each such query to a query processor , whose function is to break down DML statements into instructions that the storage manager understands. Analysts who submit queries to explore data in the database fall in this category. Specialized users: Specialized users are sophisticated users who write specialized database applications that do not fit into the traditional data-processing framework. Database Administrator: One of the main reasons for using DBMSs is to have central control of both the data and the programs that access those data. A person who has such central control over the system is called a database administrator ( DBA ). Database Administrator Functions/Roles: The functions of a DBA include: Schema definition : The DBA creates the original database schema by executing a set of data definition statements in the DDL, Storage structure and access-method definition.