Ch 10: Working with Multiple Tables
Relationships between Tables
Prerequisite: there must be a common field between two tables, and its data type must be the same in both.
Master & Transaction tables: the table with the primary key is the Master table; the related table with the foreign key is the Transaction table.
Three Types of Relationships
| Type | Example |
|---|---|
| One-to-One | Person and passport. |
| One-to-Many | Teacher and students. |
| Many-to-Many | Products and customers. |
Create: Tools > Relationships → add both tables → drag the common field from one onto the other.
Referential Integrity
Referential integrity: no unmatched foreign key values should exist. It is maintained by the DBMS. Options: No action, Update cascade, Set NULL, Set default.
Summary
- Relationships need a common field of the same data type.
- Types: one-to-one, one-to-many, many-to-many.
- Referential integrity is maintained by the DBMS.
Tables के बीच Relationships
शर्त: दो tables के बीच एक common field होना चाहिए, और उसका data type दोनों में एक जैसा हो।
Master & Transaction tables: primary key वाली table Master है; foreign key वाली related table Transaction है।
तीन प्रकार के Relationships
| Type | उदाहरण |
|---|---|
| One-to-One | व्यक्ति और passport। |
| One-to-Many | शिक्षक और छात्र। |
| Many-to-Many | Products और customers। |
बनाएं: Tools > Relationships → दोनों tables जोड़ें → common field एक से दूसरी पर drag करें।
Referential Integrity
Referential integrity: कोई unmatched foreign key value न हो। इसे DBMS बनाए रखता है। Options: No action, Update cascade, Set NULL, Set default।
सारांश
- Relationships के लिए same data type का common field चाहिए।
- Types: one-to-one, one-to-many, many-to-many।
- Referential integrity DBMS बनाए रखता है।
Frequently Asked Questions
What is the prerequisite for relating two tables?
There must be a common field between the two tables, and its data type must be the same in both.
Who maintains referential integrity?
Referential integrity is maintained by the DBMS.