Your company decides to start the business of selling toys online and shipping globally. The E-Commerce system that supports the new business will be developed in-house. The development decides to use the relational database as the data persistence solution for transactions. One team member is tasked to design the database. Which of the following is least likely to happen?
A. Assign one and only one primary key in each table
B. Keep the attributes related to the primary key and move unrelated ones to other tables
C. Use multiple attributes as the primary key in a relation
D. Avoid foreign key references the primary key in the master table to enforce integrity
Kindly be reminded that the suggested answer is for your reference only. It doesn’t matter whether you have the right or wrong answer. What really matters is your reasoning process and justifications.
My suggested answer is D. Avoid foreign key references the primary key in the master table to enforce integrity.
- Relation: a table
- Tuple: a record
- Attribute: a field
- Relationship: Relation and relationship are different things. A relationship is an association between the primary key of a table (master) and the foreign key of a table (details). The foreign key of the details table must reference the primary key of the master table to enforce integrity.
- Key: the attribute or attributes in a relation/table used to uniquely identify a tuple/record.
- Primary Key: the attribute or attributes in a relation/table used to uniquely identify a tuple/record.
- Candidate key: any key that can serve as the primary key.
- Foreign Key: the duplicate attribute(s) of the primary key in another table, used to establish the relationship between them.
- Composite Key: two or more attributes are combined to serve as a key, primary or foreign key.
- A table has one and only one primary key to uniquely identify a tuple/record.
- Attributes in a relation should depend on the primary key. If not, it’s common to move them out to another relation or table.
- When designing tables, given a many-to-many relationship between two relations/tables, it’s common to create a 3rd table in which the primary key is composed of the primary keys from the two tables.
- The foreign key must reference the primary key in the master table to enforce integrity.