Design a database to store details about U. S. presidents and their terms in office. Also, record details of their date and place of birth, gender, and political party affiliation (e. g., Caluthumpian Progress Party). You are required to record the sequence of presidents so the predecessor and successor of any president can be identified. How will you model the case of Grover Cleveland who served nonconsecutive terms as president? Is it feasible that political party affiliation may change? If so, how will you handle it?

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