Test Your PostgreSQL Business, Natural, and Candidate Key Knowledge with this Quiz

Progress

True / False

In PostgreSQL, a business key is a key that uniquely identifies a record using business logic.

True False

A business key is a unique identifier derived from business logic, often meaningful in the context of the business, such as an email address or a product code.

True / False

In PostgreSQL, a natural key is always the best choice for a primary key.

True False

While natural keys can be used as primary keys, they are not always the best choice due to potential changes in business requirements or data.

True / False

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

True False

A candidate key is an attribute or a set of attributes that uniquely identifies a row and can potentially serve as the primary key.

True / False

In PostgreSQL, a table can have multiple candidate keys.

True False

A table can have multiple candidate keys, but only one of them can be chosen as the primary key.

True / False

In PostgreSQL, a natural key is always a candidate key.

True False

A natural key is a candidate key because it is an attribute that can uniquely identify a row in a table.

True / False

In PostgreSQL, a surrogate key is the same as a business key.

True False

A surrogate key is an artificial key, usually an auto-incremented number, that uniquely identifies a record, whereas a business key is derived from business data.

True / False

In PostgreSQL, using a natural key as a primary key can lead to issues if the business logic changes.

True False

If the business logic changes, natural keys might need to be updated, which can cause complications and potential integrity issues.

True / False

In PostgreSQL, a business key can never be a surrogate key.

True False

A business key is inherently tied to business logic and meaningful data, while a surrogate key is an arbitrary identifier.

True / False

In PostgreSQL, a composite key can be a candidate key.

True False

A composite key, which consists of multiple columns, can serve as a candidate key if it uniquely identifies a row.

Q10

True / False

In PostgreSQL, a table must have at least one candidate key that can act as a primary key.

True False

A table must have at least one candidate key to ensure that there is a way to uniquely identify each row, which is a fundamental requirement for defining a primary key.

Q11

Single Choice

In PostgreSQL, which of the following is true about a Business Key?

It is a system-generated surrogate key. It is used solely for indexing purposes It uniquely identifies a business entity in the natural world.It is automatically generated by PostgreSQL.

A Business Key is derived from the natural characteristics of a business entity and is used to uniquely identify that entity in a business context.

Q12

Single Choice

Which of the following statements is correct regarding a Natural Key in PostgreSQL?

It is an artificial key generated by the system. It consists of one or more columns that are naturally unique It must be an auto-incremented integer.It is only used for indexing

A Natural Key is a key that is derived from the natural data in a table and is inherently unique.

Q13

Single Choice

What distinguishes a Candidate Key from other keys in PostgreSQL?

It can be null It can consist of multiple columns. It is guaranteed to be unique within a table.It is the same as the Primary Key

A Candidate Key is a set of attributes that can uniquely identify a row in a table, and it is one of the potential candidates for being chosen as the Primary Key.

Q14

Single Choice

In PostgreSQL, how would you define a Candidate Key using SQL?

SQL Code

CREATE TABLE employees (
 emp_id SERIAL PRIMARY KEY,
 emp_ssn VARCHAR(11) UNIQUE,
 emp_email VARCHAR(255),
 UNIQUE(emp_email)
);

emp_id is the Candidate Key. emp_ssn and emp_email are Candidate Keys. Only emp_ssn is a Candidate Key.emp_email cannot be a Candidate Key.

In this SQL statement, both emp_ssn and emp_email are defined as unique, making them Candidate Keys, while emp_id is the Primary Key.

Q15

Single Choice

Given the following PostgreSQL table definition, which key is a Natural Key?

SQL Code

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

product_code product_name priceNone of the above

In this case, product_code is a Natural Key as it naturally identifies each product and is also the Primary Key.

Q16

Single Choice

In PostgreSQL, when would you prefer using a Business Key over a system-generated key?

When you need a key that is not dependent on the business logic. When the key must be unique across multiple systems. When the key should reflect the business's natural identity.When the key needs to be hidden from users.

A Business Key is preferred when the key needs to naturally represent the business entity, providing clarity and consistency across different systems.

Q17

Single Choice

How would you define both a Natural Key and a Candidate Key in a PostgreSQL table?

SQL Code

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

A) order_id is the Natural Key and order_number is the Candidate Key. order_number, customer_id is a composite Candidate Key order_id is the Business Keyorder_number, customer_id is a composite Natural Key.

The combination of order_number and customer_id forms a composite Candidate Key, which can uniquely identify an order, while order_id is a system-generated Primary Key.

Q18

Single Choice

Which of the following best describes the relationship between a Business Key and a Candidate Key in PostgreSQL?

A Business Key cannot be a Candidate Key. A Candidate Key must always be a Business Key. A Business Key can be one of the Candidate Keys.A Candidate Key is never a Natural Key.

A Business Key is often a Candidate Key because it uniquely identifies a row in a table, but not all Candidate Keys are Business Keys.

Q19

Single Choice

Consider the following PostgreSQL table schema:

SQL Code

CREATE TABLE vehicles (
 vin VARCHAR(17) PRIMARY KEY,
 registration_number VARCHAR(10) UNIQUE,
 engine_number VARCHAR(20) UNIQUE
);
Which of the following is true?

vin is a Natural Key, while registration_number and engine_number are Candidate Keys. vin is a Business Key, while registration_number is a Natural Key. vin is a Candidate Key, while registration_number and engine_number are Natural Keys.registration_number is a Business Key, while engine_number is a Candidate Key.

vin (Vehicle Identification Number) is a Natural Key, as it is a naturally occurring unique identifier for vehicles. registration_number and engine_number are unique within the table and serve as Candidate Keys.

Q20

Single Choice

In a PostgreSQL system where both Business Key and Candidate Key are defined, how should a developer decide which key to use in a JOIN operation?

Always use the Business Key as it is more meaningful. Use the Candidate Key that has the least potential for change. Use the system-generated Primary Key for better performance.Always avoid using Candidate Keys in JOINs.

When performing JOIN operations, it is advisable to use a Candidate Key that is stable and unlikely to change, to ensure referential integrity and performance.

Q21

Multiple Choice

Which of the following best defines a Business Key in PostgreSQL?

SQL Code

Given the 'employees' table with columns 'employee_id', 'social_security_number', 'email', and 'phone_number', identify the Business Key and implement a unique constraint to ensure its uniqueness.

```sql
CREATE TABLE employees (
 employee_id SERIAL PRIMARY KEY,
 social_security_number VARCHAR(11) UNIQUE,
 email VARCHAR(255) UNIQUE,
 phone_number VARCHAR(15) UNIQUE
);
```

A key that uniquely identifies a row in a table and is meaningful to the business. A key that is automatically generated by the database system. A key that is derived from the data and has real-world significance.A key that can be used interchangeably with a surrogate key.

A Business Key is a unique identifier that has a real-world meaning and is used within the business domain. In this case, 'social_security_number', 'email', and 'phone_number' can all serve as Business Keys.

Q22

Multiple Choice

What distinguishes a Natural Key from a Surrogate Key in PostgreSQL?

SQL Code

Consider a 'customers' table with columns 'customer_id', 'national_id', and 'email'. Implement the table using a Natural Key and a Surrogate Key, and explain the difference.

```sql
CREATE TABLE customers (
 customer_id SERIAL PRIMARY KEY,
 national_id VARCHAR(20) UNIQUE,
 email VARCHAR(255) UNIQUE
);
```

Natural Key has business meaning; Surrogate Key does not. Surrogate Key is automatically generated; Natural Key is derived from the data. Natural Key is always a composite key; Surrogate Key is always a single column.Both Natural and Surrogate Keys are interchangeable in PostgreSQL.

A Natural Key is a key with business meaning (e.g., 'national_id'), while a Surrogate Key is an automatically generated key with no business meaning (e.g., 'customer_id'). Both are used to uniquely identify records.

Q23

Multiple Choice

Which key can serve as both a Natural Key and a Candidate Key in PostgreSQL?

SQL Code

Given a 'products' table with columns 'product_id', 'product_code', and 'product_name', identify the Natural Key and the Candidate Key. Implement the constraints.

```sql
CREATE TABLE products (
 product_id SERIAL PRIMARY KEY,
 product_code VARCHAR(50) UNIQUE,
 product_name VARCHAR(255) NOT NULL
);
```

'product_code' can serve as both a Natural Key and a Candidate Key. 'product_name' can be a Candidate Key but not a Natural Key. A Candidate Key is always automatically generated by the system.Natural Keys cannot serve as Candidate Keys in PostgreSQL.

A Candidate Key is a column or set of columns that can uniquely identify a record. A Natural Key is a type of Candidate Key that is derived from the data itself and holds business meaning.

Q24

Multiple Choice

How can you enforce uniqueness on a Candidate Key in PostgreSQL?

SQL Code

Consider a 'departments' table with columns 'department_id', 'department_code', and 'department_name'. Enforce uniqueness on the Candidate Key using a unique constraint.

```sql
CREATE TABLE departments (
 department_id SERIAL PRIMARY KEY,
 department_code VARCHAR(10) UNIQUE,
 department_name VARCHAR(100)
);
```

By applying a unique constraint on the Candidate Key column. By declaring the column as PRIMARY KEY. By creating a composite key with the Candidate Key.By using an index on the Candidate Key column.

A Candidate Key can be enforced by using a unique constraint on the respective columns to ensure that no two rows have the same value for that key.

Q25

Multiple Choice

When would you choose a Surrogate Key over a Natural Key in PostgreSQL?

SQL Code

Consider a 'transactions' table with columns 'transaction_id', 'account_number', 'transaction_date', and 'amount'. Implement the table using a Surrogate Key and discuss when it is preferred over a Natural Key.

```sql
CREATE TABLE transactions (
 transaction_id SERIAL PRIMARY KEY,
 account_number VARCHAR(20),
 transaction_date DATE,
 amount NUMERIC(10, 2)
);
```

When the Natural Key is complex or prone to change. When the Natural Key is sensitive and needs confidentiality. When performance is a concern and the Natural Key is too large.When you need a simple, immutable identifier for the records.

A Surrogate Key is preferred when there is no stable Natural Key or when the Natural Key is complex, lengthy, or prone to change. It is also useful in cases where the Natural Key is sensitive or needs to be kept confidential.

Q26

Multiple Choice

Which scenario would benefit from using a Candidate Key in PostgreSQL?

SQL Code

Given a 'suppliers' table with columns 'supplier_id', 'supplier_code', and 'contact_email', enforce a Candidate Key on 'supplier_code' and explain its importance in the context.

```sql
CREATE TABLE suppliers (
 supplier_id SERIAL PRIMARY KEY,
 supplier_code VARCHAR(50) UNIQUE,
 contact_email VARCHAR(255)
);
```

To enforce business rules that require unique identifiers. To ensure data integrity in the database. To provide a fallback unique identifier in case the primary key fails.To simplify database queries by providing additional indexing options.

A Candidate Key is beneficial in scenarios where you need to ensure uniqueness across a set of columns that are meaningful within the business context. It helps maintain data integrity and enforces unique constraints that are crucial for business operations.

Q27

Multiple Choice

In what situations would you use a Natural Key as a Candidate Key in PostgreSQL?

SQL Code

Consider a 'library_books' table with columns 'book_id', 'isbn', and 'title'. Implement 'isbn' as a Natural Key and explain when it is suitable to use a Natural Key as a Candidate Key.

```sql
CREATE TABLE library_books (
 book_id SERIAL PRIMARY KEY,
 isbn VARCHAR(13) UNIQUE,
 title VARCHAR(255)
);
```

When the key is stable and unlikely to change. When the key has significant business meaning. When the key is already unique and well understood in the domain.When the key is complex and derived from multiple columns.

A Natural Key is suitable as a Candidate Key when it is stable, has business significance, and does not change over time. For example, 'isbn' in a library system is a natural and stable identifier for books.

Q28

Multiple Choice

What are the benefits of using a Business Key as a Natural Key in PostgreSQL?

SQL Code

Consider a 'employees' table with columns 'employee_id', 'email', and 'employee_number'. Implement 'employee_number' as a Business Key and discuss its benefits.

```sql
CREATE TABLE employees (
 employee_id SERIAL PRIMARY KEY,
 email VARCHAR(255) UNIQUE,
 employee_number VARCHAR(10) UNIQUE
);
```

Provides a meaningful and understandable identifier. Simplifies the data model by reducing the need for additional surrogate keys. Improves data integrity by ensuring that the key is naturally unique.Eliminates the need for composite keys in certain scenarios.

A Business Key that also serves as a Natural Key is advantageous because it is meaningful within the business domain, is easy to understand, and provides a direct link between the data model and the real-world business entities it represents.

Q29

Multiple Choice

How does the choice between a Surrogate Key and a Natural Key impact database design in PostgreSQL?

SQL Code

Given a 'orders' table with columns 'order_id', 'customer_id', and 'order_date', discuss the design implications of using 'order_id' as a Surrogate Key versus using a composite Natural Key consisting of 'customer_id' and 'order_date'. Implement both designs.

```sql
CREATE TABLE orders (
 order_id SERIAL PRIMARY KEY,
 customer_id INT,
 order_date DATE
);

-- Alternative design
CREATE TABLE orders (
 customer_id INT,
 order_date DATE,
 PRIMARY KEY (customer_id, order_date)
);
```

Surrogate Keys simplify schema design but lack business meaning. Natural Keys offer meaningful identifiers but can complicate schema design. Surrogate Keys are always preferred for performance reasons.Natural Keys are always better for maintaining data integrity.

The choice between a Surrogate Key and a Natural Key can impact the complexity of the database schema, query performance, and data integrity. Surrogate Keys provide simplicity and immutability, while Natural Keys offer meaningful, business-related identifiers but may complicate the schema.

Q30

Multiple Choice

What challenges arise when using a composite Natural Key in PostgreSQL?

SQL Code

Consider a 'student_courses' table with columns 'student_id', 'course_id', and 'enrollment_date'. Implement a composite Natural Key using 'student_id' and 'course_id'. Discuss the challenges of this approach.

```sql
CREATE TABLE student_courses (
 student_id INT,
 course_id INT,
 PRIMARY KEY (student_id, course_id)
);
```

Increased complexity in query writing. Potential performance impacts due to larger key sizes. Difficulty in managing foreign key relationships.Simplified schema design and easier data integrity enforcement.

Using a composite Natural Key can introduce challenges such as increased complexity in query writing, potential performance impacts due to larger key sizes, and difficulty in managing foreign key relationships in other tables.

PostgreSQL Database Quiz Questions