CCNA Data and Database Fundamentals Questions

This is correct because one patient row can be referenced by many appointment rows, while each appointment row references exactly one patient through a foreign key. That is the definition of a one-to-many relationship. The same pattern applies between Doctor and Appointment, so documenting it as one-to-many accurately captures the cardinality in both directions.

The Loans table typically includes foreign key columns such as BookID and MemberID that reference the primary keys of the Books and Members tables. These foreign keys enforce referential integrity, ensuring that a loan record cannot exist for a nonexistent book or member, which is a core principle of relational database design.

One driver appears on many shipment records, while each shipment record names only one driver. That is the definition of a one-to-many relationship, and it is implemented by placing the driver identifier in the shipment table as a foreign key referencing the driver's primary key.

In a relational table, each row is a tuple representing one record, while each column is an attribute representing a field. This row-record, column-field mapping is the defining structural characteristic, distinguishing tables from unstructured or non-relational stores.

This is correct because WHERE filters rows using both conditions joined by AND, and ORDER BY with DESC sorts the surviving rows from highest to lowest SaleAmount. The single quotes around West are required for a text comparison, and the numeric comparison needs no quotes. This combination satisfies every requirement in the scenario.

DELETE removes only rows satisfying the WHERE predicate, so OrderDate < '2023-01-01' purges pre-2023 orders while preserving the table, its schema and later records. This satisfies the stem's requirement to delete all orders placed before that date, unlike DROP or TRUNCATE, which would remove the table or every row.

Oracle Database is a proprietary relational DBMS, satisfying the stem's requirement for a commercial, closed-source system. Unlike open-source alternatives such as MySQL or PostgreSQL, its source code is owned by Oracle Corporation and licensed commercially, which is precisely the defining characteristic of a proprietary relational database system.

INSERT INTO appends a new row to the named table, with VALUES supplying the column data in table order. This satisfies the stem's requirement to add a row, whereas UPDATE modifies existing rows, SELECT retrieves them and CREATE TABLE defines structure.

One customer can place many orders, while each order belongs to exactly one customer, so CustomerID in Orders acts as a foreign key referencing the Customers primary key. This cardinality defines a one-to-many relationship, not one-to-one or many-to-many.

A PRIMARY KEY constraint enforces both uniqueness and non-null values. It ensures that each EmployeeID is unique across all rows and that no row can have a NULL EmployeeID. This directly satisfies both requirements: preventing duplicate employee identification numbers and disallowing empty values in that column.

A foreign key constraint enforces referential integrity by rejecting any insert or update whose foreign key value lacks a matching primary key in the referenced table. This directly satisfies the stem's requirement that every foreign key value correspond to an existing primary key, preventing orphaned rows.

The UNIQUE constraint prevents duplicate employee numbers, and the NOT NULL constraint ensures the column cannot be left empty. Together they satisfy both requirements: every employee has an employee number, and no two employees share the same one. This is a standard way to enforce a candidate key when the column is not the table's primary key.

NoSQL stores such as document and key-value databases permit each record to carry its own fields, so schema changes need no migration or ALTER TABLE. This contrasts with the fixed, predefined columns of a relational database, satisfying the flexible schema characteristic.

Flat files lack a database engine to manage row-level locking and transaction isolation, so simultaneous writes from multiple users risk overwriting each other's changes. A relational database enforces concurrency control, which this scenario's multi-user access constraint requires.

Every scan record uses the same named fields, so the data fits neatly into columns and can be grouped and counted directly. Aggregating scans by gate for a given day is a straightforward grouped count over that consistent structure, which is exactly what structured data supports well.

NoSQL databases permit documents or records with differing fields, so each item can carry its own attributes without a fixed table definition. Relational databases enforce a predefined schema of columns and types, making this flexibility the defining contrast the question targets.

NoSQL databases store data without a fixed relational schema, so documents, key-value pairs or graphs can be written with varying fields. This schema flexibility suits unstructured or rapidly evolving data, unlike rigid SQL table structures requiring predefined columns.

MongoDB stores data as BSON documents within collections, where each document holds field-value pairs including arrays such as 'tags'. This schema-flexible structure is the defining characteristic of a document store, distinguishing it from key-value, column-family, and graph NoSQL categories.

AutoNumber fields, sometimes called automatic row IDs, generate a unique sequential or random value whenever a new record is created. They continue incrementing even if earlier rows are deleted, so the identifier never repeats. This directly meets the requirement for a unique ticket identifier that is created automatically for each new support ticket.

One-to-many fits because a single customer row can be referenced by many order rows, while each order holds exactly one foreign key back to its customer. This satisfies the stem's constraint that every order links to precisely one customer, with no order shared between customers.

SELECT retrieves rows, the asterisk returns all columns, FROM names the table, and WHERE filters rows by the Department column matching the string 'Sales'. This is syntactically valid SQL, unlike statements missing clauses or using non-existent keywords.

This is correct because it splits the repeated address data into one Customers table keyed by a unique identifier, and the Invoices table keeps only a foreign key reference. Each address is stored once, eliminating duplicated city and state text, which is exactly the normalization goal described. The relationship remains intact because the key links every invoice back to its single customer record.

Google Cloud SQL is a fully managed relational database service hosted on Google Cloud, so it satisfies the stem's cloud database requirement. The provider handles availability and scaling, meeting the high-availability and automatic-scaling constraint for the e-commerce application.

This statement selects the title column from the Inventory table and uses ORDER BY title to sort the result alphabetically in ascending order by default. It directly satisfies the requirement to list all titles in alphabetical order without retrieving unnecessary columns or applying filters that would exclude rows.

A database engine manages locking, transactions, and isolation so many users can read and write the same records simultaneously without overwriting each other. A flat file offers no such coordination, so concurrent edits corrupt or clobber data. This directly satisfies the stem's concurrent multi-user access benefit.

A primary key enforces uniqueness on one or more columns and rejects duplicate or null values, so every customer order row is uniquely identified. It satisfies the stated requirement for guaranteed unique identification, unlike other keys or indexes that permit duplicates or serve only performance purposes.

DBaaS is the managed cloud model where the provider handles backups, patching, and scaling, exactly matching the stem's described responsibilities. IaaS leaves patching and scaling to the customer, while SaaS delivers finished applications rather than a managed database engine.

MongoDB stores records as BSON documents, a binary JSON-like format, satisfying the stem's requirement for a document-oriented NoSQL database. Unlike key-value stores such as Redis or column-family databases such as Cassandra, it organises data into flexible, schema-less documents, making it the archetypal example of the document model.

Relational databases enforce structure through tables, where each row represents a record and each column a defined attribute, with relationships established via keys between tables. This tabular row-and-column model satisfies the stem's requirement for a defining relational characteristic, distinguishing it from non-relational stores such as key-value or document databases.

A many-to-many relationship cannot be stored in two tables, so a junction table holds foreign keys referencing both related tables, with each row representing one association. This resolves the cardinality that one-to-many and one-to-one schemas cannot represent directly.

A many-to-many relationship correctly describes patients who can have multiple allergies and allergies that can affect multiple patients. It is implemented by creating a junction table with foreign keys referencing the primary keys of both the patients and allergies tables, allowing any combination of patient and allergy to be recorded.

NoSQL document databases store data in flexible, JSON-like documents, allowing each user profile to have different fields without a fixed schema. This is ideal for varying attributes, such as some users having multiple phone numbers while others have none. The developer can easily add or remove fields per document, meeting the requirement for schema flexibility.

This statement correctly selects the two desired columns, specifies the table, and applies a filter on the Category column. It returns only the product names and prices for electronics, matching the user's requirement. The syntax is standard SQL and will work in any relational DBMS, making it the precise solution.

JOIN is the clause that merges rows from two tables by matching values in a related column, directly satisfying the stem's requirement to combine rows. WHERE filters rows and UNION stacks result sets vertically, so neither correlates columns across tables.

SELECT * retrieves every column, FROM Customers names the source table, and the WHERE clause filters rows where city equals 'London'. The asterisk wildcard expands to all columns, satisfying the requirement to return complete rows matching the specified city condition.

This is correct because key-value stores model data as simple key-to-value pairs and are built for horizontal scaling, distributing keys across nodes to handle very high read and write volumes. They favor availability and partition tolerance over complex joins. This directly matches the student's need for a scalable, high-throughput store.

A document store keeps each catalog item as a single record with its own set of named fields, so a camera record and a book record can coexist without forcing every item to share one rigid column layout. This directly matches the requirement that items carry different attributes and that the shape of records can evolve.

SELECT name, price FROM Products WHERE price > 50; projects only the two required columns and applies the greater-than filter on price, returning exactly the product names and prices requested. The WHERE clause enforces the price constraint.

MySQL is a widely deployed open-source relational database management system, storing data in tables with SQL querying and ACID transactions. It satisfies the stem's requirement for a popular RDBMS example, distinguishing it from NoSQL or non-relational alternatives.

Document stores hold semi-structured data as JSON-like documents, each with its own schema, and are a recognised NoSQL category alongside key-value, column-family and graph stores. This satisfies the stem's requirement for a NoSQL database type.

Second normal form requires that all non-key attributes be fully functionally dependent on the entire primary key. Here, ProductName and UnitPrice depend only on ProductID, which is part of the composite primary key (OrderID, ProductID). This partial dependency violates 2NF, and the table should be split to remove the dependency.

A database management system provides transaction control, locking and constraint enforcement, letting many users read and write simultaneously without corrupting data. A flat file offers no such concurrency control or integrity guarantees, making this the primary advantage.

With DBaaS the provider owns and maintains the servers, storage and patching, so the organisation no longer procures or racks physical hardware. This directly satisfies the stem's comparison against on-premises databases, where hardware management is the customer's responsibility.

DBaaS shifts physical host provisioning, patching and capacity to the provider, so the customer never racks, cables or replaces server hardware. This directly satisfies the stem's advantage criterion by removing hardware lifecycle management from the organisation's operational scope.

The asterisk wildcard in the SELECT clause returns every column defined in the Employees table, while FROM names the source table. This is the standard SQL shorthand for retrieving all columns without listing each one explicitly.

A foreign key constraint enforces referential integrity: every value in Enrollments' student key must match an existing Students primary key, or be null. This prevents orphaned enrolment rows and blocks deletion of a referenced student, directly satisfying the stem's requirement that the relationship enforce valid cross-table references.

A LEFT JOIN returns every row from the left Authors table plus matching Books rows, filling NULLs where no match exists. That preserves authors with no books, exactly the inclusive requirement stated in the stem.

Referential integrity constrains foreign key values to match existing primary keys in the referenced table, preventing orphaned rows. It is one of the three relational integrity types, alongside entity and domain integrity, enforced by the database engine rather than application code.

Databases support structured querying via SQL and enforce integrity through constraints such as primary keys, foreign keys and data types. Spreadsheets lack these guarantees, so they suit smaller, less critical datasets where concurrent access and validation are not required.

In a columnar database, data is stored by column rather than by row. Selecting only the needed columns allows the database to read just those columns, reducing I/O and improving performance. This is a best practice for columnar systems and directly supports the analyst's goal of retrieving only customer name and total order amount.

A foreign key constraint on the Order table references the primary key of the Customer table, ensuring every order's customer ID matches an existing customer. This enforces referential integrity, directly satisfying the requirement that orders cannot be linked to non-existent customers. It prevents orphaned records and maintains consistent relationships between the two tables.

Many-to-many applies because each student links to multiple courses and each course links to multiple students. Relational databases implement this with a junction table, here the Enrollments table, holding foreign keys to both Students and Courses.

An INNER JOIN returns only rows where the join condition matches in both tables. Customers without any matching order rows are omitted, which exactly matches the described result. This is the default join behavior when the keyword JOIN is used without a modifier, and it is appropriate when the analyst only wants customers who have placed orders.

Relational databases store data in structured tables composed of rows, which represent records, and columns, which represent attributes. This tabular structure is the foundation of the relational model and enables the use of keys, constraints, and joins. It directly characterizes an RDBMS and distinguishes it from systems that use other data models such as documents or graphs.

Database as a Service delivers a fully managed database engine, so the provider handles patching, backups and hardware, while compute and storage scale automatically on demand. This directly satisfies the startup's two constraints: eliminating hardware maintenance and gaining automatic scaling without managing servers.

In a relational database, a record, also called a row or tuple, represents one instance of the entity described by the table. For example, in a Customers table, one record contains all the columns for a single customer. This definition correctly describes how data is organized in a relational table.

Normalization restructures tables into progressively stricter normal forms, eliminating partial and transitive dependencies. This directly removes redundant data copies and the insert, update, and delete anomalies that stem from them, matching the designer's stated goal.

Domain integrity constrains a column's permitted values to a defined set, range or format, such as age between 0 and 120. It operates on the values themselves, unlike entity, referential or key constraints, which govern rows or relationships between tables.

Domain integrity constrains a column to a defined set of permissible values, so restricting Status to 'Active', 'Inactive', or 'Pending' is enforced at the domain level, typically via a CHECK constraint or lookup. Entity and referential integrity govern keys and relationships, not value ranges.

DBaaS shifts patching, version upgrades and backups to the provider, so the customer inherits automatic maintenance rather than scheduling downtime and applying updates themselves. This directly satisfies the stem's advantage over on-premises databases, where the internal team owns every maintenance window.

Entity integrity requires every row to have a unique, non-null primary key, so the OrderID column must contain a distinct value in each record. Referential integrity instead governs foreign-key relationships between tables, which the stem does not describe.

An AUTO_INCREMENT or IDENTITY column automatically generates a sequential unique number for each new row inserted, eliminating manual entry and preventing duplicates. This directly satisfies the requirement for automatic unique order numbers in a relational database, and it is supported by most relational database management systems as a standard column property.

INSERT INTO appends new rows to a table, satisfying the requirement to add data rather than modify structure or retrieve it. Unlike UPDATE, which alters existing rows, or CREATE, which defines a new table, INSERT INTO specifies the target table and supplies values for each column, directly fulfilling the scenario.

Referential integrity ensures that a foreign key value in one table matches an existing primary key value in the referenced table. By enforcing this constraint, the database rejects any order whose CustomerID is not present in the Customers table. This directly prevents orphaned records and maintains consistent relationships between orders and customers.

The same customer address is stored repeatedly across many rows, so the same fact is duplicated. When one copy is corrected, the other copies still hold the old value, producing inconsistent data. This duplication of the same fact in multiple places is exactly what redundancy means, and the mismatched addresses are the resulting inconsistency.

This is accurate because referential integrity requires every non-null foreign key value to correspond to an existing referenced key. The exception is when the foreign key column permits NULL, which represents the absence of a relationship, such as an unassigned order. This rule prevents orphaned rows that point to nonexistent parent records.

Normalization splits tables to eliminate redundancy.

SELECT retrieves only the Name column, satisfying the requirement to return product names alone, while WHERE filters rows using the Price > 50 predicate. This precisely matches the stem's constraints: table Products, names only, price threshold above $50.

INSERT INTO with VALUES adds a single row to the named table, supplying the three column values in the table's declared order (ID, Name, Email). This satisfies the requirement to add one new customer record, since no existing rows are modified or selected.

The questions asked are about how one record connects to others: appointments point to a patient and to a physician. A relational structure stores each entity in its own table and enforces those links, so a query can follow the connections and return consistent results even while records are being updated.

ProfessorID uniquely identifies each professor row, so it serves as the primary key of the Professors table. This uniqueness constraint is what allows other tables, such as Courses, to reference individual professors reliably through that identifier.

A PRIMARY KEY constraint enforces uniqueness and non-nullability on OrderID, guaranteeing each Orders row is uniquely identifiable. Unique constraints permit one null, and foreign keys reference other tables rather than identifying rows, so neither meets the stated requirement.

MongoDB stores documents in BSON rather than rows in relational tables, and it uses no fixed schema or SQL. That document-oriented, non-relational model is the defining characteristic of a NoSQL database, distinguishing it from relational systems such as MySQL, PostgreSQL and Oracle.

A database management system provides concurrency control, letting many users read and write customer records simultaneously without corrupting data. A flat file offers no such locking or transaction mechanism, so concurrent edits overwrite each other, making the database the appropriate choice.