After transforming the above ERD to a Relational Schema, what is the primary Key for patient treatment?

Question

BlackTom AI · Accepted Answer

An associative relationship that records a specific treatment event must use the foreign keys of the participating entities plus the event attributes that distinguish multiple occurrences. Therefore the PATIENT TREATMENT relation’s primary key is the composite of PatientID, PhysicianID, TreatmentCode, Date, Time.

1) Relevant concepts
- Many-to-many relationships and associative (intersection) entities: when a relationship between entities has its own attributes (Results, Date, Time), it becomes a separate relation.
- Relational schema mapping rule: the primary key of an associative relation is the combination (union) of the primary keys of the participating entity relations; include any additional relationship attributes needed to uniquely identify each instance.

2) Step-by-step reasoning
- Identify participating entity keys: PATIENT → PatientID; PHYSICIAN → PhysicianID; TREATMENT → TreatmentCode.
- PATIENT TREATMENT is an associative entity (it has attributes Date, Time, Results), so it becomes a separate relation that must reference the three FK attributes.
- To uniquely identify a particular treatment event (same patient may see same physician for the same treatment multiple times), Date and Time are needed in addition to the three FKs.
- Therefore the minimal unique identifier is the five-attribute composite: PatientID + PhysicianID + TreatmentCode + Date + Time.

3) Intermediate “calculation” steps (uniqueness reasoning)
- PatientID + PhysicianID + TreatmentCode alone could still repeat on different dates/times → not unique.
- Date + Time alone do not identify which patient/which treatment → not unique.
- A surrogate PatientTreatmentID could serve as a PK, but none is shown in the ERD; mapping rules favor the natural composite here.

4) Why the correct answer is correct
- It guarantees uniqueness for each recorded treatment event and follows mapping rules for associative entities with attributes.

5) Why other options are incorrect
- "Date, time": not unique across patients/treatments.
- "PatientID, PhysicianID, TreatmentCode": can repeat across separate visits/events.
- "PatientTreatmentID": not present in ERD (surrogate), so not the canonical mapping.

Conclusion: The primary key is PatientID, PhysicianID, TreatmentCode, Date, Time, matching the provided answer.

After transforming the above ERD to a Relational Schema, what is the primary Key for patient treatment?单项选择题

类似问题

The table is the basic building block in database design. An attribute (or column) that uniquely identifies a particular object (or row) in the table is called:

If order_id is declared as a PRIMARY KEY, which of the following are guaranteed? Choose all that apply.

什么是主键？

Inspect the following conceptual model Identify the primary key of the Store entity

Each table in a relational database has one field designated as its primary:

Which of the following best describes the purpose of a primary key?

In a relational database, which of the following about Primary Key is FALSE

A primary unique key:

更多留学生实用工具

智能学习助手

风格化写作助手

论文查重助手

文献引用助手

课堂转译助手

课堂笔记助手

Quiz搜索助手

学校历年真题

智能刷题助手

智能匹配练习

希望你的学习变得更简单