FC0-U61 Database Fundamentals — Timed 30 Questions

Question 1

A user needs to store customer information including name, address, and order history. Which database type is most appropriate?

Accepted Answer

Relational database. A relational database is the most appropriate choice because it organizes data into tables with rows and columns, allowing efficient storage and retrieval of structured customer information (name, address) while supporting relationships to order history via foreign keys. This structure enables ACID compliance and complex queries using SQL, which is ideal for transactional data with clear schema requirements.

Answer

Hierarchical database

Answer

Flat-file database

Answer

NoSQL database

Question 2

A database analyst is designing a schema for a library system. Each book can have multiple authors, and each author can write multiple books. Which relationship type should be used?

Accepted Answer

Many-to-many. The correct relationship is many-to-many because a book can have multiple authors and an author can write multiple books. In database schema design, this requires a junction table (also known as a linking or associative table) to resolve the many-to-many relationship into two one-to-many relationships, ensuring referential integrity and avoiding data redundancy.

Answer

Many-to-one

Answer

One-to-many

Answer

One-to-one

Question 3

A database administrator is troubleshooting a slow query on a large table. Which index type would improve performance for an exact match search on a single column?

Accepted Answer

B-tree index. A B-tree index is the correct choice for an exact match search on a single column because it organizes data in a balanced tree structure that allows O(log n) lookups, making it highly efficient for equality searches. In a large table, the B-tree index reduces the number of disk I/O operations by quickly navigating to the leaf node containing the exact key value.

Answer

Clustered index

Answer

Bitmap index

Answer

Hash index

Question 4

A developer writes a query: SELECT * FROM Employees WHERE Department = 'Sales'. Which statement about this query is true?

Accepted Answer

It returns all columns for employees in Sales. The SELECT * clause retrieves all columns from the specified table, and the WHERE Department = 'Sales' filter restricts the result set to only those rows where the Department column has the value 'Sales'. Therefore, the query returns every column for employees in the Sales department, making option B correct.

Answer

It sorts results by Department

Answer

It returns only the Department column

Answer

It returns all rows from the Employees table

Question 5

A database designer wants to ensure that no two employees have the same email address. Which constraint should be applied to the Email column?

Accepted Answer

UNIQUE. The UNIQUE constraint ensures that all values in the Email column are distinct, preventing any two employees from having the same email address. Unlike PRIMARY KEY, UNIQUE allows NULL values (though typically email columns are set to NOT NULL), and it can be applied to non-key columns. This directly enforces the business rule of unique email addresses without requiring the column to be the table's primary identifier.

Answer

PRIMARY KEY

Answer

CHECK

Answer

FOREIGN KEY

Question 6

A database administrator notices that a transaction that updates two tables is failing halfway, leaving data inconsistent. Which property of ACID ensures that the database returns to its original state if the transaction fails?

Accepted Answer

Atomicity. Atomicity (option D) ensures that a transaction is treated as a single, indivisible unit of work. If any part of the transaction fails (e.g., updating the second table), the entire transaction is rolled back, and the database returns to its original state before the transaction began. This prevents partial updates that would leave data inconsistent.

Answer

Consistency

Answer

Durability

Answer

Isolation

Question 7

A database administrator is designing a normalized database for an e-commerce site. Which TWO actions represent best practices for normalization?

Accepted Answer

Removing duplicate data from tables by creating separate tables for each entity. Option C is correct because normalization aims to reduce data redundancy and improve data integrity by removing duplicate data and organizing it into separate, related tables. By creating separate tables for each distinct entity (e.g., Customers, Orders, Products), you eliminate repeating groups and ensure each fact is stored only once, which is a core principle of database normalization.

Answer

Creating separate tables for each customer order without linking them

Answer

Storing all product details in a single column as a list

Answer

Storing calculated totals in the same table to avoid joins

Question 8

A database administrator is setting up user permissions. Which THREE actions follow the principle of least privilege?

Accepted Answer

Granting SELECT only on specific tables to a read-only user. Granting SELECT only on specific tables to a read-only user adheres to the principle of least privilege by limiting data access to exactly what the user needs to perform their job. This prevents unnecessary exposure of sensitive data and reduces the risk of accidental or malicious modification. In SQL databases, this is implemented via GRANT SELECT ON specific_table TO user, rather than granting broader permissions like SELECT on all tables or higher-level privileges.

Answer

Allowing remote access from any IP address for flexibility

Answer

Granting DBA privileges to all developers for convenience

Question 9

Refer to the exhibit. A user executes: SELECT AVG(Salary) FROM Employees; What is the result?

Accepted Answer

55000. The AVG function calculates the arithmetic mean of the Salary column across all rows in the Employees table. Given the salaries 50000, 60000, and 55000, the average is (50000 + 60000 + 55000) / 3 = 55000. Therefore, option A is correct.

Answer

50000

Answer

165000

Answer

60000

Question 10

Refer to the exhibit. What is the output of the SELECT statement?

Accepted Answer

Widget, 2. The SELECT statement uses COUNT(*) with GROUP BY Category, which counts the number of rows per unique category value. The table has two rows with 'Widget' and one row with 'Gadget', so the output is two rows: Widget with count 2, and Gadget with count 1. Option B correctly lists both rows, matching the query's result.

Answer

Widget, 2

Answer

Widget, 2; Gadget, 1

Answer

Widget, 2; Gadget, 1; Widget, 1

Question 11

A small business uses a MySQL database to manage inventory and sales. The database has two tables: Products (ProductID, ProductName, QuantityInStock) and Sales (SaleID, ProductID, QuantitySold, SaleDate). The business runs a nightly script that updates QuantityInStock by subtracting QuantitySold from the Products table based on the day's sales. Recently, the inventory levels have become inaccurate. For example, a product shows negative stock even though no sales occurred that day. The database administrator suspects the issue is related to how transactions are handled. The nightly script runs multiple UPDATE statements in a loop. If the script fails partway through, some products' stock is updated while others are not, leaving inconsistent data. The administrator wants to ensure that either all updates succeed or none do, and that the script does not interfere with daytime operations. Which action should the administrator take?

Accepted Answer

Wrap all UPDATE statements in a single transaction with READ COMMITTED isolation level and add error handling to roll back on failure. Option D is correct because wrapping all UPDATE statements in a single transaction with READ COMMITTED isolation level ensures atomicity: either all updates commit or none do, preventing partial updates. Adding error handling with a rollback on failure guarantees that if the script fails partway through, the entire transaction is undone, leaving the database consistent. READ COMMITTED isolation prevents dirty reads and minimizes locking, reducing interference with daytime operations.

Answer

Use a stored procedure that updates all products in one statement without error handling

Answer

Increase the frequency of the script to run every hour with smaller batches

Answer

Remove the transaction and use individual UPDATE statements with error logging

Question 12

A company needs to store customer orders with items and quantities. The database currently has a table 'Customers' and a table 'Products'. Which of the following is the best way to represent the many-to-many relationship between orders and products?

Accepted Answer

Create an Order_Items table with foreign keys to Orders and Products, plus a Quantity column.. Option B is correct because it creates a junction table (Order_Items) that resolves the many-to-many relationship between Orders and Products. The Order_Items table includes foreign keys referencing both the Orders and Products tables, plus a Quantity column to store the number of each product in an order, which is the standard normalized relational database design.

Answer

Create a single table containing all customer, order, and product information.

Answer

Store a JSON list of product IDs in an OrderProducts column in the Orders table.

Answer

Add columns Product1, Product2, Product3 to the Orders table.

Question 13

Which THREE of the following are valid database integrity constraints?

Accepted Answer

UNIQUE. A UNIQUE constraint ensures that all values in a column or a set of columns are distinct from one another, preventing duplicate entries. This is a standard SQL integrity constraint that enforces data uniqueness at the table level, and it is correctly listed as a valid database integrity constraint.

Answer

INDEX

Answer

CONDITION

Question 14

Refer to the exhibit. A database administrator runs the following query:

SELECT c.Name, SUM(o.Quantity) AS TotalItems
FROM Customers c
LEFT JOIN Orders o ON c.ID = o.CustomerID
GROUP BY c.Name;

What is the result for 'Alice'?

Accepted Answer

7. The LEFT JOIN ensures that Alice's record from the Customers table is retained even if there are no matching rows in Orders. The SUM(o.Quantity) aggregates the Quantity values from the joined Orders rows for Alice. Since Alice has two orders with quantities 2 and 5, the sum is 7, making option D correct.

Answer

2

Answer

NULL

Answer

5

Question 15

A company's database administrator notices that queries against a large customer table are running slowly. The table has millions of rows and is frequently filtered by the 'last_name' column. Which of the following is the BEST way to improve query performance without changing the application code?

Accepted Answer

Create an index on the 'last_name' column.. Creating an index on the 'last_name' column allows the database to locate rows matching filter conditions without scanning the entire table. This dramatically reduces I/O and CPU overhead for queries that filter by last_name, directly addressing the performance bottleneck without requiring any changes to application code.

Answer

Partition the table by row count.

Answer

Increase the database server's RAM.

Answer

Denormalize the table to reduce joins.

Question 16

Which THREE of the following are characteristics of a relational database? (Choose THREE.)

Accepted Answer

Queries are written using SQL.. Option B is correct because SQL (Structured Query Language) is the standard language used to query and manipulate data in relational databases. It allows users to perform operations such as SELECT, INSERT, UPDATE, and DELETE on structured data stored in tables.

Answer

The database uses a hierarchical model.

Answer

The database is schema-less and flexible.

Answer

Data is stored as JSON documents.

Question 17

Refer to the exhibit. A database administrator executes the following query: SELECT COUNT(*) FROM employees WHERE department_id = 10; What is the result?

Accepted Answer

2. The query `SELECT COUNT(*) FROM employees WHERE department_id = 10;` counts all rows in the `employees` table where the `department_id` column equals 10. According to the exhibit (not shown here but implied), there are exactly two employees in department 10, so the result is 2.

Answer

3

Answer

0

Answer

1

Question 18

Drag and drop the steps to open the Task Manager in Windows 10 into the correct order.

Question 19

Match each network type to its description.

Question 20

A user runs a query on a database table and notices that the results contain duplicate rows. Which SQL keyword would eliminate these duplicates?

Accepted Answer

DISTINCT. The DISTINCT keyword in SQL is used within a SELECT statement to remove duplicate rows from the result set. When a query returns multiple identical rows, DISTINCT filters them out so that only unique combinations of column values are displayed. This directly addresses the user's requirement to eliminate duplicate rows.

Answer

GROUP BY

Answer

WHERE

Answer

ORDER BY