CH1 Introduction

Lecture 1: Database concepts

Lecture 1: Database concepts
File-based system
Lecture 2: Database systems Main
Old way of storing data before databases. components
Lecture 3: Database Users
Data is saved in separate files like text files, Excel sheets, etc. Lecture 4: DBMS Architecture & Data
Problem: Models
Lecture 5: Mappings
Data is repeated in many files. Lecture 6: DBMS Other Functions
Hard to update (if you change one file, you must change all Lecture 7: Centralized Database
Environment
others). Lecture 8: Distributed Database
Searching takes time. Environment

Example:
A shop keeps one file for customers, another file for orders,
and another for payments — but the customer’s name is
written in all of them. If a customer changes their phone
number, you must update it in all files.

Database
Collection of related data stored together in an organized
way.
Data is connected, so there’s less repetition.
Easier to update and search.

Example:
Same shop now has one database with a Customers table,
Orders table, and Payments table — all linked by IDs, so
customer info is stored once.

Database Management System (DBMS)

Software that helps you create, read, update, and delete
data in a database.
 Makes it easier to control who can access and how data is
stored.

Examples of DBMS:
MySQL, Oracle, SQL Server, PostgreSQL.

Database System
Bigger picture: It includes the database itself + the DBMS +
the people + the applications that use it.
Think of it as everything working together to manage data.

Example:
Database → The actual tables and data
DBMS → MySQL
People → Shop employees
Applications → Shop’s billing system

Relation between them

1. File-based system came first, but had many problems.

2. Database fixed those problems by storing data in one

organized place.
3. DBMS is the tool to work with the database.

4. Database System is the complete environment.

Quick Summary:
File-based system → old way, messy, lots of repetition.
Database → organized collection of data.
DBMS → software to manage databases.
Database System → the whole package (database + DBMS +
people + apps).

Lecture 2: Database systems Main components

Main Components of a Database System
Users → People who use the system (admins, customers,
employees).
 Application Programs → Software the user interacts with
(e.g., billing app, website).
DBMS Software → Has two main jobs:
a. Process Queries → Understand and run your commands

(like SELECT * FROM Customers ).

b. Access Stored Data → Go to the database and get or save
data.
Stored DB Definition (Metadata) → Data about the data
(explained below).
Stored Database → The actual data you want (e.g., customer
names, orders).

Metadata (Database Definition)

Metadata is like the "map" or "blueprint" of your database.
Examples:
Table name ( Customers )
Column name ( Name , Phone )
Data types ( Text , Number )
Constraints (rules, e.g., phone must be 10 digits)
Access privileges (who can see/change data)
Usernames, passwords, log files.

Think of metadata as the database’s instruction manual.

Stored Database vs Metadata

Stored Database → Real information, e.g.,
Name: Mohamed
Birth date: 1980
Salary: 10000
Metadata → Describes the structure and rules for storing that
data.

Advantages of a Database System

Control redundancy → No duplicate data everywhere.
 Restrict unauthorized access → Only allowed people can
see/change certain data.
Share data → Multiple users/programs can use the same
database.
Enforce integrity constraints → Keep data valid (e.g., phone
must be numbers, ID must be unique).
Avoid inconsistency → Data stays the same in all places.
Backup & recovery → Can restore data if lost.

Disadvantages of a Database System

Needs expertise → Not everyone can design/manage it
easily.
Expensive → DBMS software and maintenance can cost
money.
Compatibility issues → One DBMS may not work with
another system.

Quick Summary Table

Term Meaning Example

Metadata Info about the Column names,
structure of the data types
database
Stored Database The real data Name =
"Mohamed"
DBMS Software to manage MySQL
the database
Advantages Benefits of DBMS Backup, security, no
redundancy
Disadvantages Problems of DBMS Cost, complexity

Lecture 3: Database Users

Step 1 – Analysis & Requirement Gathering
Who? → System Analysts
 What they do? → Talk to the business, understand what they
need, and collect all requirements.

Example: “We need a database for a hospital to store

patient info and appointments.”

Step 2 – Database Design

Who? → Database Designer
What they do? → Create the conceptual schema (blueprint
of tables, columns, and relationships).

Example: Decide that there will be a Patients table,

Doctors table, and Appointments table, and
how they connect.

Step 3 – Implementation
Who? → Database Administrator (DBA)
What they do?
Install the DBMS software.
Create the database schema (actual tables).
Add data to the database.
Create user accounts and set permissions.
Maintain database performance.

Example: Install MySQL, create tables, and make sure it

runs fast.

Step 4 – Application Development

Who? → Application Programmer
What they do? → Develop, test, and debug the applications
that will use the database.

Example: Make a hospital management app that shows

patient info from the database.
End Users
Who? → People who actually use the system (nurses,
doctors, patients).
What they do? → Enter data or retrieve data without caring
about the technical details.

Example: A nurse adds a new patient using the hospital

software.

Quick Summary Table

Step Role Main Task

1 System Analyst Gather
requirements
2 Database Designer Create database
blueprint
3 DBA Install DBMS,
create schema,
manage access
4 Application Build and test
Programmer applications
- End Users Use the application

Lecture 4: DBMS Architecture & Data Models

Part 1 – DBMS Architecture (Three Schema Architecture)

External Schema (What the user sees)

Shows only the part of the database the user needs.
Decides what data a user can see and how it’s shown.

Example: A cashier can see customer names and bills, but

not their passwords.

Conceptual Schema (The logical model)

Describes what data exists and how it’s connected.
 Defines tables, columns, and rules (constraints).

Example: Says there’s a Customers table with columns

Name , Email , Phone .

Physical Schema (The physical model)

Explains how data is stored in the computer (on disk,
indexes, storage format).
Deals with technical storage details.

Example: Data is stored in .ibd files in MySQL.

In short:
1 External Schema → What user sees
2 Conceptual Schema → What data exists
3 Physical Schema → How data is stored

Part 2 – Data Models

Logical/Conceptual Model
Close to real life: shows entities (things), attributes (details),
and relationships.
Example: ERD (Entity-Relationship Diagram) with
Employee , Project , Department .
Focuses on understanding data, not storage.

Physical Model
Shows how data is stored in the database.
Includes technical details like table structures, primary keys,
indexes, and storage paths.
Used by DBAs for optimization.

Quick Summary Table

Schema/Model Meaning Example

 External Schema User view Cashier sees bills
only
Conceptual Schema Logical structure Tables &
relationships
Physical Schema Storage details Disk files & indexes
Logical Model Concept-level data ER diagrams
Physical Model Computer-level SQL tables with
storage keys

Lecture 5: Mappings
What is Mapping in DBMS?
Definition: The process of transforming requests and
results between different levels of the database architecture.
It’s like a translator between the user’s view and the actual
stored data.

How it works (based on the diagram):

1. User Request → Goes to the External Schema (user view).
Example: "Show me all customers from Cairo."
2. External to Conceptual Mapping → Converts the user
request into the logical structure of the database.
Example: Knows that “Customers” is a table with a “City”
column.
3. Conceptual to Physical Mapping → Converts the logical
request into physical storage commands.
Example: Finds where the “City” column is stored on the
disk and retrieves it.
4. Results Mapping → The process works in reverse to send the
data back in a format the user can understand.

Simple Example:
You (External) → “I want a coffee.”
Waiter (Conceptual) → Understands your request and
tells the barista what coffee you want.
 Barista (Physical) → Makes the coffee from beans and
milk stored in the shop.
Waiter brings the coffee back to you in the way you
expect.

Quick Summary:
Mappings = The “middle work” to connect what the user
wants with how the data is actually stored.
Happens in both directions → Request going in & result
coming out.

Lecture 6: DBMS Other Functions

Multimedia Function
Stores and manages images, videos, audio inside the
database.

Example: YouTube storing video files and their details in a

database.

Spatial Data
Deals with geographic and location-based data (maps,
coordinates).

Example: Google Maps storing locations of restaurants with

their GPS coordinates.

Time Series
Handles data that changes over time and needs to be
tracked historically.

Example: Stock prices recorded every minute, or weather

data every hour.

Data Mining
The process of analyzing large sets of data to find hidden
patterns, trends, or useful information.
 Example: An online store analyzing purchases to
recommend products to customers.

Quick Summary Table

Function Purpose Example

Multimedia Store/manage YouTube videos
images, videos,
audio
Spatial Data Store/manage GPS maps
location info
Time Series Track data over time Stock prices
Data Mining Find patterns in Amazon
data recommendations

Lecture 7: Centralized Database Environment

Mainframe Environment
All processing happens on one central server (mainframe).
Users connect via terminals.
Problems:
Depends on one server → if it fails, everything stops (single
point of failure).
Performance is slow when many users connect.
Database and application layer are both on the same server →
high risk.

Client/Server Environment
The database is on a central server.
The application is installed on each user’s computer (thick
client).
Advantages:
Application layer is not a single point of failure (app can still
work if DB is down, partially).
Problems:
Database is still a single point of failure.
High support cost (need to install/maintain app on every
computer).

Internet Computing Environment (Three-Tier Architecture)

Thin client (web browser) → Application server → Database
server.
Advantages:
Lower cost for support and maintenance (no need to install
app on every PC).
Problems:
Application server is a single point of failure.
Database is a single point of failure.
N-Tier vs Three-Tier Architecture
Three-Tier: Thin client → Application server → Database
server.
N-Tier: Like three-tier but with multiple application servers
(better scalability and performance).

Quick Summary Table

Type Advantage Problem

Mainframe Centralized control Slow, one failure
stops all
Client/Server App layer not single High cost, DB single
failure failure
Internet Computing Lower maintenance App & DB single
cost failure
N-Tier Better scalability More complex
Lecture 8: Distributed Database Environment
What is a Distributed Database?
A database stored across multiple locations (different
computers or servers).
Data can be copied (replicated) or split (fragmented).

Replication
Copying the same data to multiple locations.
Types:
Partial Replication → Only some data is copied to other
locations.
Full Replication → Entire database is copied everywhere.

Advantage: Database is NOT a single point of failure — if

one server fails, others still have the data.
Disadvantage:

High cost

Fragmentation
Splitting the database into different parts and storing them in
different locations.

Example: Customers in Asia stored in one server,

customers in Europe stored in another.
Disadvantage:

If one site fails, part of data is lost

High cost

Quick Summary Table

Method Meaning Advantage Disadvantage

Partial Copy part of Not a single Cost
Replication the DB to failure point
other sites
Full Copy whole Not a single Cost
Replication DB to all sites failure point
Fragmentation Split DB into Specialization If one site
parts across by location fails, part of
sites data is lost
CH2 Entity Relationship Diagrams (ERD)

Lecture 1: Entity Relationship Modeling (ERD).

Lecture 1: Entity Relationship Modeling
Entity Relationship Modeling (ERD) (ERD).
Purpose: Shows the information a business needs by Lecture 2: Entities & Attributes
lecture 3: Relationship - degree
identifying entities (things) and relationships between them. Lecture 4: Cardinality Ratio
1. Entity Lecture 5: Participation

A thing in the real world with independent existence.

Can be:
Physical (e.g., Bank, Student)
Conceptual (e.g., Course, Department)
2. Attribute

Characteristics or properties of an entity.

Example: A Client entity can have attributes like:
Account
Name
ID
Address
Phone
3. Relationship

Describes how entities are connected to each other.

Example: A Student enrolls in a Subject.
Student attributes: Faculty, Grades, Graduation year
Subject attributes: Code, Hours, Prerequisites

Questions to Ask When Building an ERD

1. What entities need to be described in the model?

2. What attributes of these entities need to be recorded?

3. Can we identify an attribute (or combination) that uniquely

identifies each entity?
4. What relationships exist between these entities?
Lecture 2: Entities & Attributes
Entity
Definition:
A thing in the real world with independent existence.
Can be physical (like a person, bank, or car).
Or conceptual (like a course or department).
Entity types

1) Strong (regular) entity

Has its own key attribute (can stand alone).
Example: Employee(EmpID, Name, DOB, ...)
Notation: single rectangle.
2) Weak entity
Has no full key of its own → can’t be uniquely identified
without its owner.
Identified by: owner’s key + partial key (discriminator).
Existence‑dependent on the owner; if owner is deleted, weak
often goes too.
Example: Dependent(Name, Relation) belongs
to an Employee(EmpID) .
Unique ID = (EmpID + Name) .

Attribute
Definition:
Characteristics or properties of an entity.
Example:
For entity Client in a bank:
Attributes: Account, Name, ID, Address, Phone.
Attribute Types in ERD
An attribute describes some property of an entity, and attributes
can be classified into several types:
1. Simple Attribute
Definition: Cannot be divided into smaller parts.
Example: Age, Gender, ID.
2. Composite Attribute
Definition: Can be split into smaller subparts with their own
meanings.
Example:
Name → First Name, Middle Name, Last Name
Address → Street, City, Zip Code
3. Derived Attribute
Definition: Can be calculated from other attributes.
Example:
Age can be derived from Date of Birth.
Total Price can be derived from Quantity × Unit Price.
4. Multi-Valued Attribute
Definition: Can have multiple values for the same entity.
Example:
Phone Numbers (home, work, mobile)
Skills for an employee
5. Key Attribute
Definition: Uniquely identifies each entity occurrence.
Example:
Student_ID, Account_Number
6. Partial Attribute (also called Optional Attribute)
Definition: May not have a value for every entity occurrence
— it’s optional.
Example:
Middle Name (some people don’t have one)
Second Email Address

lecture 3: Relationship - degree

Unary Relationship
Definition: Relationship between the same entity type.
Example: An employee supervises another employee.
Real-life example: A person is married to another person.
In ERD: One entity box connected to itself.

Binary Relationship
Definition: Relationship between two different entities.
Most common type in databases.
Example: Employee works in Department.
Real-life example: Student enrolled in Course.
In ERD: Two entity boxes connected by a diamond.

Ternary Relationship
Definition: Relationship between three different entities at
the same time.
Example from your diagram: Employee uses skill on Project.
Why it matters: You can’t split it into multiple binary
relationships without losing meaning.
In ERD: Three entities connected to a single diamond.

Quick summary table

Degree Entities Example Common?

Involved
Unary 1 Employee Rare
supervises
 Employee
Binary 2 Student Very common
enrolled in
Course
Ternary 3 Employee Less common
uses Skill on
Project

Lecture 4: Cardinality Ratio

Cardinality Ratio
It defines the number of instances of one entity that can be
associated with instances of another entity in a relationship.

Types of Cardinality Ratios

1. One-to-Many (1 : M)

Definition: A single entity instance from Entity A can be

related to multiple instances of Entity B, but each instance of
Entity B is related to only one instance of Entity A.
Example from diagram:
Emp (M) — Work — Dept (1)
→ An employee can work in one department, but a
department can have many employees.
2. One-to-One (1 : 1)

Definition: A single entity instance in Entity A is related to only

one instance in Entity B, and vice versa.
Example from diagram:
Emp (1) — Manage — Dept (1)
→ An employee manages only one department, and a
department is managed by only one employee.

3. Many-to-Many (M : N)

Definition: Multiple instances of Entity A can relate to multiple

instances of Entity B.
Example from diagram:
Emp (M) — Work on — Project (N)
→ Employees can work on multiple projects, and projects can
have multiple employees.

Lecture 5: Participation
Participation in Relationships
Participation describes whether all or some instances of an
entity are involved in a relationship.
1. Total Participation (Must)
Definition: Every entity instance must participate in at least
one relationship instance.
Notation: Represented with double lines between the entity
and the relationship in ERD.
Example from diagram:
Emp —(Has)→ Contract (Total for Contract side).
Every Contract must be associated with an Employee.
2. Partial Participation (May)
Definition: Some entity instances may not participate in the
relationship.
Notation: Represented with single line between the entity and
the relationship in ERD.
Example from diagram:
Emp —(Own)→ Car (Partial for Car side).
 Some employees may not own a car.

Attribute on a Relationship
Definition: Sometimes, a relationship itself has attributes that
describe properties of that association.
Example:
In the diagram, the "Work on" relationship has an attribute
ST. Date (Start Date) — representing when an employee
started working on a project.
Why: These attributes cannot belong to just one entity — they
describe the link between two entities.

Identifying Relationship
Definition: A relationship that connects a weak entity to its
owner (strong entity) and helps uniquely identify it.
Notation: Double diamond for the relationship + double
rectangle for the weak entity.
Example from diagram:
Dependent is a weak entity identified through the Has
relationship with Emp .
The Name of the dependent alone is not unique — it needs
the employee’s ID to be uniquely identified.
Chapter 3 ERD Mapping to Tables

Mapping of Regular Entity Types

Each regular entity becomes a table. Mapping of Regular Entity Types
Mapping of Weak Entity Types
Attributes of the entity become table columns. Mapping Binary / Unary 1:M
Relationships
The primary key (PK) is underlined.
Mapping Binary / Unary M:N
Relationships
Example: Mapping Binary / Unary 1:1
Relationships
1 Emp (ID, SSN, Salary, Name, DOB, Street, Zone)
Mapping Ternary Relationships
Here, SSN is the primary key.

Mapping of Weak Entity Types

Weak entity tables include:
Their own attributes.
The primary key of the owner entity as part of their
composite PK (identifying relationship).
Represented with double rectangle in ERD.

Example:
1 Dependent (SSN, Name, Relation)

PK is (SSN, Name) because SSN comes from Emp .

Mapping Binary / Unary 1:M Relationships

Add the PK of the “1” side as a foreign key (FK) to the “M”
side table.

Example: Dept (DNO, DName, LOC) and Emp

(SSN, ..., DNO)
Here, DNO is added to Emp .
Mapping Binary / Unary M:N Relationships
Create a new table for the relationship.
The PK is the combination of the PKs of both participating
entities.
Add relationship attributes as columns.

Example:
1 Work_On (SSN, PNO, Hours)

PK = (SSN, PNO)

Mapping Binary / Unary 1:1 Relationships

Add FK to either table.
If total participation, put FK on that side.
If partial participation, put FK on optional side.

Mapping Ternary Relationships

Create a new table.
PK is the combination of the PKs from all three entities.
Add any relationship attributes.

Example:
1 Skills_Used (SSN, PNO, Skill_ID)

PK = (SSN, PNO, Skill_ID)

CH4 Structured Query Language(SQL)

Lecture 1: Database Schema & Constraints

Lecture 1: Database Schema &
SQL (Structured Query Language) Constraints
Lecture 2: DDL “Data Definition
Main categories: Language“ command
Lecture 3: SQL - Data Control
1. DDL (Data Definition Language) – Defines database
Language (DCL)
structure.
2. DML (Data Manipulation Language) – Manages and
manipulates data.
3. DCL (Data Control Language) – Controls access and
permissions.

Database Schema
Structure of the database (tables, relationships, constraints).

Data Types
Different types for storing data (e.g., integer, varchar, date).

Database Constraints
1. Primary Key – Not Null and Uniquely identifies a record.

2. Not Null – Ensures a column cannot have NULL values.

3. Unique Key – Ensures all values in a column are unique.

4. Referential Integrity (Foreign Key) – Ensures valid

references between tables “Parent record and Child record”
- In Insert: Insert in parent first then in child
- In Delete: Delete in child ”FK” first then the parent
5. Check – Ensures values in a column meet specific conditions.
Lecture 2: DDL “Data Definition Language“ command
CREATE Command
Used to create new database objects like tables, views, or
indexes.
1 CREATE TABLE Students (
2 StudentID INT PRIMARY KEY,
3 Name VARCHAR(100) NOT NULL,
4 Age INT CHECK (Age >= 18),
5 Email VARCHAR(100) UNIQUE
6 );

Explanation: Creates a Students table with

constraints.

ALTER Command
Used to modify an existing table structure.
1 ALTER TABLE Students
2 ADD PhoneNumber VARCHAR(15);

Explanation: Adds a new column PhoneNumber to

the Students table.

DROP Command
Used to permanently delete a table or database object.
1 DROP TABLE Students;

Explanation: Deletes the Students table completely

(data + structure).

TRUNCATE Command
Used to delete all rows from a table without removing its
structure.
1 TRUNCATE TABLE Students;

Explanation: Removes all records but keeps the table

ready for new data.
Lecture 3: SQL - Data Control Language (DCL)
GRANT Command
Used to give a user specific privileges (permissions) on
database objects or the system.
Examples:
1 -- Give SELECT permission on 'employees' table to Ahmed
2 GRANT SELECT ON TABLE employees TO Ahmed;
3
4 -- Give ALL privileges on 'department' table to Mary and Ahmed
5 GRANT ALL ON TABLE department TO Mary, Ahmed;
6
7 -- Give SELECT privilege to Ahmed with the right to grant it to
others
8 GRANT SELECT ON TABLE employees TO Ahmed WITH GRANT OPTION;

REVOKE Command
Used to remove privileges that were previously granted to a
user.
Examples:
1 -- Remove UPDATE permission on 'department' table from Mary
2 REVOKE UPDATE ON TABLE department FROM Mary;
3
4 -- Remove ALL privileges on 'department' table from Mary and
Ahmed
5 REVOKE ALL ON TABLE department FROM Mary, Ahmed;

Privilege Types
System Privileges: Permissions to perform actions on the
database as a whole (e.g., create users, create tablespaces).
Object Privileges: Permissions to perform actions on specific
database objects (e.g., SELECT, INSERT, UPDATE, DELETE
on a table).
CH05 – Data Manipulation Language (DML)

1. INSERT Command 1. INSERT Command

2. UPDATE Command
Adds new rows into a table. 3. DELETE Command
1 -- Insert with all columns specified 4. TRUNCATE Command
2 INSERT INTO Students (StudentID, Name, Age) 5. SELECT Command
3 VALUES (1, 'Ali', 20); 6. Comparison & Logical Operators
4
7. LIKE Operator
5 -- Insert into selected columns only
6 INSERT INTO Students (Name, Age) 8. Alias
7 VALUES ('Sara', 22); 9. ORDER BY
8 10. DISTINCT
9 -- Insert multiple rows at once
11. INNER JOIN
10 INSERT INTO Students (StudentID, Name, Age)
11 VALUES (2, 'Omar', 21), 12. OUTER JOIN
12 (3, 'Laila', 19); 13. SELF JOIN
14. Sub-Queries
15. Aggregate Functions
2. UPDATE Command 16. GROUP BY & HAVING
17. SELECT Conclusion
Modifies existing data in a table. SQL Query Execution Order (Priority)
Example Query
1 -- Update a specific record
2 UPDATE Students Step-by-Step Execution
3 SET Age = 21
4 WHERE StudentID = 1;
5
6 -- Update multiple columns
7 UPDATE Students
8 SET Name = 'Ahmed Ali', Age = 23
9 WHERE StudentID = 2;
10
11 -- Update all rows
12 UPDATE Students
13 SET Age = Age + 1;

3. DELETE Command
Removes rows from a table (can use WHERE).
1 -- Delete a specific record
2 DELETE FROM Students
3 WHERE StudentID = 3;
4
5 -- Delete based on condition
6 DELETE FROM Students
7 WHERE Age < 20;
8
9 -- Delete all rows (without removing structure)
10 DELETE FROM Students;
4. TRUNCATE Command
Removes all rows from a table but keeps structure.
1 TRUNCATE TABLE Students;

5. SELECT Command
Retrieves data from one or more tables.
1 -- Select specific columns
2 SELECT Name, Age FROM Students;
3
4 -- Select all columns
5 SELECT * FROM Students;
6
7 -- Select with condition
8 SELECT Name FROM Students WHERE Age > 21;

6. Comparison & Logical Operators

Comparison: = , > , < , >= , <= , <>

Logical: AND , OR , NOT
1 -- Comparison + AND
2 SELECT * FROM Students
3 WHERE Age >= 20 AND Age <= 25;
4
5 -- Using OR
6 SELECT * FROM Students
7 WHERE Age < 18 OR Age > 30;
8
9 -- Using NOT
10 SELECT * FROM Students
11 WHERE NOT Age = 22;

7. LIKE Operator

Pattern matching with % (any characters) and _ (single

character).
1 -- Names starting with 'A'
2 SELECT * FROM Students WHERE Name LIKE 'A%';
3
4 -- Names ending with 'n'
5 SELECT * FROM Students WHERE Name LIKE '%n';
6
7 -- Names with 'li' inside
8 SELECT * FROM Students WHERE Name LIKE '%li%';

8. Alias
Rename columns or tables in queries.
1 -- Column alias
2 SELECT Name AS StudentName, Age AS Years
3 FROM Students;
4
5 -- Table alias
6 SELECT S.Name, S.Age
7 FROM Students AS S;

9. ORDER BY

Sort results in ascending ( ASC ) or descending ( DESC ) order.

1 -- Sort by Age descending
2 SELECT * FROM Students ORDER BY Age DESC;
3
4 -- Sort by Name ascending then Age descending
5 SELECT * FROM Students ORDER BY Name ASC, Age DESC;

10. DISTINCT
Removes duplicate rows from results.
1 SELECT DISTINCT Age FROM Students;
2 SELECT DISTINCT Name, Age FROM Students;

11. INNER JOIN

Returns rows where there is a match in both tables.
1 SELECT S.Name, C.CourseName
2 FROM Students S
3 INNER JOIN Courses C
4 ON S.StudentID = C.StudentID;

12. OUTER JOIN

LEFT JOIN: All rows from left table + matching rows from
right.
RIGHT JOIN: All rows from right table + matching rows from
left.
FULL JOIN: All rows from both tables.
1 -- LEFT JOIN
2 SELECT S.Name, C.CourseName
3 FROM Students S
4 LEFT JOIN Courses C ON S.StudentID = C.StudentID;
5
6 -- RIGHT JOIN
7 SELECT S.Name, C.CourseName
8 FROM Students S
9 RIGHT JOIN Courses C ON S.StudentID = C.StudentID;
10
11 -- FULL JOIN
12 SELECT S.Name, C.CourseName
13 FROM Students S
14 FULL JOIN Courses C ON S.StudentID = C.StudentID;

13. SELF JOIN

Join a table with itself.
1 SELECT E1.Name AS Employee, E2.Name AS Manager
2 FROM Employees E1
3 INNER JOIN Employees E2 ON E1.ManagerID = E2.EmployeeID;

14. Sub-Queries
A query inside another query.
1 -- Compare with average
2 SELECT Name FROM Students
3 WHERE Age > (SELECT AVG(Age) FROM Students);
4
5 -- Get max salary employee
6 SELECT * FROM Employees
7 WHERE Salary = (SELECT MAX(Salary) FROM Employees);

15. Aggregate Functions

MAX(), MIN(), COUNT(), AVG(), SUM()
1 SELECT MAX(Age) AS Oldest, MIN(Age) AS Youngest FROM Students;
2 SELECT COUNT(*) AS TotalStudents FROM Students;
3 SELECT AVG(Salary) AS AvgSalary FROM Employees;

16. GROUP BY & HAVING

Groups rows with the same values; HAVING filters groups.
1 -- Group and count
2 SELECT Age, COUNT(*) AS Total
3 FROM Students
4 GROUP BY Age;
5
6 -- Group with HAVING
7 SELECT Age, COUNT(*) AS Total
8 FROM Students
9 GROUP BY Age
10 HAVING COUNT(*) > 1;

17. SELECT Conclusion

The SELECT statement can include:

1. Columns to display ( SELECT col1, col2 )
2. Table to get data from ( FROM table )
3. Conditions ( WHERE )
4. Grouping ( GROUP BY )
5. Filtering groups ( HAVING )
6. Sorting ( ORDER BY )
Example:
1 SELECT Age, COUNT(*) AS Total
2 FROM Students
3 WHERE Age >= 20
4 GROUP BY Age
5 HAVING COUNT(*) > 1
6 ORDER BY Age DESC;

SQL Query Execution Order (Priority)

Although we write SQL in a certain order, the database
executes it in a different logical order:
Execution Priority:
1. FROM / JOIN → choose tables and join them

2. WHERE → filter rows before grouping

3. GROUP BY → group remaining rows

4. HAVING → filter groups

5. SELECT → choose columns or expressions to display

6. DISTINCT → remove duplicates

7. ORDER BY → sort final results

8. LIMIT / OFFSET → take specific number of rows

Example Query
1 SELECT Department, COUNT(*) AS TotalEmployees
2 FROM Employees
3 WHERE Salary > 5000
4 GROUP BY Department
5 HAVING COUNT(*) > 2
6 ORDER BY TotalEmployees DESC;

Step-by-Step Execution
1. FROM Employees → Start with all rows from the

Employees table.
2. WHERE Salary > 5000 → Keep only employees earning more
than 5000.
3. GROUP BY Department → Group the filtered rows by
department.
4. HAVING COUNT(*) > 2 → Keep only departments with more
than 2 employees.
5. SELECT Department, COUNT(*) → Pick the columns and
apply the count.
6. ORDER BY TotalEmployees DESC → Sort departments by
number of employees.
CH6 SQL-other DB objects

Lecture 1: Views Lecture 1: Views

A view is a logical table based on a table or another view. Creating a View
Modifying a View
A view contains no data of its own; it is like a window through Removing a View
which data from base tables can be viewed or changed. Advantages of Views
Types of Views
The tables on which a view is based are called base tables. Lecture 2: Indexes
Why Use Indexes?
A view is stored as a SELECT statement in the data Benefits of Indexes
dictionary. How Indexes Work
When to Create an Index
Creating and Removing Indexes
Creating a View Example Use Case

Syntax:
1 CREATE VIEW view_name [(column1, column2, ...)]
2 AS
3 SELECT ...
4 FROM ...
5 WHERE ...;

Example 1 – Simple view:

1 CREATE VIEW emp_names AS
2 SELECT Fname, Lname
3 FROM Employee;

Example 2 – Multi-table view:

1 CREATE VIEW vw_work_hrs AS
2 SELECT Fname, Lname, Pname, Hours
3 FROM Employee, Project, Works_on
4 WHERE SSN = ESSN AND PNO = PNUMBER;

Example 3 – With CHECK OPTION:

1 CREATE VIEW Suppliers AS
2 SELECT *
3 FROM suppliers
4 WHERE status > 15
5 WITH CHECK OPTION;

(Ensures inserted/updated rows meet the WHERE

condition.)

Modifying a View
Syntax:
1 CREATE OR REPLACE VIEW view_name AS
2 SELECT ...
3 FROM ...
4 WHERE ...;

Example:
1 CREATE OR REPLACE VIEW vw_work_hrs AS
2 SELECT Fname, Lname, Pname, Hours
3 FROM Employee, Project, Works_on
4 WHERE SSN = ESSN AND PNO = PNUMBER AND Dno = 5;

Removing a View
Syntax:
1 DROP VIEW view_name;

Example:
1 DROP VIEW emp_names;

Advantages of Views
1. Restrict data access.

2. Make complex queries easy.

3. Provide data independence.

4. Present different views of the same data.

Types of Views

Feature Simple View Complex View

Number of tables One One or more
Contain functions No Yes
Contain groups of No Yes
data
DML operations Yes Not always
through a view

Lecture 2: Indexes
Why Use Indexes?
Indexes solve:
Not Sorted → Without indexes, data might be in random
order, making search slower.
Scattered → Data rows can be stored in different places on
disk, making retrieval slower.
Example
1 -- Without index: DBMS scans every row
2 SELECT * FROM Supplier WHERE City = 'London';

With an index on City , the DBMS jumps directly to "London"

entries.

Benefits of Indexes
Speed up retrieval of records for certain search conditions.
Can be defined on multiple columns.
Created by user or DBMS.
Maintained automatically by DBMS.

How Indexes Work

Indexes store a sorted list of key values and pointers to actual
data.
Example from image:
Index file (sorted by City: Athens, London, Paris) points to
Supplier table rows directly.

When to Create an Index

Create when:
Retrieving data heavily from a table.
Columns are often used in WHERE or JOIN.
Columns contain many NULLs (to find them faster).
Do not create when:
Table is updated very frequently (index maintenance slows
writes).

Creating and Removing Indexes

Create index
1 CREATE INDEX emp_inx ON Employee (Salary);

This makes salary searches faster.

Remove index
1 DROP INDEX emp_inx;

Example Use Case

Table
1 CREATE TABLE Employee (
2 ID INT PRIMARY KEY,
3 Name VARCHAR(50),
4 Salary DECIMAL(10,2),
5 Department VARCHAR(50)
6 );

Index creation
1 CREATE INDEX dept_inx ON Employee (Department);

Query using index

1 SELECT * FROM Employee WHERE Department = 'HR';

The DBMS uses the dept_inx index to find matching rows

quickly.
CH7 Normalization

Lecture 1 – What is Normalization?

Lecture 1 – What is Normalization?
Normalization of Data Lecture 2: Functional Dependency
Lecture 3: First Normal Form (1NF)
Definition: Lecture 4: Second Normal Form (2NF)
Normalization is the process of organizing data in a database to Lecture 5: Third Normal Form (3NF)
reduce redundancy (duplicate data) and improve data integrity.
It involves applying a series of tests called Normal Forms (1NF,
2NF, 3NF, …) to structure tables efficiently.

Objectives of Normalization
1. Minimize redundancy → Avoid storing the same data in
multiple places.
2. Avoid anomalies:
Insert anomaly – Unable to insert data due to missing
related information.
Update anomaly – Inconsistent data after an update.
Delete anomaly – Unintentional data loss after deleting a
record.
3. Reduce frequent NULL values – Ensure all stored values are
meaningful.
4. Improve database design for easy maintenance.

Example – Before Normalization

Table: EMP_PROJ (Employees with Projects)

SSN Ename Dept Pnumber Pname Hours

1234567 John Researc 1 ProductX 32.5
89 Smith h
1234567 John Researc 2 ProductY 7.5
89 Smith h
666884 Ramesh Researc 3 ProductZ 40.0
444 Nar. h

Problems:
Redundancy: “John Smith” and “Research” are repeated.
Update anomaly: If "Research" dept changes name, we must
update it in multiple rows.
Insert anomaly: Can’t insert a new department without having
an employee.
Delete anomaly: If John Smith leaves, we lose all info about
“ProductX” project.

After Normalization
We split into smaller related tables:
EMPLOYEE Table

SSN Ename DeptID

123456789 John Smith 5
666884444 Ramesh Nar. 5

DEPARTMENT Table

DeptID DeptName
5 Research

PROJECT Table

Pnumber Pname
1 ProductX
2 ProductY

WORKS_ON Table

SSN Pnumber Hours

123456789 1 32.5
 123456789 2 7.5

Benefits
No duplicate data
Easy updates – Change department name once.
Can add new data without dependencies
No accidental data loss

Lecture 2: Functional Dependency

Functional Dependency (FD)
Definition
A functional dependency is a constraint between two
attributes (columns) or two sets of attributes in a relation,
where one attribute (or set) uniquely determines another.

If A → B, the value of attribute A determines the value of

attribute B.

Example
1. SSN → EName
If we know the Social Security Number (SSN), we can
determine the Employee Name.
2. PNumber → {PName, PLocation}
If we know the Project Number, we can determine Project
Name and Project Location.
3. {SSN, PNumber} → Hours
If we know both Employee SSN and Project Number, we can
determine the Hours worked.

Types of Functional Dependencies

1. Fully Functional Dependency
An attribute depends on the whole key, not just part of it.
Example: {SSN, PNumber} → Hours
 Here, Hours depends on the entire composite key (both
SSN and PNumber).

2. Partial Dependency
An attribute depends on part of a composite key.
Example: {SSN, PNumber} → EName
Here, EName depends only on SSN (part of the composite
key), not on both attributes.

3. Transitive Dependency
A non-key attribute depends on another non-key attribute.
Example:
SSN → DNumber (Employee belongs to a department)
DNumber → DName (Department number determines
department name)
Therefore, SSN → DName is a transitive dependency.

Why Functional Dependencies Matter

Used in database normalization to remove redundancy.
Helps identify the primary key and relationships.
Prevents data anomalies (update, insert, delete anomalies).

Quick Practice Examples

1. Full Dependency Example

1 {OrderID, ProductID} → Quantity

Quantity depends on both OrderID and ProductID.

2. Partial Dependency Example

1 {OrderID, ProductID} → OrderDate

OrderDate depends only on OrderID, not the product.

3. Transitive Dependency Example

1 EmployeeID → DeptID
2 DeptID → DeptLocation
3 EmployeeID → DeptLocation (transitive)
Lecture 3: First Normal Form (1NF)
Normalization
Definition:
The process of decomposing unsatisfactory or “bad” relations
into smaller, well-structured relations.
This is done by breaking up their attributes into smaller
relations to improve database organization and reduce
redundancy.

Normal Form
Definition:
A condition using keys and functional dependencies (FDs)
to check if a relation schema meets the criteria of a particular
normal form.
Keys: Unique identifiers for rows in a table.
FDs: Rules showing how one attribute depends on another.

Problems in the Original Table (School Example)

In the original School Example table, we see:
Multivalued attributes: Attributes with more than one value in
the same cell.
Example:
Tel (011, 010 for the same student)
Subject (DB, CN)
Subj-Desc (Database, Networks)
Repeating groups: Multiple repeating columns for related
data.
Composite attributes: Attributes made of multiple sub-parts
(less relevant in this example).

First Normal Form (1NF) Rules

To satisfy 1NF:
1. Remove multivalued attributes — each field should have only
one value.
2. Remove repeating groups — avoid storing multiple sets of
related data in one record.
3. Ensure atomic values — each attribute must store an
indivisible value.

How the Table is Fixed

Step 1: Identify multivalued attributes and repeating groups
( Tel , Subject , Subj-Desc , G ).
Step 2: Create new tables for these values and link them with
the Primary Key (PK) of the main table as a Foreign Key (FK).
Step 3: Store only atomic values in each column.
Result:
Main student table: Stud_ID , Name , Location ,
Level , Level_Mgr
Telephone table: Stud_ID , Tel
Subject table: Stud_ID , Subject , Subj-Desc , G

Summary
Before 1NF: One table contains multivalued attributes,
repeating groups, and non-atomic values.
After 1NF: The data is split into multiple related tables with
atomic values, removing redundancy and improving
consistency.

Lecture 4: Second Normal Form (2NF)

What is Second Normal Form (2NF)?
Definition:
A table is in 2NF if:
a. It is already in First Normal Form (1NF) (no multivalued
attributes or repeating groups).
b. No partial dependency exists — meaning no non-key
attribute should depend on part of a composite primary
key.
Key Terms
Partial Dependency:
When a non-key attribute depends on part of a composite
primary key (instead of the whole key).
This usually happens when the table’s primary key consists of
two or more attributes.
❌ Not allowed in 1NF/2NF)
Example of a multivalued attribute (

Student_ID Name Phone Numbers

101 Ali 0101111111,
0112222222
102 Sara 0103333333

Here, Phone Numbers is a multivalued attribute for Ali — there

are two numbers in one cell.
This breaks 1NF, and therefore you can’t be in 2NF either.
Non-key attribute:
An attribute that is not part of any candidate key.

How the Problem Happens

In the School Example:
The table has composite keys in some relations (e.g.,
Stud_ID + Subject ).
Some attributes (like Tel or Subj-Desc ) only depend
on part of the composite key.
Tel depends only on Stud_ID
Subj-Desc depends only on Subject
This violates 2NF.

The Solution (How to Achieve 2NF)

Step 1: Identify non-key attributes that depend only on part of
a composite key.
Step 2: Move them into new tables with the key they depend
on.
From the image:
1. From main student table → separate Tel table:
Stud_ID → Tel
2. From student-subject table → separate Subject table:
Subject → Subj-Desc
The foreign key (FK) links them back to the main table.

Result After 2NF

Main Student Table: Stud_ID , Name , Location ,
Level , Level_Mgr
Tel Table: Stud_ID , Tel
Student_Subject Table: Stud_ID , Subject , G
Subject Table: Subject , Subj-Desc
This ensures:
No partial dependency
Every non-key attribute fully depends on the entire primary
key

Example: Course Enrollment Table

Before 2NF (problem with partial dependency):

Student_ Course_I Student_ Student_ Course_ Grade

ID D Name Email Name
S1 C1 Ali ali@email Databas A
Ahmed .com e
Systems
S1 C2 Ali ali@email Networki B
Ahmed .com ng
Basics
S2 C1 Mona mona@e Databas A
Adel mail.com e
Systems
Problems:

1. Composite Key: (Student_ID, Course_ID) is the

primary key.
2. Partial Dependency:

Student_Name and Student_Email depend

only on Student_ID .
Course_Name depends only on Course_ID .
This causes repetition and data redundancy.

After 2NF (fixing partial dependency):

Students Table

Student_ID Student_Name Student_Email

S1 Ali Ahmed ali@email.com
S2 Mona Adel mona@email.com

Courses Table

Course_ID Course_Name
C1 Database Systems
C2 Networking Basics

Enrollments Table

Student_ID Course_ID Grade

S1 C1 A
S1 C2 B
S2 C1 A

Now every non-key attribute is fully dependent on the

whole primary key in its table.
No partial dependencies remain.
Data redundancy is reduced.
Lecture 5: Third Normal Form (3NF)
What is Third Normal Form (3NF)?
Definition:
A table is in Third Normal Form (3NF) if:
1. It is already in Second Normal Form (2NF) (no multivalued
attributes, no partial dependencies).
2. No transitive dependency exists — meaning non-key
attributes must depend only on the primary key, not on
another non-key attribute.

Key Terms
Transitive Dependency:
When a non-key attribute depends on another non-key
attribute, which in turn depends on the primary key.
This creates indirect dependency.
Causes redundancy and update anomalies.
Non-key attribute:
Any column that is not part of a candidate key.

How the Problem Happens

Example — in a school database:

Stud_ID Name Location Level Level_Mgr

1 Ali Cairo L1 Ahmed
2 Sara Giza L2 Mona

Primary Key: Stud_ID

Problem:
Level_Mgr depends on Level (non-key attribute),
not directly on Stud_ID .
This is a transitive dependency:
1 Stud_ID → Level → Level_Mgr
The Solution (How to Achieve 3NF)
Step 1: Identify non-key attributes that depend on other non-key
attributes.
Step 2: Move them into a new table where the non-key attribute
becomes a key.
From the image:
Create a new Level table:
1 Level → Level_Mgr

Remove Level_Mgr from the Student table.

Result After 3NF

Student Table:

Stud_ID Name Location Level

1 Ali Cairo L1
2 Sara Giza L2

Level Table:

Level Level_Mgr
L1 Ahmed
L2 Mona

Example: Employee Database

Before 3NF (Problem with Transitive Dependency):

Emp_ID Emp_Nam Dept_ID Dept_Nam Dept_Loca

e e tion
1 Khaled D1 IT Cairo
2 Mariam D2 HR Giza
3 Youssef D1 IT Cairo
Problems:
Primary key: Emp_ID
Dept_Name and Dept_Location depend on
Dept_ID (non-key), not directly on Emp_ID .
Transitive dependency:
1 Emp_ID → Dept_ID → Dept_Name, Dept_Location

After 3NF (Fixing Transitive Dependency):

Employees Table:

Emp_ID Emp_Name Dept_ID

1 Khaled D1
2 Mariam D2
3 Youssef D1

Departments Table:

Dept_ID Dept_Name Dept_Location

D1 IT Cairo
D2 HR Giza

Why 3NF is Important:

Removes transitive dependencies.
Reduces data redundancy.
Makes updates easier (change a department location in one
place only).
Ensures every non-key attribute depends only on the key.