Help with composite and Foreign Keys

[HB25]

Dear Friends
I have finished the Data dictionary table for my assignment but I cannot figure out my (Composite and Foreign) Keys. I am attaching my physical data dictionary table with this massage. I would be very grateful if someone could identify them for me and update the table for me.
Thanks for your help in advance.
Regards
HB25

[tesuji]

Hi friend,

I am viewing your data dictionary and erm. Well, something seems to be wrong: some trouble with keys, composite keys, also the graphic of the erm emerges in a disordered manner. The relationships between orders and items and items and products, respectively should be redrawn. Maybe the disordered graphical elements are an result of my open office doc viewer (I don't use MS but Linux). I also suggest to post pdf files only (you may use freepdf to convert ms doc into pdf).

Btw, when is deadline, when should you hand in your results?

Ok, let's start to emend your design. There is a general rule to get the right foreign keys (and often the (composite) primary key): In an one-to-many relationship, the primary key of the entity from the one-side is moved into the entity what is located on the many-side, e.g. Between orders and items exists a one-to-many relationship, that is, orders is on the one-side and items on the many-side. Therefore, the primary key of orders must move into items, and there it becomes (at first) a foreign key.
Between products and items also exists an one-to-many relationship. Again, the primary key of products has to be moved into items. There it becomes a foreign key. Now that items has got two new attributes, finally we have to determine the primary key of items.

Do the move-in attributes order_ID and product_ID uniquely identify items ( you should keep in mind that the "entity" items is derivated from the many-to-many relationship between orders and products. Items is a such-called linking entity)? Yes, both attributes uniquely identify items. Therefore order_ID and product_ID form the COMPOSITE primary key of items. In items they are both: foreign keys and primary key (if you use a tool for designing the erm, for example DBDesigner, there such special attributes are attributed with PFK (=primary&foreign key). Further RESULT: cust_ID is neither foreign key nor part of items in your data dictionary. Therefore it MUST be removed from items.

Finally, items consists of order_ID, product_ID and qty only, where order_id and product_id form your sought-after composite primary key.

As for both foreign keys stock_ID and product_ID of entity stock, these both attributes make up the composite primary key of stock. They both moved into stock by the same rule described just above. All other entities only have single surrogate primary keys.

Your data dictionary should have two columns for keys: one for primary keys and one for foreign keys.

well, above has been developing into a somehow prosy text. Finally, i ve to appologize for my patchy bad English.

krs,
cliff

[HB25]

Dear tesuji
Thank you very much for your useful comments it did solve my problem. Once again thank you and keep up the good work.
Regards