Primary Key, Foreign Key, and Super Key in Doctor-Patient Schema
Using the schema below, we identify primary keys, foreign keys, and super keys with proper definitions.
Schema
- Doctor(doctor_id, doctor_name, specialization, room_no)
- Patient(patient_id, patient_name, age, doctor_id)
1. Primary Key (PK)
Definition: A primary key is an attribute (or a combination of attributes) that uniquely identifies each record in a table. It cannot contain NULL values and must be unique.
- Doctor:
doctor_id - Patient:
patient_id
2. Foreign Key (FK)
Definition: A foreign key is an attribute in one table that references the primary key of another table. It establishes a relationship between the two tables.
- Patient.doctor_id → references Doctor.doctor_id
3. Super Key
Definition: A super key is an attribute (or a set of attributes) that uniquely identifies a tuple in a relation. A primary key is a minimal super key.
Doctor Table Super Keys:
- {doctor_id} → minimal, also primary key
- {doctor_id, doctor_name}
- {doctor_id, specialization}
Patient Table Super Keys:
- {patient_id} → minimal, primary key
- {patient_id, patient_name}
- {patient_id, age}
Summary Table
| Relation | Primary Key | Foreign Key | Examples of Super Keys |
|---|---|---|---|
| Doctor | doctor_id | None | {doctor_id}, {doctor_id, doctor_name} |
| Patient | patient_id | doctor_id → Doctor | {patient_id}, {patient_id, age} |
কোন মন্তব্য নেই:
একটি মন্তব্য পোস্ট করুন