Project Notes - Fall Semester 2007

Fall Semester 2007

Project Notes: Rotten Tomatoes.

I start by saying this is not my final design document.

My database likely will include the following tables:

Movies (MovieNum, Title, Description, Category, Year)
Actors (ActorNum, LastName, FirstName)
MoviesActors ( ... )
Directors ( ... )
MoviesDirectors ( ... )
Users (Username, LastName, FirstName)
Ratings (MovieNum, Username, Comment, Rating)

Let's create the first and last two tables.

Lets start with the users table.

Here's the basic data we want included, initially:

silo.cs.indiana.edu%ls -ld *
-rw-r--r-- 1 dgerman faculty 133 Oct  2 11:55 users
silo.cs.indiana.edu%cat users
Username, Last, First
lbird, Bird, Larry
mjordan, Jordan, Michael
mjackson, Jackson, Mark
cmullen, Mullen, Chris
tkukoc, Kukoc, Toni
silo.cs.indiana.edu%

Let's write a program that:

creates the necessary table,
reads the data in the data file, and
inserts the data in the corresponding rows/columns of the table

Note: recall that this document already showed us how to create a database, table etc.

Here's the program and what it does when we run it:

silo.cs.indiana.edu%ls -l
total 16
-rw-r--r-- 1 dgerman faculty 1071 Oct  2 12:05 initialize
-rw-r--r-- 1 dgerman faculty  133 Oct  2 11:55 users
silo.cs.indiana.edu%cat initialize 
#!/usr/bin/perl

use DBI; 

$DB        = "dbi:mysql:demoOne:silo.cs.indiana.edu:port=16248"; 
$username  = "dgerman";             
$password  = "sp00n"; 

$dbh = DBI->connect($DB, 
                    $username, 
                    $password, {PrintError => 0}) || 
       die "Couldn't open database: ", $DBI::errstr; 

print "I have opened the database...\n"; 

$query = qq{ 
  create table projectUsers (
    username   varchar(8) primary key, 
    lastName   varchar(20), 
    firstName  varchar(20) 
  )
};   

print " Getting ready to create the projectUsers table... "; 

$dbh->do($query) || die $dbh->errstr;

print "done.\n Getting ready to populate tables with data. \n"; 

open (INPUT, "users"); 
$line = <INPUT>; 
@names = split(/,/, $line); 
while ($line = <INPUT>) {
  ($uname, $last, $first) = split(/,/, $line, 3); 
  $st =~ s/\s//g; 
  chop($first); 
  $query = qq{
    insert into projectUsers values 
      ('$uname', 
       '$last', 
       '$first' 
      )
  }; 

  print $query, "\n"; 

  $dbh->do($query) || die $dbh->errstr; 

}
close(INPUT); 
silo.cs.indiana.edu%./initialize
./initialize: Permission denied.
silo.cs.indiana.edu%chmod 700 initialize 
silo.cs.indiana.edu%./initialize 
I have opened the database...
 Getting ready to create the projectUsers table... done.
 Getting ready to populate tables with data. 

    insert into projectUsers values 
      ('lbird', 
       ' Bird', 
       ' Larry' 
      )
  

    insert into projectUsers values 
      ('mjordan', 
       ' Jordan', 
       ' Michael' 
      )
  

    insert into projectUsers values 
      ('mjackson', 
       ' Jackson', 
       ' Mark' 
      )
  

    insert into projectUsers values 
      ('cmullen', 
       ' Mullen', 
       ' Chris' 
      )
  

    insert into projectUsers values 
      ('tkukoc', 
       ' Kukoc', 
       ' Toni' 
      )
  
silo.cs.indiana.edu%

We can check now, from the prompt, in MySQL:

silo.cs.indiana.edu%cat mysql_client
mysql --socket=/nobackup/dgerman/mysql/mysql.sock \
  --port=16248 --host=silo.cs.indiana.edu -u dgerman -p

silo.cs.indiana.edu%./mysql_client
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 5.0.22-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> use demoOne
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+----------------------------+
| Tables_in_demoOne          |
+----------------------------+
| ...                        | 
| projectUsers               | 
| ...                        |
+----------------------------+
25 rows in set (0.00 sec)

mysql> describe projectUsers;
+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| username  | varchar(8)  | NO   | PRI | NULL    |       | 
| lastName  | varchar(20) | YES  |     | NULL    |       | 
| firstName | varchar(20) | YES  |     | NULL    |       | 
+-----------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> select * from projectUsers;
+----------+----------+-----------+
| username | lastName | firstName |
+----------+----------+-----------+
| lbird    |  Bird    |  Larry    | 
| mjordan  |  Jordan  |  Michael  | 
| mjackson |  Jackson |  Mark     | 
| cmullen  |  Mullen  |  Chris    | 
| tkukoc   |  Kukoc   |  Toni     | 
+----------+----------+-----------+
5 rows in set (0.01 sec)

mysql> exit
Bye
silo.cs.indiana.edu%

Let's do the same with the other two tables.

silo.cs.indiana.edu%cat initMovies 
#!/usr/bin/perl

use DBI; 

$DB        = "dbi:mysql:demoOne:silo.cs.indiana.edu:port=16248"; 
$username  = "dgerman";             
$password  = "sp00n"; 

$dbh = DBI->connect($DB, 
                    $username, 
                    $password, {PrintError => 0}) || 
       die "Couldn't open database: ", $DBI::errstr; 

print "I have opened the database...\n"; 



$query = qq{ 
  create table projectMovies (
    movieNum   varchar(8) primary key, 
    title      varchar(80)
  )
};   

print " Getting ready to create the projectUsers table... "; 

$dbh->do($query) || die $dbh->errstr;

print "done.\n Getting ready to populate tables with data. \n"; 

open (INPUT, "movies"); 
$line = <INPUT>; 
@names = split(/,/, $line); 
while ($line = <INPUT>) {
  ($movieNum, $title) = split(/,/, $line, 2); 
  $st =~ s/\s//g; 
  chop($title); 
  if ($movieNum =~ /^\s*$/) { } else { 
    $query = qq{
      insert into projectMovies values 
        ('$movieNum', 
         '$title' 
        )
    }; 

    print $query, "\n"; 

    $dbh->do($query) || die $dbh->errstr; 
  } 
}
close(INPUT); 
silo.cs.indiana.edu%./initMovies
I have opened the database...
 Getting ready to create the projectUsers table... done.
 Getting ready to populate tables with data. 

      insert into projectMovies values 
        ('ryan1998', 
         ' Saving Private Ryan ' 
        )
    

      insert into projectMovies values 
        ('spongebo', 
         ' Spongebob: The Movie ' 
        )
    

      insert into projectMovies values 
        ('johnnyEn', 
         ' Johnny English ' 
        )
    

      insert into projectMovies values 
        ('incredib', 
         ' The Incredibles ' 
        )
    

      insert into projectMovies values 
        ('princess', 
         ' The Princess Bride ' 
        )
    

      insert into projectMovies values 
        ('nglishpt', 
         ' The English Patient ' 
        )
    
silo.cs.indiana.edu%

So this was the movies table (the program has some minor new features).

Here's the last table, the one with ratings. This will allow us to extract some useful information.

silo.cs.indiana.edu%cat ratings
user, movie, rating
lbird, ryan1998, 100
mjordan, ryan1998, 92
lbird, spongebo, 92
tkukoc, spongebo, 10
mjordan, spongebo, 80
lbird, johnnyEn, 20
tukoc, johnnyEn, 95
mjordan, johnnyEn, 68
cmullen, johnnyEn, 92
mjordan, incredib, 92
tkukoc, princess, 82
cmullen, princess, 68
lbird, nglishpt, 80
mjordan, nglishpt, 60

silo.cs.indiana.edu%cat initRatings 
#!/usr/bin/perl

use DBI; 

$DB        = "dbi:mysql:demoOne:silo.cs.indiana.edu:port=16248"; 
$username  = "dgerman";             
$password  = "sp00n"; 

$dbh = DBI->connect($DB, 
                    $username, 
                    $password, {PrintError => 0}) || 
       die "Couldn't open database: ", $DBI::errstr; 

print "I have opened the database...\n"; 

$dbh->do("drop table projectRatings") || print "There is no table yet...\n";

$query = qq{ 
  create table projectRatings (
    movieNum   varchar(8), 
    username   varchar(8), 
    comment    varchar(290), 
    rating     int, 
    primary key (movieNum, username)
  )
};   

print " Getting ready to create the projectUsers table... "; 

$dbh->do($query) || die $dbh->errstr;

print "done.\n Getting ready to populate tables with data. \n"; 

open (INPUT, "ratings"); 
$line = <INPUT>; 
@names = split(/,/, $line); 
while ($line = <INPUT>) {
  ($movieNum, $username, $rating) = split(/,/, $line, 3); 
  $movieNum =~ s/\s//g; 
  $username =~ s/\s//g; 
  $rating =~ s/\s//g; 
  if ($movieNum =~ /^\s*$/) { } else { 
    $query = qq{
      insert into projectRatings (movieNum, username, rating) values 
        ('$movieNum', 
         '$username', 
         '$rating' 
        )
    }; 

    print $query, "\n"; 

    $dbh->do($query) || die $dbh->errstr; 
  } 
}
close(INPUT); 
silo.cs.indiana.edu%./initRatings
I have opened the database...
 Getting ready to create the projectUsers table... done.
 Getting ready to populate tables with data. 

      insert into projectRatings (movieNum, username, rating) values 
        ('lbird', 
         'ryan1998', 
         '100' 
        )
    

      insert into projectRatings (movieNum, username, rating) values 
        ('mjordan', 
         'ryan1998', 
         '92' 
        )
    

      insert into projectRatings (movieNum, username, rating) values 
        ('lbird', 
         'spongebo', 
         '92' 
        )
    

      insert into projectRatings (movieNum, username, rating) values 
        ('tkukoc', 
         'spongebo', 
         '10' 
        )
    

      insert into projectRatings (movieNum, username, rating) values 
        ('mjordan', 
         'spongebo', 
         '80' 
        )
    

      insert into projectRatings (movieNum, username, rating) values 
        ('lbird', 
         'johnnyEn', 
         '20' 
        )
    

      insert into projectRatings (movieNum, username, rating) values 
        ('tukoc', 
         'johnnyEn', 
         '95' 
        )
    

      insert into projectRatings (movieNum, username, rating) values 
        ('mjordan', 
         'johnnyEn', 
         '68' 
        )
    

      insert into projectRatings (movieNum, username, rating) values 
        ('cmullen', 
         'johnnyEn', 
         '92' 
        )
    

      insert into projectRatings (movieNum, username, rating) values 
        ('mjordan', 
         'incredib', 
         '92' 
        )
    

      insert into projectRatings (movieNum, username, rating) values 
        ('tkukoc', 
         'princess', 
         '82' 
        )
    

      insert into projectRatings (movieNum, username, rating) values 
        ('cmullen', 
         'princess', 
         '68' 
        )
    

      insert into projectRatings (movieNum, username, rating) values 
        ('lbird', 
         'nglishpt', 
         '80' 
        )
    

      insert into projectRatings (movieNum, username, rating) values 
        ('mjordan', 
         'nglishpt', 
         '60' 
        )
    
silo.cs.indiana.edu%

Check the database from the MySQL prompt:

mysql> select * from projectUsers;
+----------+----------+-----------+
| username | lastName | firstName |
+----------+----------+-----------+
| lbird    |  Bird    |  Larry    | 
| mjordan  |  Jordan  |  Michael  | 
| mjackson |  Jackson |  Mark     | 
| cmullen  |  Mullen  |  Chris    | 
| tkukoc   |  Kukoc   |  Toni     | 
+----------+----------+-----------+
5 rows in set (0.00 sec)

mysql> select * from projectMovies;
+----------+------------------------+
| movieNum | title                  |
+----------+------------------------+
| ryan1998 |  Saving Private Ryan   | 
| spongebo |  Spongebob: The Movie  | 
| johnnyEn |  Johnny English        | 
| incredib |  The Incredibles       | 
| princess |  The Princess Bride    | 
| nglishpt |  The English Patient   | 
+----------+------------------------+
6 rows in set (0.00 sec)

mysql> select * from projectRatings;
+----------+----------+---------+--------+
| movieNum | username | comment | rating |
+----------+----------+---------+--------+
| lbird    | ryan1998 | NULL    |    100 | 
| mjordan  | ryan1998 | NULL    |     92 | 
| lbird    | spongebo | NULL    |     92 | 
| tkukoc   | spongebo | NULL    |     10 | 
| mjordan  | spongebo | NULL    |     80 | 
| lbird    | johnnyEn | NULL    |     20 | 
| tukoc    | johnnyEn | NULL    |     95 | 
| mjordan  | johnnyEn | NULL    |     68 | 
| cmullen  | johnnyEn | NULL    |     92 | 
| mjordan  | incredib | NULL    |     92 | 
| tkukoc   | princess | NULL    |     82 | 
| cmullen  | princess | NULL    |     68 | 
| lbird    | nglishpt | NULL    |     80 | 
| mjordan  | nglishpt | NULL    |     60 | 
+----------+----------+---------+--------+
14 rows in set (0.00 sec)

mysql>

So we see there's a small mistake.

mysql> select username, avg(rating) from projectRatings group by username;
+----------+-------------+
| username | avg(rating) |
+----------+-------------+
| incredib |     92.0000 | 
| johnnyEn |     68.7500 | 
| nglishpt |     70.0000 | 
| princess |     75.0000 | 
| ryan1998 |     96.0000 | 
| spongebo |     60.6667 | 
+----------+-------------+
6 rows in set (0.00 sec)

However we can easily write a Perl/CGI script to extract this from the database. Ditto for PHP.

silo.cs.indiana.edu%cat extract
#!/usr/bin/perl

use DBI; 
use CGI; 

$q = new CGI; 

print "Content-type: text/html\n\n"; 

$DB        = "dbi:mysql:demoOne:silo.cs.indiana.edu:port=16248"; 
$username  = "dgerman";             
$password  = "sp00n"; 

$dbh = DBI->connect($DB, 
                    $username, 
                    $password, {PrintError => 0}) || 
       die "Couldn't open database: ", $DBI::errstr; 

$query = "select username, avg(rating) from projectRatings group by username;"; 

$sth = $dbh->prepare($query) || die $dbh->errstr;

$sth->execute() || die $sth->errstr;

print qq{
  <table border cellpadding=2>
    <tr><th> Movie Title 
        <th> Average Rating 
}; 

while (my $row = $sth->fetch) {
  my(@values) = @$row; 
  print "<tr>"; 
  foreach $value (@values) {
    print "<td>$value"; 
  } 
  print "\n";
} 

print "</table>"; 

$sth->finish; 
silo.cs.indiana.edu%./extract
Content-type: text/html


  <table border cellpadding=2>
    <tr><th> Movie Title 
        <th> Average Rating 
<tr><td>incredib<td>92.0000
<tr><td>johnnyEn<td>68.7500
<tr><td>nglishpt<td>70.0000
<tr><td>princess<td>75.0000
<tr><td>ryan1998<td>96.0000
<tr><td>spongebo<td>60.6667
</table>silo.cs.indiana.edu%

Last updated: Oct 2, 2007