Transcript Database Tables - Mark Dixon`s web site

14 – Databases: Multiple Tables
Mark Dixon, SoCCE
SOFT 131
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, SoCCE
SOFT 131
Page 2
Flat files: Data Duplication
Field
Track
Record
Mark Dixon, SoCCE
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
SOFT 131
Page 3
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
Artist
Artist Artist Name
ID
Country
1
Black Sabbath
UK
2
Aerosmith
US
3
Alien Ant Farm US
4
Disturbed
US
Primary Key
Down with the Sickness 4
Foreign Key
Mark Dixon, SoCCE
SOFT 131
Page 4
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, SoCCE
SOFT 131
Page 5
Question: Prescriptions
• Identify duplication and separate:
Prescription
Mark Dixon, SoCCE
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
SOFT 131
Page 6
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, SoCCE
SOFT 131
Page 7
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, SoCCE
Description
Archery
Herpetology
Music
Football
Rugby
Hitting people with swords
SOFT 131
PersonID
1
1
1
2
2
1
Page 8
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, SoCCE
SOFT 131
Page 9
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, SoCCE
B
SOFT 131
Page 10
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, SoCCE
Description
Archery
Herpetology
Music
Football
Rugby
Hitting people with swords
PersonID
1
1
1
2
2
1
SOFT 131
Person
Hobby
Page 11
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, SoCCE
HobbyID
SOFT 131
Description
PersonID
Page 12
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, SoCCE
SOFT 131
Page 13
SQL: DISTINCT records
SELECT DISTINCT [ID], [Surname]
FROM [Person], [Hobby]
WHERE [Person].[ID] = [Hobby].[PersonID];
ID
Surname
1 Dixon
2 Smith
Mark Dixon, SoCCE
SOFT 131
Page 14
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. Use
query strings to pass the artist ID.
Mark Dixon, SoCCE
SOFT 131
Page 15
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 4: 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, SoCCE
SOFT 131
Page 16