19 Databases Multiple Tables Mark Dixon Page 1

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

Track Relations (tables) 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 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. ID Surname Forenames Prescription 1 Jones Alison 2 Smith Bob 3 Hope John 4 Johnson Sally Date Patient. ID Drug. ID 6 Jan 04 1 1 11 Jan 04 2 2 18 Jan 04 3 1 5 Feb 04 4 1 8 Feb 04 2 2 Drug. ID Drug Name 10 Feb 04 2 3 1 Co-codamol 2 Tegretol 3 Sorbitol Mark Dixon Drug 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 mark. dixon@plymouth. ac. uk john. smith@john. smith. ac. uk sally. jones@sally. jones. com fred. bloggs@aaaaaa. com genny@bbbb. cccc. com Hobby. ID 1 2 3 4 5 6 Mark Dixon Description Archery Herpetology Music Football Rugby Hitting people with swords Person. ID 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 mark. dixon@plymouth. ac. uk john. smith@john. smith. ac. uk sally. jones@sally. jones. com fred. bloggs@aaaaaa. com genny@bbbb. cccc. com Hobby. ID 1 2 3 4 5 6 Mark Dixon Description Archery Herpetology Music Football Rugby Hitting people with swords Person. ID 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 Page 13

SQL: Joining tables Two tables SELECT * FROM [Person], [Hobby] WHERE [Person]. [ID] = [Hobby]. [Person. ID]; Matching records 1 Surnam e Dixon Forenam es Mark 1 Dixon Mark 2 Smith John ID Mark Dixon Phone email 01752 232556 01752 111111 mark. dixon@plymouth. a c. uk john. smith@john. smith. a c. uk Hobby. I D 1 Description Archery Person. I D 1 2 Herpetology 1 3 Music 1 6 1 4 Hitting people with swords Football 5 Rugby 2 2 Page 14

SQL: Joining tables SELECT [ID], [Surname] FROM [Person], [Hobby] WHERE [Person]. [ID] = [Hobby]. [Person. ID]; Surnam e 1 Dixon ID 1 Dixon 2 Smith Mark Dixon Page 15

SQL: DISTINCT records SELECT DISTINCT [ID], [Surname] FROM [Person], [Hobby] WHERE [Person]. [ID] = [Hobby]. [Person. ID]; 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. w 3 schools. com/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