Database

A database is an organized collection of related data stored in a structured format so that it can
be easily accessed, managed, and updated.

Examples:

• Student database in a college (Name, Roll No, Marks)

• Bank database (Account No, Balance, Transactions)

2. Data Independence vs Data Abstraction

Feature Data Independence Data Abstraction

Ability to change schema without affecting Hiding unnecessary details from

Meaning
applications users

Focus Flexibility Simplicity

Types Logical & Physical Physical, Logical, View

User sees only required data

Example Change storage without affecting user view

Define Data Abstraction

Data abstraction is the process of hiding internal implementation details and showing only
essential features to users.

Levels:

• Physical Level

• Logical Level

• View Level
3. Characteristics of Database Approach

1. Data Independence – Changes in data structure do not affect programs

2. Reduced Data Redundancy – Avoids duplication of data

Characteristics of Database Approach

The database approach has several important characteristics that distinguish it from
traditional file systems.

1. Self-Describing Nature

• A database system contains not only data but also metadata (data about data).

• This information is stored in a data dictionary.

• Example: Table structure, data types, constraints.

2. Program–Data Independence

• Application programs are independent of data structure.

• Changes in database schema do not affect programs.

• Example: Adding a new column does not require rewriting programs.

3. Data Abstraction

• Hides internal details from users.

• Users see only required information.

• Levels:

o Physical level

o Logical level

o View level

4. Multiple Views of Data

• Different users can view data in different ways.

• Example:

o Admin → full access

o Student → only marks

5. Data Sharing and Multi-user Access

 • Multiple users can access data simultaneously.

• DBMS handles concurrency control.

• Example: Online banking system.

6. Reduced Data Redundancy

• Avoids duplication of data.

• Ensures consistency.

7. Improved Data Security

• Access control mechanisms protect data.

• Example: Passwords, user roles.

Conclusion

These characteristics make the database approach efficient, secure, flexible, and suitable for
modern applications.

4. Primary Purpose of Database System

The main purpose is to:

• Store large amounts of data efficiently

• Retrieve and update data easily

• Ensure data security and integrity

5. Role of DBMS in Modern Applications

DBMS plays an important role by:

• Managing large-scale data (banking, e-commerce, hospitals)

• Providing data security

• Supporting multi-user access

• Ensuring backup and recovery

6. Database vs DBMS
Database DBMS

Collection of data Software to manage database

Passive Active

Example: Student records Example: MySQL, Oracle

7.

8. What is Data Model? Categories

A data model defines how data is structured and stored.

Categories:

1. Conceptual Model (ER Model)

2. Logical Model (Relational Model)

3. Physical Model

9. Schema

A schema is the overall design or structure of a database.

Example:

Student(Name, RollNo, Marks)

. Entity

An entity is a real-world object with independent existence.

Examples:

• Student

• Employee

Entity

An entity is an object that exists in the real world and is distinguishable.

Entity Type and Entity Set

 • Entity Type: Structure (Student)

• Entity Set: Collection of entities (All students in class)

10. Types of Database Languages

1. DDL (Data Definition Language) – CREATE, ALTER

2. DML (Data Manipulation Language) – INSERT, UPDATE

3. DCL (Data Control Language) – GRANT, REVOKE

4. TCL (Transaction Control Language) – COMMIT, ROLLBACK

11. Data Independence

Data independence is the ability to change the schema without affecting application programs.

12. Define Data Dictionary

A data dictionary is a repository that stores metadata (data about data), such as table names,
attributes, and data types.

13. Specialization vs Generalization

Specialization Generalization

Top-down approach Bottom-up approach

Divide entity into sub-entities Combine entities into one

Example: Employee → Manager Manager + Clerk → Employee

14
15. Logical vs Physical Data Independence

Logical Physical

Change logical schema Change physical storage

Harder to achieve Easier

Example: Add new column Change file structure

16. Structural Constraints in ER Model

Structural constraints define how entities are related.

Types:

• Cardinality ratio

• Participation constraint

Example:

One teacher teaches many students (1:M)

17. Attribute

An attribute is a property of an entity.

Example:
Student → Name, Age

18. Simple Attribute

Cannot be divided further.

Example: Age

19. Composite Attribute

Can be divided into sub-parts.

Example:
Name → First Name, Last Name

20. Multivalued Attribute

Can have multiple values.

Example:
Phone Number

21. Weak Entity Type

An entity that depends on another entity for existence.

Example:
Dependent depends on Employee

22.

i) Degree

Number of attributes in a relation

Example: Student(Name, RollNo) → Degree = 2

ii) Cardinality

Number of tuples (rows) in a relation

23.

25. Key and Value Sets

• Key: Attribute that uniquely identifies entity (RollNo)

• Value Set: Set of possible values of attribute

26. Relation

A relation is a table consisting of rows and columns.

27. Types of Attributes

1. Simple

2. Composite

3. Multivalued

4. Derived

28. DBMS and Data Security

DBMS ensures security by:

 • Authentication (login)

• Authorization (access control)

• Encryption

• Backup & recovery

29. Define Cardinality Ratio

Defines number of relationships between entities.

Types:

• 1:1

• 1:M

• M:N

30. Relationship Types and Sets

• Relationship Type: Defines association (e.g., "works for")

• Relationship Set: Collection of relationships

Example:

Employee works in Department

[Link] in Relational Model.

Difference from Attribute

A domain is a set of permissible values that an attribute can take.

Example:

For attribute Age → Domain = {0–120}

Difference:

Domain Attribute

Set of allowed values Column in a table

Defines data type Stores actual data

Example: Integer Example: Age

2. Foreign Key and its Importance

A foreign key is an attribute in one relation that refers to the primary key of another relation.

Example:

Student(DeptID) → references Department(DeptID)

Importance:

• Maintains referential integrity

• Prevents invalid data

• Establishes relationship between tables

3. Characteristics of a Relation

1. No duplicate tuples

2. Order of rows and columns does not matter

3. Each attribute has a unique name

4. Values are atomic (no repeating groups)

5. Each column has a domain

4. NULL Values in Relational Database

NULL represents:
 • Missing value

• Unknown value

• Not applicable value

Example:

Employee(Salary = NULL)

Note:

NULL is different from 0 or empty space.

5. Entity Integrity

Entity integrity ensures that:

• Primary key cannot be NULL

• Each tuple is uniquely identifiable

6. Update vs Delete Operation

Update Delete

Modifies existing data Removes tuple

Keeps row Removes row

Example: Change salary Example: Remove employee

7. Purpose of Primary Key

• Uniquely identifies each record

• Prevents duplicate data

• Ensures entity integrity

8. Constraint Violation with Example

Occurs when a rule is broken.

Example:

Primary key = NULL → Violation

Duplicate RollNo → Violation
9. Union vs Intersection in Relational Algebra

Union Intersection

Combines tuples from two relations Finds common tuples

Removes duplicates Only common data

Symbol: ∪ Symbol: ∩

10. Projection Operation (π)

Projection selects specific columns from a relation.

Example:

π(Name, Salary) (Employee)

→ Displays only Name and Salary

11. Aggregate Functions

Used to perform calculations on data.

Examples:

• COUNT()

• SUM()

• AVG()

• MAX()

• MIN()

12. Grouping in Relational Algebra

Grouping divides tuples into groups based on attribute values.

Example:

Group employees by department to find average salary

13. Define Tuple

A tuple is a row in a relation.

Example:

(101, Arya, 50000)

14. One-to-Many vs Many-to-Many

One-to-Many Many-to-Many

One entity relates to many Many relate to many

Example: One teacher → many students Students ↔ Courses

15. Natural Join vs Cartesian Product

Natural Join Cartesian Product

Combines matching attributes Combines all rows

Removes duplicate columns No condition

Meaningful result Large unnecessary result

16. Select vs Project Operation

Select (σ) Project (π)

Select rows Select columns

Condition based Attribute based

Example: Salary > 50000 Name, Salary

17. Query: Total and Average Salary

SELECT SUM(Salary), AVG(Salary)

FROM Employee
WHERE DeptName = 'Industrial Safety';

18. Query: Employee Details

SELECT FirstName, LastName, Salary

FROM Employee
WHERE DeptName = 'Industrial Safety';

19. Key Constraint & Entity Integrity Constraint Violation

Key Constraint:

• Ensures uniqueness of primary key

Entity Integrity Violation:

• Primary key is NULL

Example:

RollNo = NULL → Violation

20. Definitions

1. Attribute

A column in a table representing a property

Example: Name

2. Relation

A table consisting of rows and columns

3. Degree of Relation

Number of attributes (columns) in a relation

Example:

Student(Name, RollNo, Age) → Degree = 3

Real-World Example of DBMS Approach

Example: Banking System

A banking system is a common real-world application where the DBMS approach is used.

How DBMS is Used

1. Data Storage

• Customer details (Name, Account Number, Balance) are stored in database tables.

• Example:

o Customer Table

o Account Table

o Transaction Table

2. Data Sharing

• Multiple users (bank staff, ATM, mobile apps) can access the same data.

• Example: A customer withdraws money from an ATM while the bank server updates the
balance.

3. Data Integrity

• Ensures accurate and consistent data.

• Example: Balance cannot become negative (constraint enforcement).

4. Security

• Only authorized users can access or modify data.

• Example:

o Customer → can view own account

o Bank staff → limited access

5. Concurrency Control

• Handles multiple transactions at the same time.

• Example: Two withdrawals at the same time are processed correctly without errors.
6. Backup and Recovery

• Data is backed up regularly.

• Example: If system crashes, data can be restored.

Conclusion

The DBMS approach in banking ensures efficient data management, security, accuracy, and
reliability, making it essential for modern systems.

---------------------------------------------------------------------------------------------------------------------

Real-World Example of DBMS Approach

Example: Hospital Management System

A hospital management system uses a DBMS to manage patient records, doctors,

appointments, and billing.

How DBMS is Used

1. Data Storage

• Stores information in tables such as:

o Patient (Name, Age, Disease)

o Doctor (ID, Name, Specialization)

o Appointment (Date, Time)

2. Data Sharing

• Different departments access the same data.

• Example:

o Doctor views patient history

o Receptionist schedules appointments

3. Data Integrity
 • Ensures correct and consistent data.

• Example: Patient ID must be unique.

4. Security

• Protects sensitive medical data.

• Example:

o Doctors → access patient details

o Patients → limited access

5. Concurrency Control

• Multiple users can access data simultaneously.

• Example: Lab updates report while doctor checks it.

6. Backup and Recovery

• Ensures data safety in case of failure.

• Example: Patient history can be restored after crash.

Conclusion

The DBMS approach in hospitals improves efficiency, accuracy, security, and quick access to
patient information, which is critical for healthcare services.
Different Database Languages and Interfaces

A. Database Languages

Database languages are used to define, manipulate, control, and query data in a DBMS.

1. Data Definition Language (DDL)

• Used to define and modify the database structure.

• Deals with schema creation.

Commands:

• CREATE – Create tables/databases

• ALTER – Modify structure

• DROP – Delete tables

Example:

CREATE TABLE Student (

RollNo INT,
Name VARCHAR(20)
);

2. Data Manipulation Language (DML)

• Used to insert, update, delete data.

Commands:

• INSERT

• UPDATE

• DELETE

Example:

INSERT INTO Student VALUES (1, 'Anu');

3. Data Query Language (DQL)

• Used to retrieve data from database.

Command:

• SELECT

Example:

SELECT * FROM Student;

4. Data Control Language (DCL)

• Controls access and permissions.

Commands:

• GRANT

• REVOKE

Example:

GRANT SELECT ON Student TO user1;

5. Transaction Control Language (TCL)

• Manages transactions in DBMS.

Commands:

• COMMIT

• ROLLBACK

• SAVEPOINT

B. Database Interfaces

Interfaces allow users to interact with the DBMS.

1. Command Line Interface (CLI)

• User types SQL commands manually

• Used by developers and DBAs

2. Graphical User Interface (GUI)

• Uses forms, menus, icons

• Example: phpMyAdmin, Oracle GUI

3. Application Interface

• DBMS connected with applications

• Example: Banking app using backend database

4. Web Interface

• Access database through web browsers

 • Example: Online shopping sites

2. User-Friendly Interfaces and Types of Users

A. Types of Interfaces and Users

1. Menu-Based Interface

• User selects options from menus

• No need to know SQL

Users:

• Naive users (beginners)

Example: ATM machine

2. Form-Based Interface

• Users fill forms to input data

Users:

• Data entry operators

Example: Hospital patient registration

3. Graphical User Interface (GUI)

• Uses windows, icons, buttons

Users:

• General users

Example: Desktop database tools

4. Natural Language Interface

• User interacts using normal language

Users:

• Non-technical users

Example: “Show all students with marks > 80”

5. Query Language Interface

 • Users write SQL queries

Users:

• Programmers, analysts

6. Parametric Interface

• Uses predefined queries

Users:

• Clerks, bank tellers

Example: Bank transaction systems

B. Types of Database Users

1. Naive Users

• Use predefined interfaces

• Example: ATM users

2. Application Programmers

• Write programs using DBMS

• Use APIs and SQL

3. Sophisticated Users

• Use SQL for complex queries

• Example: Data analysts

4. Database Administrators (DBA)

• Manage entire database system

• Responsible for security, backup, tuning

3. Role of Database Languages in Interaction

Database languages act as a bridge between users/applications and the database.

A. Interaction Process
 1. User/Application sends query (SQL)

2. DBMS processes query

3. Data is retrieved/modified

4. Result is returned to user

B. Role of Each Language

1. DDL

• Defines structure → Helps DBMS understand data organization

2. DML

• Allows applications to modify data

• Example: Insert new customer

3. DQL

• Retrieves data for reports and analysis

4. DCL

• Ensures security

• Controls user permissions

5. TCL

• Maintains consistency and reliability

• Ensures successful transactions

C. Importance in Applications

• Enables communication between front-end and database

• Supports real-time operations (banking, e-commerce)

• Ensures data accuracy and consistency

• Helps in automation of processes

Conclusion
Database languages are essential for defining, accessing, controlling, and managing data,
while interfaces make interaction easy and user-friendly for different types of users. Together,
they ensure smooth communication between users, applications, and the database system.
Actors in Database System

In a DBMS environment, people interacting with the database are called actors. They are
classified into:

1. Actors on the Scene (Direct Users)

2. Actors behind the Scene (Indirect Users)

1. Actors on the Scene

These are users who directly interact with the database system.

1. Database Administrators (DBA)

• Responsible for overall management of the database

• Duties:

o Define schema

o Manage security and authorization

o Backup and recovery

o Performance tuning

2. Database Designers

• Design the structure of the database

• Identify entities, relationships, constraints

3. Application Programmers

• Write programs that interact with the database

• Use languages like SQL, Java, Python

4. End Users

These are the actual users who use the database.

Types of End Users:

a) Naive Users

• Use predefined applications

• Example: ATM users

b) Casual Users

• Access database occasionally

• Use simple queries

c) Sophisticated Users

• Experts who write complex queries

• Example: Data analysts

d) Standalone Users

• Use personal databases

• Example: MS Access users

2. Actors Behind the Scene

These are people who do not directly use the database but support its functioning.

1. DBMS Designers and Implementers

• Design and develop DBMS software

• Example: Developers of MySQL, Oracle

2. Tool Developers

• Create tools for:

o Design

o Performance monitoring

o Data analysis

3. Operators and Maintenance Staff

• Handle hardware and system operations

• Perform backups, monitor system

Difference Between On Scene and Behind Scene

Actors on the Scene Actors Behind the Scene

Direct interaction Indirect interaction

Use database Support DBMS

Example: DBA, users Example: DBMS developers

Conclusion

Both types of actors are essential:

• On-scene actors use and manage the database

• Behind-the-scene actors ensure smooth functioning of the DBMS

Together, they make the database system efficient, reliable, and secure.
Characteristics of Database Approach

The database approach is different from traditional file systems because it provides better
data management, flexibility, and security. The six main characteristics are explained below:

1. Self-Describing Nature of Database System

Explanation:

A DBMS stores not only data but also metadata (data about data) in a data dictionary.

• Metadata includes:

o Table names

o Data types

o Constraints

Example:

In a Student database, the system stores:

• Table: Student

• Attributes: Name (VARCHAR), Age (INT)

Even without seeing actual data, structure is known.

2. Program–Data Independence

Explanation:

Application programs are independent of data structure.

Changes in database do not affect application programs.

Example:

• If a new column Email is added to Student table

• Existing program still works without modification

3. Data Abstraction

Explanation:

Hides internal details and shows only required data to users.

Levels:

• Physical level → storage details

 • Logical level → structure

• View level → user view

Example:

• A student sees only marks

• Admin sees full details

4. Support for Multiple Views

Explanation:

Different users can view the same data in different ways.

Example:

• Teacher → sees marks and attendance

• Student → sees only marks

• Admin → sees all data

5. Data Sharing and Multi-user Transaction Processing

Explanation:

Multiple users can access database simultaneously.

DBMS ensures consistency using concurrency control.

Example:

In a banking system:

• One user withdraws money

• Another deposits money

Both transactions are handled correctly.

6. Reduced Data Redundancy and Improved Consistency

Explanation:

DBMS minimizes duplication of data and ensures consistency.

Example:

• Customer data stored once in a centralized database

• Avoids storing same data in multiple files

If address is updated → reflected everywhere

Conclusion

These six characteristics make the database approach:

• Efficient

• Secure

• Reliable

• Scalable

Thus, DBMS is widely used in banking, hospitals, e-commerce, and education systems.
Categories of Data Models

A data model defines how data is structured, stored, and manipulated in a database.
Data models are broadly classified into three main categories:

1. Conceptual (High-Level) Data Model

Explanation:

• Describes the database at a very high level

• Focuses on what data is stored, not how it is stored

• Easy for users to understand

Features:

• Represents real-world entities and relationships

• Independent of DBMS

Example Model:

• Entity-Relationship (ER) Model

Example:

• Entity: Student

• Attributes: Name, RollNo

• Relationship: Student enrolls in Course

2. Logical (Representational) Data Model

Explanation:

• Describes how data is logically structured

• More detailed than conceptual model

• Independent of physical storage

Types of Logical Models:

a) Relational Model

• Data stored in tables (relations)

• Most widely used

Example:
Student(RollNo, Name)

b) Hierarchical Model
 • Data organized in tree structure

• Parent-child relationship

Example:
Company → Department → Employee

c) Network Model

• Data organized as graph

• Allows many-to-many relationships

Features:

• Defines tables, keys, relationships

• Closer to implementation

3. Physical (Low-Level) Data Model

Explanation:

• Describes how data is actually stored in memory

• Deals with storage details

Features:

• File organization

• Indexing

• Data compression

Example:

• Data stored in disk blocks

• Use of B+ trees for indexing

Comparison of Categories

Category Level Focus Example

Conceptual High What data ER Model

Logical Medium Structure Relational Model

Physical Low Storage File structures

Conclusion

The three categories of data models provide different levels of abstraction:

• Conceptual → User view

• Logical → Structure of database

• Physical → Storage details

Together, they help in designing a database system that is efficient, flexible, and easy to
manage.
(i) Weak Entity Type

Definition:

A weak entity type is an entity that cannot be uniquely identified by its own attributes and
depends on a strong entity for identification.

Key Points:

• Does not have a primary key

• Uses a partial key + primary key of strong entity

• Existence depends on strong entity

Example:

• Employee (EmpID) → Strong entity

• Dependent (Name, Age) → Weak entity

A dependent is identified by (EmpID + Name)

(ii) Participation Constraint

Definition:

Participation constraint specifies whether all or some entities must participate in a

relationship.

Types:

1. Total Participation – Every entity must participate

2. Partial Participation – Some entities may not participate

Example:

• Every employee must belong to a department → Total participation

• Not all employees manage a project → Partial participation

(iii) Cardinality Ratio

Definition:

Cardinality ratio defines the number of entities that can be associated with another entity.

Types:

• 1:1 (One-to-One)

• 1:M (One-to-Many)

• M:N (Many-to-Many)
Example:

• One teacher teaches many students → 1:M

• Students enroll in many courses → M:N

(iv) Recursive Relationship

Definition:

A recursive relationship is a relationship where an entity is related to itself.

Example:

• Employee supervises another employee

Employee → supervises → Employee

(v) Specialization

Definition:

Specialization is a top-down approach where a higher-level entity is divided into sub-entities

based on characteristics.

Key Points:

• Inheritance concept

• Subclasses inherit attributes of parent

Example:

• Employee → Manager, Clerk

Manager and Clerk are specialized entities of Employee

Conclusion

These concepts are essential in ER modeling to:

• Represent real-world relationships

• Maintain data integrity

• Design efficient databases

1. Entity

Definition:

An entity is a real-world object or thing that exists independently and can be uniquely
identified.

Examples:

• A student named “Anu”

• An employee with ID 101

These are individual objects

2. Entity Type

Definition:

An entity type is a collection (or class) of similar entities that share the same attributes.

Features:

• Defines structure

• Similar to a table schema

Example:

Student (RollNo, Name, Age)

Describes what a student entity looks like

3. Entity Set

Definition:

An entity set is a collection of all entities of a particular entity type at a given time.

Example:

All students in a class:

• (101, Anu, 20)

• (102, Rahul, 21)

This collection forms the entity set

Differences Among Entity, Entity Type, and Entity Set

Feature Entity Entity Type Entity Set

Meaning Single object Structure/class Collection of entities

Nature Instance Definition Group

Example Anu Student All students

Representation Record Schema Table data

Simple Analogy (Easy to Remember)

Think of a classroom:

• Entity Type → “Student” (definition of student)

• Entity → One student (ANJU)

• Entity Set → All students in the class

Conclusion

• Entity → Individual object

• Entity Type → Blueprint/structure

• Entity Set → Collection of entities

These concepts are fundamental in ER modeling and help in designing databases efficiently.
Types of Relational Model Constraints

Constraints in the relational model are rules that ensure accuracy, consistency, and integrity
of data in a database.

1. Domain Constraint

Definition:

A domain constraint specifies that the value of an attribute must belong to a defined domain
(set of valid values).

Key Points:

• Defines data type, format, and range

• Prevents invalid data entry

Example:

• Age must be between 0 and 120

• Gender must be ‘M’ or ‘F’

If Age = 200 → Violation

2. Key Constraint

Definition:

A key constraint ensures that no two tuples have the same value for a key attribute.

Key Points:

• Ensures uniqueness

• Applies to primary key

Example:

Student(RollNo, Name)

• RollNo must be unique

• Two students cannot have same RollNo

3. Entity Integrity Constraint

Definition:

Entity integrity ensures that the primary key cannot be NULL.

Key Points:
 • Every tuple must have a valid identity

• No missing primary key value

Example:

Student(RollNo = NULL) → Not allowed

4. Referential Integrity Constraint

Definition:

Ensures that a foreign key value must match an existing primary key value in another table.

Key Points:

• Maintains relationship between tables

• Prevents invalid references

Example:

Department(DeptID)
Employee(DeptID)

• Employee DeptID must exist in Department

DeptID = 10 (not in Department) → Violation

5. General (Semantic) Constraints

Definition:

These are application-specific rules defined by users.

Key Points:

• Not covered by standard constraints

• Defined using triggers or assertions

Example:

• Salary must be greater than 10,000

• A student cannot enroll in more than 5 courses

Summary Table

Constraint Type Purpose Example

Domain Valid values Age (0–120)

Constraint Type Purpose Example

Key Uniqueness RollNo unique

Entity Integrity No NULL PK RollNo ≠ NULL

Referential Integrity Valid reference DeptID exists

General Business rules Salary > 10000

Conclusion

Relational model constraints ensure:

• Data accuracy

• Consistency

• Reliability

They are essential for maintaining a correct and meaningful database system.
Update Operations in the Relational Model

In the relational model, update operations are used to modify the data stored in relations
(tables). These operations must follow integrity constraints to maintain database consistency.

Types of Update Operations

There are three main update operations:

1. Insert Operation

Definition:

The INSERT operation is used to add new tuples (rows) into a relation.

Example:

INSERT INTO Student (RollNo, Name, Age)

VALUES (101, 'Anu', 20);

Possible Problems (Violations):

• Domain constraint violation → Age = -5

• Key constraint violation → Duplicate RollNo

• Entity integrity violation → RollNo = NULL

• Referential integrity violation → Invalid foreign key

2. Delete Operation

Definition:

The DELETE operation removes one or more tuples from a relation.

Example:

DELETE FROM Student

WHERE RollNo = 101;

Possible Problems:

• May violate referential integrity

Example:

• If a department is deleted, employees referencing it may become invalid

Solutions:

• Cascade delete → delete related records

 • Set NULL → foreign key becomes NULL

• Restrict → prevent deletion

3. Update (Modify) Operation

Definition:

The UPDATE operation is used to change existing values in a relation.

Example:

UPDATE Student
SET Age = 21
WHERE RollNo = 101;

Possible Problems:

• Domain constraint violation

• Key constraint violation

• Referential integrity issues

Integrity Constraints in Update Operations

All update operations must satisfy:

1. Domain Constraint → Valid data values

2. Key Constraint → Unique primary key

3. Entity Integrity → No NULL primary key

4. Referential Integrity → Valid foreign key

Summary Table

Operation Purpose Example

INSERT Add new row Add student

DELETE Remove row Delete student

UPDATE Modify data Change age

Conclusion

Update operations are essential for maintaining data in a database. However, they must be
performed carefully to avoid constraint violations and ensure data integrity and consistency.
ER-to-Relational Mapping

Definition:

ER-to-relational mapping is the process of converting an Entity–Relationship (ER) model

(conceptual design) into a relational schema (tables) used in DBMS.

Mapping Rules

1. Mapping Strong Entity Type

• Each strong entity becomes a table

• Attributes become columns

• Primary key remains the same

Example:

Entity: Student(RollNo, Name, Age)

→ Table: Student(RollNo, Name, Age)

2. Mapping Weak Entity Type

• Create a separate table

• Include primary key of owner entity as foreign key

• Combine with partial key

Example:

Employee(EmpID)
Dependent(Name, Age)

→ Table: Dependent(EmpID, Name, Age)

Primary Key = (EmpID + Name)

3. Mapping Binary Relationships

a) One-to-One (1:1)

• Add foreign key to either relation

Example:

Person ↔ Passport
→ Add PassportID in Person table

b) One-to-Many (1:M)

• Add foreign key on the “many” side

Example:

Department → Employee
→ Employee table includes DeptID

c) Many-to-Many (M:N)

• Create a new relation

• Include primary keys of both entities

Example:

Student ↔ Course
→ Table: Enrollment(StudentID, CourseID)

4. Mapping Multivalued Attributes

• Create a separate table

Example:

Student(PhoneNo)
→ StudentPhone(RollNo, PhoneNo)

5. Mapping Composite Attributes

• Break into simple attributes

Example:

Name → FirstName, LastName

6. Mapping Derived Attributes

• Usually not stored (computed when needed)

Example:

Age derived from Date of Birth

7. Mapping N-ary Relationships

• Create a separate table with all participating keys

Challenges in Converting Conceptual to Logical Design

1. Handling Complex Relationships

• Many-to-many and n-ary relationships require additional tables

2. Choosing Proper Keys

• Selecting primary and foreign keys can be difficult

3. Eliminating Redundancy

• Improper mapping may cause duplicate data

4. Maintaining Integrity Constraints

• Ensuring referential and entity integrity is challenging

5. Handling Multivalued and Composite Attributes

• Requires decomposition into multiple tables

6. Performance Issues

• Too many tables → complex joins → slow queries

7. Loss of Semantic Meaning

• Some real-world meanings in ER diagram may not fully translate into tables

8. Trade-off Between Normalization and Efficiency

• Highly normalized design reduces redundancy but may reduce performance

Conclusion

ER-to-relational mapping is a crucial step in database design. While it helps in transforming

conceptual models into implementable schemas, it involves challenges like maintaining
integrity, reducing redundancy, and ensuring performance.
Integrity Constraints in Relational Model

Integrity constraints are rules used to maintain accuracy, consistency, and reliability of data
in a database.

(a) Entity Integrity

Definition:

Entity integrity ensures that the primary key of a relation cannot be NULL.

Key Points:

• Every table must have a primary key

• Primary key must be unique and NOT NULL

• Ensures each tuple (row) is uniquely identifiable

Example:

Student(RollNo, Name, Age)

RollNo Name Age

101 Anu 20

NULL Rahul 21

Row with NULL RollNo violates entity integrity

(b) Referential Integrity

Definition:

Referential integrity ensures that a foreign key must either be NULL or match an existing
primary key value in the referenced relation.

Key Points:

• Maintains relationship between tables

• Prevents invalid references

Example:

Department(DeptID)

DeptID

10

20

Employee(EmpID, DeptID)
EmpID DeptID

1 10

2 30

DeptID = 30 does not exist in Department → Violation

Actions to Maintain Referential Integrity:

• CASCADE → Delete related records

• SET NULL → Set foreign key to NULL

• RESTRICT → Prevent deletion

(c) Domain Constraints and Constraints on NULL Values

1. Domain Constraints

Definition:

Domain constraints ensure that attribute values must belong to a valid domain (set of
permissible values).

Key Points:

• Defines data type, range, format

• Prevents invalid data

Examples:

• Age must be between 0–120

• Gender must be ‘M’ or ‘F’

Age = 150 → Violation

2. Constraints on NULL Values

Definition:

These constraints specify whether an attribute can have NULL values or not.

Key Points:

• Some attributes must have values (NOT NULL)

• NULL means:

o Unknown
 o Missing

o Not applicable

Examples:

Student(RollNo, Name, Age)

• RollNo → NOT NULL

• Name → NOT NULL

• Age → Can be NULL

RollNo Name Age

101 Anu 20

102 NULL 21

Name cannot be NULL → Violation

Summary Table

Constraint Purpose Example

Entity Integrity PK not NULL RollNo ≠ NULL

Referential Integrity FK must match PK DeptID exists

Domain Constraint Valid values Age (0–120)

NULL Constraint Control NULL values Name NOT NULL

Conclusion

These constraints are essential to:

• Maintain data correctness

• Avoid invalid and inconsistent data

• Ensure reliable database operations

1. Insertion Anomaly

Definition:

An insertion anomaly occurs when we cannot insert data into a table without adding unrelated
or unnecessary data.

Example:

Consider a table:

StudentID StudentName Course Instructor

101 Anu DBMS Ravi

102 Manu OS Suresh

Problem:

• Suppose a new course "AI" is introduced with instructor "Arjun", but no student has
enrolled yet.

• We cannot insert this course information because StudentID is required.

Issue:

• We are forced to insert dummy/null data.

2. Deletion Anomaly

Definition:

A deletion anomaly occurs when deleting a record unintentionally removes other important
information.

Example:

If we delete:

| 102 | Manu | OS | Suresh |

Problem:

• If Manu was the only student in the "OS" course,

• Deleting this row removes:

o Course = OS

o Instructor = Suresh
 Issue:

• Important data is lost unintentionally.

3. Modification (Update) Anomaly

Definition:

A modification anomaly occurs when a change in data requires multiple updates in different
rows.

Example:

StudentID StudentName Course Instructor

101 Anu DBMS Ravi

103 Meera DBMS Ravi

Problem:

• If instructor Ravi changes to "Dr. Ravi", we must update multiple rows.

Issue:

• If one row is not updated:

o Data becomes inconsistent

Why Are Anomalies Bad?

1. Data Redundancy

• Same data repeated multiple times

• Wastes storage

2. Data Inconsistency

• Different values for same data

• Leads to incorrect results

3. Data Integrity Problems

• Loss of important data (deletion anomaly)

• Invalid or incomplete data (insertion anomaly)

4. Difficult Maintenance

• Updating becomes complex and error-prone

 Solution: Normalization

To avoid these anomalies, we apply normalization:

• Split the table into smaller related tables

• Ensure proper relationships using keys

Example decomposition:

• STUDENT(StudentID, StudentName)

• COURSE(Course, Instructor)

• ENROLLMENT(StudentID, Course)

Conclusion

Insertion, deletion, and modification anomalies arise due to poor database design and
redundancy. They are considered bad because they:

• Cause data inconsistency

• Lead to loss of information

• Make the database hard to manage

Hence, proper normalization is essential for a well-structured relational database.