Joins and Implementation of relationships in sql

We have three types of relationships in SQL.

  • one-to-one
  • one-to-many
  • many-to-many
  • To implement these relationships

    One-to-one: Use foreign key to the referenced table
    Example:
    Student: sid, fname, lname, addressId
    Address: addressId, adress, city, sid

    One-to-many: Use foreign key on the “many” side of the relationship linking back to the “one” side.
    Example:
    Teacher: tid, fname, lname
    Classes: cid, cname, tid    
    Note: Here table ‘Teacher’ is one side and table ‘Classes’ is on many side

    Many-to-many: Use junction table.
    The junction table stores the primary keys of each table that is involved in the relationship.
    Example:
    Student: sid, fname, lname
    classes: cid, cname, tid
    student_classes: cid, sid

    Joins

    Here is a very good chart of all joins (and its variations) in SQL
    joins
    Reference: http://stackoverflow.com/questions/38549/difference-between-inner-and-outer-joins

    Advertisements

    Leave a Reply

    Fill in your details below or click an icon to log in:

    WordPress.com Logo

    You are commenting using your WordPress.com account. Log Out / Change )

    Twitter picture

    You are commenting using your Twitter account. Log Out / Change )

    Facebook photo

    You are commenting using your Facebook account. Log Out / Change )

    Google+ photo

    You are commenting using your Google+ account. Log Out / Change )

    Connecting to %s

    Mawazo

    Mostly technology with occasional sprinkling of other random thoughts

    amintabar

    Amir Amintabar's personal page

    101 Books

    Reading my way through Time Magazine's 100 Greatest Novels since 1923 (plus Ulysses)

    Seek, Plunnge and more...

    My words, my world...

    ARRM Foundation

    Do not wait for leaders; do it alone, person to person - Mother Teresa

    Executive Management

    An unexamined life is not worth living – Socrates

    Diabolical or Smart

    Nitwit, Blubber, Oddment, Tweak !!

    javaproffesionals

    A topnotch WordPress.com site

    thehandwritinganalyst

    Just another WordPress.com site

    coding algorithms

    "An approximate answer to the right problem is worth a good deal more than an exact answer to an approximate problem." -- John Tukey