Basic and Introduction to DBMS Unit 1 of AU

Basic and Introduction to DBMS

1. 6/9/2024 1
DATABASE MANAGEMENT
SYSTEM

2. 6/9/2024 2
Unit 1: Introduction :
Introduction to Database
Database Systems Vs File Systems
View of data
Data Models
Data base languages
Transaction Management
Database systems Structure
History of Database Systems
Database Systems Applications
Entity Relationship Model

3. 6/9/2024 3
INTRODUCTION
Data is stored facts.
Data may be numerical data which may
be integers or floating point numbers,
and non-numerical data such as
characters, date and etc.,
What is Data ?

4. 6/9/2024 4
Example:
98
89
87
92
phy
chem
maths
biology
The above numbers may be anything:
It may be distance in kms or amount
in rupees or no of days or marks in
each subject etc.,

5. 6/9/2024 5
What is information ?
Information is RELATED DATA. The data ( information)
which is used by an organisation –a college, a library, a
bank, a manufacturing company –is one of its most
valuable resources.
98
89
87
92
phy
chem
maths
biology

6. 6/9/2024 6
What is Database ?
Database is a collection of information organized in such a way that
a computer program can quickly select desired pieces of data.
98
89
87
92
phy
chem
maths
biology
76
87
79
88
phy
chem
maths
biology
91
67
87
77
phy
chem
maths
biology
86
80
79
88
phy
chem
maths
biology

7. 6/9/2024 7
Database Management System (DBMS)
DBMS contains information about a particular enterprise
–Collection of interrelated data
–Set of programs to access the data
–An environment that is both convenientand efficientto use
Database Applications:
–Banking:all transactions
–Airlines:reservations, schedules
–Universities: registration, grades
–Sales:customers, products, purchases
–Online retailers:order tracking, customized recommendations
–Manufacturing:production, inventory, orders, supply chain
–Human resources: employee records, salaries, tax deductions
Databases touch all aspects of our lives
A collection of programs that enables you to store, modify, and
extract information from a database.

8. 6/9/2024 8
Database Systems Vs File Systems
In the early days, database applications were built
directly on top of file systems
Drawbacks of using file systems to store data:
–Data redundancy and inconsistency
•Multiple file formats, duplication of information in different
files
–Difficulty in accessing data
•Need to write a new program to carry out each new task
–Data isolation—multiple files and formats
–Integrity problems
•Integrity constraints (e.g. account balance > 0) become
“buried” in program code rather than being stated
explicitly
•Hard to add new constraints or change existing ones

9. 6/9/2024 9
Database Systems Vs File Systems Cont.
Drawbacks of using file systems (cont.)
–Atomicity of updates
•Failures may leave database in an inconsistent state with partial updates
carried out
•Example: Transfer of funds from one account to another should either
complete or not happen at all
–Concurrent access by multiple users
•Concurrent accessed needed for performance
•Uncontrolled concurrent accesses can lead to inconsistencies
–Example: Two people reading a balance and updating it at the same time
–Security problems
•Hard to provide user access to some, but not all, data
Database systems offer solutions to all the above problems

10. 6/9/2024 10
View of data
Physical level:describes how a record (e.g., customer) is
stored.
Logical level:describes data stored in database, and the
relationships among the data.
typecustomer= record
customer_id: string;
customer_name: string;
customer_street: string;
customer_city: integer;
end;
(PASCAL CODE)
View level:application programs hide details of data types.
Views can also hide information (such as an employee’s
salary) for security purposes.
Data Abstraction:

11. 6/9/2024 11
View of data Contd.
An architecture for a database system:

12. 6/9/2024 12
Instances and Schemas
Similar to types and variables in programming languages
Schema–the logical structure of the database
–Example: The database consists of information about a set of
customers and accounts and the relationship between them)
–Analogous to type information of a variable in a program
–Physical schema: database design at the physical level
–Logical schema: database design at the logical level
Instance–the actual content of the database at a particular point
in time
–Analogous to the value of a variable
Physical Data Independence–the ability to modify the physical
schema without changing the logical schema
–Applications depend on the logical schema
–In general, the interfaces between the various levels and components
should be well defined so that changes in some parts do not seriously
influence others.

13. 6/9/2024 13
DB Schema Diagram for a Company:
Employee:
Instances and Schemas
Eno Ename SalaryAddress

14. 6/9/2024 14
Instances and Schemas
•DB Schema Diagram for a Company:
Department:
Dno Dname Dlocation
Project:
Pno Pname Hours

15. 6/9/2024 15
Instance Example:
Instances and Schemas
Eno Ename SalaryAddress
1
2
3
A
B
C
10,000
20,000
30,000
First street
Second street
Third street

16. 6/9/2024 16
Data Independence
Three schema architecture for a database system:
View1 View2 View n
Conceptual Schema
Internal Schema
Disk
External schema
Conceptual
mapping
Internal/Physic
al level

17. 6/9/2024 17
Two types of Data Independence
Logical Data Independence
Capacity to change the conceptual schema
without having to change external schema
Physical Data Independence
Capacity to change the Internal schema
without having to change external schema
Data Independence

18. 6/9/2024 18
DBMS Languages:
Types
Data Definition Language(
DDL)
Data Manipulation Language
(DML)
storage definition
language (SDL)
storage definition
language (SDL)
Procedural DML
Non-Procedural DML

19. 6/9/2024 19
DBMS Languages
–Used by the DBA and database designers to specify the
conceptual schema of a database.
–In many DBMSs, the DDL is also used to define internal
and external schemas (views).
–In some DBMSs, separate storage definition language
(SDL) and view definition language (VDL) are used to
define internal and external schemas.
•SDL is typically realized via DBMS commands
provided to the DBA and database designers
Data Definition Language (DDL):

20. 6/9/2024 20
Data Manipulation Language (DDL):
Data Manipulation Language (DML):
–Used to specify database retrievals and
updates
–DML commands (data sublanguage) can be
embeddedin a general-purpose programming
language (AKA host language), such as
COBOL, C, C++, or Java.
–Alternatively, stand-alone DML commands can
be applied directly (called a query language).
DBMS Languages

21. 6/9/2024 21
–Used by the DBA and database designers to
specify the conceptual schema of a database.
–In many DBMSs, the DDL is also used to define
internal and external schemas (views).
–In some DBMSs, separate storage definition
language (SDL) andview definition language
(VDL)are used to define internal and external
schemas.
•SDL is typically realized via DBMS commands
provided to the DBA and database designers
Data Definition Language (DDL):

22. 6/9/2024 22
Data Manipulation Language (DML)
Language for accessing and manipulating the data
organized by the appropriate data model
–DML also known as query language
Two classes of languages
–Procedural –user specifies what data is
required and how to get those data
–Declarative (nonprocedural) –user specifies
what data is required without specifying how to
get those data
SQL is the most widely used query language

23. 6/9/2024 23
Data Definition Language (DDL)
Specification notation for defining the database schema
Example:createtable account(
account-number char(10),
balance integer)
DDL compiler generates a set of tables stored in a data
dictionary
Data dictionary contains metadata (i.e., data about data)
–Database schema
–Data storage and definition language
•Specifies the storage structure and access methods
used
–Integrity constraints
•Domain constraints
•Referential integrity (references constraint in SQL)
•Assertions
–Authorization

24. 6/9/2024 24
Create table table name(list of attribute &
their datatypes);
Create schema schema name
authorization author name;
Drop schema schema name
cascade/restrict;
Drop table tablename cascade/restrict;
Alter table talename add/drop
Create view view name as (select …..)
DDL statements/commands in SQL

25. 6/9/2024 25
Transaction Management
A transactionis a collection of operations that
performs a single logical function in a database
application
Transaction-management componentensures that
the database remains in a consistent (correct)
state despite system failures (e.g., power failures
and operating system crashes) and transaction
failures.
Concurrency-control managercontrols the
interaction among the concurrent transactions, to
ensure the consistency of the database.

26. 6/9/2024 26
Database users and DBA (Database Administrator)
We can classify users of db into
many types.
Naïve users
Application Programmers
Sophisticated users
Specialized users
Database users

27. 6/9/2024 27
Naïve users:
-who interact with the system by invoking one of the
application programs that have been previously written.
Application Programmers:
-Computer professionals who write application
programs.
Sophisticated users
-form their requests in a db query language such as
SQL and submit them to a query processor.
Specialized users:
-Sophisticated users who write specialized db
applications
Database users and DBA (Database Administrator)
Database users

28. 6/9/2024 28
DBA (Database Administrator)
DBA is a person who has central control over the
system.
DBA is responsible for authorizing access to the
db.
DBA is responsible for acquiring s/w and h/w
resources.
DBA is responsible for coordinating and
monitoring the use of db.
DBA creates original db schema by executing a
set of data definition stmts in DDL.
Database users and DBA (Database Administrator)

29. 6/9/2024 29
Overall System Structure

30. 6/9/2024 30
Database system Structure
The functional components can be
broadly divided into the
Storage manager
Query Processor.

31. 6/9/2024 31
Storage Manager
It is a pgm module that provides the
interface b/w the low level data stored in the
db and the app.pgms & queries submitted to
the system.
It translates the various DML statements
into file system commands.
It is responsible for storing, retrieving and
updating data in the database.

32. 6/9/2024 32
The storage manager components
includes:
Authorization & Integrity Manager:
-It tests for the satisfication of integrity
constraints and checks the authority of users to
access data.
File Manager:
-allocation of files and disk & data structures
used to represent information stored on disk.
Storage Manager

33. 6/9/2024 33
Buffer Manger:
-to decide what data to cache in mainmemory in
orde to speed up the accessing of data.
Transaction Manager:
-db remains in a consistent state despite system
failures and that concurrent transaction
execution proceed without conflicting.
Storage Manager

34. 6/9/2024 34
The SM implements several DS such
as,
Data fileswhich store db
Data dictionarywhich stores meta data about the
structure of the db.
Indices: It is a file that provides fast access of
data items that hold particular values.
Storage Manager

35. 6/9/2024 35
Query Processor
It includes
DDL Interpreter:
-which interprets DDL sts and records the definitions in
the data dictionary.
DML Compiler:
-It translates DML sts into an evaluation plan consisting of
low level instructions that the qury evaluation engine
understands.
Query Evaluation Engine:
-Which executes low level instructions generated by DML
compiler.

36. 6/9/2024 36
Data Models
A collection of tools for describing
–Data
–Data relationships
–Data semantics
–Data constraints
Relational model
Entity-Relationship data model(mainly for database design)
Object-based data models(Object-oriented and Object-
relational)
Semistructured data model(XML)
Other older models:
–Network model
–Hierarchical model

37. 6/9/2024 37
A collection of Conceptual tools for describing
–Data
–Data relationships
–Data semantics
–Data constraints
Different data Models
-Relational model
-Entity-Relationship data model(Conceptual Model-mainly for
database design)
-Object-based data models(Object-oriented and Object-relational)
-Semi structured data model(XML)
Other older models:
–Network model
–Hierarchical model
Data Models
A Data model is a set of concepts that can be used to
describe the structure of the db.

38. 6/9/2024 38
Entity-Relationship data model
It is a high level conceptual data model that describes the
structure of db in terms of entities, relationship among
entities & constraints on them..
Basic Concepts of E-R Model:
-Entity
-Entity Set
-Attributes
-Relationship
-Relationship set
-Identifying Relationship
I

39. 6/9/2024 39
Entity-Relationship data model
Entity:
-It is a an object that exists in the real
world.
Example:
-Person, Employee, Car, Home etc..
Object with conceptual Existence
-Account, loan, job etc…

40. 6/9/2024 40
Entity Set:
-A set of entities of the same
type.
Attributes:
-A set of properties that
describe an entity.
Entity-Relationship data model

41. 6/9/2024 41
Types of Attributes:
Simple (or) atomic vs. Composite:
-An attribute which cant be sub divided. (Eg.Age)
-An attribute which can be divided into sub parts is called
 as composite attribute.
e.g.. Address-Apartment no.
-Street
-Place
-City
-District
Single Valued vs. Multivalued:
 -An attribute having only one value (e.g.. Age,eid,sno)
-An attribute having multiple values (e.g.. Deptlocat-A dept can be
located in several places)
Entity-Relationship data model

42. 6/9/2024 42
Stored Vs Derived
-Stored attribute is one that has some value
where as derived attribute is a one where its
value is derived from sa.
-E.g.. SA-DOB
 DA-Age derived fromDOB.
Key Attribute:
-An attribute which is used to uniquely identify
records.
E.g.. eid, sno, dno
Entity-Relationship data model

43. 6/9/2024 43
Relationship:
-It is an association among
several entities. It specifies what type
of relationship exists between
entities.
Entity-Relationship data model

44. 6/9/2024 44
Relationship set:
-It is a set of relationships of the same
type.
Entity-Relationship data model
1 AA 1000
2 BB2000
100FFF10000
1 AA AC
2 BBAD
100FFFSD
Entity
E
N
T
I
T
Y
S
E
T
DEPT. ENTITY
TYPE
Relationship Set
Emp.. ENTITY
TYPE

45. 6/9/2024 45
Weak Entity Set:
-No key attributes.
Identifying Relationship:
-The relationship associated with the
weak entity type
Entity-Relationship data model

46. 6/9/2024 46
Constraints
Two of the most important constraints are
a. Mapping Constraints
b. Participation constraints
•Participation constraints
Total Participation Partial Participation

47. 6/9/2024 47
a. Mapping Cardinalities:
Mapping Cardinalities OR CARDINALITY RATIOS, EXPRESSS THE
NUMBER OF ENTITIES TO WHICH ANOTHER ENTITY CAN BE
ASSOCIATED VIA A RELATIONSHIPSET.
Several types of Mapping Cardinalities. They are,
a.i. One-to-One
 An entity in set A is associated with at most one entity in
set B and vice versa.
e1
e2
e3
d1
d2
d3
Employee Dept.Works for

48. 6/9/2024 48
a.i. One-to-many
 An entity in set A is associated with zero
or more no. of entities in set B and an entity in B
is associated with at most one entity in A.
a. Mapping Cardinalities:
e1
e2
e3
d1
d2
d3
Employee Dept.Works for

49. 6/9/2024 49
a. Mapping Cardinalities:
a.i. Many-to-One
 One or more no. of entities in set A is
associated with at most one entity in B. An entity
in B can be associated with any no. of entities in
A.
e1
e2
e3
e4
d1
d2
d3
Employee Dept.Works for

50. 6/9/2024 50
a. Mapping Cardinalities:
a.i. Many-to-Many
 One or more no. of entities
in set A is associated with one or
more no. of entities in set B.
e1
e2
e3
e4
d1
d2
d3
Employee Dept.Works for

51. 6/9/2024 51
The participation of an entity set E in a relationship
set R is said to be total if every entity in E
participates in atleast one relationship in R.
b. Participation Constraints:
Total Participation
Partial Participation:
The participation of an entity set E in a relationship
set R is said to be partial if only a few of the entities in
E participated in relationship in R.

52. 6/9/2024 52
E.G..
Partial
participation
Total
participation
Employee
Dept.
Manages
b. Participation Constraints:

53. 6/9/2024 53
Keys
It is used to uniquely identify entities
within a given entityset or a
relationship set.
Keys in Entity set:
(i) Primary Key:
–It is a key used to uniquely identify an
entity in the entity set.
–E,g, eno,rno,dno etc…

54. 6/9/2024 54
Keys
Super Key:
It is a set of one or more attributes
that allow us to uniquely identify an
entity in the entity set. Among them
one must be a primary key attribute.
E.G.. Eid (primary key) and ename
together can be identify an entity in
entity set.

55. 6/9/2024 55
Keys
Candidate key:
They are minimal super keys for which no
proper subset is a superkey.
E.g.. Ename and eaddr can be sufficient to
identify an employee in employee set.
{eid} and {ename,eaddr} –Candidate keys

56. 6/9/2024 56
FOREIGN -----------------------
Keys
An attribute which makes a reference
to an attribute of another entity type
is called foreign key

57. 6/9/2024 57
Key
E.g…
eidEname eaddr dno
dnamedno dloc
Employee –E1
Dept –E2
Foreign
Key

58. 6/9/2024 58
Domain
A range of values can be defined for an
attribute and is called as Domain of that
attribute.
E.g.. Age –attribute A
Domain (A)= {1,2,….100}

59. 6/9/2024 59
Keys in Relationship set:
Case 1: If the relationship set R has
no attributes, then the set of
attributes
Primarykey(E1) U Primarykey(E2) U
Primarykey (n)
Case2: If the relationship set R has
attributes, then the set of attributes,

60. 6/9/2024 60
Keys in Relationship set
Primarykey(E1) U Primarykey(E2) U
Primarykey (n)U{a1,a2,…an}
describes an individual relationship in set
R.
In both cases,
Primarykey(E1) U Primarykey(E2) U
Primarykey (n) forms a superkey for a
relationship set.

61. 6/9/2024 61
E-R Diagram
Symbol Description
Entity Type
Attribute
Key Attribute

62. 6/9/2024 62
E-R Diagram
Symbol Description
Composite
Attribute
Multivalued
attribute Attribute

63. 6/9/2024 63
E-R Diagram
Symbol Description
Derived Attribute
Relationship

64. 6/9/2024 64
Symbol Description
E-R Diagram
Identifying Relationship
Weak Entity Type

65. 6/9/2024 65
Symbol Description
E-R Diagram
E1 E2R
Total participation of
E2 in R & Partial
Participation of E1 in R
E1 E2R
1 1
Cardinality Ratio
E2R
Min,max
Structural constraints
(min,max) on
participation of
Environmental in R

66. 6/9/2024 66
Recursive Relationship
If the same entity type partcipates in
a relationship more than once in
different roles.
E.g..Employee
Supervising
Supervisor
Supervise

67. 6/9/2024 67
Design of an E-R Database Schema
The steps involved in designing an E-R database
schema are,
Identify entity types and their entity sets.
List out the attributes of each entity type.
Relate several entities by specifyiing some
relationship that exists among them.
Specify some attributes of relation if any.
Specify Generalization and specialization any
exists.
Specify Aggregation (global) if any used.

68. 6/9/2024 68
Design Process:
The main phases involved in designing a ER db schema is shown below,
Mini
world
Requirements collection &
Analysis
Data Requirements
Conceptual Design
Conceptual schema
Logical Design
Physical design
Logical schema
Internal Schema
Transaction
implementation
Functional Requirements
Functional Analysis
High level Transaction
specification
Application program
Design
App.pgms
DBMS
Independent
DBMS
Specific

69. 6/9/2024 69
Requirements collection & Analysis
The db designersinterview db users to understand &
document their requirements.
They find out data requirements (what data are stored in the
db).
Conceptual Design:
Once the requirements are documented , the next step is to
create conceptual schema which carried out in conceptual
design Phase.
It describes the structure of a db in the form of entity type,
relationship among them & constraints.

70. 6/9/2024 70
Logical Design
The actual implementation of the db is carried out using
DBMS.
Physical Design
The last phase is the internal storage structures, indexes,
access paths, and file organizations for the db files are
specified.
In parallel with these activities, Application programs are
designed and implemented as db transactions.

71. 6/9/2024 71
EER Model-Enhanced or Extended E-R model
Using E-R modelonly the basic features of a db.
Some enhanced features such as Specialization,
Generalization, Union & aggregation can be shown using
EER model.
A.SPECIALIZATION:
The process of designating sub
grouping within an entity set..

72. 6/9/2024 72
E.g…..
Employee
IS A
Secretary Technician Manager
eid ename eaddr Job
Typing speed
Mgrid

73. 6/9/2024 73
It is also represented as
Employee
eid ename eaddr Job
d
SecretaryTechnicianManager
d
Job Type
Salary Type
Hourly Regular
Defining attribute
Sub classes

74. 6/9/2024 74
Generalization
The process of
defining a generalized
entity type from the
given entity types.

75. 6/9/2024 75
E.g…
CAR TRUCK
PriceMaxspeed
Vehicle
ID
No. of
seats
Vehicle
ID
Price
No. Of
Axles
Tonnage

76. 6/9/2024 76
Vehicle
Vehicle
ID
Price
d
CAR
TRUCK
Maxspeed
No. of
seats
Tonnage
No. Of
Axles

77. 6/9/2024 77
History of Database Systems
1950s and early 1960s:
–Data processing using magnetic tapes for storage
•Tapes provide only sequential access
–Punched cards for input
Late 1960s and 1970s:
–Hard disks allow direct access to data
–Network and hierarchical data models in widespread use
–Ted Codd defines the relational data model
•Would win the ACM Turing Award for this work
•IBM Research begins System R prototype
•UC Berkeley begins Ingres prototype
–High-performance (for the era) transaction processing

78. 6/9/2024 78
History (cont.)
1980s:
–Research relational prototypes evolve into commercial
systems
•SQL becomes industrial standard
–Parallel and distributed database systems
–Object-oriented database systems
1990s:
–Large decision support and data-mining applications
–Large multi-terabyte data warehouses
–Emergence of Web commerce
2000s:
–XML and XQuery standards
–Automated database administration

79. 6/9/2024 79

80. 6/9/2024 80
Relational Model:
It uses a collection of tables to
represent both data and
relationships among those data.
Basic Structure
In this model each table is called as
relation. Each relation has a no. of
row is called tuples and columns
called as fields or attributes.

81. 6/9/2024 81
For an attribute, a range of permitted
values can be specified. That range
is called as Domain of that attribute.
E.g.. Age –attribute
 Domain (Age) = {1,2…100}
Relational Model:

82. 6/9/2024 82
A table of n attributes must be a
subset of
D1*D2*…….Dn
E.g..
Relational Model:
Eid Ename Eaddr
1
2
3
Aaa
Bbb
ccc
South street
West street
New
Employee Relation

83. 6/9/2024 83
To denote the value of attribute ‘a’ on
second tuple, then it can be denoted
as t2[a]=value.
E.g..
t2[ename]=“bbb”.
Relational Model:

84. 6/9/2024 84
Relation schema
It is the description about the
structure of the relation and the
relation instance is the snapshot of
data in the relation at a given instant
in time.
E.g.. Employee_schema=(eid,ename,eaddr)
Name of the schema

85. 6/9/2024 85
Empoyee(Employee_schema) denote that
Employee is a relation on
Employee_schema
Relation schema

86. 6/9/2024 86
Keys
Strong Entity set:
 The Primary key of the entity set becomes
the primary key of the relation.
Weak Entity Set:
 The tablecorresponding a weak entity set
includes
the attributes of the weak entity set &
primary key of the strong entity set on which
the weak entity set depends.

87. 6/9/2024 87
Relationship Set:
The union of the primary key of the related
entity sets becomes a super key of the
relation.
Combined Tables:
If there exists many to one relationship set
from A to B, then the prim.key of A becomes
the prim.key of the relation & the relation
consists of the attribues of A and attributes of
the relationship set.
Keys

88. 6/9/2024 88
Keys
Foreign Key:
An attribute of a relation r1 that
references relation r2 is called as
foreign key attribute.
Schema Diagram:
A relation schema can be depicted
pictorially by schema diagram,

89. 6/9/2024 89
E.g..
Keys
eidEname eaddr dno
dnamedno dloc
Employee
pnamepno eid
Dept
Project

90. 6/9/2024 90
Query Languages:
It is a lang in which a user requests
information from db.
Query Languages
Procedural Language Non Procedural
Language
Specifies what data are
required & specify how to get
those data e.g Relational
Algebra
Specifies what data are
required without specify how
to get those data e.g.
Relational calculus

91. 6/9/2024 91
Relational Algebra
It consistes of a set of operations
Select
Project
Union
Set difference
Intersection
Cartesian Product
Join
Division
Rename

92. 6/9/2024 92
Select
It selects tuples that satisfy a given
predicate, To denote selection
(Sigma) is used.
Syntax:
cond
(Table name)

93. 6/9/2024 93
Select
E.g..
Sal>1000(Employee)
Selects tuples whose emp sal is > 1000

94. 6/9/2024 94
Project
It selects attributes from the relation.
II –Symbol for project
Syntax: ll
<Attribute
list>
(table name)
E.g…
Eid,sal
(employee)ll
Selects all
tuples from
emp relation
& only eid &
sal attributes
are projected

95. 6/9/2024 95
Join
Natural Join (or) Equi Join
Outer Join
Natural Join (or) Equi Join
-Used to combine related tuples from two relations.
-It requires that the two join attributes have the same name.
-Symbol:

96. 6/9/2024 96
Join
Syntax:

97. 6/9/2024 97
Select Operation:
It selects tupules that satisfy a given
predicate.
To denote selection -

98. 6/9/2024 98
END OF UNIT-1
References:
Database system concepts
Siberschatz, korth and sudharsan