📁 What is a Database Schema?
A Database Schema defines the structure of a database — including tables (entities), fields (attributes), and relationships between them. It's like the blueprint of your data.
🧩 Designing Schema Using ER (Entity-Relationship) Model
🔹 Identify Entities
Entities are real-world objects that we want to store data about.
Example Entities in a University Database:
StudentCourseProfessor
🔹 Identify Attributes
Every entity has some properties (attributes).
Student
StudentID(Primary Key)NameAge
Course
CourseID(Primary Key)TitleCredits
Professor
ProfID(Primary Key)NameDepartment
Department
DeptID(Primary key)DeptName
🔹 Identify Relationships
A Student can enroll in many Courses
A Course can be taught by one Professor
A Professor will belong to a Department
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>ER to Relational Model Builder</title>
<style>
.infographic-erbuild {
--ink: #1a1a1a;
--ink-soft: #5c5c5c;
--paper: #ffffff;
--paper-alt: #f0ece4;
--accent: #2563eb;
--accent-soft:#e3ebfd;
--line: #ddd6c9;
--on-accent: #ffffff;
--good: #16a34a;
--good-soft: #dcfce7;
--display-font: Georgia, 'Iowan Old Style', serif;
--body-font: -apple-system, BlinkMacSystemFont, 'Segoe UI', sans-serif;
--mono-font: 'SF Mono', Consolas, monospace;
background: var(--paper);
color: var(--ink);
font-family: var(--body-font);
padding: 2.5rem 1.5rem 2rem;
max-width: 1180px;
margin: 0 auto;
box-sizing: border-box;
overflow-x: hidden;
}
.infographic-erbuild[data-theme="dark"] {
--ink: #e8e6e1;
--ink-soft: #9a968c;
--paper: #121212;
--paper-alt: #1e1e26;
--accent: #5b8def;
--accent-soft:#202942;
--line: #2c313d;
--on-accent: #101418;
--good: #4ade80;
--good-soft: #123420;
}
.infographic-erbuild * { box-sizing: border-box; }
.eb-head { text-align: center; margin-bottom: 0.25rem; }
.eb-head h2 {
font-family: var(--display-font);
font-size: 2rem;
margin: 0 0 0.5rem;
letter-spacing: -0.01em;
}
.eb-head p {
color: var(--ink-soft);
font-size: 1rem;
margin: 0 auto;
max-width: 620px;
line-height: 1.5;
}
.eb-controls {
display: flex;
justify-content: center;
gap: 0.6rem;
margin: 1.5rem 0 1.75rem;
flex-wrap: wrap;
}
.eb-btn {
font: inherit;
font-size: 0.88rem;
background: var(--paper-alt);
border: 1px solid var(--line);
border-radius: 8px;
padding: 0.55rem 1.2rem;
cursor: pointer;
color: var(--ink);
transition: border-color 0.15s, color 0.15s, background 0.15s;
}
.eb-btn:hover { border-color: var(--accent); }
.eb-btn.is-primary { border-color: var(--accent); background: var(--accent); color: var(--on-accent); }
.eb-btn.is-primary:hover { opacity: 0.9; }
.eb-section-label {
font-size: 0.78rem;
text-transform: uppercase;
letter-spacing: 0.06em;
color: var(--ink-soft);
text-align: center;
margin: 0 0 0.75rem;
}
.eb-er-scroll {
overflow-x: auto;
-webkit-overflow-scrolling: touch;
background: var(--paper-alt);
border: 1px solid var(--line);
border-radius: 10px;
padding: 1rem 0.5rem;
min-width: 0;
margin-bottom: 2.25rem;
max-width: 620px;
margin-left: auto;
margin-right: auto;
}
.eb-er-scroll svg { width: 100%; height: auto; display: block; }
.eb-shape { cursor: pointer; }
.eb-box rect.eb-box-bg { fill: var(--paper); stroke: var(--accent); stroke-width: 1.5; transition: stroke-width 0.2s, filter 0.2s; }
.eb-box line.eb-divider { stroke: var(--accent); stroke-width: 1.5; }
.eb-box .eb-box-title { fill: var(--ink); font-size: 15px; font-weight: 700; font-family: var(--body-font); }
.eb-box .eb-attr { fill: var(--ink-soft); font-size: 12.5px; font-family: var(--body-font); }
.eb-box .eb-pk { fill: var(--ink); font-weight: 600; text-decoration: underline; }
.eb-shape.is-built rect.eb-box-bg { stroke-width: 2.5; filter: brightness(1.02); }
.eb-shape.is-built .eb-box-title { fill: var(--good); }
.eb-diamond { fill: var(--accent); stroke: var(--accent); transition: filter 0.2s; }
.eb-diamond-text { fill: var(--on-accent); font-size: 13px; font-weight: 600; font-family: var(--body-font); pointer-events: none; }
.eb-shape.is-built .eb-diamond { fill: var(--good); stroke: var(--good); }
.eb-connector { stroke: var(--ink-soft); stroke-width: 2.5; }
.eb-cardinal { fill: var(--ink); font-size: 12.5px; font-weight: 700; font-family: var(--body-font); }
.eb-hint-pill {
fill: var(--paper);
stroke: var(--line);
}
.eb-hint-text {
fill: var(--ink-soft);
font-size: 10.5px;
font-style: italic;
font-family: var(--body-font);
}
/* tables */
.eb-tables-label { display: flex; align-items: center; justify-content: center; gap: 0.5rem; }
.eb-table-grid {
display: grid;
grid-template-columns: repeat(auto-fit, minmax(210px, 1fr));
gap: 1.1rem;
}
.eb-rel-table {
background: var(--paper-alt);
border: 1px solid var(--line);
border-radius: 10px;
overflow: hidden;
min-height: 60px;
transition: border-color 0.2s;
}
.eb-rel-table.has-rows { border-color: var(--accent); }
.eb-rel-table-head {
background: var(--ink-soft);
color: var(--paper);
font-weight: 700;
font-size: 0.9rem;
padding: 0.6rem 0.9rem;
transition: background 0.2s;
}
.eb-rel-table.has-rows .eb-rel-table-head { background: var(--accent); color: var(--on-accent); }
.eb-rel-table-rows { padding: 0.2rem 0; }
.eb-rel-table-empty {
padding: 0.9rem;
font-size: 0.78rem;
color: var(--ink-soft);
font-style: italic;
}
.eb-rel-table-rows .row {
display: flex;
justify-content: space-between;
gap: 0.5rem;
padding: 0.42rem 0.9rem;
font-size: 0.82rem;
border-top: 1px solid var(--line);
opacity: 0;
transform: translateY(-4px);
animation: eb-row-in 0.3s ease forwards;
}
.eb-rel-table-rows .row:first-child { border-top: none; }
@keyframes eb-row-in { to { opacity: 1; transform: translateY(0); } }
.eb-rel-table-rows .row.is-fk-flash { animation: eb-row-in 0.3s ease forwards, eb-flash 1.1s ease 0.3s; }
@keyframes eb-flash {
0% { background: var(--accent-soft); }
100% { background: transparent; }
}
.eb-rel-table-rows .col-name { color: var(--ink); }
.eb-rel-table-rows .tag {
font-size: 0.66rem;
font-weight: 700;
padding: 0.1rem 0.4rem;
border-radius: 4px;
letter-spacing: 0.03em;
flex-shrink: 0;
}
.tag-pk { background: var(--accent-soft); color: var(--accent); }
.tag-fk { background: var(--paper); color: var(--ink-soft); border: 1px solid var(--line); }
@media (prefers-reduced-motion: reduce) {
.infographic-erbuild *, .infographic-erbuild *::before, .infographic-erbuild *::after {
transition-duration: 0.001ms !important;
animation-duration: 0.001ms !important;
}
}
</style>
</head>
<body>
<div class="infographic-erbuild" id="erbuild">
<div class="eb-head">
<h2>ER Model → Relational Model</h2>
<p>Click an entity or relationship in the diagram and watch its table build itself below. Foreign keys flash in from the relationship that created them.</p>
</div>
<div class="eb-controls">
<button class="eb-btn is-primary" id="ebBuildAll" type="button">▶ Build the whole schema</button>
<button class="eb-btn" id="ebReset" type="button">↺ Reset</button>
</div>
<p class="eb-section-label">ER Diagram — click a box or diamond</p>
<div class="eb-er-scroll">
<svg viewBox="0 0 700 620" role="img" aria-label="ER diagram of Student, Course, Professor, Department arranged in a square">
<!-- connectors -->
<line class="eb-connector" x1="240" y1="110" x2="295" y2="110"></line>
<text class="eb-cardinal" x="267" y="98" text-anchor="middle">M</text>
<line class="eb-connector" x1="405" y1="110" x2="460" y2="110"></line>
<text class="eb-cardinal" x="433" y="98" text-anchor="middle">N</text>
<line class="eb-connector" x1="555" y1="190" x2="555" y2="255"></line>
<line class="eb-connector" x1="565" y1="190" x2="565" y2="255"></line>
<text class="eb-cardinal" x="595" y="225" text-anchor="middle">N</text>
<line class="eb-connector" x1="560" y1="365" x2="560" y2="430"></line>
<text class="eb-cardinal" x="595" y="405" text-anchor="middle">1</text>
<line class="eb-connector" x1="295" y1="510" x2="240" y2="510"></line>
<text class="eb-cardinal" x="267" y="498" text-anchor="middle">1</text>
<line class="eb-connector" x1="460" y1="505" x2="405" y2="505"></line>
<line class="eb-connector" x1="460" y1="515" x2="405" y2="515"></line>
<text class="eb-cardinal" x="433" y="498" text-anchor="middle">N</text>
<!-- diamonds -->
<g class="eb-shape" data-id="enrolls" tabindex="0" role="button" aria-label="Enrolls relationship">
<polygon class="eb-diamond" points="350,55 405,110 350,165 295,110"></polygon>
<text class="eb-diamond-text" x="350" y="114" text-anchor="middle">Enrolls</text>
</g>
<g class="eb-shape" data-id="teaches" tabindex="0" role="button" aria-label="Teaches relationship">
<polygon class="eb-diamond" points="560,255 615,310 560,365 505,310"></polygon>
<text class="eb-diamond-text" x="560" y="314" text-anchor="middle">Teaches</text>
</g>
<g class="eb-shape" data-id="belongsto" tabindex="0" role="button" aria-label="Belongs To relationship">
<polygon class="eb-diamond" points="350,455 405,510 350,565 295,510"></polygon>
<text class="eb-diamond-text" x="350" y="507" text-anchor="middle">Belongs_</text>
<text class="eb-diamond-text" x="350" y="520" text-anchor="middle">To</text>
</g>
<!-- entity boxes -->
<g class="eb-shape eb-box" data-id="student" tabindex="0" role="button" aria-label="Student entity">
<rect class="eb-box-bg" x="40" y="30" width="200" height="160" rx="10"></rect>
<text class="eb-box-title" x="140" y="55" text-anchor="middle">Student</text>
<line class="eb-divider" x1="40" y1="68" x2="240" y2="68"></line>
<text class="eb-attr eb-pk" x="58" y="90">RollNo (PK)</text>
<text class="eb-attr" x="58" y="112">Name</text>
<text class="eb-attr" x="58" y="134">Email</text>
</g>
<g class="eb-shape eb-box" data-id="course" tabindex="0" role="button" aria-label="Course entity">
<rect class="eb-box-bg" x="460" y="30" width="200" height="160" rx="10"></rect>
<text class="eb-box-title" x="560" y="55" text-anchor="middle">Course</text>
<line class="eb-divider" x1="460" y1="68" x2="660" y2="68"></line>
<text class="eb-attr eb-pk" x="478" y="90">CourseID (PK)</text>
<text class="eb-attr" x="478" y="112">CourseName</text>
</g>
<g class="eb-shape eb-box" data-id="professor" tabindex="0" role="button" aria-label="Professor entity">
<rect class="eb-box-bg" x="460" y="430" width="200" height="160" rx="10"></rect>
<text class="eb-box-title" x="560" y="455" text-anchor="middle">Professor</text>
<line class="eb-divider" x1="460" y1="468" x2="660" y2="468"></line>
<text class="eb-attr eb-pk" x="478" y="490">ProfID (PK)</text>
<text class="eb-attr" x="478" y="512">Name</text>
<text class="eb-attr" x="478" y="534">Designation</text>
</g>
<g class="eb-shape eb-box" data-id="department" tabindex="0" role="button" aria-label="Department entity">
<rect class="eb-box-bg" x="40" y="430" width="200" height="160" rx="10"></rect>
<text class="eb-box-title" x="140" y="455" text-anchor="middle">Department</text>
<line class="eb-divider" x1="40" y1="468" x2="240" y2="468"></line>
<text class="eb-attr eb-pk" x="58" y="490">DeptID (PK)</text>
<text class="eb-attr" x="58" y="512">DeptName</text>
</g>
</svg>
</div>
<p class="eb-section-label">Relational Tables — built live from your clicks</p>
<div class="eb-table-grid" id="ebTableGrid">
<div class="eb-rel-table" data-table="student">
<div class="eb-rel-table-head">Student</div>
<div class="eb-rel-table-rows"></div>
</div>
<div class="eb-rel-table" data-table="course">
<div class="eb-rel-table-head">Course</div>
<div class="eb-rel-table-rows"></div>
</div>
<div class="eb-rel-table" data-table="professor">
<div class="eb-rel-table-head">Professor</div>
<div class="eb-rel-table-rows"></div>
</div>
<div class="eb-rel-table" data-table="department">
<div class="eb-rel-table-head">Department</div>
<div class="eb-rel-table-rows"></div>
</div>
<div class="eb-rel-table" data-table="enrolls">
<div class="eb-rel-table-head">Enrolls</div>
<div class="eb-rel-table-rows"></div>
</div>
</div>
</div>
<script>
(function () {
const wrapper = document.getElementById('erbuild');
const bodyAttr = document.body.getAttribute('data-darkmode');
let dark;
if (bodyAttr === 'true') {
dark = true;
} else if (bodyAttr === 'false') {
dark = false;
} else {
const match = document.cookie.match(/(?:^|;\s*)darkMode=([^;]+)/);
dark = match ? match[1] === 'true' : false;
}
if (dark) wrapper.setAttribute('data-theme', 'dark');
})();
(function () {
const baseRows = {
student: [
{ name: 'RollNo', tag: 'PK' },
{ name: 'Name', tag: '' },
{ name: 'Email', tag: '' }
],
course: [
{ name: 'CourseID', tag: 'PK' },
{ name: 'CourseName', tag: '' }
],
professor: [
{ name: 'ProfID', tag: 'PK' },
{ name: 'Name', tag: '' },
{ name: 'Designation', tag: '' }
],
department: [
{ name: 'DeptID', tag: 'PK' },
{ name: 'DeptName', tag: '' }
]
};
const built = { student: false, course: false, professor: false, department: false, enrolls: false, 'course-fk': false, 'professor-fk': false };
const shapes = document.querySelectorAll('.eb-shape');
const tables = {};
document.querySelectorAll('.eb-rel-table').forEach(t => { tables[t.dataset.table] = t; });
function markShapeBuilt(id) {
const shape = document.querySelector('.eb-shape[data-id="' + id + '"]');
if (shape) shape.classList.add('is-built');
}
const prefersReducedMotion = window.matchMedia('(prefers-reduced-motion: reduce)').matches;
function scrollToTable(id, delay) {
const table = tables[id];
if (!table) return;
setTimeout(() => {
table.scrollIntoView({ behavior: prefersReducedMotion ? 'auto' : 'smooth', block: 'center', inline: 'nearest' });
}, delay || 0);
}
function addRow(tableId, name, tag, flash) {
const table = tables[tableId];
const rowsEl = table.querySelector('.eb-rel-table-rows');
// avoid duplicate rows
if (rowsEl.querySelector('[data-row="' + name + '"]')) return;
const row = document.createElement('div');
row.className = 'row' + (flash ? ' is-fk-flash' : '');
row.dataset.row = name;
const tagHtml = tag ? '<span class="tag tag-' + tag.toLowerCase() + '">' + tag + '</span>' : '<span></span>';
row.innerHTML = '<span class="col-name">' + name + '</span>' + tagHtml;
rowsEl.appendChild(row);
table.classList.add('has-rows');
}
function buildEntity(id, silent) {
if (!silent) {
scrollToTable(id, 50);
scrollToTable(id, 550);
}
if (built[id]) return;
built[id] = true;
markShapeBuilt(id);
const rows = baseRows[id];
rows.forEach((r, i) => {
setTimeout(() => addRow(id, r.name, r.tag, false), i * 140);
});
}
function buildEnrolls() {
buildEntity('student', true);
buildEntity('course', true);
scrollToTable('enrolls', 50);
scrollToTable('enrolls', 650);
if (built.enrolls) return;
built.enrolls = true;
markShapeBuilt('enrolls');
setTimeout(() => addRow('enrolls', 'RollNo', 'FK', true), 320);
setTimeout(() => addRow('enrolls', 'CourseID', 'FK', true), 480);
}
function buildTeaches() {
buildEntity('professor', true);
buildEntity('course', true);
scrollToTable('course', 50);
scrollToTable('course', 650);
if (built['course-fk']) { markShapeBuilt('teaches'); return; }
built['course-fk'] = true;
markShapeBuilt('teaches');
setTimeout(() => addRow('course', 'ProfID', 'FK', true), 350);
}
function buildBelongsTo() {
buildEntity('professor', true);
buildEntity('department', true);
scrollToTable('professor', 50);
scrollToTable('professor', 650);
if (built['professor-fk']) { markShapeBuilt('belongsto'); return; }
built['professor-fk'] = true;
markShapeBuilt('belongsto');
setTimeout(() => addRow('professor', 'DeptID', 'FK', true), 350);
}
const actions = {
student: () => buildEntity('student'),
course: () => buildEntity('course'),
professor: () => buildEntity('professor'),
department: () => buildEntity('department'),
enrolls: buildEnrolls,
teaches: buildTeaches,
belongsto: buildBelongsTo
};
shapes.forEach(s => {
s.addEventListener('click', () => {
const fn = actions[s.dataset.id];
if (fn) fn();
});
s.addEventListener('keydown', (e) => {
if (e.key === 'Enter' || e.key === ' ') {
e.preventDefault();
s.click();
}
});
});
document.getElementById('ebBuildAll').addEventListener('click', () => {
const sequence = ['student', 'course', 'professor', 'department', 'enrolls', 'teaches', 'belongsto'];
sequence.forEach((id, i) => {
setTimeout(() => actions[id](), i * 650);
});
});
document.getElementById('ebReset').addEventListener('click', () => {
Object.keys(built).forEach(k => built[k] = false);
shapes.forEach(s => s.classList.remove('is-built'));
Object.values(tables).forEach(t => {
t.querySelector('.eb-rel-table-rows').innerHTML = '';
t.classList.remove('has-rows');
});
});
})();
</script>
</body>
</html>.demo {
font-family: system-ui, sans-serif;
padding: 16px;
}
.demo h1 {
color: #2563eb;
margin: 0 0 8px;
}
.demo p {
color: #4b5563;
margin: 0;
}document.querySelector('.demo h1')?.addEventListener('click', () => {
alert('Hello from live preview!');
});✅ Final Overview
Table | Purpose |
|---|---|
| Stores student details |
| Stores course details |
| Stores professor details |
| Stores Department details |
| Links students to courses |
| Links professors to courses |
| Links Professor to Department |