AD22302-DATABASE MANAGEMENT SYSTEMS

Prepared By
C. Jasphin,
AP/AI&DS,
SXCCE.
 COLLEGE VISION AND MISSION
Vision
To be an institution of eminence of optimal human development, excellent
engineering education and pioneering research towards developing a technically-
empowered humane society.
Mission
To transform the (rural) youth into top class professionals and technocrats willing to
serve local and global society with ethical integrity, by providing vibrant academic
experience of learning, research and innovation and stimulating opportunities to
develop personal maturity and professional skills, with inspiring and high caliber
faculty in a quality and serene infrastructural environment.
 DEPARTMENT VISION AND
MISSION
Vision

◦ To evolve as a centre of excellence with international reputation in the field of artificial

intelligence and data science to serve the changing needs of industry and society.

Mission

• To bring out career oriented graduates who are industry ready by adopting best practices of

teaching-learning process.

• To cultivate professional approach, strong ethical values and research spirit along with

leadership qualities.

• To contribute towards techno-economic and social development of the nation.

 PROGRAM OUTCOMES
PO Graduate Attribute
1 Engineering knowledge: Apply the knowledge of mathematics, science, engineering fundamentals,
and an engineering specialization to the solution of complex engineering problems.

2 Problem analysis: Identify, formulate, review research literature, and analyze complex engineering
problems reaching substantiated conclusions using first principles of mathematics, natural sciences,
and engineering sciences.
3 Design/development of solutions: Design solutions for complex engineering problems and design
system components or processes that meet the specified needs with appropriate consideration for the
public health and safety, and the cultural, societal, and environmental considerations.

4 Conduct investigations of complex problems: Use research-based knowledge and

research methods including design of experiments, analysis and interpretation of data, and synthesis
of the information to provide valid conclusions.
5 Modern tool usage: Create, select, and apply appropriate techniques,
resources, and modern engineering and IT tools including prediction and
modeling to complex engineering activities with an understanding of the
limitations.
6 The engineer and society: Apply reasoning informed by the contextual
knowledge to assess societal, health, safety, legal and cultural issues and the
consequent responsibilities relevant to the professional engineering practice.
7 Environment and sustainability: Understand the impact of the professional
engineering solutions in societal and environmental contexts, and demonstrate
the knowledge of, and need for sustainable development.
8 Ethics: Apply ethical principles and commit to professional ethics and
responsibilities and norms of the engineering practice.
9 Individual and team work: Function effectively as an individual, and as a
member or leader in diverse teams, and in multidisciplinary settings.
10 Communication: Communicate effectively on complex engineering activities
with the engineering community and with society at large, such as, being able to
comprehend and write effective reports and design documentation, make effective
presentations, and give and receive clear instructions.

11 Project management and finance: Demonstrate knowledge and understanding of

the engineering and management principles and apply these to one’s own work, as
a member and leader in a team, to manage projects and in multidisciplinary
environments.

12 Lifelong learning: Recognize the need for, and have the preparation and
ability to engage in independent and life-long learning in the broadest context of
technological change.
 PROGRAMME SPECIFIC OUTCOMES (PSOs)

Graduates should be able to:

 Evolve AI based efficient domain specific processes for effective decision
making in several domains such as business and engineering problems.
 Create, select and apply the theoretical knowledge of AI and Data
Analytics along with practical industrial tools and techniques to manage
and solve wicked societal problems
 Develop fundamental research to cater the critical needs of the society
through cutting edge technologies of AI.
 Syllabus
UNIT I CONCEPTUAL DATA MODELING
Data Models- Three Schema Architecture And Data Dependence - Database Environment – Database System
Development Lifecycle – Entity-relationship Model – Enhanced-ER Model – UML Class Diagrams
UNIT II RELATIONAL MODEL AND SQL
Relational Model Concepts - Integrity Constraints - SQL Data Manipulation – SQL Data Definition – Views -
SQL Programming
UNIT III RELATIONAL DATABASE DESIGN AND NORMALIZATION
ER And Eer-to-relational Mapping – Update Anomalies – Functional Dependencies – Inference Rules –
Minimal Cover – Properties Of Relational Decomposition – Normalization (Up To BCNF).
UNIT IV TRANSACTION MANAGEMENT
Transaction Concepts – Properties – Schedules – Serializability – Concurrency Control – Two-phase Locking
Techniques.
UNIT V OBJECT RELATIONAL AND NO-SQL DATABASES
Mapping EER To ODB Schema – Udts - Object Identifier – Reference Types – Row Types ––Collection Types –
Object Query Language; No-SQL: CAP Theorem – Document-based: Mongodb Data Model And CRUD
Operations; Column-based: Hbase Data Model And CRUD Operations.
 TEXT BOOKS

◦Ramez Elmasri, Shamkant B. Navathe,

“Fundamentals Of Database Systems”, Seventh
Edition, Pearson, 2017.
◦Thomas M. Connolly, Carolyn E. Begg, “Database
Systems – A Practical Approach To Design,
Implementation And Management”, Sixth Edition,
Global Edition, Pearson Education, 2015.
 REFERENCES
Toby Teorey, Sam Lightstone, Tom Nadeau, H. V. Jagadish,
1. “Database Modeling and Design - Logical Design”, Fifth Edition,
Morgan Kaufmann Publishers, 2011.
2. Carlos Coronel, Steven Morris, and Peter Rob, Database Systems:
Design, Implementation, and Management, Ninth Edition,
Cengage learning, 2012.
3. Abraham Silberschatz, Henry F Korth, S Sudharshan, “Database
System Concepts'', 6th Edition, Tata Mc Graw Hill, 2011.
4. Hector Garcia-Molina, Jeffrey D Ullman, Jennifer Widom,
"Database Systems: The Complete Book", 2nd edition, Pearson.
5. Raghu Ramakrishnan, “Database Management Systems'', 4 th
Edition, Tata Mc Graw Hill, 2010.
 COURSE OBJECTIVES

 To introduce database development life cycle and conceptual

modeling
 To learn SQL for data definition, manipulation and querying a
database
 To learn relational database design using conceptual mapping and
normalization.
 To learn transaction concepts and serializability of schedules
 To learn data model and querying in object-relational and No-SQL
databases
 COURSE OUTCOMES

CO1: Explain the database development life cycle and apply conceptual modeling.

CO2: Apply SQL queries to create, manipulate and query the database.

Apply the conceptual-to-relational mapping and normalization to design

CO3:
relational database.

CO4: Explain the transaction processing and concurrency control concepts.

CO5: Apply No SQL development tools on different types of No-SQL databases.

 CO - PO & PSO MAPPING

PO’s PSO’s
CO’s 1 2 3 4 5 6 7 8 9 10 11 12 1 2 3
1 2 2 3 3 - - - - 3 1 2 1 2 3 3
2 2 3 1 3 1 - - - 1 2 2 1 3 3 3
3 2 2 2 1 1 - - - 2 3 1 2 1 1 2
4 2 2 3 1 - - - - 1 2 1 2 2 2 2
5 3 1 3 2 1 - - - 1 3 1 1 2 1 1
AVG 2 2 2 2 1 - - - 2 2 1 1 2 2 2
 OBJECTIVE OF DBMS

◦ Enabling data access easy for the user.

◦ Both convenient and efficient for users to
retrieve and store information.
 UNIT I - CONCEPTUAL DATA MODELING
Data
It is a collection of information. The facts that can be
recorded and that have implicit meaning known as 'data’.
Example: Customer
1.cname. 2.cno. 3.ccity.
Information
Meaningful/Processed Data
Data vs information

Data Information
Raw facts Processed data
Un organized organized
Eg.marks Eg.average marks
In earlier days data was stored in the form of files. In
the file system adding, deleting and updating of data is
very difficult for the user.
Disadvantages of Traditional File Processing System
The traditional file system has following disadvantages:
1) data redundancy: data redundancy means repetition of the same data in
multiple places. It can lead to inconsistencies, increased storage requirements,
and impacting data integrity
2) data inconsistency: data inconsistency occurs when various copies of same data
may no longer get matched. For example changed address of an employee may
be reflected in one department and may not be available (or old address present)
for other department.
3) difficulty in accessing data: the conventional file system does not allow to
retrieve the desired data in efficient and convenient manner.
 Disadvantages of Traditional File Processing System- Cont…

4) data isolation: data stored in a file system is not easily integrated with
other data. Files can be stored in various formats (like text, binary), and on
disparate systems, which can make it challenging to combine data from
different sources.
5) integrity problems: It refers to the maintenance and assurance that the
data in a file are correct and consistent.
6) atomicity problems: Atomicity refers an operation (like updating a file)
either completely succeeds or completely fails, with no partial success. it is
difficult to ensure atomicity in file processing system.
7) concurrent access anomalies: For efficient execution, multiple users
update data simultaneously, in such a case data need to be synchronized. as
in traditional file systems, data is distributed over multiple files, one cannot
To overcome these drawbacks database system is proposed.

Database
◦ A database is a stored, systematic collection of data. it can contain any type of data,
including words, numbers, images, videos, and files.
◦ Database represents some aspect of the real world, sometimes called the miniworld or
the universe of discourse (UoD).
◦ DBMS is a computerized system that enables users to create and maintain a database.
◦ It organize data into tables.
◦ Database Management System (DBMS) is used to store, retrieve, and edit data.
◦ The collection of information stored in the database at a particular moment is called
an instance of the database. The overall design of the database is called the
database schema.
Applications of DBMS
S.No. File System DBMS
1 It is a traditional method to store data files in It is the modem way to create and manage
the computer. database.
2 File system help to store the data in the It help to easily create, insert, delete and update
computer's hard disk. the data from the database.
3 Difficult to manipulate and store the data Insertion, deletion and modification is done
because it is done manually. using SQL.
4 There is a data redundancy There is no data redundancy

5 There is no concurrent access of data There is concurrent access of data

6 There is no data recovery and backup of data There is a backup for DBMS system.
7 The data in file system is very inconsistent The data in DBMS system is consistent.
 I. DATA MODELS

• A data model is defined as a collection of concepts that can be used to describe

the structure of a database.

• Data models describe how a database’s logical structure is represented.

• Data models specify how data is linked to one another, as well as how it is
handled and stored within the system.
 Categories of Data Models
The data models are categorized according to the types of concepts
• high-level or conceptual data models provide concepts that describe the details how
the users perceive the data. It is also called entity based or object based data models
• low-level or physical data models provide concepts that describe the details of how
data is stored on the computer storage media, typically magnetic disks.
• representational (or implementation) data models provide concepts that may be
easily understood by end users.
 Types of Data Models

 Hierarchical Model

 Network Model

 Entity-Relationship Model

 Relational Model
Hierarchical Model
 Hierarchical Model was the first DBMS model. This model organizes the data in the hierarchical
tree structure.
 The hierarchy starts from the root which has root data and then it expands in the form of a tree by
adding child node to the parent node.
 Example:
Features of a Hierarchical Model
◦ One-to-many relationship: The data here is organized in a tree-like structure. Have only one
path from parent to any node.

◦ Example: In the above example, if we want to go to the node sneakers we only have one path
to reach there i.e through men's shoes node.

◦ Parent-Child Relationship: Each child node has a parent node but a parent node can have
more than one child node. Multiple parents are not allowed.

◦ Pointers: Pointers are used to link the parent node with the child node and are used to navigate
between the stored data.
Advantages of Hierarchical Model
1. It is very simple and fast to traverse through a tree-like structure.
2. Any change in the parent node is automatically reflected in the child node so,
the integrity of data is maintained.
Disadvantages of Hierarchical Model
1. Many-to-many relationships are not supported.
2. If a parent node is deleted then the child node is automatically deleted.
 Network Model
• This model is an extension of the hierarchical model.

• This model is the same as the hierarchical model, the only difference is can have more
than one parent.
• Data is organized into tree structure with many-to-many relationships

• Example: The node student has two parents i.e. CSE Department and Library.
 Features of a Network Model
Ability to Merge more Relationships:

◦This model has the ability to manage one-to-one relationships as well as many-
to-many relationships.

Many paths:

◦As there are more relationships there can be more than one path to the same
record.
Advantages of Network Model
◦Easy navigation
◦Easy and fast to retrieve data
Disadvantages of Network Model
◦Difficult to define the relationship between entities
◦Updation, deletion and insertion is very complex.
 ER Model

ER model stands for Entity-Relationship Model

Entity:
◦ An entity is referred to as a real-world object. It can be a name, place, object, class, etc.
◦ It is represented by a rectangle in an ER Diagram.
Example: teacher, department
Relationship:
◦ It represents dependency among entities
◦ Diamonds are used to show Relationships.
Example: Teacher works for a department.
Attributes:
◦ An attribute can be defined as the description of the entity.
◦ These are represented by Ellipse in an ER Diagram.
Example: salary, age, mobile_no etc.
Advantages of ER Model

1. Simple
2. Effective Communication Tool
3. Easy Conversion to any Model

Disadvantages of ER Model

1. Loss of information

2. Limited relationship

3. No representation for data manipulation

 Relational Model

• Relational Model is the most widely used model.

• The basic structure of a relational model is tables.

• The tables are also called as relations.

• In this model, the data is maintained in the form of a two-dimensional

table.
• All the information is stored in the form of row and columns.

• Example: Employee table.

Features of Relational Model
Tuples :

• Each row in the table is called tuple.

• A row contains all the information about any instance of the object.

Attribute or field:

• Attributes are the property which defines the table or relation.

• The values of the attribute should be from the same domain.

Advantages of Relational Model
◦ Simple
◦ Scalable
◦ design and maintenance is easy
Disadvantages of Relational Model
◦ Requires large data storage devices
◦ Leads to slower processing time
Advantages of Data Models
1.Represent the data accurately.
2. It helps to find the missing data.
3.Minimize the data Redundancy.
4.Data Model provides data security.
5.It is used for defining the relationship between tables, primary and foreign keys.
Disadvantages of Data Models
6.In the case of a vast database, sometimes it becomes difficult to understand the data
model.
7.Even smaller change made in structure require modification in the entire
application.
8.There is no set data manipulation language in DBMS.
4.Must have the knowledge of physical data to develop a data model
II-THREE SCHEMA ARCHITECTURE AND DATA DEPENDENCE

The goal of the three-schema architecture is to separate the user applications

from the physical database.

Three levels

 Internal level

 Conceptual level

 External level
Internal Level

 The physical representation of the database on the computer.

 This level describes how the data is stored in the database.

 It covers the data structures and file organizations used to store data on
storage devices.

 It uses a physical data model and describes the complete details of data
storage and access paths for the database.
Conceptual Level

 describes the structure of the whole database for a community of users.

 This level describes what data is stored in the database and the relationships
among the data

 The middle level in the three-level architecture is the conceptual level.

 The conceptual schema hides the details of physical storage structures and
concentrates on describing entities, data types, relationships, user operations,
and constraints.
External Level
• The external or view level includes a number of external schemas or user views.
• This level is the users’ view of the database. It describes that part of the database
that is relevant to each user.
• hides the rest of the database from that user group.
• The external view includes only those entities, attributes, and relationships in the
“real world” that the user is interested in.
 Data Independence

• Data independence is explained using the three-schema architecture.

• Data independence refers characteristic of being able to modify the schema at one
level of the database system without altering the schema at the next higher level.

Types

1.Logical data independence

2. Physical data independence

Logical data independence

• Logical data independence defined the capacity to change the conceptual schema without having to change
the external schema.

• Logical data independence is used to separate the external level from the conceptual view.

• Logical data independence occurs at the user interface level.

• Changes in the conceptual view of the data would not be affect the user view of the data.

Physical Data Independence

• Physical data independence defined the capacity to change the internal schema without having to change
the conceptual schema.

• Physical data independence is used to separate conceptual levels from the internal levels.

• Physical data independence occurs at the logical interface level.

• changes in the storage size of the database system server, will not affect the Conceptual structure of the
 III- DATABASE ENVIRONMENT
1.DBMS Component Modules
The figure is divided into two parts.

 The top part - various users of the database environment and their interfaces.

 The lower part - internal modules of the DBMS responsible for storage of data and processing of

transactions.

1. DBA staff, casual users who work with interactive interfaces to formulate queries

2. Application programmers who create programs using some host programming languages

3. Parametric users who do data entry work by supplying parameters to predefined transactions
DDL compiler processes schema definitions, specified in the DDL, and stores descriptions of the schemas

(meta-data) in the DBMS catalog

Catalog includes information such as the names and sizes of files, names and data types of data items, storage

details of each file, mapping information among schemas, and constraints

Query compiler – checks the correctness of the query syntax

Query Optimizer – rearrangement and reordering of operations

- elimination of redundancies

- use of efficient search algorithms during execution

Query Optimizer consults the system catalog for statistical and other physical

information about the stored data and generates executable code that performs the

necessary operations for the query and makes calls on the runtime processor.

Precompiler - extracts DML commands from an application program written in a host

programming language

These commands are sent to the DML compiler for compilation into object code for

database access.
Runtime database processor executes

(1) the privileged commands,

(2) the executable query plans,

(3) the canned transactions with runtime parameters.

• It works with the system catalog and may update it with statistics.

• It also works with the stored data manager to carry out the low-level input/output
(read/write) operations between the disk and main memory.

• Concurrency control and backup and recovery systems - integrated with the working of
runtime database processor for purposes of transaction management.
2.Database System Utilities

 Loading. A loading utility is used to load existing data files such as text files or sequential files into

the database

 Backup. A backup utility creates a backup copy of the database, usually by dumping the entire

database onto tape or other mass storage medium. The backup copy can be used to restore the

database in case of catastrophic disk failure.

 Database storage reorganization. This utility can be used to reorganize a set of database files into

different file organizations and create new access paths to improve performance.

 Performance monitoring monitors database usage and provides statistics to the DBA.
IV- DATABASE SYSTEM DEVELOPMENT LIFECYCLE

◦The stages of the database system development lifecycle are not strictly

sequential, but involve some amount of repetition of previous stages through

feedback loops.

◦Database system development lifecycle have several phases

Database planning

◦ It plan how the stages of the lifecycle can be realized most efficiently and effectively.
◦ First step in database planning is to clearly define the mission statement for the
database system.
◦ Once the mission statement is defined, the next activity involves identifying the
mission objectives.
◦ Database planning should also include the development of standards
 how data will be collected,
 how the format should be specified,
 what documentation will be needed, and
 how design and implementation should proceed.
System Definition
It describes the scope and boundaries of the database system
and the major user views
User Views
Defines the requirement in a database system from the perspective of a
particular job role (such as Manager or Supervisor) or enterprise application area
(such as marketing, personnel, or stock control). The requirements of a user view
may be distinct to that view or overlap with other views.
Requirements Collection and Analysis

 Requirements collection and analysis is a preliminary stage to

database design.

 This stage involves the collection and analysis of information.

There are many techniques for gathering the information, called

fact-finding techniques. This information is then analyzed to

identify the requirements (or features) to be included in the new

database system. These requirements are described in documents

collectively referred to as requirements specifications for the new

database system.
The information collected at this stage may be poorly structured and include some informal requests, which
must be converted into a more structured statement of requirements. This is achieved using requirements
specification techniques.
Database design
The process of creating a design that will support the mission statement and mission objectives for the
required database system
◦ Database design is made up of three main phases: conceptual, logical, and physical design.
Conceptual database design
◦ The data model is built using the information documented in the users requirements specification.
Logical database design
◦ The process of constructing a model of the data based on a specific data model
Physical database design
◦ The process of producing a description of the implementation of the database on secondary storage to
achieve efficient access to the data
DBMS selection

The main steps to selecting a DBMS are

◦ Define Terms of Reference of study

◦ Shortlist two or three products

◦ Evaluate products

◦ Recommend selection and produce report

Application Design

The two aspects of application design are transaction design and user interface design.

Prototyping

Building a working model of a database system.

There are two prototyping : requirements prototyping and evolutionary prototyping.

Implementation
The physical realization of the database and application designs.
Data conversion and loading
Transferring any existing data into the new database and converting any existing
applications to run on the new database.
This stage is required only when a new database system is replacing an old
system.
Testing

 The process of running the database system with the intent of

finding errors.
Operational Maintenance
The process of monitoring and maintaining the database system
Activities

◦ Monitoring the performance of the system. If the performance falls below an acceptable

level, tuning or reorganization of the database may be required.

◦ Maintaining and upgrading the database system (when required). New requirements are

incorporated into the database system through the preceding stages of the lifecycle.
 V-ENTITY-RELATIONSHIP MODEL
ER model stands for Entity-Relationship Model. It describes the structure of
database with the help of ER diagram
Entity:
◦ An entity is referred to as a real-world object. It can be a name, place, object, class,
etc.
◦ It is represented by a rectangle in an ER Diagram.
Example: teacher, department
Types
1.Strong-have attributes of their own
2.Weak-doesn’t Strong-have attributes of their own
Relationship:
It represents dependency among entities
Diamonds are used to show Relationships. The no.of entities that participate in the relationship is called as degree
Example: Teacher works for a department.
Types
1.Binary-relationship b/w 2 entities

2.Ternary-relationship b/w more than 2 entities

Attributes:
An attribute is the properties of the entities.
These are represented by Ellipse in an ER Diagram.
Example: salary, age, mobile_no etc.
Types
1.Simple and composite
2.Single and multivalued
3. Derived
Simple and composite
Attribute that cannot be divided into subparts is called as simple
Eg:Empid
Attribute that can be divided into subparts is called as composite
Eg:Name can be divided into first name & last name
Single and multivalued
Attributes with single value is called single
Eg:Regno
Attributes with multiple values /more than one values is called as
multivalued
Eg:Phoneno
Derived
Attributes derived from other attributes is called derived
Eg:age(from DOB)
Mapping coordinates or cardinality ratio
No. of entities to which another entity can be associated in the
relationship
Types
1.One to one(1:1)
2.One to many(1:n)
3.Many to many(n:n)
4.Many to one(n:1)
One to one
Entity in A is associated with atmost 1 entity in B
Entity in B is associated with atmost 1 entity in B
One to many
Entity in A is associated with any number of entities in B
Entity in B is associated with atmost 1 entity in A
Many to many
Entity in A is associated with any number of entities in B
Entity in B is associated with any number of entities in A
Many to one
Entity in A is associated with atmost 1 entity in B
Entity in B is associated with any number of entities in A
Participation constraints
It determines the maximum and minimum number of participation of
entities in the relationship
Types
1.Total participation
2.Partial participation
Total participation
It ensures that every entity must participate in the relationship
Partial participation
It ensures that some of the entity may participate in the relationship
VI-Enhanced-ER model
 Refined
ER
Design
for the
COMPAN
Y
Database
 VII-UML CLASS DIAGRAMS (ER DIAGRAM)
In UML, class diagrams are one of six types of structural diagram. Class diagrams are fundamental to the
object modeling process and model the static structure of a system. Depending on the complexity of a
system, you can use a single class diagram to model an entire system, or you can use several class
diagrams to model the components of a system.
◦ Class diagrams are the blueprints of your system or subsystem. The diagram shows the names and
attributes of the classes, connections between the classes and sometimes also the methods of the classes.
◦ The following topics describe model elements in class diagrams:
Classes
In UML, a class represents an object or a set of objects that share a common structure and behavior.
Classes, or instances of classes, are common model elements in UML diagrams.
Objects
In UML models, objects are model elements that represent instances of a class or of classes. You can add
objects to your model to represent concrete and prototypical instances. A concrete instance represents an
actual person or thing in the real world. For example, a concrete instance of a Customer class represents an
actual customer. A prototypical instance of a Customer class contains data that represents a typical
customer.
packages
• Packages group related model elements of all types, including other packages.
• Signals
In UML models, signals are model elements that are independent of the
classifiers that handle them. Signals specify one-way, asynchronous
communications between active objects.
• Enumerations
In UML models, enumerations are model elements in class diagrams that
represent user-defined data types. Enumerations contain sets of named
identifiers that represent the values of the enumeration. These values are called
enumeration literals.
• Data types
In UML diagrams, data types are model elements that define data values. You
typically use data types to represent primitive types, such as integer or string
types, and enumerations, such as user-defined data types.
•In UML models, artifacts are model elements that represent the physical entities in a software
system. Artifacts represent physical implementation units, such as executable files, libraries, software
components, documents, and databases.
•Relationships in class diagrams
In UML, a relationship is a connection between model elements. A UML relationship is a type of
model element that adds semantics to a model by defining the structure and behavior between model
elements.
•Qualifiers on association ends
In UML, qualifiers are properties of binary associations and are an optional part of association ends. A
qualifier holds a list of association attributes, each with a name and a type. Association attributes
model the keys that are used to index a subset of relationship instances.
UML Class Diagrams (ER Diagram)