DATABASE MANAGEMENT

UNIT-I
Introduction to Database- characteristics, users and
advantages, data models, schemas &instances, three
schema
architecture; data independence, DBMS languages and
interfaces, conceptual data modelling using ER Model.

UNIT-II
Relational Data Model Concepts- domains, attributes,
tuples, relations and their characteristics, constraints-entity
integrity, referential integrity, foreign keys, relational
database design using ER to relational mapping.

UNIT-III
Design guidelines for Relation Schemas. functional
dependencies, normalization of relation schema, normal
forms based
on primary key (1NF, 2NF, 3NF) lossless join and
dependency decomposition.
 UNIT 1

Introduction to Database

Introduction to Database
A database is an organized collection of data that is stored and
accessed electronically. It allows users to efficiently store,
retrieve, and manage data for various applications, from simple
tasks like managing contact lists to complex systems like
banking, e-commerce, or enterprise resource planning.

Why Use a Database?

●​ Efficiency: Quickly access and modify large amounts of

data.
●​ Organization: Structured format helps in systematic data
management.
●​ Security: Restricts access and protects data integrity.
●​ Data Sharing: Supports multiple users and applications.

Key Concepts:

●​ Data: Raw facts (e.g., names, numbers).

●​ Information: Processed data that is meaningful.
●​ DBMS (Database Management System): Software that
interacts with the user, applications, and the database itself
to capture and analyze data.​
Types of Databases:

1.​Relational Databases (RDBMS): Use tables with rows and

columns (e.g., MySQL, PostgreSQL, Oracle).
2.​NoSQL Databases: Handle unstructured data (e.g.,
MongoDB, Cassandra).
3.​Cloud Databases: Hosted on cloud platforms (e.g.,
Amazon RDS, Google Cloud Firestore).
4.​Distributed Databases: Spread across multiple locations
for redundancy and performance.

Components of a Database System:

●​ Tables: Store data in rows and columns.

●​ Schemas: Define the structure of the database.
●​ Queries: Requests for data or information.
●​ Indexes: Improve speed of data retrieval.

Common DBMS Software:

●​ MySQL
●​ Oracle Database
●​ Microsoft SQL Server
●​ PostgreSQL
●​ MongoDB

Characteristics of a Database
1.​Data Integrity​

○​ Ensures accuracy and consistency of data over its

lifecycle.​
 ○​ Enforced through constraints like primary keys, foreign
keys, and validation rules.​

2.​Data Redundancy Control

○​ Reduces unnecessary duplication of data.
○​ Achieved through normalization and relational design.
3.​Data Security
○​ Controls access to data to prevent unauthorized use.
○​ Uses authentication, authorization, encryption, and
roles.
4.​Data Independence
○​ Application programs are independent of how data is
structured or stored.
○​ Changes in the database schema do not affect the
application layer directly.
5.​Concurrent Access
○​ Supports multiple users accessing and modifying data
at the same time.
○​ Managed through locking mechanisms and
transaction control.
6.​Data Consistency
○​ Maintains a consistent view of data across different
operations and users.
○​ Transactions ensure atomicity, consistency, isolation,
and durability (ACID properties).
7.​Backup and Recovery

Provides mechanisms to restore data in case of failure.

○​ Includes automated backups, logs, and recovery tools.​

 8.​Scalability
○​ Can handle increasing amounts of data or users by
scaling vertically (better hardware) or horizontally
(more servers).
9.​Query Processing​
Allows complex queries to retrieve specific data using
query languages like SQL.
10.​ Minimal Data Duplication
●​ Maintains data only once, ensuring efficiency and
minimizing storage costs.​

Users and advantages,

Database Users
Different types of users interact with databases depending on
their roles and technical expertise:

1. Database Administrator (DBA)

●​ Responsible for managing the database system.

●​ Tasks include setting up the database, performance tuning,
security, backup, and recovery.

2. Database Designers

●​ Define the structure of the database (tables, relationships,

constraints).
●​ Ensure that the database meets user and business
requirements.​
3. Application Programmers

●​ Write application code to interact with the database using

programming languages and SQL.

4. End Users

●​ Use applications to access and manipulate data.

●​ Types:
○​ Casual Users: Use the database occasionally with
flexible queries.
○​ Naive Users: Use predefined applications without
knowledge of how the database works.
○​ Sophisticated Users: Use advanced tools like data
analysis and reporting.
○​ Specialized Users: Use complex programs for tasks
like AI, simulations, or scientific applications.

Advantages of a Database System

●​ Data Sharing
○​ Multiple users and applications can access the same
data concurrently.
●​ Data Security
○​ Access to data is controlled and limited to authorized
users.
●​ Data Consistency
○​ Centralized control reduces the chance of data
inconsistency.

Data Models in Databases

A data model defines how data is logically structured, stored,
and manipulated in a database. It provides a blueprint for
designing and organizing data effectively.

Types of Data Models:

1. Hierarchical Data Model

●​ Structure: Data is organized in a tree-like structure with

parent-child relationships.
●​ Example: XML files, early IBM databases.
●​ Pros: Simple, fast retrieval.
●​ Cons: Rigid structure, difficult to reorganize or query
complex relationships.

2. Network Data Model

●​ Structure: Similar to hierarchical, but allows many-to-many

relationships (a child can have multiple parents).
●​ Example: CODASYL DBMS.
●​ Pros: More flexible than hierarchical.
●​ Cons: Complex to design and navigate.

3. Relational Data Model (RDBMS)

●​ Structure: Data is organized into tables (relations) with

rows and columns.
●​ Example: MySQL, PostgreSQL, Oracle.
●​ Pros: Simple, widely used, supports powerful querying via
SQL.​

●​ Cons: Not optimal for unstructured data or very large-scale

distributed systems.
4. Object-Oriented Data Model

●​ Structure: Data is represented as objects, similar to

object-oriented programming.
●​ Example: db4o, ObjectDB.​
Pros: Good for complex data with behaviors.​
Cons: Less commonly used, more complex.

5. Entity-Relationship (ER) Model

●​ Structure: Represents real-world entities and their

relationships
●​ Used For: Database design, especially during the planning
phase.
●​ Components: Entities, Attributes, Relationships.
●​ Pros: Easy to understand, used as a blueprint for RDBMS.

6. Document Data Model (NoSQL)

●​ Structure: Stores data as documents (usually JSON or

BSON).
●​ Example: MongoDB, CouchDB.
●​ Pros: Great for unstructured/semi-structured data, flexible
schema.
●​ Cons: Limited support for complex querying compared to
SQL.

7. Key-Value Data Model

●​ Structure: Data is stored as key-value pairs.

●​ Example: Redis, DynamoDB.
●​ Pros: Extremely fast and scalable.
●​ Cons: No support for relationships or complex queries.
8. Graph Data Model

●​ Structure: Data is stored as nodes (entities) and edges

(relationships).
●​ Example: Neo4j, ArangoDB.
●​ Pros: Ideal for relationship-heavy data like social networks
or recommendation engines.
●​ Cons: Can be less efficient for transactional or tabular
data.

​
schemas & instances,

Schema
A schema is the overall structure or blueprint of a database. It
defines how data is organized and how the relationships among
data are maintained. In essence, it's the design that outlines the
way data is stored, including tables, fields, data types,
relationships, and constraints.

Types of Schemas:

1.​Physical Schema: Describes how data is physically stored

on storage [Link]
2.​Logical Schema: Defines the logical structure of the
database, such as tables, views, and relationships.
3.​View Schema: Represents the user-specific views of the
database, often tailored for specific applications or user
groups.

Characteristics:
 ●​ Static Nature: Schemas are generally stable and do not
change [Link]+2TutorialsPoint+2Wikipedia+2
●​ Design-Oriented: They focus on how data should be stored
and organized.
●​ Foundation for Data Integrity: Schemas enforce rules and
constraints to maintain data accuracy and
[Link]+1Wikipedia+1

Instance

An instance refers to the actual data stored in the database at a

particular moment in time. It's the snapshot of the database
content, reflecting the current state of data as per the defined
[Link]

Characteristics:

●​ Dynamic Nature: Instances change frequently as data is

added, updated, or [Link]
●​ Data-Oriented: They represent the real-time data stored in
the [Link]
●​ Reflects Current State: An instance shows the database's
data at a specific point, which can vary over time.

Comparison Table
 Aspect Schema Instance
Definition Structural design of the Actual data stored at a
database given time
Nature Static (rarely changes) Dynamic (changes
frequently)
Focus How data is organized What data is currently
stored
Examples Table definitions, data Records in tables,
types, constraints current data entries
Change Infrequent Frequent
Frequency

Three schema architecture;

The Three-Schema Architecture is a framework
proposed by the ANSI/SPARC committee in 1975 to
separate the user applications from the physical
database. This architecture provides a clear structure for
database systems, promoting data independence and
efficient data management.

1. External Schema (View Level)

 ●​ Definition: This level defines how individual users or
user applications interact with the data.
●​ Purpose: Provides customized views tailored to
specific user needs, ensuring that users access only
relevant data.
●​ Example: In a university database, a student might
view only their grades and course enrollments, while
a professor accesses information about students
enrolled in their classes.
2. Conceptual Schema (Logical Level)

●​ Definition: This level offers a unified and abstract

representation of the entire database, independent
of how data is stored physically.
●​ Purpose: Describes the structure of the whole
database for a community of users, detailing entities,
data types, relationships, user operations, and
constraints.
●​ Example: Defining entities like "Student," "Course,"
and "Enrollment," along with their attributes and
relationships, without specifying how these are
stored.

3. Internal Schema (Physical Level)

●​ Definition: This level details how data is physically

stored in the database, including data structures and
 access methods.​

●​ Purpose: Manages the physical storage of data,

optimizing performance and storage efficiency.
●​ Example: Storing student records in B-tree indexes
or hash tables, specifying file structures, and
managing data [Link]

Key Benefits of the Three-Schema Architecture

1.​Data Independence:
○​ Logical Data Independence: Changes in the
conceptual schema (e.g., adding a new field) do
not affect external schemas.
○​ Physical Data Independence: Modifications in
the internal schema (e.g., changing storage
devices) do not impact the conceptual
[Link]
2.​Enhanced Security: By providing different external
views, sensitive data can be hidden from
unauthorized users.
3.​Improved Flexibility: Multiple user views can be
created without altering the overall database
structure.
4.​Better Maintainability: Isolation of different levels
allows for easier updates and maintenance without
affecting other layers.
 Data independence,
Data Independence is a fundamental concept in
database management systems (DBMS) that ensures the
separation of data structure from application programs.
This separation allows changes to the database schema
at one level without necessitating changes at the next
higher level, thereby promoting flexibility, scalability, and
ease of maintenance in database [Link]+5The
Knowledge Academy+5GeeksforGeeks+5

🔍 What is Data Independence?

Data independence refers to the capacity to modify the
schema definition of a DBMS at one level without
affecting the schema definition at the next higher level.
This means that changes in the database's structure do
not impact how users interact with the data or how
applications access it.
Unstop+2Scaler+2GeeksforGeeks+2

🧩 Types of Data Independence

Data independence is categorized into two primary types:
1. Physical Data Independence
●​ Definition: The ability to change the physical
storage structures or devices without affecting the
conceptual
[Link]+2TutorialsPoint+2Unstop+2​

●​ Examples:​

○​ Modifying storage devices (e.g., transitioning

from HDDs to SSDs).
○​ Changing file organization techniques or
indexing methods.
○​ Altering compression algorithms or data storage
[Link]+2Unstop+2Wikipedia+2
●​ Significance: Enhances system performance and
storage optimization without disrupting the logical
structure of the database.
2. Logical Data Independence

●​ Definition: The capacity to change the logical

schema without altering the external schemas or
application
[Link]+7Unstop+7Scaler+7​

●​ Examples:​
 ○​ Adding or removing entities, attributes, or
relationships in the database schema.​

○​ Modifying data types or constraints.

●​ Significance: Allows the database to evolve in
response to changing business requirements without
necessitating changes in application code or user
interfaces. Unstop+1The Knowledge Academy+1

●​ ​

Comparison Table

Aspect Physical Data Logical Data

Independence Independence
 Definition Changes in physical Changes in logical
storage without schema without
affecting logical affecting external
schema schema

Focus Internal (Physical) Conceptual (Logical)

Level Level Level

Ease of Generally easier to More challenging to

Implementation achieve implement

Impact on Minimal or none Potentially significant

Applications if not properly
managed

Examples Changing storage Adding new

devices, file attributes, modifying
structures, indexing relationships
methods

✅ Advantages of Data Independence

●​ Flexibility: Facilitates adaptation to changing
storage technologies and business requirements
without overhauling the entire system.​
 ●​ Maintainability: Simplifies database maintenance
by isolating changes to specific schema
[Link]​

●​ Scalability: Supports the growth of the database

system without disrupting existing
[Link]+1The Knowledge Academy+1​

●​ Reduced Redundancy: Minimizes duplication of

data and efforts in application development.
●​ Enhanced Security: Allows for controlled access to
data by defining specific views for different users.​

UNIT 2
Relational Data Model Concepts;
The Relational Data Model is a foundational framework in database
management systems (DBMS) that structures data into tables,
facilitating efficient storage, retrieval, and management. Introduced
by E.F. Codd in 1970, this model underpins most modern relational
database systems like MySQL, PostgreSQL, Oracle, and SQL
Server.

🔑 Key Concepts of the Relational Data Model

1. Relation (Table)
A relation, commonly known as a table, is a two-dimensional
structure consisting of rows and columns. Each table represents a
specific entity type, such as "Students" or "Orders".Wikipedia

2. Tuple (Row)

A tuple is a single row in a table, representing a unique record or

instance of the entity. For example, a row in the "Students" table
might contain the details of one student.

3. Attribute (Column)

An attribute is a column in a table, representing a specific property or

characteristic of the entity. For instance, attributes of the "Students"
table might include "StudentID", "Name", and
"Email".[Link]

4. Relation Schema

The relation schema defines the structure of a table, specifying its

name and the attributes it contains. For example, the schema for a
"Students" table might be:

scss

CopyEdit

Students(StudentID, Name, Email)

GeeksforGeeks

5. Degree and Cardinality

●​ Degree: The number of attributes (columns) in a relation.

●​ Cardinality: The number of tuples (rows) in a relation.
 6. Keys

●​ Super Key: A set of one or more attributes that can uniquely

identify a tuple in a relation.
●​ Candidate Key: A minimal super key; that is, a super key with
no redundant attributes.
●​ Primary Key: A chosen candidate key that uniquely identifies
each tuple in a relation.
●​ Foreign Key: An attribute in one table that references the
primary key of another table, establishing a relationship
between the two [Link]+[Link]+1

7. Integrity Constraints

●​ Domain Constraint: Ensures that the values of an attribute fall

within a specified domain.
●​ Entity Integrity: Ensures that the primary key of a relation is
unique and not null.
●​ Referential Integrity: Ensures that a foreign key value in one
relation matches a primary key value in another relation or is
null.

8. Relational Instance

A relational instance is a snapshot of the data in a relation at a

particular moment, consisting of a set of tuples.

9. Normalization

Normalization is the process of organizing data to minimize

redundancy and improve data integrity. It involves decomposing
tables into smaller, well-structured tables without losing information.

10. Relational Algebra

Relational algebra is a formal language comprising operations that
take one or two relations as input and produce a new relation as
output. It provides a theoretical foundation for query languages like
SQL.

✅ Advantages of the Relational Data Model

●​ Simplicity: The tabular structure is intuitive and easy to
understand.
●​ Data Integrity: Enforces data accuracy and consistency through
integrity constraints.
●​ Flexibility: Supports complex queries and data manipulation
using relational algebra and SQL.
●​ Scalability: Efficiently handles large volumes of data and
complex relationships.
●​ Security: Provides mechanisms to control access to data at
various levels.​

Domains, attributes, tuples, relations and their

characteristics,
In the Relational Data Model, data is structured into tables,
facilitating efficient storage, retrieval, and management.
Understanding the fundamental components—domains, attributes,
tuples, and relations—is crucial for designing and interacting with
relational databases.

📘 Core Concepts
1. Domain

A domain is the set of permissible values that an attribute can

assume. Each attribute in a relation is associated with a domain,
ensuring data integrity by restricting the type of data
[Link]+5GeeksforGeeks+5V
aia+5

●​ Examples:
○​ Age: Integers ranging from 0 to 120.
○​ Email: Strings following a valid email format.
○​ EnrollmentDate: Dates in the format YYYY-MM-DD.

Domains can be defined using built-in data types or custom-defined

types with specific constraints.

2. Attribute

An attribute represents a column in a relation (table) and denotes a

specific property of the entity modeled by the relation. Each attribute
has a unique name and is associated with a [Link]

●​ Examples:​

In a Student table: StudentID, Name, DateOfBirth.​

In a Course table: CourseID, Title, [Link]​

Attributes define the structure of a relation and play a vital role in

data retrieval and manipulation.

3. Tuple

A tuple is an ordered set of attribute values, representing a single

record or row in a relation. Each value in a tuple corresponds to an
attribute and adheres to its domain constraints.
 ●​ Example:​

○​ For a Student relation: (101, 'Alice Smith', '1999-05-15')

■​ StudentID: 101
■​ Name: 'Alice Smith'
■​ DateOfBirth: '1999-05-15'​

Tuples are the fundamental units of data in a relation, encapsulating

information about individual entities.

4. Relation

A relation is a table comprising a set of tuples that share the same

attributes. It represents a collection of related data entries and is
defined by a schema specifying its name and
[Link]+1Runestone Academy+1

●​ Properties:
○​ Name: Identifier for the relation (e.g., Student).
○​ Schema: Defines the structure, including attribute names
and domains.
○​ Body: Contains the actual data as a set of
[Link] Academy+[Link]+1​

Relations model real-world entities and their interrelationships,

forming the backbone of relational databases.

🔍 Characteristics of Relations
●​ Uniqueness: Each tuple in a relation is unique; duplicate rows
are not permitted.
●​ Atomicity: Each attribute value is atomic, meaning it cannot be
subdivided further.
 ●​ Order Irrelevance:
○​ The order of tuples (rows) is not significant.
○​ The order of attributes (columns) is also not significant.
●​ Integrity Constraints:​
Primary Key: A unique identifier for tuples within a relation.
○​ Foreign Key: An attribute that creates a link between two
relations.
○​ Domain Constraints: Ensure attribute values fall within
specified [Link]​

These characteristics ensure data consistency, integrity, and facilitate

efficient query processing in relational databases.

ConstraThe Entity Integrity

Constraint is a fundamental principle in relational database
management systems (RDBMS) that ensures each record
within a table is uniquely identifiable. This is achieved by
enforcing rules on the primary key of a [Link]

🔍 What is Entity Integrity?

Entity integrity mandates that every table must have a primary key,
and this key must be both unique and non-null. This constraint
guarantees that each row in a table represents a distinct entity,
preventing duplicate or ambiguous [Link] Open
Publishing+1Wikipedia+1

📌 Key Rules of Entity Integrity

1.​Non-Null Primary Key: The primary key field(s) cannot contain
null values. A null in the primary key would mean the absence
 of an identifier, making it impossible to distinguish that record
from others.
2.​Uniqueness: Each value of the primary key must be unique
across the table. This uniqueness ensures that no two rows
represent the same entity.​

🧾 Example

StudentI Name Email

D

101 Alice alice@exa

Smith [Link]

102 Bob bob@exa

Jones [Link]

Consider a Students table:​

Valid Entry: Inserting a new student with a unique and non-null
StudentID (e.g., 103) complies with entity integrity.​
 ●​ Invalid Entry: Attempting to insert a student with StudentID as
NULL or an existing StudentID (e.g., 101) violates entity
integrity constraints.​

🛡️ Importance of Entity Integrity

●​ Data Accuracy: Ensures that each record is distinct and
identifiable, reducing redundancy and confusion.
●​ Reliable Relationships: Facilitates the establishment of
relationships between tables through foreign keys, which
reference primary keys.
●​ Efficient Data Retrieval: Unique identifiers allow for quick and
precise data access and manipulation.

Referential integrity,
Referential Integrity is a fundamental concept in relational database
systems that ensures the consistency and validity of relationships
between tables. It mandates that a foreign key in one table must
either match a primary key in another table or be null. This constraint
maintains the logical connections between related data, preventing
anomalies and preserving data [Link]+1Scaler+1

🔗 What Is Referential Integrity?

In relational databases, tables often relate to one another. For
instance, an Orders table might reference a Customers table to
indicate which customer placed a particular order. Referential
integrity ensures that these relationships remain consistent:

●​ Foreign Key Constraint: A foreign key in the child table must

correspond to an existing primary key in the parent table or be
 null.​

●​ Consistency Enforcement: The database system enforces

this rule, preventing operations that would lead to invalid
[Link]​
ustomers:​

CustomerID Name

1 Alice
Johnson

2 Bob Smith

●​ ​

●​ Orders:​

OrderID Custome OrderDate

rID

101 1 2025-05-20

102 2 2025-05-21
 ●​ ​

Here, CustomerID in the Orders table is a foreign key referencing

the CustomerID in the Customers table. Referential integrity
ensures that every CustomerID in Orders exists in Customers.

⚠️ Violations of Referential Integrity

Violations occur when the foreign key in the child table references a
non-existent primary key in the parent table. For example:Monte
Carlo Data

●​ Inserting Invalid References: Adding an order with

CustomerID 3 when no such customer [Link]​

●​ Deleting Referenced Records: Removing a customer who

has existing orders without handling the dependent records.

Such actions can lead to orphaned records and inconsistent data,

compromising the integrity of the database.

🔄 Referential Actions on UPDATE and DELETE

To manage changes in the parent table that affect related records in
the child table, various referential actions can be defined:Wikipedia

●​ CASCADE: Automatically updates or deletes related records in

the child table when the parent record is updated or deleted.
●​ SET NULL: Sets the foreign key in the child table to null when
the corresponding parent record is deleted or updated.
●​ SET DEFAULT: Sets the foreign key in the child table to a
default value when the parent record is deleted or updated.
●​ RESTRICT / NO ACTION: Prevents the deletion or update of a
parent record if related records exist in the child table.
These actions help maintain referential integrity by defining how
changes in one table propagate to related tables.

✅ Importance of Referential Integrity

●​ Data Consistency: Ensures that relationships between tables
remain accurate and [Link]​

●​ Prevents Orphan Records: Avoids situations where child

records reference non-existent parent [Link]​

●​ Facilitates Data Integrity: Maintains the logical coherence of

the database, which is crucial for reliable data retrieval and
[Link]​

●​ Supports Database Normalization: Enables the division of

data into related tables without sacrificing data integrity.

Foreign keys,
🔑 Foreign Keys in Relational Databases
A foreign key is a key used to link two tables in a relational database.
It ensures referential integrity by enforcing a relationship between the
data in two tables.

📘 Definition
A foreign key is an attribute (or a set of attributes) in one table that
references the primary key in another table. The table with the
foreign key is called the child table, and the table it references is
called the parent table.
 🧾 Example
Parent Table: Customers

CustomerID Name

1 Alice

2 Bob

Child Table: Orders

OrderID CustomerID Product

101 1 Laptop

102 2 Keyboard

103 3 Mouse ❌(invalid if

CustomerID 3 doesn’t exist
in Customers)

Here, CustomerID in the Orders table is a foreign key referencing

CustomerID in the Customers table.

🔄 Referential Actions (Optional Clauses)

You can define behavior for updates or deletions in the parent table:
 ●​ ON DELETE CASCADE – deletes child rows if the parent row is
deleted.
●​ ON UPDATE CASCADE – updates foreign key values if the
parent key is updated.
●​ SET NULL – sets the foreign key to NULL if the parent row is
deleted.​

●​ RESTRICT or NO ACTION – prevents the action if foreign key

references exist.

✅ Why Foreign Keys Are Important

●​ Enforce data integrity and prevent orphan records.
●​ Establish logical relationships between tables.
●​ Help maintain normalized database structures.
●​ Prevent invalid data entries (e.g., referencing non-existent
records).

Relational database design using ER to relational

mapping.
📘 Relational Database Design Using ER-to-Relational Mapping
Converting an Entity-Relationship (ER) model into a
Relational Database Schema is a fundamental step in
database design. This process is known as ER-to-relational
mapping and involves systematically transforming entities,
relationships, and constraints into relational tables.

🧭 Steps in ER-to-Relational Mapping

 Step 1: Mapping of Regular (Strong) Entity Sets

●​ Each strong entity becomes a relation (table).

●​ Attributes become columns, and the primary key of
the entity becomes the primary key of the relation.​

ER Entity:
makefile

CopyEdit

Entity: Student

Attributes: StudentID (PK), Name, Age

Relational Table:
sql

CopyEdit

Student(StudentID PRIMARY KEY, Name, Age)

Step 2: Mapping of Weak Entity Sets

●​ Weak entities depend on a strong entity and do not

have a primary key.​
 ●​ The table includes:​

○​ Foreign key to the strong entity.​

○​ Discriminator/Partial key + foreign key =

composite primary key.​

ER Example:
vbnet

CopyEdit

Entity: Dependent (dependent of Employee)

Attributes: DependentName, Age

Relationship: EmployeeID (from Employee)

Relational Table:
sql

CopyEdit

Dependent(EmployeeID, DependentName, Age,

PRIMARY KEY(EmployeeID, DependentName),

FOREIGN KEY(EmployeeID) REFERENCES

Employee(EmployeeID))
 Step 3: Mapping of Binary 1:1 Relationships

●​ Choose one of the participating entities and add the

foreign key of the other.​

●​ May also merge if both are total participation.​

ER Example:
scss

CopyEdit

Employee --has--> ParkingSpot (1:1)

Relational Mapping:
sql

CopyEdit

Employee(EmployeeID PRIMARY KEY, Name, SpotID UNIQUE,

FOREIGN KEY(SpotID) REFERENCES

ParkingSpot(SpotID))

Step 4: Mapping of Binary 1:N Relationships

 ●​ Add the foreign key of the “one” side to the “many”
side.​

ER Example:
scss

CopyEdit

Department (1) --- (N) Employee

Relational Mapping:
sql

CopyEdit

Employee(EmployeeID PRIMARY KEY, Name, DeptID,

FOREIGN KEY(DeptID) REFERENCES

Department(DeptID))

Step 5: Mapping of Binary M:N Relationships

●​ Create a separate relation for the relationship.​

●​ Include foreign keys to both entities and any attributes

of the relationship.​

ER Example:
 scss

CopyEdit

Student (M) --- (N) Course

Relationship: Enrollment(DateEnrolled)

Relational Mapping:

sql

CopyEdit

Enrollment(StudentID, CourseID, DateEnrolled,

PRIMARY KEY(StudentID, CourseID),

FOREIGN KEY(StudentID) REFERENCES

Student(StudentID),

FOREIGN KEY(CourseID) REFERENCES

Course(CourseID))

Step 6: Mapping of Multivalued Attributes

●​ Create a new relation for the attribute.​

●​ Include the primary key of the original entity + the multivalued attribute.​

ER Example:

nginx

CopyEdit

Employee has multiple Skills

Relational Mapping:

sql

CopyEdit

Employee(EmployeeID PRIMARY KEY, Name)

Skill(EmployeeID, SkillName,

PRIMARY KEY(EmployeeID, SkillName),

FOREIGN KEY(EmployeeID) REFERENCES

Employee(EmployeeID))

Step 7: Mapping of n-ary Relationships (n > 2)

●​ Create a separate table with foreign keys for each participating entity.​

ER Example:

vbnet

CopyEdit

Project -- assigned to --> Employee with Role

Relational Mapping:

sql

CopyEdit

Assignment(EmployeeID, ProjectID, Role,

 PRIMARY KEY(EmployeeID, ProjectID),

FOREIGN KEY(EmployeeID) REFERENCES

Employee(EmployeeID),

FOREIGN KEY(ProjectID) REFERENCES

Project(ProjectID))

🎯 Summary Table
ER Component Relational Mapping

Strong Entity One table with PK

Weak Entity Table with FK + partial key as

PK

1:1 Relationship Add FK to one side

1:N Relationship Add FK to “many” side

M:N Relationship Create new table with both

FKs

Multivalued New table with PK + attribute

Attribute

n-ary Relationship New table with all FKs

 UNIT 3
Design guidelines for Relation Schemas.
Design Guidelines for Relation Schemas in Relational Databases

Designing relation schemas carefully is crucial to ensure a

high-quality, efficient, and consistent database. Good design
avoids redundancy, ensures data integrity, and supports efficient
operations.

Here are the key guidelines:

🔹 1. Avoid Redundancy (Eliminate Repetition)

●​ Redundant data leads to waste of space and potential
update anomalies (inconsistencies when data is inserted,
updated, or deleted).
●​ Use normalization to split tables when necessary.

Example (bad design):

sql
CopyEdit
Employee(EmpID, Name, DeptName, DeptLocation)
 ●​ If many employees are in the same department, DeptName
and DeptLocation are repeated.​

●​ Better design:
sql
CopyEdit
Employee(EmpID, Name, DeptID)
Department(DeptID, DeptName, DeptLocation)

🔹 2. Ensure Information Preservation

●​ Ensure no information is lost when relations are
decomposed (split into smaller tables).
●​ All original data should be reconstructible via JOIN
operations.​
✔ A decomposition is lossless if you can combine the
smaller tables to get back the original one without
ambiguity.

🔹 3. Support Dependency Preservation

●​ Functional dependencies (FDs) should still be enforceable
without needing joins.
●​ This allows easier constraint enforcement and integrity
checking.

If:

●​ A → B is a dependency in the original relation,​

 ●​ Make sure it's present in at least one of the decomposed
relations.​

🔹 4. Minimize Null Values

●​ Design tables to reduce the number of attributes that
may be null, since nulls can lead to:​

○​ Wasted space
○​ Complicated queries
○​ Ambiguities in logic (null ≠ null)

Design Tip: Split optional data into separate relations if needed.

🔹 5. Use Meaningful Attribute Names

●​ Attribute names should be:
○​ Clear and descriptive
○​ Avoid abbreviations unless universally understood​
Consistent in style (e.g., CustomerID, not CID)​

○​ 🔹 6. Use Proper Keys (Primary and Foreign)

●​ Ensure every table has a primary key.
●​ Use foreign keys to define relationships between tables.
●​ Avoid using composite primary keys unless necessary.

🔹 7. Normalize to the Appropriate Normal Form

●​ Use Normalization (1NF, 2NF, 3NF, BCNF) to ensure
logical consistency.
 ●​ Generally, aim for 3NF or BCNF unless denormalization is
needed for performance.

🔹 8. Ensure Atomicity of Attributes

●​ Each attribute should hold a single value, not multiple or
composite values.​

❌ Bad:
sql
CopyEdit
PhoneNumbers = "1234567890, 9876543210"

✔ Better:
sql
CopyEdit
Phone(EmpID, PhoneNumber)

🧠 Summary Table
Guideline Purpose
Eliminate Save space, avoid
redundancy anomalies
Preserve No data loss during
information decomposition
Preserve Keep constraints
dependencies enforceable without joins
Minimize nulls Improve clarity and reduce
storage issues
Clear attribute Improve readability and
naming maintainability
Use proper Ensure record uniqueness
keys and data linkage
Normalize Structure data efficiently and
logically
Use atomic Avoid multi-valued or
attributes composite fields

Functional dependencies,
🔐 Functional Dependencies (FDs) in Relational Databases)
A functional dependency (FD) is a constraint between two sets
of attributes in a relation. It is fundamental in normalization and
helps in designing well-structured relational databases.

📘 Definition
A functional dependency X → Y between two attribute sets X
and Y in a relation R means:

If two tuples (rows) of R agree on the attributes in X,

they must agree on the attributes in Y.
In simpler terms:​
If you know the value of X, you can uniquely determine the
value of Y.

🧾 Example
Let’s say we have a relation Student(StudentID, Name,
Department):

●​ StudentID → Name means:​

If two students have the same StudentID, they must have
the same Name.​

●​ StudentID → Department means:​

A given StudentID determines the department they
belong to.​

🧩 Types of Functional Dependencies

Type Description

Trivial FD Y ⊆ X (e.g., StudentID, Name

→ Name)

Non-Trivial FD Y is not a subset of X (e.g.,

StudentID → Name)

Fully Functional X → Y, but no subset of X alone

determines Y
Partial A part of a composite key
Dependency determines Y

Transitive X → Y and Y → Z implies X → Z

Dependency (not direct)

📐 Why Are Functional Dependencies Important?

1.​Normalization:​
Used to remove redundancy and anomalies by identifying
normal forms (1NF, 2NF, 3NF, BCNF, etc.).​

2.​Integrity Constraints:​
Enforce rules in data relationships.​

3.​Schema Refinement:​
Helps decompose large, complex tables into smaller,
well-structured ones.​

🎓 Formal Properties (Armstrong’s Axioms)

These rules help derive all possible FDs from a given set:

●​ Reflexivity: If Y ⊆ X, then X → Y​

●​ Augmentation: If X → Y, then XZ → YZ​

●​ Transitivity: If X → Y and Y → Z, then X → Z​

Additional useful rules:

 ●​ Union: If X → Y and X → Z, then X → YZ​

●​ Decomposition: If X → YZ, then X → Y and X → Z​

●​ Pseudotransitivity: If X → Y and YZ → W, then XZ → W​

🛠️ FD Example in TableFDs:
●​ StudentID → Name, Department ✅​

●​ Name →
Studen
tID
Na
me
Departm
ent
Department ❌
(Alice is in CS, but maybe
another 101 Alic CS Alice could be in EE)
e

🚧 Violations & 102

103
Bob EE

Alic CS
Problems

If FDs are not e respected, it can

cause:

●​ Update Anomalies
●​ Insert Anomalies
●​ Delete Anomalies

Would you like to see how functional dependencies are used in

normalization steps like 2NF, 3NF, or BCNF?
Normalization of relation schema,

📘 Normalization of Relation Schema

Normalization is the process of organizing the attributes and tables
of a relational database to reduce data redundancy and improve data
integrity. It involves decomposing larger tables into smaller,
well-structured tables using functional dependencies as a guide.

🎯 Objectives of Normalization
●​ Eliminate redundant data
●​ Avoid insertion, update, and deletion anomalies
●​ Ensure data consistency
●​ Support data integrity

🧱 Normal Forms (NF)

Each normal form builds upon the previous one and applies specific
rules:

🔹 1NF (First Normal Form) – Atomicity

●​ Rule: All attributes must contain atomic (indivisible) values; no
repeating groups or arrays.
●​ Fix: Flatten multivalued fields into separate rows or relations.​

●​ Bad Example:
text
CopyEdit
Student(StudentID, Name, Courses)
Student(1, Alice, {Math, CS})

1NF-compliant:
text
CopyEdit
Student(StudentID, Name, Course)
Student(1, Alice, Math)
Student(1, Alice, CS)

🔹 2NF (Second Normal Form) – Remove Partial Dependency

●​ Rule: Must be in 1NF, and no partial dependency (i.e., no
non-prime attribute depends only on part of a composite key).​

●​ Fix: Decompose into smaller tables.​

Bad Example:
text
CopyEdit
Enrollment(StudentID, CourseID, StudentName)
-- PK = (StudentID, CourseID)
-- StudentName depends only on StudentID

2NF-compliant:
text
CopyEdit
Student(StudentID, StudentName)
Enrollment(StudentID, CourseID)

🔹 3NF (Third Normal Form) – Remove Transitive Dependency

●​ Rule: Must be in 2NF, and no transitive dependency (non-prime
attribute depending on another non-prime attribute).​

●​ Fix: Remove derived or transitively dependent fields.​

Bad Example:

text
CopyEdit
Employee(EmpID, DeptID, DeptName)
-- DeptName depends on DeptID, not on EmpID

3NF-compliant:

text
CopyEdit
Employee(EmpID, DeptID)
Department(DeptID, DeptName)

🔹 BCNF (Boyce-Codd Normal Form) – Stronger 3NF

●​ Rule: Every determinant must be a candidate key.
●​ Fixes certain anomalies not handled by 3NF.​

Example of violation:
text
CopyEdit
Professor(Course, Professor, Dept)
-- Course → Professor (but Course is not a candidate key
if Professor teaches multiple courses)

🧠 Summary Table of Normal Forms

Normal Requirements Fixes
Form

1NF Atomic values, no repeating Multivalued fields

groups

2NF 1NF + no partial dependencies Partial dependency

3NF 2NF + no transitive Transitive

dependencies dependency

BCNF 3NF + every determinant is a All redundancy due

candidate key to FDs

Without With Normalization

Normalization

Data redundancy Reduced

redundancy
 Insertion/update
anomalies
Minimal anomalies
🛠️ Why Normalize
Inconsistent data Consistent,
accurate data

Difficult Easier schema

maintenance maintenance

Normal forms based

on primary key (1NF, 2NF, 3NF) lossless join and dependency
decomposition.

Certainly! Let's delve deeper into the concepts of First Normal Form
(1NF), Second Normal Form (2NF), and Third Normal Form (3NF),
focusing on their relationship with primary keys, and discuss the
principles of lossless join and dependency preservation in database
normalization.
🔹 First Normal Form (1NF): Ensuring Atomicity
Definition: A relation is in 1NF if:

●​ All attributes contain only atomic (indivisible) values.

●​ Each record is unique; no duplicate rows.
●​ Each attribute has a unique name.
●​ The order of data storage does not affect the data's integrity.

Example:

StudentID Name Courses

1 Alice
Math,Physics

2 Bob Chemistry

Consider a table:Here, the 'Courses' attribute contains multiple

values, violating 1NF.

Conversion to 1NF:

Student Name Course

ID

1 Alice Math

1 Alice Physics

2 Bob Chemistry
Now, each attribute contains atomic values, satisfying 1NF.

🔹 Second Normal Form (2NF): Eliminating Partial

Dependencies
Definition: A relation is in 2NF if:

●​ It is in 1NF.
●​ Every non-prime attribute is fully functionally dependent on the
entire primary key.

Partial Dependency: Occurs when a non-prime attribute is

functionally dependent on part of a composite primary key.

Example:

Consider a table:

Student Course StudentN CourseN

ID ID ame ame

1 101 Alice Math

2 102 Bob Physics

Assuming the primary key is a combination of StudentID and

CourseID.

●​ StudentName depends only on StudentID.

●​ CourseName depends only on CourseID.

These are partial dependencies.

Conversion to 2NF:
●​ Student table:
StudentID StudentName

1 Alice

2 Bob

●​ ​

●​ Course table:
Course CourseN
ID ame

101 Math

102 Physics

●​ ​

●​ Enrollment table:​

Student Course
ID ID

1 101

2 102

Now, all non-prime attributes are fully functionally dependent on the

entire primary key in their respective tables.
🔹 Third Normal Form (3NF): Removing Transitive
Dependencies
Definition: A relation is in 3NF if:

●​ It is in 2NF.​

●​ There are no transitive dependencies for non-prime attributes.​

Transitive Dependency: Occurs when a non-prime attribute depends

on another non-prime attribute, which in turn depends on the primary
key.

Example:

Consider a table:

Student StudentN Departm HOD

ID ame ent

1 Alice Physics Dr.

Smith

2 Bob Chemistr Dr.

y Jones

Assuming StudentID is the primary key.

●​ Department depends on StudentID.​

●​ HOD depends on Department.​

Thus, HOD is transitively dependent on StudentID.

Conversion to 3NF:

●​ Student table:​

Student StudentName Department

ID

1 Alice Physics

2 Bob Chemistry

●​ Department table:​
​

●​
Department HOD

Physics Dr. Smith

Chemistry Dr. Jones

Now, transitive dependencies are eliminated.

🔹 Lossless Join Decomposition

Definition: A decomposition is lossless if the original relation can be
perfectly reconstructed by joining the decomposed relations.

Condition: For a decomposition of a relation R into R1 and R2, the

join is lossless if:

●​ The common attributes of R1 and R2 form a superkey in at

least one of the relations.
Example:

Given a relation R(A, B, C) with functional dependency A → B.

Decompose R into:

●​ R1(A, B)​

●​ R2(A, C)

Since A is a superkey in R1 (due to A → B), the decomposition is

lossless.

🔹 Dependency Preservation
Definition: A decomposition is dependency preserving if all functional
dependencies can be enforced by enforcing them on the individual
decomposed relations without the need to join them.

Importance: Ensures that the integrity constraints of the database

are maintained without complex checks across multiple relations.

Example:

Given a relation R(A, B, C) with functional dependencies:

●​ A → B
●​ B → C

Decompose R into:

●​ R1(A, B)
●​ R2(B, C)

Here, A → B is preserved in R1, and B → C is preserved in R2.

Thus, the decomposition is dependency preserving.
🔹 Summary
Normal Key Focus Eliminates
Form

1NF Atomicity Repeating groups,

multivalued attributes

2NF Full functional Partial dependencies

dependency

3NF Transitive Transitive dependencies

dependency

Good Decomposition:

●​ Lossless Join: Ensures no data is lost during decomposition.​

●​ Dependency Preservation: Ensures all functional dependencies

are maintained without complex joins.​

Understanding and applying these normalization principles are

crucial for designing efficient and reliable relational databases.