MI7402 /Data Management Department of MBA(Integrated)

UNIT I DATABASE MANAGEMENT SYSTEMS: 9

Data, Database, Database Management Systems, Types of Database Management Systems – Relational,
Hierarchical, Network, and Object oriented database management systems, Entity Relationship Model (E-
R Model)
INTRODUCTION
DATABASE
Database is collection of data which is related by some aspect. Data is collection of facts and figures
which can be processed to produce information. Mostly data represents recordable facts. Data aids in
producing information which is based on facts. A database management system stores data, in such a way
which is easier to retrieve, manipulate and helps to produce information.
So a database is a collection of related data that we can use for
 Defining - specifying types of data
 Constructing - storing & populating
 Manipulating - querying, updating, reporting

DISADVANTAGES OF FILE SYSTEM OVER DB

In the early days, File-Processing system is used to store records. It uses various files for storing the
records.
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
- Hard to add new constraints or change existing ones
 Atomicity problem
-Failures may leave database in an inconsistent state with partial updates carried
Out. E.g. transfer of funds from one account to another should either complete or not
happen at all
 Concurrent access anomalies
- Concurrent accessed needed for performance
 Security problems
Database systems offer solutions to all the above problems
PURPOSE OF DATABASE SYSTEM

The typical file processing system is supported by a conventional operating system. The system stores
permanent records in various files, and it needs different application programs to extract records from,
and add records to, the appropriate files. A file processing system has a number of major disadvantages.

 Data redundancy and inconsistency

 Difficulty in accessing data
 Data isolation – multiple files and formats
 Integrity problems
 Atomicity of updates
 Concurrent access by multiple users
 Security problems

St. Joseph’s College of Engineering Page 1

MI7402 /Data Management Department of MBA(Integrated)

1. Data redundancy and inconsistency:

In file processing, every user group maintains its own files for handling its data processing
applications.
Example:
Consider the UNIVERSITY database. Here, two groups of users might be the course registration
personnel and the accounting office. The accounting office also keeps data on registration and
related billing information, whereas the registration office keeps track of student courses and
[Link] the same data multiple times is called data [Link] redundancy leads to
several problems.
•Need to perform a single logical update multiple times.
•Storage space is wasted.
•Files that represent the same data may become inconsistent.
Data inconsistency is the various copies of the same data may no larger Agree.
Example:
One user group may enter a student's birth date erroneously as JAN-19-1984, whereas
the other user groups may enter the correct value of JAN-29-1984.
2. Difficulty in accessing data
File processing environments do not allow needed data to be retrieved in a convenient and
efficient manner.
3. Data isolation
Because data are scattered in various files, and files may be in different formats, writing new
application programs to retrieve the appropriate data is difficult.
4. Integrity problems
The data values stored in the database must satisfy certain types of consistency constraints.
Example:
The balance of certain types of bank accounts may never fall below a prescribed amount .
Developers enforce these constraints in the system by addition appropriate code in the various
application programs
5. Atomicity problems
Atomic means the transaction must happen in its entirety or not at all. It is difficult to ensure
atomicity in a conventional file processing system.
Example:
Consider a program to transfer $50 from account A to account B. If a system failure occurs during the
execution of the program, it is possible that the $50 was removed from account A but was not credited
to account B, resulting in an inconsistent database state.

6. Concurrent access anomalies

For the sake of overall performance of the system and faster response, many systems allow multiple
users to update the data simultaneously. In such an environment, interaction of concurrent updates is
possible and may result in inconsistent data. To guard against this possibility, the system must
maintain some form of supervision. But supervision is difficult to
provide because data may be accessed by many different application programs that have not been
coordinated previously.
Example: When several reservation clerks try to assign a seat on an airline flight, the system should
ensure that each seat can be accessed by only one clerk at a time for assignment to a passenger.

St. Joseph’s College of Engineering Page 2

MI7402 /Data Management Department of MBA(Integrated)

7. Security problems
Enforcing security constraints to the file processing system is difficult.
APPLICATION OF DATABASE
Database Applications
 Banking: all transactions
 Airlines: reservations, schedules
 Universities: registration, grades
 Sales: customers, products, purchases
 Manufacturing: production, inventory, orders, supply chain
 Human resources: employee records, salaries, tax deductions
 Telecommunication: Call History, Billing
 Credit card transactions: Purchase details,Statements
VIEWS OF DATA
It refers that how database is actually stored in database, what data and structure of data used by
database for data. So describe all this database provides user with views and these are
 Data abstraction
 Instances and schemas
Data abstraction
As a data in database are stored with very complex data structure so when user come and want to
access any data, he will not be able to access data if he has go through this data structure. So to
simplify the interaction of user and database, DBMS hides some information which is not of
user
interest, a this is called data abstraction:- So developer hides complexity from user and store abstract
view of data.
Data abstraction has three level of abstractions
 level / internal level
 Logical level / conceptual level
 view level / external level
Physical level:- this is the lowest level of data abstraction which describe How data is actual stored in
database. This level basically describe the data structure and access path /indexing use for
accessing file.
Logical level:- The next level of abstraction describe what data are stored in the database and what are
the relationship existed among those of data.
View level:- In this level user only interact with database and the complexity remain unview . user see
data and there may be many views of one data like chart and graph.

St. Joseph’s College of Engineering Page 3

MI7402 /Data Management Department of MBA(Integrated)

DATA MODELS IN DBMS

A Data Model is a logical structure of Database. It describes the design of database to reflect entities,
attributes, relationship among data, constrains etc.
Types of Data Models:
Object based logical Models – Describe data at the conceptual and view levels.
1. E-R Model
An entity–relationship model (ER model) is a systematic way of describing and defining a business
process. An ER model is typically implemented as a database. The main components of E-R model are:
entity set and relationship set.

2. Object oriented Model

An object data model is a data model based on object-oriented programming, associating methods
(procedures) with objects that can benefit from class hierarchies. Thus, ―objects‖ are levels of abstraction
that include attributes and behavior
Record based logical Models – Like Object based model, they also describe data at the conceptual and
view levels. These models specify logical structure of database with records, fields and attributes.
1. Relational Model
In relational model, the data and relationships are represented by collection of inter-related tables.
Each table is a group of column and rows, where column represents attribute of an entity and rows
represents records.

St. Joseph’s College of Engineering Page 4

MI7402 /Data Management Department of MBA(Integrated)

Sample relationship Model: Student table with 3 columns and three records.
Stu_Id Stu_Name Stu_Age
111 Ashish 23
123 Saurav 22
169 Lester 24
2. Hierarchical Model
In hierarchical model, data is organized into a tree like structure with each record is having one
parent record and many children. The main drawback of this model is that, it can have only one to
many relationships between nodes.
Sample Hierarchical Model Diagram:

3. Network Model – Network Model is same as hierarchical model except that it has graph-like
structure rather than a tree-based structure. Unlike hierarchical model, this model allows each
record to have more than one parent record.
Physical Data Models – These models describe data at the lowest level of abstraction.
Three Schema Architecture

The goal of the three schema architecture is to separate the user applications and the physical
database. The schemas can be defined at the following levels:
1. The internal level – has an internal schema which describes the physical storage structure of the
database. Uses a physical data model and describes the complete details of data storage and
access paths for the database.

2. The conceptual level – has a conceptual schema which describes the structure of the database for
users. It hides the details of the physical storage structures, and concentrates on describing
entities, data types, relationships, user operations and constraints. Usually a representational
data model is used to describe the conceptual schema.

3. The External or View level – includes external schemas or user vies. Each external schema
describes the part of the database that a particular user group is interested in and

hides the rest of the database from that user group. Represented using the representational data
model.
The three schema architecture is used to visualize the schema levels in a database. The three schemas
are only descriptions of data, the data only actually exists is at the physical level.

St. Joseph’s College of Engineering Page 5

MI7402 /Data Management Department of MBA(Integrated)

COMPONENTS OF DBMS
Database Users
Users are differentiated by the way they expect to interact with the system
• Application programmers
• Sophisticated users
• Naïve users
• Database Administrator
• Specialized users etc,.
Application programmers:
Professionals who write application programs and using these application programs they
interact with the database system
Sophisticated users :
These user interact with the database system without writing programs, But they submit
queries to retrieve the information
Specialized users:
Who write specialized database applications to interact with the database system.
Naïve users:
Interacts with the database system by invoking some application programs that have been
written previously by application
programmers Eg : people accessing database
over the web Database Administrator:

St. Joseph’s College of Engineering Page 6

MI7402 /Data Management Department of MBA(Integrated)

Coordinates all the activities of the database system; the database administrator has a good
understanding of the enterprise‘s information resources and needs.
 Schema definition
 Access method definition
 Schema and physical organization modification
 Granting user authority to access the database
 Monitoring performance
Storage Manager
The Storage Manager include these following components/modules
 Authorization Manager
 Transaction Manager
 File Manager
 Buffer Manager
 Storage manager is a program module that provides the interface between the low-level data
stored in the database and the application programs and queries submitted to the system.
 The storage manager is responsible to the following tasks:
 interaction with the file manager
 efficient storing, retrieving and updating of data
Authorization Manager
 Checks whether the user is an authorized person or not
 Test the satisfaction of integrity constraints
Transaction Manager
Responsible for concurrent transaction execution It ensures that the database remains in a
consistent state despite of the system failure

DATABASE DESIGN
A well-designed database shall:
 Eliminate Data Redundancy: the same piece of data shall not be stored in more than one place.
This is because duplicate data not only waste storage spaces but also easily lead to
inconsistencies.
 Ensure Data Integrity and Accuracy
Entity-Relationship Data Model
 Classical, popular conceptual data model
 First introduced (mid 70‘s) as a (relatively minor) improvement to the relational model:
pictorial diagrams are easier to read than relational database schemas
 Then evolved as a popular model for the first conceptual representation of data structures in the

St. Joseph’s College of Engineering Page 7

MI7402 /Data Management Department of MBA(Integrated)

process of database design

ER Model: Entity and Entity Set
Considering the above example, Student is an entity, Teacher is an entity, similarly, Class, Subjectetc are
also entities.
An Entity is generally a real-world object which has characteristics and holds relationships in a
DBMS. If a Student is an Entity, then the complete dataset of all the students will be the Entity Set
ER Model: Attributes
If a Student is an Entity, then student's roll no., student's name, student's age, student's gender etc will be
its attributes.
An attribute can be of many types, here are different types of attributes defined in ER database model:
1. Simple attribute: The attributes with values that are atomic and cannot be broken down further
are simple attributes. For example, student's age.
2. 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.
3. 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.
4. Single-valued attribute: As the name suggests, they have a single value.
5. Multi-valued attribute: And, they can have multiple values.

ER Model: Relationships

When an Entity is related to another Entity, they are said to have a relationship. For example,
A ClassEntity is related to Student entity, because students study in classes, hence this is a relationship.
Depending upon the number of entities involved, a degree is assigned to relationships.
For example, if 2 entities are involved, it is said to be Binary relationship, if 3 entities are involved, it
is said to be Ternary relationship, and so on.

Working with ER Diagrams

ER Diagram is a visual representation of data that describes how data is related to each other. In ER
Model, we disintegrate data into entities, attributes and setup relationships between entities, all this
can be represented visually using the ER diagram.
Components of ER Diagram
Entitiy, Attributes, Relationships etc form the components of ER Diagram and there are defined symbols
and shapes to represent each one of them.
Let's see how we can represent these in our ER Diagram.

Entity
Simple rectangular box represents an Entity.

Relationships between Entities - Weak and Strong

Rhombus is used to setup relationships between two or more entities.

St. Joseph’s College of Engineering Page 8

MI7402 /Data Management Department of MBA(Integrated)

Attributes for any Entity

Ellipse is used to represent attributes of any entity. It is connected to the entity.

Weak Entity
A weak Entity is represented using double rectangular boxes. It is generally connected to another entity.

Key Attribute for any Entity

To represent a Key attribute, the attribute name inside the Ellipse is underlined.

Derived Attribute for any Entity

Derived attributes are those which are derived based on other attributes, for example, age can be derived
from date of birth.
To represent a derived attribute, another dotted ellipse is created inside the main ellipse.

Multivalued Attribute for any Entity

Double Ellipse, one inside another, represents the attribute which can have multiple values.

Composite Attribute for any Entity

A composite attribute is the attribute, which also has attributes.

St. Joseph’s College of Engineering Page 9

MI7402 /Data Management Department of MBA(Integrated)

ER Diagram: Entity
An Entity can be any object, place, person or class. In ER Diagram, an entity is represented using
rectangles. Consider an example of an Organisation- Employee, Manager, Department, Product
and many more can be taken as entities in an Organisation.

The yellow rhombus in between represents a relationship.

ER Diagram: Key Attribute
Key attribute represents the main characteristic of an Entity. It is used to represent a Primary key. Ellipse
with the text underlined, represents Key Attribute.

ER Diagram: Binary Relationship

Binary Relationship means relation between two Entities. This is further divided into three types.
One to One Relationship
This type of relationship is rarely seen in real world.

The above example describes that one student can enroll only for one course and a course will also
have only one Student. This is not what you will usually see in real-world relationships.
One to Many Relationship
The below example showcases this relationship, which means that 1 student can opt for many courses,
but a course can only have 1 student. Sounds weird! This is how it is.

Many to One Relationship

It reflects business rule that many entities can be associated with just one entity. For example, Student
enrolls for only one Course but a Course can have many Students.

St. Joseph’s College of Engineering Page 10

MI7402 /Data Management Department of MBA(Integrated)

Many to Many Relationship

The above diagram represents that one student can enroll for more than one courses. And a course can
have more than 1 student enrolled in it.

ER Diagram: Recursive Relationship

When an Entity is related with itself it is known as Recursive Relationship.

ER Diagram: Ternary Relationship

Relationship of degree three is called Ternary relationship.
A Ternary relationship involves three entities. In such relationships we always consider two entites
together and then look upon the third.

For example, in the diagram above, we have three related entities, Company, Product and Sector. To
understand the relationship better or to define rules around the model, we should relate two entities and
then derive the third one.
A Company produces many Products/ each product is produced by exactly one
company. A Company operates in only one Sector / each sector has many companies
operating in it.
Considering the above two rules or relationships, we see that although the complete relationship
involves three entities, but we are looking at two entities at a time.
The Enhanced ER Model
As the complexity of data increased in the late 1980s, it became more and more difficult to use the

St. Joseph’s College of Engineering Page 11

MI7402 /Data Management Department of MBA(Integrated)

traditional ER Model for database modelling. Hence some improvements or enhancements were made to
the existing ER Model to make it able to handle the complex applications better.
Hence, as part of the Enhanced ER Model, along with other improvements, three new concepts were
added to the existing ER Model, they were:
1. Generalization
2. Specialization
3. Aggregration
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.
It's more like Superclass and Subclass system, but the only difference is the approach, which is bottom-
up. Hence, entities are combined to form a more generalised entity, in other words, sub-classes are
combined to form a super-class.

For example, Saving and Current account types entities can be generalised and an entity with
name Account can be created, which covers both.
Specialization
Specialization is opposite to Generalization. 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.

Aggregation

Aggregation is a process when relation between two entities is treated as a single entity.

St. Joseph’s College of Engineering Page 12

MI7402 /Data Management Department of MBA(Integrated)

In the diagram above, the relationship between Center and Course together, is acting as an Entity,
which is in relationship with another entity Visitor. Now in real world, if a Visitor or a Student visits a
Coaching Center, he/she will never enquire about the center only or just about the course, rather
he/she will ask enquire about both.

ER Model to Relational Model

ER Model can be represented using ER Diagrams which is a great way of designing and representing the
database design in more of a flow chart form.
It is very convenient to design the database using the ER Model by creating an ER diagram and later on
converting it into relational model to design your tables.
Not all the ER Model constraints and components can be directly transformed into relational model,
but an approximate schema can be derived.
Few examples of ER diagrams and convert it into relational model schema, hence creating tables
in RDBMS.

Entity becomes Table

Entity in ER Model is changed into tables, or we can say for every Entity in ER model, a table is created
in Relational Model.
And the attributes of the Entity gets converted to columns of the table.
And the primary key specified for the entity in the ER model, will become the primary key for the table in
relational model.

For example, for the below ER Diagram in ER Model,

A table with name Student will be created in relational model, which will have 4
columns, id, name, age, address and id will be the primary key for this table.
Table:Student

St. Joseph’s College of Engineering Page 13

MI7402 /Data Management Department of MBA(Integrated)

id name age address

Relationship becomes a Relationship Table

In ER diagram, we use diamond/rhombus to represent a relationship between two entities. In Relational

model we create a relationship table for ER Model relationships too.
In the ER diagram below, we have two entities Teacher and Student with a relationship between them.

As discussd above, entity gets mapped to table, hence we will create table for Teacher and a
table for Student with all the attributes converted into columns.
Now, an additional table will be created for the relationship, for example StudentTeacher or give it any
name you like. This table will hold the primary key for both Student and Teacher, in a tuple to describe
the relationship, which teacher teaches which student.
If there are additional attributes related to this relationship, then they become the columns for this
table, like subject name.
Also proper foreign key constraints must be set for all the tables.
Functional Dependency

The functional dependency is a relationship that exists between two attributes. It typically exists
between the primary key and non-key attribute within a table.
X→Y
The left side of FD is known as a determinant, the right side of the production is known as a dependent.

For example:
Assume we have an employee table with attributes: Emp_Id, Emp_Name, Emp_Address.
Here Emp_Id attribute can uniquely identify the Emp_Name attribute of employee table because if we
know the Emp_Id, we can tell that employee name associated with it.
Functional dependency can be written as:
Emp_Id → Emp_Name

Types of Functional dependency

St. Joseph’s College of Engineering Page 14

MI7402 /Data Management Department of MBA(Integrated)

Trivial functional dependency

o A → B has trivial functional dependency if B is a subset of A.
o The following dependencies are also trivial like: A → A, B → B

Example:
1. Consider a table with two columns Employee_Id and Employee_Name.
{Employee_id, Employee_Name} → Employee_Id is a trivial functional dependency as
2. Employee_Id is a subset of {Employee_Id, Employee_Name}.
3. Also, Employee_Id → Employee_Id and Employee_Name → Employee_Name are trivial depen
dencies too.

Non-trivial functional dependency

4. A → B has a non-trivial functional dependency if B is not a subset of A.
5. When A intersection B is NULL, then A → B is called as complete non-trivial.

Example:
ID → Name,
Name → DOB

UNIT II RELATIONAL DATABASE MANAGEMENT SYSTEMS (RDBMS):

Relational Model -Relations, Tupples, domains and type of keys, Boyce–Codd Normal Form,
normalization of databases– The first and second normal form of databases.

EVOLUTION OF RDBMS

Before the acceptance of Codd‘s Relational Model, database management systems was just an ad hoc
collection of data designed to solve a particular type of problem, later extended to solve more basic
purposes. This led to complex systems, which were difficult to understand, install, maintain and use.
These database systems were plagued with the following problems:

• They required large budgets and staffs of people with special skills that were in short supply.

• Database administrators‘ staff and application developers required prior preparation to access
these database systems.

St. Joseph’s College of Engineering Page 15