V.S.

B ENGINEERING COLLEGE,KARUR-639111
(An Autonomous Institution,Affiliated to Anna University - Chennai)
Academic Year : 2025-2026 ( Even Semester)
INTERNAL TEST - 2 ANSWER KEY
DEGREE & BRANCH : B.E & SECOND CSE(AIML) A, B / FOURTH SEMESTER
COURSE CODE & TITLE: 23ADT301 –DATABASE DESIGN AND MANAGEMENT

ANSWER ALL Questions

Marks
[Link]. PART – A (5 × 2 = 10 Marks)
allotted
1 How does normalization help in reducing update anomalies?
2 marks
Eliminates data redundancy by organizing data into multiple related tables.
Prevents insertion anomalies (avoids need for unnecessary data while inserting).
Avoids deletion anomalies (deleting one record won’t remove important unrelated data).
Reduces update anomalies (same data not repeated, so fewer inconsistent updates).
Ensures data integrity and consistency through proper functional dependencies.
2 2 marks
Define aggregation in the EER model.
Aggregation is an abstraction technique in the EER model.
It allows a relationship set to be treated as a higher-level entity.
Used to model relationships between relationships.
Helps represent complex real-world scenarios more clearly.
Example: Treating “Works_on” (Employee–Project) as an entity for another relationship.
3 2 marks
What is the lost update problem in concurrent transactions?
Occurs when two transactions update the same data simultaneously.
One transaction’s update is overwritten by another.
Leads to loss of data consistency.
Happens due to lack of proper concurrency control.
Prevented using techniques like locking or timestamp ordering.
4 2 marks
List the ACID properties of a transaction.
Atomicity: Transaction is all-or-nothing.
Consistency: Maintains database correctness before and after transaction.
Isolation: Transactions execute independently without interference.
Durability: Changes are permanently stored after commit.
 Ensures reliable and safe transaction processing.
5 2 marks
What are the advantages of using user-defined types in databases?
Allows creation of custom data types suited to application needs.
Improves data abstraction and modeling flexibility.
Supports complex data structures (e.g., objects, multimedia).
Promotes code reusability and modular design.
Enhances readability and maintainability of database schema.
PART – B (1 × 8 = 8 Marks)
6 (a) Describe the role and importance of reference types in object-oriented 8 Marks
databases with examples.

A reference type is a data type used to store the identity (OID) of an object rather
than its actual [Link] allows one object to refer to another object.

Role of Reference Types

Object Linking used to establish relationships between objects and Similar to
pointers or object references
Identity-Based Access :Objects are accessed using Object Identifiers (OIDs)
Ensures uniqueness of objects
Support for Complex Structures :Enables modeling of real-world relationships
Example: Student → Department

Importance of Reference Types

Avoids data duplication
Improves data consistency
Enables efficient navigation between objects
Supports object sharing
Maintains relationships directly (no joins required)
Enhances performance in complex queries

Reference types play a crucial role in OODBMS by:

Enabling direct object relationships,improving performance and data integrity
,they are essential for modeling complex, real-world object interactions.

OR
6 (b) Define Object Identifiers (OIDs) in object-oriented databases. How do OIDs (8 marks)
differ from primary keys in relational databases?

An Object Identifier (OID) is a unique system-generated identifier assigned to each

object in an object-oriented [Link] uniquely identifies an object independent of
its attribute values.
 Characteristics of OIDs :
Uniqueness: Each object has a distinct OID
System-generated: Automatically assigned by DBMS
Immutable: Does not change during the object’s lifetime
Invisible to users: Usually not directly accessed
Identity-based: Identifies object, not its values
Role of OIDs

Used to access and reference objects directly,helps in maintaining relationships

between objects,supports object sharing and navigation,avoids data duplication

OID :
OIDs provide true object identity
Primary keys provide value-based identification
OIDs improve performance (direct access)
Primary keys require comparison of values
Provide unique and stable identification
Enable efficient object referencing
Unlike primary keys, OIDs are independent, internal, and immutable identifiers

OIDs (OODBMS) Primary Keys (RDBMS)

Feature
Basis System-generated User-defined attribute(s)
identifier
Dependency Independent of data Based on attribute values
values
Visibility Hidden from users Visible and used in
queries
Mutability Immutable May change (rare but
possible)
Purpose Object identity Tuple identification
Access Direct object reference Requires joins/queries
PART – C (2 × 16 = 32 Marks)
7 (a) What is aggregation in the EER model? How is it mapped to the relational (16
model? Marks)
Mapping EER Model to Relations :
Mapping of Specialization or Generalization:
The two main options are to map the whole specialization into a single table, or
to map it into multiple tables. Within each option are variations that depend
on the constraints on the specialization/generalization.

Options for Mapping Specialization or Generalization:

Convert each specialization with m sub_classes {S1, S2, … , Sm} and (generalized)
super-class C, where the attributes of C are {k, a1, … , an} and k is the (primary)
key, into relation schemas using one of the following options:
[Link] relations—super_class and sub_classes:
Create a relation L for C with attributes Attrs(L) = {k, a1, … , an} and PK(L) = k.
Create a relation Li for each subclass Si, 1 ≤ i ≤ m, with the attributes
Attrs(Li) = {k} ∪ {attributes of Si} and PK(Li) = k. This option works for any
specialization (total or partial, disjoint or overlapping).

[Link] relations—subclass relations only:

Create a relation Li for each subclass Si, 1 ≤ i ≤ m, with the attributes
Attrs(Li) = {attributes of Si} ∪ {k, a1, … , an} and PK(Li) = k. This option only
works for a specialization whose sub_classes are total (every entity in the
super_class must belong to (at least) one of the sub_classes). Additionally, it is
only recommended if the specialization has the disjointedness constraint (see
Section 4.3.1). If the specialization is overlapping, the same entity may be
duplicated in several relations.

[Link] relation with one type attribute:

Create a single relation L with attributes Attrs(L) = {k, a1, …, an} ∪ {attributes of
S1} ∪ … ∪ {attri-butes of Sm} ∪ {t} and PK(L) = k. The attribute t is called a
type (or discriminating) attribute whose value indicates the subclass to which each
tuple belongs, if any. This option works only for a specialization whose sub-
classes are disjoint, and has the potential for generating many NULL values if
many specific (local) attributes exist in the subclasses.

[Link] relation with multiple type attributes:

Create a single relation schema L with attributes Attrs(L) = {k, a1, …, an} ∪
{attributes of S1} ∪ … ∪ {attributes of Sm} ∪ {t1, t2, …, tm} and PK(L) = k.
Each ti, 1 ≤ i ≤ m, is a Boolean type attribute indicating whether or not a tuple
belongs to subclass Si. This option is used for a specialization whose sub-
classes are overlapping (but will also work for a disjoint specialization).

Mapping of Shared Subclasses (Multiple Inheritance)

These classes must all have the same key attribute; otherwise, the shared subclass
would be modeled as a category (union type) .
 Mapping of Categories (Union Types)
A category (or union type) is a subclass of the union of two or more super_classes
that can have different keys because they can be of different entity types . An
example is the OWNER category , which is a subset of the union of three entity
types PERSON, BANK, and COMPANY. The other category in that figure,
REGISTERED_VEHICLE, has two super_classes that have the same key attribute.

For mapping a category whose

defining super_classes have different keys, it is customary to specify a new key
attribute, called a surrogate key, when creating a relation to correspond to the union
type. The keys of the defining classes are different, so we cannot use any one of
them exclusively to identify all entities in the relation. In our example we create a
relation OWNER to correspond to the OWNER category

OR
7 (b) Explain Armstrong’s inference rules and their significance in functional 8 marks
dependencies.

Definition of Functional Dependency:

A functional dependency, denoted by X → Y, between two sets of
attributes X and Y that are subsets of R specifies a constraint on the possible
tuples that can form a relation state r of R. The constraint is that, for any two
tuples t1 and t2 in r that have t1[X] = t2[X], they must also have t1[Y] = t2[Y].

This means that the values of the Y component of a tuple in r depend on, or are
determined by, the values of the X component; alternatively, the values of the X
component of a tuple uniquely (or functionally) determine the values of the Y
component. We also say that there is a functional dependency from X to Y, or that
Y is functionally dependent on X. The abbreviation for functional dependency is
FD or f.d. The set of attributes X is called the left-hand side of the FD, and Y is
called the right-hand side.
 A functional dependency is a property of the semantics or meaning of the
attributes. The database designers will use their understanding of the semantics of
the attributes of R—that is, how they relate to one another—to specify the
functional dependencies that should hold on all relation states (extensions) r of R.
Relation extensions r(R) that satisfy the functional dependency constraints are
called legal relation states (or legal extensions) of R.

Inference Rules for Functional Dependencies :

An FD X → Y is inferred from or implied by a set of dependencies F specified on
R if X → Y holds in every legal relation state r of R; that is, when-ever r satisfies
all the dependencies in F, X → Y also holds in r.

In real life, it is impossible to specify all possible functional dependencies for a

given situation. For example, if each department has one manager, so that
Dept_no uniquely determines Mgr_ssn (Dept_no → Mgr_ssn), and a manager
has a unique phone number called Mgr_phone (Mgr_ssn → Mgr_phone), then
these two dependencies together imply that Dept_no → Mgr_phone.

Three rules IR1 through IR3 that are well-known inference rules for functional
dependencies.
They were proposed first by Armstrong (1974) and hence are known as
Armstrong’s axioms.
IR1 (reflexive rule)2: If X ⊇ Y, then X →Y.
IR2 (augmentation rule)3: {X → Y} |=XZ → YZ.
IR3 (transitive rule): {X → Y, Y → Z} |=X → Z.

There are three other inference rules that follow from IR1, IR2 and IR3. They are
as follows:
IR4 (decomposition, or projective, rule): {X → YZ} |=X → Y.
IR5 (union, or additive, rule): {X → Y, X → Z} |=X → YZ.
IR6 (pseudo_transitive rule): {X → Y, WY → Z} |=WX → Z.

8 (a) Analyze the concept of serializability in transaction processing. Explain 16 marks

conflict serializability using precedence graphs with a suitable example, and
compare it with view serializability.
Serializability
In DBMS, multiple transactions are executed concurrently to improve performance.
However, concurrent execution may lead to [Link] ensure correctness,
the concept of serializability is used. A schedule is said to be serializable if its result
is equivalent to some serial schedule of the same transactions.

Types of Serializability
Conflict Serializability
1. Execute all the operations of transaction T1 (in sequence) followed by all the
operations of transaction T2 (in sequence).
2. Execute all the operations of transaction T2 (in sequence) followed by all the
operations of transaction T1 (in sequence).
A schedule is conflict-serializable if it can be transformed into a serial schedule by
swapping non-conflicting operations.

Conflicting Operations:
Two operations conflict if:They belong to different [Link] operate on
the same data [Link] least one operation is a write.

Conflict Equivalence of Two Schedules:

Two schedules are said to be conflict

equivalent if the relative order of any two conflicting operations is the same in both
schedules.

a schedule are said to conflict if they belong to different transactions, access the
same database item, and either both are write_item operations or one is a
write_item and the other a read_item.If two conflicting operations are applied in
different orders in two schedules, the effect can be different on the database or on
the transactions in the schedule, and hence the schedules are not conflict equivalent.

Testing for Serializability of a Schedule :

 The algorithm looks at only the read_item and write_item operations in a schedule
to construct a precedence graph (or serialization graph), which is a directed graph
G = (N, E) that consists of a set of nodes N = {T1, T2, … , Tn } and a set of
directed edges E = {e1, e2, … , em }

For each transaction Ti participating in schedule S, create a node labeled

Ti in the precedence graph.
2. For each case in S where Tj executes a read_item(X) after Ti executes a
write_item(X), create an edge (Ti → Tj) in the precedence graph.
3. For each case in S where Tj executes a write_item(X) after Ti executes a
read_item(X), create an edge (Ti → Tj) in the precedence graph.
4. For each case in S where Tj executes a write_item(X) after Ti executes a
write_item(X), create an edge (Ti → Tj) in the precedence graph.
5. The schedule S is serializable if and only if the precedence graph has no
cycles.
The precedence graph :

Comparison: Conflict vs View Serializability :

Feature Conflict Serializability View Serializability
Definition Based on conflicting Based on final view
operations
Checking Easy (precedence graph) Difficult
Practical Use Widely used Rarely used
Coverage Subset Super set
Complexity Low High
OR
8 (b) Define schedules in [Link] different types of schedules with 8 marks
examples(serial,non-serial,conflict-serializable)

A schedule in DBMS is the sequence or order of execution of operations (read,

write, commit, abort) from one or more transactions, while maintaining the order of
operations within each transaction.
Types of Schedules
1. Serial Schedule
A schedule in which transactions are executed one after another, without any
[Link] overlapping of transactions,Maintains consistency,Simple and easy
 to understand,Low concurrency.
Let T1 and T2 be two transactions:
T1: R(A), W(A)
T2: R(B), W(B)
Serial Schedule:
T1 → T2
R(A) → W(A) → R(B) → W(B)

2. Non-Serial Schedule
A schedule where operations of multiple transactions are interleaved.
Transactions execute concurrently,improves performance and resource utilization,
may lead to inconsistency problems
R(A) → R(B) → W(A) → W(B)
(Operations of T1 and T2 are mixed)

3. Conflict-Serializable Schedule
A non-serial schedule that can be converted into a serial schedule by swapping non-
conflicting [Link] same result as some serial schedule,uses conflict
operations (R-W, W-R, W-W),Checked using precedence graph,must be a-cyclic.

Serial schedules ensure correctness but reduce concurrency

Non-serial schedules improve performance but may cause inconsistency
Conflict-serializable schedules provide a balance by ensuring correctness with
concurrency.
TOTAL NO. OF PAGES 10

Prepared by Approved by
Name &Signature of Faculty HoD/CSE