ER Modeling · Database Management System

ER Modeling

CSC-265 · Semester IV · Database Management System

beginner35 minAvailable

SQLin-browser engine · runs locallyer

Ctrl Enter to run6 lines

Write SQL, then press Run. Result tables, affected-row counts and error messages appear here.

Lab brief

Objective

Convert a written scenario into an ER diagram with entities, relationships, cardinality and participation constraints, then translate it to relational tables.

Theory & concepts

An ER model describes the world as entity types (rectangles), attributes (ellipses) and relationship types (diamonds). Cardinality ratios (1:1, 1:N, M:N) state how many of one side relate to how many of the other; participation says whether participation is required (double line) or optional.

Rendering diagram…

ER entities with cardinality and participation

Scenario: a DEPARTMENT has many EMPLOYEEs; every employee
must belong to a department. Employees enroll in many
COURSEs; a course can be taken by many employees.

Diagram:
  DEPARTMENT ||----(WORKS_FOR)----o| EMPLOYEE   (1:N)
  EMPLOYEE   o----(ENROLLS)--------o COURSE      (M:N)

Tables:
  DEPARTMENT(Dno, Dname)
  EMPLOYEE(Eno, Name, Dno)
  ENROLLS(Eno, Cno, Grade)

Prerequisites

Lesson: Entity types, entity sets and attributes
Lesson: Constraints on relationships
Lesson: Reducing an ER diagram to relational tables

Instructions

1Read the scenario and identify the entity types and their key attributes.
2Add the relationships and label each with its cardinality ratio.
3Mark participation: double lines where participation is total.
4Add relationship attributes such as Grade where they belong.
5Generate the relational tables from the diagram with the mapping preview.
6Answer the closing questions about design choices.

Tasks

Draw the entities
Place the entity types, their attributes and the key for each.
Connect relationships
Add every relationship from the scenario with correct cardinality.
Enforce constraints
Mark total vs partial participation with double and single lines.
Map to tables
Generate CREATE TABLE statements and check the M:N junction table.

Self-check

All entities, attributes and keys from the scenario are represented.
Cardinality and participation constraints match the scenario.
Relationship attributes are placed correctly.
The generated tables faithfully implement the diagram, including the M:N junction.

Environment

ER canvasentity and attribute palettecardinality editortable mapping preview