Database Management System

(2CS505CC23)

Disaster Relief & Resource Logistics Hub

Semester - IV
B. Tech. CSE

Prepared By:
24BCE179 & 24BCE175
 Introduction:
The Disaster Relief & Resource Logistics Hub is a highly structured,
centralized database designed to act as the digital "brain" for a Global Crisis
Response Agency during severe natural disasters. In the chaotic aftermath of
an emergency, this system prevents logistical failure by precisely
coordinating four main pillars: specialized personnel, physical relief bases,
aerial transport fleets, and vital cargo inventory. By dynamically matching
specific needs—such as dispatching a medical officer with the right trauma
specialty to a shelter with available capacity, or calculating the exact cargo
weight for a specific helicopter model—the database ensures life-saving
resources are deployed efficiently. Ultimately, it utilizes advanced relational
database modeling to log every "Active Mission," providing organizations
like international NGOs or government response forces with the real-time
data needed to turn a chaotic crisis into a highly organized rescue operation.

 Entities:
This project designs a highly structured relational database architecture to
manage the Global Crisis Response Agency's complex disaster relief
logistics. Real-world operational requirements are mapped using an
Enhanced Entity-Relationship (EER) Diagram, featuring advanced
conceptual techniques like Supertype/Subtype specialization, Weak Entities,
Ternary Relationships, and Aggregation.

I. Crisis_Personnel (Core Supertype Entity): This central hub

tracks all deployed workforce members. It stores comprehensive
personnel data, including full names, contact information, spoken
languages, and dates of birth. It also utilizes a recursive "Supervises"
relationship to establish a clear internal chain of command among the
staff.

II. Medical_Officer (Specialized Subtype):A specific category of

Crisis Personnel. It inherits all base attributes from the main personnel
table while storing highly specific medical details, such as trauma
specialties and medical license numbers.
 III. Field_Engineer (Specialized Subtype): The second category of
Crisis Personnel. It inherits base workforce data while tracking
specialized engineering certifications and heavy machinery clearances
required for setting up and maintaining physical base operations.

IV. Next_of_Kin (Weak Support Entity): This table tracks emergency

contacts for deployed personnel. Because a dependent's record is entirely
reliant on an active worker's profile, it is modeled as a Weak Entity with
total participation, utilizing a partial key (Kin_Name) alongside the
worker's ID to identify relatives.

V. Relief_Base (Infrastructure Entity): This entity monitors the

capacity, location, and leadership of physical safe zones. It tracks
essential data such as the base's full address, available facilities,
occupancy metrics, and the designated base commander's radio
communication frequencies.

VI. Aero_Fleet (Logistics & Transport Entity): Used to manage

aerial transport logistics, this entity tracks which specific helicopter
models are stationed at various relief bases. It also logs the specific pilots
assigned to operate those vehicles based on strict agency certifications.

VII. Relief_Cargo (Resource Entity): This entity catalogs the physical

supplies required in the field. It tracks specific cargo IDs, broad supply
categories (e.g., medicine, food, tents), and overall weight tonnages to
accurately manage inventory load and distribution.

VIII. Active_Mission (Aggregated Transaction Entity): This serves

as the central operational ledger for the agency. It uses Aggregation to
wrap a ternary relationship—connecting Personnel, a Relief Base, and
Relief Cargo—into a single loggable event, tracking the exact date and
details of every deployment mission.
 Entities:

1. Crisis_Personnel
 Personnel_ID (Primary Key)
 Full_Name (First_Name, Last_Name)
 Spoken_Languages (Multivalued)
 DOB
 Age (Derived Attribute)

Attribute Data Type Key / Notes

Personnel_ID VARCHAR2(15) Primary Key
First_Name VARCHAR2(50) Part of composite Full_Name
Last_Name VARCHAR2(50) Part of composite Full_Name
Spoken_Languages VARCHAR2(100) Multivalued (To be normalized
later)
DOB DATE
Age NUMBER(3) Derived Attribute (Max 3 digits)

2. Medical_Officer
 Personnel_ID (Primary Key / Foreign Key)
 Medical_License_No
 Trauma_Specialty

Attribute Data Type Key / Notes

Personnel_ID VARCHAR2(15) Primary Key / Foreign
Key
Medical_License_N VARCHAR2(30)
o
Trauma_Specialty VARCHAR2(50)

3. Field_Engineering
 Personnel_ID (Primary Key / Foreign Key)
 Engineering_Cert_Code
 Machinery_Clearance

Attribute Data Type Key / Notes

Personnel_ID VARCHAR2(15 Primary Key / Foreign Key
)
Engineering_Cert_Code VARCHAR2(30
 )
Machinery_Clearance VARCHAR2(50
)

4. Next_of_Kin
 Kin_Name (Partial Key)
 Personnel_ID (Foreign Key)
 Relation_Type

Attribute Data Type Key / Notes

Kin_Name VARCHAR2(50) Partial Key
Personnel_ID VARCHAR2(15) Foreign Key
Relation_Typ VARCHAR2(30)
e

5. Relief_Base
 Base_ID (Primary Key)
 Base_Address (City, State)
 Available_Facilities (Multivalued)
 Max_Capacity
 Active_Occupancy
 Available_Space (Derived Attribute)
 Base_Commander_ID (Foreign Key)
 Commander_Radio_Freq

Attribute Data Type Key / Notes

Base_ID VARCHAR2(15) Primary Key
City VARCHAR2(50) Part of composite Base_Address
State VARCHAR2(50) Part of composite Base_Address
Available_Facilities VARCHAR2(200) Multivalued (To be normalized
later)
Max_Capacity NUMBER(6) (Allows up to 999,999 people)
Active_Occupancy NUMBER(6)
Available_Space NUMBER(6) Derived Attribute
Base_Commander_ID VARCHAR2(15) Foreign Key
Commander_Radio_Fre VARCHAR2(20)
q

6. Aero_Fleet
 Helicopter_Model (Primary Key)
 Base_ID (Primary Key / Foreign Key)
 Assigned_Pilot
Attribute Data Type Key / Notes
Helicopter_Model VARCHAR2(50 Primary Key
)
Base_ID VARCHAR2(15 Primary Key / Foreign Key
)
Assigned_Pilot VARCHAR2(50
)

7. Relief_Cargo
 Cargo_ID (Primary Key)
 Supply_Category
 Weight_Tons

Attribute Data Type Key / Notes

Cargo_ID VARCHAR2(20) Primary Key
Supply_Category VARCHAR2(50)
Weight_Tons NUMBER(6,2) Allows 4 digits before decimal, 2 after (e.g.,
1500.50 tons)

8. Active_Mission
 Personnel_ID (Primary Key / Foreign Key)
 Base_ID (Primary Key / Foreign Key)
 Cargo_ID (Primary Key / Foreign Key)
 Mission_Date
 Personnel_Blood_Type

Attribute Data Type Key / Notes

Personnel_ID VARCHAR2(15) Primary Key / Foreign Key
Base_ID VARCHAR2(15) Primary Key / Foreign Key
Cargo_ID VARCHAR2(20) Primary Key / Foreign Key
Mission_Date DATE
Personnel_Blood_Type VARCHAR2(5) (To be normalized later, e.g., 'AB+')
 Cardinality:

1. Crisis_Personnel to Next_of_Kin
 Relationship: Notifies
 Cardinality: 1:N (One-to-Many)
 Reading: 1 Crisis_Personnel member can have N (Many) Next_of_Kin
records, but each kin record belongs to only 1 personnel member.

2. Crisis_Personnel to Crisis_Personnel
 Relationship: Supervises (Recursive)
 Cardinality: 1:N (One-to-Many)
 Reading: 1 Senior Personnel member supervises N (Many) Subordinate
Personnel members.

3. Relief_Base to Aero_Fleet
 Relationship: Stations
 Cardinality: 1:N (One-to-Many)
 Reading: 1 Relief_Base stations N (Many) Aero_Fleet vehicles, but each
vehicle is assigned to only 1 specific base.

4. Relief_Base to Active_Mission
 Relationship: Deploys (via Aggregation)
 Cardinality: 1:N (One-to-Many)
 Reading: 1 Relief_Base can be the starting point for N (Many)
Active_Missions.

5. Crisis_Personnel to Active_Mission
 Relationship: Assigned (via Aggregation)
 Cardinality: 1:N (One-to-Many)
 Reading: 1 Crisis_Personnel member can be assigned to N (Many) different
Active_Missions over time.

6. Relief_Cargo to Active_Mission
 Relationship: Includes (via Aggregation)
 Cardinality: 1:N (One-to-Many)
  Reading: 1 specific Relief_Cargo batch is deployed for N (Many) missions
(or 1 specific mission depending on if the batch is unique).

7. Crisis_Personnel to Medical_Officer / Field_Engineering

 Relationship: IS-A (Specialization)
 Cardinality: 1:1 (One-to-One)
 Reading: 1 Crisis_Personnel record corresponds to exactly 1 specialized
Subtype record (either a Medic or an Engineer).
Source Entity Relationship Target Entity Cardinality Reading /
Name Description
Crisis_Personnel Notifies Next_of_Kin 1:N 1 Crisis_Personnel
member can have N
(Many) Next_of_Kin
records, but each kin
record belongs to only
1 personnel member.
Crisis_Personnel Supervises Crisis_Personnel 1:N 1 Senior Personnel
(Recursive) member supervises N
(Many) Subordinate
Personnel members.
Relief_Base Stations Aero_Fleet 1:N 1 Relief_Base stations
N (Many) Aero_Fleet
vehicles, but each
vehicle is assigned to
only 1 specific base.
Relief_Base Deploys (via Active_Mission 1:N 1 Relief_Base can be
Aggregation) the starting point for N
(Many)
Active_Missions.
Crisis_Personnel Assigned (via Active_Mission 1:N 1 Crisis_Personnel
Aggregation) member can be
assigned to N (Many)
different
Active_Missions over
time.
Relief_Cargo Includes (via Active_Mission 1:N 1 specific
Aggregation) Relief_Cargo batch is
deployed for N (Many)
missions (or 1 specific
mission depending on
if the batch is unique).
Crisis_Personnel IS-A Medical_Officer / 1:1 1 Crisis_Personnel
(Specialization) Field_Engineering record corresponds to
exactly 1 specialized
Subtype record (either
a Medic or an
Engineer).
Diagram:

 Here is the SQL codde that How we can make the Relational Schemas.
 Here are the Dummy data inserted into the table formatted and the table output is here:
 If we do not do the Normalization following anomalies can cause problem to the Database:

1. The Update Anomaly (Data Inconsistency)

The Problem: When data is redundant, updating a single fact requires updating multiple
rows. If an update is partially missed, the database contains conflicting information.
Example Prevented: If Commander Vikram changes his radio frequency, an unnormalized
database would require updating his frequency on every single mission row he is
associated with. If even one row is missed, the dispatchers have two conflicting
frequencies for the same commander. By moving this to the Base_Commanders table, we
only have to update it in one single cell.

2. The Insertion Anomaly (Blocked Data Entry)

 The Problem: An insertion anomaly occurs when the database prevents you from adding
a new, valid record because you lack data for unrelated fields that share the same table's
Primary Key.
Example Prevented: If the agency hires a new Medical Officer, Dr. Patel, but she has not
yet been assigned to an Active_Mission, an unnormalized database would reject her
profile because the Mission_Date or Base_ID keys would be blank. By separating the
entities, we can now insert Dr. Patel directly into the Crisis_Personnel master list
immediately upon hiring.

3. The Deletion Anomaly (Accidental Data Loss)

The Problem: This occurs when deleting one piece of target data accidentally erases
completely unrelated data that was trapped in the same row.
Example Prevented: If the Mumbai Relief Base is temporarily shut down, deleting that
base from an unnormalized table might accidentally erase the only record of a specific
engineer's Machinery Clearances, simply because that engineer was stationed there. By
normalizing the tables, deleting the Mumbai base from Relief_Base leaves the
Engineer_Clearances table completely untouched and safe.

 Normalization:

1. First Normal Form (1NF)

Definition: A table is in 1NF if all attributes are atomic (indivisible) and there
are no repeating groups or multi-valued attributes.

 Violation 1 (Composite Attributes): Full_Name (First, Last),

Base_Address (City, State), and Contact_Data (Mobile_No, Email) must be
broken down into their individual atomic components.
  Violation 2 (Multi-valued Attributes): Spoken_Languages and
Available_Facilities contain multiple values in a single cell.

 Violation 3 (Derived Attributes): Age and Available_Space are calculated

values and should not be stored in a normalized table.

Correction: We split composite attributes into individual columns, remove derived

attributes, and move multi-valued attributes into separate linking tables.

 Crisis_Personnel: (Personnel_ID [PK], First_Name, Last_Name, DOB,

Mobile_No, Email)
 Personnel_Languages: (Personnel_ID [FK], Language)
 Relief_Base: (Base_ID [PK], City, State, Max_Capacity, Active_Occupancy,
Base_Commander_ID, Commander_Radio_Freq)
 Base_Facilities: (Base_ID [FK], Facility_Name)

Table Name Attribute Key / Notes

Crisis_Personnel Personnel_ID Primary Key
First_Name
Last_Name
DOB
Mobile_No
Email
Personnel_Languages Personnel_ID Primary Key / Foreign Key (Links to Crisis_Personnel)
(New in 1NF) Language Primary Key
Relief_Base Base_ID Primary Key
City
State
Max_Capacity
Active_Occupancy
Base_Commander_ID Foreign Key (Links to Crisis_Personnel)
Commander_Radio_Freq
Base_Facilities Base_ID Primary Key / Foreign Key (Links to Relief_Base)
(New in 1NF) Facility_Name Primary Key

2. Second Normal Form (2NF)

Definition: A table is in 2NF if it is in 1NF and every non-key attribute is fully

functionally dependent on the entire Primary Key (no partial dependencies).

 Violation: In the Active_Mission table, the Primary Key is composite:

(Personnel_ID, Base_ID, Cargo_ID). However, Personnel_Blood_Type
depends only on the Personnel_ID (a partial dependency), not the Base or
Cargo.

Correction: Move the blood type attribute to the Crisis_Personnel table, where it
depends entirely on the single Personnel_ID.
  Crisis_Personnel: (Personnel_ID [PK], First_Name, Last_Name, DOB,
Mobile_No, Email, Personnel_Blood_Type)
 Active_Mission: (Personnel_ID [PK/FK], Base_ID [PK/FK], Cargo_ID
[PK/FK], Mission_Date)

Table Name Attribute Key / Notes

Crisis_Personnel Personnel_ID Primary Key
First_Name
Last_Name
DOB
Mobile_No
Email
Personnel_Blood_Type (Moved here in 2NF)
Active_Mission Personnel_ID Primary Key / Foreign Key
Base_ID Primary Key / Foreign Key
Cargo_ID Primary Key / Foreign Key
Mission_Date Depends on the full composite key

3. Third Normal Form (3NF)

Definition: A table is in 3NF if it is in 2NF and contains no transitive

dependencies (non-key attributes depending on other non-key attributes).

 Violation: In the Relief_Base table, the Commander_Radio_Freq depends

on the Base_Commander_ID, which in turn depends on the Base_ID. This
is a transitive dependency (a chain reaction), which violates 3NF.

Correction: Remove the radio frequency from the Base table and create a separate
Base_Commanders table (acting as a new subtype of Personnel) to store their
specific communication data.

 Relief_Base: (Base_ID [PK], City, State, Max_Capacity,

Active_Occupancy, Base_Commander_ID [FK])
 Base_Commanders: (Base_Commander_ID [PK/FK],
Commander_Radio_Freq)

Table Name Attribute Key / Notes

Relief_Base Base_ID Primary Key
City
State
Max_Capacity
Active_Occupancy
Base_Commander_ID Foreign Key (Radio Freq removed in 3NF)
Base_Commanders Base_Commander_ID Primary Key / Foreign Key (Links to
Crisis_Personnel)
 (NEW in 3NF) Commander_Radio_Freq

4. Boyce-Codd Normal Form (BCNF)

Definition: A table is in BCNF if for every functional dependency X => Y, X

is a candidate key.

 Violation: Aero_Fleet table, the Primary Key is composite: (Base_ID,

Helicopter_Model). However, the agency rule states that an Assigned_Pilot
is only certified for one specific Helicopter_Model. Therefore, the
dependency is Assigned_Pilot => Helicopter_Model. Since the Pilot is
dictating the Helicopter Model, but the Pilot is not a candidate key for this
table, it violates BCNF.

Correction: Decompose the table to separate the pilot's vehicle certification from
the base logistics.

 Pilot_Certifications: (Assigned_Pilot [PK], Helicopter_Model)

 Base_Assignments: (Base_ID [PK/FK], Assigned_Pilot [PK/FK])

Table Name Attribute Key / Notes

Pilot_Certifications Assigned_Pilot Primary Key
(NEW: BCNF Split) Helicopter_Model
Base_Assignments Base_ID Primary Key / Foreign Key (Links to Relief_Base)
(NEW: BCNF Split) Assigned_Pilot Primary Key / Foreign Key (Links to
Pilot_Certifications)
 Here is the Final Schema after the Normalization process:

Table Name Attribute Key / Notes

1. Crisis_Personnel Personnel_ID Primary Key
(Flattened in 1NF. First_Name
Blood Type added in 2NF) Last_Name
DOB
Mobile_No
Email
Personnel_Blood_Type
2. Personnel_Languages Personnel_ID Primary Key / Foreign Key (Links to
Personnel)
(Created in 1NF) Language Primary Key
3. Medical_Officer Personnel_ID Primary Key / Foreign Key
Medical_License_No
Trauma_Specialty
4. Field_Engineering Personnel_ID Primary Key / Foreign Key
Engineering_Cert_Code
5. Engineer_Clearances Personnel_ID Primary Key / Foreign Key (Links to
Engineering)
(Created in 1NF) Machinery_Type Primary Key
6. Next_of_Kin Kin_Name Partial Primary Key
Personnel_ID Primary Key / Foreign Key
Relation_Type
7. Relief_Base Base_ID Primary Key
(Flattened in 1NF. City
Radio Freq removed in State
3NF)
Max_Capacity
Active_Occupancy
Base_Commander_ID Foreign Key (Links to Base_Commanders)
8. Base_Facilities Base_ID Primary Key / Foreign Key (Links to Base)
(Created in 1NF) Facility_Name Primary Key
9. Base_Commanders Base_Commander_ID Primary Key / Foreign Key (Links to
Personnel)
(Created in 3NF) Commander_Radio_Freq
10. Pilot_Certifications Assigned_Pilot Primary Key
(Created in BCNF) Helicopter_Model
11. Base_Assignments Base_ID Primary Key / Foreign Key (Links to Base)
(Created in BCNF) Assigned_Pilot Primary Key / Foreign Key (Links to
Certifications)
12. Relief_Cargo Cargo_ID Primary Key
Supply_Category
Weight_Tons
13. Active_Mission Personnel_ID Primary Key / Foreign Key
(Blood Type removed in Base_ID Primary Key / Foreign Key
2NF)
Cargo_ID Primary Key / Foreign Key
Mission_Date

Physical Database Schema Diagram: