CS411 Database Systems

1
CS411Database Systems

• 07 PHP IntroductionRelational Algebra

2
PHP

• http//www.phpexamples.net/content/view/14/27/
• lt?php
• A typical shell script comment
• //Another style of comment
• /A third style of comment, which can span
  multiple lines of code, really helpful when
  commenting out code.
• / ?gt

3
Double quotes are useful

• lt?php
• //example 1
• variable 'a test'
• echo 'Example 1 This is variable to show that
  the value is not printed.ltbr /gt'
• echo "Example 2 This is variable to show that
  the value is printed."
• ?gt Output
• Example 1 This is variable to show that the
  value is not printed.ltbr /gt
• Example 2 This is a test to show that the value
  is printed.

4
String concatenation

• clickLink 'lta href"www.somesite.com"gt' .
  textToLink .

5
If statements

• if(i 1)
• echo 'i 1'

6

• lt?php
• function connectDb(user, pass, host, db)
• //this function connects to a mysql server
• sock mysql_connect(host, user, pass)
• if(isset(sock))
• if(!mysql_select_db(db, sock))
• echo mysql_error()
• return sock
• socket connectDb('bilbo','b_at_gg1ns','localhost',
  'test')
• ?gt

7

• mysql_connect(host, user, pass)
• mysql_select_db(db, sock)
• mysql_query(query, conn)
• mysql_fetch_array(query_result)

8
Create a table from PHP

• sql "CREATE TABLE Jokes ( " .
• "ID INT NOT NULL AUTO_INCREMENT PRIMARY
  KEY, " .
• "JokeText TEXT, " .
• "JokeDate DATE NOT NULL " .
• ")"
• if ( mysql_query(sql) )
• echo("ltPgtJokes table successfully
  created!lt/Pgt")
• else
• echo("ltPgtError creating Jokes table " .
• mysql_error() . "lt/Pgt")

http//dev.mysql.com/tech-resources/articles/ddws/
22.html
9
Update

• sql "UPDATE Jokes SET JokeDate'1990-04-01' "
  .
• "WHERE JokeText LIKE 'chicken'"
• if ( mysql_query(sql) )
• echo("ltPgtUpdate affected " .
• mysql_affected_rows() . " rows.lt/Pgt")
• else
• echo("ltPgtError performing update " .
• mysql_error() . "lt/Pgt")

10
Select

• result mysql_query("SELECT JokeText FROM
  Jokes")
• if (!result)
• echo("ltPgtError performing query " .
• mysql_error() . "lt/Pgt")
• exit()

11
Select

• result mysql_query("SELECT JokeText FROM
  Jokes")
• while ( row mysql_fetch_array(result) )
• // process the row...

12
Select

• result mysql_query("SELECT JokeText FROM
  Jokes")
• while ( row mysql_fetch_array(result) )
• echo("ltPgt" . row"JokeText" . "lt/Pgt")

13
ltHTMLgtltHEADgtltTITLEgtJokeList.phplt/TITLEgtlt/HEADgtltBOD
Ygt lt?php dbcnx _at_mysql_connect("localhost",
"root", "mypasswd") if (!dbcnx)echo(
"ltPgtUnable to connect to the database at this
time.lt/Pgt" ) exit() // Select the jokes
database if (! _at_mysql_select_db("jokes") )
echo( "ltPgtUnable to locate the joke database at
this time.lt/Pgt" ) exit() ?gt ltPgt Here are
all the jokes in our database lt/PgtltBLOCKQUOTEgt lt
?php // Request the text of all the jokes
result mysql_query("SELECT JokeText FROM
Jokes") if (!result) echo("ltPgtError
performing query " . mysql_error() . "lt/Pgt")
exit() while ( row mysql_fetch_array(resu
lt) ) echo("ltPgt" . row"JokeText" .
"lt/Pgt") ?gt lt/BLOCKQUOTEgtlt/BODYgtlt/HTMLgt
14
Inserting data

• ltFORM ACTION"lt?php echo(PHP_SELF) ?gt"
  METHODPOSTgt
• ltPgtType your joke hereltBRgt
• ltTEXTAREA NAME"joketext" ROWS10 COLS40
  WRAPgtlt/TEXTAREAgtltBRgt
• ltINPUT TYPESUBMIT NAME"submitjoke"
  VALUE"SUBMIT"gt
• lt/FORMgt

15
Register Globals off

• submitjoke_REQUEST"submitjoke"

16

• if ("SUBMIT" submitjoke)
• sql "INSERT INTO Jokes SET " .
• "JokeText'joketext', " .
• "JokeDateCURDATE()"
• if (mysql_query(sql))
• echo("ltPgtYour joke has been added.lt/Pgt")
• else
• echo("ltPgtError adding submitted joke " .
• mysql_error() . "lt/Pgt")

17

• SQL Injection?

18
Summary Detail pages

• Joke 1
• Joke 2
• lttrgtlttdgtlta href'editJoke.php?id1'gtJoke
  1lt/agtlt/dgtlttrgt
• lttrgtlttdgtlta href'editJoke.php?id2'gtJoke
  2lt/agtlt/dgtlttrgt
• while ( row mysql_fetch_array(result) )
• echo("lta href'editJoke.php?id" . row"id"
  . "'gt clickme! lt/agt" )

19
Reference
20

• lthtmlgt
• ltheadgtlt!--Google 'no cache'--gt
• lt/headgt
• ltbodygt
• lt?php
• include("mysql-credentials.php")
• include("myutils.php")
• function h(s) return htmlentities (s)
• function db_open(hostName,userName,password,db
  Name)
• mysql_connect(hostName, userName, password) or
  die("Unable to connect to host hostName")
• mysql_select_db(dbName) or die( "Unable to
  select database dbName")

• function db_query(q)
• resultmysql_query(q)
• if( result NULL )
• mesg"Failed 'q' ". mysql_error()
• db_close()
• die( h( mesg) )
• return result
• function db_next_row(queryresult)
• return mysql_fetch_array(queryresult)
• function db_close()
• mysql_close()

21

• db_open(hostName,userName,password,dbName)
• db_query("DROP TABLE t1")
• db_query("CREATE TABLE t1 (txt varchar(255))")
• db_query("INSERT INTO t1 (txt) VALUES('a')")
• db_query("INSERT INTO t1 (txt) VALUES('b')")
• result db_query("SELECT FROM t1")
• while( onerow db_next_row(result))
• fieldh(onerow'txt')
• print "result is fieldltbrgt"
• db_close()

• lt?php
• ini_set ('display_errors', 1)
• ini_set ('error_reporting', E_ALL)
• include ('3mysql.php')
• ?gt
• mysql-credentials.php
• lt?php
• hostName "csil-projects.cs.uiuc.edu"
• userName "mynetid"
• password "mynetidmdbpw
• dbName "mynetid"//mynetid_db"
• ?gt

22

• userhelp cs.uiuc.edu

_at_
23
Querying the Database

• Goal specify what we want from our database
• Find all the employees who earn more than
  50,000 and pay taxes in Champaign-Urbana.
• Could write in C/Java, but bad idea
• Instead use high-level query languages
• Theoretical Relational Algebra, Datalog
• Practical SQL
• Relational algebra a basic set of operations on
  relations that provide the basic principles.

24
Motivation The Stack

• To use the "stack" data structure in my program,
  I need to know
• what a stack looks like
• what (useful) operations I can perform on a stack
• PUSH and POP
• Next, I look for an implementation of stack
• browse the Web
• find many of them
• choose one, say LEDA

25
Motivation The Stack (cont.)

• LEDA already implement PUSH and POP
• It also gives me a simple language L, in which to
  define a stack and call PUSH and POP
• S init_stack(int)
• S.push(3) S.push(5)
• int x S.pop()
• Can also define an expression of operations on
  stacks
• T init_stack(int)
• T.push(S.pop())

26
Motivation The Stack (cont.)

• To summarize, I know
• definition of stack
• its operations (PUSH, POP) that is, a stack
  algebra
• an implementation called LEDA, which tells me
  how to call PUSH and POP in a language L
• I can use these implementations to manipulate
  stacks
• LEDA hides the implementation details
• LEDA optimizes implementation of PUSH and POP

27
Now Contrast It with Rel. Databases

• To summarize, I know
• definition of stack
• its operations (PUSH, POP) that is, a stack
  algebra
• an implementation called LEDA, which tells me
  how to call PUSH and POP in a language L
• I can use these implementations to manipulate
  stacks
• LEDA hides the implementation details
• LEDA optimizes implementation of PUSH and POP

28
What is an Algebra

• Mathematical system consisting of
• Operands --- variables or values from which new
  values can be constructed.
• Operators --- symbols denoting procedures that
  construct new values from given values.

29
What is Relational Algebra?

• An algebra whose operands are relations or
  variables that represent relations.
• Operators are designed to do the most common
  things that we need to do with relations in a
  database.
• The result is an algebra that can be used as a
  query language for relations.

30
Relational Algebra at a Glance

• Operators relations as input, new relation as
  output
• Five basic RA operations
• Basic Set Operations
• union, difference (no intersection, no
  complement)
• Selection??
• Projection ?
• Cartesian Product X
• When our relations have attribute names
• Renaming??
• Derived operations
• Intersection, complement
• Joins (natural,equi-join, theta join, semi-join)

31
Five Basic RA Operations
32
Set Operations

• Union, difference
• Binary operations

33
Set Operations Union

• Union all tuples in R1 or R2
• Notation R1 U R2
• R1, R2 must have the same schema
• R1 U R2 has the same schema as R1, R2
• Example
• ActiveEmployees U RetiredEmployees

34
Set Operations Difference

• Difference all tuples in R1 and not in R2
• Notation R1 R2
• R1, R2 must have the same schema
• R1 - R2 has the same schema as R1, R2
• Example
• AllEmployees - RetiredEmployees

35
Selection

• Returns all tuples which satisfy a condition
• Notation ??c(R)
• c is a condition , lt, gt, and, or, not
• Output schema same as input schema
• Find all employees with salary more than 40,000
• ?Salary gt 40000 (Employee)

36
Find all employees with salary more than
40,000. ??Salary gt 40000 (Employee)
37
Projection

• Unary operation returns certain columns
• Eliminates duplicate tuples !
• Notation ??A1,,An (R)
• Input schema R(B1,,Bm)
• Condition A1, , An B1, , Bm
• Output schema S(A1,,An)
• Example project social-security number and
  names
• ? SSN, Name (Employee)

38
? SSN, Name (Employee)
39
Cartesian Product

• Each tuple in R1 with each tuple in R2
• Notation R1 x R2
• Input schemas R1(A1,,An), R2(B1,,Bm)
• Condition A1,,An B1,Bm ?
• Output schema is S(A1, , An, B1, , Bm)
• Notation R1 x R2
• Example Employee x Dependents
• Very rare in practice but joins are very common

40
(No Transcript)
41
Renaming

• Does not change the relational instance
• Changes the relational schema only
• Notation ? B1,,Bn (R)
• Input schema R(A1, , An)
• Output schema S(B1, , Bn)
• Example
• LastName, SocSocNo (Employee)

42
Renaming Example
Employee
Name
SSN
John
999999999
Tony
777777777

• LastName, SocSocNo (Employee)

LastName
SocSocNo
John
999999999
Tony
777777777
43
Derived RA Operations1) Intersection2) Most
importantly Join
44
Set Operations Intersection

• Difference all tuples both in R1 and in R2
• Notation R1 R2
• R1, R2 must have the same schema
• R1 R2 has the same schema as R1, R2
• Example
• UnionizedEmployees RetiredEmployees
• Intersection is derived
• R1 R2 R1 (R1 R2) why ?

45
Joins

• Theta join
• Natural join
• Equi-join
• Semi-join
• Inner join
• Outer join
• etc.

46
Theta Join

• A join that involves a predicate
• Notation R1 ? R2 where ? is a
  condition
• Input schemas R1(A1,,An), R2(B1,,Bm)
• A1,An B1,,Bm ?
• Output schema S(A1,,An,B1,,Bm)
• Derived operator
• R1 ? R2 ? ? (R1 x R2)

47
Theta-Join

• R3 R1 JOINC R2
• Take the product R1 R2.
• Then apply SELECTC to the result.
• As for SELECT, C can be any boolean-valued
  condition.
• Historic versions of this operator allowed only A
  theta B, where theta was , lt, etc. hence the
  name theta-join.

48
Example
Sells( bar, beer, price ) Bars( name, addr
) Joes Bud 2.50 Joes Maple
St. Joes Miller 2.75 Sues River
Rd. Sues Bud 2.50 Sues Coors 3.00
BarInfo Sells JOIN Sells.bar Bars.name Bars