Database Management Systems Notes | DBMS Concepts & SQL Guide, Exams of Database Management Systems (DBMS)

Download Database Management Systems Notes | DBMS Concepts & SQL Guide and more Exams Database Management Systems (DBMS) in PDF only on Docsity!

Faikate Public School

Digital notes

On

database

management system

Department of Information Technology

UNIT-ONE

Introduction to Database Management System

or

ABC

DEF

XYZ

01 ABC

: Collection of related records.

ABC

DEF

XYZ

The columns of this relation are called , The rows are called Tuples or.

Database: Collection of related relations. Consider the following collection of tables:

or

As the name suggests, the database management system consists of two parts. They are: and

To find out what database is, we have to start from data, which is the basic building block of any DBMS. ฀ ฀

Record:^ Data:^ Facts Collection of related data items ,^ figures ,^ statistics^ etc. having no particular meaning (e.g. 01, ABC, 19 etc)., e.g. in the above example the three data items had no meaning. However, if we organize them in the following way, then they collectively represent meaningful information.

Table

T

Roll

Relation

What is a Database?

Name Age

Roll

Roll

T

Roll 01 02 03

Name

Name

Address KOL DEL MUM

Age

Age

FieldsAttributes Records

Domains.

T

Roll 01 02 03

Year I II I

Database Management System

On the other hand, the columns are often called single attribute are of the same data type.

or. All the data items within a

A Database management system is a computerized record-keeping system. It is a repository or a container for collection of computerized data files. The overall purpose of DBMS is to allow the users to define , store , retrieve and update the information contained in the database on demand. Information can be anything that is of significance to an individual or organization.

฀ A database-management system (DBMS) is a collection of interrelated data and a set of programs to access those data. This is a collection of related data with an implicit meaning and hence is a database. The collection of data, usually referred to as the database, contains information relevant to an enterprise. ฀ The primary goal of a DBMS is to provide a way to store and retrieve database information that is both convenient and efficient. By data, we mean known facts that can be recorded and that have implicit meaning.

The management system is important, why? Because without the existence of some kind of rules and regulations it is not possible to maintain the database. We have to select the particular attributes, which should be included in a particular table; the common attributes to create relationship between two tables; if a new record has to be inserted or deleted then which tables should have to be handled etc. These issues must be resolved by having some kind of rules to follow in order to maintain the integrity of the database. ฀ Database systems are designed to manage large bodies of information. ฀ Management of data involves both defining structures for storage of information and providing

mechanisms for the manipulation of information. In addition, 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. Because information is so important in most organizations, computer scientists have developed a large body of concepts and techniques for managing data. This chapter briefly introduces the principles of database systems.

Domain Attribute

Databases touch all aspects of our lives. Some of the major areas of application are as follows:

What is Management System?

Database Management System (DBMS) and Its Applications:

Finance For storing information about holdings, sales, and purchases of financial instruments such as stocks and bonds; also for storing real-time market data to enable online trading by customers and automated trading by the firm. Universities For student information, course registrations, and grades (in addition to standard enterprise information such as human resources and accounting). Airlines For reservations and schedule information. Airlines were among the first to use databases in a geographically distributed manner. Telecommunication For keeping records of calls made, generating monthly bills, maintaining balances on prepaid calling cards, and storing information about the communication networks.

Banking and Finance

Enterprise Information Sales : For customer, product, and purchase information. Accounting : For payments, receipts, account balances, assets and other accounting information. Human resources : For information about employees, salaries, payroll taxes, and benefits, and for generation of paychecks.

Banking Airlines Universities Manufacturing and selling Human resources

: For customer information, accounts, loans, and banking transactions. : For purchases on credit cards and generation of monthly statements.

: For management of the supply chain and for tracking production of items in factories, inventories of items in warehouses and stores, and orders for items. : For sales data noted above plus online order tracking, generation of recommendation lists, and maintenance of online product evaluations.

◦ Manufacturing

◦ Online retailers

Banking Credit card transactions

฀ LECTURE TWO -DBMS

฀ (^) Page | 1

In business, processing knowledge is more useful to make decisions for any organization.

When Processed

DATA

a) Raw facts. b) It is in unorganized form c) Data doesn’t help in Decision making process

The processed data is known as information. For example, Marks: 89; then it becomes information.

1. Knowledge refers to the practical use of information.
2. Knowledge necessarily involves a personal experience.

When Processed

INFORMATION

a) Processed data b) It is in organized form c) Information helps in Decision making process

The raw facts are called as data. The word “raw” indicates that they have not been processed. For example, is the data.

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.

The process of converting the data (raw facts) into meaningful information is called as data/information processing.

DATA INFORMATION

INTRODUCTION TO BASIC CONCEPTS OF DATABASE SYSTEMS

What is Data?

KNOWLEDGE

Cont

What is Knowledge?

What is information?

FILE ORIENTED APPROACH:

DATA/INFORMATION PROCESSING:

DIFFERENCE BETWEEN DATAAND INFORMATION:

Note:

฀ LECTURE TWO -DBMS

฀ (^) Page | 2

1. Difficulty in Accessing Data:
2. Data Redundancy and Inconsistency:

DISADVANTAGES OF FILE-ORIENTED SYSTEM:

The following are the disadvantages of File-Oriented System:

Since files and application programs are created by different programmers over a long period, the files are likely to be: a) Having different formats and the programs may be written in several programming languages. b) Moreover, the same piece of information may be duplicated in several places. ฀ This redundancy leads to: ฀ ฀

In addition, it may lead to^ Higher^ storage^ and^ access cost data inconsistency..

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.

1. File system is a collection of data. Any management with the file system, user has to write the procedures.
2. File system gives the details of the data representation and Storage of data.
3. In File, system storing and retrieving of data cannot be done efficiently.
4. 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.
5. File system does not provide crash recovery mechanism. For example, while we are entering some data into the file if System crashes then content of the file is completely lost.
6. 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.

฀ LECTURE TWO -DBMS

฀ (^) Page | 4

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.

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.

฀ A program to debit or credit an account ฀ A program to add a new account ฀ A program to find the balance of an account ฀ A program to generate monthly statements

Programmers wrote these application programs to meet the needs of the bank. New application programs are added to the system as the need arises. For example, suppose that the savings bank decides to offer checking accounts.

As a result, the bank creates new permanent files that contain information about all the checking accounts maintained in the bank, and it may have to write new application programs to deal with situations that do not arise in savings accounts, such as overdrafts. Thus, as time goes by, the system acquires more files and more application programs.

The system stores permanent records in various files, and it needs different Application programs to extract records from, and add records to, the appropriate files. Before database management systems (DBMS) came along, organizations usually stored information in such systems. Organizational information in a file- processing system has a number of major disadvantages :

1. Difficulty in Accessing Data:
2. Data Redundancy and Inconsistency:

฀ LECTURE TWO -DBMS

฀ (^) Page | 5

1. Data Isolation:

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.

1. Atomicity Problems:

all

1. Concurrent-Access Anomalies:

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.

A computer system, like any other mechanical or electrical device, is subject to failure. In many applications, it is important 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

. It is difficult to ensure atomicity in a conventional file-processing system.

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 $ 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 $ 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. However, supervision is difficult to provide

1990s:

฀ LECTURE Three -DBMS^1

1 | Page

Early

In the 1980’s:

History of Database Systems: 1950s and early 1960s: ฀ ฀ ฀ ฀ ฀ ฀ ฀ ฀ ฀ ฀ ฀ ฀ ฀ ฀

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.

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.

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.

฀ LECTURE Three -DBMS^2

Fig: Hierarchical Database System

2 | Page

Late 1990s:

1. File Management System:

The Evolution of Database systems:

1. Hierarchical Database System:

The Evolution of Database systems are as follows:

1. File Management System
2. Hierarchical database System
3. Network Database System
4. Relational Database System

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 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.

The previous system FMS drawback of accessing records and sorting records which took a long

฀ LECTURE Three -DBMS 4

4 | Page

Fig : Relationship in RDBS ฀Advantages of DBMS:

1. Controlling of Redundancy:

programs. Data Integrity: Integrity means that the data in the database is accurate. Centralized control of the data helps in permitting the administrator to define integrity constraints to the data in the database. For example: in customer database, we can enforce an integrity that it must accept the customer only from Noida and Meerut city. Security: Having complete authority over the operational data, enables the DBA in ensuring that the only mean of access to the database is through proper channels. The DBA can define authorization checks to be carried out whenever access to sensitive data is attempted. Data Consistency: By eliminating data redundancy, we greatly reduce the opportunities for

Data redundancy refers to the duplication of data (i.e storing same data multiple times). In a database system, by having a centralized database and centralized control of data by the DBA the unnecessary duplication of data is avoided. It also eliminates the extra time for processing the large volume of data. It results in saving the storage space. Improved Data Sharing: DBMS allows a user to share the data in any number of application

฀ LECTURE Three -DBMS 5

5 | Page

inconsistency. For example: is a customer address is stored only once, we cannot have disagreement on the stored values. Also updating data values is greatly simplified when each value is stored in one place only. Finally, we avoid the wasted storage that results from redundant data storage. Efficient Data Access: In a database system, the data is managed by the DBMS and all access to the data is through the DBMS providing a key to effective data processing Enforcements of Standards: With the centralized of data, DBA can establish and enforce the data standards which may include the naming conventions, data quality standards etc. Data Independence: Ina database system, the database management system provides the interface between the application programs and the data. When changes are made to the data representation, the Meta data obtained by the DBMS is changed but the DBMS is continues to provide the data to application program in the previously used way. The DBMs handles the task of transformation of data wherever necessary. Reduced Application Development and Maintenance Time: DBMS supports many important functions that are common to many applications, accessing data stored in the DBMS, which facilitates the quick development of application.

1. It is bit complex. Since it supports multiple functionality to give the user the best, the underlying software has become complex. The designers and developers should have thorough knowledge about the software to get the most out of it.
2. Because of its complexity and functionality, it uses large amount of memory. It also needs large memory to run efficiently.
3. DBMS system works on the centralized system, i.e.; all the users from all over the world access this database. Hence any failure of the DBMS, will impact all the users.
4. DBMS is generalized software, i.e.; it is written work on the entire systems rather specific one. Hence, some of the application will run slow.

฀ Disadvantages of DBMS:

P a g e

฀ LECTURE FOUR -DBMS

2 |

Although implementation of the simple structures at the logical level may involve complex physical- level structures, the user of the logical level does not need to be aware of this complexity. This is referred to as physical data independence. Database administrators, who must decide what information to keep in the database, use the logical level of abstraction. C) View level (or External View / Schema): The highest level of abstraction describes only part of the entire database. Even though the logical level uses simpler structures, complexity remains because of the variety of information stored in a large database. Many users of the database system do not need all this information; instead, they need to access only a part of the database. The view level of abstraction exists to simplify their interaction with the system. The system may provide many views for the same database. Figure above shows the relationship among the three levels of An analogy to the concept of data types in programming languages may clarify the distinction amongabstraction. levels of abstraction. Many high-level programming languages support the notion of a structured type. For example, we may describe a record as follows:

Instructor = record ID : char (5); name : char (20); dept name : char (20); salary : numeric (8,2); ;

This code defines a new record type called instructor with four fields. Each field has a name and a type associated with it. A university organization may have several such record types, including

• Department , with fields dept_name , building , and budget
• Course , with fields course_id , title , dept_name , and credits
• Student , with fields ID , name , dept_name , and tot_cred

At the physical level, an Instructor , Department , or Student record can be described as a block of consecutive storage locations. The compiler hides this level of detail from programmers. Similarly, the database system hides many of the lowest-level storage details from database

end

type

P a g e

฀ LECTURE FOUR -DBMS

3 |

Instances and Schemas

Databases change over time as information is inserted and/or deleted. T he collection of information stored in the database at a particular moment is called an instance of the database. The overall design of the database is called the database schema. Schemas are changed infrequently, if at all. The concept of database schemas and instances can be understood by analogy to a program written in a programming language. A database schema corresponds to the variable declarations (along with associated type definitions) in a program. Each variable has a particular value at a given instant. The values of the variables in a program at a point in time correspond to an instance of a database schema.

Database systems have several schemas, partitioned according to the levels of abstraction. The physical schema describes the database design at the physical level , while the logical schema describes the database design at the logical level. A database may also have several schemas at the view level, sometimes called subschemas, which describe different views of the database. Of these, the logical schema is by far the most important, in terms of its effect on application programs, since programmers construct applications by using the logical schema. The physical schema is hidden beneath the logical

programmers. Database administrators, on the other hand, may be aware of certain details of the physical organization of the data.

At the logical level, each such record is described by a type definition, as in the previous code segment, and the interrelationship of these record types is defined as well. Programmers using a programming language work at this level of abstraction. Similarly, database administrators usually work at this level of abstraction.

Finally, at the view level, computer users see a set of application programs that hide details of the data types. At the view level, several views of the database are defined, and a database user sees some or all of these views.

In addition to hiding details of the logical level of the database, the views also provide a security mechanism to prevent users from accessing certain parts of the database.

For example, clerks in the university registrar office can see only that part of the database that has information about students; they cannot access information about salaries of instructors.