Database Relations, Keys, and Referential Integrity

Question-and-answer flashcards covering attributes, primary keys, composite keys, foreign keys, multivalued attributes, relation properties, and referential integrity examples.

What are the attributes in the STUDENT relation?

StudentID, Name, Major, GPA

What are the rows (tuples) in the STUDENT relation?

(101, Alice, ComputerSci, 3.8), (102, Bob, Math, 3.5), (103, Charlie, Physics, 3.9)

Which attribute uniquely identifies each row in the STUDENT relation?

StudentID

Which attribute should be chosen as the primary key for STUDENT?

StudentID

In COURSE_ENROLLMENT, which attribute set uniquely identifies each row?

{StudentID, CourseID}

In COURSE_ENROLLMENT, which attribute set is NOT a candidate for a composite key?

{StudentID, EnrollmentDate}

Why is the combination of StudentID and CourseID considered a composite key in COURSE_ENROLLMENT?

Because the combination uniquely identifies each enrollment record

Which attribute in COURSE_ENROLLMENT is a foreign key referencing STUDENT?

StudentID

Which attribute in STUDENT is referenced by the foreign key in COURSE_ENROLLMENT?

StudentID

Is the table with duplicate StudentID rows a valid relation? Why or why not?

No, because the rows are not unique

What property of relations is violated when duplicate rows exist?

Each row must be unique

In the employee-training table, which attribute is multivalued?

CourseTitle

After identifying a multivalued attribute, what is the next step to create a valid relation?

Ensure each row contains a single value for each attribute

Is the employee-training table already a valid relation?

Yes

What is the primary key of the BOOK relation?

BookID

What is the primary key of the PUBLISHER relation?

PublisherID

What is the primary key of the CUSTOMER relation?

CustomerID

What is the primary key of the PURCHASE relation?

PurchaseID

Which attribute in BOOK is a foreign key referencing PUBLISHER?

PublisherID

In PURCHASE, which attribute is a foreign key referencing CUSTOMER?

CustomerID

In PURCHASE, which attribute is a foreign key referencing BOOK?

BookID

What is the primary key of STUDENT(StudentID, Name, MajorID)?

StudentID

What is the primary key of MAJOR(MajorID, MajorName)?

MajorID

What is the primary key of COURSE(CourseID, CourseName, MajorID)?

CourseID

What is the primary key of ENROLLMENT(StudentID, CourseID, Grade)?

Composite key (StudentID, CourseID)

Which attribute is a foreign key in STUDENT(StudentID, Name, MajorID)?

MajorID

Which attribute is a foreign key in COURSE(CourseID, CourseName, MajorID)?

MajorID

Why is a composite key used in a relation?

Because a single attribute is not sufficient to uniquely identify a record

Which OrderID has a CustomerID that does not exist in CUSTOMER?

OrderID 103

Which ORDER LINE row is valid (example 1)?

(101, 202, 1)

Which ORDER LINE row is valid (example 2)?

(102, 203, 1)

Which ORDER LINE row is valid (example 3)?

(103, 201, 5)

Which ORDER LINE row is valid (example 4)?

(106, 202, 2)