Introduction to DBMS

Designing and Implementing Databases from Scratch for Absolute Beginners

Dr. Hariram Chavan, Prof. Sana Shaikh

• Éditeur: BPB Publications
• Année: 2022
• ISBN:
  9789355510266
• Sujet: Computer Science
• Sous-sujet: Desktop Applications

Database and I: A unified view of the Database

Key Features
? Explains database fundamentals by using examples from the actual world.
? Extensive hands-on practice demonstrating SQL topics using MySQL standards.
? All-inclusive coverage for systematic reading and self-study.

Description
The knowledge of Database Management Systems (DBMS) has become a de facto necessity for every business user. Understanding various databases and how it becomes an integral part of any application has been a popular curriculum for undergraduates.In this book, you will learn about database design and how to build one. It has six chapters meant to bridge the gap between theory and legit implementation. Concepts and architecture, Entity-relation model, Relational model, Structured Query Language, Relational database design, and transaction management are covered in the book. The ER and relational models are demonstrated using a database system from an engineering college and implemented using the MySQL standard. The final chapter explains transaction management, concurrency, and recovery methods. The final chapter explains transaction management, concurrency, and recovery methods.With a straightforward language and a student-centered approach, this book provides hands-on experience with MySQL implementation. It will be beneficial as a textbook for undergraduate students, and database specialists in their professional capacity may also use it.

What you will learn
? Acquire a firm grasp of the principles of data and database management systems.
? Outlines the whole development and implementation process for databases.
? Learn how to follow step-by-step normalization rules and keep your data clean.
? MySQL operations such as DDL, DML, DCL, TCL, and embedded queries are performed.
? Develop an understanding of how the transaction management and recovery system operates.

Who this book is for
This book is ideal for anyone who is interested in learning more about Database Management Systems, whether they are undergraduate students, new database developers, or with some expertise. Programming foundations, file system ideas, and discrete structure concepts are recommended but not required.

Table of Contents
1. Database System Concepts and Architecture
2. The Entity-Relationship Model
3. Relational Model and Relational Algebra
4. Structured Query Language and Indexing
5. Relational Database Design
6. Transactions Management and Concurrency and Recovery

CHAPTER 1

Database System Concepts and Architecture

Edgar Codd
(23 August 1923 - April 18, 2003)

We all must be thankful for the efforts of Edgar (Ted) Frank Codd, the Royal Air Force pilot. He was the pilot who created the theories of data management based on the mathematical set theory. His idea of data storage using a simple table structure bought a technological revolution. “A Relational Model of Data for Large Shared Data Banks”, the landmark publication of Codd talks about representation, abstraction, relation, and normal form of data.

We are living in the digital era where the word ‘data’ has platinum importance. It became an integral part of our daily lives. The organization which acquires timely accurate data may rule the world. Data processing has changed from what happened, why it happened, and what will happen, to the most recent stage, can we make it happen. This is the power of data. So, in this chapter, we will focus on data and the related terms.

Introduction

Data, database, and database technology have played a critical role in every field. It has a major impact on the growth of every business. By data, we mean “raw facts”. Here “raw” means unprocessed. The dictionary meaning of “fact” is “a thing that is known or proved to be true”. In other words, data carries some inherent meaning associated with it and can be revealed by processing.

Data: Raw facts

The following are some raw facts on data:

• Data means known truth about anything which exists in the real world that can be stored for future use and also has some implicit meaning.
• Data is raw, unorganized facts that need to be processed. Data can be something simple and seemingly random and useless until it is organized.
• For example, the Test scores of a student, Enrolment Number, Email ID, and so on.

Structure

In this chapter, we will cover the following topics:

• Difference between data and information
• Understanding the basic concepts of database systems
• Issues with the file-based system
• Major components of the database management system and their functions
• Understanding the role of the database users and database administrator

Objectives

After studying this chapter, you will be able to understand data, information, database, and DBMS, differentiate between data and information, and outline why the database design is important. You will also be able to list the flaws of the file system, recall the main components of the database system with their functions, state the role of the database users, and database administrator.

Information: Processed data

This is the technical definition of the information. But in reality, what is information? To understand the meaning of information, we will consider a few examples. Let us consider that you are speaking with your friend and the dialog is “It is raining here”. This statement carries information if the season is other than rainy. Since, if the season is rainy, then rainfall is expected and does not carry any information. Instead, if the statement is something like “It is raining heavily”, then it may carry some information considering the current scenario of rainfall in India. One more example to understand information is the human interest in the uneven happening and the news based on that. For example, hundreds of planes take off safely or land safely every day. But it never became a piece of news. The moment there is a problem during take-off or landing, it becomes a piece of news as it carries information. This means that information is something new that you will get after processing the data or occurrence of an event.

The following are some more facts on information:

• Data can be converted into a meaningful and useful context. Later, it can be communicated to a recipient who uses it to make the decisions.
• When the data is organized, processed, and presented in a given context, it becomes information.
• For example, the subject teacher can process the test scores of the students, and classify them into weak and bright students.

Database

A database is a collection of interrelated data.

Now, answer the following questions in the YES/NO form:

• How many of you have recorded friends’ birthdays in a diary?
• How many of you have written colleagues’ addresses into an address book?
• How many of you have referred to a dictionary for new words?

If you have answered YES to any of these questions, then you have already used a paper-based database in your daily life, where the following happened:

• Birthdays were organized in the logical order of 'Month'.
• Addresses were stored in a logical order of 'Name'.
• English words with their meaning are arranged in alphabetical order.

These were examples of the paper-based databases. However, when we use the term 'database', we generally think of a computerized database. Actually, without realizing, we are all using databases almost every day in our life.

Refer to the following as examples:

• Google Search Engine
• ATM
• Online food ordering
• Online shopping
• Online booking flight

Computerized versus paper-based databases

What is it that makes the computerized databases much more popular than the paper-based ones?

Refer to Table 1.1 as follows:

Table 1.1: Computerized vs. paper-based databases

Database Management System (DBMS)

The database management system is a collection of control routines (software package) that manages the data functionality, mainly for easy storage and easy retrieval. In reality, it does many other important tasks from the creation of a database to the maintenance of a database which in turn includes the following:

• Data definition: It involves specifying the data types, data structures, and constraints of the data to be stored.
• Data storage: Storing data mostly on secondary storage (or server) which is controlled by DBMS.
• Data manipulation: The three main informal operations are add, modify, and delete; technically inserting, querying, updating and generating reports.
• Concurrent access: The main advantage of the database management systems is concurrent access or data sharing which allows multiple users and programs to access the data concurrently.
• Data protection: The DBMS provides protection against the unauthorized access, system failure, and other catastrophic failures.

Characteristics of databases

The database has many interesting features which makes it more suitable and useful than the file systems. Before the database system, the common or traditional approach to manage the data was the file system. But as we know that the file system has many limitations, we switched to the database systems to avail the benefits of it. To understand the limitations of the file systems, let us consider a simple example.

Let us assume that the engineering college does not have a centralized database system that maintains the enrolled students. In an engineering college, a student enrolls through a centralized admission process. The Director of Technical Education (DTE) gives a list of students who have registered themselves for a particular program in a particular engineering college. Also, take into consideration that this list is in an Excel format. Now, consider that the administration office (Accounts department) maintains this list for fee collection in tally software. Then, this data will be given to the first-year engineering department/humanities department. The department maintains the list in an Excel format as well. In the second year, the student’s data will be maintained by individual departments. Now, let us assume that the IT department uses MS Access to create and maintain the student’s data. The computer department uses Oracle for the same purpose. According to this scenario, the same database is available in various formats in different departments.

Now, if any student changes their mobile number, then it has to be reflected in at least three different files. That is in the administration office (Accounts department), humanity department, the actual department, and Training & Placement department, etc. If any one of the files is not updated, then the data becomes inconsistent. To add to the complexity, assume that the Training & Placement department maintains the student data for different companies based on company requirements and they have their own tailor-made software which generates the student report based on the companies’ criteria. As we have considered the different file systems, it will increase the inconsistency with each data change which is not reflected at all places/files. If you consider that the phone number, email address, and student address might frequently change, with every field, the data inconsistency increases.

Other than data inconsistency, another major problem that arises in case of a separate file system is the data structure that holds the data and the naming system used by different file systems (i.e., creator of the file). In the preceding example, let us consider the phone number field of the student database. In the administration office (Accounts department), the field name is mobile number, in the Information Technology department, it has been referred to as contact number. The Computer department stores both landline and mobile number under the heads land and hand respectively. Now, if the information about these fields is not maintained, then again it will create confusion. So, to limit the data inconsistency, to avoid misperception, to maintain the uniformity, we must maintain the data at one place and make it available to all those who would like to access it, and for this, we require a database management system.

In the case of database management systems, it maintains a single repository and will be made available to all users. In the preceding case, the administration department (Accounts department), the humanities department, the individual department, and the Training & Placement cell. If there is any change in any field, then it will be made in one place and will be available for all the users, so that there is no possibility of inconsistency and misinterpretation of the data.

The main characteristics of the database approach are as follows:

• Self-describing nature of a database system
• Programs and data insulation
• Mult...