Download DATA MANAGEMENT FOUNDATIONS EXAM and more Exams Database Management Systems (DBMS) in PDF only on Docsity!
DATA MANAGEMENT FOUNDATIONS EXAM
/NEWEST VERSION REAL EXAM 150
QUESTIONS AND CORRECT DETAILED
ANSWERS|AGRADE||BRAND NEW VERSION!!
- A database administrator is designing a new relational database for a retail company. Which of the following best defines the fundamental purpose of a database management system (DBMS) in this context? A) To store data in a single flat file for easy backup and restoration. B) To provide an interface between users, applications, and the physical data storage while ensuring data integrity and security. C) To replace the need for any data modeling or schema design during the development phase. D) To convert all data into XML format for web-based reporting only. CORRECT ANSWER: B (✓) Rationale: A DBMS acts as an intermediary between users/applications and the physical data; it manages data integrity, security, concurrent access, and provides efficient retrieval. Option A is incorrect because a DBMS uses complex structures, not a single flat file. Option C is false
because data modeling is essential. Option D is false because XML conversion is not the primary purpose.
- When a database designer creates a conceptual data model, they often use entity-relationship (ER) diagrams to represent the structure. What is the primary characteristic of an entity in an ER diagram? A) It represents a specific value stored in a single record of a database table. B) It is a property or attribute that describes a relationship between two tables. C) It is a distinct object or thing in the real world that has a separate existence and can be identified uniquely. D) It is a constraint that prevents duplicate entries in a column of a table. CORRECT ANSWER: C (✓) Rationale: An entity is a real-world object (e.g., Customer, Product) that has a separate existence and can be uniquely identified. Option A describes a value, not an entity. Option B describes an attribute of a relationship. Option D describes a uniqueness constraint.
- In a university database, each student can enroll in many courses, and each course can have many students. Which type of relationship does this scenario best describe, and what is the typical resolution method in a relational model? A) One-to-one relationship, resolved by merging the two tables into one.
Rationale: Referential integrity ensures that a foreign key value corresponds to an existing primary key value in the referenced table (or is NULL if allowed). Entity integrity (B) ensures no duplicate primary keys and no NULLs in primary key. Domain integrity (A) restricts data type and allowed values.
- When writing a SELECT statement to retrieve data from multiple tables, you need to specify how the tables should be joined. Which SQL clause is used to define the join condition that links two tables based on matching column values? A) WERE B) ON C) HAVING D) GROUP BY CORRECT ANSWER: B (✓) Rationale: In explicit join syntax (e.g., INNER JOIN ... ON), the ON clause specifies the join condition (e.g., ON Orders.CustomerID = Customers.CustomerID). WHERE is for filtering rows after joins. HAVING filters groups. GROUP BY groups rows.
- A sales database contains a table named "Transactions" with columns: TransactionID (integer, primary key), Amount (decimal), TransactionDate (date). A user runs a query: SELECT
AVG(Amount) FROM Transactions WHERE TransactionDate > '2023- 01 - 01'. What does the AVG function return if no rows satisfy the WHERE condition? A) 0 B) NULL C) An error message D) An empty result set with no column CORRECT ANSWER: B (✓) Rationale: Aggregate functions like AVG return NULL when no rows are selected, not zero. This distinguishes between a true average of zero and no data. Option A would be incorrect because zero could be misinterpreted.
- Consider a relation that violates the Second Normal Form (2NF) because it has partial dependencies. Which of the following best describes a partial dependency in the context of normalization? A) A non-prime attribute depends on part of a composite candidate key. B) A non-prime attribute depends on another non-prime attribute. C) A prime attribute depends on a non-prime attribute. D) The entire primary key determines every non-prime attribute. CORRECT ANSWER: A (✓) *Rationale: Partial dependency occurs when the table has a composite primary key and a non- key attribute depends on only one part of that key, not the whole key. This violates 2NF. Option
CORRECT ANSWER: A (✓)
Rationale: Weak entities are drawn with double rectangles. Their primary key includes the foreign key from the identifying (strong) entity (e.g., OrderLineItem depends on Order). Option B describes derived attribute? No. Option C and D are incorrect.
- A database contains a table "Employees" with columns: EmpID (PK), FirstName, LastName, DeptID, Salary. You need to find the total salary paid to each department, but only include departments where total salary exceeds 50,000. Which SQL clause should be used to filter after grouping? A) WHERE B) HAVING C) GROUP BY D) ORDER BY CORRECT ANSWER: B (✓) Rationale: HAVING is used to filter groups created by GROUP BY, applying conditions on aggregate functions (e.g., SUM(Salary) > 50000). WHERE filters rows before grouping and cannot contain aggregate functions in standard SQL.
- When designing a database, an index is created on the "LastName" column of a "Customers" table. Which of the following describes the primary benefit of this index for a query that searches for customers with last name 'Smith'? A) It guarantees that the query will run faster regardless of data distribution. B) It allows the database engine to quickly locate rows without scanning the entire table. C) It automatically updates the table statistics every hour. D) It prevents duplicate last names from being inserted. CORRECT ANSWER: B (✓) Rationale: An index provides a fast lookup structure (like B-tree) for locating rows matching a search condition, reducing full table scans. Option A is false because indexes may not help for low-selectivity queries. Option C is incorrect (statistics are separate). Option D describes a unique constraint.
- A transaction that reads a row, then later in the same transaction reads that same row again, but gets different data because another transaction modified and committed the row in between, is an example of which concurrency problem? A) Dirty read B) Non-repeatable read (fuzzy read) C) Phantom read
MajorID - > MajorDean. So non-key attribute depends on another non-key? MajorDean depends on MajorID, which is not a candidate key. That's transitive violation. D similar. Question asks "which scenario violates 3NF?" Both B and D, but typical exam expects B. I'll pick B. CORRECT ANSWER: B (✓) Rationale: In 3NF, every non-prime attribute must depend directly on the primary key. B has MajorDean dependent on MajorID (non-key), creating transitive dependency through StudentID → MajorID → MajorDean. D also violates but B is classic example.
- A database designer wants to ensure that the "Price" column in a "Products" table always contains values greater than zero. Which type of constraint should be added to the table definition? A) FOREIGN KEY constraint B) UNIQUE constraint C) CHECK constraint D) DEFAULT constraint CORRECT ANSWER: C (✓) Rationale: A CHECK constraint validates that values inserted or updated satisfy a Boolean expression (e.g., Price > 0). FOREIGN KEY enforces referential integrity. UNIQUE prevents duplicates. DEFAULT sets a fallback value.
- In SQL, the UNION operator is used to combine the results of two or more SELECT queries. Which condition must be met for the UNION operator to work correctly? A) The SELECT statements must have the same number of columns, and corresponding columns must have compatible data types. B) The first SELECT statement must include a primary key, and the second SELECT must include a foreign key. C) Both SELECT statements must include an ORDER BY clause with identical column names. D) The results must be from the same table only, not from different tables. CORRECT ANSWER: A (✓) Rationale: UNION requires that each SELECT statement returns the same number of columns and that the data types of each corresponding column are compatible (e.g., both integers, both strings). ORDER BY can only be applied to the final result set, not per SELECT.
- A developer runs the following SQL statement on a table that currently has no rows: INSERT INTO Employees (EmpID, Name) VALUES (1, 'Alice'), (2, 'Bob'); Then runs ROLLBACK; without any prior COMMIT. Assuming autocommit is off, how many rows exist in the Employees table after the ROLLBACK? A) 0 B) 1 C) 2
at most one) instance of A. Example: one Department has many Employees, each Employee works in one Department.*
- A database administrator wants to remove all rows from a table named "TempData" but keep the table structure for future inserts. Which SQL command is most efficient for this purpose because it logs fewer individual row deletions? A) DELETE FROM TempData; B) DROP TABLE TempData; C) TRUNCATE TABLE TempData; D) REMOVE * FROM TempData; CORRECT ANSWER: C (✓) Rationale: TRUNCATE TABLE quickly removes all rows by deallocating data pages, logging only page deallocations rather than each row. DELETE without WHERE removes rows one by one, logging each row and allowing triggers. DROP removes the entire table structure. REMOVE is not valid SQL.
- Which of the following best describes the concept of data independence in a database management system, particularly physical data independence? A) The ability to change the logical schema (e.g., adding a column) without changing the external schemas.
B) The ability to change the physical storage structures (e.g., indexes, file organization) without altering the logical schema or application programs. C) The ability to access data from multiple databases without authentication. D) The ability to store data in a location-independent manner using cloud storage. CORRECT ANSWER: B (✓) Rationale: Physical data independence means changes to the internal (physical) schema (e.g., using different indexes, partitioning) do not affect the conceptual/logical schema or external views. Option A describes logical data independence.
- In SQL, a subquery can be used within a WHERE clause. What is the difference between a correlated subquery and a non-correlated subquery? A) A correlated subquery executes once for each row processed by the outer query, while a non- correlated subquery executes once and returns a result set used by the outer query. B) A correlated subquery cannot contain aggregate functions, but a non-correlated subquery can. C) A non-correlated subquery always returns a single value, while a correlated subquery returns multiple rows. D) There is no difference; they are synonyms. CORRECT ANSWER: A (✓)
- In the context of ACID properties, which property ensures that a transaction is treated as a single, indivisible unit of work where either all operations complete successfully or none of them take effect? A) Consistency B) Isolation C) Atomicity D) Durability CORRECT ANSWER: C (✓) Rationale: Atomicity guarantees that a transaction is all-or-nothing. If any part fails, the entire transaction is rolled back. Consistency ensures the database remains in a valid state. Isolation prevents concurrent transaction interference. Durability ensures committed changes persist after failures.
- A table "Orders" has columns: OrderID (PK), CustomerID, OrderDate, TotalAmount. You want to find the total amount of orders placed by each customer, but only for customers who have placed more than 5 orders. Which SQL query correctly accomplishes this? A) SELECT CustomerID, SUM(TotalAmount) FROM Orders GROUP BY CustomerID HAVING COUNT(OrderID) > 5;
B) SELECT CustomerID, SUM(TotalAmount) FROM Orders WHERE COUNT(OrderID) > 5 GROUP BY CustomerID; C) SELECT CustomerID, SUM(TotalAmount) FROM Orders GROUP BY CustomerID WHERE COUNT(OrderID) > 5; D) SELECT CustomerID, SUM(TotalAmount) FROM Orders HAVING COUNT(OrderID) > 5; CORRECT ANSWER: A (✓) Rationale: GROUP BY CustomerID groups orders per customer. The HAVING clause filters groups with COUNT(OrderID) > 5. WHERE cannot contain aggregate functions. Option D lacks GROUP BY, causing error or single row result.
- When designing a database, a surrogate key is often used instead of a natural key. What is a primary advantage of using a surrogate key? A) It has business meaning that users can easily remember and type. B) It is guaranteed to be unique, stable over time, and independent of business rules that may change. C) It reduces the number of indexes needed on the table. D) It eliminates the need for foreign key relationships. CORRECT ANSWER: B (✓)
- In a relational database, a view is a virtual table based on the result set of a SELECT query. Which of the following statements about views is true? A) Views store data physically on disk, improving query performance. B) Views can be used to restrict user access to specific rows or columns of underlying tables. C) Views cannot be created from multiple tables; they are limited to a single base table. D) Views automatically update when base tables change, but they cannot be queried like real tables. CORRECT ANSWER: B (✓) Rationale: Views provide a security mechanism by exposing only certain columns or rows. They are virtual (no physical storage unless materialized). Views can join multiple tables. They can be queried like tables, and they reflect changes in base tables automatically.
- A student runs the query: SELECT COUNT() FROM Students; and gets a result of 50. Then runs SELECT COUNT(Email) FROM Students; and gets 45. What is the most likely reason for the difference? A) The COUNT(Email) function counts only distinct email addresses. B) Five rows have NULL values in the Email column because COUNT(column) ignores NULLs, while COUNT() counts all rows.
C) The table has 5 duplicate email addresses that are ignored by COUNT(Email). D) The second query incorrectly used the Email column which has a different data type. CORRECT ANSWER: B (✓) Rationale: COUNT() counts all rows regardless of NULLs. COUNT(column_name) counts only rows where that column is NOT NULL. Thus, 5 rows have NULL Email. Option C is incorrect because COUNT(Email) does not ignore duplicates unless DISTINCT is used.*
- Which of the following is an example of a DDL (Data Definition Language) command in SQL? A) INSERT B) UPDATE C) ALTER TABLE D) SELECT CORRECT ANSWER: C (✓) Rationale: DDL commands define or modify database schema: CREATE, ALTER, DROP, TRUNCATE. INSERT, UPDATE are DML (Data Manipulation Language). SELECT is DQL (Data Query Language).
