📘 DBMS – 2 Marks Answers
1. Define Functional Dependency and distinguish Trivial from Non-Trivial Dependency.
Answer:

A Functional Dependency (FD) shows a relationship between two attributes where one attribute determines another.

It is written as:

A → B means A determines B.

Example:

Student_ID → Student_Name

If we know the Student ID, we can find the student's name.

Type Meaning Example
Trivial FD Right-side attribute is already part of the left side {A, B} → A
Non-Trivial FD Right-side attribute is not part of the left side A → B
🧠 Remember:

Trivial = Already Included
Non-Trivial = New Information
2. Define SQL Constraints and list any four types.
Answer:

SQL Constraints are rules applied to table columns to maintain correct and valid data.

Four types are:

PRIMARY KEY → Uniquely identifies a row

FOREIGN KEY → Connects two tables

NOT NULL → Does not allow empty values

UNIQUE → Does not allow duplicate values
🧠 Remember:

P-F-N-U

> Primary → Foreign → Not Null → Unique
3. Define Relational Algebra and list any four fundamental operations.
Answer:

Relational Algebra is a procedural query language used to retrieve and manipulate data from relational tables.

Four fundamental operations are:

  1. SELECT (σ) → Selects required rows
  2. PROJECT (π) → Selects required columns
  3. UNION (∪) → Combines rows from two compatible tables
  4. SET DIFFERENCE (−) → Finds rows present in one table but not another
🧠 Remember:

S-P-U-D

> Select → Project → Union → Difference
4. List any four SQL String Functions and state their purposes.
Answer:
Function Purpose
UPPER() Converts text to uppercase
LOWER() Converts text to lowercase
LENGTH() Finds the length of a string
CONCAT() Joins two or more strings

Example:

UPPER('arun')

Result: ARUN

🧠 Remember:

Upper → Lower → Length → Join
5. Define ACID Properties and list the four transaction properties.
Answer:

ACID properties ensure that database transactions are performed safely and reliably.

Property Simple Meaning
Atomicity All operations happen or none happen
Consistency Database remains correct
Isolation Transactions do not interfere with each other
Durability Committed data is permanently saved
🧠 Super Easy Memory:

A-C-I-D

> All or Nothing
Correct
Independent
Don't Lose
6. Define SQL JOIN and list any four types of joins.
Answer:

A JOIN is used to combine data from two or more tables using a related column.

Four types:

  1. INNER JOIN → Returns matching rows
  2. LEFT JOIN → All rows from left table + matching rows from right
  3. RIGHT JOIN → All rows from right table + matching rows from left
  4. FULL OUTER JOIN → All rows from both tables
🧠 Remember:

Inner = Matching

Left = Everything Left

Right = Everything Right

Full = Everything Both
7. State the purposes of NOT NULL, UNIQUE and CHECK constraints.
Answer:
Constraint Purpose
NOT NULL Ensures a column cannot have an empty/NULL value
UNIQUE Ensures values are not duplicated
CHECK Ensures values satisfy a specified condition

Example:

Age INT CHECK (Age >= 18)

This allows only age 18 or above.

🧠 Remember:

> NOT NULL → Must have value

> UNIQUE → No duplicate

> CHECK → Follow the rule
8. List ISAM and B-Tree as indexing structures and state one feature of each.
Answer:

ISAM (Indexed Sequential Access Method):

Stores records in a sorted/indexed order.

Good for sequential and direct access.

B-Tree:

A balanced tree structure used for fast searching.

Maintains data in sorted order.

🧠 Remember:

ISAM → Sorted Index
B-Tree → Balanced Tree
9. Define Aggregate Functions and list any four examples.
Answer:

Aggregate functions perform calculations on multiple rows and return one result.

Examples:

COUNT() → Counts rows

SUM() → Calculates total

AVG() → Calculates average

MAX() → Finds highest value

MIN() is another commonly used aggregate function.

🧠 Remember:

C-S-A-M

> Count → Sum → Average → Maximum
10. Define Correlated Subquery and state its key characteristic.
Answer:

A Correlated Subquery is a subquery that depends on the outer query to get its values.

Key Characteristic:

It is generally executed once for each row processed by the outer query.

Simple Idea:

Outer Query
    ↓
Subquery needs outer query's value
    ↓
Result
🧠 Remember:

> Correlated = Connected

The inner query is connected to the outer query.
11. List any 4 principles of Codd's Relational Model.
Answer:

Four principles/rules of Codd's relational model are:

  1. Information Rule → Data is represented as values in tables.
  2. Guaranteed Access Rule → Every data value can be accessed using table name, primary key, and column name.
  3. Systematic Treatment of NULL → Missing or unknown values are handled systematically.
  4. Dynamic Online Catalog → Database structure/metadata is stored in tables and can be queried.
🧠 Remember:

I-G-N-C

> Information → Guaranteed Access → NULL → Catalog
12. List any four SQL Date and Time Functions with their purposes.
Answer:
Function Purpose
CURRENT_DATE Returns the current date
CURRENT_TIME Returns the current time
CURRENT_TIMESTAMP Returns current date and time
EXTRACT() Extracts a part such as year, month, or day
🧠 Remember:

> Date → Time → Both → Extract
13. Define Referential Integrity and state its role in relational databases.
Answer:

Referential Integrity ensures that a Foreign Key value must match an existing Primary Key value in the referenced table, or be NULL where permitted.

Example:

If:

Department Table
Department_ID = 10

Then a student can have:

Department_ID = 10

But not:

Department_ID = 99

if department 99 does not exist.

🧠 Remember:

> Foreign Key must point to a valid record.

Referential Integrity = No Broken Links 🔗
14. Differentiate between COUNT() and SUM() Aggregate Functions with examples.
Answer:
COUNT() SUM()
Counts the number of rows/values Calculates the total of numeric values
Example: COUNT(Mark) Example: SUM(Mark)

Example:

Suppose marks are:

80, 70, 90

COUNT(Mark)

Result: 3

SUM(Mark)

Result: 240

🧠 Remember:

> COUNT = How many?

> SUM = How much total?
15. Define Subquery and state its applications in SQL.
Answer:

A Subquery is a query written inside another SQL query.

Example:

SELECT Name
FROM Student
WHERE Mark > (SELECT AVG(Mark) FROM Student);

The inner query finds the average mark, and the outer query finds students above that average.

Applications:

Comparing values

Filtering records

Finding calculated results

Checking conditions
🧠 Remember:

> Subquery = Query inside Query
16. List Primary, Secondary and Multilevel Indexing Techniques.
Answer:

1. Primary Index

An index created using the primary key or ordering key of a table.

2. Secondary Index

An index created on a non-ordering attribute to provide faster searching.

3. Multilevel Index

An index where another index is created on top of an existing index, forming multiple levels.

Simple Diagram:

Multilevel Index 
Level 2 Index
      ↓
Level 1 Index
      ↓
    Data
🧠 Remember:

Primary → Main key
Secondary → Other field
Multilevel → Index on index
17. List any four SQL Numeric Functions with their purposes.
Answer:
Function Purpose
ABS() Returns absolute value
ROUND() Rounds a number
CEIL() / CEILING() Rounds up to the next integer
FLOOR() Rounds down to the previous integer

Example:

ROUND(25.67)

Result:

26
🧠 Remember:

> ABS → ROUND → CEIL → FLOOR

Think:

Value → Round → Up → Down
18. State the distinction between Trivial and Non-Trivial Functional Dependencies.
Answer:
Trivial FD Non-Trivial FD
Right-side attributes are already included in left side Right-side attribute is not included in left side
{A, B} → A A → B
Gives no new information Gives new information
🧠 Easiest Way:

Suppose:

{Student_ID, Name} → Name

Name is already on the left side.

✅ Trivial

But:

Student_ID → Name

Name is not on the left side.

✅ Non-Trivial
🧠 Final Memory:

> TRIVIAL = Already there

> NON-TRIVIAL = New information
@Thinking