Sy Bsc (Computer Science)

Savitribai Phule Pune University

As per New Education Policy (NEP) 2020
Semester:- 3

Subject:-Database Management System I

Chapter 1:-Introduction to DBMS

Lecture 1
Syllabus
Why should learn DMBS
Learning DBMS (Database Management System) is important for several strong
reasons, especially if you're a Computer Science student or working in any IT-related
field. Here's why you should learn DBMS:

1. Data is Everywhere
Every app or website you use — like YouTube, Amazon, Instagram — stores, retrieves, and
manages large amounts of data using a DBMS.
•E.g., Instagram uses DBMS to store user profiles, posts, likes, comments, etc.

2. Core Subject in CS/IT

In academic courses like BSc CS, BCA, BTech CS, etc., DBMS is a core subject. It's also
essential for competitive exams like NIMCET, GATE, etc.
 Here's a list of job opportunities after
learning DBMS:

•Database Administrator (DBA)

•SQL Developer
•Backend Developer
•Data Analyst
•Data Engineer
•Business Intelligence (BI) Developer
•Software Developer / Full Stack Developer
•System Analyst
 Introduction to Data, Database and DBMS
What is Data?
Data is a collection of raw facts and figures, which by itself may not carry meaningful
information.

Examples:
Names: "Aditya", "Kiran"
Numbers: 25, 3.14
Dates: "13-07-2025"
2. What is a Database?
A Database is an organized collection of related data that can be easily accessed, managed, and updated.

Example:
A Student Database might include:
Roll No Name Course Marks
101 Aditya BCS 88

Key Features:
•Stores large amount of data
•Avoids repetition (reduces redundancy)
•Easy to search, insert, update, or delete data
3. What is DBMS (Database Management System)?

DBMS is software that allows users to create, manage, and interact with databases easily.

Examples of DBMS:
•MySQL
•Oracle
•MS SQL Server
•PostgreSQL
•MongoDB (NoSQL DBMS)
 In Short:
Data → Raw facts
Database → Organized storage of data
DBMS → Software to manage databases efficiently
 File System vs DBMS
1. File System:
A file system is a method used by operating systems to store and organize files on
storage devices like hard drives or SSDs. In this system, each application stores
data in its own separate file(s), and the user manages these files manually.

Characteristics of File System:

•Data is stored in flat files (like .txt, .csv).
•Each program must manually handle reading, writing, updating data.
•There is no standard mechanism to avoid data duplication or maintain consistency.
•Difficult to maintain relationships between different data files.
•Security features are minimal or absent.
2. DBMS (Database Management System):
A DBMS is a software application that interacts with users and databases to manage data
efficiently. It allows users to create, store, retrieve, update, and delete data using a
structured approach.

Characteristics of DBMS:
Data is stored in tables (rows and columns).
Uses SQL (Structured Query Language) to query and manipulate data.
Provides data consistency, integrity, and security.
Supports backup, recovery, and concurrent data access.
Designed to reduce data redundancy and ensure accuracy.
 Connect With Us On Social Media

Telegram Doubt Discussion

Instagram Account
Telegram Channel Group
- You Can ask Your Doubt
- Pdfs, Notes are Shared. - You can also ask your
Personally. You can Dm me.
- Important Updates. doubts here, other
- Educational reels, Important
students or me can solve
Updates Through Stories.
your doubts.
 Thank You

- Don’t Forget To Like Share and Subscribe.

 Sy Bsc (Computer Science)
Savitribai Phule Pune University
As per New Education Policy (NEP) 2020
Semester:- 3

Subject:-Database Management System I

Chapter 1:-Introduction to DBMS

Lecture 2
 Advantages of File System
1] Simple and Easy to Use
2] Less Expensive
3] Faster for Small Tasks
4] No Need for Specialized Staff
 Disadvantages of File System
1] Data Redundancy
2] Poor Security
3] No Backup and Recovery
4] No Concurrency Control
Database System Environment
•Users issue a query or program.
•Application layer sends it to the DBMS.

DBMS software:

First interprets and processes the query.

Then accesses the actual data.
•Data is fetched using definitions from the metadata.
•The result is returned to the user/program
Part Meaning
Users People who want data
Application/Query Way to ask for data
Brain of the system, processes and fetches
DBMS Software
data
Meta-Data Blueprint of the database
Stored Data Actual useful information
 Data Abstraction
Data Abstraction means hiding unnecessary details and showing only the
important Information to the user.

Ex:- Just like when you drive a car, you don’t need to know how the engine works
— you just use the steering, brakes, and accelerator.
Similarly, in a database, users don’t need to know how data is stored inside —
they just see what they need.
 Three Levels of Data Abstraction
1] Physical Level – (How data is stored)
This is the lowest level.
It tells how the data is actually saved in the computer (files, memory, hard disk).
Example: Data stored in binary format, storage blocks, etc.
Users don’t see this level.
2] Logical Level – (What data is stored)

•This level tells what data is stored in the database and how it is related.
•Example: A table named Student with fields like RollNo, Name, Class.
• Seen by database administrators and developers.
3] View Level – (How data looks to users)

•This is the highest level.

•It shows only part of the data that a user needs.
•Example: A student sees only their own marks, not the whole class data.
• Seen by end users (normal users).
 Why is Data Abstraction Important?
Makes the system easy to use.
Protects data by showing only necessary information.
Helps in maintenance (you can change internal things without affecting users).
 Connect With Us On Social Media

Telegram Doubt Discussion

Instagram Account
Telegram Channel Group
- You Can ask Your Doubt
- Pdfs, Notes are Shared. - You can also ask your
Personally. You can Dm me.
- Important Updates. doubts here, other
- Educational reels, Important
students or me can solve
Updates Through Stories.
your doubts.
 Thank You

- Don’t Forget To Like Share and Subscribe.

 Sy Bsc (Computer Science)
Savitribai Phule Pune University
As per New Education Policy (NEP) 2020
Semester:- 3

Subject:-Database Management System I

Chapter 1:-Introduction to DBMS

Lecture 3
 Levels of abstraction for DBMS
In a Database Management System (DBMS), there are typically three levels of
abstraction. These levels help separate the user’s view of the data from how it is
physically stored, improving data independence and system efficiency.
 1. Physical Level (Internal Level)

•What it is: Describes how data is actually stored in the database — the low-level details.
•Includes: Data structures (e.g., B-trees, indexing), file storage, compression techniques.
•Audience: System/database administrators and DBMS developers.
•Goal: Optimize storage and access performance.
 2. Logical Level (Conceptual Level)

•What it is: Describes what data is stored in the database and the relationships among the data.
•Includes: Tables, views, relationships, constraints, data types.
•Audience: Database designers and application developers.
•Goal: Provide a logical view of the entire database, independent of physical considerations.
 3. View Level (External Level)

•What it is: Describes parts of the database that a particular user or application is interested in.
•Includes: User-defined views, permissions, and tailored representations of data.
•Audience: End-users and applications.
•Goal: Simplify data interaction and enhance security by exposing only relevant data
 Data Independence
Data Independence means you can change how data is stored or organized without
changing the way users use the data.
It helps to keep different parts of the database system separate, so changes in one part don’t
break everything else.
Two Types:-
1] Logical Data Independence
You can change the structure of the database (like adding a new column to a table).
But users and programs don’t need to change — their view stays the same.
Example: You add a “phone number” to the customer table. But the user app that
shows customer names still works the same.

2. Physical Data Independence

You can change how data is saved on the computer (like using a new storage method or
index).
But the tables and queries stay the same — users don’t notice any difference.
Example: You change the way a table is stored to make it faster. But the queries still
work the same.
Why is it useful?
Makes the system easy to manage
Let’s developers update or optimize without breaking apps
Saves time and reduces bugs
 DBMS Architectures
1. 1-Tier Architecture (Single-Tier)
Everything — the database, the application, and the user interface — runs on the same
machine.
Example: MS Access, local SQL databases.
Advantages:
Very simple to set up and use
Good for learning and testing
No need for network connection
Disadvantages:
Not secure (all data is local)
Not suitable for multiple users
Hard to manage large data
2. 2-Tier Architecture (Client-Server)
What it is:
There are two parts:
Client – where the user runs the application
Server – where the database is stored and managed
Example: Small office apps that connect to a database server (like MySQL or Oracle).
Advantages:
Faster performance than 1-tier
More secure (data is on a central server)
Better for small teams or departments
Disadvantages:
Not ideal for many users at once
Business logic is mixed with the client – hard to update
Can slow down if too many clients connect
3. 3-Tier Architecture (Presentation - Application - Database)
What it is:
There are three layers:
Presentation Layer – User Interface (e.g., website or app)
Application Layer – Business logic (processes requests)
Database Layer – Where data is stored
Example: Online shopping websites, banking apps.
Advantages:
Highly secure (each layer can be protected separately)
Easy to scale (add more users easily)
Easy to maintain (update logic without changing UI or DB)
Supports many users at the same time
Disadvantages:
More complex to build and manage
Needs more hardware and network setup
Slightly slower for very small tasks due to multiple layers
 Connect With Us On Social Media

Telegram Doubt Discussion

Instagram Account
Telegram Channel Group
- You Can ask Your Doubt
- Pdfs, Notes are Shared. - You can also ask your
Personally. You can Dm me.
- Important Updates. doubts here, other
- Educational reels, Important
students or me can solve
Updates Through Stories.
your doubts.
 Thank You

- Don’t Forget To Like Share and Subscribe.

 Sy Bsc (Computer Science)
Savitribai Phule Pune University
As per New Education Policy (NEP) 2020
Semester:- 3

Subject:-Database Management System I

Chapter 1:-Introduction to DBMS

Lecture 4
 Database User
A database user is any person or software that uses a database system to store, manage,
or retrieve data.
Types of Database Users

1. End Users (or Casual Users)

Who they are: People who use applications to interact with the database.
What they do: They run queries, enter data through forms, or view reports.
Example: A bank teller checking a customer's account balance using
banking software.
2. Application Programmers
• Who they are: Developers who write programs that interact with the database.
• What they do: They write code to insert, update, delete, or fetch data using languages like SQL.
• Example: A developer building an online shopping app that stores product and customer data.

3. Database Administrators (DBAs)

• Who they are: Experts who manage the entire database system.
• What they do: They install the database, create user accounts, give permissions, back up data,
and ensure security.
• Example: A DBA managing all the hospital's patient data to ensure it's safe and organized.

4. System Analysts / Designers

• Who they are: People who design the structure of the database.
• What they do: They decide what tables, relationships, and rules the database should have.
• Example: A designer planning how to store student records for a school system.
 Advantages of DBMS
1] Data Redundancy is Reduced
•No duplicate data is stored.
•Example: A customer’s details are stored only once and used wherever needed.
2] Data Consistency
•Changes in one place update everywhere.
•Example: Changing an address in one table updates it across the system.
3] Better Data Security
•Only authorized users can access or change data.
•Example: Employees can’t access salary data unless given permission.
4] Data Sharing
•Many users can access the database at the same time.
•Example: Bank staff across different branches accessing the same account.
5] Backup and Recovery
•Automatic backups help restore data if lost.
•Example: If the system crashes, data can be recovered.
6] Data Integrity
•Ensures data is accurate and reliable.
•Example: Only numbers allowed in phone number fields.
7] Improved Decision Making
•Reports and queries provide insights from data.
•Example: A business checks sales trends to make decisions.
 Disadvantages of DBMS

1] Cost of Hardware and Software

•DBMS software and storage systems can be expensive.

2] Complexity
•Requires trained staff to set up and manage.

3] Performance Issues
•For very small systems, DBMS may be slower compared to simple file storage.

4] Risk of Failure
•If the DBMS crashes, the whole system may stop working.

5] Regular Maintenance Required

•Needs frequent updates, backups, and monitoring
 Applications of DBMS
Field / Industry How DBMS is Used
To manage customer accounts, transactions,
Banking
and loans.
For reservations, schedules, and ticket
Airlines
bookings.
Managing student records, grades,
Education
attendance, and course data.
Storing patient records, appointments, and
Healthcare
billing information.
Inventory management, customer data, and
Retail
sales reports.
Telecommunications Billing systems, user profiles, and call data.
National ID records, tax collection, and
Government
voting databases.
Product listings, user accounts, orders, and
E-commerce
reviews.
Storing user posts, profiles, and messaging
Social Media
data.
 Connect With Us On Social Media

Telegram Doubt Discussion

Instagram Account
Telegram Channel Group
- You Can ask Your Doubt
- Pdfs, Notes are Shared. - You can also ask your
Personally. You can Dm me.
- Important Updates. doubts here, other
- Educational reels, Important
students or me can solve
Updates Through Stories.
your doubts.
 Thank You

- Don’t Forget To Like Share and Subscribe.

 Sy Bsc (Computer Science)
Savitribai Phule Pune University
As per New Education Policy (NEP) 2020
Semester:- 3

Subject:-Database Management System I

Chapter 2:- Conceptual Design

Lecture 1
 Database Design Process
The Database Design Process is a step-by-step approach to create a structured, reliable, and
efficient database. It helps to ensure that data is stored properly, relationships are clear, and
retrieval is easy.
What is database design?
Database Design can be defined as a set of procedures or collection of tasks involving various
steps taken to implement a database . A good database design is important. It helps you get the
right information when you need it. Following are some critical points to keep in mind to
achieve a good database design:
1] Data consistency and integrity must be maintained.
2] Low Redundancy
3] Faster searching through indices
4] Security measures should be taken by enforcing various integrity constraints.
 What is a Data Model ?
A data model is a way to represent how data is stored, connected, accessed, and
manipulated in a database.
It helps in:
Designing the structure of a database.
Defining how data elements relate to each other.
Visualizing the logical flow of data.
The Hierarchical, Network, and Relational models are some types of data models; each
has special traits and applications. It is important to understand how these models are
different so you can choose the right one for your situation.
1] Hierarchical Data Model
The hierarchical data model is the oldest type of the data model. It was developed by IBM in 1968. It
organizes data in a tree-like structure. Hierarchical model consists of the following
It contains nodes which are connected by branches.
The topmost node is called the root node.
Each node has exactly one parent.
One parent may have many children.

In the above figure, Electronics is the root node which has two children i.e. Televisions and Portable
Electronics. These two has further children for which they act as parent. For example: Television has
children as Tube, LCD and Plasma, for these three Television act as parent. It follows one to many
relationship.
 Advantages of the Hierarchical Data Model
Because of its tree form, it is easy to grasp.
Retrieving data in a one-to-many connection is efficient.
Disadvantages of the Hierarchical Data Model
Inflexibility in reorganizing data.
redundant data storage.
2] Network Data Model
It is the advance version of the hierarchical data model. To organize data it uses directed
graphs instead of the tree-structure. In this child can have more than one parent. It uses the
concept of the two data structures i.e. Records and Sets.
 In the above figure, Project is the root node which has two children i.e. Project 1 and Project
2. Project 1 has 3 children and Project 2 has 2 children. Total there are 5 children i.e
Department A, Department B and Department C, they are network related children as we
said that this model can have more than one parent. So, for the Department B and
Department C have two parents i.e. Project 1 and Project 2.

Advantages of the Network Data Model

It is more flexible than the hierarchical model because it allows more than one parent.
It is good for handling complex data with many-to-many relationships
Disadvantages of the Network Data Model
Increased complexity in database design and management.
requires complex programming in order to manage and work with data.
3] Relational Data Model

The relational data model was developed by E.F. Codd in 1970.

A Relational Data Model is a way to organize data in the form of tables (also called
relations). Each table has rows and columns, like an Excel sheet.
Rows represent records (data entries).
Columns represent fields (attributes or properties).

Following are the properties of the relational data model :

Data is represented in the form of table only.
It deals only with the data not with the physical structure.
It provides information regarding metadata.
It provides a way to handle the queries with ease.
 Advantages of the Relational Data Model
High data independence and flexibility.
user-friendly querying features.
removes duplication by use of normalization.
Disadvantages of the Relational Data Model
It may be slower than hierarchical models for some simple data tasks.
It requires a good understanding of SQL and normalization (organizing data properly).
 Connect With Us On Social Media

Telegram Doubt Discussion

Instagram Account
Telegram Channel Group
- You Can ask Your Doubt
- Pdfs, Notes are Shared. - You can also ask your
Personally. You can Dm me.
- Important Updates. doubts here, other
- Educational reels, Important
students or me can solve
Updates Through Stories.
your doubts.
 Thank You

- Don’t Forget To Like Share and Subscribe.

 Sy Bsc (Computer Science)
Savitribai Phule Pune University
As per New Education Policy (NEP) 2020
Semester:- 3

Subject:-Database Management System I

Chapter 2:- Conceptual Design

Lecture 2
 Conceptual Design
Conceptual design is the first step in designing a database.
It’s like drawing a plan or map of the data we want to store, without worrying about
how it will be stored on a computer.
Imagine you're designing a database for a school. You first need to understand:
What information needs to be stored?
How is that information connected?
 ER Model
ER (Entity-Relationship) model is a visual way to represent the real-world data in terms
of:
Entities
→ Things we want to store information about
→ Example: Student, Teacher, Course
Attributes
→ Details or properties of each entity
→ Example: A Student has a Name, ID, Age
Relationships
→ How entities are connected to each other
→ Example: A Student enrolls in a Course
 Example: Simple ER Design for a School
Entities:
Student
Course
Teacher
Attributes:
Student → StudentID, Name, Age
Course → CourseID, Title
Teacher → TeacherID, Name
Relationships:
A Student ENROLLS IN a Course
A Teacher TEACHES a Course
 Why Use ER Model in DBMS?
1. Helps in Planning the Database
Before creating a database, we need to plan what data to store and how it’s all connected.
The ER model is like a blueprint — it gives a clear picture of:
•What the database will include (entities)
•What details are needed (attributes)
•How the data is related (relationships)

2. Easy to Understand
ER diagrams are visual — they use shapes and lines, not code.
This makes them easy to understand, even for non-technical people like managers or clients.

3. Organizes Data Clearly

It helps to:
•Avoid duplicate data
•Identify relationships between different data items
•Break complex data into simpler parts (entities and attributes)
 What is an Entity?

An entity is a real-world object or thing that you want to store information about in a database.
Examples:
•A Student
•A Car
•A Book
•A Teacher
Each entity has attributes (details or properties):
For example, a Student has:
→ Name, Roll Number, Age, Class
 What is an Entity Set?

An entity set is a collection of similar types of entities.

Example:
•If Ravi, Anita, and John are students,
then the group of all students is the "Student" entity set.
Think of entity = one object
Entity set = group of similar objects
 Types of Entities
1. Strong Entity
Exists independently
Has a primary key
Not dependent on any other entity
Example:
Student (has StudentID, Name, etc.)
Car (has CarID, Model, etc.)
Think of it like: A complete, independent object.
 2. Weak Entity
•Cannot exist alone
•Depends on a strong entity
•Does not have a primary key
•Identified using foreign key + partial key
Example:
•Dependent (a family member of Student)
•OrderItem (in an Order)
Think of it like: Needs support to exist.

3. Composite Entity (also called Associative Entity)

•Used to connect two other entities, especially in many-to-many relationships
•Often has its own attributes
Example:
•Enrollment (connects Student and Course)
•ProjectAssignment (connects Employee and Project)
Think of it like: A bridge entity with extra info.
4. Derived Entity
Not stored permanently in the database
Value is calculated from other entities
Example:
Age (derived from Date of Birth)
TotalPrice (derived from Quantity × Price)
Think of it like: A result, not raw data.
 Connect With Us On Social Media

Telegram Doubt Discussion

Instagram Account
Telegram Channel Group
- You Can ask Your Doubt
- Pdfs, Notes are Shared. - You can also ask your
Personally. You can Dm me.
- Important Updates. doubts here, other
- Educational reels, Important
students or me can solve
Updates Through Stories.
your doubts.
 Thank You

- Don’t Forget To Like Share and Subscribe.

 Sy Bsc (Computer Science)
Savitribai Phule Pune University
As per New Education Policy (NEP) 2020
Semester:- 3

Subject:-Database Management System I

Chapter 2:- Conceptual Design

Lecture 3
 Relationship Type

What is a Relationship?
A relationship shows how two entities are related to each other.
Example:
A Student ENROLLS in a Course
A Teacher TEACHES a Course
Here, ENROLLS and TEACHES are relationship types.
They show the connection between:
Student Course
Teacher Course
 Types of Relationships

1] One-to-One (1:1)
•One person One passport
•Example: Each Employee has one ID Card.

2] One-to-Many (1:N)
•One teacher Many students
•Example: One Teacher teaches many Students.

3] Many-to-Many (M:N)
•Many students Many courses
•Example: Many Students can enroll in many Courses.
 Degree of a Relationship Set
In database design (especially in ER diagrams), a relationship connects two or more
entities.
The degree of a relationship means:
How many different entity sets are involved in that relationship.

Types of Degrees:
1] Unary Relationship (Degree 1):
Only one entity is involved.
Example:
An employee supervises another employee.
(Entity: Employee — Relationship: supervises)
2] Binary Relationship (Degree 2):
•Two entities are involved.
•This is the most common type.
•Example:
A student enrolls in a course.
(Entities: Student and Course — Relationship: enrolls in)

3] Ternary Relationship (Degree 3):

•Three entities are involved.
•Example:
A doctor treats a patient using a medicine.
(Entities: Doctor, Patient, Medicine)

Degree = Number of entity sets in the relationship.

 Participation Constraint
In ER (Entity-Relationship) diagrams, a Participation Constraint tells us:
Does every entity have to be part of a relationship?

Two Types of Participation:

1] Total Participation (also called Mandatory):
Every entity in the entity set must participate in the relationship.
Shown with a double line in an ER diagram.
Example:
Every student must enroll in at least one course.
→ So, Student has total participation in "Enrolled In" relationship.
2] Partial Participation (also called Optional):
Some entities may participate, others may not.
Shown with a single line in an ER diagram.
Example:
Not every employee is a manager.
→ So, Employee has partial participation in "Manages" relationship.

•Total Participation = "Everyone must be included"

•Partial Participation = "Some may be left out"
 Connect With Us On Social Media

Telegram Doubt Discussion

Instagram Account
Telegram Channel Group
- You Can ask Your Doubt
- Pdfs, Notes are Shared. - You can also ask your
Personally. You can Dm me.
- Important Updates. doubts here, other
- Educational reels, Important
students or me can solve
Updates Through Stories.
your doubts.
 Thank You

- Don’t Forget To Like Share and Subscribe.

 Sy Bsc (Computer Science)
Savitribai Phule Pune University
As per New Education Policy (NEP) 2020
Semester:- 3

Subject:-Database Management System I

Chapter 2:- Conceptual Design

Lecture 4
 Generalization
Definition:
Generalization is the process of combining two or more lower-level entities into a
higher-level entity based on common features.
It is a “bottom-up” approach.
Example:
Entities: Car, Truck, Bike → all have common features like Vehicle ID, Registration No,
Model.
So we create a higher entity Vehicle.
 Specialization
Definition:
Specialization is the process of dividing a higher-level entity into two or more
lower-level entities based on distinguishing characteristics.
It is a “top-down” approach.
Example:
Higher entity: Employee
Specialization: Teacher, Clerk, Manager
(based on job role).
 Aggregation
Definition:
Aggregation is a concept where a relationship itself is treated as an entity.
It is used when a relationship has its own attributes.
Example:
Consider: Student works on Project guided by Professor.
Relationship: Works_On (between Student and Project).
If we want to associate Professor with this relationship, we treat Works_On as
an entity.
 Constraints
1️⃣ Domain Constraint

Values in a column must come from a valid range or data type.

Example:
Age of a student must be between 1 and 120.
Violation:
If someone enters Age = -5 or Age = "abc", it violates the domain constraint.
2️⃣ Entity Integrity Constraint

Every row must be unique and identifiable. A primary key cannot be NULL.
Example:
•Student table → Roll Number is Primary Key.
•Each student must have a different Roll Number.
Violation:
•If two students have the same Roll Number (101, 101) → violation.
•If Roll Number is left blank (NULL) → violation.

3️⃣ Referential Integrity Constraint

If one table refers to another, the value must exist in the parent table.
Example:
•Employee table has DeptID.
•DeptID must exist in the Department table.
Violation:
•If Employee has DeptID = 10 but there is no Department 10 → violation.
4️⃣ Key Constraint

Keys ensure that data is unique and correct.

Example:
In a Student table, Roll Number must be unique.
Violation:
Two students with same Roll Number (101) → violation.

5️⃣ Not Null Constraint

Some fields must always have a value (cannot be empty).

Example:
Student Name cannot be blank.
Violation:
If Name = NULL → violation.
6️⃣ Check Constraint

A condition must be true for values in a column.

Example:
Salary must be greater than 5000.
Violation:
If Salary = 3000 → violation.
Quick Recap (One Line Each)
Domain Constraint → Wrong data type/value.
Entity Integrity → Primary Key duplicate/NULL.
Referential Integrity → Refers to missing parent.
Key Constraint → Duplicate keys.
Not Null → Empty value where not allowed.
Check Constraint → Condition not satisfied.
 Connect With Us On Social Media

Telegram Doubt Discussion

Instagram Account
Telegram Channel Group
- You Can ask Your Doubt
- Pdfs, Notes are Shared. - You can also ask your
Personally. You can Dm me.
- Important Updates. doubts here, other
- Educational reels, Important
students or me can solve
Updates Through Stories.
your doubts.
 Thank You

- Don’t Forget To Like Share and Subscribe.

 Sy Bsc (Computer Science)
Savitribai Phule Pune University
As per New Education Policy (NEP) 2020
Semester:- 3

Subject:-Database Management System I

Chapter 2:- Conceptual Design

Lecture 5
 Relational Databases
The relational model represents how data is stored and managed in Relational
databases. Data is organized into tables, each known as a relation, consisting of rows
(tuples) and columns (attributes). Each row represents an entity or record, and each
column represents a particular attribute of that entity. A Relational databases consists
of a collection of tables each of which is assigned a unique name.
 Key Terms in the Relational Model

Term Simple Meaning

Relation A table with rows and columns
Tuple A row in the table (one record)
Attribute A column in the table (a data field)
The set of allowed values for a column (e.g.,
Domain
Age must be numbers)
Degree The number of columns (attributes) in a table
Cardinality The number of rows (tuples) in a table
A column (or group of columns) that uniquely
Primary Key
identifies each row
 Types of Keys in the Relational Model

Key Type Simple Definition

Uniquely identifies each row in a table.
Primary Key
Cannot be NULL.
A column (or set of columns) that can
Candidate Key uniquely identify rows. One becomes
primary.
Any combination of columns that uniquely
Super Key
identifies rows (can have extra columns).
A candidate key that was not chosen as the
Alternate Key
primary key.
A key that links to the primary key of another
Foreign Key
table (used for relationships).
ER Diagrams
 Connect With Us On Social Media

Telegram Doubt Discussion

Instagram Account
Telegram Channel Group
- You Can ask Your Doubt
- Pdfs, Notes are Shared. - You can also ask your
Personally. You can Dm me.
- Important Updates. doubts here, other
- Educational reels, Important
students or me can solve
Updates Through Stories.
your doubts.
 Thank You

- Don’t Forget To Like Share and Subscribe.

 Sy Bsc (Computer Science)
Savitribai Phule Pune University
As per New Education Policy (NEP) 2020
Semester:- 3

Subject:-Database Management System I

Chapter 3:- SQL

Complete Chapter
 Introduction to query languages
What is a Query Language?
•A query language is used to interact with databases.
•It allows users to retrieve, insert, update, and delete data.

Types of Query Languages:

[Link] – Specifies how to get the data (e.g., Relational Algebra).
[Link]-Procedural (Declarative) – Specifies what data is needed, not how (e.g., SQL).

Why SQL?
•Structured Query Language (SQL) is the standard language for relational databases.
•Supported by most database systems: MySQL, PostgreSQL, SQL Server, Oracle, SQLite, etc.
•Based on relational algebra and relational calculus.

Key SQL Capabilities:

•Data Querying: SELECT
•Data Manipulation: INSERT, UPDATE, DELETE
•Data Definition: CREATE, ALTER, DROP
•Data Control: GRANT, REVOKE
 Basic Structure of SQL Queries
SELECT column1, column2, ...
FROM table_name
WHERE condition
GROUP BY column
HAVING condition
ORDER BY column [ASC|DESC];
Clause Purpose
SELECT Specifies which columns to retrieve
FROM Indicates the table(s) to query
WHERE Filters rows based on conditions
Groups rows sharing a value (used with
GROUP BY
aggregates)
HAVING Filters groups (used with GROUP BY)
ORDER BY Sorts the result set
 Commands in SQL
1] DDL Commands – Data Definition Language
DDL commands are used to define the structure of the database, such as tables, schemas,
indexes, views, etc. These commands deal with the schema (i.e., the blueprint or design)
of the database, not the data inside.
DDL commands are auto-committed, meaning changes are permanent and cannot be
rolled back.
These commands affect the metadata of the database.

Command Purpose
Creates new database objects (e.g., table, view,
CREATE
index).
ALTER Modifies the structure of existing objects.
DROP Deletes database objects.
Removes all records from a table, but keeps
TRUNCATE
structure.
RENAME Changes the name of a table or column.
2] DML Commands – Data Manipulation Language
DML commands are used to manipulate data stored in existing tables. These commands
allow you to insert, update, delete, or retrieve data from the database.
DML commands are not auto-committed. Changes can be rolled back using TCL
commands.

Command Purpose
SELECT Retrieves data from tables.
INSERT Adds new records to a table.
UPDATE Modifies existing records.
DELETE Removes records from a table.
3] DCL Commands – Data Control Language

DCL commands are used to control access and permissions to data and database
objects. These commands help manage security and authorization in multi-user
database environments.
Usually used by database administrators.

Command Purpose
GRANT Gives permissions to users.
REVOKE Removes previously granted permissions.
4] TCL Commands – Transaction Control Language

TCL commands are used to manage transactions in a database. A transaction is a

group of one or more SQL statements executed as a single unit of work.
These commands help ensure data consistency and integrity.
You can commit or rollback changes made by DML commands.

Command Purpose
Saves all changes made in the current
COMMIT
transaction.
ROLLBACK Undoes changes since the last commit.
SAVEPOINT Sets a point to which you can roll back.
Sets properties for a transaction (e.g.,
SET TRANSACTION
isolation level).
 Final Summary

Command Type Description Common Commands

Defines the structure of DB CREATE, ALTER, DROP,
DDL
objects TRUNCATE, RENAME
SELECT, INSERT, UPDATE,
DML Manipulates data in tables
DELETE
DCL Controls user permissions GRANT, REVOKE
COMMIT, ROLLBACK,
TCL Manages transactions SAVEPOINT, SET
TRANSACTION
 SQL Commands(Important Commands)

SELECT - extracts data from a database

UPDATE - updates data in a database
DELETE - deletes data from a database
INSERT INTO - inserts new data into a database
CREATE DATABASE - creates a new database
ALTER DATABASE - modifies a database
CREATE TABLE - creates a new table
ALTER TABLE - modifies a table
DROP TABLE - deletes a table
CREATE INDEX - creates an index (search key)
DROP INDEX - deletes an index
Forms of a Basic SQL Query (Detailed Explanation)
1.1 SELECT Clause
•The SELECT clause tells SQL what columns you want to see in the result.
•You can also use expressions, functions, and calculations in this clause.
SELECT name, age FROM students;

1.2 FROM Clause

•Specifies the table(s) from which to retrieve the data.
•You can also join multiple tables here (advanced).
SELECT name FROM employees;

1.3 WHERE Clause

•Filters the rows returned by the query.
•Can include logical, comparison, or string expressions.

SELECT name FROM students

WHERE age > 18 AND gender = 'Female';
1.4 GROUP BY Clause
•Groups rows based on one or more columns — used with aggregate functions like SUM(), AVG(), etc.
SELECT department, COUNT(*) AS total_employees
FROM employees
GROUP BY department;

1.5 HAVING Clause

•Used to filter groups created by GROUP BY.
SELECT department, COUNT(*) AS total_employees
FROM employees
GROUP BY department
HAVING COUNT(*) > 1;

1.6 ORDER BY Clause

•Sorts the output.
•You can sort by one or more columns in ascending (ASC) or descending (DESC) order.
SELECT name, age FROM students
ORDER BY age DESC;
 SQL Operations
1 Set operations

Operator Description
Combines results of two queries and
UNION
removes duplicates
UNION ALL Combines results including duplicates
Returns only common records from both
INTERSECT
queries
Returns records from the first query that are
EXCEPT / MINUS
not in the second

Example:-
SELECT name FROM employees
UNION
SELECT name FROM managers;
 2 Aggregate Functions

Aggregate functions perform a calculation on a set of values and return a single value.
Commonly used with GROUP BY.

Function Description
COUNT() Counts rows
SUM() Adds values
AVG() Calculates average
MIN() Returns smallest value
MAX() Returns largest value

Example:
SELECT department, COUNT(*) AS total_employees
FROM employees
GROUP BY department;
 3 Date and Time Functions

Date and time functions are used to extract, manipulate, and format date/time values in
SQL.
Function Description Example
CURRENT_DATE /
Returns today's date '2025-09-07'
CURDATE()
CURRENT_TIME Returns current time '12:34:56'
NOW() Current date and time '2025-09-07 12:34:56'
DATEDIFF(date1, date2) Returns difference between dates
DATE_ADD() / DATE_SUB() Add/subtract dates
EXTRACT(YEAR FROM
EXTRACT() Extracts part of a date
date_column)

Example
SELECT name, DATEDIFF(CURDATE(), hire_date) AS
days_worked
FROM employees;
4. Simple Queries

Simple queries are basic SQL SELECT statements used to retrieve data from one or more
tables with or without filters.
Components of Simple Queries:
•SELECT
•FROM
•WHERE
•ORDER BY

Example 1: Retrieve all students

SELECT * FROM students;
Example 2: With condition
SELECT name, age FROM students WHERE age > 18 ORDER BY name ASC;
5 Nested Queries (Subqueries)

Theory:
A nested query (also called a subquery) is a query inside another query. Useful for
comparisons, filtering, or dynamic conditions.
•Can be used in SELECT, FROM, or WHERE.
•Can return scalar, row, or table values.
 Joins in DBMS
A JOIN is used to combine data from two or more tables based on a related column
between [Link], joins are written using the ON or USING keyword.
 1. INNER JOIN
Returns only the rows where there is a match in both tables.
Non-matching rows are ignored.

Syntax: SELECT column_name(s) FROM table1 INNER JOIN table2 ON

table1.column_name = table2.column_name;

Example: SELECT name, dept_name FROM student INNER JOIN department

ON student.dept_id = department.dept_id;
 2. Outer Join
Outer Join is used to join multiple database tables into a combined result-set that
includes all the records, even if they don't satisfy the join condition. NULL values
are displayed against these records where the join condition is not met.
 3. LEFT JOIN (LEFT OUTER JOIN)
Returns all rows from the left table, and matching rows from the right table. If no
match, it shows NULL for the right table columns.

Syntax: SELECT column_name(s) FROM table1 LEFT JOIN table2 ON table1.column_name =

table2.column_name;

Example: SELECT name, dept_name FROM student LEFT JOIN department ON student.dept_id =
department.dept_id;
 3. RIGHT JOIN (RIGHT OUTER JOIN)
Returns all rows from the right table, and matching rows from the left table.
If no match, it shows NULL for left table columns.

Syntax: SELECT column_name(s) FROM table1 RIGHT JOIN table2 ON table1.column_name =

table2.column_name;

Example: SELECT name, dept_name FROM student RIGHT JOIN department ON student.dept_id
= department.dept_id;
 [Link] JOIN (FULL OUTER JOIN)
Returns all rows from both tables.
Non-matching rows show NULL on the side where no match exists

Syntax: SELECT column_name(s) FROM table1 FULL JOIN table2 ON table1.column_name =

table2.column_name;

Example: SELECT name, dept_name FROM student FULL OUTER JOIN department ON
student.dept_id = department.dept_id;
 Views
Views in SQL are considered as a virtual table. A view also contains rows and
columns. o To create the view, we can select the fields from one or more tables
present in the database. o A view can either have specific rows based on certain
condition or all the rows of a table.

Creating a view A view is created by selecting fields from one or more tables
present in a database
Syntax: CREATE VIEW view_name AS SELECT column1, column2, ... FROM
table_name WHERE condition;
 Connect With Us On Social Media

Telegram Doubt Discussion

Instagram Account
Telegram Channel Group
- You Can ask Your Doubt
- Pdfs, Notes are Shared. - You can also ask your
Personally. You can Dm me.
- Important Updates. doubts here, other
- Educational reels, Important
students or me can solve
Updates Through Stories.
your doubts.
 Thank You

- Don’t Forget To Like Share and Subscribe.

 Sy Bsc (Computer Science)
Savitribai Phule Pune University
As per New Education Policy (NEP) 2020
Semester:- 3

Subject:-Database Management System I

Chapter 4:- Relational Database Design

Lecture 1
 Introduction
Relational Database Design is the process of organizing data into structured tables
(called relations) to ensure data accuracy, consistency, and efficiency. It is based on
the relational model proposed by E. F. Codd in 1970.

Objectives of Database Design

Minimize data redundancy (avoid duplicate data)
Ensure data integrity and consistency
Enable easy maintenance and scalability
Support efficient querying and updates
Represent real-world entities and relationships accurately
 Stages of Relational Database Design

•Requirement Analysis
→ Identify what data needs to be stored and how it will be used.
•Conceptual Design (ER Model)
→ Create an Entity–Relationship Diagram (ERD) to visualize entities and relationships.
•Logical Design
→ Convert ERD into Relational Schema (tables, attributes, keys).
•Normalization
→ Apply normal forms (1NF, 2NF, 3NF, BCNF) to remove redundancy and ensure integrity.
•Physical Design
→ Decide on storage structures, indexes, and performance tuning.
 Properties of RDB Design
Data Redundancy
The same information is saved in many places. This takes up extra space and can be
confusing.

Update Anomalies
When the same data is in many places, updating it in one spot but not in others can
cause mistakes.

Insertion Anomalies
Sometimes, you can’t add new information unless you also add other unrelated details.
Example: You can't add a new student unless you also enter a course name.
 Deletion Anomalies
Deleting one piece of data can remove other important data too.
Example: If you delete a course, it might also delete the last student enrolled in it.

Poor Data Integrity

When the same data is copied in many places, it's easy to make [Link] can lead
to wrong or incorrect information.
 Functional Dependency

Functional Dependency is a rule that shows the relationship between two sets of columns in a table.
Definition:
If you know the value of one column, and it always gives you the value of another column, then the second column is functionally
dependent on the first.
Example:
Student_ID Name

101 Raj

102 Aisha

Here, Name depends on Student_ID

So we write:
Student_ID → Name
(Read as: Student_ID determines Name)

This means:
•If you know the Student_ID, you can find the Name
•But just knowing the Name might not give you the unique Student_ID
 Key Points:
Helps in organizing data better.
Used in normalization to remove data problems.
Written as: A → B (A determines B)
Basic Concepts of Functional Dependency
Functional Dependency (FD):

Shows how one column's value depends on another.

Written as A → B, meaning "A determines B".

Determinant:

The left side of the dependency (A in A → B).

It uniquely identifies the value on the right.

Dependent:

The right side of the dependency (B in A → B).

Its value depends on the determinant.

Trivial Functional Dependency:

If B is part of A, then A → B is called trivial.

Example: {Student_ID, Name} → Name
Non-Trivial Functional Dependency:

If B is not part of A, then it's non-trivial.

Example: Student_ID → Name

Full Functional Dependency:

B depends on the whole of A, not just part.

Example: (Roll_No, Subject) → Marks
(Marks depends on both, not just one)

Partial Dependency:

B depends on part of A only.

Should be removed during normalization.

Transitive Dependency:

A → B and B → C implies A → C
These can lead to anomalies and are removed in higher normal forms.
 Connect With Us On Social Media

Telegram Doubt Discussion

Instagram Account
Telegram Channel Group
- You Can ask Your Doubt
- Pdfs, Notes are Shared. - You can also ask your
Personally. You can Dm me.
- Important Updates. doubts here, other
- Educational reels, Important
students or me can solve
Updates Through Stories.
your doubts.
 Thank You

- Don’t Forget To Like Share and Subscribe.