■■ DATABASE MANAGEMENT SYSTEMS

Complete Viva Study Notes

Units 1 · 2 · 3 | All Topics Covered

UNIT 1 — Introduction & Entity Relationship Model

1. Database System Concepts & Architecture

Database: An organized collection of related data that can be easily accessed, managed, and updated.
DBMS: Software that manages databases. Examples: MySQL, Oracle, PostgreSQL.

3-Level ANSI/SPARC Architecture

Level Description

External Level What individual users see (views/user schemas)

Conceptual Level Logical structure of entire database (community view)

Internal Level Physical storage details on disk

2. Data Models
Model Description

Hierarchical Tree structure — parent–child relationship

Network Graph structure — supports many-to-many

Relational Tables (most widely used today)

Object-Oriented Objects like OOP paradigm

3. Schema vs Instance
• Schema = Blueprint/structure of DB (rarely changes)
• Instance = Actual data at a given point in time (changes frequently)
Think of Schema as a class and Instance as an object in OOP.

4. Data Independence
• Physical Data Independence: Change physical storage without affecting logical schema.
• Logical Data Independence: Change logical schema without affecting external views.

5. Database Languages
Language Full Form Commands

DDL Data Definition Language CREATE, ALTER, DROP, TRUNCATE

DML Data Manipulation Language SELECT, INSERT, UPDATE, DELETE

DCL Data Control Language GRANT, REVOKE

TCL Transaction Control Language COMMIT, ROLLBACK, SAVEPOINT

6. Entity-Relationship (ER) Model

Basic Components:
• Entity — Real-world object (e.g., Student, Employee) → Rectangle
• Attribute — Property of an entity (e.g., Name, Age) → Ellipse
• Relationship — Association between entities → Diamond

Types of Attributes
Type Example Notation

Simple Age, Salary Plain ellipse

Composite Name (First + Last) Ellipse with sub-ellipses

Multi-valued Phone numbers Double ellipse

Derived Age (derived from DOB) Dashed ellipse

Key Roll No Underlined in ellipse

Mapping Constraints (Cardinality)

Type Example

1 : 1 (One-to-One) One employee manages one department

1 : N (One-to-Many) One customer places many orders

M : N (Many-to-Many) Many students enroll in many courses

Keys
Key Definition

Super Key Any set of attributes that uniquely identifies a tuple

Candidate Key Minimal super key (no redundant attributes)

Primary Key The chosen candidate key for a table

Foreign Key References the primary key of another table

Composite Key Primary key made of two or more attributes

Generalization vs Specialization
• Generalization: Bottom-up approach — Student + Teacher → Person
• Specialization: Top-down approach — Person → Student, Teacher
• Aggregation: Treating a relationship set as an entity to form higher-level relationships.
UNIT 2 — Relational Data Model & SQL

1. Relational Data Model Concepts

Term Meaning

Relation A table

Tuple A row in the table

Attribute A column in the table

Domain Set of allowed values for an attribute

Degree Number of attributes (columns)

Cardinality Number of tuples (rows)

2. Integrity Constraints
Constraint Meaning

Domain Constraint Values must belong to the defined domain of the attribute

Entity Integrity Primary key cannot be NULL

Referential Integrity Foreign key must match an existing primary key or be NULL

Key Constraint No two tuples can have the same primary key value

3. Assertions & Triggers

• Assertion: A condition/predicate that must always hold true in the database.
• Trigger: A stored procedure that automatically fires when a specific DB event (INSERT / UPDATE /
DELETE) occurs.
CREATE TRIGGER before_insert BEFORE INSERT ON Employee FOR EACH ROW SET [Link] =
10000;

4. Relational Algebra (Procedural)

Operation Symbol Meaning

Select σ (sigma) Filter rows based on a condition

Project π (pi) Select specific columns

Union ∪ Combine tuples from two compatible relations

Intersection ∩ Common tuples in both relations

Difference − Tuples in R1 but not in R2

Cartesian Product × All possible combinations of tuples

Natural Join ■ Combine related tuples on common attributes

5. Relational Calculus (Non-Procedural)
• Tuple Calculus: { t | P(t) } — describes WHAT tuples to retrieve using predicates.
• Domain Calculus: Uses domain variables (individual attribute values) instead of whole tuples.
Key difference: Relational Algebra is procedural (HOW), Calculus is declarative (WHAT).

6. SQL — Key Commands

-- DDL: Define structure CREATE TABLE Student ( RollNo INT PRIMARY KEY, Name
VARCHAR(50), Age INT ); -- DML: Manipulate data INSERT INTO Student VALUES (1,
'Rahul', 20); SELECT * FROM Student WHERE Age > 18; UPDATE Student SET Age = 21 WHERE
RollNo = 1; DELETE FROM Student WHERE RollNo = 1; -- Joins SELECT [Link],
[Link] FROM Student S INNER JOIN Enrollment E ON [Link] = [Link] INNER JOIN
Course C ON [Link] = [Link];
UNIT 3 — Database Design & Normalization

1. Functional Dependencies (FD)

X → Y means: if two tuples have the same value of X, they must have the same value of Y. X is the
determinant, Y is the dependent.
Example: RollNo → Name (Roll number functionally determines Name)

Types of Functional Dependencies

Type Definition Example

Trivial FD Y is a subset of X {A,B} → A

Non-Trivial FD Y is NOT a subset of X RollNo → Name

Transitive FD X→Y and Y→Z implies X→Z ID→Dept, Dept→HOD

Partial FD Non-key attr depends on part of composite

See PK
2NF example

2. Normal Forms
1NF — First Normal Form
• Each cell must have atomic (single, indivisible) values.
• No repeating groups or arrays in any column.
Violation example: Phone = '9876, 5432' stored in one cell.

2NF — Second Normal Form

• Must satisfy 1NF.
• No partial dependency — every non-key attribute must depend on the entire primary key.
Violation: PK = (StudentID, CourseID) but StudentName depends only on StudentID (partial).

3NF — Third Normal Form

• Must satisfy 2NF.
• No transitive dependency — non-key attributes must not depend on other non-key attributes.
Violation: StudentID → DeptID → DeptName (DeptName is transitively dependent).

BCNF — Boyce-Codd Normal Form

• Stricter version of 3NF.
• For every FD X → Y, X must be a super key.
• Handles anomalies 3NF misses when multiple candidate keys overlap.
Key Difference: 3NF allows non-superkey determinants if the dependent is part of a candidate key; BCNF
does NOT.

4NF — Fourth Normal Form

• Must satisfy BCNF.
• No multi-valued dependencies (MVD) — A →→ B means each value of A has a set of B values
independent of other attributes.

5NF — Fifth Normal Form (PJNF)

 • Must satisfy 4NF.
• No join dependencies — table cannot be reconstructed by joining smaller tables unless joins are
based on candidate keys.

Normal Forms — Quick Summary

Normal Form Eliminates Condition

1NF Repeating groups / non-atomic values Atomic values only

2NF Partial dependencies 1NF + no partial FDs

3NF Transitive dependencies 2NF + no transitive FDs

BCNF Anomalies with multiple candidate keys Every determinant is a super key

4NF Multi-valued dependencies BCNF + no MVDs

5NF Join dependencies 4NF + no join dependencies

3. Lossless Join Decomposition

When decomposing a relation R into R1 and R2, the original table must be exactly recoverable by joining
the parts — no spurious (extra) tuples should appear.
• Test: R1 ∩ R2 → R1 OR R1 ∩ R2 → R2 (the intersection must be a key of at least one part).

4. Inclusion Dependencies
A generalization of referential integrity: the set of values appearing in one column must be a subset of
values in another column.

5. Normalization using FD, MVD and JDs

• FD-based normalization: Achieve 1NF → 2NF → 3NF → BCNF by eliminating partial and transitive
FDs.
• MVD-based normalization: Achieve 4NF by eliminating multi-valued dependencies.
• JD-based normalization: Achieve 5NF by eliminating join dependencies.

Alternatives to Database Design

• Universal Relation approach — start with one big table, decompose.
• Entity-Relationship approach — design ER diagram first, then convert to tables.
• Normalization approach — start with existing tables, apply normal forms iteratively.
■ Quick Viva Q&A; Reference

Question Answer

What is the difference between DDL & DML? DDL defines DB structure (CREATE/ALTER/DROP); DML manipulate

What is a candidate key? The minimal super key — a super key with no redundant attributes.

What is a super key? Any set of attributes that uniquely identifies a tuple in a relation.

What is BCNF? A normal form where every determinant in every FD must be a super

Difference between 3NF and BCNF? BCNF is stricter. 3NF allows non-superkey determinants if the depend

What is a trigger? An auto-executed stored procedure that fires on a specific DB event (

What is lossless decomposition? Decomposition where the original relation can be exactly reconstructe

What is referential integrity? A rule that a foreign key value must either match an existing primary k

What is an MVD? A Multi-Valued Dependency — A →→ B means each value of A indep

Difference between schema and instance? Schema is the structure (blueprint); instance is the actual data stored

What is data independence? The ability to change one level of the DB architecture without affecting

What is relational algebra? A procedural query language with operations like Select (σ), Project (π

What is an assertion? A constraint (predicate) that must always hold true across the databas

What is aggregation in ER model? Treating a relationship as an entity to form relationships with other ent

What is a derived attribute? An attribute whose value is computed from other attributes (e.g., Age

Best of luck for your DBMS Viva! ■