Relational databases

Searching and sorting a database

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

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

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.

The relationship (the foreign key)

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.

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.

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.

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.

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.

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.

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.

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.

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.