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

By Dr. Madhavi Vaidya

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.

• Language: English
• Publisher: BPB Online LLP
• Release date: Feb 25, 2021
• ISBN: 9788194837718

Book preview

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:

Understand the need for database management system (DBMS)

Discuss the use of DBMS over file system

Know the data models, like hierarchical, network, and relational models

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:

Table 1.1: Customer File

Orders Table:

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:

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 proper authentication schemes for access to the DBMS. Extra care is taken when the access is permitted for using the sensitive data.

Maintaining relationships: Relationships are easily maintained in RDBMS, using mapping cardinalities in databases. There is a concept of integrity constraint that has one of the important constraints as primary key created in one entity (table), because of which, the values can be entered as unique, but it should not be null. Their foreign key, a referential integrity constraint, is created in another relation (table), where only the values that are entered in the primary key column of the table are allowed. Because of this, the integrity and consistency in the tables are maintained.

After reading and understanding about the disadvantages of file systems and advantages of DBMS, we will try to understand the RDBMS.

Introduction to RDBMS

RDBMS is a terminology or concept that is used to provide the facility to store and retrieve data to the user, which is interrelated with any other relation, called as the Relational model.

A Relation in relational database is based on relational scheme, which consists of a number of attributes. A relational database is made up of a number of relations and relational database scheme.

The users are allowed to access and manipulate the data contained in the database in a convenient and operative manner. In addition, the DBMS exercises centralized control of the database, prevents unauthorized users from accessing the data, and ensures the privacy of the data as we have already learnt and understood.

The relational model has three components.

A structural component to build the model, which consists of the following:

Relation

Attributes (columns)

Tuples (rows)

Integrity constraints, which defines the operational rules of the data.

Manipulation component, which is the component to manipulate the structure.

Structured query language (SQL) allows us to write a query and then execute either on the client server machine where SQL is installed on server side, and the queries are fired from client machine; or, they can be executed from individual machines. In this book, we are going to refer to the SQL of Oracle software.

SQL * Plus is also an extension to the standard SQL, and it has an on-line command interpreter. The users can create program files, and generate formatted reports. It is an interactive tool, an Oracle client provided by the company, Oracle Corporation. Other examples are MS-SQL Server, MySQL Server from Microsoft Corporation Ltd.

The need for RDBMS over DBMS can be discussed using the following points:

A DBMS has to be persistent, that is it should be accessible when the program created the data ceases to exist or even the application that created the data restarted. A DBMS also has to provide some uniform methods independent of a specific application for accessing the information that is stored.

RDBMS is a relational database management system or relational DBMS. This adds the additional condition that the system supports a tabular structure for the data, with required relationships between the tables.

DBMS does not impose any constraints or security with regard to data manipulation, and it is the user’s or the programmer’s responsibility to ensure the ACID property. RDBMS defines the integrity constraint for the purpose of holding ACID property. ACID property corresponds to A C I D, they are nothing but Atomicity, Consistency, Isolation, and Durability.

A relational database is a collection of relations with distinct relation names. The relational database schema is the collection of schemas for the relations in the database. An instance of a relational database is a collection of relation instances, one per relation schema in the database schema.

The SQL language

SQL or Structured English like Query Language (SeQUEL).

It is a tool for organizing, managing, and retrieving the data stored by a database. When one needs to retrieve the data from database, one can use the SQL language to make the request. The DBMS processes SQL request, retrieves the requested data, and it is sent back to the user.

The process of requesting data from the database, and receiving the results back is known as a database query; hence the name, structured query language.

As networks are more common, applications that traditionally ran on a mini-computer moved to local area network of desktop workstations and servers. In these networks, SQL plays an important role as the link between an application running on a server and the DBMS that manages the shared data on a cost-effective manner. The database network architectures, and the role of SQL are explained in the following section.

Network architectures

The DBMS design depends upon the architecture used. The basic client/server architecture is used to deal with many PCs, web servers, database servers, and other components that are connected using networks. The client/server architecture consists of many PCs, and a workstation which are connected via the network.

Network architecture is of various types, which are as follows:

Centralized architecture

Client server architecture

Multi-tier architecture

Centralized architecture

In this architecture, the DBMS and the physical data both reside on a central minicomputer or mainframe system, along with the application program that accepts input from the user’s terminal, and displays the data on the user’s screen. The application program communicates with the DBMS using SQL. Take a look at the following figure 1.1:

Figure 1.1: Centralized Architecture

Client server architecture

In this scheme, the personal computers are combined in a local area network with a database server that stores shared databases. The functions of DBMS are split into two parts. Database Front ends, such as query tools which are interactive in nature, report writers and various application programs that get executed on the personal computers or desktops. The back-end database engine that stores and manages the data runs on the server. In the client server architecture, the query, an SQL request travels across the network to the database server. The database engine on the server processes the request, and scans the database, which resides on the server. When the result is calculated, the database engine sends it back across the network as a single reply to the initial request, and the front-end application displays it on the desktop screen.

The advantage of the client server architecture is that it reduces the network traffic and splits the database workload. Client-server architecture is a two-tier architecture that improves usability and scalability. Usability is increased though user-friendly, and form-based interfaces. Scalability is improved, as and when many machines can be added. Take a look at the following figure 1.2:

Figure 1.2: Client Server Architecture

Multi-tier architecture

With the emergence of Internet and WWW, the web used to browse the static documents and get accustomed with the outside world. For example, suppose a company starts using the Web to provide the product information to the customers by making product description and graphics available on its website or an app – it can be an Android app or Webapp, and so on.

Multitier client server architecture is also known as three-tier architecture and indicates the introduction of a middle tier to mediate between clients and servers. The middle tier exists between the user interface on the client side and DBMS on the server side. This third layer executes process management, which includes implementation of business logic and rules. The three tier models can accommodate several users. It hides the complexity of process distribution from the user, while being able to complete complex tasks through message queuing, application implementation, and data staging or the storage of data before being uploaded to the data warehouse. The middle tier is also called the application server. It contains a centralized processing logic, which facilitates management and administration.

Figure 1.3: Views of Data

Views of data

A DBMS is a collection of interrelated files and a set of programs that allow users to access and modify these files. A major purpose of database systems is to provide the users an abstract view of data.

There is a three-level architecture defined to understand the views of data; they are as follows:

External level

Conceptual level

Internal level

The view at each of these levels is described as scheme or schema.

Take a look at the following figure 1.4:

Figure 1.4: View of Data

Conceptual view or logical view or logical level

One conceptual view represents the entire database. The conceptual schema defines this conceptual view. It describes all the records and relationships included in the conceptual view, and indirectly in the database. It can also be described as relations stored in the database. Assume the following collection of entities (table in SQL) and relationships such as:

customers (customer_id, cust_name, cust_contact)

orders(order_id, order_date, order_status)

External or user view or view level

The external or user view is at the highest level of database abstraction where only the data view portions of the database is of a concern to a user or in the application program are included. Any number of user views may exist for a given global or conceptual view. The objects include entities, attributes, and relationships. Each external schema consists of a collection of one or more views, and relations from the conceptual schema.

Physical view or internal view or physical level

The physical schema specifies additional storage details. Physical schema summarizes how the relations described in the conceptual schema are actually stored on secondary storage devices. It contains the definition of the stored record, the method of representing data fields, and the access aids used.

Data independence

Three levels of abstraction, along with the mappings from internal to conceptual, and from conceptual to external, provide two distinct levels of data independence – logical, and physical data independence. Physical data independence allows changes in the physical storage devices or organization of the files to be made. Logical data independence implies that application programs need not be changed if fields are added to an existing record; nor do they have to be changed if fields that are not used by application programs are deleted.

Structure of database management systems

The DBMS accepts SQL commands generated from a variety of user interfaces, produces query evaluation plans (client fires the query), executes these plans against the database, and returns the answers.

Components of DBMS

Data definition language compiler

The Data definition language (DDL) compiler converts the data definition statements into a set of various tables. These tables contain the metadata (data about data) concerning the database, and are in a form that can be used by the other components of the DBMS.

Let’s take a look at the following figure 1.5:

Figure 1.5: Overall System Structure

Query processor

The database user retrieves the data by formulating a query in the data manipulation language provided with the database. The Query Processor is used to interpret the user’s query and convert it into series of operations in a form capable of being sent to the data manager for execution.

Data definition language compiler

It converts the data definition statements into a set of tables. These tables contain the metadata concerning the database and are in the form used by other components of the database.

Data manager

The data manager is responsible for interfacing with the file system. The data manager also performs the tasks of enforcing the constraints to maintain the consistency and integrity of the data as well as its security. One of the essential functions of the data manager is to convert operations coming from an application program from the user’s logical view to a physical file system. It is also responsible for backup and recovery operations.

File manager

File manager manages the file space and responsibility of handling the files.

Disk manager

The disk manager is the part of the operating system of the host computer, and all physical input and output operations are performed by the disk manager. It takes care of storing the data on