Day 2: Understanding databases, tables, and columns. The relational model.

Chapter 1: Introduction to Databases

Section 1.1: What is a Database?

A database is an organized collection of structured information, or data, typically stored electronically in a computer system. This data is usually structured in a way that makes the data easily accessible, manageable, and updateable.

Databases are integral to modern technological systems and serve as the backbone for many applications and services that we use daily. They hold various types of data, from simple numeric values and text to more complex data types like images, files, and even geographical data.

Databases come in various types, each with its own structure and methodology for storing and organizing data:

1. Relational Database: This is the most common type of database today. Relational databases store data in tables, with each table consisting of rows and columns. The ‘relational’ part of the name comes from how the data within these tables can be related to each other. The structure of a relational database allows it to efficiently manage large amounts of data, and these databases are widely used in various industries.
2. Document-Oriented Database: These databases are designed to store, manage, and retrieve document-oriented information, also known as semi-structured data. The data is stored in documents that are grouped together in collections. Each document can have a different structure. An example of a document-oriented database is MongoDB.
3. Key-Value Stores: This is a simple type of database where each value is associated with a specific key. It’s like a large hash table where a value can be found quickly if you know the key. Examples include Redis and DynamoDB.
4. Graph Databases: These are designed to store data whose relations are best represented as a graph, where data points (nodes) are connected by lines (edges). These databases are efficient in managing and querying data with complex interrelations, like social networks. Neo4j is an example of a graph database.
5. Object-Oriented Databases: These databases store data in the form of objects, as in object-oriented programming. They are best suited to situations where there are complex relationships between data entities.

Real-life Database Applications

Here are some examples of real-life applications where databases are used:

1. Banking Systems: Banks use databases to keep track of customer information, account activities, loans, and transactions. These databases help in managing the bank’s operations efficiently and securely.
2. Online Shopping: E-commerce websites like Amazon use databases to store product information, customer details, order data, and more. They handle millions of transactions a day, and databases help keep all this information organized and accessible.
3. Social Networks: Platforms like Facebook and Instagram store user data, user interactions, images, and much more in their databases. These databases not only need to handle vast amounts of data but also provide quick and efficient access to the data.
4. Libraries: Libraries use databases to keep track of their books and their borrowers. Databases help them know who has borrowed which book and when it’s due back.
5. Airline Reservation Systems: Airlines use databases to keep track of seats, reservations, schedules, fare information, and passenger details. These systems help them manage their operations smoothly.

Understanding what databases are and their wide range of applications forms the foundation for learning SQL, which is a language used to interact with databases, especially relational ones. In the next section, we will dive into more detail about SQL databases and their characteristics.

Section 1.2: SQL Databases

SQL, an acronym for Structured Query Language, is a standardized programming language used for managing and manipulating relational databases. A SQL database, therefore, is a type of database that you can interact with using SQL.

One of the primary characteristics of SQL databases is their tabular data structure. This structure is based on the concept of tables that include rows and columns. Each row represents a unique record, and each column represents a specific field in the record.

SQL databases enforce data integrity through a set of constraints and rules. They ensure that the data stored in them is accurate and consistent. This is extremely important for applications where data integrity is critical, such as in banking and healthcare systems.

Another key feature of SQL databases is their support for complex queries. SQL is a powerful language that can handle complex operations on data. It enables you to filter, sort, aggregate, and perform various other manipulations on the data stored in a database.

SQL databases also provide ACID (Atomicity, Consistency, Isolation, Durability) properties, ensuring reliable processing of database transactions. This makes SQL databases a great choice for systems where reliability and data consistency are key requirements.

Section 1.3: Database Management Systems (DBMS)

A Database Management System (DBMS) is software designed to manage databases. A DBMS provides an interface for interacting with databases, allowing users to create, retrieve, update, and manage data in a database. In addition to data management, a DBMS also helps with database security, backup and recovery, and concurrency control.

There are several types of DBMS, but the most common ones are:

1. Relational Database Management System (RDBMS): This type of DBMS is based on the relational model, where data is stored in tables. SQL is used as the standard language to interact with an RDBMS.
2. NoSQL DBMS: This type of DBMS is used to manage NoSQL databases, such as document-oriented databases, key-value stores, and graph databases. NoSQL DBMSs are designed to handle large volumes of data and are commonly used in big data and real-time web applications.
3. Object-Oriented Database Management System (OODBMS): This type of DBMS is used to manage object-oriented databases. It allows the data to be stored in the form of objects, just like in object-oriented programming.
4. Hierarchical Database Management System (HDBMS): This type of DBMS manages data in a tree-like structure, with each record having one parent record and many children.

Some of the popular SQL RDBMS are:

1. MySQL: MySQL is an open-source RDBMS. It’s widely used for web applications and is a component of the LAMP web application software stack (Linux, Apache, MySQL, PHP).
2. PostgreSQL: PostgreSQL is also an open-source RDBMS. It supports advanced data types and performance optimization, making it suitable for complex, enterprise-class applications.
3. Oracle Database: Oracle Database is a multi-model database management system provided by Oracle Corporation. It is a commercial DBMS that offers a broad range of features and is used extensively in enterprise environments.
4. Microsoft SQL Server: SQL Server is a relational database management system developed by Microsoft. It offers advanced analytics, security features, and it’s well integrated with other Microsoft products, which makes it popular in the corporate world.

In the next sections, we will delve deeper into the structure of SQL databases, examining elements like tables, columns, and rows, and their roles in the relational database model.

Chapter 2: Understanding the Relational Database Model

Section 2.1: The Relational Database

The concept of the relational database was first introduced by Edgar F. Codd, a computer scientist at IBM, in 1970. His idea was to structure data into collections of tables where each table would correspond to an entity in the real world. These entities could be anything like people, products, orders, etc. The tables, containing rows and columns, would hold data corresponding to these entities.

In a relational database, the table is the primary data structure, where each table has multiple columns representing attributes, and each row represents a single record.

2.1.1 Tables

Consider a simple example of a “Students” table in a school database:

In this table:

• Each row represents a student in the school.
• Each column represents an attribute of the student: their ID, first name, last name, and age.

2.1.2 Relationships

The “relational” part of “relational database” comes from the relationships that can exist between different tables. These relationships allow us to link data from multiple tables together.

The most common types of relationships in a relational database are:

1. One-to-One: Each record in the first table corresponds to one (and only one) record in the second table. For example, a table of people and a table of social security numbers. Each person has one social security number, and each social security number belongs to one person.
2. One-to-Many: Each record in the first table corresponds to one or more records in the second table. For example, a table of authors and a table of books. Each author can write many books, but each book has one author.
3. Many-to-Many: Each record in the first table corresponds to one or more records in the second table, and vice versa. For example, a table of students and a table of classes. Each student can enroll in many classes, and each class can have many students.

Let’s illustrate this with another table, “Classes”:

And a table “Enrollments”:

In this case, the “Enrollments” table establishes a Many-to-Many relationship between the “Students” and “Classes” tables. Each student can enroll in multiple classes, and each class can have multiple students.

The relational model provides a method to navigate between these related pieces of data, which makes the relational database a powerful tool for organizing and working with large and complex datasets.

Section 2.2: Tables in SQL

In the context of a SQL database, a table is a structured set of data. It’s essentially a collection of related data entries (rows) where each entry consists of several variables (columns).

Each table in a SQL database represents a specific entity, like a person, a book, or a student, and the columns in the table hold attributes of that entity. For instance, a “Students” table might have columns like StudentID, FirstName, LastName, and Age.

Here’s an example of a “Books” table:

In this table, each row represents a book, and each column is an attribute of the book: the book’s ID, title, author, and publication date.

Section 2.3: Columns and Data Types

A column in a SQL table represents a certain type of data that is stored in the table. When defining a table, each column is given a name and a data type that indicates the type of data that the column will hold.

Assigning the correct data type to a column is crucial for several reasons:

1. Data Integrity: By enforcing a particular data type, you ensure that only the correct type of data is stored in each column. For example, if a column is intended to store dates, the DATE data type will ensure that only valid dates are stored in that column.
2. Efficient Storage: Different data types require different amounts of storage. By choosing the appropriate data type, you can minimize the storage requirements of your database.
3. Performance: The correct data type can help optimize the performance of your SQL queries.

Here are some of the most common SQL data types:

• INT: This data type is used to store integer numbers. For example, the “Age” column in the “Students” table could use the INT data type.
• VARCHAR(n): This data type is used to store strings, with ‘n’ defining the maximum length of the strings. For example, the “Title” and “Author” columns in the “Books” table could use the VARCHAR data type.
• DATE: This data type is used to store date values. For example, the “PublishedDate” column in the “Books” table could use the DATE data type.
• FLOAT: This data type is used to store floating-point numbers, i.e., numbers with a decimal point.
• BOOLEAN: This data type is used to store boolean values, i.e., TRUE or FALSE.

In the next section, we will start learning about how to use SQL to interact with a relational database, including how to create tables, insert data into them, and query the data.

Section 2.4: Rows in SQL Tables

A row, also referred to as a record or tuple, in a SQL table represents a single, implicitly structured data item in a table. In terms of a relational database, a row—composed of columns—can be considered as a single “entry” or “instance” of the type defined by the table.

For instance, using the “Books” table from earlier:

In this row, BookID is 1, the Title is “To Kill a Mockingbird”, the Author is “Harper Lee”, and the PublishedDate is “1960-07-11”. Each piece of information corresponds to a column in the table and represents a single book in our database.

Section 2.5: Primary Key

A primary key in a SQL table is a column, or set of columns, that is used to uniquely identify each row in the table. In other words, no two rows in a table can have the same primary key value. The primary key of a table is very important, as it is the main way we reference individual records.

Choosing a good primary key is crucial to the design of a database for several reasons:

1. Uniqueness: As mentioned, the primary key value must be unique for each record. This ensures that each record can be uniquely identified.
2. Stability: A good primary key should not change over time. Primary keys are often used as references by other tables, so changing a primary key can lead to confusion or errors.
3. Simplicity: Ideally, a primary key should be as simple as possible. This often means using a single column as the primary key, though composite keys (keys made up of multiple columns) can also be used when necessary.

In our “Books” table, the “BookID” column could serve as a good primary key. It’s unique for each book, it won’t change (even if a book’s title or author changes), and it’s simple. Here’s what that looks like:

When we start learning SQL queries in the next sections, we’ll see that primary keys are used to identify which rows to update or delete, and they’re also used when creating relationships between tables.

Section 2.6: Foreign Key

In the context of relational databases, a foreign key is a column or a set of columns in one table that is used to “point” to the primary key in another table. The table containing the foreign key is known as the child table, and the table containing the candidate key is known as the referenced or parent table.

Foreign keys play a crucial role in maintaining the referential integrity of the database. They enable us to create links between tables and ensure consistency and accuracy of the data. They are essential for mapping the relationships between different entities in a relational database.

Consider the previous example of “Students” and “Classes” with the “Enrollments” table serving as the link:

In the “Enrollments” table, both “StudentID” and “ClassID” are foreign keys that point to the primary keys in the “Students” and “Classes” tables respectively. This allows us to map which student is enrolled in which class.

Section 2.7: Normalization

Normalization is a database design technique that aims to eliminate redundancy and prevent certain types of anomalies during data operations. It’s a step-by-step process of reducing a database to its most streamlined form to ensure its efficiency and accuracy. This process involves dividing a database into two or more tables and defining relationships between those tables.

The benefits of normalization include:

1. Data Integrity: By eliminating redundancy, the chances of data becoming inconsistent are reduced.
2. Efficient Data Modification: Normalization makes it easier to modify data since the data is stored in one place and not replicated across multiple tables.
3. Optimized Performance: Queries can run faster against a well-normalized database.
4. Easier Maintenance: A well-normalized database is easier to maintain due to its organized structure.

The process of normalization involves several stages, known as normal forms:

• First Normal Form (1NF): Ensures each table cell contains a single value and that 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 data in a table is dependent upon the primary key.

Further normalization stages, such as the Boyce-Codd Normal Form (BCNF), Fourth Normal Form (4NF), and Fifth Normal Form (5NF), address more complex situations but the first three forms are sufficient for most database normalization needs.

Chapter 3: Introduction to SQL Syntax

Section 3.1: Basic SQL Syntax

SQL, or Structured Query Language, is a language designed to manage and manipulate databases. While SQL commands vary somewhat between different database systems, the basic syntax rules are quite consistent.

Here are some general syntax rules:

1. Case Sensitivity: SQL is not case sensitive. This means that SELECT and select mean the same thing. However, it is a good practice to use uppercase for SQL keywords to differentiate them from column and table names. For example: SELECT column_name FROM table_name;
2. Semicolons: In SQL, statements are typically terminated with a semicolon (;). The semicolon is a signal to the server that the statement is complete and ready to be executed.
3. Comments: Comments are used to describe what your code does and are ignored by SQL interpreters. They can be single-line or multi-line:
   • Single-line comments start with two consecutive hyphens (–).
   • Multi-line comments start with /* and end with */.

-- This is a single-line comment
SELECT column_name FROM table_name; -- This is a comment at the end of a line

/*
This is a multi-line comment
which spans multiple lines
*/

Section 3.2: CREATE DATABASE Statement

The CREATE DATABASE statement is used to create a new database in SQL.

The syntax for creating a database is straightforward:

CREATE DATABASE database_name;

Replace database_name with the name you want to give to your new database. For example, to create a database named “School”, you would write:

CREATE DATABASE School;

Remember to end your SQL statements with a semicolon.

To use the database you’ve just created, you’ll need the USE statement:

USE School;

After running this command, any tables you create or modifications you make will be applied to the “School” database.

It’s important to note that creating databases and tables requires certain permissions in your SQL server. If you’re following along using your own server, make sure you have the necessary permissions to create databases.

In the following sections, we will dive deeper into SQL, learn how to create tables, and manipulate data.

Section 3.3: CREATE TABLE Statement

The CREATE TABLE statement is used to create a new table in a database.

The syntax for creating a table is as follows:

CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    column3 datatype,
    ...
);

Each column is defined with a name (like column1), a datatype (like INT or VARCHAR), and optionally other properties like NOT NULL (the column must always have a value) or AUTO_INCREMENT (the column value is automatically increased each time a row is added).

Here is an example of creating a “Students” table:

CREATE TABLE Students (
    StudentID INT AUTO_INCREMENT,
    FirstName VARCHAR(100),
    LastName VARCHAR(100),
    Age INT,
    PRIMARY KEY (StudentID)
);

In this example, the “Students” table has four columns. The StudentID column is of type INT and will automatically increment. This column is also defined as the primary key.

Section 3.4: INSERT INTO Statement

The INSERT INTO statement is used to insert new data into a table.

The syntax for inserting data into a table is as follows:

INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);

For example, to insert a new student into the “Students” table, you could use the following SQL statement:

INSERT INTO Students (FirstName, LastName, Age)
VALUES ('John', 'Doe', 18);

In this example, a new row is added to the “Students” table. The FirstName column is set to ‘John’, the LastName column is set to ‘Doe’, and the Age column is set to 18. Since the StudentID column is set to auto increment, it automatically assigns a unique ID to each student.

In the next sections, we will learn how to select data from a SQL table, and how to update and delete data.

Section 3.5: SELECT Statement

The SELECT statement is used to select data from a database. The data returned is stored in a result table, also called the result-set.

While the SELECT statement will be covered more extensively in Day 3, let’s do a brief overview and an example to verify the data inserted into our “Students” table.

Here’s the most basic syntax for the SELECT statement:

SELECT column1, column2, ...
FROM table_name;

You can also use the * character to select all columns in a table:

SELECT * FROM table_name;

So, if we want to verify the data in our “Students” table, we can run:

SELECT * FROM Students;

This will return all the data in the “Students” table, allowing us to check whether our INSERT INTO operations worked correctly.

Recommended Reading:

For more in-depth knowledge on SQL, consider the following resources:

1. SQL All-In-One For Dummies, by Allen G. Taylor: This book serves as a one-stop shop for everything you need to know to get working with SQL databases and start using them for data analysis.
2. Learning SQL: Generate, Manipulate, and Retrieve Data, by Alan Beaulieu: This classic book not only covers the SQL language but also provides a comprehensive introduction to relational database systems. It’s great for beginners and intermediate users who want to solidify their understanding of SQL and relational database management systems.

Continue learning and practicing SQL. Remember, consistent practice is key to mastering SQL. Tomorrow, you will learn more about using the SELECT statement, including fetching data from multiple tables and using various operators to filter your data.