foriegn and primary keys

[Tech]

hi there. i hope you can help me in a nice easy english language hehe.

I am on a question in my coursework which says:

"Identify all primary and foreign Key fields"

now - i have made tables (on paper - there is no real database access here) and i have identified hopefully nearly all the appropriate fields

www.technosoft2000.co.uk/table.txt

that is my table and fields i have identified from coursetext.

now i am wondering - have i done this right? i know this question maybe stupid but i have no other way of asking if i have done it correctly....



i am wondering - have i uniquely identified the primary key fields?
what about foreign keys? I know i havent done this yet but why and how would i use a foriegn key in this situation?

the Usage table is a table that joins 2 tables together




i would also like to know if i need to put in the fields from other tables into other tables like for example :

a supervisor will be monitoring a manufacturing run - so in the manufacturing run would i have the supervisor No. field in there as a foriegn key or am i wayy wrong?


i hope you can explain this - i am really confused

Many thanks

 

[Pat Hartman]

In the usage table Manufacturing Run No. and Gas Container No. are foreign keys because they provide the "linking" value to another table.

Observations:
Rename all your tables and columns to remove the embedded spaces and special characters. Your names should only contain upper and lower case letters, numbers, and if you need a separator, you can use the underscore. Keep the length to no more than 30 characters. All the spaces and special characters will eventually cause you problems when you get to writing VBA or should you ever have to convert the db to a different back end RDBMS such as Oracle or DB2.

Also, it is best to use the same name as a foreign key as you used as the primary key name- so Manufacturing Run No. and MF. Run No. should both be MFRunNum. Don't spell out long words when there is a commonly accepted abbreviation and once you use an abbreviation, stick with it. Don't vacillate.

 

[Tech]

thanks

this stuff will never be put in a database at all - its not required to but i understand what you are saying

thanks

and anything else to be fixed/changed?

have i answered the question properly?

"identify all primary and foreign key fields"

 

[Tech]

some questions arose.

if a Product has a Produce number and a plant number - would plant number be a foriegn key? is there any need for a foreign key?

each product is identified by the primary key (prod no)

am i correct at putting in the plant no?


also - i have the entity diagram drawn out with its cardinality

most of them are 1 to many apart from usage - thats a joining table.

would this mean that for each entity - there must be a field from its joining entity and must be given a foriegn key?

 

[Pat Hartman]

Primary key is the name for the column that you have defined as uniquely identifying a row in a table. When the value from a primary key in one table is placed in a column in another table AND you identify a relation between the two columns in the relationship window, the column in the second table is referred to as a "Foreign Key". Foreign in the sense that it refers to a column in a separate table.

Technically something such as Plant No. implies (to me at least) that there is some table that uniquely identifies Plants and this is a Foreign Key to that table. I don't recall if you had a Plant table in your schema but if you didn't, you should to be complete.

Relationships are defined for one purpose and one purpose only; that being to enforce referential integrity. When you declare a relationship between the Plant No field in the Product table and the Plant No field in the Plant table, in Access you check the enforce RI box to turn on declarative RI (RI enforced by the database engine - Jet). In SQL server you specify whether RI should be declarative (enforced by the SQL db engine) or controlled programatically (enforced by triggers that you write yourself). You would use triggers if you had some special business rules that you needed to enforce. When RI is enforced, Jet will not allow you to delete a Plant No record from the Plant table if there are any records in the Product table that reference that Plant. Nor would it allow you to add a record to the Product table with a value in the Plant No field that did not already exist in the Plant table. Cascade Delete and Cascade Update (not supported by all RDMBS') change the rules for how RI works.

 

[Tech]

hmm

sorry but i dont quite understand

firstly - we are not going to put this in physically in a database at all - its all paper

lets say that we have 2 entities:


Plant
Supervisor


Plant has:

Plant no.
Plant Name

Supervisor has:

Supervisor no.
Supervisor Name.
Supervisor address




now - i have done an entity diagram on this. its a 1 to many cardinality.

when i sit down on paper and write the table fields etc...

when i come to write the plant table - do i include the supervisor no. field as a foriegn key in the plant table?

do i put in the Plant no. in the supervisor table as a foriegn key?

i would just like to confirm this - i need to know if i am thinking on the correct lines

its because all that this question says:

"b) List all entity attributes both stated and assumed"

??


i appreciate your help

 

[Pat Hartman]

If a plant can have only 1 supervisor but a supervisor can supervise many plants, the SupervisorID is placed in the Plant table.

If a Supervisor can supervise only 1 plant but a plant may be supervised by many supervisors, the PlantID is placed in the Supervisor table.

 

[Tech]

www.melcfanbook.co.uk/entitydiagrammynewapproach.jpg
www.melcfanbook.co.uk/coursetext.txt



i have some questions..i hope u dont mind.

where i have placed the questions marks in that diagram - should the lines be there even tho the coursetext doesnt say any relation between them?


when u think about it - 1 plant can have MANY Manufacturing Runs

1 Product is made up of MANY components


??



if the above is true then does that mean in the Component and Product relationship - BOTH tables would have each other primary keys as foreign keys?



Product:

Product_no (PRI)
Product_name
Component_No (FOR)

Component:

Component_No (PRI)
Component_Name
Product_no (FOR)


like that? or would only one of the tables have it?

i am really confused on this

 

[Pat Hartman]

Product:

Product_no (PRI)
Product_name
Component_No (FOR)

Component:

Component_No (PRI)
Component_Name
Product_no (FOR)

- This relationship could be thought of as pathological. You can't enter a product record because it requires a component record to already exist in the component table and you can't enter a component because it requires a product record to exist in the project table.

The relationship between plant and manufacturing run is redundant since the relationship is defined via supervisor.

As far as the other question goes, I would guess that the relaitonship between component and mf run is the redundant one rather than the relationship between product and component but without a complete understanding of your business rules I can't be sure.

 

[Tech]

so there shouldnt be a relationship between Plant and MF Run entities?

same for Product and component?


www.melcfanbook.co.uk/coursetext.txt is the spec - im not saying at all in anyway for you to give me the answer - i just need to know if i am correct and if im wrong - what is wrong

many thanks

 

[Pat Hartman]

Please make sure that I get credit for your coursework:

tblPlant
PlantID (pk)

tblProduct
ProductID (pk)
PlantID (fk to tblPlant)

tblSupervisor
SupervisorID (pk)
PlantID (fk to tblPlant)

tblMFRun
MFRunID (pk)
SupervisorID (fk to tblSupervisor)
ProductID (fk to tblProduct)

tblComponent
ComponentID (pk)

tblContainer
ContainerID (pk)
ComponentID (fk to tblComponent)

The spec says nothing about the relationship between products and components. You can infer a many-to-many relationship between product and component since that is “normal”. This relationship would add a relation table.

tblProductComponent
ProductID (pk field1, fk to tblProduct)
ComponentID (pk field2, fk to tblComponent)
ComponentQty

The last paragraph in the spec is a red herring. It does not add any additional information and lead you to believe that there should be a direct relationship between the MFRun and Containers. There should not! The relationship passes through the Product table.