Lesson 2. Relational Databases

Lesson Objective

Understand the purpose of a relational database.
Explain the need for primary and foreign keys and the benefits of a relational database.
Create entity relationship diagrams and explain how relational databases eliminate redundancy.

Lesson Notes

Relational Database

A relational data consists of two or more tables, that are connected through a common field. Each table will have a primary key that can be used to identify a record of data in a table.

Entity Relationship Diagram

Entity: an object, person, or event that data is stored for (i.e. a database table).

Relationship: Is the link between two entities. It specifies the rules of how the records within tables can be linked.

Types of Relationship Link:

ONE to ONE: e.g. Husband can only have one wife and a wife can only have one husband.

ONE to MANY: e.g. A team has many players but a player belongs to only one team.

MANY to MANY: e.g. A product has many components and a component can be used in many products.

Data Inconsistency and Redundancies

The data in this database contains redundant data. Some mission commanders (Terry Watts) feature more than once in the table. Repeated data is redundant data. Repeating data can lead to inconsistencies over time. This happens by updating different records with conflicting data for the same entity. Data in the Commander field is also not formatted in a way that can easily be searched. Each bit of data should be in its own field.

To avoid redundancies and inconsistencies each data item should be held once. This is done by splitting the table (table per entity).

Primary Key and Foreign Key

A primary key is a field that is unique for each record within a table. The way of linking the primary key with another table, is by using a foreign key. A foreign key is an attribute in one table that contains the value of a primary key in another table. Primary keys cannot be duplicated whereas foreign keys can be stored multiple times in the same table.

Pros of a Relational Database!

A relational database stores related information in separate tables. This means that individual pieces of information are only stored once, thus preventing any inconsistency in the data and also saving space.

Any duplicated information that is then not needed is, by definition, redundant.

mrahmedcomputing