Making Database backed Websites

1
Making Database backed Websites

• Session 2
• The SQL
• Where do we put the data?

2
How do databases work?

• Were going to look at systems known as Relational
  Databases.
• There are other more modern systems, but websites
  almost exclusively use relational systems.
• You may hear the terms DBMS and RDBMS, which
  stand for Database Management System and
  Relational Database Management System
  respectively.

3
How do databases work?

• An DBMS is a server which is responsible for
  managing one or more databases.
• Database clients can connect to the server to
  extract data from the DB or insert data into the
  DB.
• For a website to use a database it needs a
  database client which it can use to extract data
  from the database, and use it to generate web
  pages. This is built into PHP, which will be
  covered in the last session.

4
How do databases work?

• One DBMS can manage many databases.
• Each database contains a number of tables.
• Tables have rows of data. Each row is a complete
  record.
• Each column in the table can contain a certain
  type of data element, eg numbers, text, dates,
  etc.

5
Important Data Types

• BOOLEAN
• INT(precision)
• FLOAT(precision)
• DATE, DATETIME, TIMESTAMP(M) , TIME,
  YEAR(24)
• CHAR(M)
• VARCHAR(M)
• BLOB
• TEXT
• ENUM('value1','value2',...), SET('value1','value2'
  ,...)

6
Why Relational?

• Data in different tables can be related
• Example
• A person lives at an address. Several people may
  live at the same address.
• By joining the people table and address tables
  together you can answer Where does X live? and
  Who lives at Y?.

7
Schemas

• The schema of a database is the design of the
  tables, and the way they join together.
• Designing the schema for a database is important,
  since it can be very hard to change it once a
  website is using it without a lot of downtime or
  programming effort.

8
Schemas - Simplest

• Just a single table.
• Works for simple lists of records.

But people may have home phone, work phone, and
mobile phone. You could add more fields (aka
columns) for the extra phone numbers, or
9
Schemas One to Many

• Can deal with a record of one type relating to
  several records of another type.

10
Types of Relationships

• One to One
• Username lt-gt Password
• One to Many
• Customer -gt Orders
• Many to Many
• Actors lt-gt Movies

11
Schemas Many to Many
12
Connect to the DB server

• You can access the database interactively through
  the windows command line.
• mysql -u user -h host p
• User is the username on the database.
• Host is the computer which the database is
  running on.
• Once connected you use SQL to give commands to
  the database.

13
SQL Structured Query Language

• SQL is the language used to manipulate databases.
• mysqlgt show databases
• ----------
• Database
• ----------
• mysql
• ----------
• 1 row in set (0.00 sec)

14
Create a database

• mysqlgt create database movies
• Query OK, 1 row affected (0.00 sec)
• Now there is a database you can use

15
Create some tables

• Next you need to create some tables in the
  database to hold actual data. First the actors
• mysqlgt create table actors (name varchar(50), dob
  date, id INT(8) AUTO_INCREMENT primary key)
• Query OK, 0 rows affected (0.05 sec)

16
Create more tables

• Next the movies
• mysqlgt create table movies (title varchar(200),
  releasedate YEAR, id INT(8) AUTO_INCREMENT
  primary key)
• Query OK, 0 rows affected (0.03 sec)

17
Create last table

• Finally the roles, which links the other two
  tables.
• mysqlgt create table roles (movie int(8), actor
  int(8), played varchar(200))
• Query OK, 0 rows affected (0.03 sec)

18
Check the tables

• You can look at the structure of a table using
• mysqlgt describe actors
• --------------------------------------------
  ------------
• Field Type Null Key Default
  Extra
• --------------------------------------------
  ------------
• name varchar(50) YES NULL
  
• dob year(4) YES NULL
  
• id int(8) PRI NULL
  auto_increment
• --------------------------------------------
  ------------
• 3 rows in set (0.05 sec)

19
Insert some data into the database

• Tell it about some actors
• mysqlgt insert into actors (name, dob) values
  (Michael Caine,1935-07-17)
• 1 row in set (0.05 sec)
• Dates are entered (most easily) in Japanese
  format.
• The id field is not entered since it is
  automatically generated by the database.

20
Insert more data into the database

• Tell it about some movies
• mysqlgt insert into movies (title, releasedate)
  values (The Italian Job,1969)
• 1 row in set (0.05 sec)
• The id field is not entered since it is
  automatically generated by the database.

21
Which IDs were used?

• See what actually went into the database
• mysqlgt select id, name from actors
• -------------------
• id name
• -------------------
• 1 Michael Caine
• -------------------
• 1 row in set (0.00 sec)3
• mysqlgt select id, title from movies
• ---------------------
• id title
• ---------------------
• 1 The Italian Job
• ---------------------
• 1 row in set (0.00 sec)

22
Insert more data into the database

• Now link the actors to the movies
• mysqlgt insert into roles (movie, actor, played)
  values(1,1,Charlie Croker)
• 1 row in set (0.05 sec)
• The first 1 entered is the id of the movie.
• The second 1 is the id of the actor.

23
Answering useful questions

• Now that we have a database, with data in it we
  can do useful queries. Say we want to know who
  played in The Italian Job. First find the movie
  id
• mysqlgt select id from movies where titleThe
  Italian Job
• ----
• id
• ----
• 1
• ----
• 1 row in set (0.00 sec)

24
Answering useful questions

• Next link the find any actor ids which are
  listed in the roles as having been in The Italian
  Job.
• mysqlgt select actor from roles where movie1
• -------
• actor
• -------
• 1
• -------
• 1 row in set (0.00 sec)

25
Answering useful questions

• Finally find which actor has the appropriate id.
• mysqlgt select name from actors where id1
• ---------------
• name
• ---------------
• Michael Caine
• ---------------
• 1 row in set (0.00 sec)

26
All in one query!

• Or we can combine all those queries into a single
  query and have the database do all the work for
  us
• mysqlgt select actors.name from actors, roles,
  movies where movies.title "The Italian Job" and
  movies.id roles.movie and roles.actor
  actors.id
• ---------------
• name
• ---------------
• Michael Caine
• ---------------
• 1 row in set (0.00 sec)

27
Questions?

• Presentation online at
• http//people.surfaceeffect.com/pete/
• tech/howitworks/dbwebsites/