MSC(CA) – Semester – I

Subject: - Database Systems and SQL

Semester No. I

Unit 1: Introduction to DBMS

1.1DBMS Overviews:

A Database Management System (DBMS) is a software system that is

designed to manage and organize data in a structured manner. It allows users
to create, modify, and query a database, as well as manage the security and
access controls for that database.
DBMS provides an environment to store and retrieve the data in convenient
and efficient manner.
Key Features of DBMS
 Data modeling: A DBMS provides tools for creating and modifying data
models, which define the structure and relationships of the data in a
database.
 Data storage and retrieval: A DBMS is responsible for storing and
retrieving data from the database, and can provide various methods for
searching and querying the data.
 Concurrency control: A DBMS provides mechanisms for controlling
concurrent access to the database, to ensure that multiple users can access
the data without conflicting with each other.
 Data integrity and security: A DBMS provides tools for enforcing data
integrity and security constraints, such as constraints on the values of data
and access controls that restrict who can access the data.
 Backup and recovery: A DBMS provides mechanisms for backing up and
recovering the data in the event of a system failure.
 DBMS can be classified into two types: Relational Database Management
System (RDBMS) and Non-Relational Database Management System
(NoSQL or Non-SQL)
 RDBMS: Data is organized in the form of tables and each table has a set of
rows and columns. The data are related to each other through primary and
foreign keys.
 NoSQL: Data is organized in the form of key-value pairs, documents,
graphs, or column-based. These are designed to handle large-scale, high-
performance scenarios.
A database is a collection of interrelated data which helps in the efficient
retrieval, insertion, and deletion of data from the database and organizes the
data in the form of tables, views, schemas, reports, etc. For Example, a
university database organizes the data about students, faculty, admin staff, etc.
which helps in the efficient retrieval, insertion, and deletion of data from it.

1.2 Advantages of DBMS

Some of the great advantages of DBMS are listed below:

1. Better use of data or information - We can easily and efficiently access

well-managed and synchronized forms of data with the help of DBMS. It
makes data handling simple, provides an integrated perspective of how a
certain business is operating and also aids in keeping track of how one
element of the business affects another portion.
2. Secured Data - The likelihood of security problems increases as a
database becomes more functional and accessible. The danger to data
security rises as a result of the rate at which data is shared or transferred
growing along with the user base. It is frequently utilized in the business
world where organizations spend a lot of time, money, and effort making
sure data is protected and handled effectively. Data management systems
(DBMS) offer a stronger framework for data privacy and security
policies, assisting businesses in enhancing data security.
3. Reduces Data Inconsistency and Redundancy - The major issues faced
during the process of storing data are inconsistency and redundancy.
Inconsistent data may lead to a big loss to an individual or a business
model and the storage capacity is not utilized properly because of the data
redundancy. When multiple copies with different versions or values of
the same data exist in various locations, then it causes inconsistency. Data
Redundancy and inconsistency can both be significantly decreased by
properly designing a database with the help of a database management
system.
4. Better Recovery and Backups - Backup and recovery are handled
automatically by the DBMS. Users don't need to regularly back up their
data because the DBMS handles this for them. Additionally, it returns the
database to its prior state following a crash or system failure.
 5. Fast Data Sharing - Database administration makes it possible for
consumers to access more and better-managed data. DBMS enables end
users to quickly scan their environment and react to any alterations made
there.
6. Helps in decision-making - Because of the well-managed data and
improved data access provided by DBMS, we are able to produce better-
quality information and, as a result, make better judgments. Accuracy,
validity, and the time it takes to read data are all improved by better data
quality. Although DBMS does not ensure data quality, it does offer a
framework that makes it simple to enhance data quality.
7. Increases Privacy - The privacy rule in a database specifies the privacy
restrictions that can only be accessed by authorized users. A user can only
view the data he is permitted to view since there are different degrees of
database access. For instance, on social networking sites, different
accounts that a user wishes to access have varying access restrictions and
a user can only see his/her account details, not others.
8. User Friendly - Data are presented in a straightforward and logical
manner by database management systems (DBMS). It is simple to carry
out many activities, such as the addition, deletion, or creation of files or
data.
9. Data Abstraction - In order to give users an abstract overview of the data,
database systems are primarily used. Since numerous intricate algorithms
are employed by developers to boost the effectiveness of databases that
are concealed from users by several degrees of data abstraction,
consumers can easily engage with the system.

In simple words, DBMS reduces data inconsistency and redundancy, increases

data security and privacy, and provides a platform to access and share data
faster.

1.3Users of DBMS

A Database User is defined as a person who interacts with data daily,

updating, reading, and modifying the given data. Database users can access
and retrieve data from the database through the Database Management System
(DBMS) applications and interfaces.
 Types of Database Users
Database users are categorized based on their interaction with the database.
There are seven types of database users in DBMS. Below mentioned are the
types of database users:
1. Database Administrator (DBA)
A Database Administrator (DBA) is a person/team who defines the schema
and also controls the 3 levels of the database. The DBA will then create a new
account ID and password for the user if he/she needs to access the database.
DBA is also responsible for providing security to the database and he allows
only authorized users to access/modify the database. DBA is responsible for
problems such as security breaches and poor system response time.
 DBA also monitors the recovery and backup and provides technical
support.
 The DBA has a DBA account in the DBMS which is called a system or
superuser account.
 DBA repairs damage caused due to hardware and/or software failures.
 DBA is the one having privileges to perform DCL (Data Control
Language) operations such as GRANT and REVOKE, to allow/restrict a
particular user from accessing the database .
2. Naive / Parametric End Users
Parametric End Users are the unsophisticated who don’t have any DBMS
knowledge but they frequently use the database applications in their daily life
to get the desired results. For example, Railway’s ticket booking users are
naive users. Clerks in any bank is a naive user because they don’t have any
DBMS knowledge but they still use the database and perform their given task.
3. A System Analyst
A system Analyst is a user who analyzes the requirements of parametric end
users. They check whether all the requirements of end users are satisfied.
4. Sophisticated Users
Sophisticated users can be engineers, scientists, business analyst, who are
familiar with the database. They can develop their own database applications
according to their requirement. They don’t write the program code but they
interact the database by writing SQL queries directly through the query
processor.
5. Database Designers
Data Base Designers are the users who design the structure of database which
includes tables, indexes, views, triggers, stored procedures and constraints
which are usually enforced before the database is created or populated with
data. He/she controls what data must be stored and how the data items to be
related. It is the responsibility of Database Designers to understand the
requirements of different user groups and then create a design which satisfies
the need of all the user groups.
6. Application Programmers
Application Programmers also referred as System Analysts or simply Software
Engineers, are the back-end programmers who writes the code for the
application programs. They are the computer professionals. These programs
could be written in Programming languages such as Visual Basic, Developer,
C, FORTRAN, COBOL etc. Application programmers design, debug, test, and
maintain set of programs called “canned transactions” for the Naive
(parametric) users in order to interact with database.
7. Casual Users / Temporary Users
Specialized users are sophisticated users who write specialized database
application that does not fit into the traditional data-processing framework.
Among these applications are computer aided-design systems, knowledge-base
and expert systems etc.
Conclusion
Database users are individuals who interact with data. The access and
permissions within a database depends on their function and tasks. Database
users knows the value of data security, privacy, and integrity, as well as how
to query data from databases using Structured Query language(SQL) or other
tools.

1.4 Applications of DBMS

There are different fields where a database management system is utilized.

Following are a few applications that utilize the information base administration
framework.
1. Railway Reservation System
In the rail route reservation framework, the information base is needed to store
the record or information of ticket appointments, status of train’s appearance,
and flight. Additionally, if trains get late, individuals become acquainted with
it through the information base update.
2. Library Management System
There are many books in the library so; it is difficult to store the record of the
relative multitude of books in a register or duplicate. Along these lines, the
data set administration framework (DBMS) is utilized to keep up all the data
identified with the name of the book, issue date, accessibility of the book, and
its writer.
3. Banking
Database the executive’s framework is utilized to store the exchange data of
the client in the information base.
Education Sector
Presently, assessments are led online by numerous schools and colleges. They
deal with all assessment information through the data set administration
framework (DBMS). In spite of that understudy’s enlistments subtleties,
grades, courses, expense, participation, results, and so forth all the data is put
away in the information base.
5. Credit card exchanges
The database Management framework is utilized for buying on charge cards
and age of month to month proclamations.
6. Social Media Sites
We all utilization of online media sites to associate with companions and to
impart our perspectives to the world. Every day, many people group pursue
these online media accounts like Pinterest, Facebook, Twitter, and Google in
addition to. By the utilization of the data set administration framework, all the
data of clients are put away in the information base and, we become ready to
interface with others.
7. Broadcast communications
Without DBMS any media transmission organization can’t think. The
Database the executive’s framework is fundamental for these organizations to
store the call subtleties and month to month postpaid bills in the information
base.
8. Accounting and Finance
The information base administration framework is utilized for putting away
data about deals, holding and acquisition of monetary instruments, for
example, stocks and bonds in a data set.
9. E-Commerce Websites
These days, web-based shopping has become a major pattern. Nobody needs to
visit the shop and burn through their time. Everybody needs to shop through
web based shopping sites, (for example, Amazon, Flipkart, Snapdeal) from
home. So all the items are sold and added uniquely with the assistance of the
information base administration framework (DBMS). Receipt charges,
installments, buy data these are finished with the assistance of DBMS.
10. Human Resource Management
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).
11. Manufacturing
Manufacturing organizations make various kinds of items and deal them
consistently. To keep the data about their items like bills, acquisition of the
item, amount, inventory network the executives, information base
administration framework (DBMS) is utilized.
12. Airline Reservation System
This framework is equivalent to the railroad reservation framework. This
framework additionally utilizes an information base administration framework
to store the records of flight takeoff, appearance, and defer status.
13. Healthcare System
DBMS is used in healthcare to manage patient data, medical records, and
billing information.
14. Security
DBMS provides security features to ensure that only authorized users have
access to the data.
15. Telecommunication
Database Management Systems (DBMS) are essential to the
telecommunications industry because they manage enormous volumes of data
on billing, customer information, and network optimization.

1.5Data models (Hierarchical, Network, ER, Relational)

A Data Model in Database Management System (DBMS) is the concept
of tools that are developed to summarize the description of the database. Data
Models provide us with a transparent picture of data which helps us in creating
an actual database. It shows us from the design of the data to its proper
implementation of data.

Hierarchical Model :
This is one of the oldest models in a data model which was developed by
IBM, in the 1950s. In a hierarchical model, data are viewed as a collection of
tables, or we can say segments that form a hierarchical relation. In this, the
data is organized into a tree-like structure where each record consists of one
parent record and many children. Even if the segments are connected as a
chain-like structure by logical associations, then the instant structure can be a
fan structure with multiple branches. We call the illogical associations as
directional associations.
In the hierarchical model, segments pointed to by the logical association are
called the child segment and the other segment is called the parent segment.
If there is a segment without a parent is then that will be called the root and
the segment which has no children are called the leaves. The main
disadvantage of the hierarchical model is that it can have one-to-one and one-
to-many relationships between the nodes.
Applications of hierarchical model :
 Hierarchical models are generally used as semantic models in practice as
many real-world occurrences of events are hierarchical in nature like
biological structures, political, or social structures.
 Hierarchical models are also commonly used as physical models because of
the inherent hierarchical structure of the disk storage system like tracks,
cylinders, etc. There are various examples such as Information
Management System (IMS) by IBM, NOMAD by NCSS, etc.
In the above-given figure, we have few students and few course-enroll and a
course can be assigned to a single student only, but a student can enroll in any
number of courses and with this the relationship becomes one-to-many. We
can represent the given hierarchical model like the below relational tables :
FACULTY Table
Nam
Dep Course-taught
e

John CSE CA

Jake CSE SE

Royal CSE DBMS

STUDENT Table
Course-
Name Grade
enroll

Gami CA 2.0

Mary SE 3.0

Mayen SE 4.0
Example 2: Consider the below cricket database system hierarchical model
scheme.

Hierarchical model

Here, in this example, for each player, there are some set of positions
(P_POSITION) he plays, a set of places (P_PLACE), and also a set of
birthdates (P_BDATE) of the players. In the above figure, each node
represents a logical record type and is displayed by a list of its fields. The
child node represents a set of records that are connected to each record of the
parent type, which is due to a many-to-many relationship is from child to
parent. In the above, figure, the root node PLAYER states that for every player
there will be a set of positions, a set of places (only one), and a set of
birthdates (which is only one).
Advantages of the hierarchical model :
 As the database is based on this architecture the relationships between
various layers are logically simple so, it has a very simple hierarchical
database structure.
 It has data sharing as all data are held in a common database data and
therefore sharing of data becomes practical.
 It offers data security and this model was the first database model that
offered data security.
 There’s also data integrity as it is based on the parent-child relationship and
also there’s always a link between the parents and the child segments.
Disadvantages of the hierarchical model :
 Even though this model is conceptually simple and easy to design at the
same time it is quite complex to implement.
 This model also lacks flexibility as the changes in the new tables or
segments often yield very complex system management tasks. Here, a
 deletion of one segment can lead to the involuntary deletion of all segments
under it.
 It has no standards as the implementation of this model does not provide any
specific standard.
 It is also limited as many of the common relationships do not conform to the 1
to N format as required by the hierarchical model.

Network Data Model:

The Network Model in a Database Management System (DBMS) is a data

model that allows the representation of many-to-many relationships in a more
flexible and complex structure compared to the Hierarchical Model. It uses a
graph structure consisting of nodes (entities) and edges (relationships) to
organize data, enabling more efficient and direct access paths.
What is Network Model?
This model was formalized by the Database Task group in the 1960s. This
model is the generalization of the hierarchical model. This model can consist
of multiple parent segments and these segments are grouped as levels but there
exists a logical association between the segments belonging to any level.
Mostly, there exists a many-to-many logical association between any of the
two segments. We called graphs the logical associations between the
segments. Therefore, this model replaces the hierarchical tree with a graph-like
structure, and with that, there can more general connections among different
nodes. It can have M: N relations i.e, many-to-many which allows a record to
have more than one parent segment. Here, a relationship is called a set, and
each set is made up of at least 2 types of record which are given below:
 An owner record that is the same as of parent in the hierarchical model.
 A member record that is the same as of child in the hierarchical model .
Structure of a Network Model

A Network data model

In the above figure, member TWO has only one owner ‘ONE’ whereas
member FIVE has two owners i.e, TWO and THREE. Here, each link between
the two record types represents 1 : M relationship between them. This model
consists of both lateral and top-down connections between the nodes.
Therefore, it allows 1: 1, 1 : M, M : N relationships among the given entities
which helps in avoiding data redundancy problems as it supports multiple
paths to the same record. There are various examples such as TOTAL by
Cincom Systems Inc., EDMS by Xerox Corp., etc.

Example : Network model for a Finance Department.

Below we have designed the network model for a Finance Department:

Network model of Finance Department.

So, in a network model, a one-to-many (1: N) relationship has a link between

two record types. Now, in the above figure, SALES-MAN, CUSTOMER,
PRODUCT, INVOICE, PAYMENT, INVOICE-LINE are the types of records
for the sales of a company. Now, as you can see in the given figure,
INVOICE-LINE is owned by PRODUCT & INVOICE. INVOICE has also
two owners SALES-MAN & CUSTOMER.
Let’s see another example, in which we have two segments, Faculty and
Student. Say that student John takes courses both in CS and EE departments.
Now, find how many instances will be there?
For the above example, a student’s instance can have at least 2 parent
instances therefore, there exist relations between the instances of students and
faculty segment. The model can be very complex as if we use other segments
say Courses and logical associations like Student-Enroll and Faculty-course.
So, in this model, a student can be logically associated with various instances
of Faculties and Courses.
Advantages of Network Model
 This model is very simple and easy to design like the hierarchical data
model.
 This model is capable of handling multiple types of relationships which can
help in modeling real-life applications, for example, 1: 1, 1: M, M: N
relationships.
 In this model, we can access the data easily, and also there is a chance that
the application can access the owner’s and the member’s records within a
set.
 This network does not allow a member to exist without an owner which
leads to the concept of Data integrity.
 Like a hierarchical model, this model also does not have any database
standard,
 This model allows to represent multi parent relationships.
Disadvantages of Network Model
 The schema or the structure of this database is very complex in nature as all
the records are maintained by the use of pointers.
 There’s an existence of operational anomalies as there is a use of pointers
for navigation which further leads to complex implementation.
 The design or the structure of this model is not user-friendly.
 This model does not have any scope of automated query optimization.
 This model fails in achieving structural independence even though the
network database model is capable of achieving data independence.
Features of Network Model in DBMS
 Data Relationship Representation: The network model uses a graph
structure to represent data relationships. It allows many-to-many
relationships, providing greater flexibility in how data is connected.
 Records and Sets: Data in a network model is organized into records and
sets. Records are similar to rows in a relational table, and sets are used to
define relationships between records, akin to links in a graph.
 Owner-Member Relationships: The network model defines data
relationships using owner-member pairs. An owner record can be linked to
multiple member records, and a member record can belong to multiple
owner records, facilitating complex relationships.
 Navigational Access: The network model supports navigational data
access, where records are accessed through predefined paths. This is
different from relational models, which use declarative query languages
like SQL.
 Hierarchical and Non-Hierarchical Structures: The network model can
represent both hierarchical (tree-like) and non-hierarchical (graph-like)
structures, providing flexibility in data modeling.
Operations on Network Model in DBMS
 Insertion: Adding new records and establishing owner-member
relationships.
 Deletion: Removing records and maintaining data integrity by handling
related records and relationships .
 Update: Modifying existing records and relationships between records.
 Traversal: Navigating through the network structure to access related
records using predefined paths.
 Search: Retrieving specific records based on criteria by navigating the
network structure.
Difference Between the Network Model and the
Hierarchical Model
Feature Hierarchical Model Network Model

Structure Tree-like structure Graph structure

One-to-many (single Many-to-many (multiple parents

Relationships
parent, multiple children) and children)

Flexibility Less flexible More flexible

Data Access Single access path Multiple access paths

Higher redundancy due to Lower redundancy due to

Redundancy
rigid hierarchy shared relationships

Simpler to design and More complex to design and

Complexity
implement manage

Suitable for simple,

Usage Suitable for complex,
hierarchical data
Scenario interconnected data structures
structures

Efficient for hierarchical Efficient for complex queries

Efficiency
traversal and data retrieval

Example Organizational chart Telecommunications network

Difference between Hierarchical Data Model and Network Data Model

:
S.
No. Hierarchical Data Model Network Data Model

In this model, you could create a

In this model, to store data hierarchy method
1. network that shows how data is
is used.
related to each other.

It implements 1:1, 1:n and also

2. It implements 1:1 and 1:n relations.
many to many relations.

To organize records, it uses

3. To organize records, it uses tree structure.
graphs.

Records are linked with the help

4. Records are linked with the help of pointers.
of linked list.

Insertion anomaly exits in this model i.e.

5. child node cannot be inserted without the There is no insertion anomaly.
parent node.

Deletion anomaly exists in this model i.e. it

6. There is no deletion anomaly.
is difficult to delete the parent node.

It is used to access the data which is It is used to access the data which
7.
complex and asymmetric. is complex and symmetric.

When update operation is performed, it

No such problem exists because
suffers from inconsistency problem because
8. of the single occurrence of records
of the existence of multiple instances of
while updating.
child records.

There is partial data independence

9. This model lacks data independence.
in this model.

Less flexible in comparison to the relational

10. It is flexible.
model.

Searching for a record is easy

When you are searching for a record then
because of the availability of
11. firstly you need to visit parent record before
multiple access paths to reach data
retrieving a child record.
item.
 S.
No. Hierarchical Data Model Network Data Model

Example- Oracle. SQL Server,

Example- IBM’s IMS (Information Sybase DBMS implement this
12.
Management System) implement this model. model.

Example of Hierarchical Data Model

Example of Network Data Model:

Conclusion

The network model in DBMS offers a flexible way to represent complex data
relationships through its graph-based structure. While it allows for many-to-
many relationships and more intricate data connections compared to the
 hierarchical model, it also requires more sophisticated navigational access
methods. Understanding its features and operations helps in leveraging its
capabilities for scenarios that involve complex data interactions.

E-R-Model:

Introduction of ER Model
Peter Chen developed the ER diagram in 1976 .The ER model was created
to provide a simple and understandable model for representing the structure
and logic of databases. It has since evolved into variations such as the
Enhanced ER Model and the Object Relationship Model
The Entity Relational Model is a model for identifying entities to be
represented in the database and representation of how those entities are related.
The ER data model specifies enterprise schema that represents the overall
logical structure of a database graphically.


 Peter Chen developed the ER diagram in 1976 .The ER model
was created to provide a simple and understandable model for
representing the structure and logic of databases. It has since
evolved into variations such as the Enhanced ER The Entity
Relational Model is a model for identifying entities to be
represented in the database and representation of how those
entities are related. The ER data model specifies enterprise
schema that represents the overall logical structure of a
database graphically.
The Entity Relationship Diagram explains the relationship
among the entities present in the database. ER models are used
to model real-world objects like a person, a car, or a company and
the relation between these real-world objects. In short, the ER
Diagram is the structural format of the database.
Why Use ER Diagrams In DBMS?
 ER diagrams are used to represent the E-R model in a
database, which makes them easy to convert into relations
(tables).
 ER diagrams provide the purpose of real-world modeling of
objects which makes them intently useful.
 ER diagrams require no technical knowledge and no hardware
support.
 These diagrams are very easy to understand and easy to
create even for a naive user.
 It gives a standard solution for visualizing the data logically.
Symbols Used in ER Model
ER Model is used to model the logical view of the system from a
data perspective which consists of these symbols:
 Rectangles: Rectangles represent Entities in the ER Model.
 Ellipses: Ellipses represent Attributes in the ER Model.
 Diamond: Diamonds represent Relationships among Entities.
 Lines: Lines represent attributes to entities and entity sets
with other relationship types.
 Double Ellipse: Double Ellipses represent Multi-Valued
Attributes.
 Double Rectangle: Double Rectangle represents a Weak
Entity.

Components of ER Diagram
ER Model consists of Entities, Attributes, and Relationships among
Entities in a Database System.
 Components of ER Diagram

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.
Entity are of two types
[Link] Entity – Which can be touched like car , person etc.
[Link] – tangible Entity – Which can’t be touched like air , bank
account etc.
Entity Set: An Entity is an object of Entity Type and a set of all
entities is called an entity set. For Example, E1 is an entity having
Entity Type Student and the set of all students is called Entity Set.
In ER diagram, Entity Type is represented as:

Entity Set
We can represent the entity set in ER Diagram but can’t represent
entity in ER Diagram because entity is row and column in the
relation and ER Diagram is graphical representation of data.
1. Strong Entity
A Strong Entity is a type of entity that has a key Attribute. Strong
Entity does not depend on other Entity in the Schema. It has a
primary key, that helps in identifying it uniquely, and it is
represented by a rectangle. These are called Strong Entity Types.
2. Weak Entity
An Entity type has a key attribute that uniquely identifies each
entity in the entity set. But some entity type exists for which key
attributes can’t be defined. These are called Weak Entity types.
For Example, A company may store the information of
dependents (Parents, Children, Spouse) of an Employee. But the
dependents can’t exist without the employee. So Dependent will
be a Weak Entity Type and Employee will be Identifying Entity
type for Dependent, which means it is Strong Entity Type.
A weak entity type is represented by a Double Rectangle. The
participation of weak entity types is always total. The relationship
between the weak entity type and its identifying strong entity
type is called identifying relationship and it is represented by a
double diamond.

Strong Entity and Weak Entity

Attributes
Attributes are the properties that define the entity type. For
example, Roll_No, Name, DOB, Age, Address, and Mobile_No are
the attributes that define entity type Student. In ER diagram, the
attribute is represented by an oval.

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

Key Attribute

2. Composite Attribute
An attribute composed of many other attributes is called a
composite attribute. For example, the Address attribute of the
student Entity type consists of Street, City, State, and Country. In
ER diagram, the composite attribute is represented by an oval
comprising of ovals.

Composite Attribute

3. Multivalued Attribute
An attribute consisting of more than one value for a given entity.
For example, Phone_No (can be more than one for a given
student). In ER diagram, a multivalued attribute is represented by
a double oval.

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

Derived Attribute

The Complete Entity Type Student with its Attributes can be

represented as:

Entity and Attributes

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, the
relationship type is represented by a diamond and connecting the
entities with lines.
 Entity-Relationship Set

A set of relationships of the same type is known as a relationship

set. The following relationship set depicts S1 as enrolled in C2, S2
as enrolled in C1, and S3 as registered in C3.

Relationship Set

Degree of a Relationship Set

The number of different entity sets participating in a relationship
set is called the degree of a relationship set.
1. Unary Relationship: When there is only ONE entity set
participating in a relation, the relationship is called a unary
relationship. For example, one person is married to only one
person.

Unary Relationship

2. Binary Relationship: When there are TWO entities set

participating in a relationship, the relationship is called a binary
relationship. For example, a Student is enrolled in a Course.

Binary Relationship
3. Ternary Relationship: When there are n entities set
participating in a relation, the relationship is called an n-ary
relationship.
Cardinality
The number of times an entity of an entity set participates in a
relationship set is known as cardinality. Cardinality can be of
different types:
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 one female and a female
can marry one male. So the relationship will be one-to-one.
the total number of tables that can be used in this is 2.

one to one cardinality

Using Sets, it can be represented as:

Set Representation of One-to-One

2. One-to-Many: In one-to-many mapping as well where each

entity can be related to more than one entity and the total
number of tables that can be used in this is 2. Let us assume that
one surgeon department can accommodate many doctors. So the
Cardinality will be 1 to M. It means one department has many
Doctors.
total number of tables that can used is 3.

one to many cardinality

Using sets, one-to-many cardinality can be represented as:

Set Representation of One-to-Many

3. Many-to-One: When entities in one entity set can take part

only once in the relationship set and entities in other entity sets
can take part more than once in the relationship set, cardinality is
many to one. 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 n to 1. It means that for one course there can
be n students but for one student, there will be only one course.
The total number of tables that can be used in this is 3.
 many to one cardinality

Using Sets, it can be represented as:

Set Representation of Many-to-One

In this case, each student is taking only 1 course but 1 course has
been taken by many students.
4. Many-to-Many: When entities in all entity sets can take part
more than once in the relationship cardinality is many to many.
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.
the total number of tables that can be used in this is 3.

many to many cardinality

Using Sets, it can be represented as:

Many-to-Many Set Representation

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.
Participation Constraint
Participation Constraint is applied to the entity participating in the
relationship set.
1. Total Participation – Each entity in the entity set must
participate in the relationship. If each student must enroll in a
course, the participation of students will be total. Total
participation is shown by a double line in the ER diagram.
2. Partial Participation – The entity in the entity set may or
may NOT participate in the relationship. If some courses are not
enrolled by any of the students, the participation in the course will
be partial.
The diagram depicts the ‘Enrolled in’ relationship set with Student
Entity set having total participation and Course Entity set having
partial participation.

Total Participation and Partial Participation

Using Set, it can be represented as,

Set representation of Total Participation and Partial Participation

Every student in the Student Entity set participates in a

relationship but there exists a course C4 that is not taking part in
the relationship.
How to Draw ER Diagram?
 The very first step is Identifying all the Entities, and place them
in a Rectangle, and labeling them accordingly.
 The next step is to identify the relationship between them and
place them accordingly using the Diamond, and make sure
that, Relationships are not connected to each other.
 Attach attributes to the entities properly.
 Remove redundant entities and relationships.
 Add proper colors to highlight the data present in the database.

Relational Data Model:

E.F. Codd proposed the relational Model to model data in the form of
relations or tables. After designing the conceptual model of the Database
using ER diagram, we need to convert the conceptual model into a relational
model which can be implemented using any RDBMS language like Oracle
SQL, MySQL, etc. So we will see what the Relational Model is.
The relational model uses a collection of tables to represent both data and the
relationships among those data. Each table has multiple columns, and each
column has a unique name. Tables are also known as relations. The relational
model is an example of a record-based model. Record-based models are so
named because the database is structured in fixed-format records of several
types. Each table contains records of a particular type. Each record type
defines a fixed number of fields, or attributes. The columns of the table
correspond to the attributes of the record type. The relational data model is the
most widely used data model, and a vast majority of current database systems
are based on the relational model.
What is the Relational Model?
The relational model represents how data is stored in Relational Databases. A
relational database consists of a collection of tables, each of which is assigned
a unique name. Consider a relation STUDENT with attributes ROLL_NO,
NAME, ADDRESS, PHONE, and AGE shown in the table.
Table Student
ROLL_NO NAME ADDRESS PHONE AGE

1 RAM DELHI 9455123451 18

2 RAMESH GURGAON 9652431543 18

3 SUJIT ROHTAK 9156253131 20

4 SURESH DELHI 18

Important Terminologies
 Attribute: Attributes are the properties that define an entity.
e.g.; ROLL_NO, NAME, ADDRESS
 Relation Schema: A relation schema defines the structure of the relation
and represents the name of the relation with its attributes. e.g.; STUDENT
(ROLL_NO, NAME, ADDRESS, PHONE, and AGE) is the relation
schema for STUDENT. If a schema has more than 1 relation, it is called
Relational Schema.
 Tuple: Each row in the relation is known as a tuple. The above relation
contains 4 tuples, one of which is shown as:
1 RAM DELHI 9455123451 18
 Relation Instance: The set of tuples of a relation at a particular instance of
time is called a relation instance. Table 1 shows the relation instance of
STUDENT at a particular time. It can change whenever there is an
insertion, deletion, or update in the database.
 Degree: The number of attributes in the relation is known as the degree of
the relation. The STUDENT relation defined above has degree 5.
 Cardinality: The number of tuples in a relation is known as cardinality.
The STUDENT relation defined above has cardinality 4.
 Column: The column represents the set of values for a particular attribute.
The column ROLL_NO is extracted from the relation STUDENT.
 NULL Values: The value which is not known or unavailable is called a
NULL value. It is represented by blank space. e.g.; PHONE of
STUDENT having ROLL_NO 4 is NULL.
 Relation Key: These are basically the keys that are used to identify the
rows uniquely or also help in identifying tables. These are of the following
types.
o Primary Key
o Candidate Key
o Super Key
o Foreign Key
o Alternate Key
o Composite Key
Constraints in Relational Model
While designing the Relational Model, we define some conditions which must
hold for data present in the database are called Constraints. These constraints
are checked before performing any operation (insertion, deletion, and updation
) in the database. If there is a violation of any of the constraints, the operation
will fail.
Domain Constraints
These are attribute-level constraints. An attribute can only take values that lie
inside the domain range. e.g.; If a constraint AGE>0 is applied to STUDENT
relation, inserting a negative value of AGE will result in failure.
Key Integrity
Every relation in the database should have at least one set of attributes that
defines a tuple uniquely. Those set of attributes is called keys. e.g.; ROLL_NO
in STUDENT is key. No two students can have the same roll number. So a key
has two properties:
 It should be unique for all tuples.
 It can’t have NULL values.
Referential Integrity
When one attribute of a relation can only take values from another attribute of
the same relation or any other relation, it is called referential integrity. Let us
suppose we have 2 relations
Table Student
ROLL_N ADDRES AG BRANCH_COD
O NAME S PHONE E E

945512345
1 RAM DELHI 18 CS
1

RAMES GURGAO 965243154

2 18 CS
H N 3

915625313
3 SUJIT ROHTAK 20 ECE
1

4 SURESH DELHI 18 IT

Table Branch
BRANCH_CODE BRANCH_NAME

CS COMPUTER SCIENCE

IT INFORMATION TECHNOLOGY

ELECTRONICS AND
ECE COMMUNICATION
ENGINEERING

CV CIVIL ENGINEERING

BRANCH_CODE of STUDENT can only take the values which are present in
BRANCH_CODE of BRANCH which is called referential integrity constraint.
The relation which is referencing another relation is called REFERENCING
RELATION (STUDENT in this case) and the relation to which other relations
refer is called REFERENCED RELATION (BRANCH in this case).
Anomalies in the Relational Model
An anomaly is an irregularity or something which deviates from the expected
or normal state. When designing databases, we identify three types of
anomalies: Insert, Update, and Delete.
Insertion Anomaly in Referencing Relation
We can’t insert a row in REFERENCING RELATION if referencing
attribute’s value is not present in the referenced attribute value. e.g.; Insertion
of a student with BRANCH_CODE ‘ME’ in STUDENT relation will result in
an error because ‘ME’ is not present in BRANCH_CODE of BRANCH.
Deletion/ Updation Anomaly in Referenced Relation:
We can’t delete or update a row from REFERENCED RELATION if the value
of REFERENCED ATTRIBUTE is used in the value of REFERENCING
ATTRIBUTE. e.g; if we try to delete a tuple from BRANCH having
BRANCH_CODE ‘CS’, it will result in an error because ‘CS’ is referenced by
BRANCH_CODE of STUDENT, but if we try to delete the row from
BRANCH with BRANCH_CODE CV, it will be deleted as the value is not
been used by referencing relation. It can be handled by the following method:
On Delete Cascade
It will delete the tuples from REFERENCING RELATION if the value used
by REFERENCING ATTRIBUTE is deleted from REFERENCED
RELATION. e.g.; For, if we delete a row from BRANCH with
BRANCH_CODE ‘CS’, the rows in STUDENT relation with
BRANCH_CODE CS (ROLL_NO 1 and 2 in this case) will be deleted.
On Update Cascade
It will update the REFERENCING ATTRIBUTE in REFERENCING
RELATION if the attribute value used by REFERENCING ATTRIBUTE is
updated in REFERENCED RELATION. e.g;, if we update a row from
BRANCH with BRANCH_CODE ‘CS’ to ‘CSE’, the rows in STUDENT
relation with BRANCH_CODE CS (ROLL_NO 1 and 2 in this case) will be
updated with BRANCH_CODE ‘CSE’.
Super Keys
Any set of attributes that allows us to identify unique rows (tuples) in a given
relationship is known as super keys. Out of these super keys, we can always
choose a proper subset among these that can be used as a primary key. Such
keys are known as Candidate keys. If there is a combination of two or more
attributes that are being used as the primary key then we call it a Composite
key.
Codd Rules in Relational Model
Edgar F Codd proposed the relational database model where he stated rules.
Now these are known as Codd’s Rules. For any database to be the perfect one,
it has to follow the rules.
For more, refer to Codd Rules in Relational Model .
Advantages of the Relational Model
 Simple model: Relational Model is simple and easy to use in comparison
to other languages.
 Flexible: Relational Model is more flexible than any other relational model
present.
 Secure: Relational Model is more secure than any other relational model.
 Data Accuracy: Data is more accurate in the relational data model.
 Data Integrity: The integrity of the data is maintained in the relational
model.
 Operations can be Applied Easily: It is better to perform operations in the
relational model.
Disadvantages of the Relational Model
 Relational Database Model is not very good for large databases.
 Sometimes, it becomes difficult to find the relation between tables.
 Because of the complex structure, the response time for queries is high.
Characteristics of the Relational Model
 Data is represented in rows and columns called relations.
 Data is stored in tables having relationships between them called the
Relational model.
 The relational model supports the operations like Data definition, Data
manipulation, and Transaction management.
 Each column has a distinct name and they are representing attributes.
 Each row represents a single entity.

1.6 File system Vs. DBMS

Difference between File System and DBMS

The file system is basically a way of arranging the files in a storage medium
like a hard disk. The file system organizes the files and helps in the retrieval of
files when they are required. File systems consist of different files which are
grouped into directories. The directories further contain other folders and files.
The file system performs basic operations like management, file naming,
giving access rules, etc.

Example: EXT(Extended File System).

DBMS(Database Management System)
Database Management System is basically software that manages the
collection of related data. It is used for storing data and retrieving the data
effectively when it is needed. It also provides proper security measures for
protecting the data from unauthorized access. In Database Management
System the data can be fetched by SQL queries and relational algebra. It also
provides mechanisms for data recovery and data backup.
Examples: Oracle, MySQL, MS SQL server.
Difference between File System and DBMS
Basics File System DBMS

The file system is a way of

arranging the files in a DBMS is software for
storage medium within a managing the database.
Structure computer.

Data Redundant data can be In DBMS there is no

Redundancy present in a file system. redundant data.

It doesn’t provide Inbuilt It provides in house tools

Backup and mechanism for backup and for backup and recovery of
Recovery recovery of data if it is lost. data even if it is lost.

Query There is no efficient query Efficient query processing

processing processing in the file system. is there in DBMS.

There is less data There is more data

consistency in the file consistency because of the
Consistency system. process of normalization.

It has more complexity in

It is less complex as
handling as compared to
compared to DBMS.
Complexity the file system.

File systems provide less DBMS has more security

Security security in comparison to mechanisms as compared
Constraints DBMS. to file systems.

It has a comparatively
It is less expensive than
higher cost than a file
DBMS.
Cost system.

In DBMS data
independence exists,
mainly of two types:
There is no data
1) Logical Data
independence.
Independence.
Data 2)Physical Data
Independence Independence.
Basics File System DBMS

Only one user can access Multiple users can access

User Access data at a time. data at a time.

The user has to write

The users are not required to
procedures for managing
write procedures.
Meaning databases

Data is distributed in many

Due to centralized nature
files. So, it is not easy to
data sharing is easy
Sharing share data.

Data It give details of storage and It hides the internal details

Abstraction representation of data of Database

Integrity Integrity Constraints are Integrity constraints are

Constraints difficult to implement easy to implement

To access data in a file , user

No such attributes are
requires attributes such as
required.
Attributes file name, file location.

Example Cobol, C++ Oracle,Sql

The main difference between a file system and a DBMS (Database

Management System) is the way they organize and manage data.
1. File systems are used to manage files and directories, and provide basic
operations for creating, deleting, renaming, and accessing files. They
typically store data in a hierarchical structure, where files are organized in
directories and subdirectories. File systems are simple and efficient, but
they lack the ability to manage complex data relationships and ensure data
consistency.
2. On the other hand, DBMS is a software system designed to manage large
amounts of structured data, and provide advanced operations for storing,
retrieving, and manipulating data. DBMS provides a centralized and
organized way of storing data, which can be accessed and modified by
multiple users or applications. DBMS offers advanced features like data
validation, indexing, transactions, concurrency control, and backup and
recovery mechanisms. DBMS ensures data consistency, accuracy, and
 integrity by enforcing data constraints, such as primary keys, foreign keys,
and data types.

1.7 Data independence:

What is Data Independence in DBMS?

Data independence is a property of a database management system by which
we can change the database schema at one level of the database system
without changing the database schema at the next higher level. In this article,
we will learn in full detail about data independence and will also see its types.
If you read it completely, you will understand it easily.
What is Data Independence in DBMS?
In the context of a database management system, data independence is the
feature that allows the schema of one layer of the database system to be
changed without any impact on the schema of the next higher level of the
database system. ” Through data independence, we can build an environment
in which data is independent of all programs, and through the three schema
architectures, data independence will be more understandable. Data via two
card stencils along with centralized DBMS data is a form of transparency that
has value for someone.
It can be summed up as a sort of immunity of user applications that adjusts
correctly and does not change addresses, imparting the class of data and their
order. I want the separate applications not to be forced to deal with data
representation and storage specifics because this decreases quality and
flexibility. DBMS permits you to see data with such a generalized sight. It
actually means that the ability to change the structure of the lower-level
schema without presenting the upper-level schema is called da ta
independence.
Types of Data Independence
There are two types of data independence.
 logical data independence
 Physical data independence
Logical Data Independence
 Changing the logical schema (conceptual level) without changing the
external schema (view level) is called logical data independence.
 It is used to keep the external schema separate from the logical schema.
 If we make any changes at the conceptual level of data, it does not affect
the view level.
 This happens at the user interface level.
 For example, it is possible to add or delete new entities, attributes to the
conceptual schema without making any changes to the external schema.
Physical Data Independence
 Making changes to the physical schema without changing the logical
schema is called physical data independence.
 If we change the storage size of the database system server, it will not
affect the conceptual structure of the database.
 It is used to keep the conceptual level separate from the internal level.
 This happens at the logical interface level.
 Example – Changing the location of the database from C drive to D drive.
Difference Between Physical and Logical Data Independence
Physical Data Independence Logical Data Independence

It mainly concerns about

It mainly concerns how the data
changes to the structure or data
is stored in the system.
definition.

It is difficult to achieve
It is easier to achieve than
compared to physical
logical independence.
independence.

To make changes at the physical

To make changes at the logical
level we generally do not require
level, we need to make changes
changes at the application
at the application level.
program level.

It tells about the conceptual

It tells about the internal schema.
schema.

There may or may not be a need Whenever the logical structure

for changes to be made at the of the database has to be
internal level to improve the changed, the changes made at
structure. the logical level are important.

Example- change in compression

Example – adding/modifying or
technology, hashing
deleting a new attribute.
algorithm, storage device etc.

1.8 Levels of abstraction:

Database systems comprise complex data structures. In order to

make the system efficient in terms of retrieval of data, and reduce
complexity in terms of usability of users, developers use abstraction
i.e. hide irrelevant details from the users. This approach simplifies
database design.
Level of Abstraction in a DBMS
There are mainly 3 levels of data abstraction:
 Physical or Internal Level
 Logical or Conceptual Level
 View or External Level
Physical or Internal Level
This is the lowest level of data abstraction. It tells us how the data is actually
stored in memory. Access methods like sequential or random access and file
organization methods like B+ trees and hashing are used for the same.
Usability, size of memory, and the number of times the records are factors that
we need to know while designing the database.
Suppose we need to store the details of an employee. Blocks of storage and the
amount of memory used for these purposes are kept hidden from the user.
Logical or Conceptual Level
This level comprises the information that is actually stored in the database in
the form of tables. It also stores the relationship among the data entities in
relatively simple structures. At this level, the information available to the user
at the view level is unknown.
We can store the various attributes of an employee and relationships, e.g. with
the manager can also be stored.
The logical level thus describes the entire database in terms of a small number
of relatively simple structures. Although implementation of the simple
structures at the logical level may involve complex physical-level structures,
the user of the logical level does not need to be aware of this complexity. This
is referred to as physical data independence. Database administrators, who
must decide what information to keep in the database, use the logical level of
abstraction.
View or External Level
This is the highest level of abstraction. Only a part of the actual database is
viewed by the users. This level exists to ease the accessibility of the database
by an individual user. Users view data in the form of rows and columns.
Tables and relations are used to store data. Multiple views of the same
database may exist. Users can just view the data and interact with the database,
storage and implementation details are hidden from them. Even though the
logical level uses simpler structures, complexity remains because of the
variety of information stored in a large 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
Example: In case of storing customer data,
 Physical level – it will contains block of storages (bytes,GB,TB,etc)
 Logical level – it will contain the fields and the attributes of data.
 View level – it works with CLI or GUI access of database

Data Abstraction

The main purpose of data abstraction is to achieve data independence in order

to save the time and cost required when the database is modified or altered.

1.9 Architecture of DBMS:

DBMS Architecture 1-level, 2-Level, 3-Level

a Database stores a lot of critical information to access data quickly and
securely. Hence it is important to select the correct architecture for efficient
data management. DBMS Architecture helps users to get their requests done
while connecting to the database. We choose database architecture depending
on several factors like the size of the database, number of users, and
relationships between the users. There are two types of database models that
we generally use, logical model and physical model. Several types of
architecture are there in the database which we will deal with in the next
section.
Types of DBMS Architecture
There are several types of DBMS Architecture that we use according to the
usage requirements. Types of DBMS Architecture are discussed here.
 1-Tier Architecture
 2-Tier Architecture
 3-Tier Architecture
1-Tier Architecture
In 1-Tier Architecture the database is directly available to the user, the user
can directly sit on the DBMS and use it that is, the client, server, and Database
are all present on the same machine. For Example: to learn SQL we set up an
SQL server and the database on the local system. This enables us to directly
interact with the relational database and execute operations. The industry
won’t use this architecture they logically go for 2-tier and 3- tier Architecture.

DBMS 1-Tier Architecture

Advantages of 1-Tier Architecture

Below mentioned are the advantages of 1-Tier Architecture.
 Simple Architecture: 1-Tier Architecture is the most simple architecture
to set up, as only a single machine is required to maintain it.
 Cost-Effective: No additional hardware is required for implementing 1-
Tier Architecture, which makes it cost-effective.
 Easy to Implement: 1-Tier Architecture can be easily deployed, and hence
it is mostly used in small projects.
2-Tier Architecture
The 2-tier architecture is similar to a basic client-server model. The
application at the client end directly communicates with the database on the
server side. APIs like ODBC and JDBC are used for this interaction. The
server side is responsible for providing query processing and transaction
management functionalities. On the client side, the user interfaces and
application programs are run. The application on the client side establishes a
connection with the server side to communicate with the DBMS.
An advantage of this type is that maintenance and understanding are easier,
and compatible with existing systems. However, this model gives poor
performance when there are a large number of users.
 DBMS 2-Tier Architecture

Advantages of 2-Tier Architecture

 Easy to Access: 2-Tier Architecture makes easy access to the database,
which makes fast retrieval.
 Scalable: We can scale the database easily, by adding clients or upgrading
hardware.
 Low Cost: 2-Tier Architecture is cheaper than 3-Tier Architecture
and Multi-Tier Architecture .
 Easy Deployment: 2-Tier Architecture is easier to deploy than 3-Tier
Architecture.
 Simple: 2-Tier Architecture is easily understandable as well as simple
because of only two components.
3-Tier Architecture
In 3-Tier Architecture, there is another layer between the client and the server.
The client does not directly communicate with the server. Instead, it interacts
with an application server which further communicates with the database
system and then the query processing and transaction management takes place.
This intermediate layer acts as a medium for the exchange of partially
processed data between the server and the client. This type of architecture is
used in the case of large web applications .
 DBMS 3-Tier Architecture

Advantages of 3-Tier Architecture

 Enhanced scalability: Scalability is enhanced due to the distributed
deployment of application servers. Now, individual connections need not
be made between the client and server.
 Data Integrity: 3-Tier Architecture maintains Data Integrity. Since there is
a middle layer between the client and the server, data corruption can be
avoided/removed.
 Security: 3-Tier Architecture Improves Security. This type of model
prevents direct interaction of the client with the server thereby reducing
access to unauthorized data.
Disadvantages of 3-Tier Architecture
 More Complex: 3-Tier Architecture is more complex in comparison to 2-
Tier Architecture. Communication Points are also doubled in 3-Tier
Architecture.
 Difficult to Interact: It becomes difficult for this sort of interaction to take
place due to the presence of middle layers.
For more, you can refer to the Advantages and Disadvantages of 3-Tier
Architecture in DBMS.
Conclusion
When it comes to choosing a DBMS architecture, it all comes down to how
complex and scalable the system is. The 3-level structure has the best features
and is perfect for modern, big database systems.

[Link] Languages (DDL, DML, DCL):

o SQL commands are instructions. It is used to communicate with the

database. It is also used to perform specific tasks, functions, and
queries of data.
o SQL can perform various tasks like create a table, add data to
tables, drop the table, modify the table, set permission for users.

1. Data Definition Language (DDL)

o DDL changes the structure of the table like creating a table, deleting a
table, altering a table, etc.
o All the command of DDL are auto-committed that means it permanently
save all the changes in the database.

Here are some commands that come under DDL:

 o CREATE
o ALTER
o DROP
o TRUNCATE

2. Data Manipulation Language

o DML commands are used to modify the database. It is responsible for all
form of changes in the database.
o The command of DML is not auto-committed that means it can't
permanently save all the changes in the database. They can be rollback.

Here are some commands that come under DML:

o INSERT
o UPDATE
o DELETE

3. Data Control Language

DCL commands are used to grant and take back authority from any
database user.

Here are some commands that come under DCL:

o Grant
o Revoke

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

b. Revoke: It is used to take back permissions from the user.