628 views

I've some elementary doubts regarding Foreign keys (referential integrity) :

• Can a foreign key consist of a collection of attributes - ie. can two or more columns collectively form a foreign key?
• If foreign key refers to a column within the same table, then is it compulsory that the referred column must be UNIQUE constrained?
• Can the Primary key of a column be a foreign key referring to some other relation?

Doubt related to Third Normal Form:

Is it true that 3NF is inadequate for a relation that has either:

• Multiple candidate keys
• Composite candidate keys
• Overlapping candidate keys

If possible, please provide an example.

Thank You.!

1) Yes. FK can be a collection of attributes. Eg if you have done ER diag to Relational model conversions, you will come across several such egs. For eg when you represent a n-ary relationship in relational model, then the PKs of the n entity sets form the foreign key of the new table

2) Yes, in a recursive relationship also, the referred key must be unique, because after all , it is a primary key.

3) The foreign keys of a table usually constitute the primary keys of the owner table . So yes , this is also possible .
Yes. The foreign key can be composite meaning that two attributes can be a part of a primary key. Basically for a foreign key we basically refer to a particular tables primary key. I.e the parent table must have a primary key constraint or a unique constraint. And that constraint can be composite too which means on multiple attributes. Now generally if we have a table like

Products (Id, pname,pbrand,price ,quantity-avl)

Sales(Id,pname,pbrand,orderdate)

Or

Sales(id,product_id,orderdate)

Generally if we have a primary key constraint on multiple attributes we can have a unique constraint on an id attribute too. And it can be referred by a child table.

In the above case , if we have pname and pbrand in our sales table  referreing a parent table that is products having pname and pbrand as primary key we can create foreign key constraint on the Id attribute which is also a unique constraint attribute.

Yes if we have a table referring to itself in the foreign key then that referred attribute must be unique. This type of structure us used in hierarchical data like managerid attribute refers employee ID in the same table which is the primary key.

We can have a foreign key attributes also as primary key  only if we have a one to one relationship between the entities. If we have a many to one relationship then that child attribute will not be primary as multiple children may have same parent and it will not be a primary key in the child.
@Sayan @Tejasvi

Regarding the third point, yes! now, I remember it - while conversion of relationship sets as well as while constructing a new table for a multivalued attribute, the primary key of the new relation often refers to the parent entity sets. In those cases, the primary keys of the new relations act as the foreign keys as well. :)

Can you please provide an explanation for the doubt related to Third Normal form? I've memorized this as a fact, but I want to understand why this happens.

1
2
714 views
3
811 views