A database is an organized collection of data that can be easily accessed, managed,

and updated.

In simple words — it’s a place where data is stored in a structured way so that we can
quickly find and use it when needed.

Example:

Think of a student record system at your university. It stores:

• Student names

• Roll numbers

• Courses

• Grades

All this information is kept in a database.

Main Features:

1. Data Storage – Stores large amounts of data efficiently.

2. Data Retrieval – Lets you search or fetch data quickly.

3. Data Management – You can insert, update, or delete records.

4. Security – Controls who can see or change the data.

5. Integrity – Ensures data remains correct and consistent.

Types of Databases:

1. Relational Database (RDBMS) – Data stored in tables (e.g., MySQL, Oracle, SQL
Server).

2. NoSQL Database – For unstructured data (e.g., MongoDB).

3. Cloud Databases – Hosted online (e.g., Firebase, AWS DynamoDB).

4. Graph Databases – Show relationships (e.g., Neo4j).

The file-based approach is the old method of storing data — before databases were
introduced.

Let’s go step by step

Definition:

The file-based approach means storing data in separate files on a computer system (like
text files, spreadsheets, or binary files).
Each program (application) directly reads from or writes to these files to manage data.

Example:

Suppose your university stores student data like this:

• [Link] → contains names and roll numbers

• [Link] → contains course details

• [Link] → contains student marks

If you want to find a student’s marks, your program has to manually open all files and
search line by line.

Characteristics:

• Data stored in separate files.

• Each file is handled by its own application.

• No central system to manage the data.

Disadvantages (Drawbacks):

1. Data Redundancy – Same data may be stored in multiple files.

2. Data Inconsistency – One file may have updated info while another doesn’t.

3. Difficult to Access – No standard query language (like SQL).

4. Poor Security – Anyone can open or edit files.

 5. No Concurrent Access – Only one user can modify at a time.

6. Hard to Maintain – Changing file structure means changing every program.

Why Databases Replaced It:

The database approach solved these problems by providing:

• Centralized storage

• Data independence

• Security and controlled access

• SQL for easy queries

• Reduced redundancy

Let’s talk about the roles in a database environment — meaning the different people and
components that work together to manage and use a database system.

Database Environment Overview:

A database environment includes:

• The database itself (data)

• The DBMS (software that manages data)

• The hardware

• The users (people)

• The procedures (rules/policies)

Now, let’s focus on the main roles

1. Database Administrator (DBA):

The DBA is the person responsible for managing the whole database system.

Main tasks:
 • Installing and configuring the DBMS

• Creating and maintaining databases

• Setting up user accounts and permissions

• Backup and recovery of data

• Ensuring security and performance

Think of the DBA as the database manager or boss of the database.

2. Database Designer:

Designs the structure of the database before it’s created.

Main tasks:

• Identify what data needs to be stored

• Create E-R diagrams / EER models

• Design tables, relationships, and constraints

Think of them as the architect of the database.

3. Application Programmer (Developer):

Writes programs or applications that interact with the database.

Main tasks:

• Use languages like SQL, Java, Python, etc.

• Write code to insert, update, and retrieve data

• Connect the front-end (UI) with the database

They build the software that uses the database.

4. End Users:

People who use the applications to get or enter data.

Types:

• Naïve users: Use prebuilt forms (like ATM users).

• Casual users: Run simple queries sometimes.

• Sophisticated users: Write their own queries (like analysts).

They are the actual users of data.

5. System Analyst:

Understands user requirements and helps developers design database systems that meet
business goals.

Acts as a bridge between users and developers.

Summary Table:

Role Responsibility

Database Administrator (DBA) Controls and manages the database system

Database Designer Plans the structure of data

Application Programmer Writes programs to access data

End Users Use the system to perform daily tasks

System Analyst Connects user needs with technical design

DDL vs DML (in DBMS)

1. DDL — Data Definition Language

DDL is used to define or change the structure of a database.

It deals with schema (tables, columns, constraints, etc.).

Common DDL Commands:

Command Purpose

CREATE Create a new table or database

ALTER Modify structure (add/remove columns)

DROP Delete a table or database

TRUNCATE Remove all records but keep table structure

Think of DDL as “designing the database.”

Example:

CREATE TABLE Students (

RollNo INT,

Name VARCHAR(50),

Age INT

);

2. DML — Data Manipulation Language

DML is used to manipulate or handle data inside the tables.

It deals with records (rows) — inserting, updating, deleting, or fetching data.

Common DML Commands:

Command Purpose

INSERT Add new records

UPDATE Modify existing records

DELETE Remove records

SELECT Retrieve data

Think of DML as “using the database.”

Example:

INSERT INTO Students (RollNo, Name, Age)

VALUES (1, 'Saif', 20);

Main Differences:

Basis DDL DML

Full Form Data Definition Language Data Manipulation Language

Manipulates the data inside

Purpose Defines database structure
tables

Affects Whole table/schema Specific rows or records

Yes (changes are saved

Auto Commit No (you can rollback)
automatically)

Example INSERT, UPDATE, DELETE,

CREATE, ALTER, DROP
Commands SELECT

DBMS – Database Management System

A DBMS (Database Management System) is software that helps you store, organize, and
manage data in a structured way.

It acts as a bridge between the user (or application) and the database itself.

Simple Example:

Imagine you have a Student Information System.

You don’t directly handle the data files — instead, you use software (like Oracle, MySQL, or
SQL Server) to manage them.
That software is the DBMS.

Main Functions of DBMS:

1. Data Storage Management – Stores data efficiently on disk.

 2. Data Retrieval – Lets users search or query data easily (using SQL).

3. Data Manipulation – Allows adding, updating, or deleting records.

4. Data Security – Controls who can access or modify data.

5. Backup & Recovery – Protects data from loss or damage.

6. Concurrency Control – Manages multiple users accessing data at once.

7. Data Integrity – Ensures accuracy and consistency of data.

Why We Use DBMS (Advantages):

Advantage Description

Reduced Data Redundancy Data stored once, not in many files

Data Consistency Same data everywhere

Data Security Access control and permissions

Data Sharing Multiple users can work together

Backup and Recovery Prevents data loss

Data Independence Change data structure without changing programs

Common Examples of DBMS:

• Oracle

• MySQL

• Microsoft SQL Server

• PostgreSQL

• MongoDB (for NoSQL)

In short:
DBMS = Software that stores, manages, and provides access to data efficiently and
securely.

What is Database Architecture?

Database architecture means the design or structure of how data, users, and the DBMS
software interact with each other.

In simple words — it shows how data flows between:

• The user,

• The application, and

• The database.

There are mainly two types of architectures you must know:

1. Two-tier architecture

2. Three-tier architecture

And in DBMS theory, there’s also the three-level architecture (conceptual model by
ANSI/SPARC).

Let’s cover both parts clearly

1. Three-Level (ANSI/SPARC) Database Architecture

This model focuses on data abstraction — hiding the details of how data is stored.

It has 3 levels:

Level Description

External Level (View How users see the data (user’s view). Each user may have a
Level) different view.
Level Description

Conceptual Level (Logical Overall structure of the database — defines tables,

Level) relationships, and constraints.

Internal Level (Physical

How data is physically stored on disk (storage, indexing, etc.).
Level)

Purpose:

• To provide data abstraction and independence (changes at one level don’t affect
others).

2. Two-Tier Architecture

Used in client-server systems.

Structure:

• Client (Front-End): User application (like a form or interface).

• Server (Back-End): Database server (like MySQL, Oracle).

Example:
When you open a desktop app that directly connects to a database server using SQL
queries.

Communication:
Client → DB Server → Result back to Client

3. Three-Tier Architecture

Used in modern web applications — adds one more layer between client and database.

Layers:

1. Presentation Layer (Client) – User interface (like a website or app).

2. Application Layer (Middle Layer) – Business logic (e.g., Java, PHP, .NET).

3. Database Layer (Server) – Stores and manages data (e.g., MySQL, Oracle).
Example:
When you use a university web portal:

• Browser = Presentation layer

• Server-side app (like PHP) = Application layer

• Database (MySQL) = Database layer

Summary Table:

Architecture Levels Example Used In

Three-Level External, Conceptual, Theoretical DBMS

Data abstraction
(ANSI/SPARC) Internal design

Client-server
Two-Tier Client and Server Desktop DB apps
systems

Three-Tier UI, Logic, Database Web apps Modern systems

Relational Model — Overview

The Relational Model (introduced by E.F. Codd in 1970) organizes data into tables
(relations) made up of rows and columns.
Each table represents one type of entity (like Student, Employee, Course, etc.).

Main Terminology in Relational Model

Term Description Example

A table in the database. It consists of

Relation STUDENT, EMPLOYEE
rows and columns.

A row (record) in the table. It One student’s data (e.g., RollNo 1,

Tuple
represents one instance of data. Name Saif)
Term Description Example

A column in the table. It represents a

Attribute Name, RollNo, Age
property of the entity.

The set of possible values an Age → 18–30, Gender → {Male,

Domain
attribute can take. Female}

The number of attributes (columns)

Degree If 4 columns → Degree = 4
in a relation.

The number of tuples (rows) in a

Cardinality If 10 rows → Cardinality = 10
relation.

Relation The structure or definition of a table

STUDENT(RollNo, Name, Age, Dept)
Schema (table name + attributes).

Relation The actual data stored in the table at

The filled rows in the STUDENT table
Instance a specific time.

An attribute (or set of attributes) that

Key RollNo, StudentID
uniquely identifies a row.

A unique identifier for each row in a

Primary Key RollNo in STUDENT
table.

An attribute that links one table to DeptID in STUDENT references

Foreign Key
another. DeptID in DEPARTMENT

Candidate All possible attributes that can act as

RollNo, CNIC
Key a Primary Key.

The candidate keys not chosen as

Alternate Key CNIC (if RollNo is primary)
the primary key.

Composite A key made from more than one

(CourseID, StudentID)
Key attribute.

Represents missing or unknown

Null Value Address = NULL
data.
 Example Table: STUDENT

RollNo Name Age DeptID

1 Saif 20 D1

2 Ali 21 D2

3 Ayesha 19 D1

• Relation: STUDENT

• Tuples: 3

• Attributes: 4 (RollNo, Name, Age, DeptID)

• Degree: 4

• Cardinality: 3

• Primary Key: RollNo

• Foreign Key: DeptID (references DEPARTMENT table)

In short:

The Relational Model organizes data into tables (relations) where each row = record
(tuple) and each column = attribute.

Relational Algebra vs Relational Calculus

Both are formal languages used to query relational databases — but they work in
different ways.

1. Relational Algebra

Definition:

Relational Algebra is a procedural query language —

it tells the how (the steps or operations) to get the required data.
 In simple words:

It focuses on how to get the answer.

Basic Operations of Relational Algebra:

Symbol /
Operation Description
Keyword

SELECT σ (sigma) Selects rows (tuples) based on condition

PROJECT π (pi) Selects columns (attributes)

UNION ∪ Combines results of two relations

SET DIFFERENCE − Returns tuples in one relation but not in another

CARTESIAN Combines every tuple of one relation with every tuple

×
PRODUCT of another

RENAME ρ (rho) Changes the name of a relation or attributes

Combines related tuples from two relations based

JOIN ⨝
on a common attribute

Example:

Let’s say we have a table STUDENT(RollNo, Name, Dept)

and another table DEPARTMENT(Dept, HOD)

Find names of students in ‘CS’ department:

Relational Algebra Expression:

π(Name) (σ(Dept = 'CS')(STUDENT))

🗣 Meaning:

• σ (select) only those rows where Dept = 'CS'

• π (project) only the Name column

 2. Relational Calculus

Definition:

Relational Calculus is a non-procedural query language —

it tells the what (the condition or property) of the data to retrieve,
not how to get it.

In simple words:

It focuses on what you want, not how to get it.

Types of Relational Calculus:

Type Description

Tuple Relational Calculus (TRC) Uses tuples (rows) as variables.

Domain Relational Calculus (DRC) Uses domains (attributes) as variables.

Example:

Tuple Relational Calculus (TRC):

{ [Link] | t ∈ STUDENT ∧ [Link] = 'CS' }

→ Get the Name of all students where Dept = 'CS'.

Domain Relational Calculus (DRC):

{ <Name> | ∃ RollNo, Dept (STUDENT(RollNo, Name, Dept) ∧ Dept = 'CS') }

→ Get Name of all students who belong to ‘CS’ department.

Main Differences:

Basis Relational Algebra Relational Calculus

Type Procedural Non-Procedural

Focus How to retrieve data What data to retrieve

Basis Relational Algebra Relational Calculus

Uses algebraic operations (σ, π, ∪, ×, Uses logical expressions (∃, ∀, ∧,

Operations
⨝) ⇒)

Approach Step-by-step Declarative (condition-based)

Example
π(Name)(σ(Dept='CS')(STUDENT)) `{ [Link]
Query

Used in Theoretical foundation for SQL Basis for declarative SQL syntax

In short:

Relational Algebra → HOW to get data (procedural)

Relational Calculus → WHAT data you want (non-procedural)

What is a Join?

A Join operation in Relational Algebra is used to combine related tuples (rows) from two
relations (tables) based on a common attribute.

In simple words:

Join connects two tables using a related column (usually a key).

General Join Notation:

R ⨝condition S

• R and S are two relations (tables)

• condition defines how they are related (e.g., [Link] = [Link])

Types of Joins in Relational Algebra

Let’s discuss all the main types

 1. Theta Join (θ-Join)

• Combines tuples from two relations based on a condition using comparison

operators like =, <, >, <=, >=, ≠.

Syntax:

R ⨝θ S

Example:

STUDENT ⨝ ([Link] = [Link]) DEPARTMENT

Meaning: Join STUDENT and DEPARTMENT where their DeptID values match.

It’s the most general form of join — others are special cases of it.

2. Equi Join

• A special case of Theta Join where the condition uses only =.

Syntax:

R ⨝(R.A = S.B) S

Example:

STUDENT ⨝([Link] = [Link]) DEPARTMENT

It keeps both columns (even the duplicate join attributes).

3. Natural Join (⨝)

• A special type of Equi Join where:

o The join is automatically done on attributes with the same name in both
tables.

o It removes duplicate columns.

Syntax:

R⨝S
Example:

STUDENT ⨝ DEPARTMENT

If both have an attribute DeptID, the join will automatically match them.

Natural Join = Equi Join + Remove duplicates

4. Outer Joins

Outer joins keep unmatched tuples from one or both relations and fill missing values with
NULLs.

Type Description

Keeps all tuples from the left relation and matching ones from the
Left Outer Join (⟕)
right.

Right Outer Join Keeps all tuples from the right relation and matching ones from the
(⟖) left.

Full Outer Join (⟗) Keeps all tuples from both relations.

Example (Left Outer Join):

STUDENT ⟕ DEPARTMENT

Keeps all students, even those who don’t belong to any department.

5. Self Join

• A relation joined with itself.

• Used when comparing rows within the same table.

Example:

EMPLOYEE ⨝ ([Link] = [Link]) EMPLOYEE

Used to find employees and their managers.

Summary Table:
 Keeps Unmatched Duplicate
Join Type Condition
Rows? Columns?

Theta Join Any comparison No Yes

Equi Join Equality (=) only No Yes

Automatically on same-named
Natural Join No No
attributes

Left Outer
Equality (=) Yes (left side) Yes
Join

Right Outer
Equality (=) Yes (right side) Yes
Join

Full Outer
Equality (=) Yes (both sides) Yes
Join

Self Join Join with same table Depends Yes

Example Tables:

STUDENT

StuID Name DeptID

1 Saif D1

2 Ali D2

3 Ayesha D3

DEPARTMENT

DeptID DeptName

D1 CS

D2 SE
Natural Join Result:

StuID Name DeptID DeptName

1 Saif D1 CS

2 Ali D2 SE

(Notice Ayesha (D3) didn’t appear — because D3 isn’t in DEPARTMENT)

What is Relational Calculus?

Relational Calculus is a non-procedural query language used in relational databases.

In simple words:

It focuses on what data you want to retrieve —

not how to retrieve it.

Relational Algebra vs Relational Calculus

Concept Relational Algebra Relational Calculus

Type Procedural (step-by-step process) Non-procedural (only conditions)

Focus How to get data What data to get

Example π(Name)(σ(Dept='CS')(STUDENT)) `{ [Link]

So, Relational Calculus is more about logic and condition rather than operations.

Types of Relational Calculus

There are two types:

1. Tuple Relational Calculus (TRC)

2. Domain Relational Calculus (DRC)

Let’s understand both

 1. Tuple Relational Calculus (TRC)

• In TRC, we use tuples (rows) as variables.

• Queries are written using logical expressions (AND ∧, OR ∨, NOT ¬, EXISTS ∃, FOR
ALL ∀).

General Syntax:

{ t | Condition(t) }

• t → a tuple variable (represents a row)

• Condition(t) → condition that tuple t must satisfy

Example:

Query: Get the names of students in the 'CS' department.

{ [Link] | t ∈ STUDENT ∧ [Link] = 'CS' }

Meaning:
→ “Select Name from all tuples t in STUDENT where Dept = 'CS'.”

Example 2:

Get names and roll numbers of students older than 20:

{ [Link], [Link] | t ∈ STUDENT ∧ [Link] > 20 }

2. Domain Relational Calculus (DRC)

• In DRC, we use domain variables — i.e., variables that represent individual

attribute values (columns), not whole tuples.

General Syntax:

{ <x1, x2, ..., xn> | Condition(x1, x2, ..., xn) }

• Each variable (x1, x2, etc.) represents a column value.

• The condition defines what values are selected.

 Example:

Get names of students in 'CS' department:

{ <Name> | ∃ RollNo, Dept (STUDENT(RollNo, Name, Dept) ∧ Dept = 'CS') }

Meaning:
→ Select Name from STUDENT where Dept = 'CS'.

Example 2:

Get RollNo and Name of students older than 20:

{ <RollNo, Name> | ∃ Age, Dept (STUDENT(RollNo, Name, Age, Dept) ∧ Age > 20) }

Key Points to Remember:

Concept Tuple Relational Calculus Domain Relational Calculus

Based on Tuples (rows) Domains (columns)

Variables Represent entire rows Represent individual attribute values

Syntax `{ t P(t) }`

Example `{ [Link] t ∈ STUDENT ∧ [Link]='CS' }`

Language Type Non-Procedural Non-Procedural

Focus Logic-based queries Attribute-based queries

In short:

Relational Calculus = Mathematical, non-procedural way of querying data.

TRC → Uses tuples (rows).
DRC → Uses domains (columns).
Would you like me to show a side-by-side example of the same query written in:

• Relational Algebra

• Tuple Relational Calculus

• Domain Relational Calculus

That’s an excellent way to compare and memorize them for exams.

What is a JOIN?

Join ka matlab hota hai:

Do ya zyada tables ke data ko common column ke basis par combine karna.

Suppose our two tables are:

STUDENT

Stu_ID Stu_Name Dept_ID

1 Ali 10

2 Saif 20

3 Ahsan 30

4 Sara 20

DEPARTMENT

Dept_ID Dept_Name

10 CS

20 SE

30 IT

40 AI
 1. INNER JOIN

Sirf wo records show karta hai jinka match dono tables mai hota hai.

Query:

SELECT STUDENT.Stu_Name, DEPARTMENT.Dept_Name

FROM STUDENT

INNER JOIN DEPARTMENT

ON STUDENT.Dept_ID = DEPARTMENT.Dept_ID;

Result:

Stu_Name Dept_Name

Ali CS

Saif SE

Ahsan IT

Sara SE

Dept_ID = 40 (AI) ka match nahi mila, isliye wo skip ho gaya.

2. LEFT JOIN (or LEFT OUTER JOIN)

Left table ke saare records show karega,

aur agar right table mai match na mile to NULL show karega.

Query:

SELECT STUDENT.Stu_Name, DEPARTMENT.Dept_Name

FROM STUDENT

LEFT JOIN DEPARTMENT

ON STUDENT.Dept_ID = DEPARTMENT.Dept_ID;

Result:
Stu_Name Dept_Name

Ali CS

Saif SE

Ahsan IT

Sara SE

Yahan sab match mil gaye, isliye NULL nahi aya —

agar koi student ka Dept_ID 50 hota, to Dept_Name NULL hota.

3. RIGHT JOIN (or RIGHT OUTER JOIN)

Right table ke saare records show karega,

aur agar left table mai match na ho to NULL show karega.

Query:

SELECT STUDENT.Stu_Name, DEPARTMENT.Dept_Name

FROM STUDENT

RIGHT JOIN DEPARTMENT

ON STUDENT.Dept_ID = DEPARTMENT.Dept_ID;

Result:

Stu_Name Dept_Name

Ali CS

Saif SE

Ahsan IT

Sara SE

NULL AI

Dept_ID = 40 (AI) ka koi student nahi hai → NULL aya.

 4. FULL JOIN (or FULL OUTER JOIN)

Dono tables ke saare records show karega.

Agar match nahi mila to NULL.

Query:

SELECT STUDENT.Stu_Name, DEPARTMENT.Dept_Name

FROM STUDENT

FULL OUTER JOIN DEPARTMENT

ON STUDENT.Dept_ID = DEPARTMENT.Dept_ID;

Result:

Stu_Name Dept_Name

Ali CS

Saif SE

Ahsan IT

Sara SE

NULL AI

5. CROSS JOIN

Har record ko har record ke sath combine karta hai —

yaani cartesian product.

Query:

SELECT STUDENT.Stu_Name, DEPARTMENT.Dept_Name

FROM STUDENT

CROSS JOIN DEPARTMENT;

Result:
Stu_Name Dept_Name

Ali CS

Ali SE

Ali IT

Ali AI

Saif CS

Saif SE

Saif IT

Saif AI

... ...

4 STUDENT × 4 DEPARTMENT = 16 rows total!

6. SELF JOIN

Ek hi table ko khud se join karna (useful jab parent-child relation ho).

Example:

Suppose hamesha EMPLOYEE table hai:

Emp_ID Emp_Name Manager_ID

1 Ali NULL

2 Saif 1

3 Ahsan 1

4 Sara 2

Query:

SELECT E.Emp_Name AS Employee, M.Emp_Name AS Manager

FROM EMPLOYEE E
LEFT JOIN EMPLOYEE M

ON E.Manager_ID = M.Emp_ID;

Result:

Employee Manager

Ali NULL

Saif Ali

Ahsan Ali

Sara Saif

Summary Table

Join Type Description Matching/Non-Matching Data

INNER JOIN Only matching rows Matching only

LEFT JOIN All from left, matched from right Non-matching → NULL on right

RIGHT JOIN All from right, matched from left Non-matching → NULL on left

FULL JOIN All from both Non-matching → NULL both sides

CROSS JOIN Cartesian product All combinations

SELF JOIN Join same table with itself Based on relation

Relational Algebra (RA) mai joins operations ke form mai hotay hain —
ye tables (relations) ko combine karte hain common attributes (columns) ke basis par.

Suppose our two relations are:

STUDENT
Stu_ID Stu_Name Dept_ID

1 Ali 10

2 Saif 20

3 Ahsan 30

DEPARTMENT

Dept_ID Dept_Name

10 CS

20 SE

40 AI

Types of Joins in Relational Algebra

1. Theta Join (θ-Join)

Join based on a condition using comparison operators like (=, <, >, ≤, ≥, ≠).

Notation:

STUDENT ⋈ (STUDENT.Dept_ID = DEPARTMENT.Dept_ID) DEPARTMENT

Meaning: Combine those rows where the condition is true.

This is also called a conditional join.

Result:

Stu_ID Stu_Name Dept_ID Dept_Name

1 Ali 10 CS

2 Saif 20 SE

2. Equi Join
A special case of theta join where the condition uses only equality (=).

Notation:

STUDENT ⋈ (STUDENT.Dept_ID = DEPARTMENT.Dept_ID) DEPARTMENT

Result:
Same as above

Stu_ID Stu_Name Dept_ID Dept_Name

1 Ali 10 CS

2 Saif 20 SE

3. Natural Join (⋈)

Joins automatically on all common attributes (no need to specify condition).

It removes duplicate columns.

Notation:

STUDENT ⋈ DEPARTMENT

Result:

Stu_ID Stu_Name Dept_ID Dept_Name

1 Ali 10 CS

2 Saif 20 SE

Notice: Dept_ID common column hai — us par join hua.

4. Outer Joins

Outer joins in relational algebra show non-matching tuples as well, with NULLs for
missing values.

a. Left Outer Join (⟕)

All tuples from the left relation + matching from the right.

Notation:
STUDENT ⟕ DEPARTMENT

Result:

Stu_ID Stu_Name Dept_ID Dept_Name

1 Ali 10 CS

2 Saif 20 SE

3 Ahsan 30 NULL

b. Right Outer Join (⟖)

All tuples from the right relation + matching from the left.

Notation:

STUDENT ⟖ DEPARTMENT

Result:

Stu_ID Stu_Name Dept_ID Dept_Name

1 Ali 10 CS

2 Saif 20 SE

NULL NULL 40 AI

c. Full Outer Join (⟗)

All tuples from both relations (matching + non-matching).

Notation:

STUDENT ⟗ DEPARTMENT

Result:

Stu_ID Stu_Name Dept_ID Dept_Name

1 Ali 10 CS
Stu_ID Stu_Name Dept_ID Dept_Name

2 Saif 20 SE

3 Ahsan 30 NULL

NULL NULL 40 AI

5. Cartesian Product (×)

All combinations of tuples from both relations (no condition).

It’s the base for all joins.

Notation:

STUDENT × DEPARTMENT

Result:
(3 × 3 = 9 rows)
Every student paired with every department.

Summary Table

Join Type Symbol Condition Type Missing Data

Theta Join ⋈_θ Any comparison (>, <, =) Ignored

Equi Join ⋈ Only equality (=) Ignored

Natural Join ⋈ Auto by same attribute Ignored

Left Outer Join ⟕ Match + Left unmatched NULL

Right Outer Join ⟖ Match + Right unmatched NULL

Full Outer Join ⟗ Match + Both unmatched NULL

Cartesian Product × No condition All pairs

 Database System Development Life Cycle (DBSDLC)

The Database System Development Life Cycle (DBSDLC) describes the complete
process of designing, developing, and maintaining a database system — from the idea to
the final running system.

It ensures the database is well-structured, secure, and efficient for users and
applications.

Phases of Database System Development Life Cycle

Phase Description

Define the objectives, scope, and resources for the

database.
1. Database Planning Make sure it supports the organization’s goals.
Example: Deciding why the university needs a student
management database.

Define the system’s boundaries (what it will do and

what it won’t).
2. System Definition Identify users, operations, and interfaces.
Example: The system will store student data, courses,
and grades, but not manage payroll.

Gather user requirements through interviews,

surveys, and forms.
3. Requirements Collection
Identify what data is needed and how it will be used.
and Analysis
Example: Collect information about what teachers,
students, and admins need.

Convert requirements into a logical and physical

structure.
Includes:
4. Database Design
• Conceptual Design (EER diagram)
• Logical Design (Relational schema)
• Physical Design (Storage, indexing, performance).
Phase Description

Choose a Database Management System that fits the

design and needs.
5. DBMS Selection
Example: Choose between Oracle, MySQL, SQL
Server, PostgreSQL, etc.

Design the user interfaces, forms, reports, and

queries that will use the database.
6. Application Design
Example: Create screens for student login,
registration, and marks entry.

Create the database schema, tables, and

relationships.
7. Implementation
Insert sample data, write queries, and connect it with
the application code.

Test the database and application for errors,

performance, security, and user satisfaction.
8. Testing and Evaluation
Example: Check if queries fetch correct results and
access control works.

Database goes live (used in real environment).

9. Operation and
Regular backups, updates, performance tuning, and
Maintenance
bug fixes are done.

In Simple Words:

DBSDLC helps ensure that a database is planned, designed, implemented, and

maintained properly — just like the SDLC for software, but focused on data.

What is an ER Diagram?

An ER Diagram (Entity Relationship Diagram) is a visual representation of data and the

relationships between entities in a database.
It’s used during the database design phase to plan how data will be stored, connected,
and organized.

Main Components of an ER Diagram

Symbol / Term Description Example

A real-world object or concept about STUDENT, COURSE,

Entity (Rectangle)
which data is stored. TEACHER

Attribute (Oval) Properties or details of an entity. Stu_ID, Name, Age

Primary Key Unique identifier for each record in

Stu_ID
(Underlined Attribute) an entity.

Describes how two entities are

Relationship (Diamond) ENROLLS, TEACHES
connected.

Defines how many instances of one

Cardinality 1:1, 1:N, M:N
entity are related to another.

Weak Entity (Double DEPENDENT (depends on

Entity that depends on another entity.
Rectangle) EMPLOYEE)

Multivalued Attribute Attribute that can have multiple

Phone_Number
(Double Oval) values.

Derived Attribute
Attribute derived from other data. Age (from DOB)
(Dashed Oval)

Explanation:

• Entities: STUDENT, COURSE

• Relationship: ENROLLS (many-to-many relationship)

• Attributes:

o STUDENT: Stu_ID (PK), Name, Age

o COURSE: Course_ID (PK), Title, Credit_Hours

 • Relationship attribute (optional): Grade

One student can enroll in many courses, and one course can have many students →
Many-to-Many (M:N) relationship.

Cardinality Examples

Relationship Meaning Example

1:1 One entity relates to exactly one other. One student → One locker

1:N One entity relates to many others. One teacher → Many students

M:N Many entities relate to many others. Students ↔ Courses

ERD Symbols Summary

Symbol Meaning

▭ Entity

◇ Relationship

⃝ Attribute

⃝ Multivalued Attribute

⃝ (dashed) Derived Attribute

▭▭ Weak Entity

Why ER Diagrams Are Important

• Help visualize the structure of the database

• Identify relationships and dependencies

• Simplify database design before actual implementation

• Avoid data redundancy and inconsistency

 What is an EER Diagram?

The Enhanced Entity–Relationship (EER) Diagram is an extension of the ER model that

includes advanced concepts like specialization, generalization, inheritance, and
categories (union types).

It’s used when the data model is complex and the normal ER diagram can’t express all
relationships clearly.

EER Diagram = ER Diagram + Extra Concepts

Concept Description Example

Entity Object about which data is stored STUDENT, TEACHER

Attributes Properties of an entity Name, Age, Salary

Relationship Association between entities ENROLLS, TEACHES

Dividing a higher-level entity into sub- EMPLOYEE → TEACHER,

Specialization
entities ADMIN

Combining two or more entities into

Generalization CAR + TRUCK → VEHICLE
one higher-level entity

Superclass / The higher-level and lower-level EMPLOYEE (superclass),

Subclass entities in a hierarchy TEACHER (subclass)

Category (Union When a subclass can have more than OWNER ← PERSON +
Type) one parent entity COMPANY

Disjoint / Whether subclasses can share the TEACHER and ADMIN may or
Overlapping same entity instance may not overlap

Simple Example

Let’s take a University Example

 Entities

• PERSON (Superclass)
Attributes: Person_ID, Name, Address

• STUDENT (Subclass of PERSON)

Attributes: RollNo, CGPA

• TEACHER (Subclass of PERSON)

Attributes: EmpID, Salary

• COURSE
Attributes: Course_ID, Course_Name

• Relationship: TEACHER — “TEACHES” — COURSE

• Relationship: STUDENT — “ENROLLED_IN” — COURSE

EER Concepts Used

Concept Example

Superclass / Subclass PERSON → STUDENT, TEACHER

Specialization PERSON specialized into STUDENT and TEACHER

Disjoint constraint A PERSON can be either a STUDENT or a TEACHER (not both)

Participation constraint STUDENT and TEACHER must be part of PERSON

When to Use EER Instead of ER

ER Diagram EER Diagram

Simple database design Complex database design

No inheritance or subclasses Supports inheritance (ISA relationships)

Focuses on entities and relationships Adds specialization, generalization, categories

 In short:

The EER Diagram adds “real-world hierarchy” and “inheritance” to the basic ER model —
making it more expressive for large, complex systems.

Would you like me to draw a clear EER diagram image (PERSON–STUDENT–TEACHER–

COURSE example) for your notes? It’ll be perfect for visual understanding and exams.