Transcript ASP: multiple tables - Mark Dixon`s web site

19 – Databases: Multiple Tables
Mark Dixon
Page 1
Session Aims & Objectives
• Aims
– To add dealing with multiple tables to your
understanding of databases
• Objectives,
by end of this week’s sessions, you should be able to:
– identify duplicated data in a single table
– split that table to reduce data redundancy
– generate SQL statements to (temporarily)
join tables, and use these in your code
Mark Dixon
Page 2
Flat files: Data Duplication
Field
Track
Record
Mark Dixon
Track Title
Artist Name
Country
Paranoid
Black Sabbath
UK
Falling in Love
Aerosmith
US
Pink
Aerosmith
US
Love in an Elevator
Aerosmith
US
Smooth Criminal
Alien Ant Farm
US
Meaning of Life
Disturbed
US
The Game
Disturbed
US
Voices
Disturbed
US
Down with the Sickness
Disturbed
US
Page 3
Problem: Data Duplication
• takes up lots of space
• can become inconsistent (misspellings)
• difficult to change (need to change each
instance)
• difficult to search (misspellings)
Mark Dixon
Page 4
Relations (tables)
Track
Track Title
Artist
ID
Paranoid
1
Falling in Love
2
Pink
2
Love in an Elevator
2
Smooth Criminal
3
Meaning of Life
4
The Game
4
Voices
4
Down with the Sickness 4
Artist
Artist Artist Name
ID
Country
1
Black Sabbath
UK
2
Aerosmith
US
3
Alien Ant Farm US
4
Disturbed
US
Primary Key
Foreign Key
Mark Dixon
Page 5
Normalisation
• Part of database design
• Process of breaking data down
• Codd
– 7 stages of normalisation
• Mathematical
• Difficult to apply stages
• Most professionals do it instinctively
Mark Dixon
Page 6
Question: Prescriptions
• Identify duplication and separate:
Prescription
Mark Dixon
Date
Surname
Forenames
Drug Name
6 Jan 04
Jones
Alison
Co-codamol
11 Jan 04
Smith
Bob
Tegretol
18 Jan 04
Hope
John
Co-codamol
5 Feb 04
Johnson
Sally
Co-codamol
8 Feb 04
Smith
Bob
Tegretol
10 Feb 04
Smith
Bob
Sorbitol
Page 7
Question: Solution
Patient
PatientID Surname Forenames
Prescription
1
Jones
Alison
2
Smith
Bob
3
Hope
John
4
Johnson
Sally
Date
PatientID DrugID
6 Jan 04
1
1
11 Jan 04
2
2
18 Jan 04
3
1
5 Feb 04
4
1
Drug
8 Feb 04
2
2
DrugID Drug Name
10 Feb 04
2
3
1
Co-codamol
2
Tegretol
3
Sorbitol
Mark Dixon
Page 8
People Database (with Hobbies)
Person
ID
1
2
3
4
5
Surname
Dixon
Smith
Jones
Bloggs
Anderson
Forenames
Mark
John
Sally
Fred
Genny
Phone
01752 232556
01752 111111
01752 888888
01752 123123
01752 987987
email
[email protected]
[email protected]
[email protected]
[email protected]
[email protected]
Hobby
HobbyID
1
2
3
4
5
6
Mark Dixon
Description
Archery
Herpetology
Music
Football
Rugby
Hitting people with swords
PersonID
1
1
1
2
2
1
Page 9
Entity-relationship diagrams
• Each table in db
– stores details of entity
• shown as rectangular box
Person
Hobby
•Relationships between tables
–represent relationships between entities
•shown as line between entities (boxes)
Mark Dixon
Page 10
Relationship Types
• One-to-one
A
B
• One-to-many
A
B
• Many-to-one
A
B
• Many-to-many
– (can't be implemented in relational database)
A
Mark Dixon
B
Page 11
Question: Which relationship type?
Person
ID
1
2
3
4
5
Surname
Dixon
Smith
Jones
Bloggs
Anderson
Forenames
Mark
John
Sally
Fred
Genny
Phone
01752 232556
01752 111111
01752 888888
01752 123123
01752 987987
email
[email protected]
[email protected]
[email protected]
[email protected]
[email protected]
Hobby
HobbyID
1
2
3
4
5
6
Mark Dixon
Description
Archery
Herpetology
Music
Football
Rugby
Hitting people with swords
PersonID
1
1
1
2
2
1
Person
Hobby
Page 12
SQL: Joining tables
Two tables
SELECT * FROM [Person], [Hobby];
Cartesian set
(all record
combinations):
Mark Dixon
ID
1
2
3
4
5
1
2
3
4
5
1
2
3
4
5
1
2
3
4
5
1
2
3
4
5
1
2
3
4
5
Su rn am e
Dix o n
Sm ith
Jo n es
Blo g g s
An d erso n
Dix o n
Sm ith
Jo n es
Blo g g s
An d erso n
Dix o n
Sm ith
Jo n es
Blo g g s
An d erso n
Dix o n
Sm ith
Jo n es
Blo g g s
An d erso n
Dix o n
Sm ith
Jo n es
Blo g g s
An d erso n
Dix o n
Sm ith
Jo n es
Blo g g s
An d erso n
Fo ren am es
M ark
Jo h n
Sally
Fred
Gen n y
M ark
Jo h n
Sally
Fred
Gen n y
M ark
Jo h n
Sally
Fred
Gen n y
M ark
Jo h n
Sally
Fred
Gen n y
M ark
Jo h n
Sally
Fred
Gen n y
M ark
Jo h n
Sally
Fred
Gen n y
Ph o n e
01752 232556
01752 111111
01752 888888
01752 123123
01752 987987
01752 232556
01752 111111
01752 888888
01752 123123
01752 987987
01752 232556
01752 111111
01752 888888
01752 123123
01752 987987
01752 232556
01752 111111
01752 888888
01752 123123
01752 987987
01752 232556
01752 111111
01752 888888
01752 123123
01752 987987
01752 232556
01752 111111
01752 888888
01752 123123
01752 987987
em ail
Ho b b yID
m ark . d ix o n @ p lym o u th . ac. u k
7
jo h n . sm ith @ jo h n . sm ith . ac. u k
7
sally. jo n es@ sally. jo n es. co m
7
fred . b lo g g s@ aaaaaa. co m
7
g en n y@ b b b b . cccc. co m
7
m ark . d ix o n @ p lym o u th . ac. u k
8
jo h n . sm ith @ jo h n . sm ith . ac. u k
8
sally. jo n es@ sally. jo n es. co m
8
fred . b lo g g s@ aaaaaa. co m
8
g en n y@ b b b b . cccc. co m
8
m ark . d ix o n @ p lym o u th . ac. u k
9
jo h n . sm ith @ jo h n . sm ith . ac. u k
9
sally. jo n es@ sally. jo n es. co m
9
fred . b lo g g s@ aaaaaa. co m
9
g en n y@ b b b b . cccc. co m
9
m ark . d ix o n @ p lym o u th . ac. u k
10
jo h n . sm ith @ jo h n . sm ith . ac. u k
10
sally. jo n es@ sally. jo n es. co m
10
fred . b lo g g s@ aaaaaa. co m
10
g en n y@ b b b b . cccc. co m
10
m ark . d ix o n @ p lym o u th . ac. u k
11
jo h n . sm ith @ jo h n . sm ith . ac. u k
11
sally. jo n es@ sally. jo n es. co m
11
fred . b lo g g s@ aaaaaa. co m
11
g en n y@ b b b b . cccc. co m
11
m ark . d ix o n @ p lym o u th . ac. u k
12
jo h n . sm ith @ jo h n . sm ith . ac. u k
12
sally. jo n es@ sally. jo n es. co m
12
fred . b lo g g s@ aaaaaa. co m
12
g en n y@ b b b b . cccc. co m
12
Descrip tio n
Perso n ID
Arch ery
1
Arch ery
1
Arch ery
1
Arch ery
1
Arch ery
1
Herp eto lo g y
1
Herp eto lo g y
1
Herp eto lo g y
1
Herp eto lo g y
1
Herp eto lo g y
1
M u sic
1
M u sic
1
M u sic
1
M u sic
1
M u sic
1
Fo o tb all
2
Fo o tb all
2
Fo o tb all
2
Fo o tb all
2
Fo o tb all
2
Ru g b y
2
Ru g b y
2
Ru g b y
2
Ru g b y
2
Ru g b y
2
Hittin g p eo p le w ith sw o rd s
1
Hittin g p eo p le w ith sw o rd s
1
Hittin g p eo p le w ith sw o rd s
1
Hittin g p eo p le w ith sw o rd s
1
Hittin g p eo p le w ith sw o rd s
1
Page 13
SQL: Joining tables
Two tables
SELECT *
FROM [Person], [Hobby]
WHERE [Person].[ID] = [Hobby].[PersonID];
Matching records
ID
Surname
Forenames
Phone
email
1
Dixon
Mark
01752 232556
[email protected]
1
Archery
1
1
Dixon
Mark
01752 232556
[email protected]
2
Herpetology
1
1
Dixon
Mark
01752 232556
[email protected]
3
Music
1
1
Dixon
Mark
01752 232556
[email protected]
6
Hitting people with swords
1
2
Smith
John
01752 111111
[email protected]
4
Football
2
2
Smith
John
01752 111111
[email protected]
5
Rugby
2
Mark Dixon
HobbyID
Description
PersonID
Page 14
SQL: Joining tables
SELECT [ID], [Surname]
FROM [Person], [Hobby]
WHERE [Person].[ID] = [Hobby].[PersonID];
ID
Surname
1 Dixon
1 Dixon
1 Dixon
1 Dixon
2 Smith
2 Smith
Mark Dixon
Page 15
SQL: DISTINCT records
SELECT DISTINCT [ID], [Surname]
FROM [Person], [Hobby]
WHERE [Person].[ID] = [Hobby].[PersonID];
ID
Surname
1 Dixon
2 Smith
Mark Dixon
Page 16
SQL: More
• Loads more:
– group by
– aggregate functions: average, count
– inner joins
– outer joins (left and right)
• Have a look at:
– http://www.w3schools.com/sql/sql_join.asp
Mark Dixon
Page 17
Tutorial Exercise: Music
• Task 1: Create the Music database (from the lecture) with the Track
and Artist tables.
• Task 2: Create a web page to display a list of Artists.
• Task 4: Change that web page, so that each artist name is a link to
another page, which displays all the tracks by that artist.
Hint: Use query strings to pass the artist ID between pages.
Mark Dixon
Page 18
Tutorial Exercise: Prescriptions
• Task 1: Create the Prescription database (from the lecture) with the
Prescription, Patient, and Drug tables.
• Task 2: Create a web page to display a list of Drugs.
• Task 3: Change that web page, so that each drug name is a link to
another page, which displays all the people using that drug.
Mark Dixon
Page 19