-1

I'm very confused when it comes to the topic of cardinality in Relational Algebra. I understand that cardinality essentially refers to the uniqueness of a table or data set. So I'll walk through a problem I attempted to solve and maybe someone can help me out, or give me better resources than the ones I've found.

I've got a table R2, with attributes D, E, and F, where D is a Primary Key, and E and F are Foreign Keys relating to the Primary Keys of the following table. Table R3, with attributes G, H, and I, where G and H are PKs. R2 has cardinality N2 = 100, R3 has cardinality N3 = 200. So what would the min and max cardinality be of a table created by joining R2 to R3 with the condition that E = G and F = H?

My answer is that the minimum is 1, and max is 200, or N3. My thought process is that E and F are FKs, so they can have many repeating values so long as they come from G and H, but since G and H are PKs, at least one value for E and F would be unique, and D is a PK as well, so at least one value is unique there too. So I assume those unique values mean the cardinality must be at least 1, and at most, it can have the same cardinality as R3, which is 200. But honestly, my own reasoning doesn't even make sense to me...

The whole idea seems really abstract to me. Attribute I is the only non FK/PK in the problem, so how does that affect the cardinality? Sorry for the long winded question, I'm just very confused by the whole idea of this and would love any help in general regarding the subject.

SaucedCaveman
  • 41
  • 2
  • 9

1 Answers1

0

You are not equijoining FK-to-CK (foreign key to candidate key). You are equijoining on EF subtuples matching GH subtuples. Although every E has a G & every F has an H, there does not have to be a single EF-GH match. G & H are unique so GH is unique so each EF can match at most one, so there could be 0 to 100 rows in the result.

(If you want to make sound analyses you need to find the minimum & maximum results for various cases of kinds of joins on column sets referencing (having to appear elsewhere as) others. You can handle more cases by dealing with superkeys (unique column sets) not CKs (candidate keys) (superkeys containing no smaller superkeys). You mean CK when you say "PK" (primary key)--there can be at most one PK per table. For no duplicates or nulls, SQL UNIQUE is superkey & FOREIGN KEY is foreign superkey.

philipxy
  • 14,867
  • 6
  • 39
  • 83