Introduction to Database Systems

A database is an organized collection of structured information, or data, typically stored electronically in a

computer system. A Database Management System (DBMS) is the software that interacts with end users,
applications, and the database itself to capture and analyze the data.

1. Purpose of Database Systems

Before DBMS, organizations used File Processing Systems. Databases were developed to overcome the
inherent limitations of file systems, such as:

Data Redundancy and Inconsistency: Multiple copies of the same data lead to wasted space and
conflicting information.

Difficulty in Accessing Data: File systems require writing new programs for every new task.

Data Isolation: Data is scattered in various files and formats.

Integrity Problems: Hard to enforce constraints (e.g., a bank balance shouldn't be negative).

Atomicity Issues: Ensuring that if a transaction fails halfway, the data doesn't remain in a corrupted
state.

Concurrent Access: Multiple users cannot safely update the same file simultaneously.

2. Components of DBMS

A DBMS environment consists of five main components:

1. Hardware: The physical devices like computers, storage disks, and network devices.

2. Software: The DBMS software itself, the Operating System, and application programs.

3. Data: The most important component; it includes operational data and metadata (data about data).

4. Procedures: The instructions and rules that govern the design and use of the database.

5. People: Users like Database Administrators (DBA), Database Designers, and End Users.

3. Applications of DBMS

Banking: For customer information, accounts, loans, and transactions.

Airlines: For reservations and schedule information.

Universities: For student registration, grades, and course schedules.

E-commerce: For product catalogs, shopping carts, and order tracking.

Human Resources: For records about employees, salaries, and tax deductions.

4. Three-Tier DBMS Architecture

This architecture is used to separate the user applications from the physical database. It provides a framework
for data independence.

1. Internal Level (Physical Level): Describes how the data is actually stored on the storage devices. It
deals with complex low-level data structures.

2. Conceptual Level (Logical Level): Describes what data is stored in the database and the relationships
among the data. It focuses on the business logic.

3. External Level (View Level): The highest level; it describes only part of the entire database that is
relevant to a specific user.

5. Data Independence

Data independence is the ability to modify a schema definition in one level without affecting the schema
definition in the next higher level.

Logical Data Independence: The ability to change the conceptual schema without changing the external
schemas or application programs.

Physical Data Independence: The ability to change the internal schema (storage structures) without
changing the conceptual schema.

6. Database Schema and Instance

Schema: The logical design or "blueprint" of the database. It is stable and rarely changes.

Instance: The actual data stored in the database at a particular moment in time. It changes frequently as
data is inserted or deleted.

7. Data Modeling

Data modeling is the process of creating a visual representation of either a whole information system or parts of
it to communicate connections between data points and structures. It serves as a bridge between real-world
requirements and the physical database.

8. Entity-Relationship (ER) Model

The ER model views the real world as a collection of basic objects, called entities, and relationships among
these objects.

Entity: An object that exists in the real world (e.g., a Student, a Car).

Attribute: Properties of an entity (e.g., Name, Age).

Relationship: An association among several entities (e.g., Student enrolled in Course).

9. Relational Model

Proposed by E.F. Codd, this model represents data in the form of tables (called Relations).

Tuple: A row in a table (represents a record).

Attribute: A column in a table.

 Domain: The set of permitted values for an attribute.

10. Comparison: Relational vs. Non-Relational (NoSQL)

Feature Relational (SQL) Non-Relational (NoSQL)

Structure Tabular (Rows/Columns) Document, Key-Value, Graph, Wide-column
Schema Rigid/Predefined Flexible/Dynamic
Scalability Vertical (Scale up hardware) Horizontal (Scale out across servers)
Transactions ACID compliant (High reliability) BASE (Prioritizes availability/speed)
Examples MySQL, PostgreSQL, Oracle MongoDB, Cassandra, Redis

Viva Questions & Answers

Basic Concepts

1. What is a Database?

A database is an organized collection of structured data stored electronically. It allows for efficient retrieval,
insertion, and management of information.

2. Define DBMS.

A Database Management System (DBMS) is software that acts as an interface between the database and its
users or applications. Examples include MySQL, Oracle, and PostgreSQL.

3. What is Metadata?

Metadata is "data about data." It describes the structure, constraints, and relationships within the database
(e.g., table names, column types, and primary keys).

4. Mention two disadvantages of File Processing Systems.

Data Redundancy: Same data stored in multiple files.

Data Inconsistency: Different versions of the same data appearing in different places.

5. What is the difference between data and information?

Data is raw, unorganized facts (e.g., "100"). Information is processed data that has meaning (e.g., "100 is the
student's score").

6. Who is a DBA?

A Database Administrator (DBA) is a person responsible for the design, implementation, maintenance, and
security of a database system.

7. What is the main goal of a DBMS?

To provide a way to store and retrieve database information that is both convenient and efficient.

8. What are the ACID properties?

 Atomicity: The entire transaction takes place at once or doesn't happen at all.

Consistency: The database must be in a consistent state before and after the transaction.

Isolation: Multiple transactions occur independently without interference.

Durability: Once a transaction is committed, changes are permanent even during a power failure.

9. Define Data Abstraction.

It is the process of hiding complex internal details of how data is stored and maintained, providing users with a
simplified view.

10. What is a Database Engine?

The core service for storing, processing, and securing data. It handles the actual execution of commands.

Architecture & Design

11. Explain the three-level architecture of DBMS.

It consists of the External Level (user views), Conceptual Level (logical structure), and Internal Level
(physical storage). It aims to separate the user's view from the physical storage.

12. Why do we need data independence?

To allow changes in one level of the database (like moving to a new hard drive) without requiring changes to the
user's application programs.

13. What is the difference between Physical and Logical data independence?

Physical: Ability to change physical storage without affecting the logical schema.

Logical: Ability to change the logical schema without affecting user views/applications.

14. Define Database Schema.

The skeleton or logical design of the entire database. It defines how data is organized and how relationships are
established.

15. What is a Database Instance?

The snapshot of the data in the database at a specific moment in time.

16. What is a Data Sublanguage?

Languages used to interact with the database, categorized into DDL (definition) and DML (manipulation).

17. What is the difference between DDL and DML?

DDL (Data Definition Language): Used to define or modify the structure (schema).

DML (Data Manipulation Language): Used to manage and manipulate the data within the schema
(insert, delete, update).

18. Give examples of DDL commands.

CREATE, ALTER, DROP, TRUNCATE.

19. Give examples of DML commands.

SELECT, INSERT, UPDATE, DELETE.

20. What is a View in a database?

A "virtual table" based on the result-set of an SQL statement. It does not store data itself but displays data from
other tables.

Data Modeling & ER Model

21. What is an ER Model?

An Entity-Relationship model describes data as entities, attributes, and relationships. It is used for high-level
conceptual design.

22. Define an Entity and an Entity Set.

An Entity is a real-world object (e.g., a specific student). An Entity Set is a collection of similar entities (e.g., all
students in a college).

23. What is a Weak Entity?

An entity that cannot be uniquely identified by its own attributes alone and depends on a "strong" owner entity
(e.g., a "Dependent" entity depends on an "Employee").

24. What is an Attribute?

A characteristic or property of an entity (e.g., a student's Roll Number).

25. Explain the types of attributes.

Simple: Cannot be divided (e.g., Age).

Composite: Can be divided (e.g., Name into First and Last).

Derived: Calculated from others (e.g., Age from Date of Birth).

Multi-valued: Can have multiple values (e.g., Phone Numbers).

26. What is a Primary Key?

A unique identifier for a record in a table. It cannot be NULL.

27. What is a Candidate Key?

A minimal set of attributes that can uniquely identify a tuple. There can be multiple candidate keys in a table.

28. What is a Super Key?

A set of one or more attributes that collectively allow us to identify a tuple uniquely.

29. Define a Foreign Key and its purpose.

A field in one table that refers to the Primary Key in another table. It is used to establish links between tables.

30. What is Cardinality in ER diagrams?

It defines the numerical attributes of the relationship between two entities (e.g., One-to-One, One-to-Many,
Many-to-Many).

31. What is Participation Constraint?

 Total: Every entity in the set must participate in the relationship.

Partial: Only some entities participate.

32. What is an "is-a" relationship?

It represents inheritance in ER models, where a subclass inherits properties from a superclass (e.g., a
"Manager" is-a "Employee").

Relational Model & SQL

33. What is a Relation in the Relational Model?

A Relation is essentially a Table consisting of rows and columns.

34. What is a Tuple?

A single row in a table representing a single record.

35. Define Referential Integrity.

A rule stating that a foreign key value must either match a primary key value in the related table or be NULL.

36. What is a NULL value?

A field with no value. It is different from a zero or a space; it represents missing or unknown data.

37. What is Normalization?

The process of organizing data in a database to reduce redundancy and improve data integrity.

38. Why do we normalize databases?

To eliminate anomalies (Insertion, Update, and Deletion anomalies) and save storage space.

39. What is 1NF?

A table is in 1NF if it contains only atomic (indivisible) values and there are no repeating groups.

40. What is 2NF?

A table is in 2NF if it is in 1NF and all non-key attributes are fully functional dependent on the primary key (no
partial dependency).

41. What is 3NF?

A table is in 3NF if it is in 2NF and there is no transitive functional dependency (non-key attributes shouldn't
depend on other non-key attributes).

42. What is BCNF?

Boyce-Codd Normal Form is a stronger version of 3NF. It requires that for every functional dependency $X
\rightarrow Y$, $X$ must be a super key.

43. What is a Join in SQL?

An operation used to combine rows from two or more tables based on a related column between them.

44. Name different types of Joins.

Inner Join, Left Join, Right Join, Full Outer Join, and Self Join.
NoSQL & Advanced Topics

45. What is NoSQL?

"Not Only SQL" databases are non-tabular and store data differently than relational tables (e.g., documents or
graphs).

46. When should you use NoSQL instead of SQL?

When dealing with large volumes of unstructured data, requiring high availability, or needing a flexible schema
that changes often.

47. What does "Horizontal Scaling" mean?

Adding more machines (servers) to your pool of resources to handle increased load, rather than just upgrading
the hardware of one machine.

48. Explain the CAP theorem.

It states that a distributed system can only provide two out of three guarantees: Consistency, Availability, and
Partition Tolerance.

49. What is Sharding in databases?

A type of database partitioning that separates very large databases into smaller, faster, more easily managed
parts called data shards.

50. What is a Deadlock in a database?

A situation where two or more transactions are waiting indefinitely for one another to release locks on
resources.

Relational Query Languages

A Relational Query Language is a specialized language used to communicate with a database. While SQL
(Structured Query Language) is the most common example, these languages are designed to manage data
held in a relational database management system (RDBMS).

1. Categories of SQL Commands

SQL commands are divided into several sub-languages based on their functionality:

Data Definition Language (DDL)

DDL is used to define the database structure or schema. These commands are permanent (auto-committed)
once executed.

CREATE: Creates a new table, database, or index.

ALTER: Modifies an existing database object (like adding a column).

DROP: Deletes an entire table or database.

TRUNCATE: Removes all records from a table but keeps the structure.

Data Manipulation Language (DML)

DML is used for managing data within the objects defined by DDL.

INSERT: Adds new rows to a table.

UPDATE: Modifies existing data.

DELETE: Removes specific rows.

SELECT: Retrieves data from the database (often categorized as DQL - Data Query Language).

Data Control Language (DCL)

DCL deals with the permissions and access control of the database system.

GRANT: Gives a user access privileges.

REVOKE: Withdraws access privileges.

Transaction Control Language (TCL)

TCL commands manage changes made by DML statements. They group DML statements into logical
transactions.

COMMIT: Saves all changes permanently.

ROLLBACK: Undoes changes if an error occurs.

SAVEPOINT: Sets a point within a transaction to which you can later roll back.

2. Integrity Constraints

Integrity constraints are rules applied to table columns to ensure the accuracy and reliability of the data.

NOT NULL: Ensures a column cannot have a NULL value.

UNIQUE: Ensures all values in a column are different.

DEFAULT: Provides a default value for a column if none is specified.

CHECK: Ensures that all values in a column satisfy a specific condition.

Primary Key / Foreign Key: (See Database Keys below).

3. Database Keys

Keys are used to uniquely identify rows in a table and establish relationships between tables.

Super Key: A set of one or more columns that can uniquely identify a row.

Candidate Key: A minimal Super Key (no redundant attributes).

Primary Key: The specific Candidate Key chosen by the DBA to uniquely identify rows. It cannot be
NULL.

Foreign Key: A column in one table that refers to the Primary Key of another table, maintaining referential
integrity.

Composite Key: A Primary Key that consists of two or more columns.

4. SQL Basic Operations

The most fundamental operations in SQL are often referred to as CRUD (Create, Read, Update, Delete).

Selection ($\sigma$): Selecting specific rows that meet a criteria.

Projection ($\pi$): Selecting specific columns from a table.

Join ($\bowtie$): Combining data from two tables based on a common field.

Aggregate Functions: Performing calculations on multiple rows (e.g., SUM(), AVG(), COUNT(), MIN(),
MAX()).

50 Viva-Based Questions & Answers

Categories & Commands

1. What is the difference between DDL and DML? DDL changes the structure (table design), while DML
changes the data (rows).

2. What is SQL? Structured Query Language, the standard language for relational databases.

3. Is SQL case-sensitive? Generally, no for keywords (SELECT vs select), but it can be for data values
depending on the database config.

4. What does TRUNCATE do? It deletes all rows from a table but keeps the table structure intact. It is faster
than DELETE.

5. Why is TRUNCATE a DDL command? Because it resets the table's metadata (like identity seeds) and is
auto-committed.

6. What is the difference between DELETE and DROP? DELETE removes rows; DROP removes the entire table
structure and its data.

7. What is DCL used for? It is used by DBAs to manage user permissions (Security).

8. Explain COMMIT. It makes all pending data changes permanent in the database.

9. Explain ROLLBACK. It reverts the database to the last COMMIT or SAVEPOINT.

10. What is a Transaction? A unit of work that must either be completed entirely or not at all.

Integrity & Constraints

11. What are Integrity Constraints? Rules that prevent accidental damage to the database.

12. What is Domain Integrity? Ensuring all data in a column falls within a defined range/format.

13. What is Entity Integrity? Ensuring every table has a Primary Key and it is not NULL.

14. What is Referential Integrity? Ensuring that a Foreign Key value always points to an existing Primary
Key value.

15. Can a table have multiple Unique constraints? Yes.

16. Can a table have multiple Primary Keys? No, only one Primary Key (though it can be a Composite
Key).
 17. What happens if you try to insert a NULL into a NOT NULL column? The database will throw an error
and reject the insertion.

18. What is a CHECK constraint? A rule like Age >= 18 that validates data before entry.

19. Difference between UNIQUE and PRIMARY KEY? UNIQUE allows one NULL value; PRIMARY KEY does not
allow any NULLs.

20. What is a DEFAULT constraint? It fills a column with a predefined value if the user leaves it blank.

Database Keys

21. What is a Key? An attribute used to identify rows or link tables.

22. What is a Super Key? Any combination of columns that uniquely identifies a row.

23. How is a Candidate Key different from a Super Key? A Candidate Key is a "minimal" Super Key; you
can't remove any column from it without losing uniqueness.

24. Can a Foreign Key be NULL? Yes, if the relationship is optional.

25. What is a Composite Key? A key made of more than one column to ensure uniqueness.

26. What is a Surrogate Key? An artificial key (like an Auto-increment ID) used when no natural key exists.

27. What is an Alternate Key? Any Candidate Key that was not chosen as the Primary Key.

28. What is the purpose of a Foreign Key? To establish a "parent-child" relationship between tables.

29. What is "On Delete Cascade"? A setting where deleting a record in the parent table automatically
deletes related records in the child table.

30. What is "On Delete Set Null"? When the parent record is deleted, the foreign key in the child table is set
to NULL.

SQL Operations & Queries

31. What is the SELECT statement? Used to fetch data from a database.

32. What does the DISTINCT keyword do? It returns only unique (different) values, filtering out duplicates.

33. What is the WHERE clause? Used to filter records based on a condition.

34. How do you sort results in SQL? Using the ORDER BY clause (ASC or DESC).

35. What is the difference between WHERE and HAVING? WHERE filters rows before grouping; HAVING filters
groups after GROUP BY.

36. What is an Aggregate Function? Functions like COUNT, SUM, AVG that work on a set of values.

37. What does the LIKE operator do? Used for pattern matching with wildcards like % and _.

38. What is the % wildcard? Represents zero, one, or multiple characters.

39. What is the _ wildcard? Represents a single character.

40. What is GROUP BY? Groups rows that have the same values into summary rows.

41. What is an Inner Join? Returns records that have matching values in both tables.

42. What is a Left Join? Returns all records from the left table and matched records from the right.
 43. What is a Subquery? A query nested inside another SQL query.

44. What is the IN operator? Allows you to specify multiple values in a WHERE clause.

45. What is BETWEEN? Selects values within a given range.

46. How do you add a column to an existing table? ALTER TABLE table_name ADD column_name
datatype;.

47. How do you rename a table? Using RENAME or ALTER TABLE ... RENAME TO ....

48. What is the AS keyword used for? To give a table or a column a temporary name (Alias).

49. What is a Join condition? The logic (usually [Link] = [Link]) that links two tables.

50. What is the use of GRANT? To give specific users permission to perform tasks like SELECT or UPDATE on a
database.

Relational Operations & Optimization

This section covers how we manipulate data, perform complex calculations, and optimize the database for high
performance.

1. Aggregate Functions

These functions perform a calculation on a set of values and return a single value.

SUM(): Adds up numeric values.

AVG(): Calculates the average.

COUNT(): Returns the number of rows.

MIN() / MAX(): Finds the lowest and highest values.

2. SQL Joins

Joins combine columns from one or more tables based on a related column.

Inner Join: Returns rows with matching values in both tables.

Left (Outer) Join: Returns all rows from the left table and matched rows from the right.

Right (Outer) Join: Returns all rows from the right table and matched rows from the left.

Full Outer Join: Returns all rows when there is a match in either table.

Cross Join: Returns the Cartesian product (every row of A paired with every row of B).

3. Set Operators

Used to combine the results of two or more SELECT statements.

UNION: Combines results and removes duplicates.

UNION ALL: Combines results including duplicates.

INTERSECT: Returns only the common rows.

 EXCEPT / MINUS: Returns rows present in the first query but not the second.

4. Views and Subqueries

Views: A virtual table created by a query. It doesn't store data but simplifies complex queries and
enhances security.

Subqueries: A query inside another query. They can be Nested (independent) or Correlated (dependent
on the outer query's values).

5. Relational Algebra

The theoretical foundation for SQL operations.

Selection ($\sigma$): Filters rows (horizontal).

Projection ($\pi$): Filters columns (vertical).

Union ($\cup$): Combines tuples.

Cartesian Product ($\times$): Combines every tuple of one relation with every tuple of another.

6. Window Functions

Unlike aggregate functions, window functions perform calculations across a set of table rows that are related to
the current row, without grouping them into a single output row.

ROW_NUMBER(): Assigns a unique sequential integer.

RANK() / DENSE_RANK(): Assigns ranking (handles ties differently).

OVER(): Defines the "window" or set of rows the function applies to.

7. Hashing and Indexing

Indexing: A data structure (like B-Trees) used to speed up data retrieval. It works like an index in a
textbook.

Hashing: A technique where a hash function maps a search key to a specific bucket or location, providing
very fast $O(1)$ access for equality searches.

8. Query Optimization

The process of choosing the most efficient way to execute a SQL statement. The Query Optimizer analyzes
different execution plans and picks the one with the lowest "cost" (CPU/Memory usage).

50 Viva-Based Questions & Answers

Joins & Set Operators

1. What is a Join? A way to retrieve data from multiple tables based on logical relationships.

2. Difference between Join and Union? Join combines columns; Union combines rows.

3. What is a Self-Join? Joining a table with itself (e.g., an Employee table where a column points to the
Manager's ID in the same table).

4. What is an Equi-join? A join that uses the equality operator (=) to match rows.
 5. What is a Natural Join? A join that automatically matches columns with the same name in both tables.

6. Can we use UNION on tables with different column counts? No, the number and data types of columns
must match.

7. Why is UNION ALL faster than UNION? Because it doesn't spend time checking for and removing
duplicates.

8. What is a Cartesian Product? Every row from Table A paired with every row from Table B. If A has 10
rows and B has 5, the result is 50 rows.

9. When does a Join become a Cross Join? When no WHERE or ON clause is specified.

10. What is an Outer Join? A join that includes rows even if they don't have a match in the other table.

Aggregates & Windows

11. Do aggregate functions ignore NULL values? Yes, except for COUNT(*).

12. What is the difference between COUNT(*) and COUNT(column)? COUNT(*) counts all rows; COUNT(column)
counts only non-null values in that column.

13. Can you use aggregate functions in a WHERE clause? No, you must use HAVING.

14. What is a Window Function? A function that performs a calculation across related rows while keeping
individual row identities.

15. Difference between RANK() and DENSE_RANK()? RANK() skips numbers after a tie (1, 2, 2, 4);
DENSE_RANK() does not (1, 2, 2, 3).

16. What is the PARTITION BY clause? It divides the result set into partitions to which the window function is
applied.

17. What is LEAD() and LAG()? Functions used to access data from a subsequent or previous row without a
self-join.

Views & Subqueries

18. What is a View? A saved SQL query that acts as a virtual table.

19. What is a Materialized View? A view whose results are physically stored and periodically refreshed.

20. Can you update data through a View? Yes, but only if the view refers to a single table and meets
specific criteria.

21. What is a Subquery? A query nested within another statement (SELECT, INSERT, UPDATE, or DELETE).

22. Difference between a Subquery and a Join? Joins are generally faster for large datasets; subqueries
are often easier to read for complex logic.

23. What is a Correlated Subquery? A subquery that depends on the outer query for its values. It executes
once for every row processed by the outer query.

24. What is the EXISTS operator? Used in a subquery to check if any record is returned (returns True/False).

Relational Algebra

25. What is Relational Algebra? A procedural query language used to describe how data should be
retrieved.
 26. What is the symbol for Selection? The Greek letter Sigma ($\sigma$).

27. What is the symbol for Projection? The Greek letter Pi ($\pi$).

28. What is the difference between Selection and Projection? Selection filters rows; Projection filters
columns.

29. What is the Rename ($\rho$) operation? Used to give a name to the result of a relational algebra
expression.

Indexing & Hashing

30. What is an Index? A physical structure that speeds up data retrieval.

31. What is a Clustered Index? An index that defines the physical order in which data is stored in a table
(only one per table).

32. What is a Non-Clustered Index? An index that contains a sorted list of values with pointers to the actual
data.

33. What is a B-Tree? The most common data structure used for indexing in databases.

34. What are the disadvantages of Indexing? It slows down INSERT, UPDATE, and DELETE operations and
takes up extra disk space.

35. What is a Unique Index? An index that ensures no two rows have the same value in the indexed column.

36. What is Hashing? Transforming a key into a hash value to locate data directly.

37. Difference between Indexing and Hashing? Indexing is better for range queries (e.g., Price between
10 and 20); Hashing is better for exact matches (ID = 105).

38. What is a Hash Collision? When two different keys produce the same hash value.

Query Optimization

39. What is Query Optimization? The process of finding the most efficient execution plan for a query.

40. What is an Execution Plan? A visual or text description of how the database engine will execute a query.

41. What is a Full Table Scan? When the database reads every single row in a table because no suitable
index was found.

42. What is a Query Optimizer? A DBMS component that estimates the cost of various execution plans.

43. How does EXPLAIN help in SQL? It shows the execution plan of a query so you can see if indexes are
being used.

44. What is Cost-Based Optimization? An approach where the optimizer picks a plan with the lowest
estimated "cost" (resource usage).

45. Why should we avoid SELECT *? It retrieves unnecessary data, increasing I/O and reducing
performance.

46. What is Hinting in SQL? Giving the optimizer specific instructions on which index or join method to use.

47. What is Statistics in DBMS? Data about the distribution of values in columns, used by the optimizer to
make decisions.

48. What is Query Rewriting? When the optimizer changes the query structure to a more efficient form
without changing the result.
 49. What is Selective Indexing? Creating indexes only on columns frequently used in WHERE and JOIN
clauses.

50. What is a Deadlock? When two transactions hold locks and wait for each other to release them, causing
a standstill.

Relational Database Design

Relational database design focuses on organizing data to minimize redundancy and ensure data consistency
through rules and structured normalization.

1. Data Integrity Rules

Data integrity ensures that the data in the database is accurate, complete, and reliable.

Entity Integrity: Every table must have a Primary Key, and the Primary Key cannot be NULL.

Referential Integrity: A Foreign Key value must either match a Primary Key value in the related table or
be NULL.

Domain Integrity: All data in a column must fall within a defined range or format.

User-Defined Integrity: Specific business rules defined by the user (e.g., "Balance cannot be less than
0").

2. Functional Dependency (FD)

Functional dependency describes the relationship between attributes in a relation.

If attribute $A$ uniquely determines attribute $B$, we say $B$ is functionally dependent on $A$ (denoted
as $A \rightarrow B$).

Trivial FD: If $B$ is a subset of $A$ (e.g., $\{RollNo, Name\} \rightarrow RollNo$).

Non-Trivial FD: If $B$ is not a subset of $A$ (e.g., $RollNo \rightarrow Name$).

3. Need for Normalization

Normalization is the process of organizing data to reduce redundancy and eliminate anomalies:

Insertion Anomaly: Being unable to insert data because some other data is missing.

Update Anomaly: Changing data in one place requires changes in multiple rows.

Deletion Anomaly: Deleting one piece of data unintentionally removes other valuable information.

4. Normal Forms (1NF to BCNF)

First Normal Form (1NF): A table is in 1NF if it contains only atomic (indivisible) values and there are no
repeating groups.

Second Normal Form (2NF): It must be in 1NF, and all non-key attributes must be fully functionally
dependent on the Primary Key (no partial dependency).

Third Normal Form (3NF): It must be in 2NF, and there must be no transitive functional dependency
(non-key attributes should not depend on other non-key attributes).
 Boyce-Codd Normal Form (BCNF): A stronger version of 3NF. For every FD $X \rightarrow Y$, $X$
must be a super key.

5. Multivalued Dependency & 4NF

Multivalued Dependency (MVD): Occurs when one attribute determines a set of values for another
attribute, and these values are independent of other attributes.

Fourth Normal Form (4NF): A table is in 4NF if it is in BCNF and contains no non-trivial multivalued
dependencies.

50 Viva Questions & Answers

Normalization Fundamentals

1. What is Normalization? The process of organizing data to reduce redundancy and improve integrity.

2. Why do we use it? To eliminate insertion, update, and deletion anomalies.

3. What is a Modification Anomaly? Problems that arise when data is updated, inserted, or deleted in a
poorly designed database.

4. Define Atomic Value. A value that cannot be further subdivided.

5. What is a Repeating Group? When a single column contains multiple values for one record.

6. What is De-normalization? The process of intentionally adding redundancy to improve read

performance.

7. What is a Determinant? The attribute on the left side of a functional dependency ($A$ in $A \rightarrow
B$).

8. What is Lossless Join Decomposition? Ensuring that when a table is split, it can be joined back without
losing or gaining data.

9. What is Dependency Preservation? Ensuring that all functional dependencies are maintained after the
table is split.

10. Who proposed the Normal Forms? E.F. Codd.

Functional Dependencies & Keys

11. What is Full Functional Dependency? When a non-key attribute depends on the entire primary key, not
just a part of it.

12. What is Partial Dependency? When a non-key attribute depends on only a part of a composite primary
key.

13. What is Transitive Dependency? When $A \rightarrow B$ and $B \rightarrow C$; therefore $A
\rightarrow C$.

14. Define Primary Key. A unique, non-null identifier for a record.

15. Define Candidate Key. A minimal set of attributes that can uniquely identify a row.

16. What is a Super Key? Any set of attributes that uniquely identifies a row.

17. What is a Non-prime Attribute? An attribute that is not part of any candidate key.
 18. What is a Prime Attribute? An attribute that is part of at least one candidate key.

19. Can a table have multiple 1NF violations? Yes, if it has non-atomic values or repeating groups.

20. Is 3NF always better than 2NF? Yes, as it further reduces redundancy.

Normal Forms 1NF - BCNF

21. Requirement for 1NF? Atomic values and no repeating groups.

22. Requirement for 2NF? Must be in 1NF and have no partial dependencies.

23. Requirement for 3NF? Must be in 2NF and have no transitive dependencies.

24. Difference between 3NF and BCNF? 3NF allows $X \rightarrow Y$ if $Y$ is a prime attribute; BCNF
requires $X$ to be a super key regardless.

25. Is every BCNF table also in 3NF? Yes.

26. Is every 3NF table also in BCNF? No.

27. When does a 2NF violation occur? When a non-key attribute depends on only a portion of a composite
primary key.

28. How do you fix a 2NF violation? Move the partially dependent attributes to a new table.

29. How do you fix a 3NF violation? Move the transitively dependent attributes to a new table.

30. What is 3.5 Normal Form? Another name for BCNF.

Advanced Normalization (MVD & 4NF)

31. What is a Multivalued Dependency (MVD)? When one key implies two or more independent multi-
valued facts.

32. Symbol for MVD? Double arrow ($A \twoheadrightarrow B$).

33. Requirement for 4NF? Must be in BCNF and have no non-trivial MVDs.

34. Give an example of an MVD. A student having multiple hobbies and multiple phone numbers that are
independent of each other.

35. What is a Trivial MVD? When $B$ is a subset of $A$, or $A \cup B$ is the whole relation.

36. Is 4NF common in real-world apps? Less common than 3NF/BCNF but used for complex data.

37. What is 5NF? Deals with Join Dependencies; also called Project-Join Normal Form.

38. What is a Join Dependency? When a table can be reconstructed by joining several smaller tables.

39. Is 4NF stricter than BCNF? Yes.

40. Does 4NF eliminate redundancy caused by MVDs? Yes.

Integrity & Design

41. What is Entity Integrity? Primary keys cannot be null.

42. What is Referential Integrity? Foreign keys must point to valid primary keys.

43. What is a Surrogate Key? An artificial key used when no natural key exists.
 44. What is "On Delete Cascade"? Automatically deleting child records when a parent record is deleted.

45. What is "On Delete Set Null"? Setting foreign keys to NULL in the child table when a parent is deleted.

46. What is a NULL value? A value representing "unknown" or "not applicable".

47. What is the difference between NULL and Zero? Zero is a number; NULL is the absence of a value.

48. What is a Domain? The set of all possible values for an attribute.

49. What is a Composite Key? A primary key made of two or more columns.

50. What is a Recursive Relationship? A relationship where an entity is related to itself.

Programming Constructs in Databases

Modern database systems allow for complex logic to be stored directly within the database using procedural
extensions (like PL/SQL for Oracle or T-SQL for SQL Server).

1. Flow Control Statements

These statements allow the database to execute logic based on specific conditions, similar to traditional
programming languages.

Conditional Statements: IF...ELSE, CASE, and COALESCE are used to perform different actions based on
logical tests.

Looping Statements: WHILE, LOOP, and REPEAT allow for the repeated execution of code blocks until a
condition is met.

2. Functions and Stored Procedures

Both are blocks of reusable code, but they serve different purposes.

Stored Procedures: Used to perform a set of actions. They can return multiple values (via output
parameters) or no values at all. They can also contain transaction control logic (COMMIT, ROLLBACK).

Functions: Used to perform calculations and must return a single value. They are generally used within
SQL statements (e.g., SELECT function_name(column) FROM table).

3. Cursors

A cursor is a database object used to retrieve data from a result set one row at a time.

Implicit Cursors: Automatically created by the DBMS for single-row queries.

Explicit Cursors: Defined by the programmer for queries that return multiple rows, allowing the code to
"fetch" and process each row individually in a loop.

4. Triggers

A trigger is a special type of stored procedure that automatically "fires" or executes when a specific event
occurs in the database.

Events: INSERT, UPDATE, or DELETE.

Timing: Triggers can be set to run BEFORE or AFTER the event.

 Usage: Commonly used for auditing, enforcing complex integrity rules, or synchronizing tables.

5. Exception Handling

Exception handling allows the database to respond gracefully to errors (like division by zero or unique key
violations) rather than crashing the entire script.

Try...Catch: Logic is placed in a "Try" block; if an error occurs, control shifts to the "Catch" block to handle
the error or log it.

Database Transaction Processing

A transaction is a logical unit of work that contains one or more SQL statements.

1. Desirable Properties (ACID)

To ensure data integrity, every transaction must follow the ACID properties:

Atomicity: "All or nothing." If any part of the transaction fails, the entire transaction is rolled back.

Consistency: A transaction must move the database from one valid state to another valid state.

Isolation: Transactions occurring simultaneously should not interfere with each other.

Durability: Once a transaction is committed, the changes are permanent, even in the event of a system
failure.

2. Schedules and Serializability

Schedule: A sequence of operations (Read, Write, Commit, Abort) from one or more transactions.

Serial Schedule: Transactions are executed one after another. There is no concurrency, so consistency
is guaranteed.

Concurrent Schedule: Operations from multiple transactions are interleaved.

Serializability: A concurrent schedule is "serializable" if its outcome is the same as some serial execution
of those same transactions. This is the gold standard for concurrency control.

3. Concurrency Control

When multiple users access data at the same time, the DBMS must prevent conflicts.

Locking: Transactions "lock" data before using it (Shared locks for reading, Exclusive locks for writing).

Deadlock: A situation where two transactions are waiting for each other to release locks. The DBMS
usually resolves this by killing one transaction.

Timestamp Ordering: Assigning a unique time to each transaction to determine the order of execution.

4. Recoverability

Recoverability ensures that if a transaction fails or the system crashes, the database can return to a consistent
state.

Recoverable Schedule: If transaction $T_2$ reads data written by $T_1$, then $T_1$ must commit
before $T_2$ commits.
 Log-Based Recovery: The DBMS keeps a "Log" of all changes. If the system crashes, it uses the log to
REDO committed transactions and UNDO uncommitted ones.

50 Viva Questions & Answers

Programming Constructs

1. What is a Stored Procedure? A pre-compiled collection of SQL statements stored on the server.

2. Difference between Procedure and Function? Functions must return a value and can be used in
SELECT; Procedures don't have to return a value and handle complex tasks.

3. What is a Trigger? A script that runs automatically in response to DML events.

4. What are the types of Triggers? Row-level (runs for every row) and Statement-level (runs once per
query).

5. What is a Cursor? A pointer used to process a multi-row result set row-by-row.

6. Explain FETCH in a cursor. It is the command used to retrieve the next row from the cursor into variables.

7. What is an Autonomous Transaction? An independent transaction started by another transaction that

can commit or rollback without affecting the parent.

8. What is the purpose of Exception Handling? To intercept errors and prevent the termination of a
program.

9. Can a Function call a Stored Procedure? Generally no, because functions cannot perform DML
operations that change the database state.

10. What is the RAISE_ERROR or SIGNAL statement? Commands used to manually trigger an exception in the
code.

Transaction Concepts (ACID)

11. Define Transaction. A single logical unit of work.

12. What is Atomicity? Ensuring that either all operations of a transaction happen or none do.

13. What does Durability guarantee? That committed data will not be lost even if the system crashes.

14. What is a 'Dirty Read'? When a transaction reads data that has been modified by another transaction but
not yet committed.

15. What is a 'Non-repeatable Read'? When a transaction reads the same row twice and gets different data
because another transaction updated it in between.

16. What is a 'Phantom Read'? When a transaction runs a query twice and gets a different number of rows
because another transaction inserted/deleted rows.

17. Explain the 'Isolation' property. It ensures that concurrent transactions appear as if they are executing
one after another.

18. What is a Savepoint? A point within a transaction to which you can roll back without canceling the whole
transaction.

19. Difference between ROLLBACK and COMMIT? COMMIT saves changes; ROLLBACK undoes them.

20. What is the 'Consistency' property? Ensuring data follows all integrity constraints before and after the
 transaction.

Schedules & Concurrency

21. What is a Schedule? The order in which operations of concurrent transactions are executed.

22. What is a Serial Schedule? A schedule where one transaction finishes completely before the next starts.

23. What is a Serializable Schedule? A non-serial schedule that produces the same result as a serial one.

24. Define Conflict Serializability. A schedule is conflict serializable if it can be transformed into a serial
schedule by swapping non-conflicting operations.

25. What are the two types of locks? Shared (S) locks for reading and Exclusive (X) locks for writing.

26. What is Two-Phase Locking (2PL)? A protocol that ensures serializability by requiring transactions to
acquire all locks in a 'growing' phase and release them in a 'shrinking' phase.

27. What is a Deadlock? A "circular wait" where transactions are stuck waiting for each other.

28. How is a Deadlock detected? Using a "Wait-for Graph." A cycle in the graph indicates a deadlock.

29. What is Starvation? When a transaction waits indefinitely for a lock while other transactions keep getting
priority.

30. What is a Lock Manager? A DBMS component that keeps track of which transaction holds which lock on
which data item.

Concurrency Control Protocols

31. What is Optimistic Concurrency Control? Assuming conflicts are rare and only checking for them at
the end of the transaction.

32. What is Pessimistic Concurrency Control? Using locks to prevent conflicts from happening in the first
place.

33. Explain Timestamp Ordering. Using the start time of a transaction to decide priority and resolve
conflicts.

34. What is 'Thomas Write Rule'? A rule that allows certain out-of-order writes to be ignored to improve
concurrency.

35. What is Granularity of locking? The size of the data item being locked (e.g., locking a whole database,
a table, or just one row).

36. What is an Intent Lock? A lock used to signal that a transaction intends to lock something at a lower
level of granularity.

Recovery & Log System

37. What is a Recoverable Schedule? One where a transaction commits only after all transactions it read
from have committed.

38. What is a Cascading Rollback? When the failure of one transaction causes several other transactions to
roll back.

39. How do you prevent Cascading Rollbacks? By ensuring transactions only read committed data
(Cascasdeless Schedule).

40. What is the 'Log' in a database? A file that records all changes made to the database.
 41. Explain the 'Write-Ahead Logging' (WAL) rule. A rule stating that log records must be written to disk
before the actual data is updated on disk.

42. What is a Checkpoint? A point in the log where the DBMS ensures all previous committed changes are
safely written to the physical disk.

43. What is the 'Shadow Paging' recovery technique? Maintaining two copies of database pages (current
and shadow) to handle recovery without a log.

44. What are 'Immediate Updates'? When database changes are written to disk as they happen (but after
logging).

45. What are 'Deferred Updates'? When database changes are only written to disk after the transaction
commits.

46. What is the REDO operation? Replaying the log to apply changes of committed transactions after a
crash.

47. What is the UNDO operation? Reversing changes of transactions that were active (uncommitted) at the
time of a crash.

48. What is a Media Failure? A hardware failure, like a disk crash, that destroys data.

49. How do you recover from Media Failure? By restoring from a backup and replaying the transaction
logs.

50. What is a "Stable Storage"? Storage designed to survive all types of failures (usually implemented using
disk mirroring/RAID).

NoSQL Databases
NoSQL databases are designed to handle large volumes of unstructured or semi-structured data, offering high
scalability and flexible schemas compared to traditional relational databases.

1. Introduction to MongoDB and DynamoDB

MongoDB: A document-oriented NoSQL database that stores data in JSON-like documents with dynamic
schemas. It is highly popular for its flexibility and powerful query language.

DynamoDB: A fully managed, serverless, key-value and document database provided by Amazon Web
Services (AWS). It is designed for high-performance applications that require consistent, single-digit
millisecond latency at any scale.

2. Serverless Cloud Databases

A serverless database is a cloud computing model where the cloud provider manages the underlying
infrastructure, scaling, and maintenance.

Scalability: Automatically scales up or down based on application demand.

Cost-Efficiency: Users typically pay only for the resources consumed (read/write units or storage) rather
than a fixed server price.

Management: No manual patching, setup, or configuration of servers is required.

3. SQL vs. NoSQL

Feature SQL (Relational) NoSQL (Non-Relational)
Data Model Tables with fixed rows/columns. Documents, Key-Value, Graphs, Column-family.
Schema Rigid and predefined. Dynamic and flexible.
Scaling Vertical (bigger servers). Horizontal (more servers).
Complexity Best for complex joins/transactions. Best for large, changing data sets.

4. Working with MongoDB: Structure and JSON

Structure: Instead of tables and rows, MongoDB uses Collections and Documents.

JSON/BSON: Data is represented in JSON (JavaScript Object Notation) for human readability and stored
in BSON (Binary JSON) for efficiency.

Example JSON Representation:

JSON

{
"student_id": 101,
"name": "Arjun",
"major": "Electronics",
"skills": ["C", "SQL", "Python"],
"projects": {
"title": "Waste Segregator",
"status": "Completed"
}
}

5. Indexing and Performance (EXPLAIN)

Indexing in NoSQL is critical for performance. Without an index, MongoDB must perform a "Collection Scan"
(scanning every document).

Index Creation: [Link]({ "student_id": 1 })

EXPLAIN: The .explain() method provides insight into how a query is executed. It helps compare:

COLLSCAN: Entire collection was scanned (Slow).

IXSCAN: An index was used to find the data (Fast).

6. Vector Databases

A vector database stores data as mathematical vectors (long lists of numbers) rather than text or integers.

Purpose: Used primarily for AI and Machine Learning tasks like image recognition, recommendation
systems, and Natural Language Processing.

Similarity Search: Instead of looking for exact matches, it finds data points that are "closest" or most
similar in a multi-dimensional space.

50 Viva Questions & Answers

NoSQL & Cloud Basics

1. What does NoSQL stand for? "Not Only SQL."

 2. What is a Document Database? A database that stores data in flexible, semi-structured documents
(usually JSON).

3. Define Serverless Computing. A model where the cloud provider manages the server infrastructure, and
the user pays per use.

4. What is Sharding in NoSQL? The process of breaking up a large database into smaller chunks (shards)
across multiple servers to improve performance.

5. What is Replication? Storing copies of data on different servers to ensure high availability.

6. What are the four types of NoSQL databases? Key-Value, Document, Column-family, and Graph.

7. What is the CAP Theorem? A principle stating a distributed system can only provide two of three:
Consistency, Availability, and Partition Tolerance.

8. What is "Eventual Consistency"? A consistency model where data will become consistent across all
nodes eventually, but not necessarily immediately.

9. Give an example of a Key-Value store. Redis or Amazon DynamoDB.

10. What is a Graph Database? A database that uses nodes and edges to represent and store data (e.g.,
Neo4j).

MongoDB Specifics

11. How does MongoDB store data? In BSON (Binary JSON) format.

12. What is a Collection? A group of MongoDB documents, equivalent to a table in SQL.

13. What is a Document? A basic unit of data in MongoDB, equivalent to a row in SQL.

14. What is the _id field? A unique identifier automatically assigned to every document in MongoDB.

15. How do you insert a document in MongoDB? Using the [Link]() or insertMany()
command.

16. What is an Embedded Document? Placing a related document inside another document (nesting).

17. What is a Reference? Storing the ID of one document in another document to link them (similar to a
foreign key).

18. What is the Aggregation Framework? A set of tools in MongoDB for data processing and
transformation (like GROUP BY in SQL).

19. What is a "Pipeline" in aggregation? A series of stages (like $match, $group, $sort) that data passes
through.

20. Is MongoDB ACID compliant? Yes, it supports multi-document ACID transactions since version 4.0.

JSON & Data Representation

21. What is JSON? JavaScript Object Notation; a lightweight data-interchange format.

22. Difference between JSON and BSON? JSON is text-based (human-readable); BSON is binary
(machine-optimized).

23. What are the basic data types in JSON? String, Number, Boolean, Array, Object, and Null.

24. Why is JSON preferred for web applications? It is easy for browsers to parse and lightweight for
network transmission.
 25. Can JSON have comments? Standard JSON does not support comments.

26. What is a "Key-Value pair"? The fundamental unit of JSON (e.g., "name": "Arjun").

Indexing & Performance

27. What is a Collection Scan? When the database reads every document because no index was available.

28. How do indexes improve performance? They allow the database to locate data without scanning the
whole collection.

29. What does the explain("executionStats") command show? It provides detailed metrics like execution
time and the number of documents scanned.

30. What is a Compound Index? An index based on multiple fields.

31. What is a TTL Index? A "Time To Live" index that automatically deletes documents after a certain period.

32. What is a Sparse Index? An index that only contains entries for documents that have the indexed field.

33. Can over-indexing hurt performance? Yes, it slows down write operations because every index must
be updated.

DynamoDB & Vector DBs

34. What is a Partition Key in DynamoDB? The attribute used to determine which partition the data is
stored in.

35. What is a Sort Key? An optional attribute used to group items with the same partition key.

36. Difference between Global Secondary Index (GSI) and Local Secondary Index (LSI)? GSI can use
any partition/sort key; LSI must use the same partition key as the table.

37. What is DynamoDB "On-Demand" mode? A billing option where you pay only for the requests you
make.

38. What is a Vector Embedding? The process of converting text or images into a list of numbers (a vector).

39. What is a Vector Database? A database optimized to store and search high-dimensional vector
embeddings.

40. What is "Nearest Neighbor Search"? Finding the vectors that are most similar to a query vector.

41. Why can't SQL databases easily handle vector search? Traditional indexes (B-Trees) aren't designed
for multi-dimensional similarity math.

42. Mention a popular Vector Database. Pinecone, Milvus, or Weaviate.

Comparison & General

43. When should you choose SQL over NoSQL? When data is highly structured and complex joins/strict
consistency are required.

44. When should you choose NoSQL over SQL? When data is unstructured, the schema changes often, or
extreme horizontal scale is needed.

45. What is a Schema-less database? A database where documents in the same collection can have
different fields.

46. What is the "N+1" query problem? A performance issue where an application makes many small