I85PREP
⌘ K
Ruankao Database Systems Engineer (Intermediate) study materials
Ruankao Database Systems Engineer (Intermediate) study resources

Ruankao Database Systems Engineer (Intermediate) practice questions

One question at a time. Build your Ruankao Database Systems Engineer (Intermediate) understanding.

First 50 of 190 questions. Each one lists the choices, the correct answer, and an explanation. The checker at the top of this page saves progress in this browser when it is available.

Continue in the practice tool above to load more questions, 50 at a time.

Question 1Computer architecture, logic and data representation

In the von Neumann model used in 软考 computer-architecture teaching, which sequence is the basic CPU instruction cycle?

  1. Decode, then fetch the instruction, then execute
  2. Fetch the instruction, decode it, then execute it Correct answer.
  3. Execute the instruction, then fetch the next opcode, then decode
  4. Write back to memory, then decode, then fetch

Explanation

A conventional CPU instruction cycle fetches the next instruction from memory using the program counter, decodes the opcode and addressing mode, then executes the operation (which may include a memory operand access). Decode-before-fetch and execute-before-fetch reverse the data path. Write-back is a cache or pipeline stage, not the start of the instruction cycle.

Question 2Computer architecture, logic and data representation

A query loop scans Emp rows in primary-key order and repeatedly reads adjacent 8 KB pages. Which hardware property is this access pattern mainly exploiting?

  1. Spatial locality in the cache and memory hierarchy Correct answer.
  2. Write-allocate policy on every store
  3. TLB shootdown across all cores
  4. Instruction-level backward compatibility of CISC only

Explanation

Spatial locality means that an access to address A makes nearby addresses likely to be used soon. Sequential page scans hit that pattern in cache lines and OS prefetch. Write-allocate is a miss-handling policy for stores, TLB shootdown is a multiprocessor invalidation event, and CISC compatibility is unrelated to sequential table scans.

Question 3Computer architecture, logic and data representation

A DBMS data page is cached. The platform uses write-through caching for that page. What is the essential property of write-through compared with write-back?

  1. Dirty cache blocks are written to lower-level storage only when the block is evicted
  2. Reads bypass the cache and always go to disk
  3. Each write updates the cache block and also updates lower-level storage before the write is considered complete Correct answer.
  4. Parity is striped across disks so one disk failure is tolerated

Explanation

Write-through keeps the next lower level consistent on every store: the cache copy and memory (or controller) are both updated as part of the write. Write-back delays the memory update until eviction, which is the opposite policy. Bypassing the cache on reads is not write-through. RAID parity is a disk-array technique, not a CPU/cache write policy.

Question 4Computer architecture, logic and data representation

A storage array uses five equal-size disks. It must survive any single disk failure and still serve I/O, and it should not devote a whole disk solely to a dedicated parity drive. Which RAID level matches this requirement in 软考 teaching?

  1. RAID 0 striping with no redundancy
  2. RAID 6 dual distributed parity when only one disk may fail
  3. RAID 1 mirroring of two disks only, ignoring the other three
  4. RAID 5 block striping with distributed parity Correct answer.

Explanation

RAID 5 stripes data and parity across member disks so any one disk can fail and the array can reconstruct the missing block. Parity is distributed rather than parked on a dedicated parity disk (that dedicated-parity layout is the older RAID 4 idea). RAID 0 has no redundancy. RAID 1 mirrors pairs and does not by itself use five disks as described. RAID 6 is designed to survive two disk failures, which overstates the stated requirement.

Question 5Computer architecture, logic and data representation

Which page-replacement policy is known to exhibit Belady's anomaly, where increasing the number of page frames can increase the number of page faults for some reference strings?

  1. Optimal replacement (replace the page used farthest in the future)
  2. Least-recently-used (LRU) replacement
  3. First-in, first-out (FIFO) replacement Correct answer.
  4. Clock replacement when it behaves as a stack algorithm

Explanation

Belady's anomaly is the counterintuitive rise in faults when the resident-set size grows. FIFO is the classic policy that is not a stack algorithm and can show the anomaly. Optimal and LRU are stack algorithms: the pages in a smaller memory are a subset of those in a larger one for the same reference string, so extra frames cannot increase faults. Clock is an LRU approximation, not the FIFO anomaly example used in textbooks.

Question 6Computer architecture, logic and data representation

A sliding-window sender may have at most 16 unacknowledged frames outstanding. Each frame is 1000 bytes of data. The round-trip time is 80 ms and other overhead is ignored. What is the maximum throughput?

  1. 20 KB/s
  2. 16 KB/s
  3. 200 KB/s Correct answer.
  4. 1.6 MB/s

Explanation

Maximum send rate is window / RTT when the window is the bottleneck. That is 16 × 1000 bytes / 0.080 s = 200000 bytes/s = 200 KB/s using 1 KB = 1000 bytes as stated. 20 KB/s undercounts by 10×. 16 KB/s is the window size mistaken for a rate. 1.6 MB/s treats 80 ms as 8 ms or drops a zero.

Question 7Operating systems, translation and concurrency

A database process issued a synchronous read and is waiting for the disk interrupt. In a three-state process model (running, ready, blocked), which state is it in until the I/O completes?

  1. Running, because it still occupies a time slice
  2. Ready, because the CPU scheduler can run it immediately
  3. Zombie, because it has finished but is not reaped
  4. Blocked (waiting), because it cannot run until the I/O event arrives Correct answer.

Explanation

A process that has requested I/O and cannot continue until the device completes is blocked/waiting. It is not running because it has yielded the CPU for the wait, and it is not ready because the scheduler must not dispatch it until the interrupt marks the buffer complete. Zombie is a terminated process awaiting parent collection, which this wait is not.

Question 8Operating systems, translation and concurrency

Coffman's four necessary conditions for deadlock are mutual exclusion, hold-and-wait, no preemption, and circular wait. Which listed condition is NOT one of those four?

  1. Starvation of a low-priority process by a CPU scheduler Correct answer.
  2. Mutual exclusion on a non-sharable resource
  3. Hold-and-wait while requesting another resource
  4. Circular wait among processes each holding one resource

Explanation

Deadlock requires the four Coffman conditions at once. Starvation (indefinite postponement by scheduling) can occur without a circular wait and is a different liveness failure. Mutual exclusion, hold-and-wait, and circular wait are three of the four deadlock conditions (the fourth is no preemption).

Question 9Operating systems, translation and concurrency

After lexical analysis has produced a token stream, which compiler phase typically checks that an identifier has been declared before use and that operators are applied to compatible types?

  1. Semantic analysis (including type checking and symbol-table use) Correct answer.
  2. Lexical analysis (scanning) a second time
  3. Object-code linking against the C library
  4. Disk firmware microcode scheduling

Explanation

Lexical analysis classifies character sequences into tokens. Syntax analysis checks context-free structure. Semantic analysis uses the symbol table to enforce declaration-before-use, type compatibility, and similar context-sensitive rules. Linking resolves external symbols after compilation. Firmware scheduling is not a compiler phase.

Question 10Computer Network Fundamentals and Architecture

In the TCP/IP model used with 软考 network questions, which layer is responsible for end-to-end reliable byte-stream delivery between two application processes, including acknowledgements and retransmission?

  1. Transport layer (TCP) Correct answer.
  2. Network layer (IP forwarding only)
  3. Data-link layer (Ethernet framing only)
  4. Physical layer (bit signalling only)

Explanation

TCP at the transport layer provides a reliable, ordered byte stream with acknowledgements, retransmission, and ports that demultiplex to processes. IP at the network layer delivers datagrams hop-by-hop without TCP's reliability contract. Ethernet framing and physical signalling operate on a single link, not end-to-end between processes.

Question 11Computer Network Fundamentals and Architecture

Which exchange is the TCP three-way handshake that establishes a connection?

  1. FIN, then ACK, then FIN, then ACK
  2. ACK only from the client, then data
  3. RST from both sides, then SYN
  4. SYN, then SYN-ACK, then ACK Correct answer.

Explanation

Connection setup is SYN (client), SYN-ACK (server), ACK (client). The four-segment FIN/ACK sequence is connection release, not establishment. An ACK-only start does not advertise initial sequence numbers. RST aborts a connection; it is not the handshake.

Question 12Computer Network Fundamentals and Architecture

IEEE 802.11 wireless LANs typically use CSMA/CA rather than classic Ethernet CSMA/CD. What is the main reason?

  1. Wireless stations can always detect collisions as reliably as wired Ethernet taps
  2. A transmitting station often cannot reliably hear a colliding transmitter (the hidden-node / energy problem), so it avoids collision by deferring and using acknowledgements Correct answer.
  3. 802.11 forbids MAC-layer acknowledgements
  4. UDP replaces CSMA on Wi-Fi

Explanation

On a shared wireless channel, a sender may not detect a collision the way a wired Ethernet transceiver can (hidden nodes, large dynamic range). 802.11 therefore uses collision avoidance: listen, backoff, optionally RTS/CTS, and ACK of frames. CSMA/CD's collision detection is the wired Ethernet story. 802.11 relies on ACKs. UDP is a transport protocol, not a MAC.

Question 13Computational Thinking and Problem Solving

Which data structure is the natural model for nested function-call return addresses and for matching parentheses in a compiler's parser?

  1. A stack (LIFO) Correct answer.
  2. A FIFO queue
  3. A hash table with chaining only
  4. A complete binary max-heap used as a priority queue of addresses

Explanation

Call/return and nested constructs are last-in, first-out: the most recently entered frame or parenthesis must be left first. A FIFO queue serves arrival order (e.g. BFS, print spoolers), not nested matching. Hash tables map keys to values; they are not the call-stack model. A heap orders by priority, not by nesting depth.

Question 14Computational Thinking and Problem Solving

Breadth-first search (BFS) on an unweighted graph, starting from a source vertex, uses which structure to hold vertices that have been discovered but not yet expanded?

  1. A stack only
  2. A disjoint-set (union-find) structure as the frontier
  3. A FIFO queue Correct answer.
  4. A min-heap keyed by edge weight as in Dijkstra on negative weights

Explanation

BFS expands vertices in the order they were first discovered, which is FIFO, so the frontier is a queue. A stack would give depth-first search. Union-find tracks components, not a BFS frontier. Dijkstra uses a priority queue on path distance; unweighted BFS does not need edge weights.

Question 15Computational Thinking and Problem Solving

An in-order traversal of a binary search tree (BST) that stores distinct keys visits the keys in which order?

  1. Random hash-bucket order
  2. Level order from the root only
  3. Sorted (nondecreasing) key order Correct answer.
  4. Always reverse-sorted order regardless of left/right convention

Explanation

In a BST, every left-subtree key is less than the node and every right-subtree key is greater. In-order is left, node, right, so keys appear in sorted order. Hash-bucket order is hashing, not tree walk. Level order is BFS. Reverse sorted order would be right, node, left (or a reversed comparator), not standard in-order.

Question 16Computational Thinking and Problem Solving

What is the worst-case time complexity of quicksort on n keys when every pivot is the smallest remaining key (e.g. already-sorted input with a naive first-element pivot)?

  1. O(1)
  2. O(log n)
  3. O(n log n)
  4. O(n²) Correct answer.

Explanation

If each partition is 1 versus n−1, the recurrence is T(n)=T(n−1)+Θ(n), which is Θ(n²). Average-case randomized or well-pivoted quicksort is O(n log n), but the question asks for that degenerate worst case. O(1) and O(log n) are not sorting bounds for arbitrary n keys by comparisons.

Question 17Computational Thinking and Problem Solving

In a hash table that resolves collisions by separate chaining, each bucket holds a linked list of keys that hashed to that slot. A lookup computes the bucket index and then does what?

  1. Searches only that bucket's chain (or tree) for the key Correct answer.
  2. Always scans every key in the entire table
  3. Rehashes the whole table on every unsuccessful search
  4. Converts the table into a BST of all keys before comparing

Explanation

Separate chaining stores collisions in one bucket's list (or a small tree). Lookup hashes to the bucket and searches that chain only. Scanning the whole table would be O(n) every time and would ignore the hash. Rehashing the entire table is a resizing operation, not a point lookup. Building a BST of all keys is a different dictionary.

Question 18Computational Thinking and Problem Solving

Which index structure, as used in typical disk-based DBMS teaching, stores complete index entries that point to rows (or the rows themselves in a clustered leaf) only in leaf pages, with internal pages holding separator keys to guide search?

  1. A B+ tree Correct answer.
  2. A binary AVL tree of in-memory pointers with data in every node
  3. A hash table with no range-scan leaf chain
  4. A binary max-heap

Explanation

B+ trees keep all row locators (or clustered rows) in the linked leaf level; internal nodes are a fanout directory of separator keys. That design matches range scans and high fanout on disk. AVL trees store data in every node and are a binary in-memory structure. Hash indexes do not provide ordered leaf walks. A heap is a priority queue, not a disk range index.

Question 19Computational Thinking and Problem Solving

For an array of n elements stored in contiguous memory, what is the time complexity of accessing the element at a given index i (0-based) in the RAM model?

  1. O(n) because each access walks from index 0
  2. O(log n) like binary search
  3. O(1) random access from the base address plus i times the element size Correct answer.
  4. O(n log n)

Explanation

Arrays support random access: address(i) = base + i × size, which is O(1). Walking from the head is a linked-list cost. Binary search is O(log n) comparisons to find a key, not to read a known index. O(n log n) is a sort bound.

Question 20Computational Thinking and Problem Solving

You must compute single-source shortest paths on a directed graph that may contain negative edge weights but no negative cycle. Which algorithm is appropriate, and why is Dijkstra's algorithm not safe here?

  1. Dijkstra is always correct with negative edges if you run it twice
  2. Breadth-first search, because BFS handles negative weights
  3. Prim's algorithm on the underlying undirected MST
  4. Bellman–Ford (or a label-correcting method); Dijkstra assumes non-negative weights so a once-finalized vertex never needs a shorter path Correct answer.

Explanation

Dijkstra finalizes a vertex when it is dequeued as the tentative minimum, which is valid only when remaining edges cannot make a finalized vertex cheaper-i.e. non-negative weights. Negative edges break that argument. Bellman–Ford relaxes all edges |V|−1 times and can handle negative weights (and detect negative cycles). BFS is for unweighted (or equal-weight) graphs. Prim computes a spanning tree, not shortest paths from a source.

Question 21Computational Thinking and Problem Solving

In the standard binary-heap implementation of a priority queue, what is the time to build a heap from n unordered keys using Floyd's bottom-up heapify (not n repeated inserts)?

  1. O(n) Correct answer.
  2. O(n log n) only, matching n inserts
  3. O(n²)
  4. O(1)

Explanation

Floyd's method heapifies from the last internal node toward the root. The sum of sift-down costs over a complete tree is linear: most nodes are near the leaves and move O(1). Repeated insert is O(n log n). O(n²) would be a naive selection-like process. O(1) cannot inspect n keys.

Question 22Computational Thinking and Problem Solving

Which comparison sort is stable (equal keys keep their original relative order) in the usual textbook implementations?

  1. Heapsort
  2. Mergesort Correct answer.
  3. The common in-place Lomuto/Hoare quicksort
  4. Selection sort as usually coded with swaps

Explanation

Mergesort copies equal keys in original order when the merge prefers the left run, so it is stable. Standard heapsort, in-place quicksort, and swap-based selection sort are not stable: they can reorder equal keys. Stability matters when sorting on a second key after a first key.

Question 23Data, Databases, and Information Retrieval

In the set-theoretic relational model taught in 软考 database theory, a relation instance is best described as which of the following?

  1. An ordered list of tuples that may contain exact duplicate rows as separate members
  2. A nested repeating group of XML elements with mixed order
  3. A set of tuples over the same heading (attribute names and domains), so duplicate tuples are not distinguished Correct answer.
  4. A single BLOB file with no schema

Explanation

A relation is a set of tuples sharing a heading. Sets do not retain duplicates or a required tuple order (order is not part of the relation value). SQL bags are a practical relaxation; the theory question is the relational model. Repeating groups violate 1NF. A schemaless BLOB is not a relation.

Question 24Data, Databases, and Information Retrieval

Which statement correctly describes a primary key of a relational table in standard teaching?

  1. It is a chosen candidate key: its values uniquely identify each tuple and it does not accept NULL Correct answer.
  2. It may contain NULLs as long as they are rare
  3. It must be a single integer column named id
  4. It is any column that appears in an index

Explanation

A primary key is one candidate key selected for identification. Entity integrity forbids NULL in the primary key, and uniqueness distinguishes tuples. The key may be composite; it need not be named id. An index can exist on non-key columns.

Question 25Data, Databases, and Information Retrieval

Referential integrity for a foreign key means which of the following?

  1. The foreign-key column may hold any value, including values that do not appear in the referenced key
  2. Every non-NULL foreign-key value must match an existing referenced candidate/primary key value Correct answer.
  3. The foreign key must have the same name as the primary key
  4. Foreign keys are allowed only on views, not base tables

Explanation

A foreign key value that is not NULL must appear as a candidate/primary key value in the referenced relation (or the same relation). Column names need not match. Foreign keys are defined on base tables (and some systems on views with constraints). Unmatched values would be dangling references.

Question 26Data, Databases, and Information Retrieval

In the ANSI/SPARC three-schema architecture, which pair is correct?

  1. External schema = physical files on disk; internal schema = SQL views for users
  2. Conceptual schema = byte offsets of indexes only; external schema = RAID level
  3. Internal schema = user reports; conceptual schema = Ethernet frames
  4. External schemas = user/community views; conceptual schema = enterprise logical structure; internal schema = physical storage Correct answer.

Explanation

External (view) schemas describe what user groups see. The conceptual schema is the community logical model. The internal schema describes files, indexes, and clustering. Swapping external with internal reverses the architecture. RAID and Ethernet are not schema levels of the database.

Question 27Data, Databases, and Information Retrieval

A DBA changes a relation from an unclustered heap to a B+ tree clustered index without changing table and view definitions that applications use. Which independence is this?

  1. Physical data independence Correct answer.
  2. Logical data independence only, because the conceptual schema changed
  3. Device independence of the SQL GRANT catalog
  4. Loss of both logical and physical independence

Explanation

Physical data independence is insulation of the conceptual/external schemas from internal storage changes (file organization, indexes, clustering). The conceptual schema (tables, columns, constraints) did not change, so this is not a logical-schema change. GRANT catalogs and “losing independence” misstate the benefit of the three-schema split.

Question 28Data, Databases, and Information Retrieval

A funds-transfer transaction debits account A and credits account B. If the system crashes after the debit is written but before the credit, and recovery undoes the debit so neither update remains, which ACID property is being restored?

  1. Durability of a committed transfer that already finished
  2. Consistency in the sense of a SQL CHECK that both accounts must be NULL
  3. Isolation from a dirty read that already committed
  4. Atomicity (all-or-nothing of the transaction's writes) Correct answer.

Explanation

Atomicity means the transaction's updates all persist or none do. Recovery that rolls back the partial debit restores all-or-nothing. Durability applies after COMMIT. Isolation is about concurrent interference. A CHECK forcing NULL accounts is not the transfer invariant and is not what undo is doing here.

Question 29Data, Databases, and Information Retrieval

A superkey of relation R is a set of attributes that uniquely identifies tuples. A candidate key is which of the following?

  1. Any superkey, including those with leftover unused columns
  2. The first column of the CREATE TABLE statement only
  3. A minimal superkey: a superkey with no proper subset that is still a superkey Correct answer.
  4. Any foreign key, whether or not it uniquely identifies local tuples

Explanation

Candidate keys are minimal superkeys: drop any attribute and uniqueness is lost. Superkeys may contain extra attributes. Column order in CREATE TABLE is irrelevant. A foreign key identifies tuples in the referenced relation, not necessarily uniquely in the child.

Question 30Data, Databases, and Information Retrieval

Relation R(A,B,C,D) has F = {A→B, B→C} and no other FDs besides those implied by Armstrong's axioms. Which of the following is a candidate key of R?

  1. A
  2. AD Correct answer.
  3. ABD
  4. BC

Explanation

Compute closures: A+ = ABC, which misses D, so A is not a key. AD+ = ABCD, so AD is a superkey. A+ and D+ = {D} show that neither proper subset of AD is a superkey, so AD is a candidate key. ABD properly contains AD, so it is a non-minimal superkey. BC+ = BC (B→C), missing A and D.

Question 31Data, Databases, and Information Retrieval

Let R(A,B,C,D,E) and F = {AB→C, C→D, D→E}. What is the attribute closure (AB)+?

  1. AB only
  2. ABC only
  3. ABCD only
  4. ABCDE Correct answer.

Explanation

Start with AB. AB→C adds C, so ABC. C→D adds D, so ABCD. D→E adds E, so ABCDE. Therefore (AB)+ is the full heading and AB is a superkey (in fact a candidate key given these FDs). Stopping at AB, ABC, or ABCD ignores the remaining FDs.

Question 32Data, Databases, and Information Retrieval

Armstrong's transitivity rule says: if X→Y and Y→Z hold, then which FD also holds?

  1. X→Z Correct answer.
  2. Z→X always
  3. Y→X always
  4. XZ→∅ only

Explanation

Transitivity: from X→Y and Y→Z infer X→Z. The converse Z→X is not implied (dependencies are not symmetric). Y→X would be reflexivity only if X ⊆ Y, which is not given. XZ→∅ is not the transitivity rule (the empty set is functionally determined by every set, but that is not the content of the rule being asked).

Question 33Data, Databases, and Information Retrieval

Relation Enroll(Sno, Cno, Grade, Cname) has primary key (Sno, Cno). The only extra FD is Cno→Cname. Why is Enroll not in 2NF?

  1. Grade is atomic, so the table is not even in 1NF
  2. Cname is a prime attribute, so 2NF does not care about Cno→Cname
  3. Cname is a non-prime attribute that depends on a proper subset Cno of the candidate key (partial dependency) Correct answer.
  4. The table has two columns in the key, which 2NF forbids

Explanation

2NF forbids partial FDs of non-prime attributes on a candidate key. Cname is not part of the key (non-prime) and is determined by Cno alone, a proper subset of (Sno, Cno). Atomic grades mean 1NF can hold. Composite keys are allowed in 2NF; the issue is the partial FD, not the mere presence of two key columns.

Question 34Data, Databases, and Information Retrieval

RepairJob(Plate, Time, PartNo, PartName, Supplier, Qty) has key (Plate, Time, PartNo). FDs include PartNo→PartName, PartNo→Supplier, and no FD from PartNo to Plate. Highest normal form among 1NF/2NF/3NF/BCNF that the schema as a whole satisfies is which?

  1. 1NF only (it violates 2NF) Correct answer.
  2. 2NF but not 3NF
  3. 3NF but not BCNF
  4. BCNF

Explanation

PartName and Supplier are non-prime and depend on PartNo, a proper subset of the key - a partial dependency - so the relation is not in 2NF. Assuming atomic attributes, it is in 1NF. 3NF and BCNF both require at least 2NF (BCNF is stronger than 3NF). Do not jump to 3NF while a partial FD remains.

Question 35Data, Databases, and Information Retrieval

A commonly used definition of 3NF is: the relation is in 2NF and no non-prime attribute is transitively dependent on a candidate key. Which situation is a transitive dependency of that kind?

  1. Key K → non-prime A, and A is atomic
  2. Key K → non-prime A and A → non-prime B with A not a superkey (so K → A → B) Correct answer.
  3. A trivial FD X→Y with Y ⊆ X
  4. A candidate key composed of two attributes

Explanation

Transitive dependence of a non-prime on a key is K → A → B where B is non-prime and the middle determinant A is not a key. Direct FDs from the key, trivial FDs, and merely having a composite key are not that pattern. Removing A→B (or placing B with A) is the usual 3NF decomposition.

Question 36Data, Databases, and Information Retrieval

Relation Teaches(Student, Subject, Teacher) has FDs {Student,Subject}→Teacher and Teacher→Subject. Candidate keys are {Student,Subject} and {Student,Teacher}. Which statement is correct?

  1. The relation is not in 1NF
  2. The relation is in BCNF because it has two candidate keys
  3. Teacher→Subject violates 3NF because Subject is non-prime
  4. The relation is in 3NF but not in BCNF, because Teacher→Subject has a determinant that is not a superkey while Subject is prime Correct answer.

Explanation

Subject is prime (it is in a candidate key). 3NF allows an FD X→A if A is prime even when X is not a superkey. BCNF requires every determinant to be a superkey; Teacher is not a superkey, so Teacher→Subject violates BCNF. Two candidate keys do not imply BCNF. Attributes are atomic, so 1NF holds.

Question 37Data, Databases, and Information Retrieval

A binary decomposition of R into R1 and R2 is lossless-join with respect to a set of FDs F when which condition holds?

  1. R1 and R2 have no attributes in common
  2. R1 and R2 have the same number of tuples as R
  3. The intersection R1.heading ∩ R2.heading is a superkey of R1 or of R2 (i.e. it determines one of the two headings) Correct answer.
  4. The decomposition is always lossy if any FD exists

Explanation

For a two-way decomposition, the join is lossless if the common attributes form a superkey of at least one component, so that component can be reconstructed without spurious tuples. Disjoint headings yield a Cartesian product (lossy for a real decomposition of one relation). Tuple counts of R1 and R2 are not the test. FDs are what let you prove the superkey condition; they do not make every decomposition lossy.

Question 38Data, Databases, and Information Retrieval

First normal form (1NF) requires which property of a relation?

  1. Every attribute value is atomic (no repeating groups or nested relations as a single attribute) Correct answer.
  2. Every determinant is a candidate key (that is BCNF)
  3. There are no partial dependencies (that is 2NF)
  4. There are no transitive dependencies (that is 3NF)

Explanation

1NF is the relational requirement that each attribute be atomic from the model's point of view-no repeating groups. BCNF, 2NF, and 3NF are stricter forms that already assume 1NF and then constrain FDs. Mixing those definitions is a common 软考 trap.

Question 39Data, Databases, and Information Retrieval

Given F = {A→B, B→C} on attributes {A,B,C}, which additional FD is implied?

  1. C→A
  2. C→B
  3. A→C Correct answer.
  4. B→A

Explanation

Transitivity: A→B and B→C imply A→C. None of the converses is implied: C does not determine A or B, and B does not determine A, from F alone.

Question 40Data, Databases, and Information Retrieval

The functional dependency X→Y is trivial when which condition holds?

  1. X is empty and Y is the whole heading
  2. X and Y are disjoint and both nonempty
  3. Y contains an attribute that is not in X and X is a candidate key
  4. Y is a subset of X (Y ⊆ X) Correct answer.

Explanation

X→Y is trivial iff Y ⊆ X: a set always determines its subsets (Armstrong reflexivity). Non-trivial FDs have some attribute on the right not in the left. Key-ness is separate from triviality. Disjoint nonempty X and Y is a typical non-trivial pattern, not a trivial one.

Question 41Data, Databases, and Information Retrieval

How does the Cartesian product R × S differ from the equijoin R ⋈_{R.a=S.a} S?

  1. They are identical for all instances
  2. The equijoin can produce more tuples than the Cartesian product
  3. The Cartesian product pairs every tuple of R with every tuple of S with no match condition; the equijoin keeps only pairs with equal a values Correct answer.
  4. The Cartesian product requires R and S to have identical headings, but join never does

Explanation

R × S has |R|·|S| tuples (in bag SQL, similarly for counts). An equijoin is the subset of that product where the specified attributes compare equal (then typically one copy of the join column is dropped in NATURAL JOIN). An equijoin cannot have more tuples than the product. Union compatibility is for ∪/∩/−, not a requirement that product headings match.

Question 42Data, Databases, and Information Retrieval

Emp has attributes Name and Salary. Which relational-algebra expression is well-typed and selects names of employees with Salary > 5000?

  1. σ_{Salary>5000}(π_{Name}(Emp))
  2. π_{Name}(σ_{Salary>5000}(Emp)) Correct answer.
  3. π_{Salary}(σ_{Name}(Emp))
  4. Emp − Emp

Explanation

Selection on Salary must happen while Salary is still an attribute, then projection keeps Name. If you project Name first, Salary is gone and σ_{Salary>5000} is illegal. π_{Salary}(σ_{Name}(Emp)) misuses selection syntax and would not return names. Emp − Emp is the empty relation, not the query.

Question 43SQL retrieval, joins and transaction semantics

In SQL, what does SELECT DISTINCT dno FROM Emp return for the Emp instance with dno values 10, 10, 20, and NULL?

  1. Two rows: 10 and 20 only, dropping NULL
  2. Four rows identical to Emp's four dno values including the duplicate 10
  3. Three rows: 10, 20, and NULL (duplicate 10 collapsed; NULL kept once) Correct answer.
  4. Zero rows because DISTINCT forbids NULL

Explanation

DISTINCT removes duplicate result rows. The repeated10 collapses to one row,20 remains and the NULL marker appears once under SQL duplicate-elimination semantics. NULL is not an ordinary known scalar value, and DISTINCT does not automatically discard it.

Question 44SQL retrieval, joins and transaction semantics

In a SELECT with GROUP BY, which clause filters groups after aggregation (e.g. COUNT(*) > 2)?

  1. HAVING Correct answer.
  2. WHERE
  3. FROM only
  4. ORDER BY

Explanation

WHERE filters rows before grouping. HAVING filters groups after GROUP BY and aggregates. FROM names sources. ORDER BY sorts the final rows and does not filter groups.

Question 45SQL retrieval, joins and transaction semantics

Tables: Emp(eid, ename, dno, salary) has rows (1,'Alice',10,5000), (2,'Bob',10,6000), (3,'Cara',20,7000), (4,'Dan',NULL,4000). Dept(dno, dname) has rows (10,'HR'), (20,'IT'), (30,'Legal'). Which join result includes Dan (dno NULL) as a result row padded with NULL Dept columns?

  1. SELECT * FROM Emp INNER JOIN Dept ON Emp.dno = Dept.dno
  2. SELECT * FROM Emp CROSS JOIN Dept
  3. SELECT * FROM Emp LEFT OUTER JOIN Dept ON Emp.dno = Dept.dno Correct answer.
  4. SELECT * FROM Emp INNER JOIN Dept ON Emp.dno = Dept.dno WHERE Emp.dno IS NOT NULL

Explanation

LEFT OUTER JOIN keeps every Emp row. Dan's dno is NULL, so the ON equality fails, and Dept columns are NULL-padded. INNER JOIN drops unmatched Emp rows, so Dan disappears. CROSS JOIN would pair Dan with every department (three rows), which is not a single padded unmatched row. The extra WHERE IS NOT NULL also drops Dan.

Question 46SQL retrieval, joins and transaction semantics

Tables: Emp(eid, ename, dno, salary) has rows (1,'Alice',10,5000), (2,'Bob',10,6000), (3,'Cara',20,7000), (4,'Dan',NULL,4000). Dept(dno, dname) has rows (10,'HR'), (20,'IT'), (30,'Legal'). How many rows does SELECT * FROM Emp INNER JOIN Dept ON Emp.dno = Dept.dno return?

  1. 2
  2. 4
  3. 12
  4. 3 Correct answer.

Explanation

INNER JOIN keeps pairs with equal dno. Alice and Bob match HR (10); Cara matches IT (20); Dan's NULL matches nothing; Legal (30) matches no Emp row. Cardinality is 3, not 4 (Dan is dropped), not 12 (that is the product), and not 2 (that would be only HR).

Question 47SQL retrieval, joins and transaction semantics

Tables: Emp(eid, ename, dno, salary) has rows (1,'Alice',10,5000), (2,'Bob',10,6000), (3,'Cara',20,7000), (4,'Dan',NULL,4000). Dept(dno, dname) has rows (10,'HR'), (20,'IT'), (30,'Legal'). How many rows does SELECT * FROM Emp LEFT OUTER JOIN Dept ON Emp.dno = Dept.dno return?

  1. 3
  2. 12
  3. 7
  4. 4 Correct answer.

Explanation

LEFT JOIN returns one result row per Emp row: three matched employees plus Dan padded with NULL Dept columns. That is 4. 3 is the inner-join count. 12 is the cross product. 7 has no operator that yields it here.

Question 48SQL retrieval, joins and transaction semantics

Tables: Emp(eid, ename, dno, salary) has rows (1,'Alice',10,5000), (2,'Bob',10,6000), (3,'Cara',20,7000), (4,'Dan',NULL,4000). Dept(dno, dname) has rows (10,'HR'), (20,'IT'), (30,'Legal'). How many rows does SELECT * FROM Emp CROSS JOIN Dept return?

  1. 7
  2. 4
  3. 12 Correct answer.
  4. 3

Explanation

CROSS JOIN is the Cartesian product: 4 Emp rows × 3 Dept rows = 12, with no ON predicate. 4 is |Emp|, 3 is |Dept| or the inner join, and 7 is unrelated.

Question 49SQL retrieval, joins and transaction semantics

Tables: Emp(eid, ename, dno, salary) has rows (1,'Alice',10,5000), (2,'Bob',10,6000), (3,'Cara',20,7000), (4,'Dan',NULL,4000). Dept(dno, dname) has rows (10,'HR'), (20,'IT'), (30,'Legal'). How many groups does SELECT dno, COUNT(*) FROM Emp GROUP BY dno produce?

  1. 2 (NULL rows are discarded before grouping)
  2. 4 (one group per Emp row)
  3. 3 (dno 10, dno 20, and dno NULL) Correct answer.
  4. 1 (all rows share one implicit group)

Explanation

GROUP BY dno makes one group per distinct dno value including NULL as its own group: {10,10}, {20}, {NULL} - three groups. COUNT(*) in the NULL group is 1. GROUP BY does not drop NULL keys. Four groups would mean grouping by eid. One group would be an aggregate without GROUP BY.

Question 50SQL retrieval, joins and transaction semantics

Tables: Emp(eid, ename, dno, salary) has rows (1,'Alice',10,5000), (2,'Bob',10,6000), (3,'Cara',20,7000), (4,'Dan',NULL,4000). Dept(dno, dname) has rows (10,'HR'), (20,'IT'), (30,'Legal'). What does SELECT COUNT(*) FROM Emp E INNER JOIN Dept D ON E.dno = D.dno WHERE D.dname = 'HR' return?

  1. 4
  2. 3
  3. 1
  4. 2 Correct answer.

Explanation

The inner join on dno yields Alice-HR, Bob-HR, Cara-IT. The WHERE clause keeps only HR, so Alice and Bob - COUNT(*) = 2. Dan never joined. Legal is unused. 4 would count all Emp, 3 the unfiltered inner join, 1 a single HR department row without employees.