Database Design Relationships

• 1-1:
  
  
  
  
  
  • In order to create the relationship between two relations with a 1-1 relationship - post the primary key of one table into the other table, it doesn't matter which way around.
    
    
  • The key placed into the other relation is called a foreign key.
    
    
  • MP{name, dob, party, constituencyName*}
    
    
  • Constituency{name, size}
    
    

  ---

• 1-Many:
  
  
  
  
  
  • To create the relationship - the primary key of the 1 side is posted to the many side as a foreign key.
    
    
  • LibraryBook{id_no, name}
    
    
  • Reader{membership_no, name, book_id*}
    
    
  • Imagine if the many side was posted to the one side instead.
    
    

  ---

• Many-Many (M-N):
  
  
  
  
  
  • To create the relationship we can't add a foreign key, because will result in repeted data.
    
    
  • Instead, create an extra link table between the two relations, which has as its primary key the foreign keys from the two relations.
    
    
  • Actor{id_no, name}
    
    
  • Film{id_no, title}
    
    
  • Cast{actor_no*, film_no*}
    
    
  • The foreign keys from the original tables form a compound primary key in the link table.
    
    
  • The link table has a 1-many relationship with both the original tables.
    
    
  • This method prevents duplications of the relationship - actors could not be entered for the same film twice because the foreign keys are the primary key.
    
    
  • A link table can have its own attributes, if it is appropriate for the relationship to have attributes.
    
    
  • E.g. the Cast table could have an attribute 'time_spent_on_set'