Advanced Database Management Systems - Recap
Uploaded by
Andrea MalubaAdvanced Database Management Systems - Recap
Uploaded by
Andrea MalubaAdvanced database
management systems
Recap
What is a Database?
A database is an organized collection of structured data stored electronically in a computer system. It's designed
to:
● Store data efficiently
● Allow easy retrieval
● Enable data modification
● Support multiple users simultaneously
Key characteristics:
● Persistent storage (data remains after program termination)
● Shared access (multiple users/applications)
● Logical coherence (data is related and organized)
What is a Database Management System?
A DBMS is software that interacts with: Core functions:
1. End users ● Data storage, retrieval, and update
2. Applications ● User-accessible catalog (data
3. The database itself dictionary)
● Transaction support (ACID properties)
to capture and analyze data. It provides: ● Concurrency control services
● Data definition (creating/modifying structure) ● Recovery services
● Data manipulation (inserting, updating, deleting) ● Authorization services
● Data security and integrity ● Data integrity services
● Concurrent access control ● Data independence
● Recovery mechanisms ●
Core functions
● Data storage, retrieval, and update
● User-accessible catalog (data dictionary)
● Transaction support (ACID properties)
● Concurrency control services
● Recovery services
● Authorization services
● Data integrity services
● Data independence
Data Storage, Retrieval, and Update
A DBMS efficiently manages:
● Storage: How data is physically stored (files, blocks, indexing).
● Retrieval: Fetching data via queries (SQL SELECT).
● Update: Modifying data (INSERT, UPDATE, DELETE).
Example:
● When a user updates their profile in an app, the DBMS modifies the record in
storage.
User-Accessible Catalog (Data Dictionary)
A metadata repository that stores:
● Table definitions
● Constraints (keys, FDs)
● User permissions
● Index information
Purpose: Helps the DBMS manage schema and optimize queries.
SELECT * FROM INFORMATION_SCHEMA.TABLES; -- Retrieves metadata about tables
Basic SQL
● SELECT, FROM, WHERE
● ORDER BY, GROUP BY, HAVING
● JOINs (INNER, LEFT, RIGHT, FULL)
● Aggregate Functions: COUNT, SUM, AVG, MIN, MAX
SELECT department,
AVG(salary)
FROM employees
GROUP BY department
HAVING AVG(salary) > 50000;
SQL QUERIES – retrieval queries
SELECT name, salary --This tells SQL what columns you want in the result."Give me the name and salary columns.
FROM employees --Specifies which table you want to pull data from. "...from the employees table."
WHERE salary > 50000; --Filters rows based on a condition. It acts like a bouncer at the door.
SQL QUERIES – ORDER BY, GROUP BY, HAVING
SQL QUERIES – ORDER BY, GROUP BY, HAVING
ORDER BY – Sort results GROUP BY – Aggregate by groups HAVING – Filter after grouping
SELECT name, salary SELECT department, COUNT(*) AS SELECT department, AVG(salary)
employee_count
FROM employees FROM employees
FROM employees
ORDER BY salary DESC; GROUP BY department
GROUP BY department;
HAVING AVG(salary) > 60000;
Used when you want to aggregate data
Used to sort the results by one or more (like counts or averages) by category. Similar to WHERE, but it works after
columns. GROUP BY.
E.g: Sort the result by salary, from
highest to lowest.
⚠ You can’t use aggregate functions
Can also sort ascending (ASC, default): like AVG() in WHERE, but you can in
HAVING.
ORDER BY name ASC
SQL QUERIES - JOINs (INNER, LEFT, RIGHT, FULL)
Assume:
employees(emp_id, name, department_id) Used to combine rows from two or more
tables based on a related column.
departments(department_id, dept_name)
INNER JOIN – Matching records in both tables LEFT JOIN – All from left, even if no match
SELECT e.name, d.dept_name SELECT e.name, d.dept_name
FROM employees e FROM employees e
INNER JOIN departments d ON e.department_id = LEFT JOIN departments d ON e.department_id =
d.department_id;
d.department_id;
List all employees, even if they donʼt belong to any
List employees and their departments (only if
department.
matched).
SQL QUERIES - JOINs (INNER, LEFT, RIGHT, FULL)
Assume:
employees(emp_id, name, department_id)
departments(department_id, dept_name)
RIGHT JOIN – All from right, even if no match FULL JOIN – All from both sides, matched or not
SELECT e.name, d.dept_name SELECT e.name, d.dept_name
FROM employees e FROM employees e
RIGHT JOIN departments d ON e.department_id = FULL OUTER JOIN departments d ON
d.department_id; e.department_id = d.department_id;
List all departments, and any employees (if any) Include every employee and every department,
in them. even unmatched.
summary
Clause What It Does When It Executes
