21 Databases Multiple Tables Mark Dixon 1 Questions

21 – Databases: Multiple Tables Mark Dixon 1

Questions: Databases • How many records are in the following table? • How many fields does the following table have? Mark Dixon 6 4 2

Questions: SQL • Write an SQL statement to display the name and land mass of all countries in Africa. SELECT Name, Land Mass FROM Country WHERE Continent = 'Africa'; Mark Dixon 3

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 a suitable primary key for a table – identify duplicated data in a single table – split that table to reduce data redundancy, using a suitable foreign key – generate SQL statements to (temporarily) join tables, and use these in your code Mark Dixon 4

Data Duplication • Look for repeating data: Track 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 5

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 6

Solution: Normalisation • Part of database design • Process of breaking data down (splitting) • Codd – 7 stages of normalisation • Mathematical • Difficult to apply stages • Most professionals do it instinctively Mark Dixon 7

Relations (tables) Track Title Artist Name Count ry Black Aerosmith Sabbath Aerosmith UK 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 Paranoid Falling in Love Pink Mark Dixon US 8

Relations (tables) Track Title Artist Paranoid Artist ID Falling in Love 1 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 Mark Dixon I D Artist Name Count ry 1 Black Sabbath UK 9

Relations (tables) Track Title Artist Paranoid Artist ID Falling in Love 1 Aerosmith 2 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 Mark Dixon I Artist D Name Count ry 1 Black Sabbath UK 2 Aerosmith US 10

Relations (tables) Track Title Artist Paranoid Artist ID I D Artist Name Count ry Falling in Love 1 1 UK Pink 2 Black Sabbath 2 Aerosmith US 2 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 Mark Dixon 11

Relations (tables) Track Title Artist Paranoid Artist ID I D Artist Name Count ry Falling in Love 1 1 UK Pink 2 Black Sabbath Love in an Elevator 2 2 Aerosmith US Smooth Criminal 2 Alien Ant Farm US Meaning of Life Disturbed US The Game Disturbed US Voices Disturbed US Down with the Sickness Disturbed US Mark Dixon 12

Relations (tables) Track Title Artist Paranoid Artist ID I D Artist Name Count ry Falling in Love 1 1 UK Pink 2 Black Sabbath Love in an Elevator 2 2 Aerosmith US Smooth Criminal 2 3 US Meaning of Life 3 Disturbed US Alien Ant Farm The Game Disturbed US Voices Disturbed US Down with the Sickness Disturbed US Mark Dixon 13

Relations (tables) Track Title Artist Paranoid Artist ID I D Artist Name Count ry Falling in Love 1 1 UK Pink 2 Black Sabbath Love in an Elevator 2 2 Aerosmith US Smooth Criminal 2 3 US Meaning of Life 3 Alien Ant Farm The Game 4 Disturbed US Voices Disturbed US Down with the Sickness Disturbed US Mark Dixon 14

Relations (tables) Track Title Artist Paranoid Artist ID I D Artist Name Count ry Falling in Love 1 1 UK Pink 2 Black Sabbath Love in an Elevator 2 2 Aerosmith US Smooth Criminal 2 3 US Meaning of Life 3 Alien Ant Farm The Game 4 4 Disturbed US Voices 4 Disturbed US Down with the Sickness Disturbed US Mark Dixon 15

Relations (tables) Track Title Artist Paranoid Artist ID I D Artist Name Count ry Falling in Love 1 1 UK Pink 2 Black Sabbath Love in an Elevator 2 2 Aerosmith US Smooth Criminal 2 3 US Meaning of Life 3 Alien Ant Farm The Game 4 4 Disturbed US Voices 4 Down with the Sickness 4 Disturbed Mark Dixon US 16

Relations (tables) Foreign Key Track Title Artist ID Paranoid 1 Falling in Love 2 Pink Primary Key Artist I D Artist Name Count ry 1 Black Sabbath UK 2 2 Aerosmith US Love in an Elevator 2 3 Alien Ant Farm US 4 Disturbed US Smooth Criminal 3 Meaning of Life 4 The Game 4 Voices 4 Down with the Sickness 4 Mark Dixon 17

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 18

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 19

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 20

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 21

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 22

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 23

SQL: Joining tables Two tables SELECT * FROM Person, Hobby; Cartesian set (all record combinations): Mark Dixon 24

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 25

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 26

Question: SQL Joining Tables • Write an SQL query to join the following: Track Mark Dixon Artist Track Title Artist ID I D Artist Name Count ry Paranoid 1 1 UK Falling in Love 2 Black Sabbath Pink 2 2 Aerosmith US Love in an Elevator 2 3 Alien Ant Farm US 4 Disturbed US Smooth Criminal 3 Meaning of Life 4 The Game 4 Voices 4 Down with the Sickness 4 27

MS Access: SQL Query • Open database • Create new query • Select SQL item from drop-down list SQL View Mark Dixon Data. Sheet View 28

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 29

Tutorial Exercise: Countries • LEARNING OBJECTIVE: avoid duplicate data by splitting a table create a web-page to temporarily re-join and display the data • Task 1: Create the Countries database (from the lecture) with the Country table. • Task 2: Split the Country table to remove duplicate data. identify the duplicate data put it in a separate table create a primary key for the new table create a foreign key in the old table that links to the new table • Task 3: Create a web page to display a list of Countries with associated data (Population, Land Mass, and Continent). • Task 4: Add a text box and button to your page. When the user clicks the button the page should display only countries that match the continent entered in the text box (e. g. if the user types in Africa and clicks the button, only the African countries should be displayed). Mark Dixon 30

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 31

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 32