1 / 17

03 – Database Design: Multiple Tables

03 – Database Design: Multiple Tables. 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

jennis
Download Presentation

03 – Database Design: Multiple Tables

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. 03 – Database Design: Multiple Tables

  2. 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

  3. Flat files: Data Duplication Field Record Track

  4. Problem: Data Duplication • takes up lots of space • can become inconsistent (misspellings) • difficult to search (misspellings) • difficult to change (need to change each instance)

  5. Relations (tables) Primary Key Foreign Key Track Artist

  6. 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

  7. Question: Prescriptions • Identify duplication and separate: Prescription

  8. Question: Solution Patient Prescription Drug

  9. People Database (with Hobbies) Person Hobby

  10. Entity-relationship diagrams Person Hobby • Each table in db • stores details of entity • shown as rectangular box • Relationships between tables • represent relationships between entities • shown as line between entities (boxes)

  11. Relationship Types A A A A B B B B • One-to-one • One-to-many • Many-to-one • Many-to-many • (can't be implemented in relational database)

  12. Question: Which relationship type? Person Hobby Person Hobby

  13. Example: Barbican Usage • Add Use table for use codes:

  14. Queries: Multiple Tables • Add multiple tables:

  15. 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

  16. 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

  17. 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.

More Related