Interview users and record domain vocabulary.
PART 1 • CONCEPTUAL DATABASE DESIGN
Turn Real Requirements into a Correct ER Model
Identify the important objects, describe their properties and express business rules with relationships, cardinalities and participation constraints.
BY THE END, YOU CAN
- Extract entities and relationships from requirements.
- Classify attributes and choose keys.
- Specify cardinality and participation.
- Model weak entities correctly.
- Use specialization and generalization.
- Defend every modelling decision.
An ER Diagram Is a Decision Model, Not Decoration
Every box, attribute and line should correspond to a fact or rule in the problem statement.
UNIVERSITY REQUIREMENT
“The university stores each student with a unique ID, name and phone numbers. Every course has a unique code, title and credits. A student can enrol in many courses, and a course can contain many students. For each enrolment, store the semester and grade.”
- Nouns suggest entities: Student and Course.
- Descriptive facts suggest attributes: name, title and credits.
- Verbs suggest relationships: Student enrols in Course.
- Quantity words suggest cardinality: many students and many courses.
- Facts about the relationship belong to it: semester and grade describe Enrolment.
Choose objects that need independent identity.
Describe each entity and select identifiers.
Name relationships using meaningful verbs.
State cardinality, participation and uniqueness.
Test the diagram with real examples and exceptions.
Entities Have Identity; Attributes Describe Them
One distinguishable object
A particular student such as Student 101.
The common definition
STUDENT with student_id, name and date_of_birth.
Current collection of entities
All students stored at a particular time.
name
Simple
Cannot be meaningfully divided for the application.
addressstreet · city · pin
Composite
Contains meaningful component attributes.
phone
Multivalued
One entity can have several values.
age
Derived
Calculated from another value such as date_of_birth.
student_id
Key
Uniquely identifies each entity instance.
date_of_birth
Stored
Retained directly and may be used to derive other facts.
A Relationship Represents a Meaningful Association
Name relationships with verbs so the diagram can be read as a sentence.
STUDENTENROLSCOURSE
EMPLOYEE supervises EMPLOYEE
One entity type participates more than once in different roles.
STUDENT enrols in COURSE
Two entity types participate. This is the most common degree.
SUPPLIER supplies PART to PROJECT
Three entity types participate in one inseparable fact.
RELATIONSHIP ATTRIBUTE
Grade belongs to ENROLMENT
A grade cannot be assigned to Student alone or Course alone. It describes one student’s enrolment in one course, so it belongs to the relationship (or associative entity).
Cardinality Answers “How Many?”; Participation Answers “Is It Required?”
Maximum participation
1:1
One on each side
1:NOne relates to many
M:NMany relate to many
Minimum participation
Partial / optional
Minimum is zero; an entity may exist without the relationship.
Total / mandatoryMinimum is one; every entity must participate.
STUDENT (0, N)A student may initially have no enrolment and later join many courses.
ENROLMENT (1, 1)Every enrolment must belong to exactly one student and one course.
DEPARTMENT (1, N)Under this rule, every department must offer at least one course.
Interactive ER Relationship Builder
Choose a template or modify the controls. The diagram and plain-language rule update together.
ENROLS
Entity Relationship Total participation uses a double line
A Weak Entity Cannot Be Identified by Its Own Attributes Alone
EMPLOYEE
employee_id, name
DEPENDENT
dependent_name, birth_date
A dependent cannot exist in this database without an owning employee.
dependent_name distinguishes dependents only within one employee.
(employee_id, dependent_name) uniquely identifies a dependent.
Every weak entity participates in its identifying relationship.
Enhanced ER Concepts Handle Richer Domains
Specialization
Start with EMPLOYEE and form FACULTY and STAFF subtypes based on distinguishing properties.
EMPLOYEEISAFACULTYSTAFF
Generalization
Recognize common properties of CAR and TRUCK and create the VEHICLE supertype.
CARTRUCKISAVEHICLE
Aggregation
Treat a relationship and its entities as a higher-level object when it must participate in another relationship.
EMPLOYEEWORKS_ONPROJECTMONITORED BY MANAGER
Disjoint vs overlapping
Disjoint: one entity belongs to at most one subtype. Overlapping: it may belong to several.
Total vs partial completeness
Total: every supertype entity must enter a subtype. Partial: some may remain only in the supertype.
Six Formative Concept Checks
Use the explanation to correct the underlying modelling idea, not only the selected option.
1. Which requirement most strongly suggests an entity?
2. Where should “grade” be placed in Student enrols in Course?
3. A student can join many clubs and each club has many students. Cardinality?
4. Total participation means:
5. What identifies a weak entity instance?
6. Creating Faculty and Staff from Employee is:
Answered correctly: 0 of 6
University, Design and Interview Practice
- Define entity and entity set.
- What is a composite attribute?
- Define cardinality ratio.
- What is total participation?
- What is a partial key?
- Explain attribute types with notation and examples.
- Compare cardinality and participation.
- Explain weak entities with an identifying relationship.
- Differentiate specialization and generalization.
- Design ER models for library and hospital systems.
- When should a relationship become an associative entity?
- Can a weak entity have attributes?
- How do you validate an ER model with users?
- How would you model multiple phone numbers?
Show the ER design checklist used before submission
- Every entity has a meaningful singular name and identifier.
- Every attribute describes the correct entity or relationship.
- Multivalued and composite values are shown deliberately.
- Every relationship has a readable verb name.
- Both sides have justified minimum and maximum participation.
- Weak entities include owner, identifying relationship and partial key.
- Subtype constraints are marked when specialization is used.
- The model is tested with normal cases, empty cases and exceptions.
LEVEL 3 SUMMARY
You Can Now Defend an ER Model
- Requirements supply candidate entities, attributes, relationships and constraints.
- Keys identify entities; attribute types express structure and value behavior.
- Cardinality gives the maximum while participation gives the minimum.
- Weak entities combine an owner key with a partial key.
- Specialization, generalization and aggregation model richer semantics.
- A correct diagram is validated against business rules and counterexamples.
COURSE CHECKPOINT
Mark this level when you can convert a paragraph into a justified ER model and explain every constraint.
Saved in this browser only.