Relational Databases

By McDonald, T.  |  Date 5th of November 2020

Why use a database?

Databases are a great way to store and retrieve data.  Since they are so widely used in computing, knowing how to use them is a must have skill in this industry.  For instance, many websites use databases to store product details.  One of the main benefits to databases is allows data to be organised, manipulated and retrieved rather than just storing it making it a very powerful tool.    

Tables and keys

First let’s look at the parts of the table 

Customer_Table

customer_id

name

address

phone

1

Tony McDonald

Some place

0788454567

2

A man

17 somewhere else

0793453459

3

A woman

45 who knows

0712311180

 

All relational databases have columns and rows. 

  • Columns are vertical, so phone is the label of a column containing phone numbers.
  • Rows are horizontal.  In the above example there are 3 rows with ids of 1,2 and 3.

Rows represent an entry or observation and columns represent an attribute.  In addition, one column is reserved for a special purpose: the primary key.  The primary key (PK) is a column with a unique value for each row allowing it to be easily identified.  For example, if you have a database with 5 people called John McDonald, you can’t use name as a PK because their names are not unique.  

Foreign Keys and Composite Keys

This is a value stored in a table that is a primary key in a different table; the foreign key defines the relationship between two tables.  For instance, if the table above had information about the salesperson dealing with a customer, instead of storing all that information in the customer table we can store it in a second table and link the two with a foreign key in the customer table. 

Customer_Table

customer_id

name

address

phone

foreign_key

1

Tony McDonald

Some place

0788454567

S1

2

A man

17 somewhere else

0793453459

S2

3

A woman

45 who knows

0712311180

S1

Salesperson_Table

salesperson_id

name

branch

payroll_number

S1

Fred Fish

The bowl

55555A

S2

A salesman

The Den

34527F

S3

A saleswoman

Enterprise

09845X

Note, the foreign key is the row id of the salesperson table.

Sometimes a table uses a composite key to identify a row.  This is because a single key will not uniquely identify a single row in a table; only together will the columns uniquely identify a row.

Two Foreign Keys Can Make A Composite Key.   

Total_Sales_Table

customer_id

salesperson_id

total_sales

1

S1

55 000

2

S1

12 000

3

S3

27 000                


Now you can see how the relational databases work and fit together, i will move on to some SQL basics in the next blog.  Please leave a comment and subscribe.

Comments