Hello, I am inside my first database class and I am having trouble creating the

Question

Hello, I am inside my first database class and I am having trouble creating the diagram for these recursive examples. Any help with these examples would be greatly appreciated. 1.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? 2.Data modeling is finally getting to you. Last night you dreamed you were asked by Noah to design a database to store data about the animals on the ark. All you can remember from Sunday school is the bit about the animals entering the ark two-by-two, so you thought you should check the real thing. Take with you seven pairs of every kind of clean animal, a male and its mate, and two of every kind of unclean animal, a male and its mate, and also seven pair of every kind of bird, male and female. Genesis 7:2 Next time Noah disturbs your sleep, you want to be ready. So, draw a data model and make certain you record the two-by-two relationship.

Explanation / Answer

Hi,

To create a simple data base to store details about U.S. presidents and their terms in office, we create two tables .

First table is PresidentDetails which has a unique key assigned to each president as presidentId which will act as a primary key for a president.Other details can be president's Name, date of birth, place of birth, qualifications, list of political parties president was affiliated to.( List because we are considering the case where president may belong to a particular party). If you do not want to include political parties list , then also its fine, we can find its alternative in next table we will discuss.

Find the attributes of this table below:-

PRESIDENT DETAILS TABLE(including list of political parties)

PRESIDENT DETAILS TABLE(excluding list of political parties)

Now, the next table:-

Ruling President table, where we will store an id ,say, elected id which will be unique in table indicting the time the election held. for e.g. If 1 is elected id this indicates the first elected president i.e. refers to result of first election. This elected Key will be primary key.

Other field will be p_id i.e president Id defined in last table which will act as foriegn key. Other fields can be year the president was appointed, political party the president belong to during that period of time (in case of excluding list of political parties in last table), duration the president served etc.

RULING PRESIDENT TABLE(in case of excluding list of political parties in previous table)

RULING PRESIDENT TABLE(in case you have included list of political parties in last table)

I would suggest you to go with the approach of not including list of political parties in president detail table.because it will effect the normalization of the data base

So, our final schema would be:-

PRESIDENT DETAILS TABLE

RULING PRESIDENT TABLE

In case you want to select a president by political party, you can return all the presidents belonging to a political party by finding their p_id in Ruling president table and searching for name in president detail table.

In case you want to find predecessor and successor of a president then find the p_id corressponding to his name in the president detail table and return the name corresponding to p_id of row just above(in case of predecessor) and below row (in case of successor) the row belonging to p_id we have just finded.

In case you want to find out the total number of years for which a president served juct sum the duration corresponding to a p_id. Similarly, in case you want to find all political parties to which a president belong return all the rows corresponding to p_id in ruling president table.

Thanks!!! Hope you will like it!!!!

Tha

No. Field Name Description Data Type Remarks 1. p_id president's id number Primary key 2. p_name president's name varchar 3.   dob president's date of birth Date 4 place president's place of birth varchar 5 parties list of political parties List<varchar>

Related Questions

Navigate

• Browse (All)
• Browse H
• Subjects

---

---

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