需要数据库设计帮助
设计一个数据库来存储有关美国总统及其任期的详细信息。此外，记录他们的出生日期和地点、性别以及政党背景的详细信息。您需要记录总统的顺序，以便可以识别任何总统的前任和继任者。请记住，格罗弗·克利夫兰曾担任过两届非连续任期，而且政治立场可能会发生变化。
这是我到目前为止所掌握的，请告诉我是否可以改进。另外，我怎样才能做到这一点“可以识别任何总统的前任和继任者”，这是使用递归关系完成的吗？我将如何执行它？
Table President Table Origin
PK presID PK orID
presFName orCity
presLName orState
affiliation birthDate
gender
term
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
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
好吧，在这种情况下您有两个实体（表）：Presidents 和Terms。条款将记录任期日期和现任主席。
主席表将记录主席的详细信息，不包括条款，因为这已包含在条款表中。
知道了这一点，你就可以自己回答问题了。以下是一些线索：
识别前驱和后继。这将按日期完成，以便您知道前驱是您现在正在查看的日期之前的前一个日期，后继将是当前日期后。请注意，您所看到的总统（第一任总统和现任总统）可能并不总是存在前任和继任者。
您想要查看 ORDER BY [DATE] 和 SELECT TOP...
从技术上讲，设计可以进一步为出生地创建第三个表，但这是语义。
您当前的想法将任期放入总统表中 - 这不包括担任第二个任期的总统。但是，您可以提供两列 - 但数据标准化将向您表明，术语可能是一个单独的表（但是，设计论证表明每个术语只有两个术语，因此它是摇摆不定的）。
实际上，关于总统的很多数据可以使用标准化规则扩展到其他表中 - 但我还不会透露这一点......
如果您有任何具体问题，请在评论中提问 - 我已经是间接的，因为它的作业:)
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 :)
当尝试设计数据库时，我采取的第一步是尝试识别实体及其关系。根据我的经验，最好的方法是完成要求（您的案例中的家庭作业问题）并开始列出名词。每一个都是一个潜在的实体。在这种情况下，我看到：
总统
任职期限
政党
序列
我看到的关系是，一位总统有一个或多个任期，并且可以有一个或多个政党从属关系（一个政党可以有一位或多位与其有联系的总统 - 所以这将是许多-对多关系。）
经过进一步审查，在我看来，该序列是更多派生信息。出生地和出生日期看起来更像是总统的属性，而不是实际的实体，尽管您可以将“地点”变成其自己的实体。因此，我将开始为总统、任期、政治政党以及城市、州等制定表格。
完成后，我开始查看每个实体的属性要求。总统有出生日期、出生地点和性别。
现在添加实体之间的关系，这可能涉及在某些情况下添加一个简单的列，该列是另一个实体的外键，或者可能涉及添加一个连接到实体的全新表。
对于条款，我只会坚持开始和结束日期。我建议不要使用任何“term id”。您始终可以通过简单的 SQL 查询从日期中找到术语编号。
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.
好吧，为了将总统与其起源联系起来，您需要带上您的
presID作为 Table Origin 中的外键
然而，确实，创建 tbl_term 可能会更好，因为有许多总统拥有多个任期。上面的示例要求您有多个任期的多个总统条目。
tbl_term
PK term_id
FK pres_id
term
检索前任/后任
声明@maxTerm INT声明 @minTerm INT从 tbl_term AS t 中选择 @maxTerm = MAX(term_id), @minTerm = MIN(term_id)JOIN tbl_pres AS p
ON p.pres_id = t.pres_id
WHERE p.presLName = 'lname'
AND p.presFName = 'fname'
那么前任是
从 tbl_pres AS p 中选择 p.presLName、p.presFNameJOIN tbl_term AS t
ON p.pres_id = t.pres_id
WHERE t.term_id = @minTerm - 1
反之亦然
从 tbl_pres AS p 中选择 p.presLName、p.presFNameJOIN tbl_term AS t
ON p.pres_id = t.pres_id
其中 t.term_id = @maxTerm + 1
Well in order to link the presidents to their origins you will need to bring over your
presIDAs 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 INTDECLARE @minTerm INTSELECT @maxTerm = MAX(term_id), @minTerm = MIN(term_id) FROM tbl_term AS tJOIN 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 pJOIN 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 pJOIN tbl_term AS t
ON p.pres_id = t.pres_id
WHERE t.term_id = @maxTerm + 1