RDBMS In-Depth

Mastering SQL and PL/SQL Concepts, Database Design, ACID Transactions, and Practice Real Implementation of RDBM (English Edition)

Dr. Madhavi Vaidya

• Publisher: BPB Publications
• Year: 2021
• eBook ISBN: 9788194837701
• Topic: Computer Science
• Subtopic: Desktop Applications
• Index: Computer Science

Understanding and implementing the database management systems concepts in SQL and PL/SQL

Key Features

• Practice SQL concepts by writing queries and perform your own data visualization and analysis.
• Gain insights on Entity Relationship Model and how to implement in your business environment.
• Series of question banks and case-studies to develop strong hold on RDBMS concepts.

Description
Relational Database Management Systems In-Depth brings the fundamental concepts of database management systems to you in more elaborated learning with conceptual clarity of RDBMS. This book brings an extensive coverage of theoretical concepts on types of databases, concepts of relational database management systems, normalization and many more. You will explore exemplification of Entity Relational Model concepts that would teach the readers to design accurate business systems. Backed with a series of examples, you can practice the fundamental concepts of RDBMS and SQL queries including Oracle's SQL queries, MySQL and SQL Server.In addition to the illustration of concepts on SQL, there is an implementation of crucial business rules using PL/SQL based stored procedures and database triggers.Finally, by the end of this book there is a mention of the useful data oriented technologies like Big Data, Data Lake etc and the crucial role played by such techniques in the current data driven decisions.Throughout the book, you will come across key learnings and key terms that will help you to understand and revise the concepts learned. Along with this, you will also come across questions and case studies by the end of every chapter to prepare for job interviews and certifications.

What you will learn

• Depiction of Entity Relationship Model with various business case studies.
• Illustration of the normalization concept to make the database stronger and consistent.
• Designing the successful client-server applications using PL/SQL concepts.
• Learning the concepts of OODBS and Database Design with Normalization and Relationships.

Who this book is for
This book is meant for academicians, students, developers and administrators including beginners and readers experienced in some other programming languages and database systems.

Table of Contents
1. Database Systems Architecture
2. Database Management System Models
3. Relational query languages
4. Relational Database Design
5. Query Processing and Optimization
6. Transaction Processing
7. Implementation Techniques
8. SQL Concepts
9. PL/SQL Concepts
10. Collections in PL/SQL
11. What Next?

About the Author
Dr. Madhavi Vaidya is an experienced and qualified Assistant Professor with a demonstrated history of working in the education management industry. Skilled in programming languages like C, Python, SQL, Oracle Database, Dr. Madhavi has understanding and knowledge of Data Analytics, Information Retrieval, Software Engineering and Project Management.She has strong education professional with a Master of Computer Applications and Doctor of Philosophy in the subject of Computer Science and Engineering. One of the key areas of her research is Big Data analytics using Hadoop-MapReduce and various Big Data technologies. She is a Content Writer @Udemy and various other online courses are in her credit including BPB publications.Ample research papers are in her credit, presented and published in various National and International conference along with research chapters and articles in ACM, IEEE, Elsevier and Developer IQ magazine. Reviewing articles and research papers- Acting as a reviewer for the journals like IEEE Access, ASSA, IGI Global and many other prestigious journals. LinkedIn Profile: https://www.linkedin.com/in/dr-madhavi-v-01882327

CHAPTER 1

Database Systems Architecture

Introduction

This chapter explains the need for database management systems over the file management system. A database management system is a collection of interrelated data. One of the main advantages of using a database system is that the organization of data can be exercised, such as, via the DBA (database administrator), a software used for centralized management and control over the data.

SQL (structured query language) allows us to write a query and then execute on the client server machine where SQL is installed on server side, and the queries are fired from client machine.

The most common use of relational database management system (RDBMS) is to implement simple CRUD operation which allows to perform create, read, update, and delete tasks. Relational model allows two or more than two entities connected to each other. We will learn about the advantages of relational model by the end of this chapter, and various terms used in relational model in the later part of this book.

Structure

• Introduction
• Purpose of database system
• Network architecture
• Views of data
• Database system architecture
• Data models
• Conclusion
• Questions

Objectives

After studying this unit, you should be able to:

1. Understand the need for database management system (DBMS)
2. Discuss the use of DBMS over file system
3. Know the data models, like hierarchical, network, and relational models
4. Identify the relational model along with the advantages

Data storage is very important as it protects and retrieves the required data whenever a kit is needed. Data is a key asset for businesses, so the need for data storage is important. A set of programs are used to access the data.

A DBMS is a collection of interrelated data. It provides users and programmers with a systematic way to create, retrieve, update, and manage data. These are systems that can be used to manage transactional databases, such as HR systems, banking systems, payroll systems, and so on.

Database is a collection of inter-related data which helps in efficient retrieval, insertion, and deletion of data from database and organizes the data in the form of tables, views, schemas, reports, and so on. For example, an examination management system which organizes the data on students, subjects, semesters information and the paper details. Database contains information about a particular enterprise. It provides an environment that is both convenient and efficient to use.

Database management system

A DBMS is a software system that allows to access data situated in databases. The objective of DBMS is to provide a convenient and effective method of defining, storing, and retrieving the information contained in the database.

The number of database applications which are available are as follows:

• Banking: Customers, accounts, loan, transactions
• Airlines: Reservations, schedules
• Universities: Registration, grades
• Sales: Customers, products, purchases
• Manufacturing: Production, inventory, orders, supply chain
• Human resources: Employee records, salaries, tax deductions

Customer Table:

CustNum	CustName	Address
1	A	Mumbai
2	B	Delhi
3	C	Jaipur

Table 1.1: Customer File

Orders Table:

OrderNum	OrderDate	Amount
101	01-01-2009	12340
102	02-02-2009	20075
103	13-06-2009	9740

Table 1.2: Orders File

Although, this seems to be a working solution, the information does not store the relation regarding which order belongs to which customer, and how many orders are placed by each customer. For doing this, one would be duplicating all the information of customers into orders table or vice versa.

For example, the excel sheet or data file would look like the following table 1.3:

OrderNum	OrderDate	Amount	CustNum	CustName	Addr
101	01-01-2009	12340	1	A	Mumbai
102	02-02-2009	20075	2	B	Delhi
103	13-06-2009	9740	1	A	Mumbai

Table 1.3: Cust_orders File

As we can see in the preceding table, the data duplication, where custnum is repeated, and if the same customer gives many orders, then the customers’ data would be repeated that many times.

This redundancy would create the inconsistencies, which is an important aspect in keeping data properly.

There are a few other advantages, which are as follows:

• If the customer with custnum 1 shifts to Indore, then this change should be done not only in customer’s file but in all the records of Orders file. This can be a difficult task, since otherwise data will be inconsistent.
• CustNum & OrderNum identify a particular customer or order. While giving these numbers, there are chances that the same number is given to another customer or order. There are chances that a user may delete the customer’s record from the customer’s file when his/her orders are pending.
• If the same file is accessed by many users – the administrative office and the proprietor – then the proprietor as well as office person can modify it. It is difficult to set read/write/modify permissions for owner and read permissions for clerk.
• One more important point is to check whether the values entered under columns, such as order_amt < 0 or customer address may be entered as blank too.

Now, we will try to understand the limitations of the file system by looking at the following points:

• Difficulty in accessing data: Suppose one wants to find the data of those orders which were placed on 1st January 2009, and the order amount>10000 in such cases. A conventional file processing environment does not allow the needed data to be retrieved in a convenient and efficient manner. This can be done easily by using database’s management system.
• Atomicity of updates: Failures may leave database in an inconsistent state with partial updates carried out. For example, transfer of funds from one account to another should either complete or not happen at all.
• Simultaneous (concurrent) access by multiple users: Uncontrolled concurrent accesses can lead to inconsistencies. For example, the orders placed by the proprietor and/or the administrative staff; both would not come to know which order was placed at what time, and how many items were there in balance or in store.
• Supervision and regulations: On data it can be difficult since many application programs should be coordinated.
• Security problems: Enforcing security constraints could be difficult in an ad-hoc manner.

Advantages of DBMS

One of the main advantages of using a database system is that the organization can exercise, via the DBA, centralized management and regulations over the data. The database administrator is the focus of the centralized control.

The advantages of the database management system are as follows:

• Reduction of redundancies: Centralized control of the data by the DBA avoids unnecessary duplication of data. DBA reduces the total amount of data storage required. In this manner, the redundancies that exist are controlled and the system ensures that these multiple copies are consistent.
• Reduction in keeping number of files: When the same data is stored in a number of files, it brings in data duplication. In such cases, if the data is modified at one place, the data would be copied in each of the files. This happens as follows:
  • Storage space gets wasted.
  • Processing time may be wasted as more data is to be handled.
  • Inconsistencies may be created.
  • Shared data: Sharing of data is allowed by any number of application programs or users.
  • Integrity: Data integrity means that the data contained in the database is both accurate and consistent. Data values being entered are checked to ensure that they fall within a specified range, and are of the correct format.
  • Security: Data is very important for an organization and it should be kept confidential. Any unauthorized person must not access such confidential data. The DBA, who has the ultimate responsibility to take care of the data in DBMS, can ensure the...

Table of contents

• Cover Page
• Title Page
• Copyright Page
• Dedication Page
• About the Author
• About the Reviewers
• Acknowledgement
• Preface
• Errata
• Table of Contents
• 1. Database Systems Architecture
• 2. Database Management System Models
• 3. Relational Query Languages
• 4. Relational Database Design
• 5. Query Processing and Optimization
• 6. Transaction Processing
• 7. Implementation Techniques
• 8. SQL Concepts
• 9. PL/SQL Concepts
• 10. Collections in PL/SQL
• 11. What’s Next?
• Index