Fundamental of Database Management System , livre ebook

Fundamentals of Database Management System
Dr. Mukesh Negi
FIRST EDITION 2019
Copyright © BPB Publication, INDIA
ISBN: 978-93-88176-62-0
All Rights Reserved. No part of this publication can be stored in a retrieval system or reproduced in any form or by any means without the prior written permission of the publishers
 
LIMITS OF LIABILITY AND DISCLAIMER OF WARRANTY
The Author and Publisher of this book have tried their best to ensure that the programmes, procedures and functions described in the book are correct. However, the author and the publishers make no warranty of any kind, expressed or implied, with regard to these programmes or the documentation contained in the book. The author and publisher shall not be liable in any event of any damages, incidental or consequential, in connection with, or arising out of the furnishing, performance or use of these programmes, procedures and functions. Product name mentioned are used for identification purposes only and may be trademarks of their respective companies.
All trademarks referred to in the book are acknowledged as properties of their respective owners.
Distributors:
BPB PUBLICATIONS 20, Ansari Road, Darya Ganj New Delhi-110002 Ph: 23254990/23254991
MICRO MEDIA Shop No. 5, Mahendra Chambers, 150 DN Rd. Next to Capital Cinema, V.T. (C.S.T.) Station, MUMBAI-400 001 Ph: 22078296/22078297
BPB PUBLICATIONS 376 Old Lajpat Rai Market, Delhi-110006 Ph: 23861747
DECCAN AGENCIES 4-3-329, Bank Street, Hyderabad-500195 Ph: 24756967/24756400
 
 
 
Published by Manish Jain for BPB Publications, 20, Ansari Road, Darya Ganj, New Delhi-110002 and Printed by Repro India Pvt Ltd, Mumbai
Acknowledgment
Words are not enough when you start writing with feelings on paper. It is difficult to portray endless journey. My sincere thanks to my parents, colleagues, teachers, mentors and all my family members for being supportive throughout the period and encouraging me always to focus on my goal. Special thanks to my wife Priya Negi for appreciating my efforts and being my helping hand always. A successful writing journey can’t be possible without taking numerous feedbacks from different people and by listening to their expectations from a book. Uncountable people were part of my journey where I had received tremendous suggestions and ideas to refine and convert them into words. I would like to thanks all those people who supported and guided me with their great suggestions without revealing their identities. Such people always create a difference in the world.
My deepest thanks to the current Vice-Chancellor of Uttarakhand Residential University and ex-Vice Chancellor of Kumaon University, Nainital, Shri Professor H.S. Dhami for always being a source of my energy with support and energy. Further, I would like to extend and express my gratitude and thankfulness to all of my teachers from Almora Campus (Uttarakhand), RCC Institute (Lucknow), and Government Inter College (Chaukhutia, Ganai), Uttarakhand for nurturing me with so dedication with their knowledge.
Preface
Database and Database Management System is swing around only one critical object, Data. Hence it’s vital to understand the concept of data first. Whether it’s human or machine, we all are the sources of data. That’s mean, knowingly or unknowingly, we all are producing data and some data is generating from the data associated with us. For example, some very generic static data associated with us are, Name, Address, Nationality, Father Name, Mother Name etc. once we go for admission in any college, school or university then they generate some data based on our existing standard data such as Enrollment or Roll Number, Section or Semester, House Name, Admission No, Fee Receipt No etc. These all data are generated based on all of our pre-existing static data. Another important dimension is, the volume of this data, which is increasing day by day. For example, once students promote to next section or semesters their old will also be retained, and new data will also be generating such as newly submitted fee receipt no, semester, subjects, faculty, fee submission date, due fees etc. and many more. Apart from standard text data, we are storing unstructured data as well like photographs, videos, pdf files etc. we call then unstructured data since they have no fixed structure like the name of a person, it’s not easy to analyze unstructured data. Context is, data is all around us, we all, human and machine are generating data, depending on the field or sector the Velocity (How fast data is generating) and Variety (like a test, images, videos etc.) of data would be different. For example, college or university data Velocity and Variety could be slow and different but, if we take the example of an aircraft where hundreds of sensors (like smoke, temperature etc. sensors) are plugged inside aircraft to collect the health of each equipment to alert monitoring team each second to alert about health of state, then it generates Gigabyte of data in an hour. Now, data is there but, what is the use of this data if you are not able to arrange it properly, and how will you analyze, work and take action on data if it’s not arranged in particular order, like the index of a book. Here data and database management system come into the picture, which gives you a framework to store data and organize data so that it can be used of the desired purpose and on desired time. you can’t wait to get admission in a college or university for 2 or 3 months after submitting the registration form (in terms of database management system, after submitting your complete data required for admission). There has to be a process to arrange the data as soon as received, arrange it in proper order such as segregate streamwise forms, shortlist, assign sections, release list etc. and all of this is process through a database management system where data is organized in an efficient manner, so that it can retrieve and process on time. consider another case of Aircraft, its generating Megabytes to Gigabytes in seconds and hours which is being monitored on a real-time basis by traffic health monitoring squad. Now suppose, how powerful system (CPU RAM etc.) required to process such huge data on a real-time basis, and what will happen if real-time data of a particular component with some snag in it will not be alerted on time to the traffic monitoring control system. No system in the world is complete and no use of any system in the world with any data. So before going ahead with to understand and database, it’s very essential first to understand the background of data and then what is database management system, why it is designed, how it is designed and how to create and use an effective database management system. In this book, I am going to explain every concept in simple language and with real-life examples by executing real queries along with showing screenshots. Let’s begin the journey of Data & Database Management System.
Contents
1. Fundamentals of Data and Database Management System
1.1 What is Data?
1.2 Types of Data
1.3 What is a Database?
1.4 What is a Database Management System (DBMS)?
1.5 Advantages of Database
1.6 Disadvantages of DBMS
2. Database Architecture and Models
2.1 Database Architecture
2.2 Two-Tier architecture
2.3 Three Tier Architecture
2.4 Data Flow from External to Internal World
2.5 Database Models
3. Relational Databases and Normalization
Database Keys and Constraints
3.1 Keys
3.2 Need of Key
3.3 Constraints
3.4 Super Key
3.5 Candidate Key
3.6 Composite Key
3.7 Primary Key
3.8 Foreign Key
3.9 Unique constraint
3.10 Not null constraint
3.11 Default Constraint
3.12 Check Constraint
3.13 Database Design (Normalization Rules)
4. Open Source Terminology and Introduction to SQL (Structure Query Language)
4.1 About Oracle, MySQL and Open Source Technology
4.2 Introduction to SQL (Structured Query Language)
4.2.1   DDL: Data Definition Language
4.2.2   DML: Data Manipulation Language
4.2.3   TCL: Transaction Control Language
4.2.4   DCL: Data Control Language
4.3 Data Types
5. Database Queries
5.1 Structured Query Language (SQL)
6. SQL Operators

6.1 SQL Operators
6.1.1 Arithmetic Operators
6.1.2 Character Operators
6.1.3 Comparison Operators
6.1.4 Logical Operators
6.1.5 Set Operators
7. Introduction to Database Joins
7.1 SQL Joins
7.1.1 Inner Join (Also called SIMPLE JOIN)
7.1.2 Left Outer Join (Also called LEFT JOIN)
7.1.3 Right Outer Join (Also called RIGHT JOIN)
7.1.4 Full Outer Join (Also called FULL JOIN)
7.1.5 Inner Join (Simple Join)
7.1.6 Left Outer Join (Left Join)
7.1.7 Right Outer Join (Also called Right Join)
7.1.8 Full Outer Join (Also called Full Join)
8. Aggregate functions, Subqueries and Users
8.1 Aggregate Functions
8.2 SQL Sub Queries
8.3 Database Users
9. Backup & Recovery
9.1 Tablespace, Datafiles, and Control Files
9.2 Oracle SQL Loader
9.3 Database Backup and Recovery
9.4 Recycle Bin
9.5 MYSQL
9.6 High Availability Configuration (Clustering, Load Balancing & Failover)
10. Database Installation
10.1 MySQL 5.7 Installation
10.2 Oracle Express Edition Installation
10.3 Remote Database Server Connectivity
10.4 Oracle SQL PLUS Utility
11. Oracle and MySQL Tools
11.1 SQL Developer (Oracle)
11.2 MySQL Client Tool (Workbench)
11.4 DBeaver (Client for Oracle, MySQL, and Other Databases as well)
11.3 Working with MySQL Workbench
12. Exercise
12.1 SQL Exercise
12.2 Abbreviations
C HAPTER 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