ADBMS

1. Functional dependency
What is Functional Dependency?
Functional Dependency (FD) is a constraint between two sets of
attributes in a relation (table). It expresses a relationship where
one attribute uniquely determines another attribute.
Notation:
X→YX \right arrow YX→Y
This means that if two rows have the same value for X, then they
must also have the same value for Y.
• X → Determinant (Left side)
• Y → Dependent (Right side)

If X determines Y, then Y is functionally dependent on X.

Example of Functional Dependency

Consider a Student Table:
Student_ID Name Department

101 Alice CSE

102 Bob IT

103 Charlie CSE

Here,
Student_ID → Name
Because each Student_ID uniquely determines a student’s Name.
However, Name → Student_ID is NOT true because multiple
students can have the same name.

Types of Functional Dependencies

1. Trivial Functional Dependency
o A dependency is trivial if Y is a subset of X.
o Example: {Student_ID, Name} → Name (Name is already

part of {Student_ID, Name}).

2. Non-Trivial Functional Dependency
o If Y is not a subset of X, the dependency is non-trivial.
o Example: Student_ID → Name (Name is not part of

Student_ID).
3. Partial Functional Dependency
o When a non-key attribute is dependent on a part of the
primary key (in composite keys).
o Example: {Student_ID, Course_ID} → Student_Name
(Student_Name depends only on Student_ID, not
Course_ID).
4. Full Functional Dependency
o A non-key attribute is dependent on the entire composite
key.
o Example: {Student_ID, Course_ID} → Grade (Grade

depends on both Student_ID and Course_ID).

5. Transitive Functional Dependency
o If A → B and B → C, then A → C.
o Example: Student_ID → Department and Department →

HOD, so Student_ID → HOD.

6. Multivalued Functional Dependency
o If one attribute determines multiple independent values
of another attribute.
o Example: Student_ID →→ Phone_Number (A student can
have multiple phone numbers).

Importance of Functional Dependency in ADBMS

✔ Helps in Normalization (Reduces redundancy & anomalies).
✔ Used in designing efficient databases.
✔ Determines candidate keys and primary keys.

2. Third nf
What is Third Normal Form (3NF)?
Third Normal Form (3NF) is a database normalization rule that
helps reduce redundancy and improve data integrity.
A relation (table) is in 3NF if:
1. It is in Second Normal Form (2NF) (i.e., no partial
dependencies).
2. There is no transitive dependency (i.e., non-key attributes
should not depend on other non-key attributes).
Transitive Dependency:
If A → B and B → C, then A → C (this is a transitive
dependency).
To be in 3NF, we must remove transitive dependencies.

Example of 3NF
Before 3NF (Not in 3NF)
Student_ID Student_Name Department HOD

101 Alice CSE Dr. Smith

102 Bob IT Dr. John

103 Charlie CSE Dr. Smith

Problem (Transitive Dependency):

• Student_ID → Department (Valid Dependency)
• Department → HOD (Another dependency)
• Student_ID → HOD (Transitive Dependency)
Solution: Convert to 3NF
Decompose into two tables:
Student Table (Without Transitive Dependency)
Student_ID Student_Name Department
101 Alice CSE

102 Bob IT

103 Charlie CSE

Department Table (Separate HOD Information)
Department HOD
CSE Dr. Smith

IT Dr. John

Now, there is no transitive dependency!

Student_ID determines Department, and Department
determines HOD separately.

Advantages of 3NF
Reduces data redundancy
Avoids update anomalies
Improves database efficiency

How to Check if a Table is in 3NF?

✔ Identify Functional Dependencies
✔ Check for transitive dependencies
✔ If found, decompose the table

3. Boyye codd normal form

Boyce-Codd Normal Form (BCNF) in ADBMS
Definition
Boyce-Codd Normal Form (BCNF) is a stronger version of Third
Normal Form (3NF) that removes anomalies caused by
functional dependencies.
A table is in BCNF if:
1. It is in Third Normal Form (3NF).
2. For every functional dependency (X → Y), X must be a
superkey.
o This means no non-trivial dependency should exist
where a non-superkey determines another attribute.
BCNF fixes some issues that still exist in 3NF when there
are overlapping candidate keys.

Example: Why BCNF is Needed?

Consider a Student_Course Table (Not in BCNF):
Student_ID Course Instructor
101 DBMS Dr. Smith

102 OS Dr. John

103 DBMS Dr. Smith

104 OS Dr. John

Functional Dependencies:
1. Student_ID, Course → Instructor
2. Instructor → Course (Problem: Instructor is not a
superkey, so this violates BCNF)

Step 1: Convert to BCNF

To remove the Instructor → Course dependency, we split the
table into two tables:
Student_Course Table (Now in BCNF)
Student_ID Course
101 DBMS

102 OS

103 DBMS

104 OS
Instructor Table (Now in BCNF)
Course Instructor
DBMS Dr. Smith
Course Instructor

OS Dr. John

Now, all dependencies are on a superkey, so the table is in

BCNF.

Key Differences: 3NF vs. BCNF

Boyce-Codd
Third Normal Normal Form
Feature
Form (3NF) (BCNF)
Removes Transitive Yes
Yes
Dependencies
Handles Functional More Strict
Yes
Dependencies
Every Determinant Yes
Not Always
is a Superkey
Normal Complex key
Use Case dependencies
scenarios

Advantages of BCNF
✔ Eliminates redundancy more effectively than 3NF
✔ Prevents update anomalies completely
✔ Ensures a more robust database structure

How to Check if a Table is in BCNF?

Step 1: Identify Functional Dependencies
Step 2: Check if the left side of each dependency is a
superkey
Step 3: If not, decompose the table into BCNF

4. Referential integrity
Referential Integrity ensures that relationships between tables
remain consistent in a database.

Imagine a college database with two tables:

1 Department Table (Stores department details)
2 Student Table (Stores student details)
Every student belongs to a department. The Dept_ID in the
Student Table must match a valid Dept_ID in the Department
Table.
Table 1: Department (Main Table)
Dept_ID (Primary Key) Dept_Name
1 CSE

2 IT

3 ECE
Table 2: Student (Dependent Table)
Student_ID (Primary Dept_ID (Foreign
Name Key)
Key)
101 Alice 1

102 Bob 2

103 Charlie 3

Dept_ID in the Student Table is a Foreign Key that

references Dept_ID in the Department Table.

Why is Referential Integrity Important?

✔ Prevents Invalid Data
• If we try to add a student with Dept_ID = 5, the database
rejects it because Dept_ID = 5 does not exist in the
Department Table.
✔ Prevents Orphan Records
• If we delete Dept_ID = 1, what happens to students in that
department?
• Referential Integrity ensures proper handling of such cases.

How Referential Integrity Works?

When deleting/updating a record in the Department Table, we can
set different rules for handling related records in the Student
Table:
Rule What Happens When a Dept is Deleted?

CASCADE Delete all students in that department.

SET NULL Set Dept_ID of affected students to NULL.

Prevent deletion if students exist in that
RESTRICT department.

Example in SQL
CREATE TABLE Department (
Dept_ID INT PRIMARY KEY,
Dept_Name VARCHAR(50)
);

CREATE TABLE Student (

Student_ID INT PRIMARY KEY,
Name VARCHAR(50),
Dept_ID INT,
FOREIGN KEY (Dept_ID) REFERENCES
Department(Dept_ID) ON DELETE CASCADE
);

5. Object oriented database

An Object-Oriented Database (OODB) stores data as objects,
similar to Object-Oriented Programming (OOP). Unlike
Relational Databases (RDBMS), which use tables, OODBs store
complex data, including methods and relationships.

Key Features

✔ Objects & Classes – Data is stored as objects (like in Java,

C++, Python).
✔ Encapsulation – Objects contain both data and methods.
✔ Inheritance – Objects can inherit properties and behavior.
✔ Polymorphism – Different objects share a common interface.
✔ Persistence – Objects are stored permanently.
✔ Relationships – Objects link directly (no foreign keys needed).

Example: Library Management System

Instead of tables, OODBs store objects:

class Book:
def __init__(self, book_id, title, author):
self.book_id = book_id
self.title = title
self.author = author

def borrow(self):
print(f"{self.title} is borrowed.")

# Creating an object
book1 = Book(101, "OOP Principles", "E. Balagurusamy")
book1.borrow() # Output: OOP Principles is borrowed.

Pros & Cons

Advantages
✔ Models real-world objects easily (e.g., multimedia, CAD).
✔ Faster access to complex data (no joins needed).
✔ Reusability of objects & classes.

Disadvantages
✘ Not as widely supported as RDBMS.
✘ More complex to design & manage.
✘ No standard query language (uses OQL instead of SQL).

Examples of Object-Oriented Databases

1 ObjectDB – Java-based OODB.

2 db4o – Lightweight OODB for Java & .NET.
3 Versant – High-performance OODBMS.
4 ZODB – Python-based OODB.

6. Object oriented database with object relational database

Object-Oriented Database (OODB)
✔ Stores data as objects (like in OOP).
✔ Directly saves objects with attributes & methods.
✔ Best for CAD, AI, multimedia applications.
✔ Example: ObjectDB, db4o, ZODB.
Example (OODB - Python)
class Book:
def __init__(self, book_id, title, author):
self.book_id = book_id
self.title = title
self.author = author

def display(self):
print(f"Book: {self.title} by {self.author}")

book1 = Book(101, "Database Systems", "Navathe")

book1.display() # Output: Book: Database Systems by Navathe

OODB stores book1 as an object with its methods.

2. Object-Relational Database (ORDB)

✔ Hybrid of RDBMS + OODB.

✔ Uses tables but allows objects & custom data types.
✔ Ideal for enterprise & web applications (ERP, CRM, Banking).
✔ Example: PostgreSQL, Oracle, IBM Db2, Microsoft SQL
Server.

Example (ORDB - SQL in PostgreSQL)

CREATE TYPE BookType AS (

book_id INT,
title VARCHAR(100),
author VARCHAR(100)
);

CREATE TABLE Library (

book BookType,
published_year INT
);

ORDB still uses tables but supports object-like structures.

7. Object oriented data base advantage and disadvantages

Advantages and Disadvantages of Object-Oriented Database
(OODB)
Advantages of OODB
1. Real-World Representation
o Stores data as objects, classes, and inheritance, making it
closer to real-world modeling (e.g., CAD, multimedia).
2. No Need for Complex Joins
o Unlike relational databases (RDBMS), OODB directly
links objects, reducing the need for costly JOIN
operations.
3. Reusability
o Objects and classes can be reused in different parts of an
application, saving development time.
4. Encapsulation
o Objects contain both data and methods, improving data
security and integrity.
5. Faster Performance for Complex Data
o OODB is optimized for applications handling complex
data types (e.g., 3D models, AI, medical imaging).
6. Support for Relationships
o Directly supports one-to-many, many-to-many

relationships without the need for foreign keys.

7. Persistence
o Objects remain stored in the database permanently and can
be retrieved efficiently.

Disadvantages of OODB
1. Complexity in Design & Implementation
o OODB is more complex to design than relational
databases due to object relationships.
2. Not Standardized
o Unlike SQL (which is standard for RDBMS), OODB
lacks a universal query language (OQL is used but not
widely adopted).
3. Limited Support & Adoption
o Most organizations use RDBMS (e.g., MySQL,

PostgreSQL) instead of OODB due to better support and

community adoption.
4. Slow for Simple Queries
o If an application only requires simple data storage and
retrieval (e.g., employee records, inventory), RDBMS is
faster than OODB.
5. Difficult Integration with Existing Systems
o Many enterprise applications rely on relational databases
(SQL-based), making it hard to integrate OODB with
existing systems.
6. Lack of Reporting & Analytics Tools
o RDBMS has strong analytics and reporting tools, but
OODB lacks advanced querying and reporting features.

8. Shared memory shared disk shared nothing

Difference Between Shared Memory, Shared Disk, and Shared
Nothing Architectures
These are three different parallel database architectures used for
managing large-scale data processing and distributed systems.

1 Shared Memory Architecture

All processors share a single memory and storage system.
✔ How It Works:
• Multiple CPUs (processors) share the same memory (RAM)
and disk.
• All CPUs can access and modify the same data.

✔ Example:
• Single large server with multiple processors (e.g., Oracle
Database on a high-performance server).
✔ Advantages:
Fast communication between processors (since they share
memory).
Easy to program and manage since all CPUs access the same
data.
Disadvantages:
Memory bottleneck – When too many CPUs try to access the
same memory, performance decreases.
Scalability issues – Adding more processors makes
synchronization difficult.

2 Shared Disk Architecture

All processors have separate memory but share a common
disk/storage system.
✔ How It Works:
• Each processor has its own local memory.
• All processors share the same disk storage (database).

✔ Example:
• Clustered database systems (e.g., Oracle RAC (Real

Application Clusters), IBM DB2).

✔ Advantages:
More scalable than Shared Memory – Adding more
processors does not overload memory.
Fault tolerance – If one processor fails, others can still access
the shared disk.
Disadvantages:
Disk bottleneck – If too many processors try to access the
same disk, performance slows down.
Complex synchronization – Since multiple processors write
to the same disk, locking mechanisms are needed to avoid
conflicts.

3 Shared Nothing Architecture

Each processor has its own memory and disk (completely
independent nodes).
✔ How It Works:
• Each processor manages its own memory and disk without
sharing.
• Data is partitioned across multiple nodes.
• Nodes communicate only through a network.
✔ Example:
• Distributed databases (e.g., Google Bigtable, Amazon
Redshift, Apache Hadoop, NoSQL databases like MongoDB,
Cassandra).
✔ Advantages:
Highly scalable – More nodes can be added easily.
No contention for resources – Each processor handles its own
data.
Best for distributed and cloud computing.
Disadvantages:
Complex data distribution – Data must be carefully
partitioned to avoid imbalance.
High network communication overhead – Nodes must
communicate over a network to share results.

9.Cloud based service

Cloud-based ADBMS deploys databases on cloud infrastructure,
offering scalability, flexibility, and cost efficiency.
1.Types of Cloud-Based Database Services
📌 1. Database as a Service (DBaaS) – Fully managed cloud
databases.
✔ Examples: Amazon RDS, Google Cloud SQL, Azure SQL,
MongoDB Atlas
✔ Benefits: No hardware management, auto-backups, high
scalability.
📌 2. Cloud-Based Relational Databases – Follow ACID
principles, best for structured data.
✔ Examples: Amazon Aurora, Google Cloud Spanner, Azure
SQL
✔ Benefits: Ideal for ERP, CRM, banking, supports SQL.
📌 3. Cloud-Based NoSQL Databases – High-speed, flexible
storage for big data & IoT.
✔ Examples: Amazon DynamoDB, Google Fire store, Azure
Cosmos DB
✔ Benefits: Handles unstructured data, low-latency performance.
📌 4. Cloud-Based Big Data & Analytics – Optimized for large-
scale analytics & AI.
✔ Examples: Google Big Query, Amazon Redshift, Azure
Synapse
✔ Benefits: Best for BI, AI/ML workloads, real-time data
processing.

2.Benefits of Cloud-Based ADBMS

Feature On-Premise Cloud-Based

Scalability Limited High

Expensive (Hardware & Pay-as-you-go

Cost
Maintenance)
Optimized & high-
Performance Infrastructure-dependent speed

Built-in security &

Security Manual setup compliance
High uptime &
Availability Hardware failure risks backups

3.Challenges of Cloud-Based ADBMS

❌ Security Risks – Vulnerable to cyber threats.
❌ Latency Issues – Network dependency may cause delays.
❌ Vendor Lock-in – Hard to switch between providers.
📌 Cloud ADBMS = Scalable, Cost-Effective, but Requires
Careful Security & Vendor Planning! 🚀
9. Primary key constraint implementation
Primary Key Constraint Implementation in SQL
A Primary Key is a constraint in SQL that uniquely identifies each
record in a table. It does not allow NULL values and must contain
unique values.

1 Implementing Primary Key in SQL

Example: Creating a Table with a Primary Key
CREATE TABLE Students (
StudentID INT PRIMARY KEY,
Name VARCHAR(50),
Age INT
);
✔ Explanation:
• StudentID is the Primary Key (must be unique and not NULL).

• If you try to insert duplicate or NULL values in StudentID, it

will throw an error.

2 Using PRIMARY KEY for Multiple Columns (Composite

Key)
You can define a Primary Key on multiple columns when a
single column cannot uniquely identify a record.
CREATE TABLE Enrollments (
StudentID INT,
CourseID INT,
EnrollmentDate DATE,
PRIMARY KEY (StudentID, CourseID)
);
✔ Explanation:
• The combination of (StudentID, CourseID) is unique for each
record.
• You cannot have duplicate StudentID & CourseID pairs in
this table.

3 Adding a Primary Key to an Existing Table

If you already have a table and want to add a Primary Key later,
use:
ALTER TABLE Students
ADD CONSTRAINT PK_Student PRIMARY KEY (StudentID);
✔ Explanation:
• PK_Student is the name of the constraint (optional but useful
for reference).
• The Primary Key is added to the StudentID column.

4 Removing a Primary Key

To remove a Primary Key constraint:
ALTER TABLE Students
DROP CONSTRAINT PK_Student;
✔ Note: You must remove Foreign Key references before
dropping a Primary Key.

5 Inserting Data into a Table with a Primary Key

INSERT INTO Students (StudentID, Name, Age) VALUES (101,
'Alice', 20);
INSERT INTO Students (StudentID, Name, Age) VALUES (102,
'Bob', 22);
Invalid: The following query fails because 101 already exists.
INSERT INTO Students (StudentID, Name, Age) VALUES (101,
'Charlie', 21);
-- ERROR: Duplicate entry '101' for key 'PRIMARY'
6 Primary Key vs. Unique Key
Feature Primary Key Unique Key

Ensures unique Ensures unique

Uniqueness values
values
Allowed (one or
NULL Values Not allowed more)

Number of Multiple per table

Only one per table
Keys
PRIMARY KEY UNIQUE (Email)
Example
(ID)

10. User authentication mechanism

User Authentication Mechanism
=User authentication verifies identity before granting system
access.

1 Authentication Types
1. Password-Based – Users log in with a password (stored
securely using hashing).
✔ Example (SQL User Table):
CREATE TABLE Users (
UserID INT PRIMARY KEY,
Username VARCHAR(50) UNIQUE,
PasswordHash VARCHAR(255) -- Hashed storage
);
2. Multi-Factor (MFA) – Combines:
✔ Password (Something you know)
✔ OTP/Token (Something you have)
✔ Fingerprint/Face ID (Something you are)
3. Token-Based (JWT) – Server issues a token after login for
future authentication.
✔ Example:
{ "user_id": 101, "role": "admin", "exp": 1715996400 }

No session storage, ideal for APIs.

4. Biometric – Uses Face ID, Fingerprint, Retina Scan.

✔ Fast & secure but costly.

5. OAuth 2.0 (Third-Party Login) – Login via Google,

Facebook, etc.
✔ Secure & avoids password handling.

2 Best Practices

✔ Hash passwords (bcrypt, Argon2).

✔ Use MFA for extra security.
✔ Limit login attempts.
✔ Use HTTPS for encryption.
✔ Implement session timeouts.
Stronger authentication = Better security!

11. Trigger
Trigger in ADBMS (Advanced Database Management
Systems)
A trigger is a special type of stored procedure in a database that
automatically executes when a specified event occurs in a table.
Why Use Triggers?
Automatically enforce business rules
Maintain data integrity
Log changes in a table
Restrict unauthorized modifications

1 Types of Triggers
Type Description

Executes before an INSERT, UPDATE,

Before Trigger or DELETE.
Executes after an INSERT, UPDATE,
After Trigger or DELETE.

Replaces the default action (used in

Instead Of Trigger views).

Row-Level Trigger Executes for each row affected.

Statement-Level Executes once for the whole

Trigger statement, not for each row.

2 Creating a Trigger
Example 1: Trigger to Prevent Negative Salary
CREATE TRIGGER prevent_negative_salary
BEFORE INSERT OR UPDATE ON Employees
FOR EACH ROW
BEGIN
IF NEW.salary < 0 THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Salary cannot be negative';
END IF;
END;
✔ Explanation:
• Runs before INSERT or UPDATE on Employees.

• If the salary is negative, it stops the transaction with an error.

Example 2: Log Changes in Employee Table

CREATE TABLE Employee_Audit (
AuditID INT AUTO_INCREMENT PRIMARY KEY,
EmployeeID INT,
OldSalary DECIMAL(10,2),
NewSalary DECIMAL(10,2),
ChangeDate TIMESTAMP DEFAULT
CURRENT_TIMESTAMP
);

CREATE TRIGGER log_salary_changes

AFTER UPDATE ON Employees
FOR EACH ROW
INSERT INTO Employee_Audit (EmployeeID, OldSalary,
NewSalary)
VALUES (OLD.EmployeeID, OLD.salary, NEW.salary);
✔ Explanation:
• AFTER UPDATE: Runs after an update on Employees.
• Records old and new salaries in Employee_Audit table.

Example 3: Delete Restriction Trigger

CREATE TRIGGER prevent_employee_delete
BEFORE DELETE ON Employees
FOR EACH ROW
BEGIN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Employees cannot be deleted!';
END;
✔ Prevents deleting employees from the table.

3Advantages of Triggers
✔ Automatic Execution – Runs without manual intervention.
✔ Enforces Business Rules – Ensures valid data entry.
✔ Maintains Data Integrity – Prevents invalid transactions.
S
4 Disadvantages of Triggers
Hard to Debug – Errors in triggers are difficult to trace.
Performance Impact – Too many triggers slow down the
database.
Hidden Execution – Runs automatically, making tracking
difficult.