proper format for this database table

[mensanator at aol.com]

mensanator at aol.com wrote:
> John Salerno wrote:
> > mensanator at aol.com wrote:
> >
> > > [Eid] [Sid] [university] [yearStart] [yearEnd] [degreeEarned]
> > > 5      1     U of I       1971        1975      BS
> > > 6      1     U of I       1975        1976      MS
> > > 7      1     U of I       1976        1977      PhD
> > >
> > > where [Eid] is the primary key of the Education table and
> > > [Sid] is the foreign key from the Student table so that the
> > > single student record (1) links to three education records
> > > (5,6,7).
> > >
> >
> > Hmmm. I think I'm lost. My table will have a key that represents the
> > persons ID, in my case it would be salerjo01. This will be what ties
> > this table to that person. Can I repeat this key multiple times in the
> > Education table?
>
> Yes, because it would be a foreign key.
>
> Perhaps a simple example might help:
>
> import sqlite3
>
> con = sqlite3.connect(":memory:")
> cur = con.cursor()
>
> # in the [person] table, [personid] is the primary key
> # but in the [education] table, it's a foreign key
> # I deliberately didn't make them AUTOINCREMENT to
> # simplify the example
> cur.executescript("""
>     create table person(
>         personid PRIMARY KEY,
>         firstname,
>         lastname
>     );
>
>     create table education(
>         educationid PRIMARY KEY,
>         personid,
>         institution,
>         yearStart,
>         yearEnd,
>         degreeEarned
>     );
>     """)
>
> persons = [(1,'Tom','Smith'), \
>            (2,'Dick','Smith'), \
>            (3,'Harry','Smith')]
>
> degrees = [(1,1,'University of Illinois',1971,1975,'BS'), \
>            (2,1,'University of Illinois',1975,1976,'MS'), \
>            (3,1,'University of Illinois',1976,1977,'PhD'), \
>            (4,2,'University of Illinois',1971,1974,None), \
>            (5,2,'DeVry Institute of Technology',1974,1976,'ASEET')]
>
> cur.executemany("""
> INSERT INTO person(personid,
>                    firstname,
>                    lastname)
> VALUES      (?,?,?)"""
> , persons)
>
> cur.executemany("""
> INSERT INTO education(educationid,
>                       personid,
>                       institution,
>                       yearStart,
>                       yearEnd,
>                       degreeEarned)
> VALUES      (?,?,?,?,?,?)"""
> , degrees)
>
>
> # Note: since both tables have a field named [personid],
> #       the table name must be included when referencing
> #       that field
> cur.execute("""
> SELECT      firstname,
>             lastname,
>             institution,
>             yearStart,
>             yearEnd,
>             degreeEarned
> FROM        education
> INNER JOIN  person
> ON          person.personid = education.personid
> ORDER BY    education.personid;
> """)
>
> report = cur.fetchall()
>
>
> for i in report:
>     print '%5s %-5s %-30s  %d-%d  %-6s' % (i)
>
> ##  Tom Smith University of Illinois          1971-1975  BS
> ##  Tom Smith University of Illinois          1975-1976  MS
> ##  Tom Smith University of Illinois          1976-1977  PhD
> ## Dick Smith University of Illinois          1971-1974  None
> ## Dick Smith DeVry Institute of Technology   1974-1976  ASEET
>
> ## Note that the third person, Harry Smith, doesn't show up
> ## in the report.
> ## That's because no education records were created for him.
> ## When you do an INNER JOIN between two tables, the linking field,
> ## [personid] must exist in both tables.

If you actually DO want to see Harry Smith listed in the
report even though he has no education, you would change
the INNER JOIN to a RIGHT JOIN. But sqlite3 doesn't
support RIGHT JOIN, only LEFT JOIN. So we just have
to change the direction of the JOIN. Instead of from
education to person, make it it from person to
education and then we can use LEFT JOIN. And it might
be a good idea to sort on person.personid since
education.personid will be null if there is no matching
record.

The modified query becomes:

cur.execute("""
SELECT      firstname,
            lastname,
            institution,
            yearStart,
            yearEnd,
            degreeEarned
FROM        person
LEFT JOIN   education
ON          person.personid = education.personid
ORDER BY    person.personid;
""")

And I had to change the print statement since nulls
(which translate to None) crash the %d. With that
change we now can see ALL the people and note
that Harry is uneducated.

  Tom Smith University of Illinois          1971-1975  BS
  Tom Smith University of Illinois          1975-1976  MS
  Tom Smith University of Illinois          1976-1977  PhD
 Dick Smith University of Illinois          1971-1974  None
 Dick Smith DeVry Institute of Technology   1974-1976  ASEET
Harry Smith None                            None-None  None