Database Fundamentals

As a beginner working with data in relational databases, there are some key concepts to understand before you get going with writing SQL queries. These are the things I found most useful when getting started querying databases.

The Relational Model

Data is organised in rows and columns stored in tables.

Databases hold a collection of data stored in tables.

Relationship Categories in Databases

one-to-one: one husband and one wife

many-to-one: many students and one school

one-to-many: one customer and many bank accounts

many-to-many: many students and many teachers

Primary Key v Foreign Key

To make the most of the structure of a database, and to avoid storing expensive spreadsheets, we need to abide by rules to keep data clean, organised and in a logical structure.

To help do this we can use a Primary Key. This is a column that best identifies one unique row, and identifies each record as unique, like an ID.

It ensures that there are no duplicates

There can only be one primary key per table

It cannot be unknown (NULL)

A foreign key is a column that matches a primary key in another table.

SQL Statements

SQL is a standardised language for querying, manipulating and modifying relational databases. The basic SQL statements used to transform the data into more segmented tables fall into four main groups.

Retrieve data with SELECT

Add new data with INSERT

Remove data with DELETE

Modify data with UPDATE

There are plenty of new concepts to get your head around with writing SQL and understanding the structure of a database. These are the relational database fundamentals I feel are most important for complete beginners to grasp the basics.

Photo by Ylanite Koppens from Pexels