54 questions · Itf Database Fundamentals topic · All types, answers revealed
Which of the following is a characteristic of a relational database table?
Correct definition.
Why this answer
In a relational database, each row represents a single record (tuple) and each column represents an attribute (field).
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?
Correct; deletes rows with OrderDate before the specified date.
Why this answer
DELETE FROM table WHERE condition removes rows that satisfy the condition.
Which DBMS is an example of a proprietary relational database system?
Oracle is a proprietary commercial RDBMS.
Why this answer
Oracle Database is proprietary (commercial). MySQL, PostgreSQL, and SQLite are open-source.
Which of the following SQL statements will add a new row to the 'Products' table?
Correct syntax for inserting a new row.
Why this answer
INSERT INTO table VALUES (value1, value2, ...) adds a new row.
A sales database has a Customers table with CustomerID as the primary key and an Orders table that includes CustomerID. Which type of relationship is typically established between Customers and Orders?
Correct; one customer can have many orders.
Why this answer
A customer can have multiple orders, but each order belongs to one customer, so it's a one-to-many relationship.
Which of the following best describes normalization in a relational database?
Correct; normalization reduces data redundancy.
Why this answer
Normalization is the process of organizing data to reduce redundancy and improve data integrity.
In a relational database, which constraint ensures that a foreign key value matches an existing primary key value in the referenced table?
Correct; foreign key enforces referential integrity.
Why this answer
Referential integrity is enforced by foreign key constraints, ensuring that relationships between tables remain consistent.
Which TWO of the following are key characteristics of a NoSQL database compared to a traditional relational database?
NoSQL databases allow varied data structures within the same collection.
Why this answer
NoSQL databases often use flexible schemas (allowing varied data structures) and can scale horizontally across many servers.
A company stores customer data in a flat file. Which of the following is a disadvantage of using a flat file compared to a relational database?
Correct; flat files lack concurrency control.
Why this answer
Flat files lack built-in support for concurrent access, leading to data corruption or conflicts when multiple users try to update the file simultaneously.
Which of the following is a characteristic of a NoSQL database compared to a relational database?
NoSQL databases allow different documents to have different fields.
Why this answer
NoSQL databases often have a flexible schema, allowing different structures in the same collection.
Which of the following best describes a NoSQL database?
Correct. NoSQL databases allow dynamic schemas.
Why this answer
NoSQL databases are designed for flexible schema and can handle unstructured data, often used for big data and real-time web applications.
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.)
Correct; shows columns of the Customers table.
Why this answer
SELECT retrieves data, SHOW TABLES lists tables in MySQL, and DESCRIBE shows column info. INSERT adds data, not structure.
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?
MongoDB stores documents (JSON-like objects) with flexible schemas.
Why this answer
MongoDB is a document-oriented NoSQL database that stores data in JSON-like documents.
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?
Each customer can have multiple orders, but each order links to one customer.
Why this answer
One customer can have many orders, but each order belongs to one customer, which is a one-to-many relationship.
Which THREE of the following are valid SQL statements?
Valid SELECT query.
Why this answer
SELECT, INSERT, UPDATE, DELETE are core DML statements. CREATE is DDL but also valid SQL.
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?
Google Cloud SQL is a fully managed database service.
Why this answer
Amazon RDS and Google Cloud SQL are cloud-based DBaaS offerings.
Which of the following is a benefit of using a database instead of a flat file?
Correct. Databases handle concurrent access with locking and transactions.
Why this answer
Databases provide concurrent access control, allowing multiple users to read/write data simultaneously without corruption.
A company needs to store customer orders and ensure that each order is uniquely identified. Which database concept should be used?
Correct. A primary key uniquely identifies each row.
Why this answer
A primary key uniquely identifies each row in a table, ensuring no duplicates and no null values.
A company uses a cloud database service where the provider automatically handles backups, patching, and scaling. This deployment model is known as:
DBaaS is a managed database service in the cloud.
Why this answer
Database-as-a-Service (DBaaS) is a cloud service where the provider manages the database infrastructure.
Which of the following is an example of a NoSQL database that stores data as JSON-like documents?
Correct. MongoDB is a document NoSQL database.
Why this answer
MongoDB is a document-oriented NoSQL database that stores data in BSON (binary JSON) format.
Which TWO of the following are characteristics of a relational database? (Select TWO.)
Correct. This is a fundamental characteristic.
Why this answer
Relational databases store data in tables and enforce relationships through keys.
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?
Many-to-many requires an associative table with foreign keys to both tables.
Why this answer
Many-to-many relationships require a junction table to associate records from both sides.
A SELECT statement combines rows from two tables based on a related column. Which SQL clause is used to accomplish this?
Correct. JOIN combines rows from tables based on a condition.
Why this answer
JOIN clauses, such as INNER JOIN, combine rows from two tables based on a related column.
Which SQL statement is used to retrieve all columns from a table named 'Customers' where the city is 'London'?
This correctly uses SELECT with WHERE clause.
Why this answer
The SELECT statement retrieves data. The correct syntax is: SELECT * FROM Customers WHERE city = 'London';
A user needs to retrieve all product names and prices from a table named 'Products' where the price is greater than 50. Which SQL statement should be used?
Correct. This retrieves the required columns with the condition.
Why this answer
The SELECT statement with a WHERE clause filters rows based on a condition.
Which TWO of the following are examples of popular relational database management systems (RDBMS)? (Select TWO.)
Correct. MySQL is a popular RDBMS.
Why this answer
MySQL and PostgreSQL are both widely used relational databases.
Which TWO of the following are examples of NoSQL database types?
Document stores store data in documents.
Why this answer
Document stores (e.g., MongoDB) and key-value stores (e.g., Redis) are NoSQL types.
Which of the following is a primary advantage of using a database instead of a flat file system?
Databases handle concurrent access and maintain data integrity through ACID properties.
Why this answer
Databases provide structured storage, multi-user access, query capabilities, and data integrity, whereas flat files lack concurrent access and query languages.
Which TWO of the following are benefits of using a cloud database service (DBaaS) over an on-premises database?
The cloud provider handles hardware maintenance.
Why this answer
DBaaS eliminates the need for hardware management and allows easy scaling of resources on demand.
Which THREE of the following are advantages of using a cloud database service (DBaaS) over an on-premises database? (Select THREE.)
Correct. The cloud provider handles hardware.
Why this answer
DBaaS provides managed services, automatic scaling, and no hardware management, reducing operational overhead.
Which SQL statement is used to retrieve all columns from a table named 'Employees'?
Correct syntax for retrieving all columns.
Why this answer
The SELECT * statement retrieves all columns from the specified table.
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?
Correct INNER JOIN syntax with matching keys.
Why this answer
INNER JOIN returns only rows with matching foreign key values; Students.StudentID = Enrollments.StudentID is the join condition.
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?
Foreign keys maintain referential integrity between tables.
Why this answer
Foreign keys enforce referential integrity, ensuring that values in the foreign key column match a primary key value in the referenced table.
A database contains two tables: 'Authors' (AuthorID, Name) and 'Books' (BookID, Title, AuthorID). A query needs to return all authors and any books they have written, including authors with no books. Which type of JOIN should be used?
Correct; LEFT JOIN includes all authors.
Why this answer
A LEFT JOIN returns all rows from the left table (Authors) and matching rows from the right table (Books). If no match, NULLs are returned for the right table.
Which THREE of the following are types of data integrity enforced in relational databases?
Ensures foreign key values match a primary key or are null.
Why this answer
Entity integrity (via primary keys), referential integrity (via foreign keys), and domain integrity (via data types and constraints) are the three main types.
Which of the following is a valid reason to use a database instead of a spreadsheet?
Correct; databases offer query languages and integrity constraints.
Why this answer
Databases support complex queries using SQL and provide data integrity features like constraints.
A database has a 'Students' table and an 'Enrollments' table. Which type of relationship exists if a student can enroll in multiple courses and each course can have multiple students?
Correct. Students and courses have a many-to-many relationship, often implemented via a junction table.
Why this answer
A many-to-many relationship requires a junction table (like Enrollments) linking the two tables.
A cloud database service is being considered for a startup to avoid hardware maintenance and allow automatic scaling. Which type of service is this?
Correct. DBaaS offers managed database services with automatic scaling.
Why this answer
Database-as-a-Service (DBaaS) provides managed database instances in the cloud, handling maintenance, backups, and scaling.
A database designer wants to reduce data redundancy and avoid update anomalies. Which process should be applied?
Correct. Normalization reduces data redundancy and improves integrity.
Why this answer
Normalization is the process of organizing data to minimize redundancy and dependency, typically by dividing tables and establishing relationships.
A developer modifies a database and wants to ensure that every value in a column meets a specific condition, such as age must be between 0 and 120. Which type of integrity constraint should be used?
Correct. Domain integrity enforces valid data values for columns.
Why this answer
Domain integrity ensures that data falls within a valid range or set of values, often enforced with CHECK constraints.
A database designer wants to ensure that every value in a column called 'Status' is either 'Active', 'Inactive', or 'Pending'. Which type of constraint should be applied?
Correct; domain integrity limits the allowable values for a column.
Why this answer
Domain integrity is enforced by data type and check constraints. A CHECK constraint on the Status column can restrict values to the allowed list.
Which of the following are advantages of using a cloud database service (DBaaS) compared to an on-premises database? (Select TWO.)
Correct; the provider handles maintenance.
Why this answer
DBaaS provides managed maintenance and automatic scalability without hardware management. High availability is inherent, and initial cost is lower (pay-as-you-go).
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?
Entity integrity is enforced by the primary key constraint.
Why this answer
Entity integrity requires a primary key to be unique and not null for each row.
Which SQL command is used to add a new row to a table?
INSERT adds new records.
Why this answer
INSERT INTO is the SQL command for adding new rows.
A database designer wants to split a table into two to reduce data redundancy and avoid update anomalies. This process is known as:
Normalization splits tables to eliminate redundancy.
Why this answer
Normalization is the process of organizing data to reduce redundancy and improve integrity.
Which SQL statement would you use to retrieve the names of all products with a price greater than $50 from a table named 'Products'?
This correctly retrieves product names with price > 50.
Why this answer
The SELECT statement is used to query data; the WHERE clause filters rows. The correct syntax is SELECT column FROM table WHERE condition.
An employee wants to add a new customer record to the 'Customers' table with columns 'ID', 'Name', and 'Email'. Which SQL statement should be used?
Correct. This inserts a new row with specified values.
Why this answer
The INSERT statement adds new rows to a table.
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.)
Correct; normalization reduces anomalies when updating data.
Why this answer
Normalization aims to reduce redundancy, enforce data integrity, and eliminate update anomalies. It rarely improves query performance (often may reduce it due to more joins).
A university database includes tables: 'Professors' (ProfessorID, Name, Department) and 'Courses' (CourseID, Title, ProfessorID). Which THREE statements about this design are correct?
ProfessorID uniquely identifies each professor.
Why this answer
ProfessorID in Courses is a foreign key; many courses can have the same professor (one-to-many); the primary key of Professors is ProfessorID.
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?
A primary key ensures each row is uniquely identified.
Why this answer
A primary key uniquely identifies each row in a table; it must contain unique values and no NULLs.
Which of the following is an example of a NoSQL database?
Correct; MongoDB is NoSQL.
Why this answer
MongoDB is a popular document-oriented NoSQL database.
Which SQL statement is used to remove all rows from a table while keeping the table structure?
Correct. DELETE without WHERE removes all rows but keeps the table.
Why this answer
DELETE removes rows based on a condition or all rows if no WHERE clause is used, but TRUNCATE also removes all rows. However, DELETE is the standard DML command.
A database designer wants to reduce data redundancy in a relational database. Which THREE of the following are normalization techniques or concepts?
This is part of achieving 2NF.
Why this answer
Normalization involves splitting tables to reduce redundancy. First normal form (1NF) eliminates repeating groups, second normal form (2NF) removes partial dependencies, and third normal form (3NF) removes transitive dependencies.
Which of the following is a primary advantage of using a database over a flat file system for storing customer records?
Databases are designed for concurrent access with transaction controls.
Why this answer
Databases support concurrent multi-user access with locking mechanisms, while flat files cannot handle simultaneous writes safely.
Ready to test yourself?
Try a timed practice session using only Itf Database Fundamentals questions.