Database Management System

Chapter One: Introduction to database

 Outlines
◼ Basic concepts and definition
◼ Data management levels
◼ Database applications
◼ Database Management System
◼ Database actors

2
 Data management levels
◼ Data management passes through the different levels of
development along with the development in technology
and services.
◼ These levels could best be described by categorizing the
levels into three levels of development.
◼ Even though there is an advantage and a problem
overcome at each new level, all methods of data
handling are in use to some extent.

3
 Data Management levels
◼ The major three levels are:-
◼ Manual Approach

◼ Traditional File Based Approach

◼ Database Approach

4
 1. Manual Approach
In the manual approach, data storage and retrieval follows
the primitive and traditional way of information handling
where cards and paper are used for the purpose.

5
 Limitations of the Manual approach
◼ Prone to error
◼ Difficult to update, retrieve, integrate
◼ You have the data but it is difficult to compile the
information
◼ Limited to small size information
◼ Cross referencing is difficult

6
 2. Traditional File Based Approach
◼ File based systems were an early attempt to
computerize the manual filing system.
◼ This approach is the decentralized computerized data
handling method.
◼ A collection of application programs perform services
for the end-users.
◼ In such systems, every application program that
provides service to end users define and manage its
own data.

7
 2. Traditional File Based Approach
◼ Such systems have number of programs for each of the
different applications in the organization.
◼ Since every application defines and manages its own
data, the system is subjected to serious data duplication
problem.
◼ File, in traditional file based approach, is a collection of
records which contains logically related data.

8
2. Traditional File Based Approach

9
 Limitations of the Traditional File Based approach

◼ Separation or Isolation of Data

◼ Limited data sharing
◼ Duplication or redundancy of data
◼ Data dependency on the application
◼ Fixed query processing

10
 Limitations of the Traditional File Based approach

◼ The most significant problem experienced by the

traditional file based approach of data handling is the
“update anomalies”. We have three types of update
anomalies;
◼ Modification Anomalies

◼ Deletion Anomalies

◼ Insertion Anomalies

11
 Limitations of the Traditional File Based approach

◼ Modification Anomalies: a problem experienced when

one or more data value is modified on one application
program but not on others containing the same data set.
◼ Deletion Anomalies: a problem encountered where one
record set is deleted from one application but remain
untouched in other application programs.
◼ Insertion Anomalies: a problem encountered where
one cannot decide whether the data to be inserted is
valid and consistent with other similar data set

12
 3. Database Approach

What is database?
◼ Database is an organized collection of logically related
data
◼ Database is a shared collection of logically related data

designed to meet the information needs of an

organization
◼ Database is a collection of logically related data where
these logically related data comprises entities, attributes,
relationships, and business rules of an organization's
information.

13
 3. Database Approach

◼ database also contains a description of the data which

called as“Metadata” or “Data Dictionary” or
“Systems Catalogue” or “Data about Data”.
◼ Metadata: Data that describe the properties or
characteristics of end-user data, and the context of
that data.

14
 Benefits of the database approach

◼ Data can be shared

◼ Improved accessibility of data
◼ Redundancy can be reduced
◼ Quality data can be maintained
◼ Inconsistency can be avoided
◼ Transaction support can be provided
◼ Integrity can be maintained

15
 Benefits of the database approach

◼ Security majors can be enforced

◼ Improved decision support
◼ Standards can be enforced
◼ Compactness
◼ Less labour
◼ Centralized information control

16
 Limitations and risk of Database Approach
◼ Introduction of new professional and specialized
personnel.
◼ Complexity in designing and managing data
◼ The cost and risk during conversion from the old to the
new system
◼ High cost incurred to develop and maintain
◼ Complex backup and recovery services from the users
perspective
◼ Reduced performance due to centralization
◼ High impact on the system when failure occur

17
 Database applications
◼ Banking: transactions
◼ Airlines: reservation , schedules
◼ Universities:registration, grades
◼ Sales:customers ,sales purchases
◼ Online retailers:order tracking
◼ Manufacturing: production,inventory,orders,supply
chain
◼ Human resource: employee records,salaries ,tax
deductions

18
 University database example
◼ Application program examples
◼ Add new students, instructors and courses

◼ Register students for courses and generate class rosters

◼ Assign grades to students

◼ Compute grade point averages(GPA)

◼ Generate transcripts

19
 Database users and administrator
◼ Native users

◼ Application programmer

◼ Sophisticated uses

◼ Specialized user

◼ Online users

20
 Database users and administrator
◼ Native users
◼ Those who need not be aware the presence of database
systems
◼ These are end users who work though menu driven
applications

◼ Application programmer
◼ Are responsible for developing application programs/user interfaces
written in high level language

21
 Database users and administrator
◼ Sophisticated uses
◼ Are users familiar with the structure of the Database and
facilities of the DBMS.
◼ Have complex requirements
◼ Have higher level queries
◼ Are most of the time engineers, scientists, business analysts, etc

◼ Specialized user
◼ Who rights specialized database applications that do not fit into
fractional database processing framework

◼ Online users
◼ Who may communicate with database directly though online
22
 Database Administrator
◼ A person/group in charge for implementing database
system in an organization.
◼ The DBA has all privileges allowed by the database
management system. He can assign or remove
privileges from the users.

23
 Database Management System(DBMS)

◼ Database Management System (DBMS): A software

system that is used to create, maintain, and provide
controlled access to user databases.
Examples
◼ Oracle

◼ My SQL

◼ SQL Server

24
 Database
◼ Massive
◼ Persistent
◼ Safe
◼ Multi-user
◼ Continent
◼ Efficient
◼ reliable

25
 Key people
◼ DBMS implementer
◼ Builds system
◼ Database designer
◼ Establishes schema
◼ Database application developer
◼ Programs that operate a database
◼ Database administrator
◼ Loads data ,keeping running smoothly

26
Fundamentals of Database Systems

Chapter 2: Database System Concepts and

Architecture
 Outlines
◼ Data Models, Schema and Instances

◼ DBMS Architecture and Data Independence

◼ Database Language and Interface

◼ The Database System Environment

◼ Classification of DBMS

28
 DBMS Architecture
There are three levels
◼ External level/view level

◼ Conceptual level/logical level

◼ Internal level/physical/storage level

29
 DBMS Architecture
◼ External Level: Users' view of the database. Describes
that part of database that is relevant to a particular user.
Different users have their own customized view of the
database independent of other users.
◼ Conceptual Level: Community view of the database.
Describes what data is stored in database and
relationships among the data.
◼ Internal Level: Physical representation of the database
on the computer. Describes how the data is stored in
the database.

30
 DBMS Architecture
External schemas at the external level to describe the
various user views. Usually uses the same data model as
the conceptual level
Conceptual schema at the conceptual level to describe the
structure and constraints for the whole database for a
community of users. Uses a conceptual data model.
◼ Conceptual schema represents:-

◼ All entities, attributes and relationships

◼ constraints on the data
◼ Security and integrity information

31
 DBMS Architecture

Internal schema at the internal level to describe physical

storage structures and access paths. Typically uses a
physical data model.
◼ it covers :

◼ Data structure
◼ File organizations

32
 ANSI-SPARC Three-level Architecture

◼ There are three levels

33
 ANSI-SPARC Architecture and Database Design Phases

◼ The contents of the external, conceptual and internal

levels The purpose of the external/conceptual and the
conceptual/internal mappings

34
 DBMS schemas at three levels:

Differences between Three Levels of ANSI-SPARC Architecture

35
 Data independence
Logical Data Independence:
◼ Refers to immunity of external schemas to changes in
conceptual schema.
◼ Conceptual schema changes e.g. addition/removal of entities
should not require changes to external schema or rewrites of
application programs.
◼ The capacity to change the conceptual schema without
having to change the external schemas and their application
programs

36
 Data independence
Physical Data Independence
◼ The ability to modify the physical schema without changing the
logical schema
◼ The capacity to change the internal schema without having to
change the conceptual schema
◼ Refers to immunity of conceptual schema to changes in the
internal 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

37
 Database Languages
Data Definition Language (DDL)
◼ Allows DBA or user to describe and name entitles, attributes
and relationships required for the application.
◼ Specification notation for defining the database schema
Data Manipulation Language (DML)
◼ Provides basic data manipulation operations on data held in
the database.
◼ Language for accessing and manipulating the data organized
by the appropriate data model
◼ DML also known as query language

38
 Database Languages
DML can be procedural or non-procedural
◼ Procedural DML: user specifies what data is required and how

to get the data.

◼ Non-Procedural DML: user specifies what data is required but

not how it is to be retrieved

◼ SQL is the most widely used non-procedural language query language
◼ Fourth Generation Language (4GL)

◼ Query Languages

◼ Forms Generators

◼ Report Generators

◼ Graphics Generators

◼ Application Generators

39
 Data Model
What is data model?
◼ Data Model: a set of concepts to describe the
structure of a database, and certain constraints that the
database should obey.
◼ A data model is a description of the way that data is
stored in a database. Data model helps to understand
the relationship between entities and to create the most
effective structure to hold data.

40
 Data Model…
Data Model is a collection of tools or concepts for
describing
◼ Data
◼ Data relationships
◼ Data semantics
◼ Data constraints
◼ The main purpose of Data Model is to represent the
data in an understandable way.

41
 Categories of data models include
Categories of data models include:
◼ Object-based
◼ Record-based
◼ Physical
Object-based Data Models
◼ Entity-Relationship
◼ Semantic
◼ Functional
◼ Object-Oriented

42
 Data Model…

◼ We have three major types of data models

◼ Hierarchical Model
◼ Network Model
◼ Relational Data Model

43
 Hierarchical Model
◼ The simplest data model
◼ Record type is referred to as node or segment
◼ The top node is the root node
◼ Nodes are arranged in a hierarchical structure as sort of
up sidedown tree
◼ A parent node can have more than one child node
◼ A child node can only have one parent node
◼ The relationship between parent and child is one-to-
many

44
 Hierarchical Model
◼ Relation is established by creating physical link between
stored records (each is stored with a predefined access path to
other records)
◼ To add new record type or relationship, the database must be
redefined and then stored in a new form.

Department

Employee Job

Time Card Activity

45
 Hierarchical Model
Advantages of hierarchical data model:
◼ Hierarchical Model is simple to construct and operate on

◼ Corresponds to a number of natural hierarchically organized

domains - e.g., assemblies in manufacturing, personnel

organization in companies
◼ Language is simple; uses constructs like GET, GET UNIQUE,

GET NEXT, GET NEXT WITHIN PARENT etc.

Disadvantages of hierarchical data model:
◼ Navigational and procedural nature of processing

◼ Database is visualized as a linear arrangement of records

◼ Little scope for "query optimization

46
 Network Model
◼ Allows record types to have more that one parent unlike
hierarchical model
◼ A network data models sees records as set members
◼ Each set has an owner and one or more members
◼ Allow no many to many relationship between entities
◼ Like hierarchical model network model is a collection
of physically linked records.
◼ Allow member records to have more than one owner

47
 Network Model

Department Job

Employee

Activity

Time Card

48
 Network Model
Advantages of network data model:
◼ Network Model is able to model complex relationships and
represents semantics of add/delete on the relationships.
◼ Can handle most situations for modeling using record types
and relationship types.
◼ Language is navigational; uses constructs like FIND, FIND
member, FIND owner, FIND NEXT within set, GET etc.
Programmers can do optimal navigation through the database.
Disadvantages of network data model:
◼ Navigational and procedural nature of processing
◼ Database contains a complex array of pointers that thread
through a set of records.
◼ Little scope for automated "query optimization”
49
 Relational model
Database = set of named relations(or tables)
◼ Attribute
◼ Tuple
Schema =structural description of relations in a database
schema includes name of relations, attribute ,types of
each attribute
Instance = actual contents at given point in time
NULL = special value “unknown ” “undefined”

50
 Relational model
◼ Developed by Dr. Edgar Frank Codd in 1970 (famous
paper, 'A Relational Model for Large Shared Data
Banks')
◼ Terminologies originates from the branch of
mathematics called set theory and relation
◼ Can define more flexible and complex relationship
◼ Viewed as a collection of tables called “Relations” equivalent to
collection of record types

51
Relational model…
◼ Viewed as a collection of tables called “Relations” equivalent to collection
of record types
◼ Relation: Two dimensional table
◼ Stores information or data in the form of tables rows and columns
◼ A row of the table is called tuple equivalent to record
◼ A column of a table is called attribute equivalent to fields
◼ Data value is the value of the Attribute
◼ Records are related by the data stored jointly in the fields of records in two
tables or files. The related tables contain information that creates the
relation
◼ The tables seem to be independent but are related some how.
◼ No physical consideration of the storage is required by the user
◼ Many tables are merged together to come up with a new virtual view of the
relationship

52
Relational model…

53
 Relational model…
◼ The rows represent records (collections of information about
separate items)
◼ The columns represent fields (particular attributes of a record)
◼ Conducts searches by using data in specified columns of one
table to find additional data in another table
◼ In conducting searches, a relational database matches
information from a field in one table with information in a
corresponding field of another table to produce a third table
that combines requested data from both tables

54
 Relational model…
Properties of Relational Databases
◼ Each row of a table is uniquely identified by a primary
key composed of one or more columns
◼ Each tuple in a relation must be unique
◼ Group of columns, that uniquely identifies a row in a
table is called a candidate key
◼ entity integrity rule of the model states that no
component of the primary key may contain a NULL
value.

55
 Relational model…
Properties of Relational Databases
◼ A column or combination of columns that matches the
primary key of another table is called a foreign key.
Used to cross-reference tables.
◼ The referential integrity rule of the model states that,
for every foreign key value in a table there must be a
corresponding primary key value in another table in the
database or it should be NULL.
◼ All tables are logical entities

56
 Relational model…
Properties of Relational Databases
◼ A table is either a BASE TABLES (Named Relations)
or VIEWS (Unnamed Relations)
◼ Only Base Tables are physically stores
◼ VIEWS are derived from BASE TABLES with SQL
instructions like:
◼ [SELECT .. FROM .. WHERE .. ORDER BY]
◼ Is the collection of tables o Each entity in one table
◼ Attributes are fields (columns) in table

57
 Relational model…
Properties of Relational Databases
◼ Order of rows and columns is immaterial
◼ Entries with repeating groups are said to be un-

normalized
◼ Entries are single-valued

◼ Each column (field or attribute) has a distinct name

All values in a column represent the same attribute and

have the same data format

58
 Building Blocks of the Relational Data Model

The building blocks of the relational data model are:

◼ Entities: real world physical or logical object

◼ Attributes: properties used to describe each Entity or

real world object.
◼ Relationship: the association between Entities

◼ Constraints: rules that should be obeyed while

manipulating the data.

59
 Building Blocks of the Relational Data Model

Entities (persons, places, things etc.) which the

organization has to deal with. Relations can also
describe relationships
Example : student, employee, course, instructor
Attributes - the items of information which characterize
and describe these entities.

60
Fundamentals of Database Management
Systems

Chapter three : The ER Model

 Outlines
◼ The high-level conceptual model
◼ Entities, Attributes, and Keys
◼ Relationships, Associations, and Constraints
◼ The ER Diagrams
◼ Mapping ER-models to relational tables

62
 The E-R Model: over view
◼ An entity-relationship model (or E-R model) is a
detailed, logical representation of the data for an
organization or for a business area.
◼ The E-R model is expressed in terms of entities in the
business environment, the relationships (or
associations) among those entities, and the attributes
(or properties) of both the entities and their
relationships.
◼ An E-R model is normally expressed as an entity-
relationship diagram (or E-R diagram, or simply ERD),
which is a graphical representation of an E-R model.

63
The E-R Model

64
 The E-R Model

Entity
◼ PRODUCT

◼ ORDER

◼ ITEM

◼ SUPPLIER

◼ SHIPMENT T

65
 Drawing tools
◼ Microsoft Visio
◼ Oracle Designer
◼ All Fusion ERWin
◼ Power Designer

66
 Building Blocks of the Relational Data Model
The building blocks of the relational data model are:
◼ Entities: real world physical or logical object

◼ Attributes: properties used to describe each Entity or

real world object.
◼ Relationship: the association between Entities

◼ Constraints: rules that should be obeyed while

manipulating the data.

67
 Building Blocks of the Relational Data Model
Entity: A person, place, object, event, or concept in the
user environment about which the organization wishes to
maintain data.
Thus, an entity has a noun name. Some examples of each
of these hinds of entities follow:
Person: EMPLOYEE, STUDENT, PATIENT
Place: STORE, WAREHOUSE, STATE
Object: MACHINE, BUILDING, AUTOMOBILE
Event: SALE, REGISTRATION, RENEWAL
Concept: ACCOUNT, COURSE, WORK CENTER

68
 Building Blocks of the Relational Data Model
◼ Entity type: A collection of entities that share
common properties or characteristics.
◼ Entity instance: A single occurrence of an entity type.

69
 Types of entity
◼ Strong entity type: An entity that exists
independently of other entity types

◼ Weak entity type: An entity type that whose

existence depends on some other entity type.

70
 Attribute
◼ Attribute: A property or characteristic of an entity or
relationship type that is of interest to the organization.

71
 Types of Attributes
1. Simple (atomic) Vs Composite attributes
2. Single-valued Vs multi-valued attributes
3. Stored vs. Derived Attribute
4. Null Values

72
 Types of Attributes…
(1) Simple (atomic) Vs Composite attributes
◼ Simple : contains a single value (not divided into sub

parts)
◼ E.g. Age, gender
◼ Composite: Divided into sub parts (composed of other attributes)
◼ E.g. Name, address

73
 Types of Attributes …
(2) Single-valued Vs multi-valued attributes
◼ Single-valued : have only single value(the value may

change but has only one value at one time)

◼ E.g. Name, Sex, Id. No. color_of_eyes
◼ Multi-Valued: have more than one value
◼ E.g. Address, dependent-name
◼ Person may have several college degrees

74
 Types of Attributes…
Stored vs. Derived Attribute
◼ Stored : not possible to derive or compute

◼ E.g. Name, Address

◼ Derived: The value may be derived (computed) from
the values of other attributes.
Example
◼ Age (current year – year of birth)

◼ Length of employment (current date- start date)

◼ Profit (earning-cost)

◼ G.P.A (grade point/credit hours)

75
 Types of Attributes…

Null Values
◼ NULL applies to attributes which are not applicable or

which do not have values.

◼ You may enter the value NA (meaning not applicable)

◼ Value of a key attribute can not be null.

76
 Relationships
◼ Relationships are the glue that holds together the
various components of an E-R model.
◼ Intuitively, a relationship is an association
representing an interaction among the instances of one
or more entity types that is of interest to the
organization.
◼ Thus, a relationship has a verb phrase name.

77
 Relationship type and instances
(a) Relationship type (Completes)

78
 Relationship type and instances
(b) Relationship instances

79
 Relationship type and instances
Relationship type: A meaningful association between
(or among) entity types.
Relationship instance: An association between (or
among) entity instances where each relationship
instance includes exactly one entity from each
participating entity type.

80
 Degree of a Relationship

Degree: The number of entity types that participate in a

relationship.
Unary relationship: A relationship between the
instances of a single entity type.
Binary relationship: A relationship between the
instances of two entity types.
Ternary relationship: A simultaneous relationship
among the instances of three entity types.

81
 Cardinality Constraints
◼ Cardinality constraint: Specifies the number of
instances of one entity that can (or must) be
associated with each instance of another entity.
◼ Minimum cardinality: The minimum number of
instances of one entity that may be associated with
each instance of another entity.
◼ Maximum cardinality: The maximum number of
instances of one entity that may be associated with
each instance of another entity.

82
 Cardinality Constraints

Cardinality can be :-
◼ ONE-TO-ONE, e.g. Building - Location,

◼ ONE-TO-MANY, e.g. hospital - patient,

◼ MANY-TO-ONE, e.g. Employee - Department

◼ MANY-TO-MANY, e.g. Author - Book

83
 Cardinality Constraints

◼ Example

84
 Problem in ER Modeling
◼ While designing the ER model one could face a
problem on the design which is called a connection
traps. Connection traps are problems arising from
misinterpreting certain relationships .
◼ There are two types of connection traps;
◼ Fan trap
◼ Chasm Trap:

85
 Problem in ER Modeling
1.Fan trap:
◼ Occurs where a model represents a relationship between

entity types, but the pathway between certain entity

occurrences is ambiguous.
◼ May exist where two or more one-to-many (1:M)
relationships fan out from an entity. The problem could
be avoided by restructuring the model so that there
would be no 1:M relationships fanning out from a singe
entity and all the semantics of the relationship is
preserved.

86
 1.Fan trap:
◼ Example

87
 Fan trap …
◼ Problem: Which car (Car1 or Car3 or Car5) is used by
Employee 6 Emp6 working in Branch 1 (Bra1)?
◼ Thus from this ER Model one can not tell which car is
used by which staff since a branch can have more than
one car and also a branch is populated by more than one
employee.
◼ Thus we need to restructure the model to avoid the
connection trap.

88
 Fan trap…
◼ To avoid the Fan Trap problem we can go for
restructuring of the E-R Model. This will result in the
following E-R Model.

89
 Chasm Trap

◼ Occurs where a model suggests the existence of a

relationship between entity types, but the path way does
not exist between certain entity occurrences.
◼ May exist when there are one or more relationships with
a minimum multiplicity on cardinality of zero forming
part of the pathway between related entities.

90
 Chasm Trap…
Example

If we have a set of projects that are not active currently

then we can not assign a project manager for these
projects. So there are project with no project manager
making the participation to have a minimum value of zero.

91
 Chasm Trap:
Problem: How can we identify which BRANCH is
responsible for which PROJECT? We know that whether
the PROJECT is active or not there is a responsible
BRANCH. But which branch is a question to be answered,
and since we have a minimum participation of zero
between employee and PROJECT we can’t identify the
BRANCH responsible for each PROJECT.

92
 Chasm Trap…
◼ The solution for this Chasm Trap problem is to add
another relationship between the extreme entities
(BRANCH and PROJECT)

93
 Constraints
◼ Domain Integrity: No value of the attribute should be
beyond the allowable limits
◼ Entity Integrity: In a base relation, no attribute of a
primary key can be null
◼ Referential Integrity: If a foreign key exists in a
relation, either the foreign key value must match a
candidate key in its home relation or the foreign key
value must be null foreign key to primary key match-
ups
◼ Enterprise Integrity: Additional rules specified by the
users or database administrators of a database are
incorporated
94
 Key constraints
Key constraints
◼ If tuples are need to be unique in the database, and then we need
to make each tuple distinct. To do this we need to have relational
keys that uniquely identify each relation.
◼ Super Key: an attribute or set of attributes that uniquely
identifies a tuple within a relation.
◼ Candidate Key: a super key such that no proper subset of that
collection is a Super Key within the relation. A candidate key has
two properties:
1.Uniqueness
2.Irreducibility
If a candidate key consists of more than one attribute it is called
composite key.
95
 Key constraints
◼ Primary Key: the candidate key that is selected to
identify tuples uniquely within the relation.
◼ The entire set of attributes in a relation can be considered as a
primary case in a worst case.
◼ Foreign Key: an attribute, or set of attributes, within
one relation that matches the candidate key of some
relation.
◼ A foreign key is a link between different relations to create
the view or the unnamed relation

96
 Relational languages and views
◼ The languages in relational database management
systems are the DDL, SDL, VDL and the DML that
are used to define or create the database and perform
manipulation on the database.
◼ We have the two kinds of relation in relational
database.
◼ The difference is on how the relation is created, used
and updated:

97
 Relational languages and views
1. Base Relation
◼ A Named Relation corresponding to an entity in the
conceptual schema, whose tuples are physically stored
in the database.
2. View
◼ Is the dynamic result of one or more relational
operations operating on the base relations to produce
another virtual relation. So a view virtually derived
relation that does not necessarily exist in the database
but can be produced upon request by a particular user at
the time of request.
98
 Relational languages and views
Purpose of a view
◼ Hides unnecessary information from users

◼ Provide powerful flexibility and security

◼ Provide customized view of the database for users

◼ A view of one base relation can be updated.

◼ Update on views derived from various relations is not allowed

◼ Update on view with aggregation and summary is not allowed

99
Symbols to draw ERD

100
Symbols to draw ERD

101
ERD example

102