Transcript multiple arrays and database tables

LIS651 lecture 5
multiple arrays and databases
Thomas Krichel
2010-02-19
databases
• Databases are collection of data with some
organization to them.
• The classic example is the relational database.
• But not all database need to be relational
databases.
relational databases
• A relational database is a set of tables. There
may be relations between the tables.
• Each table has a number of record. Each record
has a number of fields.
• When the database is being set up, we fix
– the size of each field
– relationships between tables
example: Movie database
ID
M1
M2
M3
M4
M5
M6
| title
| Gone with the wind
| Room with a view
| High Noon
| Star Wars
| Alien
| Blowing in the Wind
| director
| F. Ford Coppola
| Coppola, F Ford
| Woody Allan
| Steve Spielberg
| Allen, Woody
| Spielberg, Steven
• Single table
• No relations between tables, of course
| date
| 1963
| 1985
| 1974
| 1993
| 1987
| 1962
problem with this database
• All data wrong, but this is just for illustration.
• Name covered inconsistently. There is no way to
find films by Woody Allan without having to go
through all spelling variations.
• Mistakes are difficult to correct. We have to
wade through all records, a masochist’s
pleasure.
Better movie database
ID | title
M1| Gone with the wind
M2| Room with a view
M3| High Noon
M4 | Star Wars
M5| Alien
M6| Blowing in the Wind
| director
| D1
| D1
| D2
| D3
| D2
| D3
ID
D1
D2
D3
| birth year
| 1942
| 1957
| 1942
| director name
| Ford Coppola, Francis
| Allan, Woody
| Spielberg, Steven
| year
| 1963
| 1985
| 1974
| 1993
| 1987
| 1962
Relational database
• We have a one to many relationship between
directors and film
– Each film has one director
– Each director has produced many films
• Here it becomes possible for the computer
– To know which films have been directed by Woody
Allen
– To find which films have been directed by a director
born in 1942
Many-to-many relationships
• Each film has one director, but many actors star
in it. Relationship between actors and films is a
many to many relationship.
• Here are a few actors
ID
A1
A2
A3
| sex
|f
|m
|f
| actor name
| Brigitte Bardot
| George Clooney
| Marilyn Monroe
| birth year
| 1972
| 1927
| 1934
Actor/Movie table
actor id
| movie id
A1
| M4
A2
| M3
A3
| M2
A1
| M5
A1
| M3
A2
| M6
A3
| M4
… as many lines as required
Many-to-many relationships
• Each film has one director, but many actors star
in it. Relationship between actors and films is a
many to many relationship.
• Here are a few actors
ID
A1
A2
A3
| sex | actor name
|f
| Brigitte Bardot
| m | George Clooney
|f
| Marilyn Monroe
| birth year
| 1972
| 1927
| 1934
Many-to-many relationships
• Each film has one director, but many actors star
in it. Relationship between actors and films is a
many to many relationship.
• Here are a few actors
ID
A1
A2
A3
| sex
|f
|m
|f
| actor name
| Brigitte Bardot
| George Clooney
| Marilyn Monroe
| birth year
| 1972
| 1927
| 1934
Actor/Movie table
actor id
| movie id
A1
| M4
A2
| M3
A3
| M2
A1
| M5
A1
| M3
A2
| M6
A3
| M4
… as many lines as required
databases in libraries
• Relational databases dominate the world of
structured data
• But not so popular in libraries
– Slow on very large databases (such as catalogs)
– Library data has nasty ad-hoc relationships, e.g.
• Translation of the first edition of a book
• CD supplement that comes with the print version
Difficult to deal with in a system where all relations and
field have to be set up at the start, can not be changed
easily later.
databases in web sites
• Lots of active web sites are driven by relational
databases. All large active web sites are.
• The design of a active web site first involves
looking at databases.
• In a shop situation, we have the following tables
–
–
–
–
customers
products
orders
orders_products
for multiple to multiple relationship between orders and products.
SQL
• SQL, also pronounced sequel, stands for
"structured query language".
• It is a standard language for querying database.
• In database speak a query is anything one can do
to a database.
mySQL
• They are a very successful, open-source vendor
of SQL software.
• Their product is basically freely available.
• We will learn the mySQL dialect of SQL.
phpmyadmin
• phpmyadmin is a set of PHP scripts that create a
general purpose interface to work with a mySQL
database.
• It is written in PHP.
• It lives at http://wotan.liu.edu/phpmyadmin.
• You need an account. This is not your wotan
account, but a permission to use a database on
the mySQL server running at wotan.
arrays and tables
• Arrays seem to cause the most confusion in
student understanding.
• An array is just a way for PHP to address multiple
things as one variable.
• Arrays can be of multiple dimensions.
• This is typically the case when the array
represents the contents of a table.
example
• Here is an example table
name
Bruch Festbock
Balitka 8
Budweiser
type
dark
wheat
light
brewer
Bruch
Baltika
A.-B.
rating price
good 1.18
good 0.88
lousy 0.99
• typically, records appear in lines and fields in
columns.
arrays and tables
• Arrays seem to cause the most confusion in
student understanding.
• An array is just a way for PHP to address multiple
things as one variable.
• Arrays can be of multiple dimensions.
• This is typically the case when the array
represents the contents of a table.
example
• Here is an example table
name
Bruch Festbock
Balitka 8
Budweiser
type
dark
wheat
light
brewer
Bruch
Baltika
A.-B.
rating price
good 1.18
good 0.88
lousy 0.99
• typically, records appear in lines and fields in
columns.
one way to set out the table
$beers[0]['name']='Bruch Landbock';
$beers[0]['type']='dark';
$beers[0]['brewer']='Bruch‘;
….
$beers[2]['price']=0.99;
• Here, records are a numeric array. Fields are
string arrays that are members of the numeric
array.
• What instruction would improve the rating of
Budweiser?
another way …
$beer=array( 'name'=> 'Bruch Landbock', type=>'dark',
'brewer'=> 'Bruch', 'rating'=>'good', price=>'1.18');
$beers[]=$beer;
…
$beer=array( 'name'=>'Budweiser', type=>'light',
'brewer'=>'A.-B.', 'rating'=>'lousy', price=>0.99);
$beers[]=$beer;
• This will give the same array as before.
yet another way, as a matrix
$names=array('Bruch Landbock', 'Baltika 8','Budweiser');
$types=array( 'dark', 'wheat', 'light' );
$brewers=array( 'Bruch', 'Baltika', 'A.-B.');
$ratings=array( 'good', 'good', 'lousy');
$prices=array(1.18,0.88,0.99);
$beers=array( $names, $types,$brewers,$ratings,
$prices);
• What instruction would improve the rating of
Budweiser?
another way to set out the table
$names=array('Bruch Landbock', 'Baltika 8','Budweiser');
$types=array( 'dark', 'wheat', 'light' );
$brewers=array( 'Bruch', 'Baltika', 'A.-B.');
$ratings=array( 'good', 'good', 'lousy');
$prices=array(1.18,0.88,0.99);
$beers=array( 'name'=>$names, 'type'=>$types,
'brewer'=> $brewers, 'rating'=>$ratings,
'price'=>$prices);
• What instruction would improve the rating of
Budweiser?
another way to set out the table
$names=array('Bruch Landbock', 'Baltika 8','Budweiser');
$types=array( 'dark', 'wheat', 'light' );
$brewers=array( 'Bruch', 'Baltika', 'A.-B.');
$ratings=array( 'good', 'good', 'lousy');
$prices=array(1.18,0.88,0.99);
$beers=array( 'name'=>$names, 'type'=>$types,
'brewer'=> $brewers, 'rating'=>$ratings,
'price'=>$prices);
• What instruction would improve the rating of
Budweiser?
multiple arrays
• Example
$products[0]['name']="Grosswald Pilsener";
$products[0]['price']=1.56;
$products[1]['name']="Grosswald Export";
$products[1]['price']=1.34;
$products[2]['name']="Bruch Landbier";
$products[2]['price']=1.22;
http://openlib.org/home/krichel
Thank you for your attention!
Please switch off machines b4 leaving!