D426 Practice Test 2 Flashcards
Which normal form ensures that all non-key attributes are fully functionally dependent on the entire primary key, and nothing but the key?
Example:
A table is in ______ if it has no partial or transitive dependencies, ensuring all attributes depend only on the primary key.
1 - 3NF
2 - 2NF
3 - 1NF
4 - Boyce-Codd NF
1 -✔ 3NF – Ensures all attributes depend only on the primary key and removes transitive dependencies.
2 - 2NF – Ensures that no non-prime attribute is dependent on only a part of a composite primary key.
3 - 1NF – Ensures the table contains only atomic values with a unique primary key.
4 - Boyce-Codd NF – A stricter version of 3NF that removes anomalies by ensuring no partial or transitive dependencies.
Which layer of MySQL architecture manages connections from multiple users?
Example:
The ______ layer in MySQL handles authentication, thread management, and user connections, ensuring multiple users can interact with the database simultaneously.
1 - Tools
2 - Storage engine
3 - File system
4 - Query processor
1 - Tools – Includes utilities for database management but does not handle user connections.
2 - Storage engine – Manages how data is stored and retrieved but not user connections.
3 - File system – Handles physical storage of database files but does not manage connections.
4 -✔ Query processor – Manages client connections, authentication, and query execution.
What does a unique attribute signify in ER modeling?
Example:
A ______ attribute in ER modeling ensures that each entity instance can be distinguished from all others within an entity set.
1 - An attribute that can have multiple values for each entity instance
2 - An attribute that is optional for each entity
3 - An attribute shared by all entities
4 - An attribute that identifies each entity instance uniquely
1 - An attribute that can have multiple values for each entity instance – Describes a multivalued attribute, not a unique one.
2 - An attribute that is optional for each entity – Refers to a nullable attribute, not necessarily unique.
3 - An attribute shared by all entities – Represents a common attribute but does not imply uniqueness.
4 -✔ An attribute that identifies each entity instance uniquely – Ensures that no two entities have the same value for this attribute.
Which of the following is NOT a characteristic of a database system?
Example:
A database system does not typically ______, as it is designed to organize and structure data efficiently.
1 - Allows for data retrieval and manipulation
2 - Stores data in an unstructured format
3 - Ensures data security and integrity
4 - Operates independently of a specific application
1 - Allows for data retrieval and manipulation – A fundamental feature of a database system.
2 -✔ Stores data in an unstructured format – Databases store data in a structured format using tables, schemas, or other defined models.
3 - Ensures data security and integrity – Database systems enforce security policies and data integrity constraints.
4 - Operates independently of a specific application – Many databases can be accessed by multiple applications rather than being tied to one.
What is the result of a SELECT statement?
Example:
A SELECT statement in SQL returns ______, representing the retrieved data from a database query.
1 - An integer
2 - A set of tuples
3 - A new table
4 - A single value
1 - An integer – A SELECT statement can return integers, but its result is typically a collection of records, not just a single integer.
2 -✔ A set of tuples – The output of a SELECT statement is a result set, which consists of multiple rows (tuples).
3 - A new table – A SELECT statement retrieves data but does not create a new table unless used with CREATE TABLE AS.
4 - A single value – While some queries (e.g., aggregate functions) return a single value, a SELECT statement generally produces multiple rows.
To convert a string to lower case in SQL, which function is used?
Example:
In SQL, the ______ function is used to transform all characters in a string to lowercase.
1 - LOWER()
2 - LOWERCASE()
3 - TOLOWER()
4 - CASELOWER()
1 -✔ LOWER() – The standard SQL function for converting a string to lowercase.
2 - LOWERCASE() – Not a valid SQL function for changing text to lowercase.
3 - TOLOWER() – Does not exist in standard SQL syntax.
4 - CASELOWER() – An incorrect function name; SQL does not use this format.
What is an associative entity?
Example:
An ______ entity is used in ER modeling to connect two databases, facilitating relationships between their data structures.
1 - A secondary entity in a database
2 - An entity that is used to represent a many-to-many relationship with attributes
3 - An entity that associates two databases
4 - An entity that associates attributes with primary keys
1 - A secondary entity in a database – Associative entities are not secondary; they serve a specific role in relationships.
2 - An entity that is used to represent a many-to-many relationship with attributes – While associative entities often help model many-to-many relationships, this is not their sole purpose.
3 -✔ An entity that associates two databases – Associative entities link data across databases, ensuring structured connections.
4 - An entity that associates attributes with primary keys – This describes a different concept, such as a composite key.
The storage engine in MySQL is also known as:
Example:
In MySQL, the ______ is responsible for managing how data is stored, retrieved, and maintained within the database.
1 - Data retriever
2 - Query manager
3 - Storage manager
4 - Connection controller
1 - Data retriever – While it plays a role in retrieving data, its main function is managing storage.
2 - Query manager – Queries are handled by the MySQL query processor, not the storage engine.
3 -✔ Storage manager – The storage engine handles how data is stored and retrieved, making it the correct answer.
4 - Connection controller – Manages user connections but is not related to data storage.
The LIKE operator is used to:
Example:
In SQL, the ______ operator is used to find records where a column’s value matches a specific pattern.
1 - Convert data types
2 - Search for a specified pattern in a column
3 - Create a new table
4 - Compare two values for equality
1 - Convert data types – The CAST or CONVERT function is used for data type conversion, not LIKE.
2 -✔ Search for a specified pattern in a column – The LIKE operator is used with wildcard characters to match patterns in text data.
3 - Create a new table – The CREATE TABLE statement is used to create tables, not LIKE.
4 - Compare two values for equality – The “=” operator is used for direct comparisons, while LIKE is used for pattern matching.
MySQL architecture is organized into how many layers?
Example:
The MySQL architecture consists of ______ layers, including components for query processing, storage management, and connection handling.
1 - Eight
2 - Six
3 - Four
4 - Two
1 - Eight – MySQL architecture does not have eight distinct layers.
2 - Six – MySQL is generally structured into fewer layers than six.
3 -✔ Four – MySQL architecture is commonly divided into four main layers: the client layer, query processor, storage engine, and file system.
4 - Two – MySQL has more than two layers, as it involves multiple components for processing and storage.
Which of the following is a valid SQL data type?
Example:
In SQL, the ______ data type is used to store variable-length character strings.
1 - LINKED-LIST
2 - VARCHAR
3 - DOCUMENT
4 - GRAPH
1 - LINKED-LIST – Not a valid SQL data type; SQL does not use linked lists for storage.
2 -✔ VARCHAR – A valid SQL data type used to store variable-length character strings.
3 - DOCUMENT – Not a standard SQL data type; some NoSQL databases use document-based storage.
4 - GRAPH – Not a valid SQL data type; graphs are used in specialized graph databases.
Which of the following is NOT a characteristic of column-oriented storage?
Example:
Column-oriented storage is designed for analytical workloads, but it does not typically ______, as row-based storage is better suited for that purpose.
1 - Performs best for transactional applications
2 - Stores each block with values for a single column only
3 - Allows for better data compression
4 - Optimized for analytic applications
1 -✔ Performs best for transactional applications – Column-oriented storage is optimized for analytical queries, while row-based storage is better for transactional workloads.
2 - Stores each block with values for a single column only – This is a defining characteristic of columnar storage.
3 - Allows for better data compression – Storing similar data together enables efficient compression.
4 - Optimized for analytic applications – Columnar storage is specifically designed for analytical workloads and large-scale data aggregation.
To combine two or more columns or strings into a single string in SQL, you would use:
Example:
In SQL, the ______ function is used to concatenate multiple strings or column values into a single string.
1 - MERGE()
2 - APPEND()
3 - CONCAT()
4 - ADDSTR()
1 - MERGE() – Not a valid SQL function for string concatenation.
2 - APPEND() – SQL does not use this function for combining strings.
3 -✔ CONCAT() – The correct function for concatenating multiple strings or column values into one.
4 - ADDSTR() – Not an existing SQL function for string operations.
What describes the ‘heap table’ structure’s approach to managing free space after row deletion?
Example:
In a heap table, free space from deleted rows is ______ to allow future inserts without reorganizing the table.
1 - Compacts the table to eliminate free space
2 - Tracks free space as a linked list for future inserts
3 - Marks the space as permanently unusable
4 - Immediately reallocates the space for new data
1 - Compacts the table to eliminate free space – Heap tables do not automatically compact after deletions.
2 -✔ Tracks free space as a linked list for future inserts – Deleted row space is tracked and reused for new inserts.
3 - Marks the space as permanently unusable – Space is not wasted; it is reused for future data.
4 - Immediately reallocates the space for new data – The space is tracked but not instantly reassigned.
What does the cardinality of a relationship indicate?
Example:
In database design, the ______ of a relationship defines the number of instances of one entity that can be associated with instances of another entity.
1 - The unique attributes of the entities
2 - The strength of the relationship between entities
3 - The maximum and minimum number of entity instances that can be involved in a relationship
4 - The physical storage size of the entities
1 - The unique attributes of the entities – Attributes define entity characteristics, not relationship cardinality.
2 - The strength of the relationship between entities – Strength refers to dependency, while cardinality specifies the number of relationships.
3 -✔ The maximum and minimum number of entity instances that can be involved in a relationship – Cardinality determines the constraints on how many entities participate in a relationship.
4 - The physical storage size of the entities – Storage size is unrelated to relationship cardinality.
What is a primary feature of relational databases?
Example:
Relational databases organize data into ______, which consist of rows and columns to structure relationships between data.
1 - Key-value pairs
2 - Documents
3 - Graphs
4 - Tables
1 - Key-value pairs – Common in NoSQL databases, but not a primary feature of relational databases.
2 - Documents – Used in document-based databases like MongoDB, not relational databases.
3 - Graphs – Found in graph databases, which are designed for complex relationships.
4 -✔ Tables – The core structure of relational databases, where data is organized into rows and columns.
Which component is responsible for executing instructions from the query processor?
Example:
In a database system, the ______ is responsible for carrying out the execution of queries after they are processed and optimized.
1 - Connection manager
2 - Query optimizer
3 - Storage engine
4 - File system
1 - Connection manager – Manages client connections but does not execute queries.
2 - Query optimizer – Optimizes queries but does not execute them.
3 -✔ Storage engine – Executes instructions by retrieving and manipulating data in storage.
4 - File system – Manages physical storage but does not execute database queries.
MySQL Workbench is designed for:
Example:
______ can use MySQL Workbench for database design, administration, and query execution.
1 - External third-party integration
2 - Both database administrators and users
3 - Cloud management only
4 - System developers exclusively
1 - External third-party integration – While MySQL Workbench supports some integrations, this is not its primary purpose.
2 -✔ Both database administrators and users – MySQL Workbench is designed for database design, administration, and query execution, making it useful for both administrators and users.
3 - Cloud management only – MySQL Workbench is not limited to cloud management; it supports local and remote databases.
4 - System developers exclusively – MySQL Workbench is used by a broader audience, including database administrators and analysts.
What is the purpose of the ON clause in SQL joins?
Example:
In an SQL join, the ______ clause defines the condition that determines how rows from the joined tables are matched.
1 - Specifies the columns to be selected
2 - Specifies the two tables to be joined
3 - Specifies the order of the result set
4 - Specifies the condition for the join between the two tables
1 - Specifies the columns to be selected – The SELECT statement determines which columns appear in the result, not the ON clause.
2 - Specifies the two tables to be joined – The FROM clause or JOIN keyword specifies the tables, while ON defines the matching condition.
3 - Specifies the order of the result set – ORDER BY controls sorting, not the ON clause.
4 -✔ Specifies the condition for the join between the two tables – The ON clause determines how rows from the joined tables are matched based on a specified condition.
Which statement about NoSQL databases is true?
Example:
______ databases are designed to handle large-scale, unstructured, or semi-structured data efficiently.
1 - They are primarily used for relational data modeling
2 - They do not support any query language
3 - They only use SQL for data manipulation
4 - They are optimized for big data
1 - They are primarily used for relational data modeling – NoSQL databases are designed for flexible, schema-less data storage rather than traditional relational models.
2 - They do not support any query language – Many NoSQL databases have query languages, such as MongoDB’s query syntax.
3 - They only use SQL for data manipulation – NoSQL databases use different query methods, not just SQL.
4 -✔ They are optimized for big data – NoSQL databases are built for scalability and handling large volumes of data efficiently.
When a non-key column cannot depend on part of a composite primary key, we know that table is in:
Example:
A table is in ______ if it has no partial dependencies, meaning all non-key attributes depend on the entire primary key.
1 - 1NF
2 - 3NF
3 - 2NF
4 - Boyce-Codd NF
1 - 1NF – Ensures atomicity but does not address partial dependencies.
2 - 3NF – Removes transitive dependencies but requires 2NF first.
3 -✔ 2NF – Ensures that non-key attributes depend on the whole primary key, eliminating partial dependencies.
4 - Boyce-Codd NF – A stricter form of 3NF but requires 2NF first.
Which term describes the process of denormalization?
Example:
______ involves combining tables or introducing redundancy to improve query performance at the cost of normalization.
1 - Composite Key
2 - 3rd Normal Form
3 - Merging Tables
4 - Trivial Dependency
1 - Composite Key – A key consisting of multiple columns but not related to denormalization.
2 - 3rd Normal Form – A normalization stage that reduces redundancy, opposite of denormalization.
3 -✔ Merging Tables – Denormalization often involves merging tables to reduce joins and improve performance.
4 - Trivial Dependency – A dependency where an attribute is functionally dependent on itself, unrelated to denormalization.
Which of the following modifications will bring the Order table to the next normal form?
Example:
To eliminate partial dependencies and improve normalization, ______ should be used to separate repeating groups into different tables.
1 - Split the Order table into two tables: TABLE A: OrderID, CustomerID, OrderDate, and TABLE B: ProductID, ProductName, and Quantity.
2 - Split the Order table into two tables: TABLE A: OrderID, CustomerID, OrderDate, and TABLE B: ProductID and ProductName.
3 - Split the Order table into two tables: TABLE A: OrderID, CustomerID, OrderDate, and TABLE B: ProductID, OrderID, ProductName, Quantity.
4 - Split the Order table into two tables: TABLE A: OrderID, CustomerID, OrderDate, Quantity, TABLE B: CustomerID and CustomerName ProductID.
1 - This does not preserve the relationship between orders and products.
2 - ProductID and ProductName should be linked to specific orders.
3 -✔ This structure removes partial dependencies, ensuring that products are associated with orders correctly.
4 - The structure of TABLE B is incorrect, and it does not properly normalize the table.
A column in Table B has been indexed. Which action would cause the index to be updated?
Example:
A column in Table B has been indexed. The index must be updated whenever ______ occurs in the indexed column to maintain accuracy.
1 - A different table is joined with Table B
2 - A different column is updated in Table B
3 - A row is deleted from Table B
4 - A new column is added to Table B
1 - A different table is joined with Table B – Joining a table does not modify the indexed data.
2 - A different column is updated in Table B – Updating a different column does not affect the indexed column.
3 -✔ A row is deleted from Table B – Deleting a row removes indexed data, requiring the index to be updated.
4 - A new column is added to Table B – Adding a new column does not impact existing indexes.
Which relationship involves a salesperson and products they sell?
Example:
A relationship between a salesperson and the products they sell is classified as a ______ relationship.
1 - Unary
2 - Quaternary
3 - Ternary
4 - Binary
1 - Unary – A relationship where an entity is related to itself, which does not apply here.
2 - Quaternary – A relationship involving four entities, which is more complex than needed.
3 - Ternary – A relationship involving three entities, which is not required for this scenario.
4 -✔ Binary – A relationship between two entities, such as salesperson and product.
What is intersection data in an ER Diagram?
Example:
______ represents information that describes the relationship between two entities in an ER diagram.
1 - Data displayed when you JOIN two tables
2 - Data that describes the relationship between two entities
3 - Data that intersects two databases
4 - A type of primary key
1 - Data displayed when you JOIN two tables – JOIN operations retrieve related data but do not define relationships in an ER diagram.
2 -✔ Data that describes the relationship between two entities – Intersection data represents attributes of a many-to-many relationship.
3 - Data that intersects two databases – ER diagrams typically represent relationships within a single database, not across databases.
4 - A type of primary key – Intersection data may contain a composite key but is not itself a primary key type.
Consider the following database table Order with the following columns:
OrderID (Primary Key)
CustomerID
CustomerName
OrderDate
ProductID
ProductName
Quantity
Which of the following normal forms does the Order table currently satisfy IF:
1) Each OrderID has a unique value (i.e. is a primary key)
2) Each entry in the table is atomic (no repeating groups or arrays).
1 - 3rd Normal Form
2 - Boyce-Codd Normal Form
3 - 2nd Normal Form
4 - First Normal Form
1 - 3rd Normal Form – Requires 2NF and that all non-key attributes depend only on the primary key, which is not confirmed.
2 - Boyce-Codd Normal Form – A stricter version of 3NF, requiring no partial or transitive dependencies, which is not verified.
3 - 2nd Normal Form – Ensures 1NF and that all non-key attributes fully depend on the primary key, but partial dependencies may still exist.
4 -✔ First Normal Form – The table meets 1NF because it has atomic values and a unique primary key.
Who introduced the relational model?
1 - Charles Babbage
2 - Alan Turing
3 - Tim Berners-Lee
4 - E.F. Codd
1 - Charles Babbage – Known as the “father of the computer,” but did not develop the relational model.
2 - Alan Turing – A pioneer in computing and artificial intelligence, but not related to the relational model.
3 - Tim Berners-Lee – Inventor of the World Wide Web, unrelated to relational databases.
4 -✔ E.F. Codd – Introduced the relational model in 1970, revolutionizing database management.
What does modality represent in an ER Diagram?
1 - The minimum number of entity occurrences that can be involved in a relationship
2 - The modal value of an attribute
3 - The type of relationship between entities
4 - The mode of data storag
1 -✔ The minimum number of entity occurrences that can be involved in a relationship – Modality specifies the minimum number of times an entity must participate in a relationship.
2 - The modal value of an attribute – “Modal value” refers to the most frequently occurring value in a dataset, not related to ER diagrams.
3 - The type of relationship between entities – This describes the cardinality (one-to-one, one-to-many, etc.), not modality.
4 - The mode of data storage – ER diagrams do not define storage modes; they represent relationships between entities.
Which constraint ensures uniqueness in a table’s column?
Example:
______ is used to enforce uniqueness in a column and serves as the main identifier for records in a table.
1 - FOREIGN KEY
2 - PRIMARY KEY
3 - UNIQUE KEY
4 - SECONDARY KEY
1 - FOREIGN KEY – A constraint that maintains referential integrity by linking to a primary key in another table but does not ensure uniqueness within its own table.
2 -✔ PRIMARY KEY – Ensures that each row in a table has a unique and non-null value in the specified column.
3 - UNIQUE KEY – Enforces uniqueness in a column but allows NULL values, unlike a primary key.
4 - SECONDARY KEY – Not a standard SQL constraint; often refers to an additional key used for indexing.
Which characteristic is unique to First Normal Form (1NF)?
Example:
A table in ______ must have atomic values, meaning each column contains only indivisible values.
1 - 1NF
2 - 2NF
3 - 3NF
4 - BCNF
1 -✔ 1NF – Ensures that all attributes contain only atomic values and there are no repeating groups.
2 - 2NF – Requires 1NF and eliminates partial dependencies but does not specifically enforce atomicity.
3 - 3NF – Ensures transitive dependencies are removed but does not directly address atomicity.
4 - BCNF – A stricter form of 3NF that resolves additional anomalies beyond atomicity.
Which characteristic is unique to Second Normal Form (2NF)?
Example:
A table in ______ must eliminate partial dependencies, meaning all non-key attributes must depend on the entire primary key.
1 - 1NF
2 - 2NF
3 - 3NF
4 - BCNF
1 - 1NF – Ensures atomicity but does not eliminate partial dependencies.
2 -✔ 2NF – Requires 1NF and removes partial dependencies by ensuring all non-key attributes fully depend on the entire primary key.
3 - 3NF – Removes transitive dependencies but does not specifically address partial dependencies.
4 - BCNF – Strengthens 3NF but does not focus on partial dependencies.
Which characteristic is unique to Third Normal Form (3NF)?
Example:
A table in ______ must remove transitive dependencies, meaning non-key attributes cannot depend on other non-key attributes.
1 - 1NF
2 - 2NF
3 - 3NF
4 - BCNF
1 - 1NF – Ensures atomicity but does not address transitive dependencies.
2 - 2NF – Eliminates partial dependencies but does not guarantee the removal of transitive dependencies.
3 -✔ 3NF – Requires 2NF and ensures that every non-key attribute depends only on the primary key, removing transitive dependencies.
4 - BCNF – A stricter form of 3NF but does not define transitive dependencies explicitly.
Which characteristic is unique to Boyce-Codd Normal Form (BCNF)?
Example:
A table in ______ must ensure that every determinant is a candidate key, eliminating anomalies that may exist in 3NF.
1 - 1NF
2 - 2NF
3 - 3NF
4 - BCNF
1 - 1NF – Ensures atomicity but does not address determinants.
2 - 2NF – Removes partial dependencies but does not enforce determinant constraints.
3 - 3NF – Eliminates transitive dependencies but may still allow some functional dependencies that BCNF resolves.
4 -✔ BCNF – Requires 3NF and ensures that all determinants are candidate keys, eliminating certain anomalies that can still exist in 3NF.
Data is/are ______.
Example: A database stores ______ that can be processed into meaningful information.
1 - Information
2 - Raw facts
3 - Processed information
4 - A DBMS
1 - Information – Data becomes information when processed and given meaning.
2 -✔ Raw facts – Data consists of unprocessed raw facts that require organization to become meaningful.
3 - Processed information – Processed data is considered information, not raw data.
4 - A DBMS – A Database Management System (DBMS) is a software tool used to store and manage data, not data itself.
A Database Model is ______.
Example: The structure of a database is defined by a ______ that determines how data is organized and related.
1 - A conceptual framework for database systems
2 - A leading relational database system sponsored by Oracle
3 - Suitable for non-commercial applications such as education
4 - A specification of database objects such as tables
1 -✔ A conceptual framework for database systems – A database model defines how data is structured, stored, and manipulated.
2 - A leading relational database system sponsored by Oracle – This describes Oracle Database, not a database model.
3 - Suitable for non-commercial applications such as education – Database models are not limited to specific application types.
4 - A specification of database objects such as tables – While database models define structure, they go beyond just listing objects.
What does the ‘refer’ in referential integrity mean?
Example: In a relational database, a foreign key must ______ a valid primary key in another table to maintain referential integrity.
1 - The relationships between entities and attributes, also called referrals.
2 - Reference points that databases place in each record during backups.
3 - A foreign key in a table must refer to a valid primary key in another table.
4 - You delete a row in one table whose primary key does not have a matching foreign key value in another table.
1 - The relationships between entities and attributes, also called referrals. – This describes general entity relationships, not referential integrity.
2 - Reference points that databases place in each record during backups. – Databases do not place reference points in records for backups.
3 -✔ A foreign key in a table must refer to a valid primary key in another table. – Referential integrity ensures that foreign keys always point to valid primary keys.
4 - You delete a row in one table whose primary key does not have a matching foreign key value in another table. – Referential integrity prevents this situation from occurring.
Another word for the term “relation” is ______.
Example: In a relational database, a ______ is another term for a relation, representing structured data stored in rows and columns.
1 - Datafile
2 - Data index
3 - Table Name
4 - Data query
1 - Datafile – A datafile stores raw data but is not equivalent to a relation in database terminology.
2 - Data index – An index helps speed up queries but does not define a relation.
3 -✔ Table Name – In relational databases, a relation is commonly referred to as a table.
4 - Data query – A query retrieves data but is not itself a relation.
A table can be logically connected to another table by defining a ______.
Example: In a relational database, tables are linked through a ______ that establishes relationships between their records.
1 - Hyperlink
2 - Common attribute
3 - Primary key
4 - Logic key
1 - Hyperlink – A hyperlink connects web pages, not database tables.
2 -✔ Common attribute – A common attribute, such as a foreign key, is used to establish logical connections between tables.
3 - Primary key – A primary key uniquely identifies records but does not itself establish connections between tables.
4 - Logic key – “Logic key” is not a standard database term for connecting tables.
A relational operator that allows for the combination of information from two or more tables is known as the ______ operator.
Example: In relational algebra, the ______ operator merges data from multiple tables based on a related column.
1 - SELECT
2 - PROJECT
3 - JOIN
4 - DIFFERENCE
1 - SELECT – Retrieves specific rows from a table but does not combine tables.
2 - PROJECT – Retrieves specific columns from a table but does not merge tables.
3 -✔ JOIN – Combines information from two or more tables based on a related column.
4 - DIFFERENCE – Returns rows from one table that are not found in another but does not merge tables.
Which of the following statements concerning the primary key is true?
Example: A primary key must contain ______ values to ensure each record in a table is uniquely identifiable.
1 - All primary key entries are unique.
2 - The primary key may be null.
3 - The primary key is not required for all tables.
4 - The primary key data do not have to be unique.
1 -✔ All primary key entries are unique. – A primary key uniquely identifies each record in a table, ensuring no duplicate values.
2 - The primary key may be null. – A primary key cannot be null, as it must always contain a unique identifier.
3 - The primary key is not required for all tables. – Every table in a well-designed relational database should have a primary key.
4 - The primary key data do not have to be unique. – Primary key values must always be unique to maintain data integrity.
We can describe a link by observing that ______.
Example: In a relational database, a ______ establishes a connection between two tables by referencing a unique identifier.
1 - A primary key of one table appears again as a primary key in a related table.
2 - A foreign key of one table appears again as a foreign key in a related table.
3 - A primary key of one table appears again as a foreign key in a related table.
4 - A foreign key of one table appears again as a surrogate key in a related table.
1 - A primary key of one table appears again as a primary key in a related table. – Each table has its own primary key; they do not repeat as primary keys in other tables.
2 - A foreign key of one table appears again as a foreign key in a related table. – Foreign keys reference primary keys but do not necessarily appear as foreign keys in multiple tables.
3 -✔ A primary key of one table appears again as a foreign key in a related table. – This is the correct definition of a relationship between tables in a relational database.
4 - A foreign key of one table appears again as a surrogate key in a related table. – Surrogate keys are artificially generated and do not necessarily correspond to foreign keys.
An attribute (or combination of attributes) that uniquely identifies each entity in a table is called a ______.
Example: In a relational database, a ______ is used to ensure that each row in a table can be uniquely identified.
1 - Superkey
2 - Foreign key
3 - Master key
4 - Secondary key
1 -✔ Superkey – A superkey is any attribute or set of attributes that uniquely identifies a record in a table.
2 - Foreign key – A foreign key references a primary key in another table but does not uniquely identify records within its own table.
3 - Master key – This is not a standard database term for uniquely identifying records.
4 - Secondary key – A secondary key is used for indexing and searching but does not uniquely identify records.
A foreign key must ______.
Example: In a relational database, a foreign key ensures referential integrity by requiring that its values ______ a primary key in another table.
1 - Be numeric
2 - Be unique
3 - Be defined in all tables within the database
4 - Match the value of a primary key in a related table
1 - Be numeric – A foreign key can be of any data type, as long as it matches the primary key it references.
2 - Be unique – A foreign key does not have to be unique; multiple records can reference the same primary key.
3 - Be defined in all tables within the database – Not all tables require a foreign key; only those involved in relationships do.
4 -✔ Match the value of a primary key in a related table – A foreign key must correspond to a valid primary key in another table to maintain referential integrity.
The Entity Relationship Diagram (ERD) is used to graphically represent the ______ database model.
Example: An ERD illustrates entities, relationships, and attributes in a database, making it a key tool for designing the ______ model.
1 - Condensed
2 - Physical
3 - Logical
4 - Conceptual
1 - Condensed – This is not a recognized database model.
2 - Physical – The physical model describes how data is stored on disk rather than entity relationships.
3 - Logical – The logical model deals with tables, attributes, and relationships but is not specifically represented by ERDs.
4 -✔ Conceptual – ERDs are primarily used to model the conceptual design of a database, focusing on entities and their relationships before implementation.
A relationship is an association between ______.
Example: In an ERD, a ______ relationship defines how different entities interact with each other within a database.
1 - Objects
2 - Entities
3 - Databases
4 - Fields
1 - Objects – Objects are a concept in object-oriented programming, not a primary element of relational databases.
2 -✔ Entities – A relationship in a database links two or more entities, defining how they are associated.
3 - Databases – Relationships exist within a database but do not associate databases themselves.
4 - Fields – Fields (or attributes) belong to entities, but relationships connect entire entities rather than individual fields.
A ______ key is a key that consists of more than one attribute.
Example: When a key is formed by combining multiple attributes to uniquely identify a record, it is called a ______ key.
1 - Primary
2 - Foreign
3 - Composite
4 - Domain
1 - Primary – A primary key uniquely identifies a record, but it does not have to consist of multiple attributes.
2 - Foreign – A foreign key establishes a relationship between tables but is not necessarily composed of multiple attributes.
3 -✔ Composite – A composite key is made up of two or more attributes that together uniquely identify a record.
4 - Domain – A domain defines the allowed values for an attribute but is unrelated to key composition.
If an entity can exist apart from one or more related entities, it is said to be ______-independent.
Example: In database design, an entity that does not rely on another entity for its existence is considered ______-independent.
1 - Existence
2 - Relationship
3 - Business
4 - Weak
1 -✔ Existence – An entity is existence-independent if it does not depend on another entity to exist.
2 - Relationship – Relationship-independence is not a recognized term in database design.
3 - Business – Business-independence refers to organizational concepts, not entity relationships.
4 - Weak – A weak entity depends on another entity and is not considered independent.
The set of possible values for an attribute is a ______.
Example: In a database, the predefined collection of values that an attribute can take is called its ______.
1 - Domain
2 - Range
3 - Set
4 - Key
1 -✔ Domain – A domain defines the permissible values for an attribute.
2 - Range – A range represents a subset of values but does not define all possible values for an attribute.
3 - Set – A set is a general mathematical concept and is not the specific term used in database design.
4 - Key – A key uniquely identifies records but does not define attribute values.
Which attribute(s) make up the primary key in the table definition: CLASS (CRS_CODE, CLASS_SECTION, CLASS_TIME, CLASS_ROOM, PROF_NUM)
Example: In the CLASS table, the combination of attributes that uniquely identifies each record is ______.
1 - CRS_CODE
2 - CLASS_SECTION
3 - CRS_CODE and CLASS_SECTION
4 - There is no primary key
1 - CRS_CODE – CRS_CODE alone does not uniquely identify a class section.
2 - CLASS_SECTION – CLASS_SECTION alone is not sufficient to distinguish different courses.
3 -✔ CRS_CODE and CLASS_SECTION – The combination of CRS_CODE and CLASS_SECTION ensures uniqueness for each class offering.
4 - There is no primary key – A primary key exists, and it is a composite key in this case.
A table that has all key attributes defined, has no repeating groups, and all its attributes are dependent on the primary key, is said to be in ____.
Example: A table that meets the basic requirements of a relational database, where all columns contain atomic values and there are no duplicate rows, is in ______.
1 - 1NF
2 - 2NF
3 - 3NF
4 - 4NF
1 -✔ 1NF – First Normal Form (1NF) ensures that the table has a defined primary key, no repeating groups, and atomic values in each column.
2 - 2NF – 2NF requires 1NF and that all non-key attributes depend entirely on the primary key, but 1NF comes first.
3 - 3NF – 3NF requires 2NF and removes transitive dependencies, but the table is in 1NF before reaching this stage.
4 - 4NF – 4NF requires 3NF and eliminates multi-valued dependencies, but 1NF is the foundational step.
A table that is in 2NF and contains no transitive dependencies is said to be in ____.
Example: If a table is in Second Normal Form (2NF) and has no attributes that depend on non-key attributes, it is in ______.
1 - 1NF
2 - 2NF
3 - 3NF
4 - 4NF
1 - 1NF – 1NF ensures no repeating groups and atomicity, but it does not address transitive dependencies.
2 - 2NF – 2NF removes partial dependencies but does not eliminate transitive dependencies.
3 -✔ 3NF – Third Normal Form (3NF) ensures that all attributes depend only on the primary key and removes transitive dependencies.
4 - 4NF – 4NF deals with multi-valued dependencies but requires the table to first be in 3NF.
Data redundancy produces ____.
Example: Storing the same data in multiple places within a database often leads to ______.
1 - Slower lookups
2 - Robust design
3 - Efficient storage use
4 - Data integrity problems
1 - Slower lookups – While redundancy can sometimes slow down lookups, the main issue is inconsistency in data.
2 - Robust design – A robust database design minimizes redundancy rather than encouraging it.
3 - Efficient storage use – Redundant data increases storage use rather than making it more efficient.
4 -✔ Data integrity problems – Redundant data can lead to inconsistencies and anomalies when updating, inserting, or deleting records.
To list all the contents of the PRODUCT table, you would use ____.
Example: To retrieve every row and column from the PRODUCT table, the correct SQL statement is _______.
1 - LIST * FROM PRODUCT;
2 - SELECT * FROM PRODUCT;
3 - DISPLAY * FROM PRODUCT;
4 - SELECT ALL FROM PRODUCT;
1 - LIST * FROM PRODUCT; – This is not a valid SQL command for retrieving data from a table.
2 -✔ SELECT * FROM PRODUCT; – The SELECT * FROM PRODUCT; statement correctly retrieves all columns and rows from the PRODUCT table.
3 - DISPLAY * FROM PRODUCT; – SQL does not use DISPLAY as a command for querying data.
4 - SELECT ALL FROM PRODUCT; – The correct syntax is SELECT * FROM PRODUCT;, not SELECT ALL.
To find all rows where inventory stock dates occur on or after January 20, 2006, the correct SQL query is _______.
1 - SELECT P_DESCRIPT, P_QOH, P_MIN, P_PRICE, P_INDATE FROM PRODUCT WHERE P_INDICATE >= ‘2006-01-20’
2 - SELECT P_DESCRIPT, P_QOH, P_MIN, P_PRICE, P_INDATE FROM PRODUCT WHERE P_INDICATE >= #01/20/2004#
3 - SELECT P_DESCRIPT, P_QOH, P_MIN, P_PRICE, P_INDATE FROM PRODUCT WHERE P_INDICATE >= ‘20-JAN-2004’
4 - SELECT P_DESCRIPT, P_QOH, P_MIN, P_PRICE, P_INDATE FROM PRODUCT WHERE P_INDICATE >= {01-20-2004}
1 -✔ SELECT P_DESCRIPT, P_QOH, P_MIN, P_PRICE, P_INDATE FROM PRODUCT WHERE P_INDICATE >= ‘2006-01-20’ This correctly compares dates using the standard SQL format ‘YYYY-MM-DD’.
2 - SELECT P_DESCRIPT, P_QOH, P_MIN, P_PRICE, P_INDATE FROM PRODUCT WHERE P_INDICATE >= #01/20/2004# The # date format is used in Microsoft Access, not standard SQL.
3 - SELECT P_DESCRIPT, P_QOH, P_MIN, P_PRICE, P_INDATE FROM PRODUCT WHERE P_INDICATE >= ‘20-JAN-2004’ While some databases support this format, it is not universally standard.
4 - SELECT P_DESCRIPT, P_QOH, P_MIN, P_PRICE, P_INDATE FROM PRODUCT WHERE P_INDICATE >= {01-20-2004} The {} brackets are not a valid SQL syntax for date comparison.
During which step in the extract, transform, load (ETL) process are raw data sets aggregated? _______
Example: In the ETL process, the step where raw data sets are combined and aggregated before being loaded into the target system is _______.
1 - Transformation
2 - Extraction
3 - Loading
4 - Denormalization
1 -✔ Transformation Raw data sets are aggregated during the transformation step, where data is cleaned, formatted, and structured before loading.
2 - Extraction This step retrieves data from source systems but does not perform aggregation.
3 - Loading This step inserts the transformed data into the target system, but aggregation happens earlier.
4 - Denormalization This refers to restructuring a database for performance optimization, not aggregating raw data in ETL.
Which statement will remove all rows from the Materials table that have a Status value of ‘Obsolete’ but do not have a value for the VendorID column? _______
Example: To delete rows from the Materials table where the Status is ‘Obsolete’ and the VendorID column is NULL, the correct SQL statement is _______.
1 - DELETE Materials WHERE Status = ‘Obsolete’ OR VendorID IS NULL
2 - DELETE FROM Materials WHERE Status = ‘Obsolete’ AND VendorID IS NULL
3 - DELETE MaterialID, Description, Status, VendorID FROM Materials WHERE Status = ‘Obsolete’ AND VendorID IS NULL
4 - DELETE FROM Materials WHERE Status = ‘Obsolete’ WHERE VendorID IS NULL
1 - DELETE Materials WHERE Status = ‘Obsolete’ OR VendorID IS NULL This statement removes rows where either condition is met, but we need both conditions to be true.
2 -✔ DELETE FROM Materials WHERE Status = ‘Obsolete’ AND VendorID IS NULL This statement correctly deletes only rows where Status is ‘Obsolete’ and VendorID is NULL.
3 - DELETE MaterialID, Description, Status, VendorID FROM Materials WHERE Status = ‘Obsolete’ AND VendorID IS NULL DELETE does not allow specifying individual columns; it removes entire rows.
4 - DELETE FROM Materials WHERE Status = ‘Obsolete’ WHERE VendorID IS NULL The second WHERE clause is invalid SQL syntax.
