Test Your Knowledge on Business Key, Natural Key, and Candidate Key

Progress

True / False

In ORACLE, a Business Key is always unique within a table

True False

A Business Key is a unique identifier used within the business context and must be unique within the table to ensure accurate identification of records.

True / False

A Natural Key in ORACLE is derived from the attributes of the business domain.

True False

A Natural Key is a key that is formed of attributes that naturally exist in the business domain, such as a social security number or email address.

True / False

In ORACLE, a Candidate Key can only consist of a single column.

True False

A Candidate Key can be composed of one or more columns, as long as it uniquely identifies a row in the table.

True / False

A Business Key in ORACLE can serve as a Natural Key.

True False

A Business Key can also be a Natural Key if it is derived from business attributes that uniquely identify a record.

True / False

In ORACLE, a table can have multiple Candidate Keys.

True False

A table can have multiple Candidate Keys, each of which can uniquely identify a row within the table.

True / False

Natural Keys in ORACLE databases are preferred over surrogate keys for performance reasons.

True False

Natural Keys are not necessarily preferred for performance reasons; surrogate keys are often used for performance and manageability benefits.

True / False

In ORACLE, a Candidate Key can be chosen as the Primary Key of a table.

True False

Any Candidate Key can be chosen as the Primary Key of a table since it uniquely identifies each row in the table.

True / False

A Business Key in ORACLE should not change over time to maintain data integrity.

True False

Business Keys should remain stable over time to maintain data integrity and avoid issues with data consistency.

True / False

In ORACLE, a Natural Key can contain null values.

True False

A Natural Key cannot contain null values because it must uniquely identify a row in the table, and null values would prevent this.

Q10

True / False

In ORACLE, while designing a table, it's mandatory to have at least one Candidate Key.

True False

When designing a table, it is mandatory to have at least one Candidate Key, as it is required to uniquely identify rows within the table. This ensures the integrity and uniqueness of the data.

Q11

Multiple Choice

Which SQL code snippet demonstrates the creation of a table with a business key in Oracle?

SQL Code

CREATE TABLE employees (
employee_id NUMBER PRIMARY KEY,
email VARCHAR2(100) UNIQUE,
first_name VARCHAR2(50),
last_name VARCHAR2(50)
);

CREATE TABLE employees ( employee_id NUMBER PRIMARY KEY, email VARCHAR2(100) UNIQUE, first_name VARCHAR2(50), last_name VARCHAR2(50) ); CREATE TABLE employees ( employee_id NUMBER PRIMARY KEY, email VARCHAR2(100) UNIQUE, first_name VARCHAR2(50), last_name VARCHAR2(50) ); CREATE TABLE employees ( employee_id NUMBER PRIMARY KEY, email VARCHAR2(100) UNIQUE, first_name VARCHAR2(50), last_name VARCHAR2(50) );CREATE TABLE employees ( employee_id NUMBER PRIMARY KEY, email VARCHAR2(100), first_name VARCHAR2(50), last_name VARCHAR2(50) );

Options A, B, and C correctly demonstrate creating a table with `email` as a business key because it uniquely identifies each employee. Option D is incorrect because it does not include the `UNIQUE` constraint on the `email` column.

Q12

Multiple Choice

Which SQL code snippet demonstrates the use of a natural key in Oracle?

SQL Code

CREATE TABLE products (
product_code VARCHAR2(50) PRIMARY KEY,
product_name VARCHAR2(100),
price NUMBER(10, 2)
);

CREATE TABLE products ( product_code VARCHAR2(50) PRIMARY KEY, product_name VARCHAR2(100), price NUMBER(10, 2) ); CREATE TABLE products ( product_code VARCHAR2(50) PRIMARY KEY, product_name VARCHAR2(100), price NUMBER(10, 2) ); CREATE TABLE products ( product_code VARCHAR2(50) PRIMARY KEY, product_name VARCHAR2(100), price NUMBER(10, 2) );CREATE TABLE products ( product_id NUMBER PRIMARY KEY, product_name VARCHAR2(100), price NUMBER(10, 2) );

Options A, B, and C correctly demonstrate using `product_code` as a natural key, as it is derived from the business context and uniquely identifies a product. Option D is incorrect because it does not use a natural key.

Q13

Multiple Choice

Which SQL code snippet demonstrates identifying a candidate key in a table?

SQL Code

CREATE TABLE students (
student_id NUMBER,
ssn VARCHAR2(11) UNIQUE,
email VARCHAR2(100) UNIQUE,
PRIMARY KEY (student_id)
);

CREATE TABLE students ( student_id NUMBER, ssn VARCHAR2(11) UNIQUE, email VARCHAR2(100) UNIQUE, PRIMARY KEY (student_id) ); CREATE TABLE students ( student_id NUMBER, ssn VARCHAR2(11) UNIQUE, email VARCHAR2(100) UNIQUE, PRIMARY KEY (student_id) ); CREATE TABLE students ( student_id NUMBER, ssn VARCHAR2(11) UNIQUE, email VARCHAR2(100) UNIQUE, PRIMARY KEY (student_id) );CREATE TABLE students ( student_id NUMBER, ssn VARCHAR2(11), email VARCHAR2(100), PRIMARY KEY (student_id) );

Options A, B, and C correctly demonstrate identifying `ssn` and `email` as candidate keys because they both uniquely identify a student. Option D is incorrect because it does not enforce uniqueness on either `ssn` or `email`.

Q14

Multiple Choice

Which SQL code snippet demonstrates creating a table with both a natural key and a surrogate key?

SQL Code

CREATE TABLE orders (
order_id NUMBER GENERATED ALWAYS AS IDENTITY,
order_number VARCHAR2(20) UNIQUE,
customer_id NUMBER,
PRIMARY KEY (order_id)
);

CREATE TABLE orders ( order_id NUMBER GENERATED ALWAYS AS IDENTITY, order_number VARCHAR2(20) UNIQUE, customer_id NUMBER, PRIMARY KEY (order_id) ); CREATE TABLE orders ( order_id NUMBER GENERATED ALWAYS AS IDENTITY, order_number VARCHAR2(20) UNIQUE, customer_id NUMBER, PRIMARY KEY (order_id) ); CREATE TABLE orders ( order_id NUMBER GENERATED ALWAYS AS IDENTITY, order_number VARCHAR2(20) UNIQUE, customer_id NUMBER, PRIMARY KEY (order_id) );CREATE TABLE orders ( order_id NUMBER GENERATED ALWAYS AS IDENTITY, order_number VARCHAR2(20), customer_id NUMBER, PRIMARY KEY (order_id) );

Options A, B, and C correctly demonstrate creating a table where `order_id` is the surrogate key and `order_number` is the natural key. Option D is incorrect because it lacks the unique constraint on `order_number`.

Q15

Multiple Choice

Which SQL code snippet demonstrates a situation where both a natural key and candidate keys are used?

SQL Code

CREATE TABLE vehicles (
vin VARCHAR2(17) PRIMARY KEY,
registration_number VARCHAR2(20) UNIQUE,
license_plate VARCHAR2(10) UNIQUE
);

CREATE TABLE vehicles ( vin VARCHAR2(17) PRIMARY KEY, registration_number VARCHAR2(20) UNIQUE, license_plate VARCHAR2(10) UNIQUE ); CREATE TABLE vehicles ( vin VARCHAR2(17) PRIMARY KEY, registration_number VARCHAR2(20) UNIQUE, license_plate VARCHAR2(10) UNIQUE ); CREATE TABLE vehicles ( vin VARCHAR2(17) PRIMARY KEY, registration_number VARCHAR2(20) UNIQUE, license_plate VARCHAR2(10) UNIQUE );CREATE TABLE vehicles ( vin VARCHAR2(17) PRIMARY KEY, registration_number VARCHAR2(20), license_plate VARCHAR2(10) );

Options A, B, and C correctly demonstrate a table where `vin` is the natural key, and `registration_number` and `license_plate` are candidate keys. Option D is incorrect because it lacks the unique constraint on `registration_number` and `license_plate`.

Q16

Multiple Choice

Which SQL code snippet demonstrates an advanced scenario where a business key is enforced along with other candidate keys?

SQL Code

CREATE TABLE employees (
employee_id NUMBER,
email VARCHAR2(100) UNIQUE,
social_security_number VARCHAR2(11) UNIQUE,
PRIMARY KEY (employee_id)
);

CREATE TABLE employees ( employee_id NUMBER, email VARCHAR2(100) UNIQUE, social_security_number VARCHAR2(11) UNIQUE, PRIMARY KEY (employee_id) ); CREATE TABLE employees ( employee_id NUMBER, email VARCHAR2(100) UNIQUE, social_security_number VARCHAR2(11) UNIQUE, PRIMARY KEY (employee_id) ); CREATE TABLE employees ( employee_id NUMBER, email VARCHAR2(100) UNIQUE, social_security_number VARCHAR2(11) UNIQUE, PRIMARY KEY (employee_id) );CREATE TABLE employees ( employee_id NUMBER, email VARCHAR2(100), social_security_number VARCHAR2(11), PRIMARY KEY (employee_id) );

Options A, B, and C correctly demonstrate enforcing a business key (`email`) and other candidate keys (`social_security_number`). Option D is incorrect because it does not enforce uniqueness on the `social_security_number`.

Q17

Multiple Choice

Which SQL code snippet demonstrates a certification-level scenario using both natural keys and surrogate keys in a composite key?

SQL Code

CREATE TABLE enrollment (
enrollment_id NUMBER GENERATED ALWAYS AS IDENTITY,
student_id NUMBER,
course_code VARCHAR2(10),
PRIMARY KEY (enrollment_id, student_id)
);

CREATE TABLE enrollment ( enrollment_id NUMBER GENERATED ALWAYS AS IDENTITY, student_id NUMBER, course_code VARCHAR2(10), PRIMARY KEY (enrollment_id, student_id) ); CREATE TABLE enrollment ( enrollment_id NUMBER GENERATED ALWAYS AS IDENTITY, student_id NUMBER, course_code VARCHAR2(10), PRIMARY KEY (enrollment_id, student_id) ); CREATE TABLE enrollment ( enrollment_id NUMBER GENERATED ALWAYS AS IDENTITY, student_id NUMBER, course_code VARCHAR2(10), PRIMARY KEY (enrollment_id, student_id) );CREATE TABLE enrollment ( enrollment_id NUMBER GENERATED ALWAYS AS IDENTITY, student_id NUMBER, course_code VARCHAR2(10), PRIMARY KEY (enrollment_id) );

Options A, B, and C correctly demonstrate a composite key scenario with a natural key (`course_code`) and a surrogate key (`enrollment_id`). Option D is incorrect because it does not include a composite key.

Q18

Multiple Choice

Which SQL code snippet demonstrates enforcing a business key and candidate keys in a composite key scenario?

SQL Code

CREATE TABLE suppliers (
supplier_id NUMBER,
supplier_code VARCHAR2(20) UNIQUE,
company_name VARCHAR2(100) UNIQUE,
PRIMARY KEY (supplier_id)
);

CREATE TABLE suppliers ( supplier_id NUMBER, supplier_code VARCHAR2(20) UNIQUE, company_name VARCHAR2(100) UNIQUE, PRIMARY KEY (supplier_id) ); CREATE TABLE suppliers ( supplier_id NUMBER, supplier_code VARCHAR2(20) UNIQUE, company_name VARCHAR2(100) UNIQUE, PRIMARY KEY (supplier_id) ); CREATE TABLE suppliers ( supplier_id NUMBER, supplier_code VARCHAR2(20) UNIQUE, company_name VARCHAR2(100) UNIQUE, PRIMARY KEY (supplier_id) );CREATE TABLE suppliers ( supplier_id NUMBER, supplier_code VARCHAR2(20), company_name VARCHAR2(100), PRIMARY KEY (supplier_id) );

Options A, B, and C correctly demonstrate a composite key scenario where `supplier_code` is a business key, and `company_name` is a candidate key. Option D is incorrect because it does not enforce uniqueness on `company_name`.

Q19

Multiple Choice

Which SQL code snippet demonstrates using a natural key as part of a composite primary key?

SQL Code

CREATE TABLE flight_schedule (
flight_id NUMBER,
flight_code VARCHAR2(10),
departure_time TIMESTAMP,
PRIMARY KEY (flight_code, departure_time)
);

CREATE TABLE flight_schedule ( flight_id NUMBER, flight_code VARCHAR2(10), departure_time TIMESTAMP, PRIMARY KEY (flight_code, departure_time) ); CREATE TABLE flight_schedule ( flight_id NUMBER, flight_code VARCHAR2(10), departure_time TIMESTAMP, PRIMARY KEY (flight_code, departure_time) ); CREATE TABLE flight_schedule ( flight_id NUMBER, flight_code VARCHAR2(10), departure_time TIMESTAMP, PRIMARY KEY (flight_code, departure_time) );CREATE TABLE flight_schedule ( flight_id NUMBER, flight_code VARCHAR2(10), departure_time TIMESTAMP, PRIMARY KEY (flight_id) );

Options A, B, and C correctly demonstrate using `flight_code` as a natural key within a composite primary key. Option D is incorrect because it does not include a composite key.

Q20

Multiple Choice

Which SQL code snippet demonstrates a certification-level scenario with both business and candidate keys?

SQL Code

CREATE TABLE departments (
department_id NUMBER,
department_name VARCHAR2(50) UNIQUE,
department_code VARCHAR2(10) UNIQUE,
PRIMARY KEY (department_id)
);

CREATE TABLE departments ( department_id NUMBER, department_name VARCHAR2(50) UNIQUE, department_code VARCHAR2(10) UNIQUE, PRIMARY KEY (department_id) ); CREATE TABLE departments ( department_id NUMBER, department_name VARCHAR2(50) UNIQUE, department_code VARCHAR2(10) UNIQUE, PRIMARY KEY (department_id) ); CREATE TABLE departments ( department_id NUMBER, department_name VARCHAR2(50) UNIQUE, department_code VARCHAR2(10) UNIQUE, PRIMARY KEY (department_id) );CREATE TABLE departments ( department_id NUMBER, department_name VARCHAR2(50), department_code VARCHAR2(10), PRIMARY KEY (department_id) );

Options A, B, and C correctly demonstrate a table where `department_name` is a business key and `department_code` is a candidate key. Option D is incorrect because it does not enforce uniqueness on `department_code`.

Q21

Single Choice

In Oracle databases, what is a Business Key?

A key that is generated automatically by Oracle. A key that uniquely identifies a record using meaningful real-world data. A key that is used only for internal processing within Oracle.A key that consists of multiple columns.

A Business Key is a key that is derived from the business domain and is meaningful in the real world. It is used to uniquely identify a record based on business attributes.

Q22

Single Choice

Which of the following best describes a Natural Key in an Oracle database?

A key that is automatically generated by Oracle's sequence. A key that is naturally occurring and meaningful in the business context. A key that combines multiple foreign keys.A key that serves as a backup to the primary key.

A Natural Key is a key that exists naturally within the data and has business significance, such as a Social Security Number or an email address.

Q23

Single Choice

What is a Candidate Key in Oracle databases?

A key that is always used as the primary key. A key that is a potential primary key but not necessarily selected as one. A key that is always foreign.A key that is created by concatenating two columns.

A Candidate Key is a set of attributes that can uniquely identify a record in a table. A table can have multiple Candidate Keys, but only one is selected as the Primary Key.

Q24

Single Choice

Consider the following Oracle SQL table definition:Which of the following could be considered as Natural Keys?

SQL Code

CREATE TABLE employees (
 emp_id NUMBER,
 emp_ssn VARCHAR2(11),
 emp_email VARCHAR2(50),
 emp_name VARCHAR2(100),
 PRIMARY KEY (emp_id)
);

emp_id emp_name emp_ssnemp_email

emp_ssn (Social Security Number) is a naturally occurring identifier that has significance in the business context, making it a Natural Key.

Q25

Single Choice

In the following Oracle SQL code, which columns can be Candidate Keys?

SQL Code

CREATE TABLE students (
 student_id NUMBER,
 student_email VARCHAR2(100),
 student_roll_no VARCHAR2(20),
 student_name VARCHAR2(100),
 PRIMARY KEY (student_id)
);

student_id and student_email student_id and student_roll_no student_name and student_emailstudent_roll_no and student_name

Both student_id and student_roll_no are unique and can serve as Candidate Keys because they can uniquely identify each student in the table.

Q26

Single Choice

Given the following Oracle table:Which of these could serve as a Business Key?

SQL Code

CREATE TABLE products (
 product_id NUMBER,
 product_sku VARCHAR2(50),
 product_name VARCHAR2(100),
 product_price NUMBER,
 PRIMARY KEY (product_id)
);

product_id product_sku product_nameproduct_price

The product_sku (Stock Keeping Unit) is a Business Key because it is meaningful in the business context and can be used to uniquely identify a product.

Q27

Single Choice

In Oracle, why might you choose to use a Natural Key over a Surrogate Key?

Natural Keys are faster in query performance. Natural Keys are easier to manage because they have real-world meaning. Natural Keys can be generated by Oracle sequences.Natural Keys never change, making them more reliable.

Natural Keys have real-world significance, making them easier to manage and understand within the context of the business. However, they may change over time, which is a potential drawback.

Q28

Single Choice

Consider the following Oracle SQL table definition:If order_number is unique and business meaningful, what key type does it represent?

SQL Code

If order_number is unique and business meaningful, what key type does it represent?

Surrogate Key Natural Key Composite KeyForeign Key

order_number is a Natural Key because it has business significance and is unique, allowing it to identify an order in a meaningful way.

Q29

Single Choice

In Oracle, a Candidate Key is defined as:

A key that can only be used as a foreign key. A key that cannot have any null values. A key that is used as a composite key.A key that can be chosen as the primary key from multiple keys.

A Candidate Key is an attribute or set of attributes that can uniquely identify a record. If a table has multiple Candidate Keys, any of them could potentially be chosen as the Primary Key.

Q30

Single Choice

Analyze the following Oracle table:If both product_code and warehouse_id are unique within the context of the business, how would you classify these keys?

SQL Code

CREATE TABLE inventory (
 inventory_id NUMBER,
 product_code VARCHAR2(50),
 warehouse_id NUMBER,
 quantity NUMBER,
 PRIMARY KEY (inventory_id)
);

Both are Surrogate Keys Both are Candidate Keys Both are Natural KeysBoth are Foreign Keys

Both product_code and warehouse_id are Natural Keys because they have business meaning and can uniquely identify records within the context of the business.

Oracle Database Quiz Questions