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.