210 Questions
Which one of the following is a key component of a database system that translates query processor instructions into low-level file-system commands that modify or retrieve data?
Storage manager
Which one of the following is a file containing a complete record of all inserts, updates, and deletes processed by the database?
Log
Which one of the following is a directory of tables, columns, indexes, and other database objects?
Catalog / data dictionary
Which one of the following is a common query language used for writing database queries?
SQL
Which one of the following is an SQL statement that inserts rows into a table?
INSERT
Which one of the following is a specification of database objects such as tables, columns, data types, and indexes?
Database design
Which type of data is mostly digital in today's world?
Numeric
What is a database system?
Software that reads and writes data in a database
What is the role of a database administrator?
Securing the database system against unauthorized users
What is the purpose of a query language?
To read and write data in a database
What is the role of a database designer?
Determining the format of each data element and the overall database structure
What is the role of a database programmer?
Developing computer programs that utilize a database
Which of the following is a key feature of MySQL Enterprise?
MySQL Enterprise includes additional administrative applications.
What is the purpose of the root account in MySQL?
The root account has full control of MySQL.
What is the MySQL Command-Line Client used for?
The MySQL Command-Line Client is used for executing SQL commands using an editor.
What does MySQL Server return when an SQL statement is syntactically incorrect or the database cannot execute the statement?
MySQL Server returns an error code and description.
What is the purpose of MySQL Workbench?
MySQL Workbench is used for executing SQL commands using an editor.
Which of the following is a key feature of the relational model?
The relational model is a database model based on a tabular data structure.
Which statement is true about primary keys?
A primary key is a column used to identify a row.
What is the purpose of the TRUNCATE statement?
To delete all rows from a table.
What is a foreign key?
A column that refers to a primary key.
Which action is specified by the ON UPDATE clause of a FOREIGN KEY constraint?
Propagates primary key changes to foreign keys.
What does the UNIQUE constraint ensure?
Values in a column, or group of columns, are unique.
What is a constraint in a database?
A rule that governs allowable values in a database.
Which statement is true about the CREATE TABLE statement?
The CREATE TABLE statement creates a new table by specifying the table name, column names, and column data types.
What is the purpose of the DROP TABLE statement?
To delete a table, along with all the table's rows, from a database.
What are the data types represented by Decimal?
Numbers with fractional values
What does the IS NULL operator do?
Returns TRUE when the value is NULL
What does the LIMIT clause do in MySQL?
Limits the number of rows returned by a SELECT statement
What does the SET clause do in an UPDATE statement?
Specifies the new column values for an UPDATE statement
Which one of the following is a key feature of MySQL Community?
It is a free edition of MySQL
Which one of the following is a key component of a database system that interprets queries, creates a plan to modify the database or retrieve data, and returns query results to the application?
Query processor
What is the purpose of the UNIQUE constraint in a database?
It ensures that the column can only have unique values
Which one of the following is a common query language used for writing database queries?
SQL
What is the role of a database designer in the database design process?
To develop the specification of database objects such as tables, columns, data types, and indexes
Which SQL statement inserts rows into a table?
INSERT
Which of the following is responsible for securing the database system against unauthorized users?
Database administrator
What is the primary role of a database designer?
Determine the format of each data element and the overall database structure
Which type of data was mostly analog in the past?
Numeric
What is the purpose of a query language?
Write and execute queries in a database
What is the role of a database programmer?
Develop computer programs that utilize a database
What is the most common format for data encoding in today's world?
Zeros and ones
Which clause is used to specify a condition for selecting rows in a SELECT statement?
WHERE
What is the purpose of the IS NULL operator in SQL?
It returns TRUE when the value is NULL
Which statement is true about the NOT NULL constraint in SQL?
It prevents a column from having a NULL value
Which clause is used to add rows to a table in SQL?
INSERT INTO
What is the purpose of the SET clause in an UPDATE statement?
It specifies the new column values
What is a NULL value in SQL?
A special value that represents unknown or inapplicable data
Which statement is true about the MySQL Command-Line Client?
It allows developers to connect to the database server and execute SQL statements.
What is the purpose of the SHOW CREATE TABLE statement?
To show the CREATE TABLE statement for a table in the default database.
What is the purpose of the USE DatabaseName statement?
To select a default database for use in subsequent SQL statements.
What is the purpose of the DROP DATABASE DatabaseName statement?
To delete a database, including all tables in the database.
What is the purpose of the SHOW TABLES statement?
To list all tables in the default database.
What is the purpose of the SHOW COLUMNS FROM TableName statement?
To show the structure of a table in the default database.
Which statement is true about referential integrity?
Referential integrity requires foreign key values to be either NULL or match some value of the referenced primary key.
What is the purpose of the CASCADE action in a FOREIGN KEY constraint?
CASCADE propagates primary key changes to foreign keys.
What is a primary key?
A primary key is a column that ensures uniqueness and non-null values.
What does the TRUNCATE statement do?
The TRUNCATE statement deletes all rows from a table.
What is a foreign key?
A foreign key is a column that refers to a primary key.
What is the purpose of the UNIQUE constraint?
The UNIQUE constraint ensures that values in a column are unique.
Which operation combines two tables into one result, including all columns and all combinations of rows from both tables?
Product operation
Which operation selects table columns?
Project operation
Which operation selects table rows based on a logical expression?
Select operation
Which operation is equivalent to a product followed by a select?
Join operation
Which operation combines all rows of two compatible tables into a single table, excluding duplicate rows?
Union operation
Which one of the following is a database operation that reads table blocks directly, without accessing an index?
Table scan
Which one of the following is a single-level index that contains column values along with pointers to rows containing the column value?
Single-level index
Which one of the following is a multi-level index that stores column values and row pointers in a hierarchy?
Multi-level index
Which one of the following is a balanced multi-level index where all branches are the same length?
Balanced multi-level index
Which one of the following is a hash index where index entries are assigned to buckets?
Hash index
Which keyword represents the highest column value in a partition?
MAXVALUE
What type of partition requires a partition expression with positive integer values?
Hash partition
What is the partition number for each row computed as in a hash partition?
(partition expression value) modulo N
What does the physical design of a database specify?
Indexes, table structures, and partitions
What is the role of a storage engine or storage manager in a database system?
Translate instructions into low-level commands
Which operator is used in a WHERE clause to determine if a value matches one of several values?
IN
Which operator provides an alternative way to determine if a value is between two other values?
BETWEEN
What is a self-join?
A join between a table and itself.
What is a cross-join?
A join that combines two tables without comparing columns.
What is a subquery, sometimes called a nested query or inner query?
A query within another SQL query.
Which normal form is achieved when every cell of a table contains exactly one value and the table has a primary key?
First normal form
Which normal form is achieved when all non-key columns depend on the whole primary key?
Second normal form
Which normal form is achieved when all non-key columns depend on the key, the whole key, and nothing but the key?
Third normal form
Which normal form is achieved when, whenever a non-key column A depends on column B, then B is unique?
Boyce-Codd normal form
Which storage media is used to store large amounts of data and groups data in sectors?
Magnetic disk
Which of the following is true about cardinality in entity-relationship modeling?
Cardinality refers to the maximum and minimum number of relationships and attributes.
What is the cardinality of a relationship when the maximum number of instances of one entity that can relate to a single instance of another entity is one?
Singular
What is the cardinality of a relationship when the minimum number of instances of one entity that can relate to a single instance of another entity is zero?
Optional
What is the identifying attribute of a weak entity?
A weak entity does not have an identifying attribute.
What is a supertype entity in entity-relationship modeling?
A supertype entity is a subset of another entity type.
Which one of the following is a database operation that reads table blocks directly, without accessing an index?
Table scan
Which one of the following is a multi-level index that stores column values and row pointers in a hierarchy?
B+tree
Which one of the following is a subset of table data, usually a subset of rows rather than columns, stored on different computers of a distributed database?
Shard
Which one of the following is a balanced multi-level index where all branches are the same length?
B+tree
Which one of the following is a file containing column values, along with pointers to rows containing the column value?
Single-level index
Which operation in relational algebra combines two tables into one result, including all columns and all combinations of rows from both tables?
Product
What is the purpose of the rename operation in relational algebra?
To specify new table and column names
Which set operation in relational algebra combines all rows of two compatible tables into a single table, excluding duplicate rows?
Union
What is the purpose of the aggregate operation in relational algebra?
To apply aggregate functions
In the entity-relationship model, what is an entity?
A person, place, product, concept, or activity
Which normal form is achieved when all non-key columns depend on the whole primary key?
Second normal form
What is the purpose of normalization in database design?
To eliminate redundancy
Which storage media is typically used as the primary memory when computer programs execute?
Volatile memory
Which operator is used in a WHERE clause to determine if a value matches one of several values?
IN
What is the purpose of denormalization in database design?
To improve performance
Which operator provides an alternative way to determine if a value is between two other values?
BETWEEN
Which table structure imposes no order on the rows?
Heap table
What is a self-join?
A join that joins a table to itself
What is a cross-join?
A join that combines two tables without comparing columns
What is a subquery, sometimes called a nested query or inner query?
A query within another SQL query
Which keyword represents the highest column value in a partition?
MAXVALUE
What does a list partition associate each partition with?
An explicit list of partition expression values
What does a hash partition require for the partition expression?
Positive integer values
What is the partition number for each row computed as in a hash partition?
(partition expression value) modulo N
What is the difference between a key partition and a hash partition?
The partition expression is determined automatically in a key partition
Which of the following is true about cardinality in entity-relationship modeling?
Cardinality refers to the maximum and minimum number of instances of relationships and attributes.
What is the cardinality of a relationship when the maximum number of instances of one entity that can relate to a single instance of another entity is one?
One-to-One
What is an identifying attribute of an entity?
An identifying attribute is unique, singular, and required.
What is a strong entity in entity-relationship modeling?
A strong entity has one or more identifying attributes.
What is a weak entity in entity-relationship modeling?
A weak entity does not have an identifying attribute.
Which component of MySQL is responsible for managing connections from multiple users and compiling queries into low-level instructions for the storage engine?
Query processor
What is the purpose of the Audit component in MySQL?
Track all database changes
Which component of MySQL is responsible for executing instructions, managing indexes, and interacting with the file system?
Storage engine
What is the purpose of the Monitor component in MySQL?
Collect and display information on CPU and memory utilization
What is the purpose of the Utility programs component in MySQL?
Include installation, client, administrative, developer, and miscellaneous tools
What is the purpose of a connection in MySQL?
Manage connections from multiple users
What is an execution plan in MySQL?
A detailed, low-level sequence of steps that specify exactly how to process a query
Which type of database runs on multiple computers connected by a wide area network?
Distributed database
What is the purpose of the two-phase commit technique in distributed transactions?
To coordinate updates between participating nodes
In a distributed transaction, when does phase 2 begin?
When participating nodes receive confirmation from the transaction coordinator
What is the term used to describe updates in a distributed transaction that occur at the same time from the perspective of the database user?
Synchronous updates
What is the term used to describe databases that use local transactions instead of distributed transactions?
Eventually consistent databases
What is a replica in the context of replicated databases?
All of the above
What is the purpose of the ETL process in data warehousing?
All of the above
Which tier of a multi-tier architecture consists of servers managing resources like databases and email?
Bottom tier
In a multi-tier architecture, what do the middle tiers execute?
All of the above
What is the top tier of a web architecture responsible for?
Managing user interaction
What is the bottom tier of a web architecture composed of?
Services
What does Infrastructure-as-a-service (IaaS) provide to customers?
All of the above
What does Platform-as-a-service (PaaS) provide to customers?
All of the above
What does Software-as-a-service (SaaS) provide to customers?
Complete applications
Which of the following best describes a fact table in a database system?
A table that contains numeric data used to measure business performance.
What type of data does a dimension table contain?
Textual data that describes the fact data.
What is a dimension hierarchy in a database system?
A sequence of columns in which each column has a one-many relationship to the next column.
How many rows would a date dimension table contain if an organization tracks data for 100 years?
36,500 rows
How many rows would a time dimension table contain?
1,440 rows
What is the type 2 design for slowly changing dimensions in a fact table?
Adding start and end foreign keys to the fact table.
What is an in-memory database?
A database that stores data in main memory, instead of or in addition to storage media.
What is an embedded database?
A database that is packaged with a programming language.
What is a federated database?
A collection of two or more participating databases underneath a coordinating software layer.
Which tier of a web architecture consists of computers interacting directly with end-users?
Top tier
Which tier of a web architecture generates web pages for display on web browsers and transmits user requests to services running on lower tiers?
Middle tier
Which tier of a web architecture runs application software, processes user requests, and communicates with databases and other services?
Middle tier
Which tier of a web architecture comprises services such as database and authentication?
Bottom tier
Which type of computer architecture consists of multiple processors managed by a single operating system instance?
Parallel computer
In a shared memory computer, what do processors share?
Memory and storage media
In a shared storage computer, what do processors share?
Storage media only
Which component of MySQL is responsible for executing instructions, managing indexes, and interacting with the file system?
Storage engine
What is the purpose of the Monitor component in MySQL?
To collect and display information on CPU, memory, and index utilization
Which component of MySQL supports government and business audit requirements for sensitive databases?
Audit
What is the purpose of the Utility programs component in MySQL?
To provide various tools for installation, administration, and development
What is a connection in the context of MySQL?
A link between tools and the query processor
What is an execution plan in MySQL?
A detailed, low-level sequence of steps that specify exactly how to process a query
What is the purpose of the MySQL Enterprise Edition?
To provide a paid version of MySQL with additional features
Which of the following is true about a distributed transaction in a distributed database?
A distributed transaction updates data on multiple nodes of a distributed database.
What is the purpose of the two-phase commit technique in distributed transactions?
To instruct all nodes to commit the updates.
What is the difference between synchronous updates and asynchronous updates in a distributed transaction?
Synchronous updates occur at the same time from the perspective of the database user, while asynchronous updates occur at different times.
What is the term used to describe databases that use local transactions instead of distributed transactions?
Eventually consistent databases
What is a replica in the context of replicated databases?
All of the above
What is a data warehouse optimized for?
Analytics
What is the purpose of the ETL process in data warehousing?
All of the above
Which of the following best describes a fact table in a database system?
A fact table contains numeric data used to measure business performance.
What is a dimension table in a database system?
A dimension table contains textual data that describes the fact data.
What is a dimension hierarchy in a database system?
A dimension hierarchy is a sequence of columns in which each column has a one-many relationship to the next column.
How many rows does a date dimension table contain if an organization tracks data for 100 years?
36,500 rows
How many rows does a time dimension table contain?
1,440 rows
What is the type 2 design for slowly changing dimensions in a fact table?
Adding start and end foreign keys to the fact table.
What is an in-memory database?
A database that stores data in main memory, instead of or in addition to storage media.
What is an embedded database?
A database that is packaged with a programming language.
What is a federated database?
A collection of two or more participating databases underneath a coordinating software layer.
Which of the following is NOT a reason to include a year attribute in the sales tracking system?
To ensure uniqueness of the salesperson-product combination
What is the purpose of adding a year attribute to the sales tracking system?
To determine in which year a particular value of the Quantity attribute applies
In which scenario would the combination of Salesperson Number and Product Number NOT be unique?
When tracking sales on an annual basis
What is an example of an associative entity with its own unique identifier?
Motorist and Police Officer
What serves as the unique identifier for each traffic ticket in the example given?
Unique Serial Number
What is the purpose of including a year attribute in the sales tracking system?
To make it clear in which year a particular value of the Quantity attribute applies
Which type of entity is designed to list the attributes that directly describe the entity, with no complications involving other entities?
Entity box
What does the quantity attribute describe in an E-R diagram?
The combination of a particular salesperson and a particular product
In an E-R diagram, where is the intersection data, such as quantity, represented for a many-to-many relationship between salespersons and products?
In a separate box attached to the relationship line
When a many-to-many relationship is converted into an associative entity, what happens to the cardinalities and modalities?
They are reversed
What is the unique identifier of a many-to-many relationship or an associative entity in an E-R diagram?
The combination of the unique identifiers of the two entities in the relationship
What additional attribute(s) may need to be added to the combination of unique identifiers to ensure uniqueness in a many-to-many relationship or an associative entity?
An additional attribute related to a time element
Which type of unary relationship involves one person being married to another person and vice versa?
One-to-one
In the context of unary relationships, what does the modality of one rather than zero signify?
The situation where a salesperson does not have a backup
What does the downward branch out of the salesperson entity box in Figure 9.2.1b represent?
A salesperson managing zero to many other salespersons
What is the cardinality of a one-to-many unary relationship?
One entity can be associated with zero to many instances of another entity
What is the maximum number of instances of one entity that can relate to a single instance of another entity in a one-to-one unary relationship?
One
What does the one-to-one unary relationship called Back-Up involve in the context of the salesperson entity?
Organizing salespersons in pairs as backup to each other
Which symbol in an E-R diagram represents the multiple association?
The crow's foot
In a many-to-many binary relationship, what does it indicate?
A salesperson is authorized to sell many products
What does the 'inner' symbol represent in an E-R diagram?
The modality - the minimum number of entity occurrences involved in a relationship
Can the quantity attribute be added to the product entity box in a many-to-many relationship between salespersons and products?
No, because there would be lots of 'quantities', one for each salesperson selling the product
What does it mean when a company rule sets a limit of a maximum of ten customers in a sales territory?
A salesperson can have many customers but not more than 10
What does the 'outer' symbol represent in an E-R diagram?
The cardinality - the maximum number of entity occurrences involved in a relationship
Which type of relationship involves three different entity types?
Ternary relationship
In a many-to-many unary relationship, how are the parts related to each other?
Each part is a component of several other parts
What does a binary relationship represent in an E-R diagram?
A relationship between two entity types
What does a one-to-one (1-1) binary relationship mean?
A single occurrence of one entity type can be associated with a single occurrence of the other entity type
What does a one-to-many (1-M) binary relationship represent?
A relationship between two entity types where multiple occurrences of one entity type can be associated with a single occurrence of the other entity type
What does the "bar" or "one" symbol indicate in an E-R diagram?
The minimum one cardinality
Test your knowledge of MySQL Enterprise, the paid edition for managing commercial databases. Learn about the root account, the administrative account with full control of MySQL, and the MySQL Command-Line Client, a text interface included in the MySQL Server download.
Make Your Own Quizzes and Flashcards
Convert your notes into interactive study material.
Get started for free
