Basic set of notes describing the design of relational databases:

http://www.cs.indiana.edu/classes/a348/spr2007/notes/Seven.html

Similar kind of notes: 

http://www.cs.indiana.edu/classes/a348/spr2007/notes/Eight.html

Here's the notes for Homework Three: 

http://www.cs.indiana.edu/~dgerman/homeworkThree.html

Introductory notes to be followed in today's class: 

http://www.cs.indiana.edu/classes/a348/spr2008/notes/weekThree/three.html

We'll discuss all of these in detail in class. 

Don't forget the text is here:

http://www.cs.indiana.edu/~dgerman/eowp06.pdf


----


Web programming

Web server

Unix

The server stays up all the time, listens to a port, takes requests, 
provides answers. Requests are in HTTP, so are the answers. Requests
are about resources. Resources could be in htdocs (.html, .jpeg, movies,
Excel spreadsheets) and in that case the resource is sent back as the
response. Resources could be in cgi-bin and in that case they are programs
and in that case the server runs them and sends back the output of the
program. One possible behaviour: program reads data from a lot of
sensors and reports back an aggregation of that. accuweather.com, e.g.

amazon.com works slightly differently, instead of "sensors" it has 
access into a huge inventory, that changes like the weather. 

side comment: the server deals with requests to htdocs or cgi-bin
differently. So if you set up a symbolic link in htdocs to a script
in cgi-bin you will be able to see the source code if you access the
symbolic link. 


Relational databases

A relational database is a collection of tables. 

A table has a number of columns, named, storing values of a certain type. 

One row in the table is going to be called: record (tuple). 

Example: roster of players

Player

username    lastName    firstName

egordon     Gordon
djwhite     White
ajratlif    Ratliff
mwhite      White
jellis
lstemler
abassett
jcrawfor

One or more of the columns in the table will serve a special role. 

In our example the username column will be the key. 

The key uniquely identifies a record. 

This table could be said to represent the entity "Player". 

There are two methods that we want to cover when designing databases:

a) intuitive approach

b) the more mathematical approach


The problem: we have a contest with three judges and a few players.
The judges are going to score the players and we want to record that.
We want to be able to take any questions on the grading afterwards.

 	      lbird  mjordan tkukoc
egordon 	8 	3 	6
jellis	 	7 	- 	8
djwhite 	- 	- 	-
lstemler 	9 	- 	2
mwhite 	 	- 	5 	4
jcrawfor 	6 	7 	-

 
Judge 

username  lastName   firstName
lbird
mjordan
tkukoc
kbryant

Tables are meant to dynamically be added records.

Tables have a fixed of number of columns, determined at design time. 

You can change them, but one seldom wants to allow that, because it's
a sign of poor planning. 

So how can we change this:

 	      lbird  mjordan tkukoc
egordon 	8 	3 	6
jellis	 	7 	- 	8
djwhite 	- 	- 	-
lstemler 	9 	- 	2
mwhite 	 	- 	5 	4
jcrawfor 	6 	7 	-

The purpose is to be able to store it in a table whose number of
columns does not change. 

Scores

playerUsername    judgeUsername   score
egordon            lbird           8
egordon            mjordan         3
egordon            tkukoc          6
jellis             lbird           7
jellis             mjordan         -  no need to add this
jellis             tkukoc          8
... and so on


playerUsername    judgeUsername   score
egordon            lbird           8
egordon            mjordan         3
egordon            tkukoc          6
jellis             lbird           7
jellis             tkukoc          8

So the problem seems to have the following database structure: 


Player                              Judge 



So the design steps are: 

1. Determine the entities in your problem 

2. Determine the relationships between the entities (that's how the
entities are interacting) The relationship here is Scores so the picture
becomes



   Player  ------ Scores ------- Judge
  *username      *playerUsername *username
   lastName      *judgeUsername   lastName
   firstName      score           firstName 



Library in town: 


   Book ------ Transactions ------ Patrons 
  *callNumber    *callNumber      *username
   isbn          *username         lastName
   title         ?dateOut          firstName
   author         dateReturned     address 
                  review 

What we are building is called an E-R (entity-relationship) diagram. 


The question for next time is: 

   -- how do we determine in this intuitive approach the key in a relationship table?