Slides badge

Relational databases

Searching and sorting a database

  1. Find everyone who has 60 or more agility and 95 or more charisma and display their name, strength, agility and charisma from the BondCharacters table sorted by their names in reverse order (Z to A).

Searching and sorting a database

  1. Find everyone who has 60 or more agility and 95 or more charisma and display their name, strength, agility and charisma from the BondCharacters table sorted by their names in reverse order (Z to A).

Learning Intentions

  • Understand what a relational database is
  • Understand what relationships between tables in a database are
  • Understand the benefits of relational databases

Success Criteria

  • I can explain the term relational database
  • I can discuss the pros and cons of relational databases

Flat file database vs relational database

  • A flat file database has only one table.
  • A relational database has multiple tables linked together.
  • We have looked at flat file databases so far.

Relational databases

  • A relational database is a collection of tables containing data, which are related to each other through common items.
  • This provides maximum flexibility and is the most efficient database design.
  • Logical links between tables are called relationships.
Database icons created by Eucalyp - Flaticon

A flat file database

  • Order
  • Customer ID
  • First Name
  • Surname
  • Address
  • Telephone Number
  • Item
  • Quantity
  • Cost

A relational database

  • Customer
    • Customer ID
    • First Name
    • Surname
    • Address
    • Telephone Number
  • Order
    • Item
    • Quantity
    • Cost
    • CustomerID

The relationship (the foreign key)

What are the benefits of separating data?

  • Reduced duplication – by having the customers details separate from an order we don’t have to repeat all the information on each order, saving on database storage
  • Simplicity – it’s far easier to create an order record if we design it this way, we only need to add four fields instead of eight
  • Updating – if the customer’s details change, only one record is changed (the one in the Customer table) and not every single order.

Understanding relationships

  • A relational database simply wouldn’t be relational without relationships.
  • A relationship is a logical connection between one table and another. The connection is made between a field in one table and a field in another table.

Understanding relationships

  • One branch of a bank will contain many customer accounts.
  • By relating the BranchNumber field of the Branch table to the BrunchNumber field in the Accounts table, you are creating a “One-to-Many” relationship between the two tables.
  • There are three different types of relationship you can define between tables – One-to-Many, One-to-One, and Many-to-Many.

One-to-Many relationship

  • A One-to-Many relationship occurs when one table, known as the parent table, has many matching records in another table, known as the child table. In the example before, the Branch parent has many matching records in the Account child table.
  • One-to-Many relationships link ONE record in the first table to MANY records in the second table – this is the most common table relationship.

One-to-Many relationship

One-to-One relationship

  • A One-to-One relationship occurs when there is a direct match between a record in one table and a record in another.
  • For example, are two tables in the following examples - a customer table and an address table. The business logic behind the relationship states that a customer may only have one address at any time.
  • Every single record in the “Customer” table therefore directly matches one record in the “Address” table.
  • One-to-One relationships link ONE record in the first table to exactly ONE record in the second table.

One-to-One relationship

Many-To-Many relationship

  • A Many-to-Many relationship occurs when one table can have many possible matches with records of another table, and vice versa. To model this relationship in Access, you must create a third table, known as a junction table, and reduce the Many-to-Many relationship between two tables into two One-to-Many relationships – using the junction table as the intermediary:
  • Many-to-Many relationships link MANY records in the first table to MANY records in the second table.
  • A junction table keeps track of related records in two other tables.

Primary keys

  • A field that uniquely identifies individual records is called a primary key. There must only be one field that could possibly be a primary key within a table.
  • If you have more than one field that could be a primary key, you probably need to split the table into two or more tables.
Key icons created by Smashicons - Flaticon

Foreign keys

  • When you relate two tables you are making a link between a primary key and a foreign key.
  • A foreign key is the field in a table that exactly matches the primary key field of another table.
  • For example, to relate the “Cars” table with another table called “CarSales”, containing records of all cars sold in a particular dealership, then you could include a “Vehicle Identification Number”  field in the “CarSales” table as the foreign key. By relating the two VIN fields you are linking the information in both tables in a logical manner.

Keys

Primary keys

  • A primary key is assigned to a field which is used to uniquely identify a particular piece of information on the database so that you can find a particular record in a file as quickly as possible.
  • E.g. The Scottish Qualifications Authority will have a database of all the students who are sitting National or Higher exams. The primary key field in the database would be the candidate number as  each number can only belong to one person - therefore it is unique to them.

Creating relationships

Creating relationships

Ensure that you Enforce Referential Integrity

Creating relationships

Download the Vet database. Add in a relationship between the Vet and Pet and the Pet and the Pet and Customer.
 
Complete the search tasks in the worksheet.

Task

Presentation Overview
Close
JB
Relational databases
© 2020 - 2024 J Balfour
13:41 | 29-04-2024
Join Live Session
Start Remote
Save Progress
Slideshow Outline
Presenter Mode
Widget Screen
Canvas Controls
Random Selector
Timer
Volume Meter
Binary Converter
Python Editor
Show Knox 90
Provide Feedback
Help
!
Keywords
    DragonDocs Management
    Random selector
    Sections
      Binary conversion
      Denary to binary conversion
      Binary to denary conversion
      Feedback 👍
      Accessibility

      Apply a filter:

      Dark Mode
      Bold Text
      ×
      All slideshow files