Relational databases: a key and a relationship
| English | 中文 | Pinyin |
|---|---|---|
| primary key/ˈpraɪməri kiː/ | 主键 | zhǔ jiàn |
| foreign key/ˈfɒrən kiː/ | 外键 | wài jiàn |
What would explain this observation?
- Duplicating a user address in every order can produce conflicting records when the address changes.
- Start with a prediction. State the quantities or features you would compare, then decide what evidence could distinguish two explanations.
Build the model
- A relational table contains rows and attributes. A primary key 主键 identifies a row; a foreign key 外键 links to a referenced key in another table. Normalization can reduce avoidable duplication and update anomalies while preserving meaningful relationships.
- primary key: Attribute set uniquely identifying a table row; foreign key: Attribute set referencing a key in another table.
Which design helps avoid duplicated customer-address updates?
A join combines rows according to a specified condition. The result count depends on relationship cardinality and filters. A foreign key constraint enforces a relationship rule; it does not automatically encrypt personal data.
Match each technical term to its precise meaning.
Use the definitions to distinguish related quantities and processes.
Choose evidence that can test it
- A join combines rows according to a specified condition. The result count depends on relationship cardinality and filters. A foreign key constraint enforces a relationship rule; it does not automatically encrypt personal data.
- Design a small synthetic user-order dataset with no real personal information. Declare keys, test duplicate and missing-reference inserts, then check a query result against a hand-worked expected table.
Which two habits make the investigation or model in this case more defensible?
Design a small synthetic user-order dataset with no real personal information. Declare keys, test duplicate and missing-reference inserts, then check a query result against a hand-worked expected table.
Work from known quantities
- State the known values and their units. Choose the relation because its assumptions fit this case, then rearrange before substitution.
- Known: four customers have respectively 2, 0, 3 and 1 orders. An inner join of customer to order returns 2+0+3+1 = 6 rows. A left join also retains the customer with no order as one null-matched row, giving 7 here.
Three customers have 1, 3 and 2 orders. How many rows does the customer-order inner join return? Use the same sequence: known quantities → model → relation → substitution → unit and interpretation.
Three customers have 1, 3 and 2 orders. How many rows does the customer-order inner join return?
The result is 6 rows. Known: four customers have respectively 2, 0, 3 and 1 orders. An inner join of customer to order returns 2+0+3+1 = 6 rows. A left join also retains the customer with no order as one null-matched row, giving 7 here.
Check the conclusion and its limits
- A primary key need not be a person name. A join is not a simple concatenation of tables. Use parameterized queries for untrusted input rather than building SQL from raw strings.
- Return to the original observation. Explain what the result supports, which conditions it assumes, and one way to test a competing explanation.
A foreign key automatically encrypts every row. This claim is false: A primary key need not be a person name. A join is not a simple concatenation of tables. Use parameterized queries for untrusted input rather than building SQL from raw strings.
Relational databases: a key and a relationship: A join combines rows according to a specified condition. The result count depends on relationship cardinality and filters. A foreign key constraint enforces a relationship rule; it does not automatically encrypt personal data.
A foreign key automatically encrypts every row.
A primary key need not be a person name. A join is not a simple concatenation of tables. Use parameterized queries for untrusted input rather than building SQL from raw strings.
Attribute set uniquely identifying a table row: write the technical term.
primary key means Attribute set uniquely identifying a table row.