Database System Concepts

(Unit-1)
Prepared by:
Neeraj Sharma
(Assistant Professor, JLU-SOET)

1
UNIT -1:
Introduction of Database System Concepts

Subject: Database System Concepts

Subject Code:

2
Introduction to DBMS
 DBMS stands for Database Management System. We can
break it like this DBMS = DataBase + Management System.

 Database is a collection of data and Management System is a

set of programs to store and retrieve those data.

 DBMS is a collection of inter-related data and set of programs

to store & access those data in an easy and effective manner.

3
Need of DBMS
Database systems are basically developed for large amount of
data.
When dealing with huge amount of data, there are two things
that require optimization:
 Storage of data.

 Retrieval of data.

4
 Traditional approach
DATA DEPENDENCY

LOSS OF
FLEXIBILITY MULTIPLE FILES

REDUNDANCY UPDATE
PROBLEM

5
INCONSISTENCY
 USE OF DBMS
 To develop software applications In less time.
 Data independence and efficient use of data.
 For uniform data administration.
 For data integrity and security.
 For concurrent access to data, and data recovery from crashes.
 To use user-friendly declarative query language.

6
COMPONENTS OF DBMS

7
DESCRIPTION OF COMPONENTS
 Users: Users may be of any kind such as DB administrator,
System developer, or database users.

 Database application: Database application may be

Departmental, Personal, organization's and / or Internal.

 DBMS: Software that allows users to create and manipulate

database access,

 Database: Collection of logical data as a single unit.

8
Drawbacks of File system
 Data redundancy
 Data inconsistency
 Data Isolation
 Atomicity issues
 Dependency on application programs
 Data Security

9
 DBMS vs. Flat File
DBMS Flat File Management System

Multi-user access It does not support multi-user

access
Design to fulfil the need for small It is only limited to smaller DBMS
and large businesses system.
Remove redundancy and Integrity Redundancy and Integrity issues

Easy to implement complicated No support for complicated

transactions transactions
10
 Advantage of DBMS over file system
There are several advantages of Database management system
over file system. Few of them are as follows:
 No redundant data.
 Data Consistency and Integrity.
 Data Security.

11
Advantage of DBMS over file system
 Privacy
 Easy access to data
 Easy recovery
 Flexible

12
 Disadvantages of DBMS
 DBMS implementation cost is high compared to the file
system.

 Complexity: Database systems are complex to understand.

 Performance: Database systems are generic, making them

suitable for various applications. However this feature affect
their performance for some applications

13
DBMS Architecture
 The DBMS design depends upon its architecture. The basic
client/server architecture is used to deal with a large
number of PCs, web servers, database servers and other
components that are connected with networks.
 The client/server architecture consists of many PCs and a
workstation which are connected via the network.
 DBMS architecture depends upon how users are
connected to the database to get their request done.

14
Types of DBMS Architecture

15
1-Tier Architecture
 In this architecture, the database is directly available to
the user. It means the user can directly interact with the
DBMS and uses it.
 Any changes done here will directly be done on the
database itself. It doesn't provide a handy tool for end
users.
 The 1-Tier architecture is used for development of the
local application, where programmers can directly
communicate with the database for the quick response.

16
2-Tier Architecture
 The 2-Tier architecture is same as basic client-server. In the
two-tier architecture, applications on the client end can
directly communicate with the database at the server side. For
this interaction, API's like: ODBC, JDBC are used.
 The user interfaces and application programs are run on the
client-side.
 The server side is responsible to provide the functionalities
like: query processing and transaction management.
 To communicate with the DBMS, client-side application
establishes a connection with the server side.

17
2-Tier Architecture

18
3-Tier Architecture
 The 3-Tier architecture contains another layer between the
client and server. In this architecture, client can't directly
communicate with the server.
 The application on the client-end interacts with an application
server which further communicates with the database system.
 End user has no idea about the existence of the database
beyond the application server. The database also has no idea
about any other user beyond the application.
 The 3-Tier architecture is used in case of large web application.

19
3-Tier Architecture

20
Data Independence
• Data independence can be explained using the three-schema
architecture.
• Data independence refers characteristic of being able to
modify the schema at one level of the database system
without altering the schema at the next higher level.
• There are two types of data independence:
– Logical Data Independence
– Physical Data Independence

21
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.
• Physical data independence occurs at the logical interface
level.
22
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.
• Logical data independence occurs at the user interface
level.

23
Logical Data Independence

24
Features and functions of Database
System
• DBMS provides Several functionalities
– Data Storage Management
– Security Management
– Backup and Recovery Management
– Database Access Language and Application Programming
Interface
– Data Dictionary Management
– Data Transformation and Presentation
– Multi User Access Control
– Data Integrity Management
– Database Communication Interface
25
Data Storage Management

• One of the most important tasks for DBMS is to create a

database for complex data and manage the data. It gives
relief to the user by creating a structure for the complex
data sets so that users can access it and manipulate them
very easily.
• Modern database systems not only provide storage for the
data but they store and manage the metadata (data of
data) like data procedural rules, validation rules etc. DBMS
also provides performance tuning, which makes accessing
data faster and easier.

26
Security Management

• Security is another aspect which is handled by the Database

Management Systems. Database systems provide a high level
of security measures using various security algorithms to keep
the data safe and ensure the data privacy.
• There are certain security rules that ensure what data can be
accessed from the database and which user can access it. It
also makes sure what operations (read, write, delete) can be
performed on the specific data. It is very important for the
organizations where multi-user databases are required.

27
Backup and Recovery Management
• To keep the data safe and ensure the integrity, the database
system provides the features for backup and recovery
management. If the system fails due to some reason then it
recovers the data and keeps the data safe.

28
Database Access Language and
Application Programming Interface
• DBMS provides a database access language which is also called
a query language. Query languages are non-procedural
languages used to access the database and manipulate the
data. SQL is an example of a query language. The majority of
DBMS vendors provide the support of various query languages
to access the data..

29
Data Dictionary Management
• Data dictionary management is also useful functionality
provided by the Database Management System. In the data
dictionary, it stores the data and its related information about
its relationship. So, a data dictionary keeps the data structures
and their relationships with other data, so that a programmer
is not responsible for storing the relationship in the database
through complex coding. DBMS provides the data abstraction
and removes the dependency of the data from the system.

30
Data Transformation and Presentation

• DBMS provides the functionality of data transformation, which

means programmers need not worry about the logical and
physical representation of the data. DBMS stores the data in
the determined data structure.
• For example, if a user asks for the date from a database and he
receives it as 14 December 2022, but in the database, it is
stored in different columns of month, date and year.

31
Multi User Access Control
• Multi User Access control is another feature which is provided
by the modern Database Systems. So, more than one user can
access the database at the same time without any problem.
This feature makes sure the integrity of the data present in the
database. It also follows the ACID property, so the database
will be consistent while multiple users are accessing it
concurrently. It is very useful for the database of organizations
where multiple database engineers are working concurrently.

32
Data Integrity Management
• Database systems provide data integrity management by
maximizing the data consistency and minimizing the data
redundancy. The data dictionary is the feature database
system used to store the relationships of the data to keep the
data integrity. Data integrity is needed where a transaction
based database system is present.

33
Database Communication Interface
• When a user requests data from the database, it uses some
environments like browsers (Chrome or Firefox etc.) to get the
data.

34
Applications where we use Database
Management Systems are:
 Airlines: reservations, schedules, etc.

 Telecom: calls made, customer details, network usage, etc.

 Universities: registration, results, grades, etc.

 Sales: products, purchases, customers, etc.

 Banking: all transactions etc. 35

Data Models
 Data Model is the modeling of the data description, data
semantics, and consistency constraints of the data. It
provides the conceptual tools for describing the design of a
database at each level of data abstraction. Therefore, there
are following four data models used for understanding the
structure of the database:

36
Relational Data Models
 This type of model designs the data in the form of rows and
columns within a table. Thus, a relational model uses tables
for representing data and in-between relationships. Tables
are also called relations.
 This model was initially described by Edgar F. Codd, in 1969.
The relational data model is the widely used model which is
primarily used by commercial data processing applications.

37
Relational Data Models
 Relational data model is the primary data model, which is
used widely around the world for data storage and
processing. This model is simple and it has all the properties
and capabilities required to process data with storage
efficiency.

38
Relational Data Models Concepts
• Tables − In relational data model, relations are saved in the format of Tables.
This format stores the relation among entities. A table has rows and columns,
where rows represents records and columns represent the attributes.
• Tuple − A single row of a table, which contains a single record for that relation is
called a tuple.
• Relation instance − A finite set of tuples in the relational database system
represents relation instance. Relation instances do not have duplicate tuples.
• Relation schema − A relation schema describes the relation name (table name),
attributes, and their names.
• Relation key − Each row has one or more attributes, known as relation key,
which can identify the row in the relation (table) uniquely.
• Attribute domain − Every attribute has some pre-defined value scope, known as
attribute domain.

39
Example: STUDENT Relation
NAME ROLL_NO PHONE_NO ADDRESS AGE

Ram 14795 7305758992 Noida 24

Shyam 12839 9026288936 Delhi 35
Laxman 33289 8583287182 Gurugram 20
Mahesh 27857 7086819134 Ghaziabad 27
Ganesh 17282 9028 9i3988 Delhi 40

• In the given table, NAME, ROLL_NO, PHONE_NO,

ADDRESS, and AGE are the attributes.
• The instance of schema STUDENT has 5 tuples.
• t3 = <Laxman, 33289, 8583287182, Gurugram, 20>
40
Example: STUDENT Relation

41
Entity-Relationship Data Model
 An ER model is the logical representation of data as objects
and relationships among them. These objects are known as
entities, and relationship is an association among these
entities. This model was designed by Peter Chen and
published in 1976 papers. It was widely used in database
designing. A set of attributes describe the entities. For
example, student_name, student_id describes the 'student'
entity. A set of the same type of entities is known as an
'Entity set', and the set of the same type of relationships is
known as 'relationship set'.
42
Entity-Relationship Data Model
 ER model stands for an Entity-Relationship model. It is a
high-level data model. This model issued to define the data
elements and relationship for a specified system.
 It develops a conceptual design for the database. It also
develops a very simple and easy to design view of data.
 In ER modeling, the database structure is portrayed as a
diagram called an entity-relationship diagram.

43
Entity-Relationship Data Model
 For example, Suppose we design a school database. In this
database, the student will be an entity with attributes like
address, name, id, age, etc. The address can be another
entity with attributes like city, street name, pin code, etc and
there will be a relationship between them.

44
Component of ER Diagram

45
Entity
 An entity may be any object, class, person or place. In the ER
diagram, an entity can be represented as rectangles.
 Consider an organization as an example- manager, product,
employee, department etc. can be taken as an entity.

46
Weak Entity
 An entity that depends on another entity called a weak
entity. The weak entity doesn't contain any key attribute of
its own. The weak entity is represented by a double
rectangle.

47
Attribute
 The attribute is used to describe the property of an entity.
Ellipse is used to represent an attribute.
 For example, id, age, contact number, name, etc. can be
attributes of a student.

48
Key Attribute
 The key attribute is used to represent the main
characteristics of an entity. It represents a primary key. The
key attribute is represented by an ellipse with the text
underlined.

49
Multivalued Attribute
 An attribute can have more than one value. These attributes
are known as a multivalued attribute. The double oval is
used to represent multivalued attribute.
 For example, a student can have more than one phone
number.

50
Derived Attribute
 An attribute that can be derived from other attribute is
known as a derived attribute. It can be represented by a
dashed ellipse.
 For example, A person's age changes over time and can be
derived from another attribute like Date of birth.

51
Relationship
 A relationship is used to describe the relation between
entities. Diamond or rhombus is used to represent the
relationship.

52
One-to –One Relationship
 When only one instance of an entity is associated with the
relationship, then it is known as one to one relationship.
 For example, A female can marry to one male, and a male
can marry to one female.

53
One-to –Many Relationship
 When only one instance of the entity on the left, and more
than one instance of an entity on the right associates with
the relationship then this is known as a one-to-many
relationship.
 For example, Scientist can invent many inventions, but the
invention is done by the only specific scientist.

54
Many-to –One Relationship
 When more than one instance of the entity on the left, and
only one instance of an entity on the right associates with
the relationship then it is known as a many-to-one
relationship.
 For example, Student enrolls for only one course, but a
course can have many students.

55
Many-to –Many Relationship
 When more than one instance of the entity on the left, and
more than one instance of an entity on the right associates
with the relationship then it is known as a many-to-many
relationship.
 For example, Employee can assign by many projects and
project can have many employees.

56
ER-Diagram Examples

57
ER-Diagram Examples

58
Hierarchical Model
 This concept uses a hierarchical tree structure to organise
the data. The hierarchy begins at the root, which contains
root data, and then grows into a tree as child nodes are
added to the parent node.

59
Network Model
 The main difference between this model and the hierarchical
model is that any record can have several parents in the
network model. It uses a graph instead of a hierarchical tree.

60
Object-based Data Model
 An extension of the ER model with notions of functions,
encapsulation, and object identity, as well. This model
supports a rich type system that includes structured and
collection types. Thus, in 1980s, various database systems
following the object-oriented approach were developed.
Here, the objects are nothing but the data carrying its
properties.

61
Semi Structured Data Model
 This type of data model is different from the other three data
models (explained above). The semistructured data model
allows the data specifications at places where the individual
data items of the same type may have different attributes
sets. The Extensible Markup Language, also known as XML, is
widely used for representing the semistructured data.
Although XML was initially designed for including the markup
information to the text document, it gains importance
because of its application in the exchange of data.

62
Thank you!!

63