D426 Keys

A table tracking student course enrollments needs a primary key. A student can enroll in many courses, and a course can have many students. Which of the following would be the best choice for a primary key for the Enrollments table?

The StudentID column alone

The CourseID column alone

StudentID and CourseID

An EnrollmentDate column

StudentID and CourseID

This is a classic many-to-many relationship:

• One student → many courses

• One course → many students

So neither StudentID nor CourseID alone can uniquely identify a row.

Many-to-many relationships → use a composite primary key

PRIMARY KEY (StudentID, CourseID)

A HealthPlan table needs to link to a specific dependent of an employee. The Family table has a composite primary key of (EmployeeID, DependentNumber). What must the foreign key in the HealthPlan table be?

EmployeeID

DependentNumber

(EmployeeID, DependentNumber)

FamilyID

(EmployeeID, DependentNumber)

When a table has a composite primary key, any foreign key that references it must include all columns in that key.

FOREIGN KEY (EmployeeID, DependentNumber) REFERENCES Family(EmployeeID, DependentNumber);

A Primary Key A constraint enforces certain rules on a column or set of columns. Which two constraints does it combine to enforce these rules? 

FOREIGN KEY and CHECK 

NOT NULL and DEFAULT

UNIQUE and NOT NULL

UNIQUE and CHECK

UNIQUE and NOT NULL

When implementing a weak entity that depends on a strong parent, how is its primary key often formed?

A. A completely independent artificial key.

B. Composite key combining the parent's primary key and the weak-entity attribute.

C. A foreign key only (no primary key).

D. Using a TEXT column of combined values.

B. Composite key combining the parent's primary key and the weak-entity attribute. A weak entity is often identified by the parent's key plus its own distinguishing attribute so its primary key depends on the parent.

Which of the following is NOT a valid reason to choose an artificial (surrogate) key?

A. No natural unique column exists.

B. The natural key is large, unstable, or descriptive.

C. To reduce join performance.

D. To provide a simple, stable identifier.

C. To reduce join performance. Reducing join performance is not a reason to choose a surrogate key; the other options are valid reasons.

We can describe a link by observing that ____.

A. a primary key of one table appears again as a primary key in a related table
B. a foreign key of one table appears again as a foreign key in a related table
C. a primary key of one table appears again as a foreign key in a related table
D. a foreign key of one table appears again as a surrogate key in a related table

C. The reference of a foreign key in one table to the primary key in another links those tables together.

A Departments table has a ManagerID column that is a foreign key referencing the ID in the Employees table. What does this relationship imply?

Every employee must be a manager of a department

A department can exist without a manager

Every value in ManagerID must correspond to an existing employee ID

The ManagerID and ID columns must have the same name

Every value in ManagerID must correspond to an existing employee ID

A table Courses has a primary key CourseID. A table Prerequisites has a foreign key CourseID that references the Courses table. What must happen before the Courses table can be dropped? 

All data must be deleted from the Courses table 

The Courses table must be renamed first 

The Prerequisites table must be dropped first

The database must be taken oƯline

The Prerequisites table must be dropped first

A table that is referenced by a foreign key cannot be dropped while that relationship still exists.

Most databases wont let you drop a table that has a dependency. Where Prerequisites depends on Courses. So you can’t drop Courses because Prerequisites depends on it. You’d have to drop the Prerequisites table first.

What is a candidate key?

What is simple primary key vs stable primary key?

What is a subtype primary key vs supertype primary key?

What is an artificial (surrogate) key?

What is a superkey?

What is a composite key and composite primary key?