-1

I have a database for an investment firm:

  • B (broker)
  • O (office of broker)
  • I (investor)
  • S (stock)
  • Q (quantity of stock owned by investor)
  • D (dividend paid by stock)

Functional dependencies

  • S ⟶ D
  • I ⟶ B
  • IS ⟶ Q
  • B ⟶ O

I need to find minimal key for relation scheme R=BOSQID and need to prove it.

I have no idea how to solve this problem. Can you give me any idea?

Ben
  • 51,770
  • 36
  • 127
  • 149
Jay Lee
  • 29
  • 2
  • 5
  • 1
    This is an odd question. The relation schema shown is not in any of the higher normal forms (it's in 1NF, but not 2NF, let alone 3NF, BCNF, 4NF, 5NF, etc). Normally, you'd be expected to reduce the schema to multiple table schemas, each of which would have keys and foreign keys identified. In this case, the result would correspond to the functional dependencies. To make a minimal key for the given schema without the normalization, you have to find a key that functionally determines all the columns in the schema, remembering the trivial FDs and Armstrong's Axioms (Rules of Inference). – Jonathan Leffler Mar 20 '12 at 00:49
  • Are BOISQD tables within the database or columns within a single table? – xQbert Mar 20 '12 at 00:57
  • 1
    @xQbert: to make sense as a question, they have to be columns, do they not? – Jonathan Leffler Mar 20 '12 at 00:58
  • Just to be clear, you need to find some minimal set of columns {X, Y, ...} such that XY... ⟶ BOSQID. For example, BOSQID ⟶ BOSQID, but the LHS is not minimal. – Jonathan Leffler Mar 20 '12 at 01:00
  • @Jonathan Leffler: thank you. There is no given table, and column. I do not understand what you mean. can you explain a little bit more? – Jay Lee Mar 20 '12 at 01:04
  • @Jonathan Leffler I try not to assume, thus the question; but no I don't think they HAVE to be columns, they could be entities and we are assuming they are driven based on some Unique ID. So I asked the question. – xQbert Mar 20 '12 at 01:08
  • When you're given a relation scheme or schema, conceptually you have a single relation (table) with the attributes (columns) identified in the question. You have to find a minimal key for this relation. A super key is a combination of columns that determines every column in the relation. Therefore, for the columns BOSQID, the trivial superkey BOSQID ⟶ BOSQID holds, but this is not a minimal key unless you cannot delete any column from the LHS without losing the property that the LHS determines the RHS. Your task is to find a minimal set LHS that determines the RHS (hint: it isn't BOSQID). – Jonathan Leffler Mar 20 '12 at 01:27

1 Answers1

1

Jay, the way I understand this is the following. You need to find the minimal set of fields that would allow you to identify all fields BOSQID. There is an algorithm which I don't remember right now to properly do the analysis you're looking for, but the exercise seems to be simple enough in order not to need it.

  • Take B -> O. As B determines O we can keep B and remove O from the keys. Current possible key fields: BSQID.
  • Take I -> B. As I determines B we can keep I and remove B from the keys. Notice that, by transitivity, I determines O. Current possible key fields SQID
  • Take S -> D. As S determines D we can keep S and remove D from the keys. Current possible key fields SQI
  • Take IS -> Q. As IS determines Q we can keep IS and remove Q from the keys. Current possible key fields: IS

As we no longer have functional dependencies we can't go on, so the result is IS. There are more complex examples to work on in which this simple technique won't help you because it'll drive you crazy, that's why I recommend you too look for the algorithm to solve this.

Mosty Mostacho
  • 42,742
  • 16
  • 96
  • 123
  • Thank you for answer. I have another question. How many minimal keys does relation scheme R have? Is the answer BOSQID, BSQID, SQID, SQI, and IS? – Jay Lee Mar 20 '12 at 01:47
  • 1
    No, the minimal is only IS. Any of the others also determines all the other fields, but IS is the minimal (in the amount of fields it has) – Mosty Mostacho Mar 20 '12 at 01:50
  • I have another question. Suppose I decomposed relation R into the two relations R1 = ISQD and R2= IBO. Is this BCNF decomposition? is it a 3NF decomposition? – Jay Lee Mar 20 '12 at 05:23