Solution

Typical DBMS query processing steps:

Scanning, parsing and validating → C

Query optimizer → A

Query code generator → D

Runtime database processor → B

Correct order:

C → A → D → B

Solution

In an unclustered B+ tree, retrieving records in sorted order requires scanning index entries and then fetching records from data pages.

Cost of scanning leaf level entries:

$F \times N$

Number of records:

$P \times N$

So, total cost:

$FN + PN = (F + P)N$

Final Answer: (b) (F + P) * N

Solution

Solution

A is correct because a database design is in BCNF when every relation schema in the design is in BCNF.

B is incorrect because BCNF removes dependency problems caused by non-super-key determinants.

C is correct because 3NF decomposition can be made lossless.

D is correct because 4NF deals with multivalued dependencies.

Solution

Find closure of $EC$:

$EC^+ = {E, C}$

Using $E \to A$,

$EC^+ = {E, C, A}$

Using $A \to B$,

$EC^+ = {E, C, A, B}$

Using $EC \to D$,

$EC^+ = {E, C, A, B, D}$

So, $EC$ determines all attributes.

Solution

A → False (only owner or DBA can grant privileges)

B → True

C → False (system error is not a malicious attack)

D → True

False statements:

A and C

Solution

In worst case, insertion causes splitting at every level.

There are 4 levels, so 4 new nodes may be created due to splitting.

If the root also splits, one extra new root node is created.

Maximum newly created nodes:

$4 + 1 = 5$

Solution

Solution

Solution

Relational algebra has the same expressive power as relational calculus only when relational calculus expressions are restricted to safe expressions.

Safe expressions ensure that the query result is finite and depends only on the database.

So,

Relational algebra is equivalent to domain relational calculus restricted to safe expressions.

Also,

Relational algebra is equivalent to tuple relational calculus restricted to safe expressions.

Therefore, $(c)$ and $(d)$ are correct.

Solution

In relational algebra, some operations are based on mathematical set theory.

Intersection is a set operation.

Cartesian product is also a set operation.

Join and project are relational algebra operations, but they are not basic mathematical set theory operations.

So, the operations included for mathematical set theory are:

$(b)$ Intersection

$(c)$ Cartesian product

Solution

If a relation is in $BCNF$, then it is also in $3NF$.

Also, if a relation is in $3NF$, then it is already in $2NF$.

So, if $R$ is in $BCNF$, then $R$ must be in $2NF$ and $3NF$.

Solution

Solution

A foreign key is used to create a relationship between two tables.

A foreign key in one table generally refers to the primary key of another table.

This ensures referential integrity between the two tables.

Solution

Solution

A candidate key must uniquely identify each tuple in a relation.

So, uniqueness is a property of candidate key.

Also, a candidate key must be minimal. It means no proper subset of it can uniquely identify tuples.

This property is called irreducibility.

Therefore, both $P$ and $Q$ are true.

Solution

Solution

A candidate key must uniquely identify each tuple in a relation.

So, uniqueness is a property of candidate key.

Also, a candidate key must be minimal. It means no proper subset of it can uniquely identify tuples.

This property is called irreducibility.

Therefore, both $P$ and $Q$ are true.

Solution

Solution

Hadoop ecosystem includes several tools used for big data storage and processing.

Map Reduce is used for distributed data processing.

HBase is a NoSQL database used in the Hadoop ecosystem.

Hive is used for querying large datasets stored in Hadoop.

Therefore, the common Hadoop tools are Map Reduce, HBase and Hive.

Solution

Solution

In the relational database model, data is stored in the form of relations or tables.

Complex data-types are not directly supported in the basic relational model because values are generally atomic.

Multivalued attributes are also not directly stored in a single attribute; they are usually represented by creating another relation.

Generalization relationships are mainly part of the ER model or enhanced ER model.

But associations between entities can be represented using relations, primary keys and foreign keys. Multiplicity like one-to-one or one-to-many can also be represented using constraints.

Therefore, the supported feature is associations with multiplicities.

Solution

Solution

Deleting tuple $(20,40)$ means employee id $20$ is deleted.

Since $Manager_id$ is a foreign key referencing $Employee_id$ with on-delete cascade, all tuples having $Manager_id=20$ must also be deleted.

So, tuple $(35,20)$ must be deleted.

Now, deleting employee id $35$ will again affect tuples having $Manager_id=35$.

So, tuple $(30,35)$ must also be deleted.

No tuple has $Manager_id=30$, so the cascade stops here.

Therefore, the other tuples that must be deleted are

$(35,20)$ and $(30,35)$

Solution

Solution

Solution

Solution

Solution

Solution

We need the number of distinct customers who placed orders, so we count unique customer_id from orders table.

Correct query:

Select COUNT(DISTINCT customer_id) As total_customers From orders;

Solution

Orders table usually contains:

customer_id → referencing Customers table

Thus it contains a foreign key.

Solution

From passage:

• customers can place multiple orders

• orders can contain multiple products

• representatives manage customer accounts

Thus correct statement:

Each sales representative can manage multiple customer accounts.

Solution

From passage:

• customers can place multiple orders

• orders can contain multiple products

• representatives manage customer accounts

Thus correct statement:

Each sales representative can manage multiple customer accounts.

Solution

One customer can place multiple orders.

Thus relationship:

Customer → Orders

$1 : N$

Solution

Solution

Solution

Solution

Solution

Solution

In DBMS, the query optimizer is responsible for choosing the most efficient way to execute a query.

It performs tasks like:

Rearranging operations

Choosing proper order of operations

Removing redundant operations

Using efficient algorithms and indexes

So, the required component is:

$query\ optimizer$

Solution

Tables are:

$R1(x,y)$ has $50$ tuples

$R2(y,z)$ has $30$ tuples

Natural join will be performed on the common attribute:

$y$

Maximum number of tuples will occur when every tuple of $R1$ matches with every tuple of $R2$ on attribute $y$.

So, maximum number of tuples:

$50 \times 30=1500$

Solution

The query first selects employees whose department number is either $D1$ or $D2$.

Then,

$group\ by\ DEPTNO$

means employees are grouped department-wise.

After that,

$having\ count(*)>5$

means only those department groups will be displayed whose employee count is greater than $5$.

So, the query displays the total number of employees of departments $D1$ and $D2$ only if their count is greater than $5$.

Solution

In the given ER diagram:

Entity $R_1$ has attributes $A$, $B$, and $C$.

Here, $B$ is shown as a double oval, so $B$ is a multivalued attribute.

A multivalued attribute requires a separate table.

So, for $R_1$:

One table for entity $R_1$

One separate table for multivalued attribute $B$

Entity $R_2$ has attributes $X$ and $Y$.

So, one table is required for $R_2$.

The relationship between $R_1$ and $R_2$ is $1:M$.

For a $1:M$ relationship, no separate relationship table is required. We can add the primary key of the $1$ side as a foreign key in the $M$ side table.

Total number of tables:

$1$ table for $R_1$

$1$ table for multivalued attribute $B$

$1$ table for $R_2$

Total:

$1+1+1=3$

Solution

Given FDs

$CH \rightarrow C$

$A \rightarrow BC$

$B \rightarrow CEH$

$E \rightarrow A$

$A \rightarrow EFG$

From

$A \rightarrow BC$

$A \rightarrow EFG$

we get

$A \rightarrow BCFG E$

Then

$B \rightarrow CEH$

Thus

$A^+ = {A,B,C,D,E,F,G,H}$

So $A$ is a candidate key.

Now check dependency

$E \rightarrow A$

Since $E$ is not a superkey, but RHS contains a prime attribute, it satisfies 3NF but violates BCNF.

Therefore the relation is

in 3NF but not in BCNF.

Solution

Solution

Solution

SQL (Structured Query Language) is used to:

• create tables

• insert data

• update data

• delete data

• query data

Thus its primary purpose is managing and manipulating relational databases.

Solution

Solution

Goal: Find a set of attributes whose closure covers all attributes of R = {A, B, C, D, E}.

Check (1) CD⁺:
From C → F we get F. D gives nothing further. So CD⁺ = {C, D, F}.
Missing A, B, E ⇒ not a key.

Check (2) EC⁺:
Start {E, C}. Using E → A ⇒ add A. Using A → B ⇒ add B. Using EC → D ⇒ add D.
Therefore EC⁺ = {E, C, A, B, D} = {A, B, C, D, E}. Covers all attributes of R ⇒ key.

Check (3) AE⁺:
A → B gives B; E → A adds nothing new. No way to reach C or D ⇒ not a key.

Check (4) AC⁺:
A → B gives B; C → F gives F. No E or D ⇒ not a key.

Answer: (2) EC

Note: F is outside R, so while C → F is valid, a key must cover only attributes of R. EC⁺ covers {A, B, C, D, E}.

Solution

Solution

To check conflict serializability we build precedence graph.

For $S_1$

Conflicts:

$T_1 \rightarrow T_3$
$T_3 \rightarrow T_2$
$T_2 \rightarrow T_1$

Cycle exists:

$T_1 \rightarrow T_3 \rightarrow T_2 \rightarrow T_1$

Therefore

$S_1$ not conflict-serializable

For $S_2$

Conflicts:

$T_3 \rightarrow T_1$
$T_2 \rightarrow T_1$
$T_3 \rightarrow T_2$

Graph has no cycle

Serializable order:

$T_3 \rightarrow T_2 \rightarrow T_1$

Thus

$S_2$ is conflict-serializable

Solution

Standard database design process:

Requirement Analysis

Conceptual Design

Logical Design

Physical Design

Thus order:

(D) → (B) → (C) → (A)

Solution

Solution

Solution

Functional dependencies:

$U \rightarrow V$

$VW \rightarrow X$

$Y \rightarrow W$

$X \rightarrow U$

Start with $UY$:

$U \rightarrow V$

$Y \rightarrow W$

Thus we get $V,W$

Then

$VW \rightarrow X$

Then

$X \rightarrow U$

So closure:

$UY^+ = {U,V,W,X,Y}$

Add $Z$ to cover all attributes.

Thus minimal key:

$UYZ$

Similarly:

$VYZ$ and $XYZ$

Thus candidate keys:

$UYZ,; VYZ,; XYZ$

Solution

Relational databases (MySQL, PostgreSQL, Oracle etc.) store data in relations (tables).

Each table consists of:

Rows → tuples / records

Columns → attributes / fields

Thus relational model structure:

tables with rows and columns

Solution

Solution

Solution

For a relation on ${1,2,3,4}$ to be reflexive, it must contain

$(1,1),(2,2),(3,3),(4,4)$

Check each:

$R_1$ → missing $(3,3)$ ✘

$R_2$ → missing $(3,3),(4,4)$ ✘

$R_3$ → contains $(1,1),(2,2),(3,3),(4,4)$ ✔

$R_4$ → missing all diagonal pairs ✘

$R_5$ → contains all $(1,1),(2,2),(3,3),(4,4)$ ✔

Thus reflexive relations:

$R_3$ and $R_5$

Solution

If two transactions update the same data item without concurrency control, then many problems may occur.

Dirty read problem can occur when one transaction reads uncommitted data of another transaction.

Lost update problem can occur when one transaction update is overwritten by another transaction.

Inconsistent database state can occur because uncontrolled concurrent execution may violate consistency.

But transaction failure is not necessarily due to concurrency control absence.

So, correct problems are:

(a), (b) and (d)

Solution

Solution

Check dependency preservation

Given FDs:

$P \rightarrow Q$

$Q \rightarrow R$

$R \rightarrow S$

$S \rightarrow Q$

Decomposed relations:

$R_1(P,Q)$

$R_2(Q,R)$

$R_3(Q,S)$

Each dependency is preserved:

$P \rightarrow Q$ → in $R_1$

$Q \rightarrow R$ → in $R_2$

$R \rightarrow S$ → derived via $Q$ in $R_3$

$S \rightarrow Q$ → in $R_3$

Thus dependency preserving

Check lossless join:

Common attribute between relations is Q, which functionally determines other attributes through dependencies.

Hence not lossless join.

Solution

Initial head position:

$101$

Direction is towards higher cylinder numbers.

Request sequence in sorted order:

$45,87,123,197,198,250,280$

Using C_LOOK, first serve higher side requests:

$101 \to 123 \to 197 \to 198 \to 250 \to 280$

Then jump to lowest request:

$280 \to 45$

Then continue:

$45 \to 87$

Total head movement:

$=|123-101|+|197-123|+|198-197|+|250-198|+|280-250|+|45-280|+|87-45|$

$=22+74+1+52+30+235+42$

$=456$

Solution

Solution

Given functional dependency set:

$F={A\rightarrow BC,\ D\rightarrow E,\ BC\rightarrow D,\ A\rightarrow D}$

First split right side of $A\rightarrow BC$:

$A\rightarrow B$

$A\rightarrow C$

So,

$F={A\rightarrow B,\ A\rightarrow C,\ D\rightarrow E,\ BC\rightarrow D,\ A\rightarrow D}$

Now check whether $A\rightarrow D$ is redundant or not.

From:

$A\rightarrow B$

$A\rightarrow C$

we get:

$A\rightarrow BC$

And given:

$BC\rightarrow D$

So,

$A\rightarrow BC\rightarrow D$

Therefore,

$A\rightarrow D$

can be derived from other dependencies, so it is redundant.

Remove $A\rightarrow D$.

Hence minimal cover is:

$F'={A\rightarrow B,\ A\rightarrow C,\ BC\rightarrow D,\ D\rightarrow E}$

Solution

The subquery is:

select f.sex from professor f where f.empid = s.advisor

Here, the subquery uses s.advisor from the outer query.

So, it is a correlated subquery.

Therefore, S1 is true and S2 is false.

Also, advisor refers to empid of professor relation. Since empid is unique, the subquery returns only one professor’s sex.

So,

s.sex = ALL (single value)

and

s.sex = ANY (single value)

will give the same result.

Therefore, S4 is true and S3 is false.

Solution

To find the primary key, find closure of $A$.

Given:

$A\rightarrow BC$

So,

$A^+={A,B,C}$

Also,

$A\rightarrow D$

So,

$A^+={A,B,C,D}$

And,

$D\rightarrow E$

So,

$A^+={A,B,C,D,E}$

Since $A^+$ contains all attributes of relation $R$, $A$ is a candidate key.

Therefore, the primary key is:

$A$

Solution

We need total credits of courses for each department.

So, we must use:

sum(credits)

Also, department and course must be joined using:

deptid = deptno

Then grouping must be done department-wise using:

group by deptid, name

Option (a) correctly uses join, sum and group by.

Option (b) uses count(credits), so it counts courses, not total credits.

Option (c) is syntactically incorrect.

Option (d) uses the join condition in having instead of where, so it is not the correct standard query.

Solution

$A$

Since the primary key contains only one attribute, there is no partial dependency.

So, relation $R$ is in $2NF$.

Now check for $3NF$.

Given dependency:

$D\rightarrow E$

Here, $D$ is not a super key and $E$ is a non-prime attribute.

So, relation is not in $3NF$.

Therefore, the highest normal form of relation $R$ is:

$2NF$

Solution

We need departments whose total course credits are greater than 40.

First, department and course must be joined:

deptid = deptno

Then courses must be grouped department-wise:

group by deptid, name

After grouping, total credits must be checked using HAVING:

having totalcredits >40

Option (d) correctly uses:

where deptid = deptno
group by deptid, name
having totalcredits >40

Solution

We need to check which functional dependency can be derived from the other dependencies.

Given:

$A\rightarrow BC$

and

$BC\rightarrow D$

So, by transitivity:

$A\rightarrow BC\rightarrow D$

Therefore,

$A\rightarrow D$

can be derived from $A\rightarrow BC$ and $BC\rightarrow D$.

So, $A\rightarrow D$ is redundant.

Solution

Solution

The outer query selects students.

Inside the query:

EXISTS checks whether there is at least one course from department number 5.

The inner EXISTS checks whether the same student has enrolled in that course.

So the query means:

Find students who have enrolled in at least one course offered by department number 5.

It does not check all courses. For all courses, double NOT EXISTS would be required.

Solution

For $R_1(A,B,C)$:

Given:

$A\rightarrow B$

$A\rightarrow C$

So,

$A^+={A,B,C}$

Therefore, $A$ is a key of $R_1$.

Since all dependencies have left side as key $A$, $R_1$ is in $3NF$.

Now for $R_2(A,D,E)$:

Given:

$A\rightarrow D$

$D\rightarrow E$

Here,

$A^+={A,D,E}$

So, $A$ is a key of $R_2$.

But dependency $D\rightarrow E$ violates $3NF$, because $D$ is not a super key and $E$ is a non-prime attribute.

Since the key $A$ is a single attribute, there is no partial dependency.

So, $R_2$ is in $2NF$ but not in $3NF$.

Solution

Solution

Solution

In $DEPT$, $did$ is the primary key.

In $EMP$, $did$ can refer to $DEPT(did)$.

So, $EMP.did$ is a foreign key.

Solution

Functional dependency $A \to B$ means:

If two rows have the same value of $A$, then they must have the same value of $B$.

Now check each dependency.

For $X \to Y$:

When $X=1$, $Y=1$ in both rows.
When $X=2$, $Y=1$.
When $X=3$, $Y=2$.

So, same $X$ always gives same $Y$.

Therefore, $X \to Y$ is true.

For $X \to Z$:

When $X=1$, $Z=a$ in both rows.
When $X=2$, $Z=b$.
When $X=3$, $Z=b$.

So, same $X$ always gives same $Z$.

Therefore, $X \to Z$ is true.

For $Z \to X$:

When $Z=b$, we have two different values of $X$:

$X=2$ and $X=3$

So, $Z \to X$ is false.

For $Y \to Z$:

When $Y=1$, we get:

$Z=a$ and $Z=b$

So, $Y \to Z$ is false.

For $Y \to X$:

When $Y=1$, we get:

$X=1$ and $X=2$

So, $Y \to X$ is false.

Hence, only $X \to Y$ and $X \to Z$ are true.

Solution

Solution

Solution

Solution

Solution

Solution

Solution

Solution

Solution

Solution

Solution

Solution

Solution

Traditional file systems have many drawbacks.

Statement $1$:

Data inconsistency can occur because the same data may be stored in multiple files.

So, statement $1$ is correct.

Statement $2$:

Accessing required data is difficult because special programs may be needed for each task.

So, statement $2$ is correct.

Statement $3$:

Data isolation occurs because data may be stored in different files and formats.

So, statement $3$ is correct.

Statement $4$:

Atomicity problems can occur because updates may be incomplete if a system failure happens.

So, statement $4$ is also correct.

Therefore, all given statements are drawbacks of file systems.

Solution

In a hierarchical database model, data is arranged like a tree.

It clearly follows a parent-child relationship.

So, statement $1$ is correct.

In a network database model, records are connected using links, and it can also represent parent-child type relationships.

So, statement $2$ is also correct.

In a relational database model, data is stored in tables.

It uses relations, primary keys, and foreign keys, but it is not mainly based on a parent-child structure.

So, statement $3$ is not correct.

Therefore, correct statements are:

$1$ and $2$

Solution

The output contains only the common tuple present in both $R1$ and $R2$.

In $R1$, tuples are:

$(a1,9), (b1,5), (c1,6), (d1,11)$

In $R2$, tuples are:

$(b1,5), (f1,8)$

Common tuple is:

$(b1,5)$

So,

$R1 \cap R2 = {(b1,5)}$

Therefore, the direct correct operation is:

$R1 \cap R2$

Solution

Solution

The phantom problem occurs in database concurrency control when a transaction re-executes a query and finds new rows inserted by another transaction.

Techniques used to prevent phantom problem:

• Index locking – locks index ranges to prevent insertion of new tuples in the range.
• Predicate locking – locks records satisfying a condition.

Time stamping and execution indexing are not specifically used to solve phantom problem.

Therefore, correct options are B and C.

Solution

Solution

Solution

Solution

Solution

Tree locking protocol (graph-based protocol) rules:

• The first lock may be on any node.

• A transaction may lock a node only if it already holds a lock on the parent.

• Nodes may be unlocked anytime but cannot be relocked.

Properties:

• Deadlock cannot occur because locking follows a hierarchical order.

• Transactions can lock data items earlier since unlocking can happen anytime.

Thus:

B → True

C → True