ER Diagrams and Schema Design — Visualizing and Structuring Your Database

The school records students, subjects and marks. One student can take several subjects, and each subject can have many students.

An entity-relationship model describes the things a system records and the relationships between them. A relational schema turns that model into tables, keys and constraints.

Identify entities, give each a stable identity and express relationship cardinality: one-to-one, one-to-many or many-to-many. A student-subject junction table can store one mark per pair when that matches the school’s rules. If exams repeat across terms, add the relevant exam or term identity to the design.

Related concepts: Normalize Data and Foreign Keys.

An ER Diagram is a visual representation of entities (real-world objects like Student or Teacher), their attributes (like name, marks), and the relationships between them. It helps you understand how your data fits together — before you start coding tables.

• Entity: Object or concept (e.g., Student)
• Attribute: Property of the entity (e.g., name, roll_no)
• Primary Key: Uniquely identifies each entity (e.g., roll_no)
• Relationship: Association between entities (e.g., "enrolls in" between Student and Course)

Let’s design a simple school system with the following:

• Student (roll_no, name, class)
• Subject (subject_code, name)
• Marks (roll_no, subject_code, marks)

ER Diagram (conceptually):

SQL · portable concepts; preserved examples identify their engine

1. Student Table

CREATE TABLE students (
  roll_no INT PRIMARY KEY,
  name VARCHAR(50),
  class VARCHAR(10)
);

2. Subject Table

CREATE TABLE subjects (
  subject_code VARCHAR(10) PRIMARY KEY,
  name VARCHAR(50)
);

3. Marks Table (Many-to-Many relationship)

CREATE TABLE marks (
  roll_no INT,
  subject_code VARCHAR(10),
  marks INT,
  PRIMARY KEY (roll_no, subject_code),
  FOREIGN KEY (roll_no) REFERENCES students(roll_no),
  FOREIGN KEY (subject_code) REFERENCES subjects(subject_code)
);

SQL · portable concepts; preserved examples identify their engine

• All tables are in 1NF – atomic values only
• 2NF – no partial dependencies in composite keys
• 3NF – no transitive dependencies

• Storing multiple subjects in one field (violates 1NF)
• Repeating student name in the marks table (redundancy)
• Missing foreign key constraints (data inconsistency)

You can expand this design by adding:

• Teachers table (with subject_code as FK)
• Attendance table (roll_no, date, status)
• Exams table (term_name, date_range)

• dbdiagram.io
• DrawSQL
• MySQL Workbench (visual design + schema generation)

A drawing does not enforce a relationship; the schema’s keys and constraints do. Do not assume one mark per subject when the real school records several assessments. Names are display data and may be neither stable nor unique.

Check the number of subject combinations the current three-student, two-subject model could hold before recording actual results.

This practice uses a separate, fresh fixture named practice_students. It does not continue the changing table state of the earlier examples.

SQL · create only these practice tables in a disposable database:

CREATE TABLE practice_students (roll_no INTEGER PRIMARY KEY, name VARCHAR(50), class VARCHAR(10), marks INTEGER, city VARCHAR(30));
INSERT INTO practice_students VALUES
(1, 'Aarav', '10A', 85, 'Pune'),
(2, 'Diya', '9B', 70, 'Chennai'),
(3, 'Sneha', '10A', 85, 'Pune');
CREATE TABLE practice_subjects (subject VARCHAR(30));
INSERT INTO practice_subjects VALUES ('Maths'),('Science');

This practice uses a separate, fresh fixture named practice_students. It does not continue the changing table state of the earlier examples.

SQL · create only these practice tables in a disposable database:

CREATE TABLE practice_students (roll_no INTEGER PRIMARY KEY, name VARCHAR(50), class VARCHAR(10), marks INTEGER, city VARCHAR(30));
INSERT INTO practice_students VALUES
(1, 'Aarav', '10A', 85, 'Pune'),
(2, 'Diya', '9B', 70, 'Chennai'),
(3, 'Sneha', '10A', 85, 'Pune');
CREATE TABLE practice_subjects (subject VARCHAR(30));
INSERT INTO practice_subjects VALUES ('Maths'),('Science');

SQL · this practice variation runs with SQLite 3; the preserved examples retain their stated dialect

SELECT COUNT(*) AS possible_enrollments FROM practice_students CROSS JOIN practice_subjects;

The six possible pairs describe the many-to-many space. Actual enrollment rows would record which of those pairs exists, with a suitable uniqueness rule.

An entity-relationship model describes the things a system records and the relationships between them. A relational schema turns that model into tables, keys and constraints.

The six possible pairs describe the many-to-many space. Actual enrollment rows would record which of those pairs exists, with a suitable uniqueness rule.

Continue your understanding with Normalize Data and Foreign Keys.

Source for the engine-specific behavior: MySQL 8.4 reference documentation.