1Z0-006 Database Concepts & Terminology 5 — Questions and Answers
Question 1: Which term describes the process of organizing database tables to reduce data redundancy and improve data integrity?
- Indexing
- Partitioning
- Normalization (Correct answer)
- Replication
Correct answer: Normalization
Normalization is the process of structuring a relational database according to normal forms (1NF, 2NF, 3NF, etc.) to eliminate redundancy and dependency anomalies.
Question 2: In Oracle Database, what is a 'tablespace'?
- A logical storage container that groups related data files (Correct answer)
- A type of index used to organize large tables
- A special user account with DBA privileges
- A temporary area used for sorting query results
Correct answer: A logical storage container that groups related data files
A tablespace is the logical unit of Oracle database storage that groups one or more physical data files, and all database objects belong to a tablespace.
Question 3: What does 'atomicity' mean in the context of ACID database properties?
- Each transaction is the smallest possible unit of computation
- All operations in a transaction either complete fully or are entirely rolled back (Correct answer)
- Transactions are isolated from each other during execution
- Data remains consistent across multiple database replicas
Correct answer: All operations in a transaction either complete fully or are entirely rolled back
Atomicity guarantees that a transaction is treated as a single indivisible unit—if any part fails, all changes made by the transaction are rolled back entirely.
Question 4: Which of the following best describes a 'data dictionary' or 'system catalog' in a DBMS?
- A glossary document maintained by database administrators
- A repository of metadata that describes the structure and organization of the database (Correct answer)
- A special table that stores frequently accessed query results
- An index automatically maintained by the database engine
Correct answer: A repository of metadata that describes the structure and organization of the database
The data dictionary (system catalog) is a set of system tables and views that store metadata about all database objects, such as table definitions, column data types, and constraints.
Question 5: What is a 'surrogate key'?
- A key borrowed from another table to serve as a local primary key
- A system-generated unique identifier with no business meaning used as a primary key (Correct answer)
- A natural key derived from real-world attributes of an entity
- A secondary key used when the primary key is unavailable
Correct answer: A system-generated unique identifier with no business meaning used as a primary key
A surrogate key is an artificially created unique identifier (often an auto-incremented number) that has no intrinsic business meaning but serves as the primary key.
Question 6: The term 'concurrency control' in database management refers to:
- Running multiple database instances simultaneously on different servers
- Managing simultaneous access to the database by multiple users to ensure data consistency (Correct answer)
- Compressing data to allow more records to be stored concurrently
- Scheduling backup operations during periods of low user activity
Correct answer: Managing simultaneous access to the database by multiple users to ensure data consistency
Concurrency control mechanisms, such as locking and multiversion concurrency control (MVCC), coordinate simultaneous transactions so they do not corrupt each other's data.
Question 7: Which of the following is an example of a DML (Data Manipulation Language) statement in SQL?
- CREATE TABLE employees
- GRANT SELECT ON employees TO user1
- UPDATE employees SET salary = 5000 WHERE id = 10 (Correct answer)
- DROP INDEX emp_idx
Correct answer: UPDATE employees SET salary = 5000 WHERE id = 10
UPDATE is a DML statement because it modifies existing data within a table, whereas CREATE and DROP are DDL and GRANT is DCL.
1Z0-006 Database Concepts & Terminology 5
Which term describes the process of organizing database tables to reduce data redundancy and improve data integrity?