DATA MODELS &

DATABASE Fundamentals with

Real-world Examples

SCHEMA
WHAT ARE DATA
MODELS?
A Data Model is basically a way to organize,
structure, and represent data.
It defines how data is stored, connected, and
manipulated inside a database.
Types of Data Models:
• Hierarchical Model – Tree structure (e.g., File System)
• Network Model – Graph structure (e.g., Airline
Routes)
• Relational Model – Tables (e.g., Amazon Orders,
Students)
• ER Model – Entities, Attributes, Relationships (e.g.,
Student enrolls in Course)
HIERARCHICAL DATA
MODEL
A Hierarchical Data Model organizes data into a
tree structure, where:
1. Each record (called a node) has one parent
(except the root).
2. A parent node can have multiple child nodes.
3. Data is accessed through navigational paths
(top → down).

Example: File system in your computer (folders inside

folders).
NETWORK MODEL
The Network Model represents data using nodes
(records) and edges (relationships/sets) in a
graph structure.
Records = data items (like tables/objects).
Sets = relationships between records.
Supports many-to-many relationships directly.
RELATIONAL MODEL
Relational Model stores data in the form of tables
(relations).
Rows (tuples/records) → actual data entries.
Columns (attributes/fields) → properties of the data.
Each table has a primary key (unique identifier).
Tables can be connected to each other using keys
(foreign key).

In short: Data is stored in tables and relationships are

made by keys.
Example: Students table, Courses table, Enrollments
table.
ENTITY-RELATIONSHIP
MODEL (ER MODEL)
ER Model is a conceptual design model for
databases.
It represents:
Entities → Real-world objects (like Student, Course).
Attributes → Properties of entities (like Student has
Name, RollNo).
Relationships → How entities are connected (Student
enrolls in Course).
Think of it as a blueprint of the database, made
before creating tables.
DATABASE SCHEMA
LEVELS
A Database Schema is the structure or blueprint
of a database that defines how data is organized and
how relationships between data are handled.
It describes:
The tables in the database
The columns/attributes in each table
The data types of each column
The keys (Primary Key, Foreign Key, etc.)
The relationships between tables
TYPES OF SCHEMA
• Conceptual Schema – High-level, defines entities &
relationships (ER Diagram).
• Logical Schema – Converts design into relational
tables (Student, Course, Teacher, Enrollment).
• Physical Schema – Actual implementation (Indexes,
Partitions, Storage Engines).
CONCEPTUAL
SCHEMA (HIGH-LEVEL
DESIGN)
What data we need?
Represents the overall structure of the database.
Focuses on entities, attributes, and relationships.
Independent of any database technology.
Usually designed using ER diagrams.
LOGICAL SCHEMA
(DBMS-SPECIFIC
DESIGN)
How data will be organized in the database?
Converts conceptual design into tables, columns,
and keys.
Still independent of hardware/physical storage,
but dependent on DB type (Relational, NoSQL, etc.).
Uses Relational Model (tables, PK, FK).
PHYSICAL SCHEMA
(LOW-LEVEL
IMPLEMENTATION)
How data will be stored on disk?
Describes physical storage details → indexes,
partitions, data types, clusters.
DBMS-specific implementation (MySQL, Oracle,
PostgreSQL, etc.).
Performance, optimization, and hardware details
matter here.
REAL-WORLD ANALOGY:
HOUSE DESIGN
• Conceptual Schema → Architect’s Sketch (Rooms,
Kitchen, Bathroom).
• Logical Schema → Engineering Blueprint
(Dimensions, Materials).
• Physical Schema → Actual Construction (Bricks,
Wiring, Plumbing).
SUMMARY
• Data Models: Different ways to represent data (Tree,
Graph, Tables).
• Conceptual Schema: Abstract view of entities &
relationships.
• Logical Schema: Tables, attributes, keys.
• Physical Schema: How data is stored on disk with
indexes/partitions.
DATA INTEGRITY
Ensures that data is accurate, consistent, and reliable
in the database. Basically Rules that ensure the data in a
database is accurate, valid, and consistent.
Types of Key and Constraints
1. Primary Key
2. Candidate Key
3. Super Key
4. Unique Constraint
5. Not Null Constraint
6. Check Constraint
7. Foreign Key
PRIMARY KEY
A Primary Key is a column (or a set of columns) in a database
table that:
Uniquely identifies each row/record.
Cannot be NULL (must always have a value).
There can be only one Primary Key in a table.

CREATE TABLE Student (

StudentID INT PRIMARY KEY,
Name VARCHAR(100),
Dept VARCHAR(50),
Email VARCHAR(100)
);
CANDIDATE KEY
A Candidate Key is a column (or set of columns) in a
table that can uniquely identify each row.
From these candidate keys, one is chosen as the
Primary Key.
CREATE TABLE Student (
StudentID INT PRIMARY KEY,
Email VARCHAR(100) UNIQUE
AadhaarNo VARCHAR(20) UNIQUE
Name VARCHAR(100)
);
SUPER KEY
A Super Key is any set of one or more attributes
(columns) that can uniquely identify a row (tuple) in a
table.

 A Super Key may contain extra/unnecessary

attributes.
 Every Primary Key is a Super Key, but not every
Super Key is a Primary Key.
CHECK CONSTRAINT
A CHECK constraint in SQL is a rule applied to a
column that ensures only values satisfying a specific
condition are allowed in that column.

CREATE TABLE Student (

StudentID INT PRIMARY KEY,
Name VARCHAR(100),
Age INT CHECK (Age >= 18)
);
UNIQUE CONSTRAINT
A Unique Constraint in a database is a rule that
ensures all the values in a column (or a group of
columns) are unique across the table.
NOTE:- A Primary Key is also unique, but a UNIQUE
constraint can be applied separately as well.
CREATE TABLE Students (
student_id INT PRIMARY KEY,
email VARCHAR(100) UNIQUE,
phone VARCHAR(15) UNIQUE
);
FOREIGN KEYS
A Foreign Key is a column that links one table to
another.
It ensures referential integrity → meaning, the value
in the child table must exist in the parent table.
Parent Table:-
CREATE TABLE Department (
DeptID INT PRIMARY KEY,
DeptName VARCHAR(100)
);
Child Table with Foreign Key
CREATE TABLE Employee (
EmpID INT PRIMARY KEY,
Name VARCHAR(100),
DeptID INT NOT NULL,
FOREIGN KEY (DeptID) REFERENCES Department(DeptID)
);
You cannot insert an employee without a DeptID.
You cannot insert a DeptID that doesn’t exist in
Department.
WHAT IS DATABASE
DESIGN?
Database design means the process of planning
and structuring how data will be stored,
organized, and managed in a database so that it is
accurate, efficient, secure, and supports all application
requirements.
STEPS IN DATABASE
DESIGN:
Requirement Analysis
Understand what data needs to be stored.
Example: In a college management system, you need
data about Students, Teachers, Courses, and Results.
Conceptual Design (ER Model)
Identify entities (e.g., Student, Course) and
relationships (e.g., Student enrolls in Course).
Represent using an Entity-Relationship (ER)
Diagram.
Logical Design
•Convert ER model into tables (relations) with attributes, keys, and constraints.

Normalization
Organize data to remove redundancy and inconsistency (1NF,
2NF, 3NF, BCNF).

Physical Design
Decide how tables will be stored in the DBMS (indexes, partitions,
storage).
GOALS OF DATABASE
DESIGN:

•Ensure Data Integrity (correct & consistent data).

•Avoid Data Redundancy (duplicate data).
•Ensure Data Security (only authorized access).
•Optimize Performance (fast queries and updates).
•Provide Scalability (handle more data in the future).
DATABASE DESIGN
LIFECYCLE PHASES:
Requirement Analysis,
Logical Design,
 Physical Design,
Object Oriented Design Principles
REQUIREMENT
ANALYSIS
This is the first and most important step.
In this step, we interact with users and stakeholders
to gather requirements.
The focus is on understanding what data the
business needs and what kinds of queries or
reports they will require in the future.
In this step, we identify the entities (objects), their
attributes (properties), and the relationships
between them.
Example:
If we are designing a College
Database, then in Requirement
Analysis we will find out:
• Student details need to be stored (ID, Name,
Email, Phone, etc.)
• Course details (CourseID, Title, Credits)
• Faculty details
• The relationship between Student and
Course (Enrollment)
Output: A rough document containing
the list of entities and their
attributes.
LOGICAL DESIGN
This is the second step in database design.
In this step, we convert the requirements (collected earlier)
into a logical model that represents the database structure.
The focus here is on what data will be stored and how
entities are related, without worrying about physical storage
or performance yet.
In this step, we:
Create Entity-Relationship (ER) Diagrams
Define entities, attributes, and relationships more formally
Identify primary keys, foreign keys, and candidate keys
Apply Normalization to remove redundancy and improve
consistency
PHYSICAL DESIGN

This is the third step in database design.

In this step, we take the logical model and convert it into an actual
implementation in a specific Database Management System (DBMS).
The focus is on how the data will be stored, accessed, and optimized
in the real database.
In this step, we:
•Choose the DBMS (MySQL, PostgreSQL, Oracle, MongoDB, etc.)
•Create tables with proper data types INT, VARCHAR, DATE, etc)
•Define indexes for faster searching
•Set up constraints (Primary Key, Foreign Key, Unique, Not Null, Check)
•Decide storage details (partitioning, clustering, file organization)
•Plan for performance, security, and backups