K.S.

RANGASAMY COLLEGE OF TECHNOLOGY, TIRUCHENGODE -637 215

(Autonomous)
B.E. / B.TECH. SEMESTER EXAMINATION - MAY / JUNE- 2025
COMPUTER SCIENCE AND ENGINEERING
(ARTIFICIAL INTELLIGENCE AND MACHINE LEARNING)
60 AM 403 - DATABASE MANAGEMENT SYSTEMS

PART - A (10 × 2 = 20 Marks)

1. Main characteristics of the database approach 2

DBMS uses metadata, supports abstraction, and ensures integrity. Traditional systems
are file-based with no such features.
2. Compare strong entity set and weak entity set with example. 1
Strong Entity: Has a primary key (e.g., Student(Student_ID, Name)).
Weak Entity: No primary key; depends on a strong entity (e.g., Dependent(Student_ID,
Dependent_Name)).
3. List two reasons why null values might be introduced into the database. 2
a. Data is unknown.
b. Data is not applicable.
4. A functional dependency F said to be minimal – Give a reason. 2
A functional dependency is minimal if it cannot be reduced further without changing the
closure of the dependency set.
5. Highlights of the popular RAID levels 1 and level 5. 1
RAID 1: Mirroring for redundancy. 1
RAID 5: Block-level striping with parity for performance and fault tolerance.
6. Differentiate B-tree and B+-tree. Why is a B+-tree usually preferred? 1
B-Tree stores data in internal and leaf nodes; 1
B+-Tree stores data only in leaf nodes, making range queries faster
7. Differentiate between serial schedule and serializable schedule. 1
Serial: Executes one transaction at a time. 1
Serializable: Appears equivalent to a serial schedule.
8. Purpose of the checkpoint mechanism. How often should checkpoints be 2
performed:
To reduce recovery time after failure. Should be performed periodically or based on a
threshold.
9. Compare the additional functions of a DBMS to those of a centralized DBMS. 2
Centralized DBMS lacks replication and distributed capabilities present in modern
DBMS systems.
10. Goals or tasks that data mining attempts to facilitate. 2
Classification, clustering, prediction, association rules, and anomaly detection

PART - B (4 × 15 = 60 Marks)
11. a. Difference between Physical Level, Conceptual Level and View Level
Physical Level: How the data are actually stored, File Organizations, Indexing 3
Conceptual Level: What data are stored, What relationship exists among the data 2
View Level: Who are the users of the data, Highest Level of Data abstraction 2
b. Types of End users:
Casual users – occasional queries. 2
Naive users – use forms. 2
Application programmers – develop applications. 2
DBAs – manage the DB. 2
Or
12. a. Relational Algebra operations
i. Select (σ): 2
Chooses a subset of tuples (rows) from a relation that satisfy a given condition.
σ_predicate(Relation)
Ex: σ_Major='CS'(Students)
ii. Project (π): 2
Selects specific attributes (columns) from a relation, eliminating duplicates.
 π_{attribute_list}(Relation)
Ex: π_{Name, Major}(Students)
iii. Cartesian Product (×) 2
Combines each tuple from the first relation with every tuple from the second relation.
Relation1 × Relation2
Ex: Students × Courses
iv. Join (⋈) 2
Combines related tuples from two relations based on a join condition (typically a natural

Relation1 ⋈_condition Relation2

join that matches common attributes).

Ex: Enrollments ⋈ Courses

b. i.Find all loan numbers with a loan value greater than $10,000. 2
σ_amount>10000(loan)
ii. Find the names of all depositors who have an account with a 2

π_customer_name(σ_balance>6000(account ⨝ depositor))
value greater than $6,000.

iii. Find the names of all depositors who have an account with a 3

π_customer_name(σ_branch_name='Uptown' ∧ balance>6000(account ⨝ depositor))

value greater than $6,000 at the "Uptown" branch.

13. a. i. Aggregate functions and grouping. 2

Aggregate functions perform calculations on sets of values, returning a single value.
Grouping allows you to divide rows into groups and apply aggregate functions to each
group.
COUNT() - Counts the number of rows
SUM() - Calculates the sum of values
AVG() - Calculates the average of values
MIN()/MAX() - Finds the minimum/maximum value
STDDEV()/VARIANCE() - Statistical functions
Grouping Options:
GROUP BY - Groups rows sharing a property
HAVING - Filters groups (like WHERE but for groups)
ROLLUP - Creates subtotals and grand totals
CUBE - Generates all possible grouping combinations
GROUPING SETS - Specifies multiple grouping sets
ii. Triggers. 2
Triggers are stored procedures that automatically execute in response to specific events
on a table or view.
Types: Before, After, Instead of
Events: Insert, update, delete
iii. Assertions and how they differ from triggers 2
Assertions are constraints that specify conditions that must always be true in the
database.
Declarative - specify what must be true, not how to enforce it
Checked at the end of any transaction that might violate them
Not widely implemented in most DBMS (unlike triggers)
iv. Views and their updatability 2
Views are virtual tables representing the result of a stored query.
It's based on a single table
Contains all NOT NULL columns (unless they have defaults)
Has no DISTINCT, GROUP BY, HAVING clauses
Has no aggregate functions
Has no subqueries in SELECT that reference the same table
Doesn't use set operations (UNION, INTERSECT, etc.)

b. i. Insert (24556, Turnamian, Finance, 98000). 2

INSERT INTO Employee (id, name, department, salary)
VALUES (24556, 'Turnamian', 'Finance', 98000);
ii. Delete record 2. 3
DELETE FROM Employee
 WHERE id = 15151; 2
iii. Insert (34556, Thompson, Music, 67000).
INSERT INTO Employee (id, name, department, salary)
VALUES (34556, 'Thompson', 'Music', 67000);
1. Header
2. record 0: 10101 Srinivasan Comp. Sci. 65000
3. record 1: 24556 Turnamian Finance 98000
4. record 2: 34556 Thompson Music 67000
5. record 3: (empty)
6. record 4: (empty)…..
Or
14. a. Query to find companies whose employees earn a higher salary, on average, than the
average salary at "First Bank Corporation".
i. Using SQL functions as appropriate 4
SELECT company_name
FROM works
GROUP BY company_name
HAVING AVG(salary) > (
SELECT AVG(salary)
FROM works
WHERE company_name = 'First Bank Corporation'
);
ii. Without using SQL functions
SELECT W1.company_name 4
FROM works W1
GROUP BY W1.company_name
HAVING
(SUM(W1.salary) * 1.0 / COUNT(W1.salary)) > (
SELECT SUM(W2.salary) * 1.0 / COUNT(W2.salary)
FROM works W2
WHERE W2.company_name = 'First Bank Corporation' );

b. Relation Dependencies 4
AB⁺ :

AB → C ⇒ Add C
Start with AB:

Now we have: A, B, C
CD → E needs C and D (but D not yet in AB⁺)
DE → B needs D and E (don’t have either yet)
AB⁺ = {A, B, C} → Not a candidate key because we don’t get D and E
ABD⁺:

AB → C ⇒ Add C
Start with ABD:

CD → E (C + D present) ⇒ Add E
Now: A, B, D, C

DE → B (D + E present) ⇒ Already have B

ABD⁺ = {A, B, C, D, E} ⇒ All attributes of R

Now we have: A, B, C, D, E

So, ABD is a candidate key.

15. a. Transaction state: 8

b. Phantom phenomenon 7
A phantom occurs when a transaction re-reads a set of rows using the same query and
finds new rows ("phantoms") that weren't there before.
Caused by inserts or deletes by other transactions during execution.
Even if two-phase locking (2PL) is used on individual rows, new rows satisfying the
query condition may appear.
2PL locks existing rows (shared/exclusive), not the range of rows (i.e., gaps between
values).
Hence, another transaction can insert a "phantom" row in the range.
T1: Reads all employees with salary > 50,000.
T2: Inserts a new employee with salary = 60,000.
T1: Re-reads and sees this new row — inconsistent view.
Use predicate locking or index-range locking (in Serializable isolation level) to
prevent phantom rows.
Or
16. a. Rigorous two-phase locking 8
Strict Serializability (Strictest Form of Isolation)
– Transactions appear to execute one after another (no overlapping effects).
Prevents Cascading Aborts
– Since data is not released early, other transactions don’t read uncommitted data.
Simpler Recovery

Rigorous 2PL: Holds all locks till commit ⇒ Strict serializability + no cascading aborts
– Rollback is easy as no other transaction depends on uncommitted changes.

Better than basic/strict 2PL for consistency, recovery, and concurrency control
b. Stable storage 7
Stable storage refers to a hypothetical storage medium that never loses data, even
during power failures, crashes, or hardware faults.
In real life, no storage device (like SSDs, HDDs, RAM) is truly immune to:
o Disk failures
o Power outages
o Bit rot
o Wear and tear
Hence, stable storage is theoretical—we can only approximate it, not perfectly
achieve it.
To simulate stable storage, DBMSs use fault-tolerant techniques such as:
Redundancy
Write-Ahead Logging (WAL)
Checkpointing
Replication
Atomic Writes & Recovery Protocols

17. a. Horizontal partitioning 8

Splits a relation row-wise into subsets of tuples (rows) based on a predicate
(condition). All partitions have the same schema (same columns)
SELECT * FROM Employee WHERE Dept = 'HR';
 All horizontal partitions should be: Disjoint (no overlapping rows)
Complete (together cover all rows)
b. Two-phase commit protocol 7

Ensures atomicity of transactions across multiple sites in a distributed database.

All sites either commit or abort together.
Phase1 – Prepare phase
Phase 2: Commit / Abort Phase
Or
18. a. Different phases of the knowledge discovery 8
1. Selection
o Choose relevant data from the database.
o What data do we need?
2. Preprocessing
o Clean data (handle missing, noisy, or inconsistent data).
o Make it usable.
3. Transformation
o Convert data into suitable format (e.g., normalization).
o Structure the data for mining.
4. Data Mining
o Apply algorithms to discover patterns/trends.
o Core of KDD.
5. Interpretation/Evaluation
o Make sense of patterns, remove irrelevant ones.
o What is useful?
6. Knowledge Presentation
o Visualize and present mined knowledge clearly.
o Show insights to users.
b. Data warehouse 7
Roll-up (Consolidation)
Increase level of aggregation.
e.g., Day → Month → Quarter → Year
Drill-down
Decrease level of aggregation.
e.g., Year → Month → Day
Slice
Select a single dimension value.
e.g., Sales for Region = 'South'
Dice
Select a sub-cube by multiple dimensions.
e.g., Sales for Region = 'East' and Year = 2023
Pivot (Rotate)
Reorient the cube view.
 e.g., Swap rows and columns for better analysis
Drill-across
Analyze data across fact tables using common dimensions.
e.g., Compare sales vs inventory across regions
Drill-through
Go from summary to detailed transactional data.
e.g., Click on total sales → See invoice details
Multidimensional Data Warehouse Illustration Example:
Assume a Sales Data Cube with dimensions:
 Time (Year, Quarter, Month)
 Product (Category, Brand)
 Location (Country, Region)

PART - C (1 × 20 = 20 Marks)
a. RAID Classification 4
Condition Analysis according to Repeat_Customer:
SELECT City, AVG(Age) AS AvgAge FROM Customers WHERE Repeat_customer =
'Yes' GROUP BY City;
Simple Query:
SELECT COUNT(*) FROM Customers WHERE Repeat_customer = 'Yes'; 4
b. B+ Tree Construction:
i. Four Nodes 4

ii. Six Nodes 4

iii. Eight Nodes

Prepared By HoD