M4 - SQL (Core Skill)

Database Concepts

Relational Databases

A relational database is a type of database that stores data in structured formats using tables. Each table consists of rows and columns, where each row represents a unique record and each column represents a specific attribute of that record. The relational model allows for easy data retrieval and manipulation using Structured Query Language (SQL). Key features include:

• Data Integrity: Ensures accuracy and consistency of data.
• Relationships: Tables can be linked through keys, enabling complex queries across multiple tables.
• ACID Properties: Ensures reliable transactions through Atomicity, Consistency, Isolation, and Durability.

For example, consider a database for a library where you have a table for Books and another for Authors. You can link these tables using a foreign key, allowing you to query books by their authors efficiently.

Tables and Records

In a relational database, tables are the primary structure for storing data. Each table consists of records (or rows) and fields (or columns). A record represents a single entry in the table, while fields represent the attributes of that entry. For example, in a Customers table:

• Fields: CustomerID, FirstName, LastName, Email
• Records: Each row would contain data for a different customer.

Key points to remember:

• Tables must have a unique name within the database.
• Each table should represent a single entity (e.g., Customers, Orders).
• Records can be added, updated, or deleted using SQL commands such as INSERT, UPDATE, and DELETE.

Example SQL to create a Customers table:

CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    Email VARCHAR(100)
);

Columns and Data Types

Each column in a table represents a specific attribute of the data stored in that table. Columns have defined data types that dictate what kind of data can be stored. Common data types include:

• INT: Integer values
• VARCHAR(n): Variable-length string with a maximum length of n
• DATE: Date values
• FLOAT: Floating-point numbers

Choosing the correct data type is crucial for optimizing storage and ensuring data integrity. For example, using VARCHAR(100) for an email address allows for flexibility in length, while INT for an ID ensures efficient indexing. Here’s an example of defining columns with data types in SQL:

CREATE TABLE Products (
    ProductID INT PRIMARY KEY,
    ProductName VARCHAR(100),
    Price FLOAT,
    CreatedAt DATE
);

Primary Keys

A primary key is a unique identifier for each record in a table. It ensures that no two records can have the same value in the primary key column(s). This is crucial for maintaining data integrity and enabling efficient data retrieval. Characteristics of primary keys include:

• Uniqueness: Each value must be unique across the table.
• Non-null: A primary key cannot contain NULL values.
• Immutable: The value of a primary key should not change.

For example, in a Students table, the StudentID can serve as a primary key:

CREATE TABLE Students (
    StudentID INT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50)
);

Foreign Keys

A foreign key is a field (or collection of fields) in one table that uniquely identifies a row of another table. It establishes a relationship between the two tables, allowing for data integrity and referential integrity. Key points about foreign keys include:

• They can contain duplicate values.
• They can be NULL, allowing for optional relationships.
• They enforce referential integrity by ensuring that the value in the foreign key column matches a value in the primary key column of the referenced table.

For example, in an Orders table, the CustomerID can be a foreign key referencing the Customers table:

CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    OrderDate DATE,
    CustomerID INT,
    FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);

Database Design Basics

Normalization Fundamentals

Normalization is the process of organizing data in a database to reduce redundancy and improve data integrity. The goal is to divide large tables into smaller, related tables and define relationships between them. The main forms of normalization are:

• First Normal Form (1NF): Ensures that all columns contain atomic values and each record is unique.
• Second Normal Form (2NF): Requires that all non-key attributes are fully functional dependent on the primary key.
• Third Normal Form (3NF): Ensures that all attributes are only dependent on the primary key, eliminating transitive dependencies.

For example, if a Students table contains both student information and course details, it should be split into two tables to achieve 1NF. This reduces redundancy and improves data integrity.

Entity Relationships

Entity relationships define how tables relate to one another in a database. Understanding these relationships is crucial for effective database design. The main types of relationships are:

• One-to-One: A record in one table is linked to a single record in another table.
• One-to-Many: A record in one table can be associated with multiple records in another table.
• Many-to-Many: Records in one table can relate to multiple records in another table and vice versa, often requiring a junction table.

For example, in a Courses and Students scenario, a student can enroll in multiple courses (One-to-Many), while a course can have many students (Many-to-Many). This relationship can be managed using an Enrollments table.

Referential Integrity

Referential integrity is a property of data stating that all its references are valid. In relational databases, this means that a foreign key must either be NULL or match a primary key in another table. Maintaining referential integrity ensures that relationships between tables remain consistent. Key points include:

• It prevents orphan records, which occur when a foreign key points to a non-existent record.
• It can be enforced through database constraints, ensuring that any operation (insert, update, delete) maintains the integrity of the data.

For example, if a Customer is deleted from the Customers table, any associated Orders should also be deleted or updated to maintain referential integrity. This can be achieved using cascading actions in SQL:

ALTER TABLE Orders
ADD CONSTRAINT fk_Customer
FOREIGN KEY (CustomerID)
REFERENCES Customers(CustomerID)
ON DELETE CASCADE;