DATABASES
7.2 RELATIONAL DATABASES

WHAT IS A RELATIONAL DATABASE

A relational database is a database that have two or more tables that are linked together. For example, a database on Formula 1 details could have a 'Team' table, a 'Drivers' table, a 'Points' table and a 'Circuits' table. In a relational database all of these tables could be linked together.

WHY HAVE RELATIONSHIPS

A single flat-file database can get messy when it contains various entities of different kinds. In the example in this video from LucidChart a database with the entities 'Customers', 'Products' and 'Orders' are split from a flat-file database to a relational database because of redundant data, duplicate data, a lack of a unique way to identify each attribute and lacks ensuing data integrity.

Imagine a school database in flat-file format, if a classroom change was needed, the database would have to find every student due to be in the classroom and change every occurrence. In a relational database you would simple change the classroom once in the 'Classes' table and because the students table relates to the classes table the new classroom details will automatically change for all instances.

RELATIONAL DATABASE CHARACTERISTICS

Relational databases have two or more tables that are related to each other. Tables will have a Primary key (PK) field which will provide each record a unique identifier, a rule of a primary key is that it must be unique, present and cannot change. Tables are often linked together using an intermediary table or bridge table(sometimes called junction table), for example a 'Student' table and a 'Classes' table might have an intermediary table called ' Lessons'. The 'Lessons' table would use the join from each table to provide details from each table within the database. 

Tables will also have Foreign Keys (FK), these are normally the primary key from another table that are used to make that relationship, it is often the case where you will have multiple foreign keys in one table. There are other types of keys frequently used in databases as can be seen in the terminology check below.

Later on we talk about database queries, these are a type of search or filter of data that produce a new table containing only the query details. These queries are normally written in Structured Query Language, often abbreviated to SQL.

When setting up a relational database indexes help with the smooth use of the system, similar to an index in a book they aid quick searching of data by pointing the search in the right direction.

​In order for data within the database to remain valid and reliable, databases often need constant maintenance and updates.

RELATIONSHIP JOIN TYPES

When tables are joined together we need to assign a join type. The join will determine the amount of records that are linked from each table, the amount can either be Zero, One or Many. For example if you are setting up a school database you might want to set up so one class can have many students(one-to-many), and each student can have many classes(many-to-many), but each class can only have one teacher (one-to-one).  
This video by 'Prescott Computer Guy' talks about setting up these join types.

RELATIONSHIP JOIN NOTATION

Whilst you can simple use the words to describe the join types such as one-to-one or one-to-many for example you should be familiar with the popular 'Crows Foot' notation of these join types. This type of join notation is frequently used in relationship diagrams.

Some databases such as MS Access will as standard will generally create a one-to-one join type when you set up the relationship, which you can at any point change as you please, others with need to be done manually. 

The join type if frequently known as the cardinality.

CREATING A RELATIONAL DATABASE

It is always very temping to dive right into your chosen database software and start building, however one thing with databases is that once it is set up it can be very difficult and time consuming to go back and make changes to the structure of the database. Spending time to plan the database will nearly always save time in the long run and give you a better change of producing a suitable end product.

First you will need to think about all of the data that will be held in the database, you will then need to take this data through a process called normalisation. The are many levels of normalisation however IB and A-Level recognise that we only need to take this process to 3rd normalised form. The next page helps you learn about normalisation and building your database structure.

SIMPLE VS COMPLEX QUERIES

A simple query is a request for information from a database that is straightforward and involves only a few tables and conditions. Simple queries can typically be executed quickly and are easy to understand and maintain.

A complex query, on the other hand, involves a large amount of data, multiple tables, and complex conditions and calculations. Complex queries can take longer to execute, are more difficult to understand and maintain, and can require a higher level of technical expertise to implement. Complex queries may also involve subqueries, multiple joins, and aggregations, and may involve the use of advanced query optimization techniques to improve performance.

Overall, the difference between a simple and complex query lies in the level of complexity of the data and the conditions used to retrieve the information, as well as the level of technical expertise required to implement the query.

REFERENTIAL INTEGRITY

​Referential integrity is a database concept that ensures the relationships between tables in a database are maintained consistently. It is a set of rules that define the relationships between the tables and enforce constraints on the data to ensure that the relationships are maintained even if the data is updated or deleted.

Referential integrity helps to prevent data inconsistencies and ensures that the data stored in the database is accurate and up-to-date. For example, if a record in one table references a record in another table, referential integrity ensures that the referenced record exists and is not deleted.

Referential integrity is typically enforced through the use of foreign keys, which are fields in one table that reference the primary key of another table. The foreign key constraints define the relationships between the tables and ensure that data is inserted, updated, and deleted in a consistent manner.

By enforcing referential integrity, organizations can reduce the risk of data inconsistencies and improve the accuracy and reliability of the data stored in their databases. This, in turn, supports better decision making and helps organizations to maintain a high-quality and consistent view of their data over time.

1. What is a relational database and how does it differ from a flat-file database?
2. What are the main components of a relational database and how do they work together to store and manage data?
3. How is data organized in a relational database and what are the benefits of this type of organization?
4. What are some common relational database management systems (RDBMS) and what are their key features?
5. How are relationships between data in a relational database established and maintained?
6. What are some advantages and disadvantages of using a relational database compared to other types of databases?

TERMINOLOGY CHECK

FLAT-FILE DATABASE: A database that stands alone with no relationships to other tables
RELATIONAL DATABASE: A database that have two or more tables that are linked together
DATA INTEGRITY: Refers to the accuracy and correctness of data. To ensure data integrity , data entry rules can be set up and timely database maintenance should be done to ensure data remains accurate and correct
REDUNDANT DATA: When the same piece of data is created in multiple tables then or other data in a table that is additional, not used and not needed. When the same piece of data is created in multiple tables this can lead to inconsistencies and difficulties in updating data, for example if if an item of data changes you should only have to change it in one area of the database, it would be very ineffective if you need to find the same item of data in multiple locations within the database.
​DATA CONCURRENCY:  The simultaneous access and modification of data by multiple users or processes in a computer system.

PRIMARY KEYS: Used to uniquely identify attributes within a table and allowing a link to other tables. 
FOREIGN KEYS: A key in a table that is the primary key in another table, used as a link between the two tables.
COMPOSITE KEYS: A unique identifier made up of two other identifiers, normally primary and foreign keys. If two foreign keys are used to make this key then it is often referred to as a compound key.
SECONDARY KEY:  A additional or alternative key to allow unique identification of a record.
CANDIDATE KEY:  A candidate key is any field that could be used to uniquely identify a record - such as a telephone number or email address.
UNIQUE KEY​: Used to uniquely identify attributes within a table but not used as a link to other tables

NEXT: NORMALISATION