Relational Database Concepts and SQL Fundamentals
Normalization
Normalization is a database design technique used to organize the structure of relational databases. The goal is to reduce data redundancy and improve data integrity by dividing large tables into smaller ones and defining relationships between them. The process involves several normal forms (1NF, 2NF, 3NF, BCNF, etc.), each with its own set of rules:
- 1NF (First Normal Form): Ensures that each column contains atomic values (no repeating groups).
- 2NF (Second Normal Form): Ensures that the table is in 1NF and all non-key attributes are fully dependent on the primary key.
- 3NF (Third Normal Form): Ensures that the table is in 2NF and all attributes are not transitively dependent on the primary key.
SQL Queries
SQL (Structured Query Language) is used to interact with relational databases. Key types of SQL queries include:
- SELECT: Retrieves data from the database.
SELECT * FROM employees;
- INSERT: Adds new rows to a table.
INSERT INTO employees (name, age) VALUES ('John', 30);
- UPDATE: Modifies existing data.
UPDATE employees SET age = 31 WHERE name = 'John';
- DELETE: Removes rows from a table.
DELETE FROM employees WHERE name = 'John';
- JOIN: Combines rows from two or more tables based on a related column.
SELECT employees.name, departments.name FROM employees JOIN departments ON employees.department_id = departments.id;
ER Model (Entity-Relationship Model)
The Entity-Relationship Model is a conceptual framework used to describe the structure of a database. It uses:
- Entities: Objects or things that have a distinct existence in the database (e.g., “Employee”).
- Attributes: Properties or details of entities (e.g., “Employee Name”).
- Relationships: Associations between entities (e.g., “Employee works in Department”).
ER diagrams visually represent entities, attributes, and relationships in a structured form.
Transaction Process
A transaction in a database system refers to a sequence of operations performed as a single unit of work. It follows the ACID properties:
- Atomicity: All operations in a transaction are either fully completed or fully rolled back.
- Consistency: A transaction must transform the database from one consistent state to another.
- Isolation: Transactions are isolated from one another, meaning the intermediate state of a transaction is invisible to others.
- Durability: Once a transaction is committed, the changes are permanent, even in the case of a system failure.
Example of a transaction in SQL:
BEGIN TRANSACTION;
UPDATE account SET balance = balance - 100 WHERE account_id = 1;
UPDATE account SET balance = balance + 100 WHERE account_id = 2;
COMMIT;
ER Schema
An ER schema is a detailed representation of the ER model, showing the entities, their attributes, and relationships in a database. It is typically converted into a relational schema, which can be implemented using tables, keys, and foreign keys. For example:
- Entity: Employee (Attributes: EmployeeID, Name, Position)
- Relationship: Works_In (Associates Employee with Department)
Three Schema Architecture
The Three Schema Architecture is a framework that defines three levels of abstraction for database systems:
- Internal Schema: Describes the physical storage of the data. It specifies how the data is stored on disk.
- Conceptual Schema: Describes the logical structure of the entire database. It includes the entities, relationships, and constraints but without any details on how the data is stored.
- External Schema: Describes the user views of the data. It is concerned with what data users can access and how they can interact with it, often via applications.
View of Data
A view in a database is a virtual table that provides a specific representation of data. It is created by querying one or more tables in the database. Views do not store data themselves but are used to simplify complex queries or provide a customized view of the data. Example of creating a view:
CREATE VIEW employee_view AS
SELECT name, department FROM employees WHERE department = 'HR';
The view can be used like a table:
SELECT * FROM employee_view;