Test Your MySQL Key Knowledge: Business, Natural, and Candidate Keys

Progress

True / False

In MySQL, a business key is always the primary key of a table.

True False

A business key is a key that has real-world meaning outside the database and is used by business processes. It is not necessarily the primary key, which is a unique identifier for the records in a table.

True / False

In MySQL, a natural key is a key that is derived from application data.

True False

A natural key is a key that is based on the actual data of a table and has a logical relationship to the data it represents, such as a Social Security Number.

True / False

In MySQL, a candidate key can be composed of more than one column.

True False

A candidate key can consist of one or more columns. It uniquely identifies a row in a table and can be chosen as the primary key.

True / False

In MySQL, every natural key is a candidate key.

True False

A natural key is inherently a candidate key because it uniquely identifies rows within the table and can serve as a primary key.

True / False

In MySQL, business keys are typically generated by the database system.

True False

Business keys are usually derived from the business processes and not generated by the database system. They have a real-world meaning.

True / False

In MySQL, a candidate key must always be a numeric column.

True False

A candidate key can be of any data type as long as it uniquely identifies rows within the table. It does not have to be numeric.

True / False

In MySQL, a surrogate key is always a candidate key.

True False

A surrogate key is an artificial key added to a table to serve as a primary key. As such, it is always a candidate key because it uniquely identifies rows within the table.

True / False

In MySQL, a table can have multiple candidate keys but only one business key.

True False

A table can have multiple candidate keys, and it can also have multiple business keys, as long as they uniquely identify records in a meaningful way to the business.

True / False

In MySQL, a natural key can sometimes change, which can complicate database management.

True False

Natural keys are derived from real-world data, which can change. This can complicate database management because primary keys are generally expected to be stable.

Q10

True / False

In MySQL, it is possible for a single table to have no natural key but still have a candidate key.

True False

A table might not have a natural key derived from its data but can still have a candidate key, such as an auto-incremented surrogate key, which uniquely identifies the records in the table.

Q11

Single Choice

What is a business key in MySQL?

A unique identifier used by business users A foreign key A primary key generated by the databaseA key that includes only numeric values

A business key is a unique identifier that is meaningful to business users, often used in reports and interfaces.

Q12

Single Choice

Which of the following best describes a natural key in MySQL?

A surrogate key generated by the system A composite key made up of multiple columns A key with meaningful business valueA foreign key referencing another table

A natural key is a key with meaningful business value, often derived from real-world attributes

Q13

Single Choice

In MySQL, which of the following can be considered a candidate key?

Any column with unique values Only the primary key Any foreign keyOnly composite keys

A candidate key is any column or set of columns that can uniquely identify a row in a table.

Q14

Single Choice

How does MySQL ensure the uniqueness of a business key?

By using auto-increment By defining a UNIQUE constraint By using a foreign key constraintBy using a composite key

MySQL ensures the uniqueness of a business key by defining a UNIQUE constraint on the column.

Q15

Single Choice

What is the main difference between a natural key and a surrogate key in MySQL?

A natural key is generated by MySQL, while a surrogate key is provided by the user A natural key has business meaning, while a surrogate key does not A natural key is always a composite key, while a surrogate key is notThere is no difference

A natural key has business meaning and is derived from data attributes, while a surrogate key is an artificial key created by the system.

Q16

Single Choice

Which of the following is true about candidate keys in MySQL?

There can be only one candidate key in a table Candidate keys can be used as primary keys Candidate keys must be composite keysCandidate keys cannot have null values

Candidate keys can be chosen to act as primary keys because they uniquely identify rows in the table.

Q17

Single Choice

In MySQL, what happens if a natural key in a table needs to be updated?

It cannot be updated The related foreign key values in other tables are automatically updated It must be updated manually in all related tablesThe entire row is deleted and reinserted

Updating a natural key may require manual updates to related foreign key values unless cascading updates are enabled.

Q18

Single Choice

Why might a business choose to use a surrogate key instead of a natural key in MySQL?

To simplify the primary key structure To ensure the key remains constant even if the business rules change To improve performanceAll of the above

Surrogate keys simplify primary key structure, ensure constancy despite changes, and can improve performance.

Q19

Single Choice

Which of the following MySQL constraints can be used to enforce the uniqueness of a candidate key?

PRIMARY KEY FOREIGN KEY UNIQUECHECK

The UNIQUE constraint ensures that all values in a column or a set of columns are distinct.

Q20

Single Choice

During a MySQL database design certification exam, you're asked to design a table for storing product information. Which of the following keys would you choose as the primary key if the product codes are guaranteed to be unique and never change?

Surrogate key Composite key Natural keyForeign key

If product codes are unique and immutable, a natural key can be used as the primary key, as it has business meaning and does not require additional surrogate keys.

Q21

Multiple Choice

Which SQL statement demonstrates the creation of a candidate key in a table design?

SQL Code

CREATE TABLE employees (
 employee_id INT PRIMARY KEY,
 email VARCHAR(100) UNIQUE,
 social_security_number VARCHAR(11) UNIQUE
);

CREATE TABLE employees ( employee_id INT PRIMARY KEY, email VARCHAR(100) ); CREATE TABLE employees ( employee_id INT PRIMARY KEY, email VARCHAR(100) UNIQUE ); CREATE TABLE employees ( employee_id INT PRIMARY KEY, social_security_number VARCHAR(11) UNIQUE );CREATE TABLE employees ( employee_id INT PRIMARY KEY, email VARCHAR(100) UNIQUE, social_security_number VARCHAR(11) UNIQUE );

A candidate key is a column, or a set of columns, that can uniquely identify a record in a table. In this example, both 'email' and 'social_security_number' are candidate keys, with 'employee_id' as the primary key.

Q22

Multiple Choice

Which SQL code snippet correctly defines a natural key in a 'products' table?

SQL Code

CREATE TABLE products (
 product_code VARCHAR(10) PRIMARY KEY,
 product_name VARCHAR(100)
);

CREATE TABLE products ( product_id INT PRIMARY KEY, product_code VARCHAR(10) ); CREATE TABLE products ( product_code VARCHAR(10) PRIMARY KEY, product_name VARCHAR(100) ); CREATE TABLE products ( product_code VARCHAR(10) UNIQUE, product_name VARCHAR(100) );CREATE TABLE products ( product_id INT PRIMARY KEY, product_name VARCHAR(100) );

A natural key is a key that has a logical relationship to the data within the table. In this example, 'product_code' serves as a natural key because it uniquely identifies each product based on real-world information.

Q23

Multiple Choice

Which SQL statement demonstrates the use of a business key in a table design?

SQL Code

CREATE TABLE orders (
 order_number VARCHAR(20) PRIMARY KEY,
 customer_id INT,
 order_date DATE
);

CREATE TABLE orders ( order_id INT PRIMARY KEY, customer_id INT, order_date DATE ); CREATE TABLE orders ( order_number VARCHAR(20) PRIMARY KEY, customer_id INT, order_date DATE ); CREATE TABLE orders ( order_id INT PRIMARY KEY, order_number VARCHAR(20) UNIQUE );CREATE TABLE orders ( order_id INT PRIMARY KEY, order_date DATE );

A business key is a key that is derived from the business context, often serving as a natural key. In this example, 'order_number' is a business key that uniquely identifies each order.

Q24

Multiple Choice

Which SQL code correctly defines multiple candidate keys in a 'customers' table?

SQL Code

CREATE TABLE customers (
 customer_id INT PRIMARY KEY,
 email VARCHAR(100) UNIQUE,
 phone_number VARCHAR(15) UNIQUE
);

CREATE TABLE customers ( customer_id INT PRIMARY KEY, email VARCHAR(100) UNIQUE ); CREATE TABLE customers ( customer_id INT PRIMARY KEY, email VARCHAR(100), phone_number VARCHAR(15) ); CREATE TABLE customers ( customer_id INT PRIMARY KEY, email VARCHAR(100) UNIQUE, phone_number VARCHAR(15) UNIQUE );CREATE TABLE customers ( customer_id INT PRIMARY KEY, phone_number VARCHAR(15) UNIQUE );

In this table, 'email' and 'phone_number' are candidate keys because they uniquely identify each customer, while 'customer_id' is the primary key.

Q25

Multiple Choice

Which SQL snippet best illustrates a table design using a natural key as the primary key?

SQL Code

CREATE TABLE countries (
 country_code CHAR(3) PRIMARY KEY,
 country_name VARCHAR(100)
);

CREATE TABLE countries ( country_id INT PRIMARY KEY, country_code CHAR(3) ); CREATE TABLE countries ( country_code CHAR(3) PRIMARY KEY, country_name VARCHAR(100) ); CREATE TABLE countries ( country_code CHAR(3) UNIQUE, country_name VARCHAR(100) );CREATE TABLE countries ( country_id INT PRIMARY KEY, country_name VARCHAR(100) );

A natural key is a key that has a meaningful relationship with the data. In this example, 'country_code' is a natural key and serves as the primary key because it uniquely identifies each country in a real-world context.

Q26

Multiple Choice

Which SQL code demonstrates the use of a business key as a natural key in a table design?

SQL Code

CREATE TABLE employees (
 employee_number VARCHAR(10) PRIMARY KEY,
 employee_name VARCHAR(100)
);

CREATE TABLE employees ( employee_id INT PRIMARY KEY, employee_number VARCHAR(10) ); CREATE TABLE employees ( employee_number VARCHAR(10) PRIMARY KEY, employee_name VARCHAR(100) ); CREATE TABLE employees ( employee_number VARCHAR(10) UNIQUE, employee_name VARCHAR(100) );CREATE TABLE employees ( employee_id INT PRIMARY KEY, employee_name VARCHAR(100) );

In this example, 'employee_number' serves as both a business key and a natural key because it uniquely identifies each employee based on a business-related identifier.

Q27

Multiple Choice

Which SQL statement correctly illustrates the concept of a candidate key by defining multiple unique keys in a table?

SQL Code

CREATE TABLE users (
 user_id INT PRIMARY KEY,
 username VARCHAR(50) UNIQUE,
 email VARCHAR(100) UNIQUE
);

CREATE TABLE users ( user_id INT PRIMARY KEY, username VARCHAR(50) ); CREATE TABLE users ( user_id INT PRIMARY KEY, username VARCHAR(50) UNIQUE ); CREATE TABLE users ( user_id INT PRIMARY KEY, email VARCHAR(100) UNIQUE );CREATE TABLE users ( user_id INT PRIMARY KEY, username VARCHAR(50) UNIQUE, email VARCHAR(100) UNIQUE );

Candidate keys are columns that can uniquely identify a record in a table. In this example, 'username' and 'email' are candidate keys, while 'user_id' is the primary key.

Q28

Multiple Choice

Which SQL code best demonstrates the use of a natural key and a surrogate key in a single table?

SQL Code

CREATE TABLE employees (
 employee_id INT AUTO_INCREMENT PRIMARY KEY,
 social_security_number VARCHAR(11) UNIQUE,
 employee_name VARCHAR(100)
);

CREATE TABLE employees ( employee_id INT AUTO_INCREMENT PRIMARY KEY, employee_name VARCHAR(100) ); CREATE TABLE employees ( social_security_number VARCHAR(11) PRIMARY KEY, employee_name VARCHAR(100) ); CREATE TABLE employees ( employee_id INT AUTO_INCREMENT PRIMARY KEY, social_security_number VARCHAR(11) UNIQUE, employee_name VARCHAR(100) );CREATE TABLE employees ( employee_id INT AUTO_INCREMENT, employee_name VARCHAR(100) );

In this example, 'employee_id' is a surrogate key generated automatically by the system, while 'social_security_number' is a natural key that uniquely identifies each employee based on a real-world attribute.

Q29

Multiple Choice

Which SQL statement properly defines a business key, natural key, and candidate key in a 'suppliers' table?

SQL Code

CREATE TABLE suppliers (
 supplier_id INT PRIMARY KEY,
 supplier_code VARCHAR(10) UNIQUE,
 supplier_name VARCHAR(100)
);

CREATE TABLE suppliers ( supplier_id INT PRIMARY KEY, supplier_code VARCHAR(10) ); CREATE TABLE suppliers ( supplier_code VARCHAR(10) PRIMARY KEY, supplier_name VARCHAR(100) ); CREATE TABLE suppliers ( supplier_id INT PRIMARY KEY, supplier_code VARCHAR(10) UNIQUE, supplier_name VARCHAR(100) );CREATE TABLE suppliers ( supplier_id INT, supplier_code VARCHAR(10) );

In this example, 'supplier_code' is a business key and natural key because it uniquely identifies suppliers based on a business-related identifier. It is also a candidate key along with the 'supplier_id'.

Q30

Multiple Choice

Which SQL code demonstrates the creation of a table with both a business key and a candidate key, ensuring data integrity?

SQL Code

CREATE TABLE vehicles (
 vehicle_id INT PRIMARY KEY,
 vin VARCHAR(17) UNIQUE,
 license_plate VARCHAR(10) UNIQUE
);

CREATE TABLE vehicles ( vehicle_id INT PRIMARY KEY, vin VARCHAR(17) UNIQUE ); CREATE TABLE vehicles ( vin VARCHAR(17) PRIMARY KEY, license_plate VARCHAR(10) UNIQUE ); CREATE TABLE vehicles ( vehicle_id INT PRIMARY KEY, vin VARCHAR(17) UNIQUE, license_plate VARCHAR(10) UNIQUE );CREATE TABLE vehicles ( vehicle_id INT PRIMARY KEY, license_plate VARCHAR(10) UNIQUE );

In this example, 'vin' (Vehicle Identification Number) serves as both a business key and a natural key, uniquely identifying each vehicle. It is also a candidate key along with 'license_plate'.

MySQL Database Quiz Questions