UNIT 1

Introduction:
Databases and database systems are an essential component of life in modern society: most of us
encounter several activities every day that involve some interaction with a database.
For example, if we go to the bank to deposit or withdraw funds, if we make a hotel or airline
reservation, if we access a computerized library catalog to search for a bibliographic item, or if
we purchase something online—such as a book, toy, or computer—chances are that our activities
will involve someone or some computer program accessing a database.

Data
Data are the known facts that can be recorded and that have implicit meaning OR it is a
collection of facts, figures and statistics related to an object. Data is unprocessed. For example:
Students fill an admission form when they get admission in college. The form consists of raw
facts about the students. These raw facts are student's name, father name, address etc. The
purpose of collecting this data is to maintain the records of the students during their study period
in the college.

Information
Processed data is called information. For example: Data collected from census is used to
generate different type of information. The government can use it to determine the literacy rate in
the country. Government can use the information in important decision to improve
literacy rate.
Database
A Database is a collection of related data organized in a way that data can be easily accessed,
managed and updated. By data, we mean known facts that can be recorded and that have implicit
meaning.
For example, consider the names, telephone numbers, and addresses of the people you know.
You may have recorded this data in an address book or you may have stored it on a hard drive,
using a personal computer and software such as Microsoft Access or Excel. This collection of
related data with an implicit meaning is a database.

Miniworld or Universe of Discourse (UoD):

A database represents some aspect of the real world, sometimes called the miniworld or the
universe of discourse (UoD). Changes to the miniworld are reflected in the database.

Database management system (DBMS)

A database management system (DBMS) is a collection of programs that enables users to create
and maintain a database. The DBMS is a general-purpose software system that facilitates the
processes of defining, constructing, manipulating, and sharing databases among various users
and applications.
Defining a database involves specifying the data types, structures, and constraints of the data to
be stored in the database. The database definition or descriptive information is also stored by the
DBMS in the form of a database catalog or dictionary; it is called meta-data.
Constructing the database is the process of storing the data on some storage medium that is
controlled by the DBMS.
Manipulating a database includes functions such as querying the database to retrieve specific
data, insert a data, delete a data and update a data.
Sharing a database allows multiple users and programs to access the database simultaneously.
Here are some examples of popular DBMS used these days:
 MySql
 Oracle
 SQL Server
 IBM DB2
 PostgreSQL
 Amazon SimpleDB (cloud based) etc

File Oriented Approach

In file oriented system data is going to be stored in the form of records in separate files, and so
they were called file processing systems.
In file oriented approach the programs are dependent on the files and files become dependents
upon the programs i.e. when the physical format of the file is changed, the program should also
be changed.
Disadvantages of file oriented approach:
 Data redundancy: The same information may be written in several files. This redundancy
leads to higher storage and access cost. It may lead to data inconsistency i.e. the various
copies of the same data may longer agree for example a changed customer address may be
reflected in single file but not else where in the system.
 Data inconsistency: The data in different files may be different that cause data
inconsistency.
 Difficulty in accessing data: The conventional file processing system do not allow data to
be retrieved in a convenient and efficient manner according to user choice.
 Data isolation: Since data are scattered in various file and files may be in different formats
with new application programs, retrieving the appropriate data is difficult.
 Integrity Problems: Developers enforce data validation in the system by adding appropriate
code in the various application program. However when new constraints are added, it is
difficult to change the programs to enforce them.
 Atomicity: It is difficult to ensure atomicity in a file processing system when transaction
failure occurs due to power failure, networking problems etc.
 Security problems: There is no security provided in file processing system to secure the data
from unauthorized user access.

Difference between File Processing System and Database system:

[Link] File based system Database system

1 Software that manages the data files Software to create and manages the databases
in a computer system
2 The data and program are inter- The data and program are independent of each
dependent. other.
3 File-based system caused data Database system control data redundancy. The
redundancy. The data may be data appeared only once in the system
duplicated in different files
4 File–based system caused data In database system data always consistent.
inconsistency. The data in different Because data appeared only once.
files may be different that cause data
inconsistency.
5 File based system is less complex Database system is very complex system
system
6 The cost of file processing system is The cost of database system is much more than
less than database system a file processing system
7 File based system does not provide Database system provides concurrency facility
concurrency facility
8 Data is organized in a distributed Data is maintained in a centralized manner
manner
 9 Maintenance is easy Maintenance is difficult
10 Task such as storing, retrieving and Operation such as updating, selecting and
searching are done manually. So it is searching a data is easier since it allows using
difficult to manage the data SQL queries
11 Backup and recovery process is not Has a sophisticated backup and recovery
efficient
12 Suitable for small scale organization Suitable for medium to large scale organization
or individual users or multiple users
13 Security is low in File Management Security is high in Database Management
System. System
14 File based system does not provide Database system provides data atomicity
data atomicity functionality functionality

Advantages of DBMS or Purpose of DBMS

 Controlling Redundancy: The file based data management systems contained multiple files
that were stored in many different locations in a system or even across multiple systems.
Because of this, there were sometimes multiple copies of the same file which lead to data
redundancy. This is prevented in a database as there is a single database and any change in it
is reflected immediately. Because of this, there is no chance of encountering duplicate data.
Centralized control of data by the DBA avoids unnecessary duplication of data and
effectively reduces the total amount of data storage required.

 Providing Storage Structures and Search Techniques for Efficient Query Processing:
Processing Database systems must provide capabilities for efficiently executing queries and
updates. Because the database is typically stored on disk, the DBMS must provide
specialized data structures to speed up disk search for the desired records. Auxiliary files
called indexes are used for this purpose. Indexes are typically based on tree data structures or
hash data structures, suitably modified for disk search.

 Providing Backup and Recovery: DBMS provides facilities to recover from hardware and
software failure. The backup and recovery subsystem of DBMS ensures that if a system fails
in the middle of any transaction then database is restored in the state from where transaction
is begin and when system is recovered it also ensure that transaction is resumed from the
point where it is interrupted . This is called atomicity (To recover all the system
automatically).

 Sharing of data: A database allows the sharing of data under its control by any number of
application programs or users.

 Restricting Unauthorized Access/Data Security: The DBA who has the ultimate
responsibility for the data in the dbms can ensure that proper access procedures are followed
 including proper authentication schemas for access to the DBS and additional check before
permitting access to sensitive data.
When multiple users share a large database, it is likely that most users will not be authorized
to access all information in the database. For example, financial data is often considered
confidential, and only authorized persons are allowed to access such data. In addition, some
users may only be permitted to retrieve data, whereas others are allowed to retrieve and
update. Hence, the type of access operation—retrieval or update—must also be controlled.
DBMS should provide a security and authorization subsystem, which the DBA uses to create
accounts and to specify account restrictions.

 Providing Multiple User Interfaces: a DBMS should provide a variety of user interfaces.
These include query languages for casual users, programming language interfaces for
application programmers, forms and command codes for parametric users, and menu-driven
interfaces and natural language interfaces for standalone users.

 Data Consistency: Data consistency is ensured in a database because there is no data

redundancy. All data appears consistently across the database and the data is same for all the
users viewing the database. Moreover, any changes made to the database are immediately
reflected to all the users and there is no data inconsistency.

 Data Independence: Data independence is usually considered from two points of views;
physically data independence and logical data independence. Physical data Independence
allows changes in the physical storage devices or organization of the files to be made without
requiring changes in the conceptual view or any of the external views and hence in the
application programs using the data base. Logical data independence indicates that the
conceptual schema can be changed without affecting the existing external schema or any
application program.

 Data Integrity: Data integrity means that the data contained in the database is both accurate
and consistent. Therefore data values being entered for storage could be checked to ensure
that they fall within a specified range and are of the correct format.

 Conflict resolution: DBA resolve the conflict on requirements of various user and
applications. The DBA chooses the best file structure and access method to get optional
performance for the application.

Disadvantage of DBMS

 Cost: DBMS software and hardware (networking installation) cost is high

 Complexity: The complexity of the database may result in poor performance.
 Qualified Personnel: The professional operation of a database system requires
appropriately trained staff. Without a qualified database administrator nothing will work for
long.
 Frequent upgrade/replacement cycles: DBMS vendors frequently upgrade their products
by adding new functionality. Such new features often come bundled in new upgrade
versions of the software.
 Setup of the database system requires more knowledge, money, skills, and time.
Applications of Database Management System

Let’s see some of the applications where database management system is used −
 Railway Reservation System − In 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 delay to people through the database.
 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.
  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.
 Universities and colleges − 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.
 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.
 Social Media Sites − By filling the required details we are able to access social media
platforms. Many users sign up daily on social websites such as Facebook, Pinterest and
Instagram. All the information related to the users are stored and maintained with the help
of DBMS.
 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.
 Military − In military areas the DBMS is playing a vital role. Military keeps records of
soldiers and it has so many files that should be kept secure and safe. DBMS provides a
high security to military information.
 Online Shopping − Now-a-days we all do Online shopping without wasting the time by
going shopping with the help of DBMS. The products are added and sold only with the
help of DBMS like Purchase information, invoice bills and payment.
 Human Resource Management − The management keeps records of each employee’s
salary, tax and work through DBMS.
 Manufacturing − Manufacturing companies make products and sell them on a daily
basis. To keep records of all those details DBMS is used.
 Airline Reservation system − Just like the railway reservation system, airlines also need
DBMS to keep records of flights arrival, departure and delay status.

Characteristics of database approach

The main characteristics of the database approach are as follows:

 Self-describing nature of a database system

 Insulation between programs and data, and data abstraction
 Support of multiple views of the data
 Sharing of data and multiuser transaction processing
 Control of data redundancy
 Easy to operate on data

Self-describing nature of a database system: A fundamental characteristic of the database

approach is that the database system contains not only the database itself but also a complete
definition or description of the database structure and constraints. This definition is stored in the
DBMS catalog, which contains information such as the structure of each file, the type and
storage format of each data item, and various constraints on the data. The information stored in
the catalog is called meta-data, and it describes the structure of the primary database. These
definitions are specified by the database designer prior to creating the actual database and are
stored in the catalog

Insulation between Programs and Data, and Data Abstraction: In traditional file processing,
the structure of data files is embedded in the application programs, so any changes to the
structure of a file may require changing all programs that access that file. By contrast, DBMS
access programs do not require such changes in most cases. The structure of data files is stored in
the DBMS catalog separately from the access programs. We call this property as program-data
independence.
The characteristic that allows program-data independence and program-operation independence
is called data abstraction. A DBMS provides users with a conceptual representation of data that
does not include many of the details of how the data is stored or how the operations are
implemented. Informally, a data model is a type of data abstraction that is used to provide this
conceptual representation.

Support of Multiple Views of the Data: A database supports multiple views of data. A view is
a subset of the database, which is defined and dedicated for particular users of the system.
Multiple users in the system might have different views of the system. Each view might contain
only the data of interest to a user or group of users
Sharing of Data and Multiuser Transaction Processing:
A multiuser DBMS, as its name implies, must allow multiple users to access the database at the
same time. This is essential if data for multiple applications is to be integrated and maintained in
a single database. The DBMS must include concurrency control software to ensure that several
users trying to update the same data do so in a controlled manner so that the result of the updates
is correct. For example, when several reservation agents try to assign a seat on an airline flight,
the DBMS should ensure that each seat can be accessed by only one agent at a time for
assignment to a passenger.

A fundamental role of multiuser DBMS software is to ensure that concurrent transactions operate
correctly and efficiently.

Control of data redundancy: In the database approach, ideally, each data item is stored in
only one place in the database. In some cases, data redundancy still exists to improve
system performance, but such redundancy is controlled by application programming and
kept to minimum by introducing as little redundancy as possible when designing the
database.
Easy to operate on data: Inserting more data, deleting unuseful data, updating, searching
etc is easy
People associated with database system:
People associated with database system can be classified into two types:
Actors on the Scene - People whose jobs involve the day-to-day use of a large database are
called as Actors on the scene.
Workers behind the Scene - People those who work to maintain the database system
environment but who are not actively interested in the database contents as part of their daily
job are called as Workers behind the scene.
Actors on the scene
The actors on the scene are:
• Database Administrators (DBA)
• Database Designers
• End Users
• System Analysts and Application Programmers (Software Engineers)

Database Administrators (DBA): In any organization there will be a leader who leads all the
structure, manage all the resources etc. So will be the chief administrator of the organization.
Like chief administrator, in DBMS DBA is the leader of database. DBA is responsible for
authorizing access to the database, monitoring its use and for acquiring software and hardware
 resources as needed. The DBA is accountable for problems such as security breaches and poor
system response time.

Database Designers: Database designers are responsible for selecting appropriate structure to
represent and store the data. Before selecting the structure, Designer will meet all the database
users and find out all the requirements which they want to fulfill. Database designers typically
interact with each potential group of users and develop views of the database that meet the data
and processing requirements of these groups. Each view is then analyzed and integrated with the
views of other user groups.
It is the responsibility of database designers to communicate with all prospective database users
in order to understand their requirements and to create a design that meets these requirements.
The final database design must be capable of supporting the requirements of all user groups.
After collect all the requirement it comes with their design and structure and this work is
completed before database implementation.

End Users: End users are the users, who use the applications developed. End users need not
know about the working, database design, the access mechanism etc. They just use the system to
get their task done.
The Different end users are as follows:
1) Casual Users: These are the users who communicate with the database for little period of
time. They occasionally use the database. So these are the temporary users.
2) Parametric or Naive Users: These are the users who communicate with the database for a
regular period. Their main job is constantly querying and updating the database using standard
queries called canned transactions—that have been carefully programmed and tested. The
tasks that such users perform are varied:
_ Bank tellers check account balances and post withdrawals and deposits.
_ Reservation agents for airlines, hotels, and car rental companies check availability for a given
request and make reservations.
3) Sophisticated End Users: These are engineers, scientists, business analysts who are
familiarize themselves with the facilities of the DBMS in order to implement their own
applications to meet their complex requirements.
4) Standalone users: These are the users who maintain personal databases by using ready-
made program packages that provide easy-to-use menu-based or graphics-based interfaces. An
example is the user of a tax package that stores a variety of personal financial data for tax
purposes.
System Analysts and Application Programmers: System analysts determine the requirements
of end users, especially naive and parametric end users, and develop specifications for standard
canned transactions that meet these requirements.
Application programmers implement these specifications as programs; then they test, debug,
document, and maintain these canned transactions. Such analysts and programmers—commonly
referred to as software developers or software engineers—should be familiar with the full range
of capabilities provided by the DBMS to accomplish their tasks.

Workers behind the Scene

Workers behind the scene are:

 DBMS system designers and implementers

 Tool Developers
 Operators and maintenance personnel (system administration personnel)

DBMS system designers and implementers: DBMS system designers and implementers design
and implement the DBMS modules and interfaces as a software package. A DBMS is a very
complex software system that consists of many components, or modules, including modules for
implementing the catalog, query language processing, interface processing, accessing and
buffering data, controlling concurrency, and handling data recovery and security. The DBMS
must interface with other system software such as the operating system and compilers for various
programming languages.

Tool developers: Tool developers design and implement tools—the software packages that
facilitate database modeling and design, database system design, and improved performance.
Tools are optional packages. They include packages for database design, performance
monitoring, natural language or graphical interfaces, prototyping, simulation, and test data
generation. In many cases, independent software vendors develop and market these tools.

Operators and maintenance personnel (system administration personnel): They are

responsible for the actual running and maintenance of the hardware and software environment
for the database system.
What is View?
A view is a subset of the database, which is defined and dedicated for particular users of the
system. Each view might contain only the data of interest to a user or group of users.
Example:

The views that can be derived from the above database are:
Data Models
A Data Model is a collection of concepts that can be used to describe the structure of a database.
Data Models are fundamental entities to introduce abstraction in a DBMS. Data models define
how data is connected to each other and how they are processed and stored inside the system.

Data models can be broadly classified as :

 Hierarchical Model
 Network Model
 Relational Model
 Object-Oriented Model
 Entity-relationship model

Relational Model:
Principle in a relational database is the table, a tabular arrangement of data values. Data is stored
in tables called relations.

 A table is called a relation.

 The row (or record) in the table is called a tuple
 The column (or field) is called an attribute.
 The number of tuples is called the cardinality
 The number of attributes is called the degree of the table
 Each row in a relation contains a unique value.
Network Model:
Network model is a collection of data in which records are physically linked through linked
lists .A DBMS is said to be a Network DBMS if the relationships among data in the database
are of type many-to-many. The relationships among many-to-many appear in the form of a
network. Thus the structure of a network database is extremely complicated because of these
many-to-many relationships in which one record can be used as a key of the entire database.

Hierarchical Model:
The Hierarchical Data Model is a way of organizing a database with multiple one to many
relationships. The structure is based on the rule that one parent can have many children but
children are allowed only one parent. The organization of the records is as a collection of
trees.
 Object-Oriented Model:
Object DBMSs add database functionality to object programming languages. They bring
much more than persistent storage of programming language objects. The object-oriented
model is based on a collection of objects. As a result, applications require less code, use more
natural data modeling.

Entity Relationship (ER) Model:

The entity-relationship (E-R) model is the most popular conceptual model used for designing
a database.
The E-R model views the real world as a set of basic objects (known as entities), their
characteristics (known as attributes), and associations among these objects (known as
relationships). The entities, attributes, and relationships are the basic constructs of an E-R
model.

Example:
Database Schema
The overall design of the database is called the database schema or the overall description of the
database is called the database schema. A database schema is the skeleton structure that
represents the logical view of the entire database. It defines how the data is organized and how
the relations among them are associated. A displayed schema is called a schema diagram
It formulates all the constraints that are to be applied on the data. Schemas provide a logical
classification of objects in the database. It contains a descriptive detail of the database, which can
be depicted by means of schema diagrams. A schema contains schema objects like table, foreign
key, primary key, views, columns, data types, stored procedure, etc.
A schema can be defined as the design of a database. It can be categorized into three parts. These
are:
 Physical Schema
 Logical Schema
 View Schema

A physical schema/Internal Schema can be defined as the design of a database at its

physical level. In this level, it is expressed how data is stored in blocks of storage.
A logical schema/Conceptual Schema can be defined as the design of the database at its
logical level. In this level, the programmers as well as the database administrator (DBA)
work. However, the internal details (such as an implementation of data structure) will be
remaining hidden at this level.
View schema/External Schema can be defined as the design of the database at view level
which generally describes end-user interaction with database systems

Database Instance OR Database State OR Snapshot

The data in the database at a particular moment in time is called a database state or snapshot. It
is also called the current set of occurrences or instances in the database.

What is the difference between Database Instance and Database Schema?

The description of a database is called as database schema whereas the data in the database at a
particular moment in time is called as database instance.
Three Schema Architecture or Three Level Architecture:

The three schema architecture is also called ANSI/SPARC architecture or three-level

architecture. This framework is used to describe the structure of a specific database system. The

three schema architecture is also used to separate the user applications and physical database.

The three schema architecture contains three-levels. It breaks the database down into three

different categories. They are:

 Internal Level/Physical Schema

 Conceptual Level/ Conceptual Schema

 External Level/View Schema

 Internal Level
The internal level has an internal schema which describes the physical storage structure of
the database. The internal schema is also known as a physical schema. It uses the physical
data model. It is used to define that how the data will be stored in a block. The physical level
is used to describe complex low-level data structures in detail.
Conceptual Level
The conceptual schema describes the design of a database at the conceptual level. Conceptual
level is also known as logical level. The conceptual schema describes the structure of the
whole database. The conceptual level describes what data are to be stored in the database and
also describes what relationship exists among those data. In the conceptual level, internal
details such as an implementation of the data structure are hidden. Programmers and database
administrators work at this level.
External Level
The external level is used to describe the different view of the database. An external schema
is also known as view schema. Each view schema describes the database part that a particular
user group is interested and hides the remaining database from that user group. The view
schema describes the end user interaction with database systems.
The reason this level is called “view” is because several users can view their desired data
from this level which is internally fetched from database with the help of conceptual and
internal level mapping. The user doesn’t need to know the database schema details such as
data structure, table definition etc. user is only concerned about data which is what returned
back to the view level after it has been fetched from database (present at the internal level).

Data Independence
Data Independence is defined as a property of DBMS that helps you to change the Database
schema at one level of a database system without requiring to change the schema at the next
higher level. Data independence helps you to keep data separated from all programs that make
use of it.

There are two types of data independence:

Logical data independence: Logical data independence refers characteristic of being able to
change the conceptual schema without having to change the external schema. Logical data
independence is used to separate the external level from the conceptual view. If we do any
changes in the conceptual view of the data, then the user view of the data would not be
affected.
 Examples of changes under Logical Data Independence are:
 Add/Modify/Delete a new attribute, entity or relationship is possible without a rewrite of
existing application programs
 Merging two records into one

Physical data independence: Physical data independence can be defined as the capacity to
change the internal schema without having to change the conceptual schema. If we do any
changes in the storage size of the database system server, then the Conceptual structure of the
database will not be affected. Physical data independence is used to separate conceptual
levels from the internal levels.
Examples of changes under Physical Data Independence are:
 Using a new storage device like Hard Drive or Magnetic Tapes
 Modifying the file organization technique in the Database
 Switching to different data structures.
 Changing the access method.
 Change of Location of Database from say C drive to D Drive

Fig : Data Independence

Database Languages:
A DBMS has appropriate languages and interfaces to express database queries and updates.
Database languages can be used to read, store and update the data in the database.
 Data Definition Language (DDL) : It is used to define database structure or pattern. It is
used to create schema, tables, indexes, constraints, etc. in the database. Using the DDL
statements, we can create the skeleton of the database. Data definition language is used to
store the information of metadata like the number of tables and schemas, their names,
indexes, columns in each table, constraints, etc. Here are some tasks that come under DDL:
Create: It is used to create objects in the database such as tables, views, instances
etc..
Alter: It is used to alter the structure of the database. This command is used to
add, delete or change columns in the existing table.
Drop: It is used to delete objects from the database.
Truncate: It is used to remove all records from a table, but the structure of the
table still exists.
Rename: It is used to rename an object.
Comment: It is used to comment on the data dictionary. Comments are of two
types. (a) Single line comments (b) Multiline comments
Single line comments starts with - -
Example:
- - select all the content from Customer table
Select * from Customer;
Multiline comments starts with /* and ends with */
Example:
/* select all the columns of all
The records in the Customer table*/
Select * from Customer;
These commands are used to update the database schema that's why they come under
Data definition language.
Syntax of Create command with example:
CREATE TABLE table_name
(
column_1 datatype,
column_2 datatype,
column_3 datatype,
....
);
Example: –
We need to create a table for storing Student information of a particular College.
CREATE TABLE Student_info
(
College_Id number(2),
College_name varchar(30),
Branch varchar(10)
);
Syntax of Alter command with example:
 Syntax to add a column to an existing table:
ALTER TABLE table_name
ADD column_name datatype;
Example: –
In our Student_info table, we want to add a new column for mobile number. The syntax would
be as below as follows.
ALTER TABLE Student_info
ADD Mobile_no number(10);
 Syntax to change the datatype of a column in an existing table:
ALTER TABLE table_name
MODIFY column_name datatype;
Example:
In our Student_info table, we want to change the datatype of Branch column from varchar to
char.. The syntax would be as below as follows.
ALTER TABLE Student_info
MODIFY Branch char(6);
Syntax of Drop command with example:
Syntax to drop an existing table:
DROP TABLE table_name;
Example:
If the College Authority wants to change their Database by deleting the Student_info Table.
DROP TABLE Student_info;
Syntax of Truncate command with example:
Syntax to remove an existing table:
TRUNCATE TABLE table_name;
Example:
The College Authority wants to remove the details of all students for new batches but wants to
keep the table structure. The command they can use is as follows.
TRUNCATE TABLE Student_info;
Syntax of Rename command with example:
 Syntax to Rename the existing table:
ALTER TABLE table_name
RENAME TO new_table_name;
Example:
ALTER TABLE Student
RENAME TO Student1;
 Syntax to Rename an existing column:

ALTER TABLE table_name

RENAME COLUMN old_name TO new_name;
Example:
ALTER TABLE Student
RENAME COLUMN Student_name TO S_name;

 Storage Definition Language (SDL), is used to specify the internal schema. The
mappings between the two schemas may be specified in either one of these languages.
 View Definition Language (VDL), to specify user views and their mappings to the
conceptual schema, but inmost DBMSs the DDL is used to define both conceptual and
external schemas.
 Data Manipulation Language (DML) : It is used for accessing and manipulating data in a
database. It handles user requests. Here are some tasks that come under DML:
Select: It is used to retrieve data from a database table which returns this data in
the form of a result table known as Result set.
Insert: It is used to insert data into a table.
Update: It is used to update existing data within a table.
Delete: It is used to delete all records from a table.
Call : It is used to call stored procedures which are the sub routines stored in
SQL catalog.
Merge: It combines Insert, Update and Delete operations into a single statement.
Explain plan: it displays the execution plan chosen by the Oracle optimizer for
Select, Update, Insert and Delete statements.

 Procedural Data Manipulation Language require a user to specify what data are
needed and how to get those data, The DML component of the PL/SQL language is
procedural.
 Nonprocedural Data Manipulation Language require a user to specify what data are
needed without specifying how to get those data. The DML component of the SQL
language is nonprocedural.

Syntax of Select command with example:

 Syntax to retrieve only specific fields from the table:

SELECT column1, column2,….., columnN FROM table_name;

Example:

SELECT Reg_no, Name, Age FROM students;

 Syntax to retrieve all the fields from the table:

SELECT * FROM table_name;

Example :
SELECT * FROM students;

Syntax of Insert command with example:

INSERT INTO table_name VALUES ('value1', 'value2' , ….. , ‘valueN’);
Example :
INSERT INTO students VALUES ('1', 'Ajay', 'Mysore');
Syntax of Update command with example:
UPDATE table_name SET column_name = value WHERE [condition];
Example :
UPDATE students SET due_fees = 20000 WHERE stu_name = 'Rahul';

Syntax of Delete command with example:

DELETE FROM table_name WHERE [condition];
Example :
DELETE FROM students WHERE stu_id = '001';

 Data Control Language (DCL): DCL commands are used to enforce database security
in a multiple user database environment. Only DBA’s or owners of the database object
can provide/ revoke privileges on a database object. Some commands that come under
DCL are:

Grant: It is used to give user access privileges to a database.

Revoke: It is used to take back permissions from the user.
Syntax of Grant and Revoke commands with example:
GRANT privilege_name on object_name TO {user_name/ public/ role_name}
REVOKE privilege_name on object_name FROM {user_name/ public/ role_name}
where,
privilege_name – is which persmision has to be granted. Some access rights are All, Execute,
Select.
object_name – is the name of the database object such as table, view, stored procedure,
sequence.
user_name – is the user to which access should be provided.
public – is used to permit access to all the users.
roles- are set of privileges grouped together.

Example:
GRANT insert,
Select on Accounts to Ram
{User Ram has granted permissions on Accounts database object like he can query or insert into
Accounts.}
REVOKE insert,
Select on Accounts to Ram
{User Ram’s permissions like query/insert on Accounts database object has been removed}
 Transaction Control Language (TCL) : These commands are used for maintaining
consistency of the database and for the management of transactions made by the DML
commands.
 Transaction is a set of SQL statements that are executed on the data stored in dbms.
Whenever any transaction is made, these transactions are temporarily happen in database, but
to make the changes permanent, we use TCL commands. It is used to run the changes made
by the DML statement. Here are some commands that come under TCL:

Commit: It is used to save the transaction on the database. This command is

used to save the data permanently.
Whenever we perform any of the DML command like -INSERT, DELETE or
UPDATE, these can be rollback if the data is not stored permanently. So in
order to be at the safer side COMMIT command is used.
Syntax:
commit;
Rollback: It is used to restore the database to original since the last Commit.
This command is used to get the data or restore the data to the last savepoint or
last committed state. If due to some reasons the data inserted, deleted or
updated is not correct, you can rollback the data to a particular savepoint or if
savepoint is not done, then to the last committed state.
Syntax:
rollback;
Save Point: Set a point in transaction to rollback later. This command is used
to save the data at a particular point temporarily, so that whenever needed can
be rollback to that particular point.
Syntax:
savepoint A;
Consider the following Table Student:
By using this command you can update the record and save it permanently by
using COMMIT command.
Now after COMMIT :
Database Interfaces :
A database management system (DBMS) interface is a user interface which allows for the ability
to input queries to a database without using the query language itself.

User-friendly interfaces provide by DBMS may include the following:

 Menu-Based Interfaces for Web Clients or Browsing :

These interfaces present the user with lists of options (called menus) that lead the user
through the formation of a request. Pull-down menus are a very popular technique in Web
based interfaces. They are also often used in browsing interface which allow a user to look
through the contents of a database in an exploratory and unstructured manner.
 Forms Based Interfaces: A forms-based interface displays a form to each user. Users can
fill out all of the form entries to insert new data, or they fill out only certain entries, in which
case the DBMS will retrieve matching data for the remaining entries. Forms are usually
designed and programmed for naive users as interfaces to canned transactions
 Graphical User Interfaces: A graphical interface (GUI) typically displays a schema to the
user in diagrammatic form. The user can then specify a query by manipulating the diagram.
In many cases, GUIs utilize both menus and forms. Most GUIs use a pointing device, such as
a mouse, to pick certain parts of the displayed schema diagram.

 Natural Language Interfaces: These interfaces accept requests written in English or some
other language and attempt to "understand" them. A natural language interface usually has its
own "schema," which is similar to the database conceptual schema, as well as a dictionary of
important words. The natural language interface refers to the words in its schema, as well as
to the set of standard words in its dictionary, to interpret the request.

 Interfaces for Parametric Users: Parametric users, such as bank tellers, often have a small
set of operations that they must perform repeatedly. Systems analysts and programmers
 design implement a special interface for naïve users. Usually, a small set of abbreviated
commands is included, with the goal of minimizing the number of keystrokes required for
each request.
 Interfaces for the DBA: Most database systems contain privileged commands that can be
used only by the DBA's staff. These include commands for creating accounts, setting system
parameters, granting account authorization, changing a schema, and reorganizing the storage
structures of a database.
 Speech Input and Output: There is an limited use of speech say it for a query or an answer
to a question or being a result of a request it is becoming commonplace Applications with
limited vocabularies such as inquiries for telephone directory, flight arrival/departure, and
bank account information are allowed speech for input and output to enable ordinary folks to
access this information.
The Speech input is detected using a predefined words and used to set up the parameters that
are supplied to the queries. For output, a similar conversion from text or numbers into speech
take place.

E-R Model: Entity-Relationship Modeling

Entity
An Entity may be an object with a physical existence – a particular person, car, house, or
employee – or it may be an object with a conceptual existence – a company, a job, or a university
course.
An entity can be of two types:

Tangible Entity: Tangible Entities are those entities which exist in the real world
physically. Example: Person, car, etc.

Intangible Entity: Intangible Entities are those entities which exist only logically and have no
physical existence. Example: Bank Account, etc.
Example: If we have a table of a Student (Roll_no, Student_name, Age, Mobile_no) then each
student in that table is an entity and can be uniquely identified by their Roll Number i.e
Roll_no.

Note: In E-R model we don't represent the data but we represent the structure or schema.
When we convert E-R model to relational model then data can be stored in tuple or row and
hence, represented as an entity.
Entity Type
The entity type is a collection of the entity having similar attributes.

The E-R representation of the above Student Entity Type is done below.

Types of Entity type

 Strong Entity Type

 Weak Entity Type
 Strong Entity Type: Strong entities are those entity types which has a key attribute. The
primary key helps in identifying each entity uniquely. It is represented by a rectangle.

In the above example, Roll_no identifies each element of the table uniquely and hence, we
can say that STUDENT is a strong entity type.

Weak Entity Type: Weak entity type doesn't have a key attribute. Weak entity type
can't be identified on its own. It depends upon some other strong entity for its distinct
identity.

For example :There can be children only if the parent exits. There can be no independent
existence of children.

A weak entity is represented by a double outlined rectangle. The relationship between a

weak entity type and strong entity type is called an identifying relationship and shown with
a double outlined diamond instead of a single outlined diamond. This representation can be
seen in the diagram below.

Example: If we have two tables of Customer(Customer_id, Name, Mobile_no, Age, Gender)

and Address(Locality, Town, State, Customer_id). Here we cannot identify the address
uniquely as there can be many customers from the same locality. So, for this, we need an
attribute of Strong Entity Type i.e ‘Customer’ here to uniquely identify entities of 'Address'
Entity Type.
Entity Set
Entity Set is a collection of entities of the same entity type. In the above example of
STUDENT entity type, a collection of entities from the Student entity type would form an
entity set. We can say that entity type is a superset of the entity set as all the entities are
included in the entity type.

Example 1: In the below example, two entities E1 (2, Angel, 19, 8709054568) and E2(4,
Analisa, 21, 9847852156) form an entity set.
Attribute(s):
Attributes are the properties which define the entity type. For example, Roll_No, Name, DOB,
Age, Address, Mobile_No are the attributes which defines entity type Student. In ER diagram,
attribute is represented by an oval.

1. Key Attribute –
The attribute which uniquely identifies each entity in the entity set is called key attribute. For
example, Roll_No will be unique for each student. In ER diagram, key attribute is represented by
an oval with underlying lines.

2. Composite Attribute –
An attribute composed of many other attribute is called as composite attribute. For example,
Address attribute of student Entity type consists of Street, City, State, and Country. In ER
diagram, composite attribute is represented by an oval comprising of ovals.
3. Multivalued Attribute –
An attribute consisting more than one value for a given entity. For example, Phone_No (can be
more than one for a given student). In ER diagram, multivalued attribute is represented by
double oval.

4. Derived Attribute –
An attribute which can be derived from other attributes of the entity type is known as derived
attribute. e.g.; Age (can be derived from DOB). In ER diagram, derived attribute is represented
by dashed oval.

5. Stored Attribute: Stored Attribute is a attribute which derives the other attribute. A derivable
attribute is called stored attribute. Example Birth date is the stored attribute and age is the
derived attribute.

DOB
dateDate
Date

6. Complex Attributes
The composite and multi-valued attributes can be nested in an arbitrary way is called complex
attributes.
Example: A person with more than one address and phone number.

{AddressPhone( {Phone(AreaCode, PhoneNumber) }, Address(StreetAddress(Number, Street,

ApartmentNumber), City, State, Zip) ) }

The complete entity type Student with its attributes can be represented as:

Domain of an Attribute:
Attribute domains are rules that describe the available values of a field type. They are used to
constrain the values allowed in any particular attribute for a table or feature class. They provide a
method for enforcing data integrity by limiting what can be placed on a field to a valid list or
range of choices.

Relationships between the entities:

Relation: A relationship is used to describe the relation between entities or The association
among entities is called a relationship.

Relationship Type and Relationship Set:

A relationship type represents the association between entity types. For example,‘Enrolled in’
is a relationship type that exists between entity type Student and Course. In ER diagram,
relationship type is represented by a diamond and connecting the entities with lines.
A set of relationships of same type is known as relationship set. The following relationship set
depicts S1 is enrolled in C2, S2 is enrolled in C1 and S3 is enrolled in C3.

Degree of a relationship set:

The number of different entity sets participating in a relationship set is called as degree of a
relationship set.

1. Unary Relationship –
When there is only ONE entity set participating in a relation, the relationship is called as
unary relationship. For example, one person is married to only one person.

2. Binary Relationship -
When there are TWO entities set participating in a relation, the relationship is called as binary
relationship. For example, Student is enrolled in Course.
3. Ternary Relationship –

When there are THREE entities set participating in a relation, the relationship is called as
ternary binary relationship. For example, Employee works in particular department and also
employee is going to work in particular location.

4. n-ary Relationship –
When there are n entities set participating in a relation, the relationship is called as n-ary
relationship.

Role Name and Recursive Relationships

Each entity type that participates in a relationship type plays a particular role in the relationship.
The role name signifies the role that a participating entity from the entity type plays in each
relationship instance. It helps to explain what the relationship means.

Example: in the WORKS_FOR relationship type, EMPLOYEE plays the role of employee or
worker and DEPARTMENT plays the role of department or employer. In some cases the same
entity type participate more than once in a relationship type in different roles. In such cases, the
role name becomes essential for distinguishing the meaning of each participation. Such
relationships types are called recursive relationships.
Constrains on Relationship Types:
Two main types of relationship constraints: cardinality ratio and participation.

 Cardinality ratio
 Participation Constraints

Cardinality Ratio:
The number of times an entity of an entity set participates in a relationship set is known as
cardinality. OR The cardinality ratio for a binary relationship specifies the maximum number of
relationship instances that an entity can participate in.

 One to one (1:1)

 One to many (1:M)
 Many to one (M:1)
 Many to many (M:N)

1. One to one – When each entity in each entity set can take part only once in the relationship,
the cardinality is one to one. Let us assume that a male can marry to one female and a female can
marry to one male. So the relationship will be one to one.

Using Sets, it can be represented as:

2. One to many – When entities in one entity set can take part more than once in the relationship
set and entities in other entity set can take part only once in the relationship set.

Here, one mother can have many children and many children belong to a single mother.

3. Many to one – When entities in one entity set can take part only once in the relationship set
and entities in other entity set can take part more than once in the relationship set.

Let us assume that a student can take only one course but one course can be taken by many
students. So the cardinality will be M to 1. It means that for one course there can be n students
but for one student, there will be only one course.

Using Relationship Sets, it can be represented as:

4. Many to many – When entities in all entity sets can take part more than once in the
relationship cardinality is many to many.
Ex 1: Let us assume that a student can take more than one course and one course can be taken by
many students. So the relationship will be many to many.

Using sets, it can be represented as:

In this example, student S1 is enrolled in C1 and C3 and Course C3 is enrolled by S1, S3 and S4.
So it is many to many relationships.
Ex 2: Let us assume that an N number of employee can works for M number of projects in an
organization.
Using Relationship Sets, it can be represented as:

Participation Constraint :
Participation constraint is applied on the entity participating in the relationship set.
This constraint specifies the number of instances of an entity that can participate in a relationship
type. There are two types of Participation constraint:
1. Total Participation-It specifies that each entity in the entity set must compulsorily participate
in at least one relationship instance in that relationship set. That is why, it is also called as
mandatory participation.

Total participation is represented using a double line between the entity set and relationship set.

Here Double line between the entity set “Student” and relationship set “Enrolled in” signifies
total participation. It specifies that each student must be enrolled in at least one course.

2. Partial Participation-

It specifies that each entity in the entity set may or may not participate in the relationship
instance in that relationship set. That is why, it is also called as optional participation.

Partial participation is represented using a single line between the entity set and relationship set.
The diagram depicts the ‘Enrolled in’ relationship set with Student Entity set having total
participation and Course Entity set having partial participation.

Using set, it can be represented as,

Every student in Student Entity set is participating in relationship but there exists a course C4
which is not taking part in the relationship.
Structural Constraints of Relationships in ER Model
To understand Structural Constraints, we must take a look at Cardinality Ratios and
Participation Constraints.

Cardinality Ratios of relationships : The entities are denoted by rectangle and relationships
are diamond.

There are numbers (represented by M and N) written above the lines which connect relationships
and entities. These are called cardinality ratios. These represent the maximum number of entities
that can be associated with each other through relationship, R.
Types of Cardinality : There can be 4 types of cardinality –

Participation Constraints : Participation Constraints tell us that the participation in a

relationship can either be total or
partial.

When each entity in an entity set participates in a relation, it is called Total Participation.
However, when all entities in the given entity set do not participate in a relation, it is
called Partial Participation.
Structural Constraints: Structural Constraints are also called Structural properties of a database
management system (DBMS). Cardinality Ratios and Participation Constraints taken together are
called Structural Constraints. The name constraints refer to the fact that such limitations must be
imposed on the data, for the DBMS system to be consistent with the requirements.

The Structural constraints are represented by Min-Max notation. This is a pair of numbers (m,
n) that appear on the connecting line between the entities and their relationships. The minimum
number of times an entity can appear in a relation is represented by m whereas, the maximum
time it is available is denoted by n. If m is 0 it signifies that the entity is participating in the
relation partially, whereas, if m is either greater than or equal to 1, it denotes total participation
of the entity.
Note – Number of times an entity participates in a relationship is same as the number appearance
of the entity in the tuples.

Strong entity:
An entity type should have a key attribute which uniquely identifies each entity in the entity set
is called strong entity. The Strong Entity is the one whose existence does not depend on the
existence of any other entity in a schema. It is denoted by a single rectangle.

 Two strong entity sets “Student” and “Course” are related to each other.
 Student ID and Student name are the attributes of entity set “Student”.
 Student ID is the primary key using which any student can be identified uniquely.
 Course ID and Course name are the attributes of entity set “Course”.
 Course ID is the primary key using which any course can be identified uniquely.
 Weak Entity Types
Weak entity is an entity that depends on another entity. Weak entity doesn't have any key
attribute of its own. Weak entity is depended on strong entity to ensure the existence of weak
entity. Weak entity is represented by 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.

For example, A company may store the information of dependents (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.
Difference between Strong Entity and Weak Entity:

BASIS FOR
STRONG ENTITY WEAK ENTITY
COMPARISON

Basic The Strong entity has a primary key. The weak entity has a
partial discriminator key.

Depends The Strong entity is independent of any Weak entity depends on the
other entity in a schema. strong entity for its
existence.

Denoted Strong entity is denoted by a single Weak entity is denoted with

rectangle. the double rectangle.

Relation The relation between two strong entities is The relationship between a
denoted by a single diamond simply called weak and a strong entity is
relationship. denoted by Identifying
Relationship denoted with
double diamond.

Participation Strong entity may or may not have total Weak entity always has
participation in the relationship. total participation in the
identifying relationship
shown by double line.

Entity Relationship Diagram – ER Diagram in DBMS

An Entity–relationship model (ER model) describes the structure of a database with the help of
a diagram, which is known as Entity Relationship Diagram (ER Diagram). An ER model is a
design or blueprint of a database that can later be implemented as a database. The main
components of E-R model are: entity set and relationship set.
What is an Entity Relationship Diagram (ER Diagram)?

An ER diagram shows the relationship among entity sets. An entity set is a group of similar
entities and these entities can have attributes. In terms of DBMS, an entity is a table or attribute
of a table in database, so by showing relationship among tables and their attributes, ER diagram
shows the complete logical structure of a database. Lets have a look at a simple ER diagram to
understand this concept.

A simple ER Diagram:

In the following diagram we have two entities Student and College and their relationship. The
relationship between Student and College is many to one as a college can have many students
however a student cannot study in multiple colleges at the same time. Student entity has
attributes such as Stu_Id, Stu_Name & Stu_Addr and College entity has attributes such as
Col_ID & Col_Name.
The notations used in ER-Diagram are as follows:
As above image describes,

 Rectangle – It represents entity in the ER Model.

 Ellipse – It represents attribute in the ER Model.
 Diamond – It represents relationship between entity and attribute.
 Line – It links attribute(s) to entity set(s) and entity set(s) to relationship set(s).
 Doubles Ellipses – It represents multivalued attributes.
 Dashed Ellipses – It denotes derived attributes.
 Double lines – It indicates total participation of an entity in a relationship set.
 Double Rectangle – It represents weak entity set.
 Double Diamonds – It represents weak relationships.
 Multiple ellipses connected to single ellipse using lines – It represents composite attribute
 Ellipse with line inside it – It represents single values attributes
 1:1,1:M,M;1,M;N – represents the cardinality ratio

Some Important ER diagrams:

1. ER diagram of a Customer placing Order of a Product:
2. ER diagram of Student database:
3. ER diagram of a Company Database
4. ER diagram of a Hospital Management System:
5. ER diagram of Airline Databese System:
6. ER diagram of a Bank database