Respuesta :
Answer / Explanation:
To answer the question, we first write a program that profiles the details required of the presidents:
thus, we have:
Table President Table Origin
PK presID PK or ID
pres First Name or City
pres Last Name or State
affiliation birth Date
gender
term
However, if we take a good look at the question properly,
A president can serve many terms, but a term can only be served by one president.
A president has one Origin.
Multiple presidents may be from the same place
"Multiple presidents may be from the same place" => while technically true, the nature of the data is such that storing it with the president's details is highly defendable.
Affiliations & terms should be on different tables.
For terms spent, a doubly linked list could be possible. A simple sequence of terms (search for term_id -1 & term_id +1) could also help.
Moving forward, we need to know that in order to link the presidents to their origins, we will need to recall the Pres ID as indicated above but as a foreign key in Table Origin
Also, it is true that we may be better served creating a tbl_term because there are many presidents with more than one term. However, The above example would require having multiple president entries for multiple terms.
Therefore,
tbl_term : PK term_id : FK pres_id : term
To retrieve predecessors/successors , we have ,
DECLARE @maxTerm INT
DECLARE @minTerm INT
SELECT @maxTerm = MAX(term_id), @minTerm = MIN(term_id) FROM tbl_term AS t
JOIN tbl_pres AS p
ON p.pres_id = t.pres_id
WHERE p.presLName = 'lname'
AND p.presFName = 'fname'
Then predecessor is
SELECT p.presLName, p.presFName FROM tbl_pres AS p
JOIN tbl_term AS t
ON p.pres_id = t.pres_id
WHERE t.term_id = @minTerm - 1
Vice versa
SELECT p.presLName, p.presFName FROM tbl_pres AS p
JOIN tbl_term AS t
ON p.pres_id = t.pres_id
WHERE t.term_id = @maxTerm + 1
