Fundamentals of Database Management System

Learn essential concepts of database systems

Dr. Mukesh Chandra Negi, Dr. Mukesh Negi

• Publisher: BPB Publications
• Year: 2019
• eBook ISBN: 9789388176620
• Topic: Informatik
• Subtopic: Data Mining

Designed to provide an insight into the database concepts Key Features

• Book contains real-time executed commands along with screenshot
• Parallel execution and explanation of Oracle and MySQL Database commands
• A Single comprehensive guide for Students, Teachers and Professionals
• Practical oriented book

Description
Book teaches the essentials of DBMS to anyone who wants to become an effective and independent DBMS Master. It covers all the DBMS fundamentals without forgetting few vital advanced topics such as from installation, configuration and monitoring, up to the backup and migration of database covering few database client tools. What Will You Learn

• Relational Database, Keys
• Normalization of database
• SQL, SQL Queries, SQL joins
• Aggregate Functions, Oracle and Mysql tools

Who This Book Is For

• Students of Polytechnic Diploma Classes- Computer Science/ Information Technology
• Graduate Students- Computer Science/ CSE / IT/ Computer Applications
• Master Class Students—Msc (CS/IT)/ MCA/ M.Phil, M.Tech, M.S.
• Industry Professionals- Preparing for Certifications
• 
  Table of Contents
• Fundamentals of data and Database management system
• Database Architecture and Models
• Relational Database and normalization
• Open source technology & SQL
• Database queries
• SQL operators
• Introduction to database joins
• Aggregate functions, subqueries and users
• Backup & Recovery
• Database installation
• Oracle and MYSQL tools
• Exercise
• About the Author
  Dr. Mukesh Negi is an Oracle, IBM, ITIL & Prince2 Certified Engineer with more than sixteen years of experience in multiple Advance and Emerging IT Technologies such as DBMS & Big Data, Cloud Computing, Virtualization, Internet of Things, Artificial Intelligence, Machine Learning, Business Intelligence & Analytics, IT Security etc. In the Education field, He is serving as an Editorial Board Member of many international journals. He has conducted several Faculty Development Programs and serving as a Guest & Visiting Faculty in many reputed University and Colleges in India.
  

CHAPTER 1

Fundamentals of Data and Database Management System

Introduction to Database

1.1 What is Data?

Data is a very basic unit or object we all deal with, in our daily life. A very generic definition of Data is, it’s a collection of raw facts and figures such as any information about human beings, machines, measurements, calculations, words, observations or description of any object existing in the world. Sometimes, it’s just a raw data and sometimes it is processed data. Processed data is also called information and unprocessed data is called raw data. There is no use of data until you will convert it into information that means until you will convert unprocessed data into processed data. It’s a very basic principle, proper arrangement of data gives you information, information gives you knowledge and knowledge gives you wisdom. So, when we talk about data, then it can be your name, gender, address, contact no, father name, mother name, name of a place, city, village, model of car, organization name, departments etc. and we will get information, once we will arrange all this information in a pattern. For example, write the first name in a single line, second name in front of first name in corresponding parallel line, and then third name in front of second name in another corresponding parallel line. This will give you proper information about the first, second and last name of all.

Let’s try to understand with a very basic example. Assume a University database with students and faculty records. There could be multiple students in multiple streams and multiple faculties in multiple streams. University collects all standard basic static (seldom changed) information of students as well as faculty, such as name, age, gender, address, phone number, date of birth etc. and apart from that generate and maintain other university related dynamic details, such as student enrollment number, semester, paid fees, due fees, date of joining, faculty ID (employee ID), salary, department etc. Now, let assume that if all students and faculty data is collected but not organized, then what will happen. We have complete data with us, but until we will not organize all data in the proper manner, there is no use of that. You wouldn’t be able to identify if Amit is a student or faculty. It’s just like you have mixed crystal of sugar in a water glass and then giving a thought to separate them again. To convert all students and faculty data into information, you have to arrange all the data in a proper manner, so that anyone can understand and interpret it easily. For example, you will create a stream and semester wise students register, department wise faculty register and then you will write corresponding students and faculties information in it with proper indexing. This is called arrangement of raw data and now, we can say data is converted into information. Whoever will read this arranged information will get knowledge and knowledge will give him the wisdom to identify each and every student and faculty individually. Please refer the following to understand data with a few more examples.

Examples

• Students admission data (Static Data) Multiple students fill registration form online when getting admission to a college. This form contains multiple raw objects, such as student name, mother’s name, father’s name, the address of the student, contact no, photograph, date of birth etc. called data. Once submitted, this data is electronically processed and saved in the university database, under relevant sections in an arranged manner so that it can be accessed and processed efficiently whoever required.
• Data collected from survey Different organizations conduct online as well as door to door surveys to collect feedback in the form of data, to know the opinion of people about their products and service satisfaction level. They also collect data about their competitors in a particular area from the market to prepare a competitive business strategy. All these data organized and analyzed to identify different patterns to increase end-user experience and satisfaction level.
• Dynamic data of students Dynamic data of students, maintained by the university after permanent enrollment of students, such as enrollment no, roll no, paid fee, paid date, due fee, due date, current semester, assessment, and previous semester marks etc.
• Dynamic data of faculty Like students, after recruitment of faculty, the university maintained various dynamic data of faculty, such as faculty id, salary, department, subjects he teaches, date of Joining etc.

1.2 Types of Data

There are two general types of data:

• Qualitative Qualitative data describe qualities and you cannot measure it. It’s all about the perceptions feelings and emotions of people. For example, collecting the satisfaction level of customers from the survey.
• Quantitative Quantitative data describe quantities and you can measure it. It can be further divided into discrete and continuous, where discrete can take certain values and continuous can take any value within a range.
  Please refer the following figure to understand with examples:

Types of Data

1.3 What is a Database?

A database is a collection of raw objects or information, organized in a systematic way so that it can be retrieved or manipulated easily and efficiently, as and when required. In the database, data is organized into rows, columns, and tables, and indexed to make retrieval faster. Data gets added, updated, or deleted as and when required with the help of database standard commands. Take the example of a University database with students and faculties data as mentioned in the data definition section. All collected date of students and faculties will be stored in tables in a well-organized manner as mentioned in the figure below, so whenever required, it can be retrieved for reporting and manipulation efficiently.

Following is a database table, where some data of students exists in rows and columns.

Database Table

1.4 What is a Database Management System (DBMS)?

Database Management System (DBMS) is a coalitioisn of two words, Database & Management System. That means, It’s a collection of Data and Management System, where Data is defined in the previous section, and Management System is a software or set of programs, used to manage (Save and Manipulate) data easily. The overall purpose of the Database Management System is to design a centralized system to manage data effectively and efficiently. Centralized mean, instead of maintaining multiple copies of same data at multiple places, consolidate it at only one centralize place (to reduce duplicity and maintenance complexities) and then allow access to intended recipients. For example, instead of maintaining the same student’s records in different registers in a different department of a university or college, consolidated at a single place and then allow access to all departments.

Some of the leading DBMS are:

• MySQL (Open Source, From Oracle)
• Oracle (Proprietary Software from Oracle)
• MS SQL Server (Proprietary Software from Microsoft)
• IBM DB2 (Proprietary Software from IBM)
• PostgreSQL (Open Source Database)
• MariaDB (Open Source Database based on MySQL)
• SQLite (Open Source)

Note:

To know the difference between open source and proprietary software, refer to section 8, About Oracle, MySQL, and open source technology.

Advantages and Disadvantages of Database Management System

1.5 Advantages of Database

• Concurrent use of database

DBMS allows multiple users to work at the same time. As I have explained above, instead of maintaining the same student’s data at multiple places, centralize it at a single place, and then allow access to all intended users from there. So there could be a moment when multiple people from different departments accessing the same student data at the same time. For example, sometimes accounting department may be accessing student X data to check due to fees and at same time computer science department accessing same student’s data, to check his last assessment score or maybe to update.

• Support ACID properties

DBMS support ACID (Atomicity, Consistency, Durability, and Isolation) properties. Atomicity means, when you are doing any transaction on the database, then either it should complete to the full extent or if failed, then failed to the full extent as well. For example, if account department is updating fees deposit status of some student, and database mark status at two places as paid, then during the transaction when database is updating the flag as paid at both places, either both paces should be updated as paid or if transaction is failed due to any reason, then both places should remain in previous state as before initiation of transaction. There shouldn’t be a case when one of the flag updates as paid and other remains with old status as not paid. Similarly, Consistency means, the database should maintain the consistency of data during failure of any transaction. If any transaction, updating multiple data at multiple places in a single transaction got failed, then all data should be the same as it was before initiation of transition. There shouldn’t be any case when during the transaction, data updated at some places and failed to update at other intended places. It will make da...

Table of contents

• Cover
• Fundamentals of Database Management System
• Copyright
• Acknowledgement
• Preface
• Table of Contents
• toc.xhtml
• 2. Database Architecture and Models
• 3. Relational Databases and Normalization
• 4. Open Source Terminology and Introduction to SQL (Structure Query Language)
• 5. Database Queries
• SQL Operators
• 7. Introduction to Database Joins
• 8. Aggregate functions, Subqueries and Users
• 9. Backup & Recovery
• 10. Database Installation
• 11. Oracle and MySQL Tools
• 12. Exercise