FC0-U71 Data and Database Fundamentals — 20 Questions

Question 1

A database has a 'Students' table with columns: StudentID (primary key), Name, Major. Another table 'Enrollments' has columns: EnrollmentID (primary key), StudentID (foreign key), CourseID. Which SQL query correctly lists each student's name and their enrolled courses by joining the tables?

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

Accepted Answer

SELECT Name, CourseID FROM Students INNER JOIN Enrollments ON Students.StudentID = Enrollments.StudentID;. INNER JOIN returns only rows where the join condition (Students.StudentID = Enrollments.StudentID) is satisfied, which lists each student's name only if they have at least one enrollment. Option A (RIGHT JOIN) would include all enrollments, even if a StudentID does not match a student, resulting in NULL names for orphaned enrollments. Option C uses an implicit cross join (comma) with a WHERE clause; though it produces the same result set, it is not considered a proper JOIN statement and is less clear, so it does not meet the requirement of 'joining tables'. Option D (LEFT JOIN) would include students with no enrollments, showing NULL for CourseID, which does not match the requirement of listing only enrolled courses.

Answer

SELECT Name, CourseID FROM Students RIGHT JOIN Enrollments ON Students.StudentID = Enrollments.StudentID;

Answer

SELECT Name, CourseID FROM Students, Enrollments WHERE Students.StudentID = Enrollments.StudentID;

Answer

SELECT Name, CourseID FROM Students LEFT JOIN Enrollments ON Students.StudentID = Enrollments.StudentID;

Question 2

Which SQL statement is used to remove all rows from a table while keeping the table structure?

Accepted Answer

DELETE FROM table_name;. DELETE FROM table_name removes all rows from a table while preserving the table structure, including its columns, constraints, indexes, and relationships. It is a DML (Data Manipulation Language) operation that can be filtered with a WHERE clause and can be rolled back within a transaction. This is exactly what the question asks for.

Answer

DROP TABLE

Answer

ALTER TABLE

Answer

REMOVE FROM table_name;

Question 3

Which of the following best describes normalization in a relational database?

Accepted Answer

Removing duplicate data from a table. Normalization is the process of organizing data in a relational database to minimize redundancy and dependency by dividing large tables into smaller, related tables and defining relationships between them. Option B, 'Removing duplicate data from a table,' captures the essence of normalization: it eliminates duplicate data by ensuring each piece of information is stored in only one place. While normalization often involves creating additional tables, the primary goal is to reduce redundancy and improve data integrity, not to improve performance (which can sometimes degrade due to joins).

Answer

Encrypting sensitive data in the database

Answer

Increasing the number of tables to improve performance

Answer

Combining all data into a single table

Question 4

An online store uses a relational database with tables: Customers, Orders, and Products. Which of the following are valid ways to query the database structure? (Select THREE.)

Accepted Answer

DESCRIBE Customers. Option A (DESCRIBE Customers) is correct because DESCRIBE (or its synonym EXPLAIN) is the standard SQL command that returns the column definitions of a table—column names, data types, nullability, keys, and defaults—which is exactly metadata about the database structure. Option D (SHOW TABLES) is correct because it is the MySQL/SQL-standard administrative statement that lists the tables present in the current database, revealing the schema's structure at the table level. Option E (SELECT * FROM information_schema.tables WHERE table_schema = 'store') is correct because information_schema is the ANSI SQL standard metadata catalog, and querying its tables view filtered by table_schema returns structural information (table names, types, engines) about the store schema. Option B (SELECT * FROM Customers) is not a structural query—it retrieves the actual row data stored in the Customers table, i.e., data manipulation rather than metadata inspection. Option C (INSERT INTO Customers VALUES (...)) is a data manipulation statement that adds rows to the table and does not query or reveal the database structure at all.

Answer

SELECT * FROM Customers

Answer

INSERT INTO Customers VALUES (...)

Question 5

A database designer is normalizing a table that contains repeating groups (multiple values in one column). Which of the following are goals of normalization? (Select THREE.)

Accepted Answer

Eliminate update anomalies. Normalization is a formal design process whose primary goals include eliminating update anomalies (C): by decomposing repeating groups into separate relations, insert, update, and delete anomalies caused by partial and transitive dependencies are removed. It also aims to reduce data redundancy (D), since storing the same fact in multiple rows or columns wastes space and creates inconsistency risks; each fact should be stored in exactly one place. Additionally, normalization enforces data integrity (E) by ensuring that dependencies are represented as keys and constraints, so the database structure itself prevents invalid or inconsistent states. Option A is not a goal because normalization typically increases the number of tables and complexity rather than simplifying the schema, and option B is not a goal because normalization often requires more joins, which can reduce rather than improve query performance.

Answer

Simplify the database schema

Answer

Improve query performance

Question 6

A database designer wants to reduce data redundancy in a relational database. Which THREE of the following are normalization techniques or concepts?

Accepted Answer

Eliminating partial dependencies by moving attributes to appropriate tables. Option B is correct because eliminating partial dependencies is the core goal of Second Normal Form (2NF): attributes that depend on only part of a composite primary key are moved into a separate table keyed by that partial key, which removes redundancy. Option C is correct because removing duplicate columns by creating separate tables is the essence of normalization, particularly 1NF/2NF decomposition, where repeating or redundant column groups are split into related tables linked by keys. Option E is correct because ensuring each table has a primary key is a fundamental normalization requirement: a primary key uniquely identifies each row and enforces entity integrity, which is necessary before dependencies can be properly analyzed and removed in 2NF and 3NF. Option A is incorrect because denormalization deliberately reintroduces redundancy to improve read performance, which is the opposite of reducing redundancy. Option D is incorrect because storing all data in a single table maximizes redundancy and anomalies, rather than normalizing the schema.

Answer

Denormalization for performance

Answer

Using a single table to store all data

Question 7

A small marketing team maintains a spreadsheet of client contact details. They need to ensure that each client's email address is unique and that no blank email addresses are saved. Which database constraint should they apply to the Email column when moving this data into a relational database table?

Accepted Answer

UNIQUE. The UNIQUE constraint is designed specifically to prevent duplicate values in a column, which matches the requirement that every client email address be distinct. A primary key would also enforce uniqueness but imposes additional restrictions, such as disallowing nulls and limiting the table to one such key, making it unsuitable when email is not the intended row identifier.

Answer

CHECK

Answer

FOREIGN KEY

Answer

PRIMARY KEY

Question 8

A database administrator is designing a new relational database for a hospital. The system must ensure that each patient has a unique medical record number, that no two patients share the same combination of first name, last name, and date of birth, and that every prescription is associated with a valid patient. Which two database constraints should be implemented to meet these requirements? (Choose two.)

Accepted Answer

Primary key on the MedicalRecordNumber column. The requirements are to ensure unique medical record numbers for patients and to associate every prescription with a valid patient. A primary key on the MedicalRecordNumber column guarantees uniqueness and non-nullability. A foreign key on the Prescription table referencing the Patient table enforces referential integrity, ensuring each prescription links to an existing patient. Together, these constraints satisfy the stated needs.

Answer

Check constraint on the DateOfBirth column to ensure it is in the past

Answer

Unique constraint on the combination of FirstName, LastName, and DateOfBirth

Answer

Default constraint on the PrescriptionDate column

Question 9

Which of the following is a primary advantage of using a database over a flat file system for storing customer records?

Accepted Answer

Supports simultaneous multi-user access. A database management system supports concurrent multi-user access through locking, transactions, and isolation levels, allowing many users to read and write customer records simultaneously without corrupting data. A flat file system offers no such concurrency control, so simultaneous writes can overwrite each other.

Answer

Simpler to set up and manage

Answer

Stores data in a non-structured format

Answer

No need for a query language

Question 10

A database administrator wants to enforce that every record in the 'Orders' table must have a non-null unique value in the 'OrderID' column. Which database concept ensures this?

Accepted Answer

Entity integrity. Entity integrity requires that every row in a table be uniquely identifiable by a primary key, which by definition cannot be NULL and must be unique. Enforcing a non-null, unique value in the 'OrderID' column is exactly the definition of entity integrity, typically implemented via a PRIMARY KEY constraint. This ensures each record represents a distinct, identifiable entity.

Answer

Referential integrity

Answer

Input validation

Answer

Domain integrity

Question 11

In a relational database, a foreign key in the 'Enrollments' table references the primary key of the 'Students' table. What does this relationship primarily enforce?

Accepted Answer

Referential integrity. Foreign keys enforce referential integrity, ensuring that values in the foreign key column match a primary key value in the referenced table.

Answer

Entity integrity

Answer

Normalization

Answer

Domain integrity

Question 12

A company stores product data in a MongoDB database. Each product document contains fields like 'name', 'price', and 'tags' (an array). What type of NoSQL database is MongoDB?

Accepted Answer

Document store. MongoDB is a document store because it stores data in flexible, JSON-like documents (BSON) where each document can have a different structure. The example product document with fields like 'name', 'price', and 'tags' (an array) is a classic document-oriented model, allowing nested data and arrays without a fixed schema. This contrasts with other NoSQL types that organize data differently.

Answer

Wide-column store

Answer

Graph database

Answer

Key-value store

Question 13

Which SQL statement is used to retrieve all columns from a table named 'Customers' where the city is 'London'?

Accepted Answer

SELECT * FROM Customers WHERE city = 'London';. SELECT * FROM Customers WHERE city = 'London'; is the correct SQL statement to retrieve all columns from the Customers table where the city is London. SELECT * specifies all columns, FROM identifies the table, and WHERE filters rows based on the city condition. The semicolon terminates the statement.

Answer

RETRIEVE * FROM Customers WHERE city = 'London';

Answer

SELECT * FROM Customers HAVING city = 'London';

Answer

GET * FROM Customers WHERE city = 'London';

Question 14

A database designer wants to split a table into two to reduce data redundancy and avoid update anomalies. This process is known as:

Accepted Answer

Normalization. Normalization is the process of organizing data to reduce redundancy and improve integrity.

Answer

Denormalization

Answer

Indexing

Answer

Partitioning

Question 15

Which DBMS is an example of a proprietary relational database system?

Accepted Answer

Oracle Database. Oracle Database is a proprietary relational database management system owned by Oracle Corporation. It is commercial, closed-source, and requires a license for use, which distinguishes it from open-source alternatives. Therefore, it is the correct example of a proprietary relational DBMS.

Answer

MySQL

Answer

PostgreSQL

Answer

SQLite

Question 16

A company uses a cloud database service where the provider automatically handles backups, patching, and scaling. This deployment model is known as:

Accepted Answer

Database as a Service (DBaaS). Database as a Service (DBaaS) is a cloud service model where the provider manages the database infrastructure, including backups, patching, scaling, and high availability, while the customer focuses on data and queries. This matches the scenario where the provider automatically handles those operational tasks. DBaaS is a specialized form of PaaS focused on database engines.

Answer

Platform as a Service (PaaS)

Answer

Software as a Service (SaaS)

Answer

Infrastructure as a Service (IaaS)

Question 17

Which SQL command is used to add a new row to a table?

Accepted Answer

INSERT INTO. INSERT INTO is the SQL command for adding new rows.

Answer

ADD

Answer

CREATE

Answer

UPDATE

Question 18

In a relational database, which type of relationship is typically implemented by creating a third table (junction table) that contains foreign keys from both related tables?

Accepted Answer

Many-to-many. A many-to-many relationship occurs when multiple records in one table relate to multiple records in another. Relational databases cannot directly implement this with a single foreign key, so a junction table (also called an associative or bridge table) is created. This third table contains foreign keys referencing the primary keys of both related tables, and each row represents a unique association between the two entities.

Answer

Self-referencing

Answer

One-to-one

Answer

One-to-many

Question 19

Which of the following is a characteristic of a NoSQL database compared to a relational database?

Accepted Answer

Supports flexible schema. NoSQL databases are characterized by a flexible schema, meaning they do not require a fixed table structure and can handle unstructured or semi-structured data. This allows for easy evolution of data models and rapid development. In contrast, relational databases enforce a strict schema and normalization.

Answer

Requires normalized data

Answer

Enforces strict schema definition

Answer

Uses SQL for queries

Question 20

A database administrator needs to choose a database for an e-commerce application that requires high availability and automatic scaling. Which TWO options are cloud database services?

Accepted Answer

Google Cloud SQL. Google Cloud SQL (B) is a fully managed, cloud-hosted relational database service on Google Cloud Platform that supports MySQL, PostgreSQL, and SQL Server, making it a cloud database service suitable for high availability and scaling needs. Amazon RDS (E) is AWS's managed relational database service that automates provisioning, patching, backups, and Multi-AZ failover, directly matching the e-commerce requirement for high availability and automatic scaling. By contrast, SQLite (A) is an embedded, serverless file-based library, not a cloud service. Microsoft SQL Server Express (C) is a free on-premises edition of SQL Server with size and resource limits, and MySQL Community Edition (D) is a self-managed open-source database typically deployed on your own infrastructure, so neither is a managed cloud database service.

Answer

SQLite

Answer

Microsoft SQL Server Express

Answer

MySQL Community Edition