English
Menu

Database Basics

In an employee table (emp_no, email, name, dept), both emp_no and email are different for every employee and never NULL. Name and dept may be the same as those of other employees. Which lists all the candidate keys of this table?

  1. Aemp_no only
  2. Bemp_no, and email (each one a candidate key on its own)
  3. Cemp_no, and the combination of emp_no and name
Show answer and explanation

Answer: B. emp_no, and email (each one a candidate key on its own)

A candidate key is a minimal set of columns that can uniquely identify a row: removing even one column from it would make unique identification impossible. In this table both emp_no and email can uniquely identify an employee on their own, so both are candidate keys. One candidate key is chosen as the primary key (emp_no is the natural choice here), and the one not chosen, email, is called an alternate key. Giving the alternate key UNIQUE and NOT NULL constraints prevents duplicates. The combination of emp_no and name can also identify a row uniquely, but uniqueness is kept even without name, so it is not minimal and therefore not a candidate key. A set of columns that identifies rows uniquely but is not necessarily minimal is called a superkey.

  • A. Only one is chosen as the primary key, but there are two candidate keys, including email.
  • B. Correct. Each can uniquely identify a row on its own and is minimal, so both are candidate keys.
  • C. The combination of emp_no and name stays unique even without name, so it is not minimal and not a candidate key.
Practice this topic Back to topic

Related calculators

SQL PracticeRun SQL in your browser and check the results, with 10 practice exercises that grade your answers

Books on Database Basics Ad

As an Amazon Associate I earn from qualifying purchases.

More Database Basics questions

  1. An enrollment table (student_no, course_no, grade) records which student took which course and with what grade. One student takes several courses, and one course is taken by several students. If the same student never registers for the same course twice, which is an appropriate primary key for this table?
  2. The primary key of a customer table (cust_id, name) is cust_id, and cust_id in an orders table (order_id, cust_id) is defined as a foreign key referring to cust_id in the customer table. The customer table contains only the rows with cust_id 'C1' and 'C2'. What happens when INSERT INTO orders VALUES ('O9', 'C5') is executed in this state?
  3. SQL statements are divided into the data definition language (DDL), data manipulation language (DML) and data control language (DCL). Which one is classified as DDL?
  4. A product table has five rows of (name, price): (pen, 120), (notebook, 250), (eraser, 80), (file, 300), (ruler, 150). Which shows, in order, the rows returned by SELECT name FROM product WHERE price >= 150 ORDER BY price DESC?
  5. The dept column of an employee table contains, from the top row down, Sales, Dev, Sales, Admin, Dev, Sales (six rows in total). How many rows does SELECT DISTINCT dept FROM employee return?