Databases and the Web

Document Sample
Databases and the Web Powered By Docstoc
					Databases and the Web
            By David Drake
              ITC-RCSC
  (Research Computing Support Center)
            244 Wilson Hall
            October 17, 2001
       Databases and the Web
I.   Databases in Abstract
II.  Creating Databases using Relational
     DataBase Management Systems
     (RDBMSs)
III. Databases and Web Interfaces
    I.      Databases in Abstract
• Definition:
  – A database is an organized collection of data
    whose content must be quickly and easily
     • Accessed
     • Managed
     • Updated
  – A relational database is one whose data are
    split up into tables, sometimes called relations
                Normalization
• First Normal Form (1NF)
  – All attributes are single valued & non-repeating
• 2NF
  – Must be 1NF & must have primary key
  – Each non-primary key attribute must be functionally
    dependent on primary key
• 3NF
  – Must be 2NF
  – Each non-primary key attribute must be dependent only
    on primary key
                       Tables
• Each column constitutes an attribute
• Each row constitutes a record or tuple

                       Attribute 1   Attribute 2
                       (column 1)    (column 2)
           Record 1
           (tuple 1)
           Record 2
           (tuple 2)
                      Keys
• Primary
  – An attribute or group of attributes which
    uniquely identifies each record in a table
  – May not be a Null value
• Foreign
  – used primarily for enforcing referential
    integrity, but also for establishing relationships
    between the two tables
            Relationships
• One-to-one (1-to-1)
• One-to-many (1-to-M or 1-to-)
• Many-to-Many (M-to-M or -to-)
    Structured Query Language
              (SQL)
• Pronounce “Sequel” or “Ess Que Ell”
• Industry standard language of (Relational)
  Databases
• Allows for complete
  – Table Creation, Deletion, Editing
  – Data extraction (Queries)
  – Database management & administration
  II.       Creating Databases using
                   RDBMSs
A. Microsoft Access
  –     Creating Tables
  –     Entering, Importing, Editing, & Viewing Data
  –     Defining Relationships
  –     Constructing Queries
B. MySQL … (Documentation)
  –     Where to put it (servers at UVa)
  –     Ditto MS Access
C. Others
  –     mSQL, PostGreSQL, Oracle, DB2, Informix, Sybase,
        Empress, Adabas…
         III. Databases and
              Web Interfaces
• What a Web-Interface for a Database
  involves
  – Your database (Access, MySQL)
  – A Web server (html)
  – A way of connecting the two
    (CGI scripts and SQL)
  – Security concerns
           Where to put your
          Database and Scripts
• Academic side of UVa
  – Access
     • ESERVICES es-web1 (web.virginia.edu)
  – MySQL
     • MySQL server (dbm1.itc.virginia.edu)
     • Home directory (blue.unix, faculty, curry, avery, minerva –
       www.virginia.edu, jm.acs – www.itc.virginia.edu)
• Medical side of UVa
  – See the UVa Health System‟s Web Development
    Center
      Active Server Pages (ASP)
•    When a browser calls an ASP document, the
     ASP Server reads the .asp document and
    1. Substitutes appropriate files for the (server-side)
       include statements
    2. Runs the ASP code (Visual Basic Script – see the
       Tutorial and Language Reference, …)
    3. Returns the resulting HTML code to the browser
•    Example
            ASP Key Points (1)
• ASP code enclosed in: <% ASP code %>
• Everything outside is HTML
• The result of the combined HTML and ASP code
  must be a “standard” HTML document, e.g.:
  – <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Final//EN">
    <html>
    <head>
    <title>Miracle Drug Study</title>
    <meta name="Description" content="">
    <meta name="Keywords" content="">
    <link rel=STYLESHEET type="text/css" href="">
    </head>
    <body>
    </body>
    </html>
           ASP Key Points (2)
• Connect with database:
   – Create connection object:
      • set conn = Server.CreateObject("ADODB.Connection")
   – Open connection:
      • conn.open("Provider=Microsoft.Jet.OLEDB.4.0;Data
        Source=f:\web\database\rescomp\study.mdb")
• Submit Query:
   – Generate SQL statement:
      • SQL = "SELECT FirstName, LastName, DOB, Gender FROM
        Patients WHERE Gender = '" & Gender & "' ORDER BY
        FirstName DESC"
   – set Patients = conn.execute(SQL)
          ASP Key Points (3)
• Add to or edit table:
  – Create and open Record Set object:
     • set RS = Server.CreateObject("ADODB.Recordset")
       RS.Open "Treatment", conn, , adLockOptimistic, adCmdTable
  – Create new record, Edit, & Update:
     • RS.AddNew
       RS(“Dosage”) = 200
       RS.Update
          ASP Key Points (4)
• Add to or edit table (continued):
  – Or Find desired record, Edit, & Update :
     • do while NOT RS.eof
             if RS(“ID”) = 7 then
                      RS(“Dosage”) = 200
                      RS.Update
             else
                      RS.MoveNext
             end if
       loop
            ASP Key Points (5)
• Clean up (free server resources) when done:
   – Queries:
      • Patients.Close
        set Patients = nothing
   – Record Sets:
      • RS.Close
        set RS = nothing
   – The Connection:
      • conn.close
        set conn = nothing
              ASP Security
• Apart from various Internet Information
  Services (IIS – Window‟s Web service)
  security holes (for viruses and worms),
  security is quite good.
• Use https:// if you want to protect content
  over the Web – provides Secure Socket
  Layer (SSL) security
  PHP: Hypertext Preprocessor
           (PHP)
• Server-side
• Cross-platform
• HTML embedding scripting language (see
  the PHP online manual
• Example 1, Example 2
         PHP Key Points (1)
• PHP code enclosed in <?php PHP code ?>
  or <? PHP code ?>
• Everything outside is HTML
• Output is (generally) to a browser requiring
  standard HTML
           PHP Key Points (2)
Connecting with RDBMS and editing, adding, and
  deleting databases therein are all done through
  PHP functions
• Connect with MySQL RDBMS
   – mysql_connect($hostName, $userName, $password) or
     die("Unable to connect to host $hostName");
• Connect with database
   – mysql_select_db($dbName) or die("Unable to select
     database $dbName");
           PHP Key Points (2)
Queries: Nearly all table interaction and
  management is done through queries:
• Basic information searches
   – $SQL = "SELECT FirstName, LastName, DOB,
     Gender FROM Patients WHERE Gender = '$Gender„
     ORDER BY FirstName DESC";
     $Patients = mysql_query($SQL);
• Editing, adding, and deleting records and tables
   – $SQL = "INSERT INTO Patients (FirstName,
     LastName) VALUES('$firstName', '$lastName')";
     $Patients = mysql_query($SQL);
         PHP Key Points (3)
• Cleaning up: close the database connection
  – mysql_close();
        PHP/MySQL Security
• PHP/MySQL can be made secure apart from one
  serious flaw as implemented at UVa:
  – Because web files must be readable by the world (unix
    permissions), anyone with an account on the server
    where you run the php code can see the code, including
    your MySQL $password.
  – A couple of exceptions to this flaw are as follows:
     • If you secure your server so that there are no other users on it
     • If you use Perl instead, compile it, and then hide or remove
       your source code containing the MySQL password
     • One other possible poor to fair workaround: use .htaccess to
       password protect your php directory
  – See the passwords link
      Practical Extraction and
      Report Language (Perl)
            a.k.a Pathologically Eclectic Rubbish Lister


• Ubiquitous
  – Originally designed to be a better general
    purpose tool than a Unix shell, it has grown and
    spread to be support from Windows to
    Macintosh to VMS.
• Cryptic
          Perl Key Points (1)
• First Line must specify the location of the
  Perl engine (The DBI module will not work
  for “#!/usr/local/bin /perl” – see below):
  – #!/usr/local/bin/perl5 -w
• First printed line must be the following if
  you want it to respond to go to a browser:
  – print "Content-type: text/html\n\n";
          Perl Key Points (2)
Modules
• You *must* use the DBI module which
  allows you to interface with the database
  (see DBI link 1 & DBI link 2)
  – use DBI;
• You should also make use of the CGI
  module
  – use CGI;
             Perl Key Points (3)
• Set the usual parameters:
  – my $hostname = "dbm1.itc.virginia.edu";
    my $username = "dld5s"; # "my" defines a local variable
    my $username = "dld5s";
    my $password = "ias!";
    my $database = $username . "_study"; # = dld5s_study
    my $data_source = "DBI:mysql:$database:$hostname";

• Connect to the database:
  – my $dbh = DBI->connect($data_source, $username, $password)
    or die "Can't connect to $data_source: $DBI::errstr\n";
             Perl Key Points (4)
• Define the SQL statement and execute
  – my $SQL = "SELECT FirstName, LastName, DOB, Gender
         FROM Patients
         WHERE Gender = '$Gender„
         ORDER BY FirstName DESC";
    my $sth = $dbh->prepare($SQL)
    or die "Unable to prepare $SQL: dbh->errstr\n";
    $sth->execute or die "Unable to execute query: $dbh->errstr\n";

• Clean up
  – $sth->finish;
    $dbh->disconnect;
              Other Resources
• MySQL by DuBois (New        • {Active Server Pages
  Riders)                       3.0 by Mitchell &
• MySQL & mSQL                  Atkinson (Sams)}
  byYarger, Reese, & King
                              • ITC Desktop Web
• Beginning PHP4 by Blan,
  Choi, et. al (Wrox)           pages
• Learning Perl by Schwartz
  & Christiansen (O‟Reilly)