CSCI 107 Databases

Introduction by example

• Stanford social database

• select gobtpac_external_user username, f_unca_format_name(gobtpac_pidm, 'FML') FMLname, gb_common.f_get_id(gobtpac_pidm) BannerID
  from gobtpac
     where exists (
        select 'x' from sfrstcr
        join ssbsect
        on sfrstcr_term_code = ssbsect_term_code
        and sfrstcr_crn = ssbsect_crn
        where sfrstcr_pidm = gobtpac_pidm
           and ssbsect_subj_code in ('CSCI','EGM')
           and sfrstcr_rsts_code in 
              (select stvrsts_code
               from stvrsts
               where STVRSTS_VOICE_TYPE = 'R'
                  and STVRSTS_ENTERABLE_IND = 'Y')
                  and sfrstcr_term_code = 
                     (select min(stvterm_code)
                      from stvterm
                      where stvterm_end_date > sysdate
                      and stvterm_code like '%0'
                     )
              )
  

Three tutorials on databases

Read the first one

• Relational Databases for Dummies
• geekgirl’s plain-english computing
  • Databases from scratch I: Introduction
  • Databases from scratch II: Simple Database Design
  • Databasics III: Data entry design
• a fishy database

Introduction of the process

• The Entity-Relationship Model
• Normalization
• SQL
• Server-side with PHP

Database cartoons

• SQL injection
• Mauve
• Build a database
• Complex relational model
• Dilbert query results