Design a database to store details about U.S. presidents and their terms in offi

Question

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

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

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

Explanation / Answer

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

Related Questions

Navigate

• Browse (All)
• Browse D
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.