Database Management System

By Dr Pawan Singh Mehra

Course Code: CO-202

Module 1
Dr. Pawan Singh Mehra,
Department of CSE

1
Contents of Module-1
 Database system concepts and its architecture
 Data models, schema and instances
By Dr Pawan Singh Mehra

 Data independence and database language and interface

 Data definition languages, DML
 Overall database structure
 Data modeling using Entity Relationship Model
 E.R. model concept, notation for ER diagrams mapping constraints
 Keys, Concept of Super key, Candidate key & Primary key
 Generalizations & Aggregation
 Reducing ER diagrams to tables, extended ER model
2
By Dr Pawan Singh Mehra

What is Data?

3
What is Information?
By Dr Pawan Singh Mehra

Data need to be processed using some set of procedures to

convert it into information
For example:
Number of students in a section
Number of students with CGPA greater than 9
4
Traditional File System

• File systems consist of

different files which are
By Dr Pawan Singh Mehra

grouped into directories. The

directories further contain
other folders and files.
• The file system performs
basic operations like
File System Files management, file naming,
edit etc.
• Some file systems are FAT,
NTFS etc.
5
Issues with Traditional File System

Redundancy
By Dr Pawan Singh Mehra

No
No backup

File
concurrent
and Recovery
Access

System
Uncontrolled/
Inconsistency Unauthorized
access
6
Database Management System
DATABASE DBMS USER
By Dr Pawan Singh Mehra

Organised Software which is used to User communicates

collection of manage the database. e.g. with DBMS interface
related data MS Access, MYSQL, Oracle to fulfil requirement
7
History/Timeline of DBMS
1960 Charles Bachman at designed first DBMS i.e. Integrated Data Store(IDS)
1970 E.F. Codd from IBM introduced the concept of Relational DB,1NF
By Dr Pawan Singh Mehra

1971 2nd and 3rd Normal Form introduced by Codd

1974 Boyce-Codd Normal Form introduced, SQL introduced
1976 Peter Chen from MIT introduced ER Model
1978 Oracle DB2 introduced
1985 Object Oriented DB developed
1995 MySQL , Internet based DB developed
1997 XML applied to DB processing 8
Advantages of DBMS over File System
 No redundancy
 Data consistency
By Dr Pawan Singh Mehra

 Data security & privacy

 Concurrent access/Sharing of data
 Recovery
 Flexibility

9
Database Application Examples
 Enterprise Information
 Sales: customers, products, purchases
 Accounting: payments, receipts, assets
By Dr Pawan Singh Mehra

 Human Resources: Information about employees, salaries, payroll

taxes.
 Manufacturing: management of production, inventory, orders, supply
chain.
 Banking and finance
 Customer information, accounts, loans, and banking transactions.
 Credit card transactions
 Finance: sales and purchases of financial instruments (e.g., stocks
and bonds; storing real-time market data
 Universities: Student, Faculty, Staff, registration, grades, procurement10.
Database Application Examples(Cont..)
 Airlines: reservations, schedules
 Telecommunication: records of calls, texts, and data usage, generating
monthly bills, maintaining balances on prepaid calling cards
By Dr Pawan Singh Mehra

 Web-based services
• Online retailers: order tracking, customized recommendations
• Online advertisements
 Document databases
 Navigation systems: For maintaining the locations of varies places of
interest along with the exact routes of roads, train systems, buses, etc.

11
Architectures in DBMS Applications
 Database applications are usually partitioned into two types
 Two-tier architecture -- the application resides at the client machine,
where it invokes database system functionality at the server machine
By Dr Pawan Singh Mehra

 Three-tier architecture -- the client machine acts as a front end and

does not contain any direct database calls.
• The client end communicates with an application server, usually
through a forms interface.
• The application server in turn communicates with a database system
to access data.

12
Architectures in DBMS Applications
By Dr Pawan Singh Mehra

13
Source: Korth, 7th Edition
Who controls DBMS?
 A person who has central control over the system is called a Database
administrator (DBA).
 Functions of a DBA include:
By Dr Pawan Singh Mehra

• Schema definition
• Storage structure and access-method definition
• Schema and physical-organization modification
• Granting of authorization for data access
• Routine maintenance
• Periodically backing up the database
• Ensuring that enough free disk space is available for normal
operations, and upgrading disk space as required
• Monitoring jobs running on the database
14
Data Models
 A collection of tools for describing
• Data
• Data relationships
By Dr Pawan Singh Mehra

• Data semantics
• Data constraints
 Relational data model
 Entity-Relationship data model (mainly for database design)
 Object-based data models (Object-oriented and Object-relational)
 Semi-structured data model (XML)
 Other older models:
• Network data model
• Hierarchical data model
15
Relational Data Model
 All the data is stored in various tables.
 Example of tabular data in the relational model Columns
By Dr Pawan Singh Mehra

Rows

16
A Sample Relational Model
Columns
By Dr Pawan Singh Mehra

Rows

17
Data Abstraction
 Hide the complexity of data structures to represent data in the
database from users through multiple levels of data abstraction
By Dr Pawan Singh Mehra

18
Levels of Abstraction
 Physical level: describes how a record is physically stored in drive(data
structures/File types/ Way of retrieving or searching data/Indexes
etc./Locations/)
By Dr Pawan Singh Mehra

 Logical level: describes what data stored in database, and the

relationships among the data.
type instructor = record
ID: string;
name : string;
dept_name : string;
salary : integer;
end;
 View level: application programs hide details of data types. Views can
also hide information (such as an employee’s salary) for security purposes.
19
By Dr Pawan Singh Mehra

View of Data

20
Schemas and Instances
 Schema- The overall design of the database
• Similar to types and variables in programming languages
 Logical Schema – the overall logical structure of the database
By Dr Pawan Singh Mehra

• Example: The database consists of information about a set of

customers and accounts in a bank and the relationship between
them
• Analogous to type information of a variable in a program
 Physical schema – the overall physical structure of the database.
• Analogous to data structures/File types/ Way of retrieving or
searching data/Indexes etc./Locations
 Instance – the actual content of the database at a particular point in
time
• Analogous to the value of a variable 21
Data Independence
 The changes made at lower levels of schema should not affect the
upper levels of schema.
 Two types of Data Independence
By Dr Pawan Singh Mehra

 Physical Data Independence

• Any change in the physical location of the tables should not
affect the conceptual level or external view of data.
• For example: Moving schema from one location to another.
 Conceptual Data Independence
• Any change in conceptual level of schema should not affect
the external view of the schema.
• For example: Adding or deleting attributes of the table.
22
Data Definition Language(DDL)
 Specification notation for defining the database schema
 Example: create table instructor (
ID char(5),
By Dr Pawan Singh Mehra

name varchar(20),
dept_name varchar(20),
salary numeric(8,2))
 DDL compiler generates a set of table templates stored in a data
dictionary
 Data dictionary contains metadata (i.e., data about data)
• Database schema
• Integrity constraints
 Primary key (ID uniquely identifies instructors)
• Authorization
 Who can access what
23
Data Manipulation Language(DML)
 Language for accessing and updating the data organized by the
appropriate data model
• DML also known as query language
By Dr Pawan Singh Mehra

 There are basically two types of data-manipulation language

• Procedural DML -- require a user to specify what data are needed
and how to get those data.
• Declarative DML -- require a user to specify what data are needed
without specifying how to get those data.
 Declarative DMLs are usually easier to learn and use than are
procedural DMLs.
 Declarative DMLs are also referred to as non-procedural DMLs
 The portion of a DML that involves information retrieval is called a
query language. 24
Database Design
 The process of designing the general structure of the database:
 Logical Design – Deciding on the database schema. Database design
requires that we find a “good” collection of relation schemas.
By Dr Pawan Singh Mehra

• Business decision – What attributes should we record in the

database?
• Computer Science decision – What relation schemas should we
have and how should the attributes be distributed among the
various relation schemas?
 Physical Design – Deciding on the physical layout of the database (data
structures/File types/ Way of retrieving or searching data/Indexes
etc./Locations)

25
Database Engine
 A database system is partitioned into modules that deal with each of
the responsibilities of the overall system.
 The functional components of a database system can be divided into
By Dr Pawan Singh Mehra

• The storage manager,

• The query processor component,
• The transaction management component.

26
Storage Manager
 The storage manager is responsible for storing, retrieving, and
updating data in the database
 It is also responsible for
By Dr Pawan Singh Mehra

 Authorisation and Integrity management

 Consistency(in case of system failure and conflict free
concurrent transaction execution)
 File management (allocation of space in disk and data structure)
 Buffer management(decision of what data is to brought from
disk to Main memory and from main memory to cache)
 Data dictionary (stores metadata about schema of DB)
 Indices (for faster access to data)
27
Query Processing
Parsing and translation
Evaluation of query for syntax
Conversion into relational algebra
By Dr Pawan Singh Mehra

Optimization
minimizes the cost of query evaluation
Evaluation
Once the query plan is chosen, the query
is evaluated with that plan,

28
Transaction Management
 A transaction is a collection of operations that performs a single
logical function in a database application
 It ensures that the database remains in a consistent (correct) state
By Dr Pawan Singh Mehra

despite system failures (e.g., power failures and operating system

crashes) and transaction failures.
 Concurrency-control manager controls the interaction among the
concurrent transactions, to ensure the consistency of the database.

29
Database Architecture
 Centralized databases
• One to a few cores, shared memory
 Client-server
By Dr Pawan Singh Mehra

• One server machine executes work on behalf of multiple client

machines.
 Parallel databases
• Many core, shared memory
• Shared disk
 Distributed databases
• Geographical distribution
• Schema/data heterogeneity

30
Database Users
 Naïve Users(tellers, agents, web users)
 Through application interfaces
 Application programmers(Computer professional)
By Dr Pawan Singh Mehra

 Through application programs

 Sophisticated users(analysts)
 Through query tools/analysis software
 Database Administrators
 Through administrative tools

31
Entity Relationship Model
 It represents the overall logical structure of a database.
 It models an enterprise as a collection of entities and relationships
 The ER data model employs three basic concepts:
By Dr Pawan Singh Mehra

• entity sets,
• relationship sets,
• attributes.
 The ER model also has an associated diagrammatic representation,
the ER diagram, which can express the overall logical structure of a
database graphically

32
Entity and Entity Sets
 An entity is an object that exists and is distinguishable from other
objects.
 Example: specific person, company, event, plant
By Dr Pawan Singh Mehra

 An entity set is a set of entities of the same type that share the same
properties.
 Example: set of all persons, companies, trees, holidays
 An entity is represented by a set of attributes; i.e., descriptive
properties possessed by all members of an entity set.
 Example:
instructor = (ID, name, salary )
course= (course_id, title, credits)
 A subset of the attributes form a primary key of the entity set; i.e.,
uniquely identifying each member of the set. 33
Entity and Entity Sets
By Dr Pawan Singh Mehra

34
Relationship Sets
 A relationship is an association among two or more entities

Example:
By Dr Pawan Singh Mehra

44553 (Peltier) advisor 22222 (Einstein)

student entity relationship set instructor entity

 A relationship set is a mathematical relation among n ≥ 2 entities, each

taken from entity sets
{(e1, e2, … en) | e1 ϵ E1, e2 ϵ E2, …, en ϵ En}
where (e1, e2, …, en) is a relationship

Example:
(44553,22222) ϵ advisor 35
By Dr Pawan Singh Mehra

Relationship Set

36
Entity Set and Relationship Set via ER Diagram
By Dr Pawan Singh Mehra

Diamonds represent relationship sets.

37
Attributes of Entity Set
 Attributes: A characteristic or trait of an entity type that describes the
entity
 Types of attributes
By Dr Pawan Singh Mehra

 Simple
 Composite
 Multivalued
 Derived
 Key attribute

38
Entity Set with Attributes
By Dr Pawan Singh Mehra

COMPOSITE
MULTI-VALUED
KEY

SIMPLE DERIVED

39
Entity Set with Attributes
By Dr Pawan Singh Mehra

40
Roles
 Entity sets of a relationship need not be distinct
 The labels “manager” and “worker” are called roles; they specify how
employee entities interact via the works_for relationship set.
By Dr Pawan Singh Mehra

 Roles are indicated in E-R diagrams by labeling the lines that connect
diamonds to rectangles.
 Role labels are optional, and are used to clarify semantics of the
relationship

41
Degree of a Relationship Set
 It is the number of entity sets involved
 Binary relationship involve two entity sets(i.e. Degree 2)
 Most of the relationship sets in a database system are binary
By Dr Pawan Singh Mehra

 There are occasions when it is more convenient to represent

relationships as non-binary.
 E-R Diagram with a Ternary Relationship

42
Mapping Cardinality
 It expresses the number of entities to which another entity can be
associated via a relationship set.
 Most useful in describing binary relationship sets.
By Dr Pawan Singh Mehra

 For a binary relationship set, the mapping cardinality must be one of

the following types:
 One to one
 One to many
 Many to one
 Many to many

43
One to One Relationship
By Dr Pawan Singh Mehra

a directed line (), signify “one,” or an undirected line (—), signify “many”
44
One to Many Relationship
By Dr Pawan Singh Mehra

a directed line (), signify “one,” or an undirected line (—), signify “many”
45
Many to One Relationship
By Dr Pawan Singh Mehra

a directed line (), signify “one,” or an undirected line (—), signify “many”
46
Many to Many Relationship
By Dr Pawan Singh Mehra

a directed line (), signify “one,” or an undirected line (—), signify “many”
47
Total Participation and Partial Participation
 Total participation (indicated by double line): every entity in the
entity set participates in at least one relationship in the relationship set
By Dr Pawan Singh Mehra

 participation of student in advisor relation is total

 every student must have an associated instructor
 Partial participation: some entities may not participate in any
relationship in the relationship set
 Example: participation of instructor in advisor is partial
48
Notations for Cardinality Constraints
 A line may have an associated minimum and maximum cardinality,
shown in the form l..h, where l is the minimum and h the maximum
cardinality
By Dr Pawan Singh Mehra

 A minimum value of 1 indicates total participation.

 A maximum value of 1 indicates that the entity participates in at
most one relationship
 A maximum value of * indicates no limit.
 Example:

 Instructor can advise 0 or more students. A student must have 1

advisor; cannot have multiple advisors 49
Weak Entity Set
 An entity set that does not have a primary key is referred to as a weak
entity set.
 The existence of a weak entity set depends on the existence of a
By Dr Pawan Singh Mehra

identifying entity set

 it must relate to the identifying entity set via a total, one-to-many
relationship set from the identifying to the weak entity set
 Identifying relationship depicted using a double diamond
 The discriminator (or partial key) of a weak entity set is the set of
attributes that distinguishes among all the entities of a weak entity set.
 The primary key of a weak entity set is formed by the primary key of
the strong entity set on which the weak entity set is existence
dependent, plus the weak entity set’s discriminator.
50
Weak Entity Set
 We depict a weak entity set by double rectangles.
 We underline the discriminator of a weak entity set with a dashed line.
 payment_number – discriminator of the payment entity set
By Dr Pawan Singh Mehra

 Primary key for payment – (loan_number, payment_number)

51
Extended ER Diagram: Specialisation & Generalisation
 Specialisation
 Top-down design process; we designate subgroupings within an entity set
that are distinctive from other entities in the set.
By Dr Pawan Singh Mehra

 These subgroupings become lower-level entity sets that have attributes or

participate in relationships that do not apply to the higher-level entity set.
 Depicted by a triangle component labeled ISA (E.g. customer “is a”
person).
 Generalisation
 A bottom-up design process – combine a number of entity sets that share
the same features into a higher-level entity set.
 Specialization and generalization are simple inversions of each other; they
are represented in an E-R diagram in the same way.
 The terms specialization and generalization are used interchangeably
52
Extended ER Diagram: Specialisation & Generalisation
By Dr Pawan Singh Mehra

53
Extended ER Diagram: Aggregation
 Consider the ternary relationship works_on, which we saw earlier
 Suppose we want to record managers for tasks performed by an
employee at a branch( i.e. want to assign a manager to each
By Dr Pawan Singh Mehra

employee, job and branch)

54
Extended ER Diagram: Aggregation
 Suppose we want to
record managers for
tasks performed by an
By Dr Pawan Singh Mehra

employee at a branch
 ( i.e. want to assign a
manager to each
employee, job and
branch)

55
Extended ER Diagram: Aggregation
 Relationship sets works_on and manages represent overlapping
information
 Every manages relationship corresponds to a works_on relationship
By Dr Pawan Singh Mehra

 However, some works_on relationships may not correspond to any

manages relationships
 So we can’t discard the works_on relationship
 Eliminate this redundancy via aggregation
 Treat relationship as an abstract entity
 Allows relationships between relationships
 Abstraction of relationship into new entity

56
Extended ER Diagram: Aggregation
 Without introducing
redundancy, the
following diagram
By Dr Pawan Singh Mehra

represents:
 An employee works on
a particular job at a
particular branch
 An employee, branch,
job combination may
have an associated
manager

57
Symbols used in E-R Diagram
By Dr Pawan Singh Mehra

58
Symbols used in E-R Diagram (Cont.)
By Dr Pawan Singh Mehra

59
Symbols used in E-R Diagram (Cont.)

Chen IDE1FX (Crows feet notation)

By Dr Pawan Singh Mehra

60
Reducing ER Diagrams to Tables
 Rule 1 : Strong Entity Set with Simple attributes
 Rule 2 : Strong Entity Set with Composite attributes
 Rule 3 :Strong Entity Set with Multi Valued Attributes
By Dr Pawan Singh Mehra

 Rule 4 :Binary relationship with cardinality ratio M:N

 Rule 5 :Binary relationship with cardinality ratio 1:N
 Rule 6 :Binary relationship with cardinality ratio N:1
 Rule 7 :Binary relationship with cardinality ratio 1:1
 Rule 8 :Binary relationship with Weak Entity Set
 Rule 9 :Binary relationship with total participation constraint
 Rule 10:N-ary relationship

61
Rule1: Strong Entity Set with Simple attributes
 A strong entity set with only simple attributes will require only one
table in a relational model.
 Attributes of the table will be the attributes of the entity set
By Dr Pawan Singh Mehra

 The primary key of the table will be the key attribute of the entity
set.

Roll_No (PK) Name Email

62
Rule2: Strong Entity Set with Composite attributes
 A strong entity set with any number of composite attributes will require
only one table in relational model.
 While conversion, simple attributes of the entity will be considered for
By Dr Pawan Singh Mehra

attribute set, not the composite attribute itself.

Roll_No(PK) First_Name Middle_Name Last_Name Email

63
Rule3:Strong Entity Set with Multi Valued Attributes
 A strong entity set with any number of multi-valued attributes will
require 1+ N tables in relational model where N represents number of
multivalued attributes.
By Dr Pawan Singh Mehra

 One table will contain all the simple attributes with the Primary Key
 N tables will contain the Primary Key and one of the multi valued
attributes(If minimum asked then 1 table only for multivalued
attributes) Roll_No (PK) Name Address

Roll_No (PK) Email

64
Rule4:Binary relationship with cardinality ratio M:N
 In many to many relationship, three tables will be required
By Dr Pawan Singh Mehra

A1(PK) A2 A1 B1 C1 B1(PK) B2

PK={A1,B1}

65
Rule5:Binary relationship with cardinality ratio 1:N
 In One to many relationship two tables will be required
By Dr Pawan Singh Mehra

A1(PK) A2 B1(PK) B2 C1 A1(FK)

66
Rule6:Binary relationship with cardinality ratio N:1
 In many to one relationship two tables will be required
By Dr Pawan Singh Mehra

A1(PK) A2 C1 B1(FK) B1(PK) B2

67
Rule 7:Binary relationship with cardinality ratio 1:1
 In One to One relationship two tables will be required.
By Dr Pawan Singh Mehra

A1(PK) A2 C1 B1(FK) B1(PK) B2

A1(PK) A2 B1(PK) B2 C1 A1(FK)

68
Rule 8: Binary relationship with Weak Entity Set
 The cardinality ratio between Strong and Weak Entity Set is always 1:N
 B1 is the discriminator attribute
By Dr Pawan Singh Mehra

A1(PK) A2 A1 B1 B2 C1

PK={A1,B1}

69
Rule 9: Binary relationship-Total participation constraint
 Rule1: Strong Entity Set with Simple attributes
By Dr Pawan Singh Mehra

A1(PK) A2 B1(PK) B2 A1(FK-NOT NULL) C1

70
Rule 10: N-Ary relationship
By Dr Pawan Singh Mehra

A1(PK) A2 B1(PK) B2 D1(PK) D2 A1 B1 D1 C1

71
PK={A1,B1,D1}
Keys in DBMS
 A key is an attribute or set of attributes that uniquely a tuple(a row) in
relation(table)
 It is also used to establish and identify relationship between tables
By Dr Pawan Singh Mehra

 Types of Keys
 Super Key
 Candidate Key
 Primary Key
 Alternate Key
 Foreign Key

72
Super Key
 It is a combination of all possible attributes that can uniquely identify
the tuple in a given relation
 It is a superset of candidate key
By Dr Pawan Singh Mehra

 A table can have many super keys

 It may contain attributes that are redundant
Some Super Keys
• {Roll_No} Roll_No Name Aadhar_No Email_ID Branch

• {Aadhar_No} 2K21CO01 Mohan 88213211 [email protected] CSE

• {Email_ID} 2K21EC02 Rohan 28341212 [email protected] EC

• {Roll_No, Aadhar_No} 2K21IT03 Abraham 65762231 [email protected] IT

• {Aadhar_No, Email_ID} 2K21SW04 Vernika 98322212 [email protected] SWE
• {Roll_No, Name} Relation: Student
• {Roll_No, Branch}
• {Roll_No, Name, Branch} 73
Candidate Key
 It is a minimal super key with no redundant attributes
 It is a distinct set of attributes from which primary key can be selected
By Dr Pawan Singh Mehra

Roll_No Name Aadhar_No Email_ID Branch

Candidate keys possible 2K21CO01 Mohan 88213211 [email protected] CSE
• {Roll_No} 2K21EC02 Rohan 28341212 [email protected] EC
• {Aadhar_No} 2K21IT03 Abraham 65762231 [email protected] IT
• {Email_ID} 2K21SW04 Vernika 98322212 [email protected] SWE

Relation: Student

74
Primary Key
 It is one of the candidate key chosen by the DBA to uniquely identify a
tuple
 Primary Key can never be NULL
By Dr Pawan Singh Mehra

 The value of Primary Key can never be changed

 The value of Primary key must be assigned while inserting a record
 Only one Primary key is allowed in a relation.
Roll_No Name Aadhar_No Email_ID Branch
Primary key 2K21CO01 Mohan 88213211 [email protected] CSE
• {Roll_No} 2K21EC02 Rohan 28341212 [email protected] EC
2K21IT03 Abraham 65762231 [email protected] IT
2K21SW04 Vernika 98322212 [email protected] SWE

Relation: Student
75
Alternate Key
 From the available set of candidate keys, One key is chosen as Primary
key, rest of the keys are called Alternate Keys
By Dr Pawan Singh Mehra

Roll_No Name Aadhar_No Email_ID Branch

Alternate keys 2K21CO01 Mohan 88213211 [email protected] CSE
• {Aadhar_No} 2K21EC02 Rohan 28341212 [email protected] EC
• {Email_ID} 2K21IT03 Abraham 65762231 [email protected] IT
2K21SW04 Vernika 98322212 [email protected] SWE

Relation: Student
76
Foreign Key
 A key used to link two tables together
 When Table B refers Primary Key of Table A, then the Primary Key
attribute Table A will be added into Table B as Foreign Key.
By Dr Pawan Singh Mehra

 Foreign key can be NULL.

 Foreign Key ensures referential integrity.
Roll_No Name Aadhar_No Email_ID Branch Course_ID Course_Name Roll_No
2K21CO01 Mohan 88213211 [email protected] CSE CO201 COA 2K21CO01
2K21EC02 Rohan 28341212 [email protected] EC CO202 DBMS 2K21EC02
2K21IT03 Abraham 65762231 [email protected] IT CO203 OS 2K21IT03
2K21SW04 Vernika 98322212 [email protected] SWE
Relation: Course_Enrolled
Relation: Student
Primary Key of relation Student will act as Foreign Key in relation Course_Enrolled
77