SQL: Databases and Tables

What is a Database?

A database is an organized collection of structured data designed to store information and make it easier to manage data efficiently. Modern database systems are built to store data, process transactions, and ensure data security while supporting a wide range of applications. Databases allow users to execute SQL statements for inserting, updating, and deleting records, and they provide tools for querying data through an SQL query.

In practice, a SQL database enables both data storage and fast retrieval of data, which is critical for businesses and applications that rely on handling large volumes of information. There are different database types each designed to optimize performance, scalability, and functionality in SQL.

Examples of common databases include MySQL, PostgreSQL, Oracle, and SQLite.

Database Schema and Entities

A database schema defines the database structure by specifying how data is organized and how tables are connected. It includes details about tables, columns, datatypes, constraints, and relationships. The schema acts as the blueprint of a database, making it easier retrieve data with an SQL query.

Within a schema, the main components are entities. In relational databases, an entity represents a real-world object (such as a Customer, Product, or Order). Each entity is typically stored in a table, where rows hold records of related data and columns define the attributes. For example, a Customer entity may have attributes like customer_id, name, email, and phone_number.

In many systems, different databases themselves can also be connected. For example, one database storing customer details can be linked with another database holding transaction records. This allows applications to combine information from multiple sources, giving a more complete view of the data. Tools like PostgreSQL, MySQL, and Oracle support cross-database queries, enabling seamless integration of distributed information.

Different database models (relational, document, or key-value) handle entities differently, but in relational database types like PostgreSQL, entities are the foundation of designing a well-structured SQL database.

Understanding Relationships in SQL 

In relational databases, tables can be related to each other through keys. These relationships help in organizing data and ensuring data integrity. 

One to One (1:1): Each record in Table A is linked to one and only one record in Table B.  

For example, each employee has one unique employee ID. 

One to Many (1:M): A single record in Table A can be associated with multiple records in Table B.  

For example, a customer can place multiple orders. 

Many to Many (M:M): Records in Table A can be associated with multiple records in Table B and vice versa. This typically requires a junction table. 

For example, students and courses have a many-to-many relationship, as students can enroll in multiple courses, and each course can have multiple students. 

Database Normalization

What is Normalization?
Normalization is the process of organizing data in a database to reduce redundancy (duplicate data) and improve data integrity (accuracy and consistency). It ensures that each piece of information is stored in only one place, making the database more efficient and reliable.

Why is Normalization Important?

• Avoids duplicate data (no repeating the same info in multiple tables).
• Saves storage space.
• Makes updates easier (change it once instead of in many places).
• Improves data consistency and accuracy.

Normal Forms

There are several levels of normalization. For beginners, understanding the first three is usually enough:

1. First Normal Form (1NF)
   • Each column should hold only one value (no lists or multiple values in the same column).
   • Each row must be unique.
   • Example: Instead of having a column phone_numbers with "12345, 67890", create separate rows for each phone number.
2. Second Normal Form (2NF)
   • Must already satisfy 1NF.
   • Every non-key column should depend on the whole primary key, not just part of it.
   • Example: If a table has student_id and course_id as the primary key, then student_name should not be in this table (because it only depends on student_id). It should be in a students table.
3. Third Normal Form (3NF)
   • Must already satisfy 2NF.
   • No non-key column should depend on another non-key column.
   • Example: If a table has student_id, student_name, and department_head, the department_head depends on the department, not directly on the student. It should go into a separate departments table.

Examples Before and After Normalization

Non-normalized Table (bad):

Normalized Tables (better):

Students Table

Courses Table

Instructors Table

No duplication, easier updates, cleaner relationships.

FAQs 

1. What problems does normalization solve in databases?
Normalization solves problems such as data redundancy, update anomalies (needing to change data in many places), insertion anomalies (difficulty adding new data without other information), and deletion anomalies. By eliminating these issues, databases remain consistent and reliable.

2. What is denormalization and how is it different from normalization?
Denormalization is the process of combining tables to improve query performance, even if it introduces some redundancy.

• Normalization focuses on reducing redundancy and improving consistency.
• Denormalization focuses on speeding up read operations by allowing some duplicate data. 

3. Why are relationships important in a database? 
Relationships (1:1, 1:M, M:M) ensure data integrity and prevent duplication. For example, instead of storing customer details in every order, you link the customer table to the order table, reducing redundancy and improving efficiency. 

4. Can a table have multiple primary keys? 
No. A table can only have one primary key, but that key can consist of multiple columns (known as a composite primary key). This ensures uniqueness across the specified column set. 

5. What is the difference between a schema and a database? 
A database is the container that holds all the data, while a schema defines the structure of that data (tables, columns, relationships, and constraints). Think of a database as a house, and a schema as the architectural blueprint.