Design a database to store details about US presidents and t

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? In populating your solution include the following information (note this is not the format you will store it in the database):

Chester

Arthur

Born 10/05/1829

Whig until 01/01/1854

Republican after that

21^st president

Grover

Cleveland

Born 03/18/1837

Democrat his entire career

22^nd President

24^th President

Benjamin

Harrison

Born 08/20/1833

Whig until 01/01/1856

Republican after that

23^rd President

Chester	Arthur	Born 10/05/1829	Whig until 01/01/1854	Republican after that	21st president
Grover	Cleveland	Born 03/18/1837	Democrat his entire career	22nd President	24th President
Benjamin	Harrison	Born 08/20/1833	Whig until 01/01/1856	Republican after that	23rd President

Solution

Create table President(

PresidentID integer(6) primary key,

Name varchar (30)

Place varchar (20),

Dateofbirth date,

Gender varchar(1) check in (m,f),

Ruling_startdate date,

Ruling_end date date,

Affiliation_party varchar(20),

Terms varchar(30));

We will design all the attributes under one table or define separate table for president personal details and party details.

We can separate the tables now.

President table:

Create table president(

PresidentId integer(6) primary key,

Name varchar(30) foreign key,

Dateofbirth date,

Gender varchar(1) check in (m,f)

Affiliation_party varchar(20),

Terms varchar(20));

Create table pres_det(

presidentID integer(6) primarykey,

Place_of_elect varchar(20),

Birthdate date,

Ruling_startdate date,

Ruling_enddate date,

);

CREATE SEQUENCE seq_presidentID
MINVALUE 10000
START WITH 10000
INCREMENT BY 100
Cycle.

If we execute this sequence president id is set to default value and increment by 100 for every president value.

DECLARE @maxTerm INT
DECLARE @minTerm INT

SELECT @maxTerm = MAX(term_id), @minTerm = MIN(term_id) FROM President AS p

JOIN President AS p
ON p. presidentId = pres_det.presidentId
WHERE p.Name = \'name\'

Then predecessor is

SELECT p.Name FROM pres_det AS t
JOIN President AS p
ON p.presidentId = t.presidentId
WHERE p.term = @minTerm - 1

To find the successor is

SELECT p.Name FROM pres_det AS t
JOIN President AS p
ON p.presidentId = t.presidentId
WHERE p.term = @maxTerm +1