DBMS & SQL Previous Year Questions (page 2)

Explanation

D₂: R₁ ∩ R₂ = {A, D}, and AD → B, so AD is a key of R₁ (A, B, D); the decomposition is lossless. D₁ is not always lossless since no FDs are given: if B → A and C → A, then the common attribute A is not a key of either table, so the decomposition is lossy. Hence only D₂.

Explanation

The revised simplex method is an improvement over the simplex method: it is computationally more efficient (fewer computations), it automatically generates the inverse of the current basis matrix, and fewer entries are needed in each table, since the simplex method updates the entire tableau while only a small part is used. So answer is option (d).

Explanation

Data warehouse is a subject oriented integrated, time variant and non volatile collection of data in support of management decision making process. A data warehouse is a copy of transaction data specifically structured for query & analysis.

Explanation

A decision tree can be used to visually and explicitly represent decision & division making. In data mining, a decision tree describes data but not decisions; rather the resulting classification tree can be an input for decision making.

Explanation

SELECT A, COUNT(*) FROM R; is illegal: when an aggregate function (SUM, AVG, COUNT, MAX, MIN) is used in the SELECT clause along with a non-aggregated attribute, a GROUP BY clause on that attribute is required. SELECT A, B, COUNT(*) FROM R GROUP BY A, B; is legal.

Explanation

Accessing the data from the database in DBMS done through DML (Data manipulation language). The DML command can access the data from a database.

Explanation

Relational database schema normalization is not for reducing the number of joins required to satisfy a query (normalization usually increases joins). Its goals are eliminating redundancy and anomalies and enforcing functional dependencies.

Explanation

(1) NULL values can be used to opt a tuple out of enforcement of a foreign key: correct. (2) If a table has only one candidate key and is in 3NF, then it is also in BCNF: correct. (3) Projection (Π) in relational algebra eliminates duplicate tuples while SQL SELECT returns all of them: correct. So (1), (2) and (3) are true.

Explanation

Max-product composition: T(x, z) = max over y of R(x, y) × S(y, z). T(x₁, z₁) = max(0.7 × 0.9, 0.5 × 0.1) = 0.63; T(x₁, z₂) = max(0.42, 0.35) = 0.42; T(x₁, z₃) = max(0.14, 0.25) = 0.35. T(x₂, z₁) = max(0.72, 0.04) = 0.72; T(x₂, z₂) = max(0.48, 0.28) = 0.48; T(x₂, z₃) = max(0.16, 0.20) = 0.20. So T = x₁: [0.63 0.42 0.35], x₂: [0.72 0.48 0.20].

Explanation

The file control block and the index block are already in memory, so for indexed allocation only the new block has to be written to disk: 1 disk I/O operation. (Contiguous and linked allocation would need 1 and 3 operations respectively in this scenario, per the book.)

Explanation

Completeness Condition: All the data of the global relation must be mapped into the fragments; it must not happen that a data item which belongs to a global relation does not belong to any fragment.

Explanation

EXCEPT returns the records present in the first relation and absent in the second, eliminating duplicates: SQ1 gives the single tuple <2, 5>. EXCEPT ALL keeps duplicates, removing one occurrence per matching tuple: SQ2 gives <2, 5> and <1, 6> (2 tuples). So the cardinalities are 1 and 2.

Explanation

I. An OODBMS avoids the "impedance mismatch" problem: true. II. An OODBMS does not avoid the "phantom" problem. III. An OODBMS does not provide higher-performance concurrency control than most relational databases. IV. An OODBMS provides faster access to individual data objects once they have been read from disk: true. So I and IV only.

Explanation

Let Xᵢ = 1 if key x collides with the i-th other key, else 0. For a universal hash family, a given pair collides with probability 1/m, so E(Xᵢ) = 1/m. Summing over the other (n – 1) keys, the expected number of collisions involving x is (n – 1)/m. Since n ≤ m, this is less than 1.

Explanation

The practice of examining large pre-existing databases in order to generate new information is data mining. It refers loosely to the process of semi-automatically analyzing large databases to find useful patterns.

Explanation

CHAR and VARCHAR are both declared with a maximum length, but a CHAR column is fixed to the declared length (padded with spaces), while VARCHAR(20) uses only as much space as the stored value. So A ('Ram') needs 3 spaces and B ('Sita') needs 20.

Explanation

An instructor Id. No. cannot be null when it is the primary key – valid integrity constraint. No two citizens have the same Aadhaar-Id – valid. But 'the budget of a company must be zero' is not a valid integrity constraint. So (A) and (B) are true and (C) is false.

Explanation

Two tables are created for entities M and N, and a third table for the many-to-many relationship R₂. R₁ (one-to-many) needs no separate table: the primary key of the 'one' side is placed as a foreign key in the 'many' side. So the minimum is 3 tables.

Explanation

With R(MNPQ), M → N and P → Q, the decomposition into R₁(MN) and R₂(PQ) clearly preserves both dependencies, but it is not lossless because R₁ and R₂ have no common attribute, so R cannot be recovered by joining them.

Explanation

A distance vector routing protocol requires that a router inform its neighbors about its topology changes periodically.

Explanation

In transportation table: (i) An even number of atleast 4 cells must participate in closed loop and an occupied cell can be considered only once and not more. (ii) Closed loop may or may not be square or rectangle in shape. (iii) All cells in the loop that have a plus or minus sign, except the starting cell, must be occupied.

Explanation

No single attribute determines all others. The closure {AB}⁺ = {A, B, C, D}, so AB is the only candidate key. 2NF requires non-prime attributes to be fully dependent on the key, but C depends on A alone and D on B alone (partial dependencies). So R is in 1NF but not in 2NF.

Explanation

The correlated subquery computes the average salary of the employee's own company (T.company_name = S.company_name). The query does not use max, so it does not select only the highest paid employee; it selects all employees who earn more than the average salary of their company.

Explanation

Super key: a set of attributes that uniquely identifies a tuple. Candidate key: a minimal super key (no proper subset is a super key). Primary key: the candidate key chosen to identify tuples. So Primary key ⊆ candidate key ⊆ super key.

Explanation

Vertical alignment of table contents is set by <td valign = 'top | middle | bottom | baseline'> Text here </td>. No option matches exactly; taking 'centre' as middle, option (b) matches.

Explanation

In a binary heap stored in an array from index 1 to n, the children of index i are at 2i and 2i + 1, so the parent of the element at index i is at floor(i/2).

Explanation

As keyed by the book, MODIFY is the SQL command that changes one or more fields in a record.

Explanation

A view is a virtual table through which a selective portion of the data from one or more tables can be seen; it does not contain data of its own. A stored procedure executed when an event occurs is a trigger, not a view. So only (B) is true.

Explanation

AVG returns the average of the values in a group, ignoring null values. It is an aggregate function. Syntax: AVG([ALL | DISTINCT] expression).

Explanation

Entity integrity - no duplicate rows in a table (iv); Domain integrity - enforces valid entries for a column (iii); Referential integrity - rows can't be deleted which are used by other records (ii); User defined integrity - enforces specific business rules that do not fall into entity or domain integrity (i).

Explanation

Window: xwmin = 1, ywmin = 1, xwmax = 3, ywmax = 5. Viewport (normalized screen): xvmin = 0, yvmin = 0, xvmax = 1, yvmax = 1. Scale factors sx = (1 − 0)/(3 − 1) = 1/2 and sy = (1 − 0)/(5 − 1) = 1/4. N = T(xvmin, yvmin) · S(sx, sy) · T(−xwmin, −ywmin), which gives the matrix [1/2 0 −1/2], [0 1/4 −1/4], [0 0 1].

Explanation

The query takes the Cartesian product R₁ × R₂ and projects A, B with duplicates removed. If R₂ is empty the product is empty, so R₂ must be non-empty; and since DISTINCT removes duplicates, the result equals R₁ only if R₁ has no duplicates.

Explanation

A → B is preserved in R1, B → C in R2, and B → D, D → B in R3; C → D follows from C → B (in R2, since C → D → B) and B → D. So all dependencies are preserved. R1 ∩ R2 = B and R2 ∩ R3 = B, and B is a key of R2 and R3, so the decomposition is also lossless.

Explanation

A relation is in 2NF if it is in 1NF and every non-key attribute is fully functionally dependent on the primary key. If every non-key attribute is functionally dependent on the primary key (no partial dependency), the relation is in second normal form.

Explanation

A⁺ contains all attributes except D, and D cannot be derived from any attribute, so D must be part of every key (e.g. AD, BD, ED, FD are keys). Then A → BC is a partial dependency of non-key attribute C on part of key AD, so R is in 1NF but not in 2NF.