Relational Databases, Normalization, and SQL Basics

Let us start with the central idea: a database is simply a structured collection of data. Think of it as a digital filing cabinet. Before computers, you might have had a filing cabinet with folders for each customer. When a customer placed an order, you would write the order details on a piece of paper and file it in their folder. This works for a small business, but it breaks down when you have thousands of customers and millions of orders. You cannot quickly find all orders placed yesterday, or all customers who live in London, without spending hours manually flipping through papers.

A relational database solves this by storing data in *tables*. A table is like a spreadsheet with rows and columns. Each table holds information about one specific type of thing. For example, you would have one table called Customers with columns like CustomerID, Name, and Address. You would have another table called Orders with columns like OrderID, OrderDate, and CustomerID. The magic is in the CustomerID column. This column in the Orders table refers back to the Customers table. This creates a relationship between the two tables. An order belongs to a specific customer. This is the 'relational' part of 'relational database'. It stops you from having to write the customer's full name and address on every single order, which is messy and prone to errors.

Normalisation is the process of designing your tables to minimise this repetition and avoid data anomalies. Think of it as the rulebook for tidying your database. The rules are called 'normal forms'. The first rule, First Normal Form (1NF), says that every column in a table should contain only one value, and every row should be unique. For example, you should not have a column called PhoneNumbers that contains '020 7946 0958, 07700 900 002'. Instead, you should have a separate table for phone numbers, or a second column called PhoneNumber2. The second rule, Second Normal Form (2NF), says you must remove data that is only dependent on part of a unique identifier (the primary key). The third rule, Third Normal Form (3NF), says you should remove data that is not directly dependent on the primary key. For instance, in an Orders table, the customer's address should not be stored because the address depends on the customer, not on the order itself. You would store the address in the Customers table and just reference the CustomerID in the Orders table. This is the core of normalisation: decomposing data to reduce redundancy and improve integrity.

SQL (Structured Query Language) is the language you use to talk to the relational database. It is a simple, English-like language used to create, read, update, and delete data (often called CRUD operations). The most common command is SELECT, which is used to retrieve data. A basic SQL query looks like this: SELECT * FROM Customers; This says, 'Show me everything from the Customers table.' A more useful query would be: SELECT Name, Address FROM Customers WHERE City = 'London'; This says, 'Show me the Name and Address of all customers who live in London.'

You can also combine data from multiple tables using a JOIN. For example: SELECT Customers.Name, Orders.OrderDate FROM Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID WHERE Orders.OrderDate = '2024-01-01'; This query retrieves the names of customers who placed orders on January 1st, 2024, by joining the Customers and Orders tables on the common CustomerID field. Understanding these basic operations - SELECT, FROM, WHERE, JOIN, INSERT, UPDATE, DELETE - is the foundation of working with any relational database.

Why does this matter for the DA0-002 exam? The exam expects you to understand the purpose of normalisation and how it improves data quality. You must be able to identify which normal form a table is in (usually 1NF, 2NF, or 3NF). You will also be asked to read simple SQL queries and predict the output, or to identify the correct SQL syntax for a given task. The exam will not ask you to write complex queries from scratch, but it will test your ability to recognise a correct query and understand the logic behind it. The most common mistakes come from confusing the different normal forms or misunderstanding how a JOIN works. Once you grasp the core concepts - tables, relationships, normalisation rules, and basic SQL commands - you will be well prepared for the exam.

As an IT professional, you will rarely build these databases from scratch. More commonly, you will find yourself querying an existing database to answer a business question. For example, imagine you work at a mid-sized retail company. The marketing team asks you, 'We want to send a promotion to customers who have not made a purchase in the last 90 days. Can you give us their names and email addresses?'

Here is what you would do, step by step: 1. Identify the relevant tables. You know the database has a Customers table and an Orders table. The Customers table has columns like CustomerID, FirstName, LastName, Email. The Orders table has columns like OrderID, CustomerID, OrderDate, TotalAmount. 2. Check for data anomalies. Before you write the query, you need to ensure the data is reliable. Are there customers with missing email addresses? Are there duplicate customer records? This is where your understanding of normalisation helps you spot potential problems. A well-normalised database minimises these issues. 3. Write the SQL query. You write a query to find customers who exist, but whose last order date is more than 90 days ago. You might use the MAX function on the OrderDate column. The query could look like:

SELECT Customers.FirstName, Customers.LastName, Customers.Email
    FROM Customers
    LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID
    GROUP BY Customers.CustomerID
    HAVING MAX(Orders.OrderDate) <= NOW() - INTERVAL 90 DAY
       OR MAX(Orders.OrderDate) IS NULL;

(The LEFT JOIN and IS NULL part catches customers who have never placed an order.) 4. Interpret the results. You run the query and get a list of 500 names and emails. You check a few manually to verify the logic is correct. You then export this list to a CSV file for the marketing team. 5. Handle errors. You may run into issues. For instance, if the OrderDate column is stored as a text string instead of a date type, your date calculation will fail. You will need to fix the data type or use a conversion function. This is a common real-world headache.

Another common task is data cleaning. You might notice that the State column in the Customers table contains values like 'NY', 'New York', and 'N.Y'. You would write an UPDATE query to standardise these. For example: UPDATE Customers SET State = 'NY' WHERE State IN ('New York', 'N.Y.', 'State of New York'); Without normalisation, this sort of messiness would be much more widespread and harder to fix.

Finally, you might be asked to generate basic reports. The sales team might want a monthly sales report. You would write a SELECT query that sums the TotalAmount from the Orders table, groups by month, and joins with the Customers table to show which customers are buying the most. These are the day-to-day tasks of an IT professional working with data – turning business questions into SQL queries and ensuring the underlying data is of high quality.