dbwebsites 2.1 making database backed websites session 2 the sql… where do we put the data?
TRANSCRIPT
dbwebsites 2.1
Making Database backed Websites
Session 2
The SQL…Where do we put the data?
dbwebsites 2.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.
dbwebsites 2.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.
dbwebsites 2.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.
First Name
Char(20)
Surname
Char(20)
Human
Enum(yes,no)
Age
Int
Phone
Char(15)
Char(70)
Peter Bagnall Yes 29 01524 39145 [email protected]
Mickey Mouse No 74 NULL [email protected]
dbwebsites 2.5
Important Data Types
BOOLEAN
INT(precision)
FLOAT(precision)
DATE, DATETIME, TIMESTAMP[(M)] , TIME, YEAR[(2|4)]
CHAR(M)
VARCHAR(M)
BLOB
TEXT
ENUM('value1','value2',...), SET('value1','value2',...)
dbwebsites 2.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?”.
nameVARCHAR (40)
addressINT(9)
Peter Bagnall 1
Mickey Mouse 2
Goofy 2
addressVARCHAR (200)
idINT(9)
Castle Park… 1
Disneyland 2
dbwebsites 2.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.
dbwebsites 2.8
Schemas - Simplest
Just a single table.
Works for simple lists of records.
First Name
Char(20)
Surname
Char(20)
Human
Enum(yes,no)
Age
Int
Phone
Char(15)
Char(70)
Peter Bagnall Yes 29 01524 39145 [email protected]
Mickey Mouse No 74 NULL [email protected]
But people may have home phone, work phone, and mobile phone.You could add more fields (aka columns) for the extra phone numbers, or…
dbwebsites 2.9
Schemas – One to Many
Can deal with a record of one type relating to several records of another type.
First Name
Char(20)
Surname
Char(20)
Human
Enum(yes,no)
Age
Int(3)
ID
Int(8)
Char(70)
Peter Bagnall Yes 29 1 [email protected]
Mickey Mouse No 74 2 [email protected]
Phone Number
Char(15)
Type
Enum(‘home’,’mobile’,’work’,’work fax’)
owner
Int(8)
01524 39145 home 1
07984 168 586 mobile 1
01524 592795 work 1
01524 593608 work fax 1
001 555 956 784 home 2
dbwebsites 2.10
Types of Relationships
One to OneUsername <-> Password
One to ManyCustomer -> Orders
Many to ManyActors <-> Movies
dbwebsites 2.11
Schemas – Many to Many
Actors
Name
varchar(50)
DoB
Date
ID
Int(8)
Michael Caine 14 March 1933 1
Donald Sutherland 17 July 1935 2
Movies
Title
Varchar(200)
ReleaseDate
Year(4)
ID
Int(8)
The Italian Job 1969 1
Outbreak 1995 2
Get Carter 2000 3
Get Carter 1971 4
RolesMovie
Int(8)
Actor
Int(8)
Played
Varchar(200)
1 1 Charlie Croker
2 2 Maj. Gen. Donald McClintock
3 1 Cliff Brumby
4 1 Jack Carter
dbwebsites 2.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.
dbwebsites 2.13
SQL – Structured Query Language
SQL is the language used to manipulate databases.
mysql> show databases;+----------+| Database |+----------+| mysql |+----------+1 row in set (0.00 sec)
dbwebsites 2.14
Create a database
mysql> create database movies;Query OK, 1 row affected (0.00 sec)
Now there is a database you can use
dbwebsites 2.15
Create some tables
Next you need to create some tables in the database to hold actual data. First the actors…
mysql> create table actors (name varchar(50), dob date, id INT(8) AUTO_INCREMENT primary key);
Query OK, 0 rows affected (0.05 sec)
dbwebsites 2.16
Create more tables
Next the movies…
mysql> create table movies (title varchar(200), releasedate YEAR, id INT(8) AUTO_INCREMENT primary key);
Query OK, 0 rows affected (0.03 sec)
dbwebsites 2.17
Create last table
Finally the roles, which links the other two tables.
mysql> create table roles (movie int(8), actor int(8), played varchar(200));
Query OK, 0 rows affected (0.03 sec)
dbwebsites 2.18
Check the tables
You can look at the structure of a table using…
mysql> 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)
dbwebsites 2.19
Insert some data into the database
Tell it about some actors…
mysql> 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.
dbwebsites 2.20
Insert more data into the database
Tell it about some movies…
mysql> 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.
dbwebsites 2.21
Which IDs were used?
See what actually went into the database…
mysql> select id, name from actors; +----+---------------+| id | name |+----+---------------+| 1 | Michael Caine |+----+---------------+1 row in set (0.00 sec)3
mysql> select id, title from movies;+----+-----------------+| id | title |+----+-----------------+| 1 | The Italian Job |+----+-----------------+1 row in set (0.00 sec)
dbwebsites 2.22
Insert more data into the database
Now link the actors to the movies…
mysql> 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.
dbwebsites 2.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…
mysql> select id from movies where title=“The Italian Job”;+----+| id |+----+| 1 |+----+1 row in set (0.00 sec)
dbwebsites 2.24
Answering useful questions
Next link the find any actor id’s which are listed in the roles as having been in The Italian Job.
mysql> select actor from roles where movie=1;+-------+| actor |+-------+| 1 |+-------+1 row in set (0.00 sec)
dbwebsites 2.25
Answering useful questions
Finally find which actor has the appropriate id.
mysql> select name from actors where id=1;
+---------------+| name |+---------------+| Michael Caine |+---------------+1 row in set (0.00 sec)
dbwebsites 2.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…
mysql> 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)
dbwebsites 2.27
Questions?
Presentation online at…
http://people.surfaceeffect.com/pete/
tech/howitworks/dbwebsites/