UNIT I: Introduction

To Databases

1
 Unit points
1. Introduction DBMS
2. Purpose
3. Applications
4. View of Data
5. DB Languages
6. DB Structure
7. DB Design and Models
8. ER Model
9. Design Process
[Link] ER model design issues
2

[Link] diagram to Tables

3
D
B
M
S

U
n
i Reference
t Chapter1 of Korth, Sudarshan “Database
1 Systems Concept” 5th Edition

3
4
D
B
M
S
Database Management System
U
(DBMS)
n What is data?
i
t
DBMS is a Collection of interrelated data and a set of programs to
1 access those Data.

• The Primary Goal is to provide a way to store and retrieve

database information that is both convenient and efficient

4
5
D
B
M
S Database Applications:
U
n
i  Banking: all transactions
t  Airlines: reservations, schedules
 Universities: registration, grades
1  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

5
Advantages

 Data independence
 Reduced data redundancy
 Increased security
 Better flexibility
 Effective data sharing
 Enforces integrity constraints
 Enables backup and recovery

6
Disadvantages

 Cost of Hardware and Software

 Cost of Data Conversion
 Cost of Staff Training
 Appointing Technical Staff
 Database Damage
 Vulnerability

7
 Purpose/need of DBMS
 Drawbacks of using file systems to store
data:
 Data redundancy and inconsistency
 Data inconsistency: Several copies of same data my not
agree with each other over a time period.
 Difficulty in accessing data
 Data isolation — multiple files and formats
 Integrity problems-Consistency Constraint
 Atomicity problem
 Concurrent access by multiple users
 Security problem

8
 View of Data
Major purpose of a database system is to provide users
with an abstract view of the data

View of required
information Type definition
describes data

Storage blocks
describes how a
record is stored
10
Data Abstraction
 Physical level: describes how the data (e.g.,
customer) is stored.
 Logical level: describes what data stored in
database, and the relationships among the data.
type customer = record
customer_id : string;
customer_name : string;
customer_street : string;
customer_city : 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

11
An example of three levels

12
 Instances
 Schema

13
 Similar to types and variables in
programming languages
 • Schema – the logical structure of the
database
 – e.g., 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
14
level
15
DBMS-Data Base
Management System
 DBMS contains information about a particular
enterprise
 Collection of interrelated data
 Set of programs to access the data
 An environment that is both convenient and efficient to use

16
DBMS System

17
DB Users and Administrator

Users-
 Naïve users
 Application Programmers
 Sophisticated Users (Query Analyst & DM)
 Specialized Users

Administrator(DBA) and its functions-

 Creation (DDL) and Modification
 Routine Maintenance
 Granting of Authorization

18
1
D
9
B
M
S
Components of DBMS
U
n
i Storage manager
t Query processor
Transaction management
1
Data mining and analysis

19
2
D
0
B
M
S
Storage Management
Storage manager : provides the interface
U
n between the low-level data and the application prg
i
t The storage manager is responsible to the
1 following tasks:
Interaction with the file manager
Efficient storing, retrieving and updating of data
Issues:
Storage access
File organization
Indexing and hashing

20
2
D
1
B
M
S Storage Management (contd.)
U The storage manager includes
n Authorization and integrity Manager
i File manager
t Buffer manager

1
Data structures of storage manager
Data file
Data dictionary
Indices

21
2
D
2
B
M
S Query Processing
U
n
1. Parsing and translation
i 2. Optimization 3. Evaluation
t

22
2
D
4
B
M
S
Transaction Management
U A transaction is a collection of operations to
n achieve o/p
i
t
Transaction-management component ensures
1
that the database remains in a consistent (correct)
state despite system failures
Concurrency-control manager

24
2
D
5
B
M
S Data Mining and Analysis
U
Refers to the process of semiautomatically
n
i analyzing large database to find useful patterns.
t There is also a manual component consisting of
1 preprocessing data to a acceptable form.
Useful for decision support system (DSS)

25
2
D
6
B
M
S
Overall System Structure
U
n
i
t

26
2
D
7
B
M
S Database Architecture
U
n The architecture of a database systems is
i greatly influenced by
t
the underlying computer system on which
1
the database is running:
Centralized
Client-server
Parallel (multi-processor)
Distributed

27
2
D
8
B
M
S
Two-tier and three-tier
U
Architecture
n
i
t

28
Data Models
Record Based model
Relational(table)

-Structural independence
Network(graph)

-Data independence but

not structural independence
Hierarchical(tree)

-Lack of structural
independence

30
Hierarchical Model

31
Network Model

32
Entity-relationship Model

33
Relational Model

34
Relational Model
 Relational model :-
 Rows=tuples columns=attributes Columns
 Example of tabular data in the relational model

Rows

35
36
37
38
39
 Object-Relational Data Models
•Extend the relational data model by
including object orientation and constructs
to deal with added data types.

•Allow attributes of tuples to have complex

types, - nested relations.
•Preserve relational foundations, in
particular the declarative access to data,
while extending modeling power.

•Provide upward compatibility with existing

relational languages. 40
4
D
1
B
M
S
Database Languages
U DDL : Data Definition language
n Specification notation for defining the database schema
i
t
DML : Data Manipulation language
1
Language for accessing and manipulating the data
organized by the appropriate data model

41
4
D
2
B
M
S Data Definition Language (DDL)
U
Specification notation for defining the database
n schema
i Example: create table account (
t  account-number char(10),
1 balance integer)
DDL compiler generates a set of tables stored in a
data dictionary

42
4
D
3
B
M
S Data Definition Language (DDL)
U Data dictionary contains metadata (i.e., data
n
i
about data)
Database schema
t
Data storage and definition language
 Specifies the storage structure and access methods
1
used
Integrity constraints

Authorization

43
4
D
4
B
M Data Manipulation Language
S
(DML)
U
Language for accessing and manipulating the
n
i data organized by the appropriate data model
t DML also known as query language
1
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

44
4.E-R modeling

E-Rmodel definition
Symbols used in diagram
Example

45
E-R Diagram
 Models an enterprise as a collection of entities and
relationships
 Entity: a “thing” or “object” in the enterprise that is distinguishable
from other objects
 Described by a set of attributes
 Relationship: an association among several entities
 Represented diagrammatically by an entity-relationship
diagram:

46
Modeling

 Anentity set is a set of entities of the

same type that share the same properties.

 Example: set of all persons, companies, trees, holidays

A database is a collection of entity sets,

each of which contains entities of same
type

47
47
 Weak entity is an entity that depends on another
entity. Weak entity doesn't have anay key
attribute of its own. Double rectangle is used to
represent a weak entity.

48
Relationship Sets (Cont.)
 The association between entity sets is
referred to as participation
 The entity sets E1, E2, … En participate in the relation

A relationship instance is an E-R schema

that represents an association between
the named entities in the real world
enterprise, that is being modeled.
 Example : “Hayes” – “borrows” a loan numbered “ A-102”

49
49
Relationship
 Unary
 Binary
 Ternary

Keys
Primary
Super
Candidate
Foreign
50
 ER Model: Attributes
 Simple attribute: The attributes with values that are atomic
and cannot be broken down further are simple attributes. For
example, student's age.
 Composite attribute: A composite attribute is made up of
more than one simple attribute. For example,
student's address will contain, house no., street
name, pincode etc.
 Derived attribute: These are the attributes which are not
present in the whole database management system, but are
derived using other attributes. For example, average age of
students in a class.
 Single-valued attribute: As the name suggests, they have a
single value.
 Multi-valued attribute: And, they can have multiple values.

51
52
Weak entity is an entity that depends on another
entity. Weak entity doesn't have any key attribute of
its own. Double rectangle is used to represent a weak
entity.

53
5
D
4
B
M Cardinality Constraints
S
We express cardinality constraints by
U
n drawing either a directed line (), signifying
i “one,” or an undirected line (—), signifying
t
“many,” between the relationship set and the
1 entity set.

54
55
5
D
6
B
M
S
One-To-Many Relationship
In the one-to-many relationship
U
n a loan is associated with at most one customer via
i borrower,
t
a customer is associated with several (including
1 0) loans via borrower

56
5
D
7
B
M Many-To-One Relationships
S
In a many-to-one relationship a loan is
U
n associated with several (including 0) customers
i via borrower, a customer is associated with at
t
most one loan via borrower
1

57
5
D
8
B
M
S
Many-To-Many Relationship
U
n A customer is associated with several
i (possibly 0) loans via borrower
t
A loan is associated with several (possibly 0)
1 customers via borrower

58
 E-R diagrams Symbol
partial Total

E1 R E2

1 N
E1 R E2

Cardinality Ratio

59
59
E-R Diagrams

 Rectangles represent entity sets.

 Diamonds represent relationship sets.
 Lines link attributes to entity sets and entity sets to
relationship sets.

60
60
Relationship Sets with
Attributes

61
61
 E-R Diagram With Composite, Multivalued, and
Derived Attributes

Composit
Primary e
Key Attribute

Multi-
valued
attribute
Derived
62
Attribute 62
63
64
The Enhanced ER Model

 Generalization
 Specialization
 Aggregration

65
Generalization
 Generalization is a bottom-up approach in which two lower
level entities combine to form a higher level entity.
 In generalization, the higher level entity can also combine with
other lower level entities to make further higher level entity.

66
 Specialization
 It is a top-down approach in which one higher level entity
can be broken down into two lower level entity.
 In specialization, a higher level entity may not have any
lower-level entity sets, it's possible.

67
 Aggregration

 Aggregration is a process when relation between

two entities is treated as a single entity.

68
69
7
D
0
B
Extended E-R Features: Specialization
M
S
Top-down
U
n
i
t
subgroupings within an entity set that are distinctive
from other entities in the set.
1

Depicted by a triangle component labeled ISA (E.g.

customer “is a” person).

70
7
D
1
B Specialization Example
M
S

U
n
i
t

71
Example :

 Draw the ER diagram for Banking Enterprise

72
 Disjoint

Overlap (o)
The subclasses may have
overlapping sets of entities

Disjoint (d)
The subclasses must have
disjoint sets of entities

74
Participation Constraints

 Total Participation − Each entity is involved in the relationship.

Total participation is represented by double lines.

 Partial participation − Not all entities are involved in the

relationship. Partial participation is represented by single lines.

75
completeness

76
Draw ER diagram for University hierarchy using specialization lattice

77
Converting E-R diagrams into
tables
 Entity gets converted into Table, with all the
attributes becoming fields(columns) in the table.
 Relationship between entities is also converted
into table with primary keys of the related entities
also stored in it as foreign keys.
 Primary Keys should be properly set.
 For any relationship of Weak Entity, if primary key
of any other entity is included in a table, foriegn
key constraint must be defined.

78