search

bryandido / cmpe138

1 stars 0 forks source link

Create schema from ER Diagram #1

Open bryandido opened 6 years ago

bryandido commented 6 years ago

Follow the steps from Chapter 8: ER Relational Mapping.

clif commented 6 years ago

Project DB Schema draft is here:

https://www.lucidchart.com/invitations/accept/63bd1f21-bb3f-43d9-9311-7d5b25198a0b

As I went through creating the DB Schema, I modified the enhanced ER Diagram a little to connect everything a little better.

Edits made to enhanced ER Diagram

  1. We need something to help differentiate Army, Navy, and Air Force. Although it is not true in the real world, the army will have a 'tank_type', the Navy will have a 'ship_type', and the Air Force will have a 'plane_type'. Comments? Suggestions? See Things I think we can trim off below.
  2. Added 'politician_id' as key attribute for politicians,
  3. Added 'term' attribute for head of state.
  4. Added 'position' attribute for justice department (judge, lawyer, etc.)
  5. Changed cardinality from Bill to Law from (0,M) to (0,1) since a bill can become at most one law.

Questions I have that I need your input on:

  1. Does 'address' attribute make sense to uniquely identify a military branch? If not, any suggestions?
  2. The "CAN_BECOME" is a 1:1 relationship, however, there is no total participation on either side. Therefore I went with approach number 3 and created a new relation for it. Is this the right call?

Things I think we can trim off:

  1. Army, Navy, Air Force can be clumped up to military personnel with a 'job' attribute.
  2. Justice department follows law relationship, is this really needed?
  3. Perhaps vote_for relationship

Please review the DB Schema and let's discuss what needs to be modified.

clif commented 6 years ago

For question number 2, the professor said that creating a new table for it is fine. The other option will be to have an attribute on either table which will denote that either the immigrant is a citizen or that the citizen was an immigrant.