SEMESTER V UNIT I

RELATIONAL DATABASE MANAGEMENT SYSTEMS

UNIT- I : DATABASE ARCHITECTURE AND ER DIAGRAM 12 Hours

Database system applications - Purpose of database systems - View of data-
Database languages –Relational Databases-Database Design – Data Storage and
querying Transaction Management-Data Mining and analysis-Database
architecture-Database Users and Administrators- History of database systems

Introduction

A database is a collection of data, contains information relevant to an enterprise.

A database-management system (DBMS) is a collection of interrelated data and a set of
programs to access those data. The primary goal of a DBMS is to provide a way to store and
retrieve database information that is both convenient and efficient.

Advantages of DBMS

1. Controlling Redundancy
2. Data Sharing
3. Data Consistency
4. Data Integration
5. Data Security
6. Data Atomicity
7. Concurrency Control

Disadvantages of DBMS

1. Costly
2. Complexity
3. Database Failure
4. Large in Size
5. Performance

Popular DBMS Software

Here is the list of some popular DBMS systems:

 MySQL
 Microsoft Access
 Oracle
 PostgreSQL
 dBASE

Prof. [Link] [Link].,[Link].,BEd., Page 1

  FoxPro
 SQLite
 IBM DB2
 LibreOffice Base
 MariaDB
 Microsoft SQL Server

DATABASE SYSTEM APPLICATIONS

In so many fields, we will use a database management system. Databases are widely used.
Let’s see some of the applications where database management system uses
Here are some representative applications:

 Banking
 Airlines
 Universities
 Credit card transactions
 Telecommunication
 Finance
 Sales
 Manufacturing
 Human resources
 Culture and scientific information
 Transport
 Tourism
 Telemedicine
 Natural resource Management
 Production Engineering
 Computerized Library Systems
 Automated teller Machines
 Computerized parts inventory Systems

Banking:

Banking is one of the main applications of databases. We all know there will be a
thousand transactions through banks daily and we are doing this without going to the bank.
This is all possible just because of DBMS that manages all the bank transactions. For
customer information, accounts, and loans, and banking transactions.

Prof. [Link] [Link].,[Link].,BEd., Page 2

Airlines:
For reservations and schedule information. Airlines were among the first to use databases in a
geographically distributed manner —terminals situated around the world accessed the central
database system through phone lines and other data networks.

Universities:
Now-a-days examinations are done online. So, the universities and colleges are
maintaining DBMS to store Student’s registrations details, results, courses and grade all the
information in the database. For example, telecommunications. Without DBMS there is no
telecommunication company. DBMS is most useful to these companies to store the call
details and monthly postpaid bills. For recording student information, course registrations,
and grades.

Credit card transactions:

The purchase of items and transactions of credit cards are made possible only by
DBMS. A credit card holder has to know the importance of their information that all are
secured through DBMS. For purchases on credit cards and generation of monthly statements.

Telecommunication:
For keeping records of calls made, generating monthly bills, maintaining balances on prepaid
calling cards, and storing information about the communication networks.

Finance:
Now-a-days there are lots of things to do with finance like storing sales, holding
information and finance statement management etc. these all can be done with database
systems. For storing information about holdings, sales, and purchases of financial instruments
such as stocks and bonds.

Sales: For customer, product and purchase information.

Manufacturing:
Manufacturing companies make products and sell them on a daily basis. To keep
records of all those details DBMS is used For management of supply chain and for tracking
production of items in factories, inventories of items in warehouses/stores, and orders for
items.

Human resources:
Big firms or organizations have numerous specialists or representatives working
under them. They store data about worker’s compensation, assessment, and work with the
assistance of an information base administration framework (DBMS). For information
about employees, salaries, payroll taxes and benefits, and for generation of paychecks.

Library Management System:

Now-a-days it’s become easy in the Library to track each book and maintain it
because of the database. This happens because there are thousands of books in the library. It
is very difficult to keep a record of all books in a copy or register. Now DBMS used to
maintain all the information related to book issue dates, name of the book, author and
availability of the book.

Prof. [Link] [Link].,[Link].,BEd., Page 3

Railway Reservation System
The railway reservation system database plays a very important role by keeping
record of ticket booking, train’s departure time and arrival status and also gives information
regarding train late to people through the database.

Military
Military information is very crucial information. And every country has military and
details of the soldiers. This information is kept very securely and that security feature is
provided by the DBMS. The government stores military information like – Details of the
soldiers, Operations performed by soldiers, locations, etc.

PURPOSE OF DATABASE SYSTEMS

To see why database management systems are necessary, let's look at a typical “File-
processing system" supported by a conventional operating system.

Application programs are written to manipulate files to perform the following tasks:

Debit or credit an account.

Add a new account.
Find an account balance.
Generate monthly statements.

Development of the system proceeds as follows:

New application programs must be written as the need arises.

New permanent files are created as required So we can see there are problems with the
straight file-processing approach:

Data redundancy and inconsistency

Same information may be duplicated in several places.
All copies may not be updated properly.

Difficulty in accessing data

May have to write a new application program to satisfy an unusual request.
E.g. find all customers with the same postal code.
Could generate this data manually, but a long job...

Data isolation
Data in different files.
Data in different formats.
Difficult to write new application programs.

Multiple users
Want concurrency for faster response time.
Need protection for concurrent updates.
E.g. two customers withdrawing funds from the same account at the same time - account has
$500 in it, and they withdraw $100 and $50. The result could be $350, $400 or $450 if no

Prof. [Link] [Link].,[Link].,BEd., Page 4

protection.

Security problems
Every user of the system should be able to access only the data
they are permitted to see.

E.g. payroll people only handle employee records, and cannot see customer accounts; tellers
only access account data and cannot see payroll data. Difficult to enforce this with
application programs.

Integrity problems
Data may be required to satisfy constraints.
E.g. no account balance below $25.00.

Again, difficult to enforce or to change constraints with the file-processing approach.

These problems and others led to the development of database management systems.


A DBMS provides a secure and survivable medium for the storage and retrieval of
data.

In real world ,the data is shared among several users and is persistent.

1. Data Redundancy
The same piece of information may be duplicated in several files.

In previous data management system (file processing system), different

programmer creates files and writes application programs to access it.

In file system, each application accessed its own private files which cannot be
shared between different applications.

If two applications needs same file then there must be two files for both
application. This caused data redundancy.
Ex:Address,phone no.
2. Data Integrity is Enforced
Accurate data is maintained in database management system. Some kind of
constraints are enforced on the database to maintain integrity. For example, If the data
type is numbers (age) in the table, then you cannot enter text.
3. Inconsistency can be avoided
Data is accessed and manipulated frequently. If two programs are accessing the
same data and one program changed it. Then another programs processes with wrong
data. Database management should avoid the chances of inconsistency.

Prof. [Link] [Link].,[Link].,BEd., Page 5

 4. Data can be shared
Data can be shared in centralized database management system. Because of
sharing of data, different applications can access the centralized data.
5. Restriction for unauthorized access
As data can be shared from database, there must be different users. All the users
must not have full access to database. For example, If you could access my database then,
you can get my password and vital information.
6. Provide Backup and Recovery
As database has vital and important data, there is need of backup and recovery.
Backup and recovery must be reliable and possible when needed. Software and hardware
failure is often and database backup and recovery is important in case of failure.

VIEW OF DATA

A major purpose of a database system is

to provide users with an abstract view of the
data. That is, the system hides certain details of
how the data are stored and maintained.

Data Abstraction
Many database-systems users are not
computer trained, developers hide the
complexity from users through several levels of
abstraction, to simplify users’ interactions with
the system:

Physical level. The lowest level of abstraction

describes how the data are actually stored. The
physical level describes complex low-level data
structures in detail.

Logical level. The next-higher level of abstraction describes what data are stored in the
database, and what relationships exist among those data. Database administrators, who must
decide what information to keep in the database, use the logical level of abstraction.

View level. The highest level of abstraction describes only part of the entire database. Many
users of the database system do not need all this information; instead, they need to access
only a part of the database. The view level of abstraction exists to simplify their interaction
with the system. The system may provide many views for the same database.

Prof. [Link] [Link].,[Link].,BEd., Page 6

 DATABASE LANGUAGES

A database system provides three different types of languages:

1. Data Definition Language (DDL)
which contains following SQL commands.
CREATE, ALTER, DROP, TRUNCATE

2. Data Manipulation Language (DML)

which contains following SQL commands.
INSERT, UPDATE, DELETE & SELECT

3. Transaction Control Language (TCL)

which contains following commands.
COMMIT, ROLLBACK, SAVEPOINT

4. Data Control Language (DCL)

which contains the following commands.
GRANT, REVOKE

DATA DEFINITION LANGUAGE (DDL)

Data-Definition Language is used to specify a database schema by a set of definitions
expressed by a special language called a data-definition language (DDL).

For instance, the following statement in the SQL language defines the account table:
create table account (account-number char(10), balance integer) Execution of the above DDL
statement creates the account table. In addition, it updates a special set of tables called the
data dictionary or data directory. A data dictionary contains metadata—that is, data about
data. The schema of a table is an example of metadata. A database system consults the data
dictionary before reading or modifying actual data.

We specify the storage structure and access methods used by the database system by a
set of statements in a special type of DDL called a data storage and definition language.
These statements define the implementation details of the database schemas, which are
usually hidden from the users.

The data values stored in the database must satisfy certain consistency constraints. For
example, suppose the balance on an account should not fall below $100. The DDL provides
facilities to specify such constraints. The database systems check these constraints every time
the database is updated.

DATA MANIPULATION LANGUAGE (DML)

Data manipulation is the retrieval of information stored in the database.

 The insertion of new information into the database
 The deletion of information from the database
 The modification of information stored in the database
 The selection of information stored in the database

Prof. [Link] [Link].,[Link].,BEd., Page 7

A data-manipulation language (DML) is a language that enables users to access or
manipulate data as organized by the appropriate data model.
There are basically two types:

i. Procedural DMLs require a user to specify what data are needed and how to get those data.
ii. Declarative DMLs (also referred to as nonprocedural DMLs) require a user to specify what
data are needed without specifying how to get those data. Declarative DMLs are usually
easier to learn and use than are procedural DMLs. However, since a user does not have to
specify how to get the data, the database system has to figure out an efficient means of
accessing data. The DML component of the SQL language is nonprocedural.

A query is a statement requesting the retrieval of information. The portion of a DML

that involves information retrieval is called a query language. Although technically incorrect,
it is common practice to use the terms query language and data manipulation language
synonymously.

This query in the SQL language finds the name of the customer whose customer-id is 192-83-
7465:

The query specifies that those rows from the table customer where the customer-id is 192-83-
7465

must be retrieved, and the customer-name attribute of these rows must be displayed. Queries
may involve information from more than one table.

For instance, the following query finds the balance of all accounts owned by the customer
with customerid 192-837465.

There are a number of database query languages in use, either commercially or

experimentally.

The levels of abstraction apply not only to defining or structuring data, but also to
manipulating data. At the physical level, we must define algorithms that allow efficient
access to data. At higher levels of abstraction, we emphasize ease of use.

The goal is to allow humans to interact efficiently with the system. The query processor
component of the database system translates DML queries into sequences of actions at the
physical level of the database system.

TRANSACTION CONTROL LANGUAGE (TCL)

A data control language (DCL) is a syntax similar to a computer programming

language used to control access to data stored in a database (Authorization). In particular, it is
a component of Structured Query Language (SQL). Examples of DCL commands include:
GRANT to allow specified users to perform specified tasks.

Prof. [Link] [Link].,[Link].,BEd., Page 8

 DATABASE ARCHITECTURE

Overall System Structure

The Central component is known as the core DBMS which has a query
evaluation engine to execute the queries. The disk storage is used to store the data.
Storage Manager
A storage manager is a program module that provides the interface between the low
level data stored in the database and the application programs and queries submitted to the
system. The Various Components of storage manager are:

Prof. [Link] [Link].,[Link].,BEd., Page 9

Authorization and Integrity manager:
It tests for satisfaction of various integrity constraints and checks the authority of
users accessing the data.
Transaction Manager:

It ensures that the database remains in a consistent state despite system failures, and
concurrent executions proceed without conflicting.
File Manager:
It manages the allocation of space on disk storage and the data structures used to
represent information stored on disk.
Buffer Manager:
It is responsible for fetching data from disk storage into main memory and deciding
what data to cache in main memory.
Storage Manager:
It implements several data structures as part of physical system
implementation. Data Files: which store the database itself.
Data Dictionary:
It contains metadata that is data about data. The schema of a table is an example of
metadata. A database system consults the data dictionary before reading and
modifying actual data.
Indices, which provide fast access to data item that hold particular values.
Query Processor
The Query processor is an important part of the database [Link] helps the database
system to simplify and facilitate access to data.
DDL interpreter ,which interprets DDL statements and records the definitions in the data
dictionary.
DML Compiler,which translate DML statement in a query language into an evaluation plan
consisting of low level instructions that the query evaluation engine understands.
Query evaluation engine, Which executes low-level instructions generated by the DML
compiler.
Levels of Abstraction
Physical level : The three level architecture for database system describes how a record (E.g.,
customer) is stored.

Prof. [Link] [Link].,[Link].,BEd., Page 10

Logical level: describes data stored in database, and the relationships amo data.
Type customer = record name: string;

street: string;
city : integer;
end;
1.7.3View level: application programs hide details of data types. Views can also
information (E.g., salary) for security purposes.

View of Data

1.8. Instances and Schemas

Similar to types and variables in programming languages which we already know, Schema
is the logical structure of the database E.g., the database consists of information about a set
of customers and accounts and the relationship between them) analogous to type
information of a variable in a program.

Physical schema: database design at the physical level

Logical schema: database design at the logical level

Instance is the actual content of the database at a particular point of time,

analogous to the value of a variable.

Physical Data Independence – the ability to modify the physical schema

without changing the logical schema. Applications depend on the logical schema.
In general, the interfaces between the various levels and components should
be well defined so that changes in some parts do not seriously influence
others.

Prof. [Link] [Link].,[Link].,BEd., Page 11

 Database users and administrators
USERS
This differentiation is made according to the interaction of users to the database. Database
system is made to store information and provide an environment for retrieving information.

There are three types of users:

(i) An Application Programmers

(ii) End – User and
(iii) Database Administrator.

I. APPLICATION PROGRAMMERS

As its name shows, application programmers are the one who writes application
programs that uses the database. These application programs are written in programming
languages like COBOL or PL (Programming Language 1), Java and fourth generation
language. These programs meet the user requirement and made according to user
requirements. Retrieving information, creating new information and changing existing
information is done by these application programs. They interact with DBMS through DML
(Data manipulation language) calls. And all these functions are performed by generating a
request to the DBMS. If application programmers are not there then there will be no
creativity in the whole team of Database.

II. END USERS

End users are those who access the database from the terminal end. They use the
developed applications and they don’t have any knowledge about the design and working of
database. These are the second class of users and their main motto is just to get their task
done.

There are basically two types of end users that are discussed below.

a. Casual User
b. Naive User

Casual User: These users have great knowledge of query language. Casual users access
data by entering different queries from the terminal end. They do not write programs but
they can interact with the system by writing queries.

Naïve: Any user who does not have any knowledge about database can be in this
category.
Their task is to just use the developed application and get the desired results.

III. DBA (DATABASE ADMINISTRATOR)

DBA can be a single person or it can be a group of person. Database Administrator is

responsible for everything that is related to database. He makes the policies, strategies and
provides technical supports.

Prof. [Link] [Link].,[Link].,BEd., Page 12

 One of the main reasons for using DBMSs is to have central control of both the data
and the programs that access those data. A person who has such central control over the
system is called a database administrator (DBA). The functions of a DBA include:

History:

Here, are the important landmarks from the history of DBMS:

 1960 – Charles Bachman designed the first DBMS system

 1970 – Codd introduced IBM’S Information Management System (IMS),
Dr. Edgar F. "Ted" Codd of IBM is known as the father of relational databases. He
described a relational model for databases.
 1976- Peter Chen coined and defined the Entity-relationship model, also
known as the ER model
 1980 – Relational Model becomes a widely accepted database component
 1985- Object-oriented DBMS develops.
 1974 -- Structured Query Language appeared.
 1978 -- IBM worked to develop Codd's ideas and released a product named
System/R.
 1986 -- IBM developed the first prototype of relational database and
 standardized by ANSI
 1990s- Incorporation of object-orientation in relational DBMS.
 1991- Microsoft ships MS access, a personal DBMS, and that displaces all
other personal DBMS products.
 1995: First Internet database applications
 1997: XML applied to database processing. Many vendors begin to integrate
XML into DBMS products.

ENTITY RELATIONSHIP MODELING

THE ENTITY RELATIONSHIP MODEL

E-R diagram is the short form of “Entity-Relationship” diagram. An E-R diagram

efficiently shows the relationships between various entities stored in a database. E-R
diagrams are used to model real-world objects like a person, a car, a company etc. and the
relation between these real-world objects.

An E-R diagram has following features:

E-R diagrams are used to represent E-R model in a database, which makes them easy to
be
converted into relations (tables).

E-R diagrams provide the purpose of real-world modeling of objects which makes them

Prof. [Link] [Link].,[Link].,BEd., Page 13

intently useful.

E-R diagrams require no technical knowledge & no hardware support.

These diagrams are very easy to understand and easy to create even by a naive user.

It gives a standard solution of visualizing the data logically.

COMPONENTS OF AN E-R DIAGRAM

An E-R diagram constitutes of following Components

A. Entity:- Any real-world object can be represented as an entity about which data can be
stored in a database. All the real world objects like a book, an organization, a product, a car, a
person are the examples of an entity.

Any living or non-living objects can be represented by an entity. An entity is symbolically

represented by a rectangle enclosing its name.

Entities can be characterized into two types:

Strong entity: A strong entity has a primary key attribute which uniquely identifies each
entity.

Prof. [Link] [Link].,[Link].,BEd., Page 14

Symbol of strong entity is same as an entity.
Student

Weak entity: A weak entity does not have a primary key attribute and depends on other
entity via a foreign key attribute.

B. Attribute:- Each entity has a set of properties. These properties of each entity are termed
as attributes. For example, a car entity would be described by attributes such as price,
registration number, model number, color etc. Attributes are indicated by ovals in an E-R
diagram. A primary key attribute is depicted by an underline in the E-R diagram.

An attribute can be characterized into following types:

Simple attribute:- An attribute is classified as a simple attribute if it cannot be partitioned

into smaller components.
For example, age and sex of a person. A simple attribute is represented by an oval.

Composite attribute:- A composite attribute can be subdivided into smaller components

which further form attributes. For example, ‘name’ attribute of an entity “person” can be
broken down into first name and last name which further form attributes. Grouping of these

Prof. [Link] [Link].,[Link].,BEd., Page 15

related attributes forms a composite attribute. ‘name is the composite attribute in this
example.

Single valued attribute:- If an attribute of a particular entity represents single value for
each
instance, then it is called a single-valued attribute. For example, Ramesh, Kamal and Suraj
are the instances of entity ‘student’ and each of them is issued a separate roll number. A
single oval is used to represent this attribute.

Multi valued attribute:– An attribute which can hold more than one value, it is then
termed as multi-valued attribute. For example, phone number of a person. Symbol of multi-
valued
attribute is shown below,

Derived attribute: A derived attribute calculate its value from another attribute. For
example, ‘age’ is a derived attribute if it calculates its value from ‘current date’ & ‘birth date’
attributes.

A derived attribute is represented by a dashed oval.

Prof. [Link] [Link].,[Link].,BEd., Page 16

C. Relationships:- A relationship is defined as bond or
attachment between 2 or more entities. Normally, a verb in a sentence signifies a relationship.

For example,
An employee assigned a project.
Teacher teaches a student.
Author writes a book.
A diamond is used to symbolically represent a relationship in the e-r diagram.

VARIOUS TERMS RELATED TO RELATIONSHIPS

a). Degree of relationship:- It signifies the number of entities involved in a relationship.

Degree of a relationship can be classified into following types:
Unary relationship:- If only single entity is involved in a relationship then it is a unary
relationship. For example, An employee(manager) supervises another employee.

Binary relationships:- when two entities are associated to form a relation, then it is
known as a binary relationship. For example, A person works in a company. Most of the
times we use only binary relationship in an e-r diagram. The teacher-student example shown
above signifies a binary relationship. Other types of relationships are ternary and quaternary.
As the name signifies, a ternary relationship is associated with three entities and a quaternary
relationship is associated with four entities.

Prof. [Link] [Link].,[Link].,BEd., Page 17

b.) Connectivity of a relationship:- Connectivity of a relationship describes, how many
instances of one entity type are linked to how many instances of another entity type. Various
categories of connectivity of a relationship are:

One to One (1:1) – “Student allotted a project” signifies a one-to-one relationship

because
only one instance of an entity is related with exactly one instance of another entity type.

One to Many (1:M) – “A department recruits faculty” is a one-to-many relationship

because a department can recruit more than one faculty, but a faculty member is related to
only one department.

Many to One (M:1) – “Many houses are owned by a person” is a many-to-one

relationship
because a person can own many houses but a particular house is owned only a person.

Many to Many (M:N) – “Author writes books” is a many-to-many relationship because

an
author can write many books and a book can be written by many authors.

Prof. [Link] [Link].,[Link].,BEd., Page 18

c) Weak Entity Type and Identifying Relationship:

An entity type has a key attribute which uniquely identifies each entity in the entity
set. But there exists some entity type for which key attribute can’t be defined. These are
called Weak Entity type.

For example, A company may store the information of dependants (Parents, Children,
Spouse) of an Employee. But the dependents don’t have existence without the employee. So
Dependent will be weak entity type and Employee will be Identifying Entity type for
Dependent. A weak entity type is represented by a double rectangle. The participation of
weak entity type is always total. The relationship between weak entity type and its identifying
strong entity type is called identifying relationship and it is represented by double diamond.

KEYS

Keys play an important role in the relational database.

It is used to uniquely identify any record or row of data from the table. It is also used to
establish and identify relationships between tables.

For example: In Student table, ID is used as a key because it is unique for each student. In
PERSON table, passport_number, license_number, SSN are keys since they are unique for
each person.

TYPES OF KEY

1. Primary key

It is the first key which is used to identify one and only one instance of an entity uniquely. An
entity can contain multiple keys as we saw in PERSON table. The key which is most suitable
from those lists become a primary key.

In the EMPLOYEE table, ID can be primary key since it is unique for each employee. In the
EMPLOYEE table, we can even select License_Number and Passport_Number as primary
key since they are also unique.

For each entity, selection of the primary key is based on requirement and developers.

2. Candidate key
A candidate key is an attribute or set of an attribute which can uniquely identify a tuple.
The remaining attributes except for primary key are considered as a candidate key. The

Prof. [Link] [Link].,[Link].,BEd., Page 19

candidate keys are as strong as the primary key.
For example: In the EMPLOYEE table, id is best suited for the primary key. Rest of the
attributes
like SSN, Passport_Number, and License_Number, etc. are considered as a candidate key.

3. Super Key
Super key is a set of an attribute which can uniquely identify a tuple. Super key is a superset
of a candidate key.

For example: In the above EMPLOYEE table, for(EMPLOEE_ID, EMPLOYEE_NAME)

the name of two employees can be the same, but their EMPLYEE_ID can't be the same.
Hence, this combination can also be a key.

The super key would be EMPLOYEE-ID, (EMPLOYEE_ID, EMPLOYEE-NAME), etc.

4. Foreign key
Foreign keys are the column of the table which is used to point to the primary key of
another table.
In a company, every employee works in a specific department, and employee and
department are two different entities. So we can't store the information of the department in
the employee table. That's why we link these two tables through the primary key of one table.
We add the primary key of the DEPARTMENT table, Department_Id as a new attribute
in the EMPLOYEE table.
Now in the EMPLOYEE table, Department_Id is the foreign key, and both the tables are
related.

RELATIONAL DATABASE

A relational database is a collection of information that organizes data in predefined

relationships where data is stored in one or more tables (or "relations") of columns and rows,
making it easy to see and understand how different data structures relate to each other.
Relationships are a logical connection between different tables, established on the basis of
interaction among these tables.

The relational database model

Developed by E.F. Codd from IBM in the 1970s, the relational database model allows any
table to be related to another table using a common attribute. Instead of using hierarchical
structures to organize data, Codd proposed a shift to using a data model where data is stored,
accessed, and related in tables without reorganizing the tables that contain them.

Think of the relational database as a collection of spreadsheet files that help businesses
organize, manage, and relate data. In the relational database model, each “spreadsheet” is a

Prof. [Link] [Link].,[Link].,BEd., Page 20

table that stores information, represented as columns (attributes) and rows (records
or tuples).

Attributes (columns) specify a data type, and each record (or row) contains the value of that
specific data type. All tables in a relational database have an attribute known as the primary
key, which is a unique identifier of a row, and each row can be used to create a relationship
between different tables using a foreign key—a reference to a primary key of another
existing table.

Let’s take a look at how the relational database model works in practice:

Say you have a Customer table and an Order table.

Prof. [Link] [Link].,[Link].,BEd., Page 21

 The Customer table contains data about the customer:

 Customer ID (primary key)

 Customer name

 Billing address

 Shipping address

Prof. [Link] [Link].,[Link].,BEd., Page 22

 In the Customer table, the customer ID is a primary key that uniquely identifies who the
customer is in the relational database. No other customer would have the same Customer ID.

The Order table contains transactional information about an order:

 Order ID (primary key)

 Customer ID (foreign key)

 Order date

 Shipping date

 Order status

Here, the primary key to identify a specific order is the Order ID. You can connect a
customer with an order by using a foreign key to link the customer ID from
the Customer table.

The two tables are now related based on the shared customer ID, which means you can query
both tables to create formal reports or use the data for other applications. For instance, a retail
branch manager could generate a report about all customers who made a purchase on a
specific date or figure out which customers had orders that had a delayed delivery date in the
last month.

The above explanation is meant to be simple. But relational databases also excel at showing
very complex relationships between data, allowing you to reference data in more tables as
long as the data conforms to the predefined relational schema of your database.

As the data is organized as pre-defined relationships, you can query the data declaratively. A
declarative query is a way to define what you want to extract from the system without
expressing how the system should compute the result. This is at the heart of a relational
system as opposed to other systems.

Prof. [Link] [Link].,[Link].,BEd., Page 23

Examples of relational databases

Now that you understand how relational databases work, you can begin to learn about the
many relational database management systems that use the relational database model. A
relational database management system (RDBMS) is a program used to create, update, and
manage relational databases. Some of the most well-known RDBMSs include MySQL,
PostgreSQL, MariaDB, Microsoft SQL Server, and Oracle Database.

Cloud-based relational databases like Cloud SQL, Cloud Spanner and AlloyDB have become
increasingly popular as they offer managed services for database maintenance, patching,
capacity management, provisioning and infrastructure support.

Ready to get started? Create a 90-day Cloud Spanner free trial instance with 10 GB of storage
at no cost.

Benefits of relational databases

The main benefit of the relational database model is that it provides an intuitive way to
represent data and allows easy access to related data points. As a result, relational databases
are most commonly used by organizations that need to manage large amounts of structur ed
data, from tracking inventory to processing transactional data to application logging.

There are many other advantages to using relational databases to manage and store your
data, including:

DATABASE DESIGN

Relational database design (RDD) models’ information and data into a set of tables with rows
and columns. Each row of a relation/table represents a record, and each column represents an
attribute of data. The Structured Query Language (SQL) is used to manipulate relational
databases. The design of a relational database is composed of four stages, where the data are
modeled into a set of related tables.

The stages are −

Database Design can be defined as a set of procedures or collection of tasks involving

various steps taken to implement a database. Following are some critical points to keep in
mind to achieve a good database design:
1. Data consistency and integrity must be maintained.
2. Low Redundancy
3. Faster searching through indices
4. Security measures should be taken by enforcing various integrity constraints.
5. Data should be stored in fragmented bits of information in the most atomic format possible.

Prof. [Link] [Link].,[Link].,BEd., Page 24

 6. What are the Following Steps that can be taken by a Database Designer to Ensure
Good Database Design?
7. Step 1: Determine the goal of your database, and ensure clear communication with the
stakeholders (if any). Understanding the purpose of a database will help in thinking of
various use cases & where the problem may arise & how we can prevent it.
8. Step 2: List down all the entities that will be present in the database & what relationships
exist among them.
9. Step 3: Organize the information into different tables such that no or very little redundancy
is there.
10. Step 4: Ensure uniqueness in every table. The uniqueness of records present in any relation
is a very crucial part of database design that helps us avoid redundancy. Identify the key
attributes to uniquely identify every row from columns. You can use various key constraints
to ensure the uniqueness of your table, also keep in mind the uniquely identifying records
must consume as little space as possible & shall not contain any NULL values.
11. Step 5: After all the tables are structured, and information is organized apply Normalization
Forms to identify anomalies that may arise & redundancy that can cause inconsistency in
the database.

Primary Terminologies Used in Database Design

 Redundancy: Redundancy refers to the duplicity of the data. There can be specific use cases
when we need or don’t need redundancy in our Database. For ex: If we have a banking system
application then we may need to strictly prevent redundancy in our Database.
 Schema: Schema is a logical container that defines the structure & manages the organization of
the data stored in it. It consists of rows and columns having data types for each column.
 Records/Tuples: A Record or a tuple is the same thing, basically its where our data is stored
inside a table
 Indexing: Indexing is a data structure technique to promote efficient retrieval of the data stored
in our database.
 Data Integrity & Consistency: Data integrity refers to the quality of the information stored in
our database and consistency refers to the correctness of the data stored.
 Data Models: Data models provide us with visual modeling techniques to visualize the data &
the relationship that exists among those data. Ex: model, Network Model, Object Oriented
Model, Hierarchical model, etc.
 Functional Dependency: Functional Dependency is a relationship between two attributes of
the table that represents that the value of one attribute can be determined by another. Ex: {A ->
B}, A & B are two attributes and attribute A can uniquely determine the value of B.
 Transaction: Transaction is a single logical unit of work. It signifies that some changes are
made in the database. A transaction must satisfy the ACID or BASE properties (depending on
the type of Database).
 Schedule: Schedule defines the sequence of transactions in which they’re executed by one or
multiple users.
 Concurrency: Concurrency refers to allowing multiple transactions to operate simultaneously
without interfering with one another.
 Database Design Lifecycle
 The database design lifecycle goes something like this:
 database Design Lifecycle
 The database design lifecycle goes something like this:

Prof. [Link] [Link].,[Link].,BEd., Page 25

 

1. Requirement Analysis
It’s very crucial to understand the requirements of our application so that you can think in
productive terms. And imply appropriate integrity constraints to maintain the data integrity
& consistency.
2. Logical & Physical Design
This is the actual design phase that involves various steps that are to be taken while
designing a database. This phase is further divided into two stages:
 Logical Data Model Design: This phase consists of coming up with a high-level design
of our database based on initially gathered requirements to structure & organize our
data accordingly. A high-level overview on paper is made of the database without
considering the physical level design, this phase proceeds by identifying the kind of
data to be stored and what relationship will exist among those data.
Entity, Key attributes identification & what constraints are to be implemented is the
core functionality of this phase. It involves techniques such as Data Modeling to
visualize data, normalization to prevent redundancy, etc.
 Physical Design of Data Model: This phase involves the implementation of the logical
design made in the previous stage. All the relationships among data and integrity
constraints are implemented to maintain consistency & generate the actual database.
3. Data Insertion and testing for various integrity Constraints
Finally, after implementing the physical design of the database, we’re ready to input the
data & test our integrity. This phase involves testing our database for its integrity to see if
something got left out or, if anything new to add & then integrating it with the desired
application.
Logical Data Model Design
The logical data model design defines the structure of data and what relationship exists
among those data. The following are the major components of the logical design:
1. Data Models: Data modeling is a visual modeling technique used to get a high-level
overview of our database. Data models help us understand the needs and requirements of
our database by defining the design of our database through diagrammatic representation.
Ex: model, Network model, Relational Model, object-oriented data model.

Prof. [Link] [Link].,[Link].,BEd., Page 26

Data Models

2. Entity: Entities are objects in the real world, which can have certain properties & these
properties are referred to as attributes of that particular entity. There are 2 types of entities:
Strong and weak entity, weak entity do not have a key attribute to identify them, their
existence solely depends on one 1-specific strong entity & also have full participation in a
relationship whereas strong entity does have a key attribute to uniquely identify them.
Weak entity example: Loan -> Loan will be given to a customer (which is optional) & the
load will be identified by the customer_id to whom the lone is granted.
3. Relationships: How data is logically related to each other defines the relationship of that
data with other entities. In simple words, the association of one entity with another is
defined here.
A relationship can be further categorized into – unary, binary, and ternary relationships.
 Unary: In this, the associating entity & the associated entity both are the same. Ex:
Employee Manages themselves, and students are also given the post of monitor hence
here the student themselves is a monitor.
 Binary: This is a very common relationship that you will come across while designing
a database.
Ex: Student is enrolled in courses, Employee is managed by different managers, One
student can be taught by many professors.
 Ternary: In this, we have 3 entities involved in a single relationship. Ex: an employee
works on a project for a client. Note that, here we have 3 entities: Employee, Project &
Client.
4. Attributes: Attributes are nothing but properties of a specific entity that define its
behavior. For example, an employee can have unique_id, name, age, date of birth (DOB),
salary, department, Manager, project id, etc.
5. Normalization: After all the entities are put in place and the relationship among data is
defined, we need to look for loopholes or possible ambiguities that may arise as a result of
CRUD operations. To prevent various Anomalies such as INSERTION, UPDATION, and
DELETION Anomalies.

Data Normalization is a basic procedure defined for databases to eliminate such anomalies
& prevent redundancy.
An Example of Logical Design

Prof. [Link] [Link].,[Link].,BEd., Page 27

 Logical Design Example

Physical Design
The main purpose of the physical design is to actually implement the logical design that is,
show the structure of the database along with all the columns & their data types, rows,
relations, relationships among data & clearly define how relations are related to each other.
Following are the steps taken in physical design
Step 1: Entities are converted into tables or relations that consist of their properties
(attributes)
Step 2: Apply integrity constraints: establish foreign key, unique key, and composite key
relationships among the data. And apply various constraints.
Step 3: Entity names are converted into table names, property names are translated into
attribute names, and so on.
Step 4: Apply normalization & modify as per the requirements.
Step 5: Final Schemes are defined based on the entities & attributes derived in logical
design.

Prof. [Link] [Link].,[Link].,BEd., Page 28

 Physical Design

Data Storage and Querying in DBMS

Database Management System is the collection of interrelated data/information or a set of

programs that manages controls, and accesses the use of data. Through DBMS, users can
manage the data efficiently in a database to increase accessibility and productivity. For
example – Employee records and telephone Books have all the different contacts saved in a
single place very efficiently.
So, a Database System is a software system, having an organized collection of structured
information, stored in a computer system. It enables the user to create, maintain, define, and
control access to the database.
Uses of DBMS
 To increase productivity through real-time component data.
 For Reducing the data redundancy and inconsistency.
 To enhance the data integrity.
 For Retrieving the data.

Prof. [Link] [Link].,[Link].,BEd., Page 29

 For Data Security.
 For Data Indexing.

The Database System is further divided into two components. They are as follows:

 Data Storage Manager

 Query Manager
Data Storage Manager
Data Storage Manager also known as “Database Control System”, is generally a program
that provides an interface between the data/information stored and the queries received. It
helps us to maintain the integrity and consistency of the database by applying the
constraints. It is a highly flexible and scalable product that provides us with the capability
of fully managed storage.
Storage Manager is generally in charge of the interactions with the File Manager, where
raw data is stored on the data with the help of the file system. It translates various DML
statements into low-level commands.
 Authorization and Integrity Manager: The main purpose of the Authorization and
Integrity Manager is to ensure the satisfaction of the integrity constraints and checks the
authority of users to access information.
 Transaction Manager: The main purpose of Transaction Manager is to ensure that
even after the system failures, the database should remain in a uniform state.
 File Manager: The main purpose of File Manager is to manage the allocation of space
on the disk storage.
 Buffer Manager: The main purpose of Buffer Manager is to fetch the data from disk
storage into the main memory.
Query Processor
Query Processor contains the following three components:
 DDL Interpreter: The DDL Interpreter is the Data Definition Language Interpreter
which is used to build and modify the structure of your tables and other objects in the
table.
 DML Compiler: DML Compiler is the Data Manipulation Language which is used for
adding (inserting), deleting, and modifying (updating) data in a database. or we can say
translating the DML statements into query language consists of low-level instructions
that only query engines can understand.
 Query Evaluation Engine: It executes the low-level language instructions, generated
by DML Compiler.

Transaction Management

Transaction management is an integral process of database management systems

(DBMS) during which transaction management software oversees, coordinates and executes
any given attempted transaction.

Transactions are a set of operations used to perform a logical set of work. It is the
bundle of all the instructions of a logical operation. A transaction usually means that the
data in the database has changed. One of the major uses of DBMS is to protect the user’s
data from system failures. It is done by ensuring that all the data is restored to a consistent
state when the computer is restarted after a crash. The transaction is any one execution of

Prof. [Link] [Link].,[Link].,BEd., Page 30

the user program in a DBMS. One of the important properties of the transaction is that it
contains a finite number of steps. Executing the same program multiple times will generate
multiple transactions.
Example: Consider the following example of transaction operations to be performed to
withdraw cash from an ATM vestibule.

Steps for ATM Transaction

1. Transaction Start.
2. Insert your ATM card.
3. Select a language for your transaction.
4. Select the Savings Account option.
5. Enter the amount you want to withdraw.
6. Enter your secret pin.
7. Wait for some time for processing.
8. Collect your Cash.
9. Transaction Completed.
A transaction can include the following basic database access operation.
 Read/Access data (R): Accessing the database item from disk (where the database
stored data) to memory variable.
 Write/Change data (W): Write the data item from the memory variable to the disk.
 Commit: Commit is a transaction control language that is used to permanently save the
changes done in a transaction

Desirable Properties of Transaction (ACID Properties)

For a transaction to be performed in DBMS, it must possess several properties often
called ACID properties.
 A – Atomicity
 C – Consistency
 I – Isolation
 D – Durability
Transaction States
Transactions can be implemented using SQL queries and Servers. In the diagram, you can
see how transaction states work.

Prof. [Link] [Link].,[Link].,BEd., Page 31

 Transaction States

The transaction has four properties. These are used to maintain consistency in a database,
before and after the transaction.
Property of Transaction:
 Atomicity
 Consistency
 Isolation
 Durability
Atomicity
 States that all operations of the transaction take place at once if not, the transactions are
aborted.
 There is no midway, i.e., the transaction cannot occur partially. Each transaction is
treated as one unit and either run to completion or is not executed at all.
 Atomicity involves the following two operations:
 Abort: If a transaction aborts, then all the changes made are not visible.
 Commit: If a transaction commits then all the changes made are visible.
Consistency
 The integrity constraints are maintained so that the database is consistent before and
after the transaction.
 The execution of a transaction will leave a database in either its prior stable state or
anew stable state.
 The consistent property of database states that every transaction sees a consistent
database instance.
 The transaction is used to transform the database from one consistent state to another
consistent state.
Isolation
 It shows that the data which is used at the time of execution of a transaction cannot be
used by the second transaction until the first one is completed.
 In isolation, if the transaction T1 is being executed and using the data item X, then that
data item can’t be accessed by any other transaction T2 until the transaction T1ends.
 The concurrency control subsystem of the DBMS enforced the isolation property
Durability
 The durability property is used to indicate the performance of the database’s consistent
state. It states that the transaction made the permanent changes.
 They cannot be lost by the erroneous operation of a faulty transaction or by the system
failure. When a transaction is completed, then the database reaches a state known as the
consistent state. That consistent state cannot be lost, even in the event of a system’s
failure.
 The recovery subsystem of the DBMS has the responsibility of Durability property.

Uses of Transaction Management

 The DBMS is used to schedule the access of data concurrently. It means that the user
can access multiple data from the database without being interfered with by each other.
Transactions are used to manage concurrency.
 It is also used to satisfy ACID properties.
 It is used to solve Read/Write Conflicts.
 It is used to implement Recoverability, Serializability, and Cascading.

Prof. [Link] [Link].,[Link].,BEd., Page 32

 Transaction Management is also used for Concurrency Control Protocols and the
Locking of data.
Advantages of using a Transaction
 Maintains a consistent and valid database after each transaction.
 Makes certain that updates to the database don’t affect its dependability or accuracy.
 Enables simultaneous use of numerous users without sacrificing data consistency.
Disadvantages of using a Transaction
 It may be difficult to change the information within the transaction database by end-
users.

DATA MINING AND ANALYSIS

What is data mining and analysis in DBMS?

Data mining is a process of extracting useful information, patterns, and trends from
raw data. Data analysis is a method that can be used to investigate, analyze, and demonstrate
data to find useful information. The data mining output gives the data pattern.

Data analysis and data mining are a subset of business intelligence (BI), which also
incorporates data warehousing, database management systems, and Online Analytical
Processing (OLAP). The technologies are frequently used in customer relationship
management (CRM) to analyze patterns and query customer databases.

What is RDBMS in data mining?

An RDBMS is a type of database management system (DBMS) that stores data in a
row-based table structure that connects related data elements. An RDBMS includes functions
that maintain the security, accuracy, integrity and consistency of the data.

Difference Between Data Mining and Data Analysis

[Link] Analysis :
 Data Analysis involves extraction, cleaning, transformation,
modeling and visualization of data with an objective to extract important and helpful
information which can be additional helpful in deriving conclusions and
make choices.
 The main purpose of data analysis is to search out some important information in
raw data so the derived knowledge is often used to create vital choices.
[Link] Mining :
Data mining could be called as a subset of Data Analysis. It is the exploration and
analysis of huge knowledge to find important patterns and rules.

Data mining could also be a systematic and successive method of identifying and
discovering hidden patterns and data throughout a big dataset. Moreover, it is used to build
machine learning models that are further used in artificial intelligence.

Prof. [Link] [Link].,[Link].,BEd., Page 33

Data mining is the act of automatically searching for large stores of information to find trends
and patterns that go beyond simple analysis procedures. Data mining utilizes complex
mathematical algorithms for data segments and evaluates the probability of future events.
Data Mining is also called Knowledge Discovery of Data (KDD).

Data Mining is a process used by organizations to extract specific data from huge databases
to solve business problems. It primarily turns raw data into useful information.

Data Mining is similar to Data Science carried out by a person, in a specific situation, on a
particular data set, with an objective. This process includes various types of services such as
text mining, web mining, audio and video mining, pictorial data mining, and social media
mining. It is done through software that is simple or highly specific. By outsourcing data
mining, all the work can be done faster with low operation costs. Specialized firms can also
use new technologies to collect data that is impossible to locate manually. There are tonnes of
information available on various platforms, but very little knowledge is accessible. The
biggest challenge is to analyze the data to extract important information that can be used to
solve a problem or for company development. There are many powerful instruments and
techniques available to mine data and find better insight from it.

Types of Data Mining

Data mining can be performed on the following types of data:

Relational Database:

A relational database is a collection of multiple data sets formally organized by tables,

records, and columns from which data can be accessed in various ways without having to

Prof. [Link] [Link].,[Link].,BEd., Page 34

recognize the database tables. Tables convey and share information, which facilitates data
searchability, reporting, and organization.

Data warehouses:

A Data Warehouse is the technology that collects the data from various sources within the
organization to provide meaningful business insights. The huge amount of data comes from
multiple places such as Marketing and Finance. The extracted data is utilized for analytical
purposes and helps in decision- making for a business organization. The data warehouse is
designed for the analysis of data rather than transaction processing.

Data Repositories:

The Data Repository generally refers to a destination for data storage. However, many IT
professionals utilize the term more clearly to refer to a specific kind of setup within an IT
structure. For example, a group of databases, where an organization has kept various kinds of
information.

Object-Relational Database:

A combination of an object-oriented database model and relational database model is called

an object-relational model. It supports Classes, Objects, Inheritance, etc.

Transactional Database:

A transactional database refers to a database management system (DBMS) that has the
potential to undo a database transaction if it is not performed appropriately. Even though this
was a unique capability a very long while back, today, most of the relational database
systems support transactional database activities.

Advantages of Data Mining

o The Data Mining technique enables organizations to obtain knowledge-based data.

o Data mining enables organizations to make lucrative modifications in operation and
production.
o Compared with other statistical data applications, data mining is a cost-efficient.
o Data Mining helps the decision-making process of an organization.

Disadvantages of Data Mining

o Many data mining analytics software is difficult to operate and needs advance training
to work on.
o Different data mining instruments operate in distinct ways due to the different
algorithms used in their design. Therefore, the selection of the right data mining tools
is a very challenging task.

Prof. [Link] [Link].,[Link].,BEd., Page 35

Data Mining Applications

Data Mining is primarily used by organizations with intense consumer demands- Retail,
Communication, Financial, marketing company, determine price, consumer preferences,
product positioning, and impact on sales, customer satisfaction, and corporate profits. Data
mining enables a retailer to use point-of-sale records of customer purchases to develop
products and promotions that help the organization to attract the customer.

Prof. [Link] [Link].,[Link].,BEd., Page 36