E-R Diagram

DSA, Database System & Operating System — Data Models, Normalization, and SQL, NEC licence examination syllabus (Nepal Engineering Council).

E-R Diagram

Drawing the model, then converting it to tables — a complete worked design from requirements to SQL.

🌍 Where this lives: the diagram is the conversation. When a developer and a domain expert disagree about whether a patient can have two active prescriptions for the same drug, no amount of code clarifies it — a diagram with a cardinality on it does, in thirty seconds. That is why E-R diagrams survive in an industry that abandoned most of UML: they are the cheapest way to make a disagreement visible before it becomes a schema. Tools like dbdiagram.io, DrawSQL and Mermaid's ER syntax exist purely to keep this step fast. Search "mermaid erDiagram syntax" — you can put a diagram in a README and have it render on GitHub.

The complete notation reference

SHAPES ┌────┐ rectangle strong entity type ╔════╗ double rectangle weak entity type ◇ diamond relationship type ◈ double diamond identifying relationship ○ ellipse attribute ((○)) double ellipse multivalued attribute ⌜ ○ ⌝ dashed ellipse derived attribute ○───○ ellipse with children composite attribute UNDERLINING ___ solid underline key attribute --- dashed underline partial key (weak entity) LINES ─── single line PARTIAL participation ═══ double line TOTAL participation LABELS ON LINES 1, N, M cardinality ratio (min, max) min-max notation, more precise TERMINOLOGY MAP — the same idea in three vocabularies: E-R MODEL RELATIONAL OBJECT/ORM ───────────────────────────────────────────────────── entity type table/relation class entity instance row/tuple object attribute column field entity set table contents collection key attribute primary key id relationship FK or junction association domain data type type

Worked design — a college database, requirements to schema

REQUIREMENTS as a domain expert would state them: R1 A department has a unique code, a name, and a budget. R2 A student has a roll number, a name, a date of birth, one or more phone numbers, and a home address made of street, city and district. We often need their age. R3 Every student belongs to exactly one department. A department may have many students, or none yet. R4 A professor has an id, a name, and belongs to exactly one department. R5 A course has a code, a title, and credits, and is offered by exactly one department. R6 A student may enrol in many courses; a course may have many students. For each enrolment we record the semester and the grade. A student may repeat a course in a later semester. R7 Each course section is taught by exactly one professor. A section is numbered within its course — section 1 of ACtE07 and section 1 of AExE01 are different sections. R8 A student may have one advisor (a professor), or none. R9 A professor may head at most one department, and a department has at most one head. READING THE REQUIREMENTS INTO THE MODEL: ENTITIES Department, Student, Professor, Course WEAK ENTITY Section (numbered within a course — R7) MULTIVALUED Student.phone (R2) COMPOSITE Student.address(street,city,district) (R2) DERIVED Student.age from dob (R2) RELATIONSHIPS BELONGS_TO Student–Department 1:N, total on Student (R3) WORKS_IN Professor–Department 1:N, total on Prof (R4) OFFERS Department–Course 1:N, total on Course (R5) ENROLS Student–Course M:N + semester, grade (R6) HAS_SECTION Course–Section identifying, 1:N (R7) TEACHES Professor–Section 1:N, total on Section (R7) ADVISES Professor–Student 1:N, partial both (R8) HEADS Professor–Department 1:1, partial both (R9) Every design decision traces to a numbered requirement. That traceability is what makes a design defensible — and it is what an examiner is checking.

The conversion, rule by rule

college_schema.sql

-- RULE 1: strong entity -> table with its key as PK
CREATE TABLE department (
    code   CHAR(6)       PRIMARY KEY,
    name   VARCHAR(60)   NOT NULL UNIQUE,
    budget NUMERIC(12,2) NOT NULL CHECK (budget >= 0)
);

CREATE TABLE professor (
    id        INTEGER     PRIMARY KEY,
    name      VARCHAR(60) NOT NULL,
    -- R4: total participation in WORKS_IN -> NOT NULL
    dept_code CHAR(6)     NOT NULL REFERENCES department(code)
                          ON DELETE RESTRICT
);

-- RULE 2: composite address flattened into components
-- RULE 4: age is DERIVED -> NOT stored
-- R3: total in BELONGS_TO -> dept_code NOT NULL
-- R8: partial in ADVISES  -> advisor_id NULLABLE
CREATE TABLE student (
    roll            INTEGER     PRIMARY KEY,
    name            VARCHAR(60) NOT NULL,
    dob             DATE        NOT NULL,
    addr_street     VARCHAR(80),
    addr_city       VARCHAR(40),
    addr_district   VARCHAR(40),
    dept_code       CHAR(6)     NOT NULL REFERENCES department(code)
                                ON DELETE RESTRICT,
    advisor_id      INTEGER     REFERENCES professor(id)
                                ON DELETE SET NULL
);

-- RULE 3: multivalued phone -> its own table
CREATE TABLE student_phone (
    roll  INTEGER     NOT NULL REFERENCES student(roll)
                      ON DELETE CASCADE,
    phone VARCHAR(15) NOT NULL,
    PRIMARY KEY (roll, phone)
);

-- RULE 6: OFFERS is 1:N -> FK on the N side (course)
CREATE TABLE course (
    code      CHAR(8)     PRIMARY KEY,
    title     VARCHAR(80) NOT NULL,
    credits   SMALLINT    NOT NULL CHECK (credits BETWEEN 1 AND 6),
    dept_code CHAR(6)     NOT NULL REFERENCES department(code)
                          ON DELETE RESTRICT
);

-- RULE 8: WEAK ENTITY -> PK = (owner PK, partial key)
-- R7: sec_no is numbered WITHIN a course
CREATE TABLE section (
    course_code CHAR(8)  NOT NULL REFERENCES course(code)
                         ON DELETE CASCADE,
    sec_no      SMALLINT NOT NULL,           -- partial key
    room        VARCHAR(20),
    -- R7: total participation in TEACHES -> NOT NULL
    prof_id     INTEGER  NOT NULL REFERENCES professor(id)
                         ON DELETE RESTRICT,
    PRIMARY KEY (course_code, sec_no)
);

-- RULE 7: M:N -> junction table carrying the relationship
-- attributes. R6 allows a retake, so semester is in the PK.
CREATE TABLE enrolment (
    roll        INTEGER  NOT NULL REFERENCES student(roll)
                         ON DELETE CASCADE,
    course_code CHAR(8)  NOT NULL REFERENCES course(code)
                         ON DELETE RESTRICT,
    semester    SMALLINT NOT NULL CHECK (semester BETWEEN 1 AND 8),
    grade       CHAR(2),
    PRIMARY KEY (roll, course_code, semester)
);

-- RULE 5: 1:1 HEADS -> FK + UNIQUE. The UNIQUE is what
-- makes it 1:1 rather than 1:N. Partial on both sides, so
-- the column is nullable.
ALTER TABLE department
    ADD COLUMN head_id INTEGER UNIQUE REFERENCES professor(id)
        ON DELETE SET NULL;

-- RULE 4: the derived attribute, computed in a view
CREATE VIEW v_student AS
    SELECT roll, name, dob,
           EXTRACT(YEAR FROM AGE(CURRENT_DATE, dob)) AS age,
           addr_city, dept_code
    FROM   student;

A CHECKLIST for the conversion, matching what earns marks: ✔ every strong entity became one table ✔ every multivalued attribute became its own table ✔ no derived attribute is stored ✔ 1:N relationships put the FK on the N side — NO extra table ✔ the M:N relationship became a junction table holding semester and grade ✔ the weak entity's PK is (course_code, sec_no) ✔ the 1:1 relationship has FK **and** UNIQUE ✔ total participation → NOT NULL; partial → nullable ✔ referential actions chosen from the semantics, not by habit THE FOUR ERRORS THAT LOSE MARKS: 1. creating a separate table for a 1:N relationship (only M:N needs one) 2. omitting UNIQUE on a 1:1 relationship, which silently permits 1:N 3. storing a derived attribute 4. leaving a totally-participating FK nullable, which permits data the model forbids Note the referential actions differ deliberately: student → department RESTRICT (students remain) student → advisor SET NULL (student remains, advisor gone) student_phone → student CASCADE (a phone has no independent meaning) section → course CASCADE (weak entity) section → professor RESTRICT (the section still needs a teacher) Each is a semantic decision, and each would be wrong if copied from a neighbour.

The advisor_id and head_id columns are both foreign keys into professor, but only head_id carries UNIQUE. That single difference is what encodes "a professor advises many students but heads at most one department". If you can explain why one has it and the other does not, you understand cardinality conversion.

Verifying the design against the requirements

verify.sql

-- The real test of a schema: does it ACCEPT what the
-- requirements allow and REJECT what they forbid?

INSERT INTO department (code, name, budget) VALUES
    ('ACtE07', 'Computer Engineering', 5000000),
    ('AExE01', 'Electrical Engineering', 4200000);
INSERT INTO professor VALUES
    (1, 'Prof. Sharma', 'ACtE07'),
    (2, 'Prof. Karki',  'AExE01');
INSERT INTO student (roll, name, dob, dept_code, advisor_id) VALUES
    (101, 'Ram Bahadur', '2004-03-15', 'ACtE07', 1),
    (102, 'Sita Devi',   '2004-07-20', 'ACtE07', NULL);
-- R8 satisfied: 102 has NO advisor, and that is legal
INSERT INTO student_phone VALUES
    (101, '9841000001'), (101, '9851000002');
-- R2 satisfied: two phones for one student
INSERT INTO course VALUES
    ('ACtE0703', 'Database Systems', 3, 'ACtE07'),
    ('AExE0101', 'Circuit Theory',   4, 'AExE01');
INSERT INTO section VALUES
    ('ACtE0703', 1, 'Room 301', 1),
    ('ACtE0703', 2, 'Room 302', 1),
    ('AExE0101', 1, 'Room 105', 2);
-- R7 satisfied: section 1 exists for BOTH courses

INSERT INTO enrolment VALUES
    (101, 'ACtE0703', 5, 'B'),
    (101, 'ACtE0703', 6, 'A'),      -- R6: a RETAKE
    (102, 'ACtE0703', 5, 'A');

-- ===== what the schema must REJECT =====

INSERT INTO enrolment VALUES (101, 'ACtE0703', 5, 'C');
-- ERROR: duplicate key — same student, course AND semester

INSERT INTO student (roll, name, dob, dept_code)
    VALUES (103, 'No Dept', '2004-01-01', NULL);
-- ERROR: null value in column "dept_code" violates
--        not-null constraint       (R3: total participation)

UPDATE department SET head_id = 1 WHERE code = 'ACtE07';
UPDATE department SET head_id = 1 WHERE code = 'AExE01';
-- ERROR: duplicate key value violates unique constraint
--        "department_head_id_key"
--        (R9: a professor heads AT MOST ONE department)

DELETE FROM department WHERE code = 'ACtE07';
-- ERROR: update or delete on table "department" violates
--        RESTRICT setting of foreign key constraint
--        "professor_dept_code_fkey" on table "professor"
-- DETAIL: Key (code)=(ACtE07) is referenced from table
--        "professor".
--
-- Note the message names the FIRST constraint checked, not
-- all of them. Students and courses also reference this
-- department; fixing only the professor reference reveals
-- the next error. Constraint failures are reported one at a
-- time.

-- ===== and a query proving the model works =====
SELECT s.name,
       c.title,
       e.semester,
       e.grade,
       p.name AS advisor
FROM   student s
JOIN   enrolment e ON e.roll = s.roll
JOIN   course c    ON c.code = e.course_code
LEFT   JOIN professor p ON p.id = s.advisor_id
ORDER  BY s.roll, e.semester;
--     name     |      title       | semester | grade |   advisor
--  ------------+------------------+----------+-------+--------------
--  Ram Bahadur | Database Systems |        5 | B     | Prof. Sharma
--  Ram Bahadur | Database Systems |        6 | A     | Prof. Sharma
--  Sita Devi   | Database Systems |        5 | A     |
--
-- The LEFT JOIN is required: Sita has no advisor, and an
-- inner join would silently DROP her row. That is partial
-- participation showing up in query design.

🌍 Go further: Chen notation is what exams use, but industry mostly uses crow's foot notation, where cardinality is drawn as line endings — a three-pronged "crow's foot" for many, a bar for one, a circle for zero. It packs the same information into less space and reads faster once learned, which is why dbdiagram.io, Lucidchart and Mermaid all default to it. Learn both: Chen for the exam, crow's foot for the job. Search "crow's foot notation cheat sheet" and compare it against the diagram above.

💡 Exam angle: expect a scenario of five to ten sentences and a request to draw the E-R diagram and convert it to tables. Work systematically: list entities, then attributes (marking multivalued/derived/composite), then relationships with both cardinality and participation. In the conversion, the marks are in the details — FK on the N side for 1:N, junction table for M:N, separate table for a multivalued attribute, (owner PK, partial key) for a weak entity, and FK plus UNIQUE for 1:1. Justify each NOT NULL by citing the participation constraint.

Syllabus points

Drawing ER diagrams (numerical/design)

Create a free account to tick topics off, take notes as you read, watch the video lessons and get a day-by-day study plan built around your exam date.