📑 Chapters

📚 DBMS Unit 2

Functional Dependencies • Normalization • Security • Control Structures

Compiled & Formatted by Ankush Raj

📘 Chapter 2.1 — Functional Dependencies & Normalization
1. Functional Dependency (FD)
Definition: A functional dependency is a relationship where one attribute uniquely determines another attribute in a relation.
Example:
Roll_No → Name
(If we know Roll_No, we know Name)
Types of FDs

1. Trivial FD

FD A → B is trivial if B is already part of A.
Example: {Roll, Name} → Name

2. Non-Trivial FD

FD A → B is non-trivial if B is not part of A.
Example: Roll → Dept

3. Full Functional Dependency (FFD)

If non-key attribute depends on entire composite key, not just part.
Example: (Roll, Subject) → Marks
Marks depends on both Roll AND Subject.

4. Partial Dependency

When non-key attribute depends on part of composite key, not whole.
Example: Roll → Student_Name
Removed in: 2NF

5. Transitive Dependency (TD)

If A → B and B → C then A → C
Example: Student_ID → Dept, Dept → HOD
Therefore Student_ID → HOD
Removed in: 3NF

6. Multi-Valued Dependency (MVD)

One attribute determines multiple independent values of another.
Notation: A →→ B
Example: Student(ID) →→ Phone_Number

7. Join Dependency (JD)

A relation can be decomposed into multiple tables and re-joined without loss.
Example: R(ABCD) into AB, BC, CD and rejoined back.
2. Decomposition
Definition: Dividing a large relation into smaller relations.

Lossless Decomposition: No information lost. Natural join gives original table.

Lossy Decomposition: Causes missing/extra tuples. Undesirable.

3. Normalization
Definition: Process of organizing data to remove redundancy, avoid anomalies, and improve consistency.
Problems Removed
• Insertion anomaly
• Update anomaly
• Deletion anomaly
Normal Forms

1. First Normal Form (1NF)

• All values are atomic (no multivalued fields)
• No repeating groups
Example (Not 1NF): Phone = {9876, 2345}

2. Second Normal Form (2NF)

• Already in 1NF
• No partial dependency
Used for: Tables with composite primary keys

3. Third Normal Form (3NF)

• Already in 2NF
• No transitive dependency

4. BCNF (Boyce-Codd Normal Form)

For every FD A → B, A must be a super key.
More strict than 3NF.

5. Denormalization

Intentional introduction of redundancy to improve read performance.
Used in: Data Warehousing, Reporting, Analytics
📘 Chapter 2.2 — Database Security
1. Database Security
Definition: Protecting database from unauthorized access, misuse, data corruption, and loss.
2. Threats to Database
1. Unauthorized Access: Hackers/users accessing restricted data

2. SQL Injection: Malicious SQL inserted into input fields

3. Data Leakage: Sensitive data exposed unintentionally

4. Malware & Viruses: Entering through applications

5. Privilege Misuse: Employees accessing more data than allowed

6. System Failure: Server crashes causing data loss

7. Data Corruption: Due to bugs or hardware failure
3. Counter Measures (Security Techniques)
1. Authentication: Username, password, biometrics, OTP

2. Authorization: Roles and privileges like SELECT, UPDATE

3. Encryption: Converting data into unreadable form

4. Backup & Recovery: Regular backups to avoid data loss

5. Firewalls: Blocking unauthorized network traffic

6. Anti-malware tools: Protect systems from virus attacks

7. Auditing & Logging: Tracking user activities

8. Views: Showing only limited data to specific users
📘 Chapter 2.3 — Control Structures, Cursors & Views
1. Control Structures
Definition: Allow conditional and iterative execution inside PL/SQL programs.
1. Conditional Control

IF Statement:

IF condition THEN
   statements;
END IF;

IF-ELSE Statement

ELSIF Ladder

CASE Statement: Used for multiple conditions

2. Iterative / Loop Control

LOOP: Executes repeatedly until condition is met

LOOP
   EXIT WHEN condition;
END LOOP;

WHILE LOOP

FOR LOOP

3. Sequential Control

Statements executed in sequence:

• Assignments
• SQL commands
• Procedure calls
2. Cursors
Definition: A pointer used to fetch query results row-by-row.
Types of Cursors

1. Implicit Cursor: Created automatically by Oracle

2. Explicit Cursor: Created manually by programmer

Steps in Explicit Cursor
1. Declare
2. Open
3. Fetch
4. Close
3. Views
Definition: A virtual table created from one or more tables using SELECT query.
Features of Views
• Does NOT store data physically
• Provides security
• Simplifies queries
• Shows limited/required data only
Example
CREATE VIEW StuView AS
SELECT Roll, Name FROM Student;