database design help needed

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. You are required to record the sequence of presidents so that the predecessor and successor of any president can be identified. Remember Grover Cleveland served two-nonconsecutive terms and that political affiliations can change.

This is what I have so far let me know if this can be improved please. also how can I do this "predecessor and successor of any president can be identified" is this done using a recursive relationship and how would I execute this?

Table President       Table Origin

PK presID            PK orID    
   presFName            orCity
   presLName            orState
   affiliation          birthDate
   gender
   term

Answer 1

Well in order to link the presidents to their origins you will need to bring over your
presID
As a foreign key in Table Origin

However it is true that you may be better served creating a tbl_term because there are many presidents with more than one term. The above example would require you having multiple president entries for multiple terms.

tbl_term
PK term_id
FK pres_id
term

To retrieve predecessors/successors
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

Answer 2

Answer Redacted.

Answer 3

Well, you have two entities (tables) in this situation: Presidents and Terms. The Terms will record the date and current president of the term.

The Presidents table will record the details of the president, not including the Terms, as this is covered in the Terms table.

Knowing this, you can answer the questions yourself. Here are some clues:

Identify predecessor and successor. This will be done by date so you know the predecessor is the immediate previous date before the one you are looking at now, and the successor will be the immediate date after. Note that predecessors and successors might not always exist for the president you are looking at (first president and current president).

You want to look into ORDER BY [DATE] and SELECT TOP...

Technically the design can further create a third table for birthplace, but this is semantics.

Your current idea places term into the presidents table - this doesn't cover a President serving a second term. You can however, feature two columns - but normalisation of data will show you that Terms could perhaps be a separate table (however, a design argument says that there will only be two terms per, so its swings-and-roundabouts).

Actually, a lot of the data about the president can be branched out into other tables using normalisation rules - but I won't give that away just yet...

If you have any specific questions, then ask in the comments - I've been indirect because its homework :)

Answer 4

When trying to design a database, the first step that I take is to try to identify entities and their relationships. The best way to do that in my experience is go through the requirements (the homework question in your case) and start listing out nouns. Each one is a potential entity. In this case, I see:

Presidents Terms in office Political party Sequence

The relationships that I see would be that a president has one or more terms, and can have one or more political party affiliations (and a political party can have one or more presidents that have been affiliated with it - so this will be a many-to-many relationship.)

Upon further review, it seems to me that the sequence is more derived information. The place of birth and birth date seem more like attributes of a president rather then an actual entity, although you could turn "place" into its own entity. So, I'd start putting down tables for Presidents, Terms, Political_Parties, and maybe Cities, States, etc.

Once that is done, I start looking through the requirements for attributes of each of these entities. A president has a birth date, a place of birth, and a gender.

Now add in the relationships between the entities, which may involve adding a simple column in some cases which is a foreign key to another entity or it may involve adding a whole new table that joins to entities.

For terms, I would just stick with start and end dates. I would suggest against any "term id". You can always find the term numbers from the dates through simple SQL queries.

Answer 5

Does this seem correct ?