Transcript Design
03 – Database Design: 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 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 search (misspellings) • difficult to change (need to change each instance) 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 Example: Barbican Usage • Add Use table for use codes: Mark Dixon Page 13 Queries: Multiple Tables • Add multiple tables: Mark Dixon Page 14 Tutorial Exercise: Music • Task 1: Create the Music database (from the lecture) with the Track and Artist tables. • Task 2: Create a query to list all track titles by artists from US Mark Dixon Page 15 Tutorial Exercise: Prescriptions • Task 1: Create the Prescription database (from the lecture) with the Prescription, Patient, and Drug tables. • Task 2: Create query to list all patients who have been prescribed Tegretol Mark Dixon Page 16 Tutorial Exercises: Barbican • Task 1: Download the Barbican database. • Task 2: Add a new table for Use codes. • Task 3: Create a query to display 1895 data with Description of use (not code). • Task 4: Create a query to list all houses used for industry in 1955. Mark Dixon Page 17