Database Management & SQL — Mock Test 1

A view is a virtual table defined by a query (CREATE VIEW ... AS SELECT ...). It stores no data of its own; its rows come from the base tables each time it is used.

The entity integrity constraint says a primary key can never be NULL, because the key must identify every row. Referential integrity is the rule for foreign keys.

UPDATE changes attribute values in existing tuples (UPDATE R SET a = v WHERE ...). DELETE removes tuples, SELECT reads them, and WHERE is only a condition clause.

A nested query (subquery) is a full SELECT-FROM-WHERE block placed inside another query, e.g. in the WHERE clause: ... WHERE salary > (SELECT AVG(salary) FROM emp).

σ<condition>(R) — the SELECT operation keeps only the tuples that satisfy the selection condition (predicate).

In a database such as MS Access, a form is the screen through which users enter and view data. Tables store data, queries retrieve it.

UPDATE table SET column = value WHERE condition modifies attribute values of the selected tuples. ALTER changes the table structure, DROP removes it.

The degree of a relationship type is the number of participating entity types (unary = 1, binary = 2, ternary = 3).

A crosstab query summarises data (sum, count, average) and shows it in a grid with one field as row headings and another as column headings.

An OLE Object field stores objects made in other Windows programs — Word documents, Excel sheets, pictures, sounds.

A data model is a set of concepts for describing the structure of a database (types, relationships, constraints) and gives data abstraction.

A primary key uniquely identifies each record and cannot be NULL.

Logical data independence: the conceptual schema can change without changing external schemas or application programs. Physical independence is the same for the internal schema.

A view is a virtual table defined by a query on other (base) tables.

ALTER TABLE ... ADD column_name datatype adds a new column. UPDATE changes values in rows, it does not change the structure.

Wait-die: an older transaction waits for a younger one, a younger one dies — nobody is forced to give up a lock, so it is non-preemptive. Wound-wait: an older transaction wounds (rolls back) the younger holder — preemptive.

R − S has tuples only in R and S − R tuples only in S; no tuple can be in both, so (R − S) ∩ (S − R) = φ. Their union is the symmetric difference, not φ, R ∩ S or R ∪ S in general.

In two-phase locking a transaction first acquires locks (growing phase) and, once it releases any lock, can only release (shrinking phase).

DELETE FROM R removes every tuple but keeps the table; DROP TABLE R removes the table and its definition from the database.

A → B, C gives A → D (via BC) and A → E, F. BC → D, E, F holds too. But BD⁺ = {B, D, E}; F needs C, so BD → EF does not hold.

A weak entity's discriminator (partial key) separates the weak entities that depend on the same strong entity; with the owner's key it forms the full key.

A relation (the question says 'function') in 1NF with no partial dependency on part of a composite key is in 2NF.

SQL aggregate functions are COUNT, SUM, AVG, MAX and MIN. COMPUTE is not one.

A view stores only its query and has no data of its own, so it is called a virtual table.

A → B and B → C is a transitive dependency, which 3NF forbids; such a relation cannot be in 3NF.