Key field

[Trish]

Hi, I am preparing for an exam and am working through some past exam papers.
There is a questions that says "show how a key field is used in a relational
database across three (3) database tables". I am assuming that the key field
they refer to is the primary key, but how does it go across 3 tables? I hope
someone can shed some light on this for me, in case a similar question comes
up Many thanks.

 

[Pete D.]

As nice as I can say it...Read the Homework!!!! Might also read about
relational database using google as the search engine. Many to Many

 

[Ken Sheridan]

Trish:

Keys can be foreign as well as primary. In a one-to-many relationship the
foreign key in the referencing (many-side) table references the primary key
of the referenced (one-side) table. The referencing table may itself be
referenced by another table, e.g.

Cities references Regions on RegionID; Regions references Countries on
CountryID.

In the above the values of RegionID and CountryID will obviously differ
across three matching rows, but you could have a situation where the value is
the same, e.g.

Sales references SalesPeople on EmployeeID; SalesPeople references Employees
on EmployeeID.

In this example the relationship between SalesPeople and Employees is
one-to-one because SalesPeople is a sub-type of Employees and would only
contain columns specific to sales staff not all employees, with the columns
common to all such as names being in Employees. So the EmployeeID column is
a foreign key of Sales, both a primary key and a foreign key of SalesPeople,
and the primary key of Employees.

Ken Sheridan
Stafford, England

 

[Steve Schapel]

Trish,

In addition to Ken's excellent advice, it is also possible (and common)
for a table to be in a one-to-many relationship with two or more other
tables.

 

[Ken Sheridan]

Steve, do you think we should tell her about decomposition into three tables
to achieve Fifth Normal Form?

Only joking, Trish. That really would frighten you. It frightens me!

Ken Sheridan
Stafford, England

 

[Larry Linson]

Steve, do you think we should tell her about
decomposition into three tables
to achieve Fifth Normal Form?

Decomposition? Your tables are _rotting_, Ken? That is a frightening
prospect.

For several versions, I know we had The Amazing Biodegradable Forms Feature,
but that was Forms that rotted, not tables... <GRIN>

Larry

 

[Ken Sheridan]

Sounds nasty I know, but blame Codd and Date, not me. As far as I know its
been the 'technical' term used since before Doris day was a virgin.

Ken Sheridan
Stafford, England

 

[Trish]

Thanks so much to all concerned for the helpful advice. I learned a lot from
it. As it happened, such a question did not arise in the exam Many
thanks again.