FC0-U71 Data and Database Fundamentals Practice Test 4 — 15 Questions

Question 1

In a relational database, which constraint ensures that a foreign key value matches an existing primary key value in the referenced table?

Accepted Answer

Foreign key constraint. A foreign key constraint enforces referential integrity by requiring that any value in the foreign key column(s) must either match a primary key or unique key value in the referenced table, or be NULL. This ensures that relationships between tables remain consistent and prevents orphaned records. The constraint is defined on the child table and references the parent table's primary key (or a unique key).

Answer

Primary key constraint

Answer

Check constraint

Answer

Unique constraint

Question 2

A database has a table 'Orders' with columns OrderID (primary key), CustomerID, OrderDate, and TotalAmount. Which SQL statement will delete all orders placed before January 1, 2023?

Accepted Answer

DELETE FROM Orders WHERE OrderDate < '2023-01-01'. DELETE FROM Orders WHERE OrderDate < '2023-01-01' is the correct SQL syntax for removing rows that match a condition. DELETE is the standard DML statement for row-level removal, and the WHERE clause filters which orders are affected. The date literal '2023-01-01' is compared against OrderDate to remove all orders placed before that date.

Answer

DROP FROM Orders WHERE OrderDate < '2023-01-01'

Answer

DELETE OrderDate < '2023-01-01' FROM Orders

Answer

REMOVE FROM Orders WHERE OrderDate < '2023-01-01'

Question 3

Which of the following are advantages of using a cloud database service (DBaaS) compared to an on-premises database? (Select TWO.)

Accepted Answer

Managed maintenance and updates. DBaaS provides managed maintenance and automatic scalability without hardware management. High availability is inherent, and initial cost is lower (pay-as-you-go).

Answer

Full control over underlying hardware

Answer

Requires dedicated IT staff for database administration

Answer

Higher initial capital expenditure

Question 4

Which of the following is a primary advantage of using a database instead of a flat file system?

Accepted Answer

Supports concurrent multi-user access with data integrity. A database management system (DBMS) provides concurrent multi-user access with mechanisms like locking, transactions, and ACID properties to maintain data integrity. This is a primary advantage over flat file systems, which lack built-in concurrency control and integrity enforcement, leading to data corruption or inconsistency when multiple users access the same file.

Answer

No need for data validation

Answer

Data is stored in a single file for easy backup

Answer

Simpler to set up and maintain

Question 5

A database administrator needs to ensure that every record in the 'Orders' table can be uniquely identified. Which constraint should be applied to the 'OrderID' column?

Accepted Answer

PRIMARY KEY. A PRIMARY KEY constraint uniquely identifies each record in a table and implicitly enforces uniqueness and NOT NULL. Applying it to OrderID guarantees every order has a unique, non-null identifier, which is exactly what the DBA needs.

Answer

FOREIGN KEY

Answer

UNIQUE constraint

Answer

CHECK constraint

Question 6

A company uses a relational database with a 'Customers' table and an 'Orders' table. Each order must be linked to exactly one customer. Which type of relationship exists between Customers and Orders?

Accepted Answer

One-to-many. In a relational database, if each order must be linked to exactly one customer, but a customer can have multiple orders, the relationship is one-to-many. The 'one' side is Customers (each customer is unique) and the 'many' side is Orders (multiple orders per customer). This is implemented via a foreign key in the Orders table referencing the primary key in Customers.

Answer

No relationship

Answer

Many-to-many

Answer

One-to-one

Question 7

Which SQL statement would you use to retrieve the names of all products with a price greater than $50 from a table named 'Products'?

Accepted Answer

SELECT Name FROM Products WHERE Price > 50. The correct SQL syntax to filter rows is SELECT ... FROM ... WHERE condition. Option A retrieves the Name column from Products where Price exceeds 50, which is exactly the requested query. WHERE is the standard clause for row-level filtering in a SELECT statement, and the comparison operator > correctly expresses 'greater than.'

Answer

SELECT Name FROM Products HAVING Price > 50

Answer

SELECT * FROM Products IF Price > 50

Answer

GET Name FROM Products WHERE Price > 50

Question 8

Which TWO of the following are key characteristics of a NoSQL database compared to a traditional relational database?

Accepted Answer

Flexible schema design. Option D (Flexible schema design) is correct because NoSQL databases such as document stores (MongoDB), key-value stores (Redis), and wide-column stores (Cassandra) do not require a fixed, predefined table schema, allowing each record to have different fields and structures. Option E (Horizontal scalability) is correct because NoSQL systems are architecturally designed to scale out by distributing data across many commodity servers via sharding and partitioning, rather than relying on expensive vertical scaling of a single server. In contrast, option A (Strict schema enforcement) describes relational databases, which enforce a rigid schema via DDL constraints, and is not a NoSQL trait. Option B (Uses SQL as the query language) is incorrect because NoSQL databases typically use non-SQL APIs such as JSON queries, key lookups, or CQL, though some offer SQL-like interfaces. Option C (ACID transactions are always guaranteed) is incorrect because many NoSQL databases follow the BASE model and provide only eventual consistency, with ACID guarantees limited to single-document or optional multi-document transactions rather than being always guaranteed.

Answer

Strict schema enforcement

Answer

Uses SQL as the query language

Answer

ACID transactions are always guaranteed

Question 9

Which TWO of the following are benefits of using a cloud database service (DBaaS) over an on-premises database?

Accepted Answer

No need to manage physical hardware. Option A is correct because a DBaaS provider owns and operates the underlying physical servers, storage, and networking, so the customer is freed from hardware procurement, racking, power, cooling, and replacement tasks. Option E is correct because DBaaS platforms typically offer elastic, automated scaling of compute, storage, and sometimes read replicas in response to workload demand, which is difficult to achieve quickly with fixed on-premises capacity. Option B is not correct because DBaaS abstracts the operating system away from the customer, who generally gets far less OS-level control than with an on-premises deployment. Option C is not correct because lower latency is not guaranteed; network round trips to the provider's region can actually increase latency compared with a local database. Option D is not correct because DBaaS data resides in the provider's data centers, not locally on the customer's premises.

Answer

Full control over the operating system

Answer

Lower latency guaranteed

Answer

Data is always stored locally

Question 10

Which THREE of the following are types of data integrity enforced in relational databases?

Accepted Answer

Referential integrity. Referential integrity (A) is a relational-database constraint that ensures a foreign key value in one table matches an existing primary key or unique value in the referenced table, preventing orphaned rows. Entity integrity (B) requires that every table have a primary key and that its values be unique and non-null, so each row is uniquely identifiable. Domain integrity (E) restricts a column's values to a defined set of valid data types, formats, ranges, or allowed values, typically enforced with CHECK constraints, data types, and NOT NULL. Network integrity (C) and file integrity (D) are not relational-database integrity types; they concern data transmission and file-level integrity (e.g., checksums), not the relational model's constraint categories.

Answer

Network integrity

Answer

File integrity

Question 11

Which THREE of the following are valid SQL statements?

Accepted Answer

SELECT * FROM Employees WHERE Department = 'Sales'. Option B, 'SELECT * FROM Employees WHERE Department = \'Sales\'', is valid SQL because SELECT with the asterisk wildcard retrieves all columns from the Employees table, and the WHERE clause filters rows where Department equals the string literal 'Sales'. Option D, 'DELETE FROM Employees WHERE ID = 5', is valid SQL because DELETE FROM removes rows from the Employees table, and the WHERE clause restricts deletion to the row whose ID equals 5. Option E, 'INSERT INTO Employees VALUES (\'John\', 50000)', is valid SQL because INSERT INTO with the VALUES clause adds a new row containing the string 'John' and the numeric value 50000 into the table's columns in order. Option A is not valid because SQL has no REMOVE TABLE statement; the correct statement is DROP TABLE. Option C is not valid because SQL has no MODIFY statement for changing data; the correct statement is UPDATE Employees SET Salary = 60000 WHERE ID = 1.

Answer

REMOVE TABLE Employees

Answer

MODIFY Employees SET Salary = 60000 WHERE ID = 1

Question 12

A small retail company stores customer orders in a relational database. The IT administrator wants each order record to automatically receive a unique number without manual entry when a new order is inserted. Which database feature should be configured to meet this requirement?

Accepted Answer

Set the OrderID column as an AUTO_INCREMENT (or IDENTITY) column.. The requirement is automatic generation of a unique number for each new order. An AUTO_INCREMENT or IDENTITY column is the standard relational database feature that produces a sequential unique value on insert, removing manual entry and preventing duplicates. Other options either require manual input, only validate data, or enforce referential integrity without generating values.

Answer

Add a CHECK constraint that verifies the OrderID is numeric.

Answer

Define a foreign key on the OrderID column referencing the CustomerID column.

Answer

Create a stored procedure that prompts the user to enter the next order number.

Question 13

A junior database administrator is reviewing the structure of a relational database used by a library. The database contains a Books table, a Members table, and a Loans table that records which member borrowed which book and the loan date. Which TWO of the following statements accurately describe the role of the Loans table in this database? (Choose two.)

Accepted Answer

It contains foreign keys that reference the primary keys of both the Books and Members tables.. The Loans table is a junction table that resolves the many-to-many relationship between Books and Members by containing foreign keys to both. It does not duplicate book details, does not remove the need for primary keys, and is not a NoSQL document store. These characteristics are fundamental to relational database design and normalization.

Answer

It is an example of a NoSQL document store.

Answer

It stores the complete details of each book, including title, author, and ISBN.

Answer

It eliminates the need for a primary key in the Books table.

Question 14

A database administrator is designing a new table to store employee records. The administrator wants to ensure that no two employees can have the same employee identification number, and that the employee identification number cannot be left empty. Which constraint should be applied to the EmployeeID column to meet both requirements?

Accepted Answer

PRIMARY KEY. The PRIMARY KEY constraint uniquely identifies each row and enforces both uniqueness and non-null values. This meets the need to prevent duplicate employee identification numbers and to disallow empty values. UNIQUE allows nulls, FOREIGN KEY enforces referential integrity, and CHECK validates conditions but does not enforce uniqueness across rows.

Answer

CHECK

Answer

UNIQUE

Answer

FOREIGN KEY

Question 15

A small business is setting up a database to track its customers and their orders. The owner wants to ensure that every order is linked to an existing customer and that the customer ID in the Order table cannot refer to a non-existent customer. Which database concept should be implemented?

Accepted Answer

Foreign key constraint on the Order table. The requirement is to enforce that every order is linked to a valid customer, which is referential integrity. A foreign key constraint on the Order table referencing the Customer table's primary key ensures that any customer ID entered in the Order table must already exist in the Customer table. This prevents orphaned orders and maintains consistent relationships between the two tables.

Answer

Primary key constraint on the Customer table

Answer

Check constraint on the Order table

Answer

Unique constraint on the Order table