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

Question 1

A small retail store keeps its sales records in a spreadsheet file. The owner complains that customer addresses are typed slightly differently in different rows, and that correcting one address requires editing many rows. Which data concept best describes the root problem the owner is experiencing?

Choose an answer to begin — your selection is scored in the full session.

Accepted Answer

Data redundancy with resulting inconsistency. Because the same customer detail is retyped into every sales row, one real-world fact exists in many places at once. That duplication is redundancy, and the divergent spellings are the inconsistency it produces. A structure that stores the customer once and references it from sales rows removes both symptoms.

Answer

A weak wireless network connection to the file share

Answer

Insufficient storage capacity on the workstation

Answer

Missing antivirus definitions on the store's computer

Question 2

A user needs to retrieve only the 'ProductName' and 'Price' columns from a table named 'Products' where the 'Category' is 'Electronics'. Which SQL statement should the user execute?

Accepted Answer

SELECT ProductName, Price FROM Products WHERE Category = 'Electronics';. The SQL SELECT statement retrieves data from a table. To get specific columns, you list them after SELECT. To filter rows, you use the WHERE clause. The correct statement selects ProductName and Price from Products and filters where Category equals 'Electronics'. This precisely meets the user's need to view only electronics products with their names and prices.

Answer

SELECT ProductName, Price FROM Products HAVING Category = 'Electronics';

Answer

SELECT * FROM Products WHERE Category = 'Electronics';

Answer

SELECT ProductName, Price FROM Products ORDER BY Category = 'Electronics';

Question 3

A help desk technician is creating a spreadsheet to track support tickets. They want each ticket to have a unique identifier that is automatically generated when a new row is added, and they want the value to never repeat even if rows are deleted. Which spreadsheet feature should they use?

Accepted Answer

Use an AutoNumber or automatic row ID feature. An automatic row ID or AutoNumber feature is designed to produce a unique value for every new record without user intervention. It continues to generate new values even after records are deleted, so duplicates do not occur. Data validation, text formatting, and sorting do not automatically create or guarantee unique identifiers.

Answer

Apply a data validation rule to the ticket ID column

Answer

Format the ticket ID column as text

Answer

Sort the ticket ID column in ascending order

Question 4

A small business keeps its customer list in a single spreadsheet where the customer's city and state are typed into every invoice row. The owner wants a structure that stores each customer's address once and references it from many invoices without duplicating the address text. Which database concept should the owner apply?

Accepted Answer

Create a separate Customers table with a primary key, and store only that key in the Invoices table.. Storing each customer's address once and referencing it from many invoices is achieved by moving the address into a Customers table with a primary key and placing a foreign key in the Invoices table. This removes repeated text, prevents inconsistent updates, and preserves the one-to-many relationship between customers and invoices.

Answer

Apply a UNIQUE constraint to the City column in the invoice table.

Answer

Convert the spreadsheet to a comma-separated values file and re-import it each month.

Answer

Add an index to the City column so repeated city names are stored only once.

Question 5

A hospital's scheduling system must answer questions such as 'which appointments belong to patient 4471, and which physician is assigned to each?' Records change frequently, and staff need immediate, consistent answers. Which data structure characteristic is most important for this requirement?

Accepted Answer

Defined relationships between records held in related tables. The scheduling question is fundamentally relational: an appointment is tied to one patient and one physician. Modeling each entity separately and enforcing the links lets the system follow those connections and return accurate answers while records are being edited. Storage-size and file-format choices do not address the connection requirement.

Answer

Very large binary media objects stored alongside each patient record

Answer

A single flat file listing every appointment in arrival order

Answer

Compression of every text field to reduce file size

Question 6

A small business stores customer orders in a relational database. The Orders table has a foreign key column named CustomerID that references the Customers table. A developer needs to prevent any order from being inserted if the CustomerID does not already exist in the Customers table. Which database feature should be used?

Accepted Answer

A referential integrity constraint. Referential integrity is the relational database rule that a foreign key must reference an existing primary key. It blocks inserts or updates that would create an order with a nonexistent CustomerID. Unique constraints, default values, and indexes serve different purposes and do not validate the relationship between the Orders and Customers tables.

Answer

A unique constraint on the CustomerID column in the Orders table

Answer

A default value of 0 for the CustomerID column

Answer

An index on the CustomerID column in the Orders table

Question 7

A technician is documenting a hospital system where each patient can have many appointments, but every appointment belongs to exactly one patient and one doctor. The technician must describe how the Appointment table relates to the Patient table. Which term correctly describes this relationship?

Accepted Answer

One-to-many. Because one patient can be associated with many appointments while each appointment is tied to a single patient, the relationship is one-to-many. This is implemented by placing a PatientID foreign key in the Appointment table, which also allows a similar DoctorID foreign key to capture the appointment-to-doctor link.

Answer

Many-to-many

Answer

Recursive

Answer

One-to-one

Question 8

A logistics analyst needs to record which driver delivered each shipment. A driver can deliver many shipments, and each shipment is delivered by exactly one driver. Which relationship type exists between drivers and shipments?

Accepted Answer

One-to-many. The business rule is asymmetric: one driver appears against many shipment rows, but each shipment row names only one driver. That is a one-to-many relationship, and it is normally implemented by storing the driver's key value in the shipment table rather than by creating a separate linking table.

Answer

Zero-to-one

Answer

One-to-one

Answer

Many-to-many

Question 9

A bookstore owner wants to list all book titles stored in a table named Inventory, sorted alphabetically by title. Which SQL statement should be used?

Accepted Answer

SELECT title FROM Inventory ORDER BY title;. Sorting query output is accomplished with the ORDER BY clause, which arranges rows by one or more columns and defaults to ascending order. The statement that selects the title column and applies ORDER BY title returns every book title in alphabetical order, matching the bookstore owner's request without filtering or aggregating the data.

Answer

SELECT title FROM Inventory WHERE title ASC;

Answer

SELECT title FROM Inventory SORT BY title;

Answer

SELECT title FROM Inventory GROUP BY title;

Question 10

A database administrator is designing a table to store employee records. She needs to ensure that no two employees can have the same employee number, and that the employee number is never left blank. Which combination of constraints should she apply to the employee number column?

Accepted Answer

UNIQUE and NOT NULL. To require that every employee has a unique employee number, the column needs both a uniqueness rule and a non-null rule. UNIQUE prevents duplicates, and NOT NULL prevents blanks. A primary key would also work, but the question asks for a combination of constraints; UNIQUE and NOT NULL are the specific constraints that meet both conditions.

Answer

INDEX and AUTO_INCREMENT

Answer

PRIMARY KEY and FOREIGN KEY

Answer

CHECK and DEFAULT

Question 11

An analyst runs a query against a sales database and wants to return only the rows where the Region column equals 'West' and the SaleAmount column is greater than 1000. The analyst also wants the results sorted from highest SaleAmount to lowest. Which clause combination should the analyst use?

Accepted Answer

WHERE Region = 'West' AND SaleAmount > 1000 ORDER BY SaleAmount DESC. Row-level filtering with two conditions requires WHERE combined with AND, and descending order requires ORDER BY with DESC. Quoting the text literal 'West' keeps the comparison valid, so the clause that filters both conditions and sorts from highest to lowest SaleAmount meets the analyst's needs.

Answer

WHERE Region = 'West' OR SaleAmount > 1000 ORDER BY SaleAmount ASC

Answer

WHERE Region = West AND SaleAmount > 1000 ORDER BY SaleAmount DESC

Answer

HAVING Region = 'West' AND SaleAmount > 1000 ORDER BY SaleAmount DESC

Question 12

A startup is choosing a data store for a product catalog where each item may have a different set of attributes, such as a camera with lens mount and sensor size versus a book with ISBN and page count. The catalog is read far more often than it is written, and the schema is expected to change frequently. Which TWO characteristics make a document-oriented NoSQL store a suitable fit? (Choose two.)

Accepted Answer

Each item can be stored as a self-contained document whose fields vary from item to item. Variable per-item attributes and frequent schema change are the two driving requirements. A document store satisfies both because each item carries its own fields and new fields can be introduced incrementally rather than through a global migration. Fixed column layouts and cross-table integrity guarantees describe relational behavior instead.

Answer

It enforces a fixed column layout that all catalog items must follow

Answer

It guarantees strict multi-table referential integrity across all catalog records

Answer

It requires every query to join at least three collections before returning a result

Question 13

A hospital stores patient records in a relational database. Administrators need to record that a patient can have multiple allergies, and each allergy may affect multiple patients. Which type of relationship exists between patients and allergies, and how is it implemented?

Accepted Answer

Many-to-many, implemented with a junction table containing foreign keys to both patients and allergies.. Because a patient can have several allergies and an allergy can affect several patients, the relationship is many-to-many. The standard relational solution is a junction table that holds foreign keys to both the patients and allergies tables, enabling flexible associations and preserving referential integrity without duplicating data in either primary table.

Answer

One-to-many, implemented by adding a foreign key to the allergies table.

Answer

Many-to-many, implemented by storing a comma-separated list of allergy names in the patients table.

Answer

One-to-one, implemented by adding a foreign key to the patients table.

Question 14

A data analyst is working with a large dataset stored in a columnar database. She needs to retrieve only the customer name and total order amount for orders placed in the last 30 days. Which two actions are most appropriate for optimizing this query? (Choose two.)

Accepted Answer

Use a SELECT statement that lists only the customer name and total order amount columns. In a columnar database, performance improves when queries read only the columns they need and filter rows as early as possible. Listing specific columns avoids reading unnecessary data, and a WHERE clause on the order date limits the rows scanned. Using SELECT *, relying on row-based indexes, or sorting before filtering do not provide the same benefits.

Answer

Use SELECT * to ensure all columns are available for analysis

Answer

Sort the result set by customer name before filtering

Answer

Create a row-based index on the order date column

Question 15

A junior administrator is reviewing a database schema and must identify which statements about primary keys and foreign keys are accurate. (Choose two.)

Accepted Answer

A foreign key value must match an existing primary key value in the referenced table, or be NULL if the column allows it.. A primary key uniquely identifies rows and forbids NULLs, while a foreign key must either match an existing referenced key or be NULL when the column allows it. These two rules together enforce entity integrity and referential integrity, which are the cornerstones of a reliable relational schema.

Answer

A primary key value can be changed freely at any time without affecting related tables.

Answer

A foreign key must be unique within its own table.

Answer

A table can contain multiple primary keys to improve query performance.