DBMS Tutorial – Patient Table Keys and Atomicity
Consider the Patient table with the following attributes:
| Attribute | Data Type | Constraint |
|---|---|---|
| patient_id | INT | PRIMARY KEY |
| Name | VARCHAR(50) | NOT NULL |
| Age | INT | CHECK > 0 |
| Gender | VARCHAR(10) | NOT NULL |
| VARCHAR(70) | NULLABLE | |
| City | VARCHAR(40) | NOT NULL |
Task i: Keys Analysis
Answer:
1. Super Keys
A super key is any set of attributes that uniquely identifies a record. Examples in the Patient table include:
{patient_id}, {patient_id, Name}, {patient_id, Email}, {patient_id, Name, Age, Gender}.
2. Candidate Keys
A candidate key is a minimal super key. Examples:
- {patient_id} – minimal and unique
- {Email} – if emails are enforced as unique
3. Primary Key
The primary key is the chosen candidate key to uniquely identify records: patient_id.
4. Alternate Keys
Alternate keys are candidate keys not chosen as primary: Email (if unique) is an alternate key.
Role of Keys in Data Integrity:
- Ensures each row is uniquely identifiable.
- Prevents duplicate records.
- Maintains referential integrity when linked to other tables.
- Reduces redundancy and improves data consistency.
Task ii: Atomicity of Attribute Values
Answer:
1. Meaning of Atomic Attribute Values
Atomicity means each attribute contains indivisible values. For example, storing a full name "Arifa Laboni" in a single field is not atomic. Proper atomic design would separate it into First_Name = "Arifa" and Last_Name = "Laboni".
2. Impact on Database Design and Data Retrieval
- Data Retrieval Efficiency: Queries can filter, sort, and index individual values easily.
- Supports Normalization: Atomic values reduce redundancy and prevent anomalies.
- Maintains Data Integrity: Updates, insertions, and deletions are consistent and safe.
- Scalability and Flexibility: Easier to join tables, perform calculations, and maintain relational design.
কোন মন্তব্য নেই:
একটি মন্তব্য পোস্ট করুন