Database Management System

Prerequisites :

Fundamentals of Programming
Course Objectives:

To understand the fundamental concepts of database management


To provide a strong formal foundation in database concepts, technology


To give systematic database design approaches covering conceptual design, logical design
and an overview of physical design


To learn basic issues of transaction management and concurrency control


To learn and understand various Database Architectures and Applications


To learn a powerful, flexible and scalable general-purpose database to handle big data
Course Outcomes:


Demonstrate programming fundamental using python language.


Apply object-oriented programming concept using python language.


Make use of data structure and algorithm using python language.


Build MYSQL database connectivity with python for Database navigation operations
such as select, create, insert, delete, update.


Demonstrate programming fundamental using python language.
Units:


Unit I: Introduction to DBMS


Unit II: Relational Algebra, SQL and PL/SQL


Unit III: Relational Database Design


Unit IV: Database Transactions and Query Processing


Unit V: Database architecture


Unit VI: Advances in Databases and Big Data
Units:


Unit I: Introduction to DBMS


Introduction to Database Management Systems, Advantages of a DBMS
overfile-processing Systems, Database-System purpose and applications,
Levels of Database Systems, Database Languages, Data Models, Components
of a DBMS and overall structure of a DBMS, Database Design and ER Model:
Entity, Attributes, Relationships, Constraints, types of Keys, Design Process,
Entity Relationship Model, ER Diagram, Design Issues, Extended E-R Features,
converting E-R & EER diagram into tables.
Introduction to DBMS:


What is DBMS


Why DBMS


Advantages of a DBMS overfile-processing Systems
What is DBMS?

DBMS consist of two parts

Database and
Management System

Data is the basic building block of any DBMS

Data

- Data can be anything like name,age,address,contact no, etc.

- Facts, figures, statistics etc. having no particular meaning

For Example : 5,ABC,Pune,etc.
Record

- Collection of related data items

For Example : 5,ABC,Pune,19,etc.
- No meaning to the data items mentioned above
- It is raw and unorganized, that need to processed to make it meaningful as mentioned
below
Roll Number Name City Age

5 ABC Pune 19
Table

- Collection of related records

Roll Number Name City Age

5 ABC Pune 20

9 XYZ Pune 18
3 LMN Pune 19
Database:

- Database can be access at different levels with their own customized front-end
application.

e.g. Faculties of different departments will have different levels of access

Database:

- Data is organized strictly in row and column format in In a database.

- The rows are called Tuple or Record and the columns are called Domain or Attribute.

Note : The data items within one row may belong to different data types.
All the data items within a single columns are of the same data type.
Database-management system (DBMS):

- DBMS Can be defined as

->collection of interrelated data
->Set of programs to access those data

- interrelated data have some implicit meaning hence it is called as database.

- The collection of data is called database.

Importance of management system

- The database can be maintain with some kind of rules.

e.g.

- Common attributes to create relationship between two tables

- Easily insertion and deletion of records
- etc.

All these issues can be resolved by having some kind of rules to maintain the integrity of
the database.
Purpose Of DBMS

- DBMS helps users to create, insert, retrieve and update the information contained in the
database as per the requirement .

- Information can be of an individual, school, college,etc.

Applications Of DBMS

- Below mentioned are few applications of DBMS

1. College Management System

2. Banking
3. Online Shopping
etc
front-end and back-end

Front-end : what the user can see

Back-end : supports front -end

Front -end and Back-end both are necessary to develop application.

Front-end : user interface

Back-end : means the server, application and database.
front-end and back-end

Back-end : it can be called as server side, it provides data on request and it also contains
the database.
front-end and back-end

Back-end Tools :

Databases like Oracle, Microsoft SQL Server, IBM DB2, MySQL, NoSQL and PostgreSQL
useful to develop backend
Levels of Database Systems
Levels of Database Systems
Physical Level :

It is also called as Internal View.

The lowest level of abstraction describes how the data are actually stored.
It also describes complex low-level data structures in detail.

In this level data is stored in the external hard drives i.e. data is stored in files and folders.

Actual physical storage structure and access paths can be describe with the help of this level.
Levels of Database Systems
Conceptual Level :

It is also called as logical level.

It is the next-higher level of abstraction describes what data are stored in the database

It also describes what relationships exist among those data.

It describes how the database appears to the users conceptually and the relationships between various
data tables.

Defines all database entities, their attributes, and their relationships

Programmers and database administrators work at this level.

Levels of Database Systems
External Level :

It is also called as View level.

The highest level of abstraction describes only part of the entire database.

The external level only shows the relevant database content to the users in the form of views and hides
the rest of the data.

So different users can see the database as a different view as per their individual requirements.

External schema level is nearest to the user

Database Languages
Database languages can be used to read, store and update the data in the database.
Data Models

Data models define how data is connected to each other and how they are processed and stored inside the
system.

A Data model provides a way to describe the design of a database at the physical, logical, and view levels.

1)Relational Data Model

2)Entity-Relationship Data Model

3)Object-based Data Model

4)Semistructured Data Model

Relational Data Model
The data in this model is in the form of rows and columns within a table.

This model uses tables for representing data and in-between relationships.

Tables are also called relations.

This model uses a collection of tables to represent data as well as the relationships among those data.

Each table has multiple columns, and each column has a unique name.

Each table contains records of a particular type with fixed number of fields, or attributes.

The relational data model is the most widely used data model

Data is stored in tables called relations.

Database
Stud_id Name Div

5 ABC A
Stud Nam City Age
_id e 9 XYZ B
3 LMN A
5 ABC Pune 19
9 XYZ Pune 18 DIV Year
3 LMN Mumb 19
ai A FY
B SY
A SY
Database

Stud_id Name Div DIV Year

5 ABC A
A FY
9 XYZ B B SY
3 LMN A A SY
Database

Stud_id Name Div Year

5 ABC A FY

9 XYZ B SY
3 LMN A SY
Entity-Relationship Data Model
An ER model is the logical representation of data as objects and relationships among them.

These objects are known as entities, and relationship is an association among these entities.

A set of attributes describe the entities.

For example, Emp_name, Emp_id describes the 'Emp' entity.

Set of the same type of entities is known as an 'Entity set', and

Set of the same type of relationships is known as 'relationship set'.

The entity-relationship (E-R) data model uses a collection of basic objects, called entities, and relationships among
these objects.
Object-based Data Model
It is an extension of the ER model with notions of functions, encapsulation, and object identity, as well.

This model supports a rich type system that includes structured and collection types.

object-oriented data model that can be called as extending the E-R model as it contains encapsulation,methods
and identity of object.

Combination of object-oriented data model and relational data model called object- relational data model.
Semistructured Data Model
Semi-structured data model consist of specification of data where individual data items of the same type may have
different sets of attributes.

The Extensible Markup Language (XML) is used to represent semi-structured data.

Components of DBMS
Components of DBMS :


Software


Hardware


Procedures


Data


Users
Components of DBMS
Software :

The primary aspect of a DBMS is the software.

Software is the set of programs used to manage the database

Software in DBMS is used to store, retrieve, and update data in the database.

Software is capable of understanding the database language and which runs the commands into the
database.
Components of DBMS
Hardware :

Hardware components includes computer, I/O channels for data, hard disks.

Hardware components is mainly used for keeping and storing the data in the database.
Components of DBMS
Procedures :

This component consist of set of instructions and rules to apply a database management system

It is used to setup and install database management system

It also consists methods to setup to login logout of the DBMS, maintain backups, handle database
and also to guide users for operating the databases, etc.
Components of DBMS
Data :

One of the most important component of the database management system.

Important task of DBMS is to process the data, in DBMS data is stored, retrieved, and updated to and from the
databases.
Components of DBMS
Users :

Person who control and manage the databases

Below mentioned are the different types of users in DBMS

Application Programmers : writes the application programs to interact with databases

Database Administrators : who manages the overall DBMS

End-Users : who interact with the DBMS to perform various operations using the different types of
commands such as insert, update, retrieve, and delete, etc.
Database Design – (ER) Modeling
Below mentioned are the Database Design Techniques.

ER Modeling

Normalization
ER Modeling
ER model : Entity-Relationship model.

It is a technique for organizing the data independent of the actual database implementation

ER model is used to define the data elements and relationship for a specified system.

ER model very simple and easy to design view of data can develop.
Entity
Entity :

Entity has an independent existence

Entity is anything for which we collect data.

Rectangle is used to represent Entity

Student
Entity

Entity instance :

It is particular member of the entity type.

Example for entity instance : A particular student

Entity

Regular Entity :

Consist of its own key attribute

Example for entity instance : A particular student

Entity

Weak entity :

An entity which depends on other entity for its existence

Weak entity doesn't have any key attribute of its own

In ER diagram double rectangular box is use to represent weak entity

Attributes

Properties which defines entity called Attribute

For example : Id, Name, Age

Oval is use to represent attribute in ER diagram

Attribute
Key attribute

The attribute which uniquely identifies every entity instance is called key attribute

For Example : Student_id

oval with underlying lines is used to represnt key attribute

Student_id
Simple attribute

The attribute that can not be divided into simpler components is called Simple attribute.

For Example : Student_id,Student_Name

Composite attribute

Attribute that can be split into components called as composite attribute.

For Example : Address

Address

City State
Single valued Attributes

The Attribute that can take only a single value for each entity instance called as Single
valued attribute

For Example : Student_age

Multi-valued Attributes

The attribute that can take more than one value for each entity instance called as multi-
valued attribute.

For Example : Student_Contact_No

Stored Attribute

The attribute that need to be stored permanently is called stored attribute

For Example : Student_Name

Derived Attribute

When value of an attribute can be calculated based on other attributes is called derived
attribute.
For Example : Student_age can be calculated from DOB and Current date
Relationships

Interrelation between entities are called relationships

For Example : Teacher teaches to student

Here teaches is a relation between the entities Teacher and Student

Teacher Teaches Student

Relationships

Relation of Weak Entity with other entities can be represent with the help of below
mentioned symbol
Relationships

Relation of Weak Entity with other entities can be represent with the help of below
mentioned symbol
Cardinality of a Relationship

Mapping Cardinality : It is the number of entities to which another entity can be

associated via a relationship set which can have four possible connectivities as given
below :

One to one relationship

One to many relationship

Many to one relationship

Many to many relationship

Cardinality of a Relationship
One to One relationship

For Example : One faculty Cabin is allotted to One Faculty

It is a 1:1 relationship
cardinality is One-To-One (1:1)
Cardinality of a Relationship
One to One relationship in ER modeling
Cardinality of a Relationship
One to many relationship

For Example : One department can have many faculties, .

Hence it is a 1:N relationship
and cardinality is One-To-Many
Cardinality of a Relationship
One to Many relationship in ER modeling
Cardinality of a Relationship
Many to One relationship

For Example : One faculty works in only one college But one college can
have many faculties. Hence it is Many-to-One relationship.
Cardinality of a Relationship
Many to One relationship in ER modeling
Cardinality of a Relationship
Many to Many relationship

For Example : One student can select multiple subject and One subjects can be enrolled
by many students.
Hence it is a M:N relationship and cardinality is Many-to-Many
Cardinality of a Relationship
Many to Many relationship in ER modeling
Relationship Participation
Relationship Participation

1. Total

In this type of participation each entity instance will be connected through each entity instance through the
relationship.

2. Partial

Example for relationship participation.

Consider the relationship - Faculty is head of the department. All the faculties will not be the head of
the department. Only one faculty will be the head of the department.
Advantages and Disadvantages of ER Modeling

Advantages :

- Simple and easily to understand.

- It is use to describe design of database.

- Can be generalized and specialized as per the requirnments

Disadvantages :

- No industry standard for notation

- Limited relationship representation

Constraints in DBMS

- Constraints plays important role to ensure the correctness of data in the database

- Constraints are the restrictions on the database

Types of Relational Constraints :


Domain Constraint


Key Constraint


Entity Integrity Contstraint



Referential Intergrity Constraint
Domain Constraint

- It defines valid set of values or domain values for an attribute and the value of the attribute
must be available in the corresponding domain.

- For Example :
Stud_id Name City Age
In Name Column values “ 33 ”
Not allowed as it is integer value 5 ABC Pune 19
And name is string value.
9 XYZ Pune 18
3 33 Pune 19
Key Constraint
- To identify an entity uniquely from entity set of entity, keys are the entity set which are used.
- Multiple keys can be available with entity set.
- Primary key must be unique

Table 1 Table 2

Stud_id Name City Age Stud_id Name City Age

5
5 ABC
ABC Pune
Pune 19
19 5 ABC Pune 19
9 XYZ Pune 18 5 XYZ Pune 18
9 XYZ Pune 18
3 LMN Pune 19 3 LMN Pune 19
3 LMN Pune 19
In Table 2 relation does not satisfy the key constraint since all
Primary Key the values of primary key are not unique.
Stud_id : 5 for 2 records
Entity Integrity Constraint
- Entity integrity constraint says that primary key value can't be null.

- As the use of primary key value is to identify individual record, if the primary key contains NULL
value, then we can't identify those records.

Stud_id Name City Age Does not satisfy the entity integrity constraint
5 ABC Pune 19
9 XYZ Pune 18
LMN Pune 19

NULL
Referential Integrity Constraint

- A referential integrity constraint is in between two tables.

- It says that if a foreign key in Table 1 refers to the Primary Key of Table 2, then every value of the Foreign Key
in Table 1 must be null or be available in Table 2.
Referential Integrity Constraint

Stud_Id Name Div Dept_Id

Foreign Key
It does not satisfy Referential Integrity Constraint
1 ABC B 1

2 XYZ B 2

3 LMN B 5
Relationship

Primary Dept_Id Dept_Name College_Name

Key Primary Key
1 AI and DS VIIT

2 Computer VIIT

3 IT VIIT
Keys

- Key is used to uniquely identify any record present in the table.

- A DBMS Key can be an attribute or a set of attributes which helps to identify a record of a table.

For Example : Stud_id Name PRN Age

1 Prashant 121 19
2 Ritesh 122 18
3 Vivek 123 21
4 Prashant 124 25
5 Rahul 125 23

- Key can also used to establish and identify relationships between tables.
Keys

Types of key:

- Primary key

- Candidate key

- Super Key

- Foreign key
Keys : Primary Key
Primary key :

- Primary key is used to identify one instance of an entity uniquely.

- Table can have multiple keys, the key which is most suitable from those lists become a primary key.

- Selection of primary key in table is completly based on requirement and developers.

For example : Stud_id Name PRN Age

Primary Key
1 Prashant 121 19
2 Ritesh 122 18
3 Vivek 123 21
4 Prashant 124 25
5 Rahul 125 23
Keys : Candidate Key
Candidate key :

- It is an attribute or set of an attribute that can uniquely identify a tuple.

Candidate Key

- The candidate key can having only one attribute or composite as well.

For example :

Primary Key Stud_id Name PRN Aadhar Age

Number
1 Prashant 121 4****9 19
2 Ritesh 122 4****3 18
3 Vivek 123 4****5 21
4 Prashant 124 4****6 25
5 Rahul 125 4****7 23
Keys : Super Key
Super Key :

- It is a set of an attribute that can uniquely identify a tuple.

- In Super key there is no restriction on number of attributes, it may consist of any number of attributes

For example :
Super Key
Stud_id Name PRN Aadhar Age
Number
1 Prashant 121 4****9 19
2 Ritesh 122 4****3 18
3 Vivek 123 4****5 21
4 Prashant 124 4****6 25
5 Rahul 125 4****7 23
Keys : Foreign Key
Foreign key :

- To point to the primary key of another table foreign key columns are used.

- Foreign Key is used to establish relationships between two tables

Keys : Foreign Key

Stud_Id Name Div Dept_Id

Foreign Key
1 ABC B 1

2 XYZ B 2

3 LMN B 3
Relationship

Primary Dept_Id Dept_Name College_Name

Key Primary Key
1 AI and DS VIIT

2 Computer VIIT

3 IT VIIT
Keys : Alternate Key

Alternate key :
Those candidate keys which are not the Primary key are Alternate keys.
Design Process

Design process consist of Collection of multiple tasks including designing, development,

implementation, and maintenance of data management system.

It provide the technical drawing of how the data is going to be stored in a system.

Design process provides clear idea about the behavior of any application

It also the reduces the processing time of an application

Design Process

Life Cycle

- Requirement Analysis :
- Planning : based on basic requirements which defines boundaries and scope of database

- Database Designing :
- Splitting into various models
Physical Model
Logical Model : It is paper work, actual implementation of database not required

- Implementation
- behavior of the application based on the requirements
- Data conversion and loading
- Testing
Design Process

Database design must able to do below mentioned things :

- Eliminating redundant data

- Accuracy of Data

- Proper access to the data

Extended Entity-Relationship (EE-R)

- It is extensions to the original ER model.

- EE-R includes below mentioned concepts to create EE-R diagrams −

Subclasses and Super classes.

Specialization and Generalization.

Category or union type.

Aggregation.
Extended Entity-Relationship (EE-R)

- Subclasses and Super class

Super class is kind of entity which can be divided into further subtype.

Sub classes are the group of entities consist of some unique attributes.

Sub class inherits the properties and attributes from super class.

For example − consider Department super class.

Super class Department has sub groups: AInDS, Computer and IT.
Extended Entity-Relationship (EE-R)

Generalization

- By identifying common attributes from a set of entities, a generalized entity can be created.

- Generalization Is a bottom-up approach.

- Two or more entities can be generalized to a higher level entity, if these entities have some
attributes in common.
Extended Entity-Relationship (EE-R)
Generalization
Extended Entity-Relationship (EE-R)

Specialization

- In specialization process an entity is divided into sub-entities based on their features.

- It is a top - down approach.

- In this process higher level entity can be specialized into two or more entities.
Extended Entity-Relationship (EE-R)

Specialization
Extended Entity-Relationship (EE-R)

- Category or Union

- This type consist of relationship of one super or sub class with more than one super class.
Extended Entity-Relationship (EE-R)

Category or Union
Extended Entity-Relationship (EE-R)

Aggregation

- In the case of E-R diagram it is not possible to represent relationship between as entity and a
relationship.

- With the help of aggregation relationship with its corresponding entities is aggregated into a higher
level entity.
Extended Entity-Relationship (EE-R)

Aggregation
References

Abraham Silberschatz, Henry Korth, S.Sudarshan," Database System concepts".

References

https://www.conceptatech.com

https://www.javatpoint.com

https://www.tutorialspoint.com

https://www.guru99.com

https://www.gatevidyalay.com

https://www.geeksforgeeks.org