Lesson 5: Conceptual Modelling, Entities and Entity Types¶
From a requirements document to the first boxes on paper
About this lesson
Course: Database Management Systems
Unit 2: Entity Relationship Modelling · 7 Lecture Hours
Lesson duration: 2 lectures
Reading: Elmasri & Navathe, Ch. 3.1, 3.2
Learning Outcomes
- Explain the motivation for conceptual modelling and where it sits in the design life-cycle.
- Define entity, entity type, entity set, and distinguish them rigorously.
- Distinguish strong and weak entity types.
- Extract candidate entity types from a requirements paragraph.
- Avoid the four classic beginner mistakes in choosing entities.
5.1 Why Model Before You Build?¶
You could start a database project by opening MySQL and typing CREATE TABLE. People do. It is a reliable way to produce a database that has to be thrown away in six months.
The database design life-cycle
- Requirements collection and analysis — interview users; write down the data requirements (what must be stored) and the functional requirements (what operations must be supported).
- Conceptual design — produce a conceptual schema, technology-free, using a high-level model. This is the ER model. This is Unit 2.
- Choice of DBMS — commercial factors, technical factors.
- Logical design (data model mapping) — convert the conceptual schema into the data model of the chosen DBMS: ER to relations. This is Unit 5, Lesson 1.
- Physical design — file organisations, indexes. Unit 6.
- Implementation and tuning — DDL, application programs, monitoring.
Mini-world
|
| requirements collection & analysis
v
Data requirements ---------------------> Functional requirements
| |
| CONCEPTUAL DESIGN (ER model) | functional analysis
v v
Conceptual schema <------ independent of DBMS -----> High-level
| transaction spec.
| LOGICAL DESIGN (mapping) <-- DBMS chosen here
v
Logical (relational) schema
|
| PHYSICAL DESIGN
v
Internal schema + application programs
Figure 1.1 — Where conceptual design sits (after Elmasri & Navathe).
The three arguments for conceptual modelling¶
1. It is a communication tool. An ER diagram is understandable by a non-technical user — the college registrar, the hospital administrator — who can then confirm or correct it. CREATE TABLE statements are not. Errors caught at this stage cost minutes; the same error caught after implementation costs weeks.
2. It is implementation-independent. The ER diagram says nothing about MySQL, tables, foreign keys or indexes. The same diagram can be mapped to a relational database, an object database or a document store. The meaning of the enterprise's data outlives any product.
3. It forces the design questions early. Drawing the diagram compels you to ask: Can a student enrol in more than one society? Must every course have a teacher-in-charge? Is a dependant identifiable without its employee? These are business questions, and answering them wrongly produces a database that cannot represent reality.
The ER model
The Entity-Relationship model, introduced by Peter Chen in 1976, describes a mini-world using just three constructs: entities, attributes and relationships. Its diagrammatic form is the ER diagram.
5.2 Entities¶
Entity
An entity is a thing or object in the mini-world that has an independent existence and can be distinguished from other objects.
Two kinds:
- Physical (tangible) existence — a particular student, a particular car, a particular book.
- Conceptual (logical) existence — a particular course, a particular job, a particular society, a university.
Two tests an entity must pass:
- Distinguishability. You must be able to tell one from another. Two students with the same name are still two entities.
- Relevance. It must matter to this mini-world. A student's favourite colour is real, but if the college has no use for it, it is not part of the model.
Entity type, entity set, and the instance/type distinction¶
This is the same intension/extension distinction met in Unit 1, applied to the ER model. It is examined constantly.
The three terms
Entity type — the definition: a name plus a set of attributes, describing a collection of entities that share the same properties. Written STUDENT. This is the schema or intension, and it is what appears in the diagram.
Entity set (entity collection) — the set of all entities of that type in the database at a given moment. This is the extension, and it changes as data is inserted and deleted.
Entity (entity instance) — one individual member of the entity set: the student with roll number X2019.
ENTITY TYPE (schema, in the diagram)
+----------------------------------------+
| STUDENT |
| Rollno, Name, Dateofbirth |
+----------------------------------------+
|
| at 10:00 a.m. the ENTITY SET is
v
+----------------------------------------+
| e1 = ('X2019','Ankit Sharma','2001-03-14') <- an ENTITY
| e2 = ('X2020','Riya Verma','2002-05-11') <- an ENTITY
| e3 = ('Z1188','Sana Iqbal','2001-11-02') <- an ENTITY
+----------------------------------------+
Figure 1.2 — Entity type versus entity set versus entity.
By convention the entity type and its entity set are given the same name, and the context tells you which is meant. In a diagram, STUDENT means the type; in the sentence "there are 400 students", it means the set.
Notation¶
An entity type is drawn as a rectangle containing its name in the singular, in capitals.
+-----------+ +-----------+
| STUDENT | | COURSE |
+-----------+ +-----------+
Strong (regular) entity types: single rectangle.
+===========+
||DEPENDENT|| Weak entity type: double rectangle.
+===========+
Naming conventions used throughout this course
- Entity type names: singular, UPPERCASE —
STUDENT, notStudents. - Attribute names: Mixed case —
Dateofbirth. - Relationship type names: UPPERCASE verb phrases —
TAKES_ADMISSION,ENROLS_IN. - Relationship names should read sensibly left-to-right: STUDENT
TAKES_ADMISSIONCOURSE.
5.3 Strong and Weak Entity Types¶
Strong (regular) entity type
An entity type that has a key attribute of its own — a set of attributes whose values uniquely identify each entity, without reference to any other entity type. It has independent existence. Drawn as a single rectangle.
Weak entity type
An entity type that has no key attribute of its own. Its entities are identified only by being related to a specific entity of another type — the identifying (owner) entity type — through an identifying relationship.
- Drawn as a double rectangle; the identifying relationship as a double diamond.
- It has a partial key (discriminator): an attribute that distinguishes the weak entities belonging to the same owner. Drawn with a dashed underline.
- A weak entity type always has total participation in its identifying relationship (double line), and the relationship is 1:N from owner to weak.
- Existence dependency: delete the owner and the weak entities must go too.
The classic example¶
An employee's dependants. The company records each dependant's name, birth date and relationship. Two different employees may each have a son called Rahul, so Name alone does not identify a dependant. But Name within one employee is unique.
+------------+ +==========+ +===========+
| EMPLOYEE |========||DEPENDENT_||=======||DEPENDENT ||
+------------+ || OF || +===========+
Ssn (key) +==========+ Name (partial key,
dashed underline)
Birthdate, Relationship
Full key of DEPENDENT = Ssn (from owner) + Name (partial key)
The double lines = total participation: every dependent
must belong to some employee.
Figure 1.3 — A weak entity type and its identifying relationship.
College examples¶
| Situation | Strong or weak? | Why |
|---|---|---|
STUDENT with Rollno |
Strong | Rollno is unique college-wide. |
COURSE with SID |
Strong | SID is unique. |
SECTION numbered 1, 2, 3 within a course |
Weak | "Section 1" is meaningless without knowing which course. Owner = COURSE; partial key = SectionNo. |
ROOM numbered 101 within a building |
Weak | Owner = BUILDING; partial key = RoomNo. |
EXAM_ATTEMPT numbered 1, 2 within a student-course pair |
Weak | Partial key = AttemptNo. |
The most common exam trap
Students declare an entity weak simply because "it depends on" another entity. Dependence is not the test. The test is whether it has a key of its own.
ADMISSION connects a student and a course and cannot exist without both — yet if it is modelled as a relationship it is not an entity at all; and if a SectionNo-style discriminator is not needed, it is not weak either. Conversely, an INVOICE belongs to a CUSTOMER and is meaningless without one, but if InvoiceNo is unique company-wide, INVOICE is strong.
5.4 Identifying Entity Types from Requirements¶
This is the practical skill the examination tests. There is a reliable procedure.
Procedure
- Underline every noun and noun phrase in the requirements document.
- Discard nouns that are: synonyms of another noun; outside the mini-world; the name of the system itself; or a mere value rather than a thing.
- For each surviving noun ask: does it have properties of its own that we must store? If yes, it is an entity type. If it is a single fact about something else, it is an attribute.
- Ask: can there be many of these, and do we need to tell them apart? If yes, entity type.
- Ask: does it have a key of its own? No key means a weak entity type — find its owner and its partial key.
- Verbs connecting two nouns become candidate relationship types (Lesson 3).
Worked extraction — the college mini-world¶
"The college keeps records of its students: each has a roll number, a name and a date of birth. The college offers several courses; each course has an identifier, a name, a number of total seats, a duration, a course type which is either full-time or part-time, and a teacher-in-charge. A student may take admission in any number of courses, and the date of admission is recorded. The college also has societies, each with an identifier, a name, a mentor and total seats. Students enrol in societies, and the date of enrolment is recorded."
Step 1–2. Nouns surviving the filter: student, roll number, name, date of birth, course, identifier, name, total seats, duration, course type, teacher-in-charge, admission, date of admission, society, mentor, enrolment, date of enrolment. ("College" is the system itself — the whole mini-world — so it is not an entity type here.)
Step 3–5. Classification:
| Noun | Verdict | Reason |
|---|---|---|
| student | Entity type STUDENT |
Has several properties; many of them; must be distinguished. |
| roll number, name, date of birth | Attributes of STUDENT |
Single facts about a student. |
| course | Entity type COURSE |
Has several properties of its own. |
| identifier, name, total seats, duration, course type, teacher-in-charge | Attributes of COURSE |
Single facts about a course. |
| admission | Relationship type TAKES_ADMISSION |
It is the verb "takes admission" connecting two entity types, not a thing with independent existence. |
| date of admission | Attribute of the relationship | It is a fact about the pairing, not about the student alone or the course alone. |
| society | Entity type SOCIETY |
Has its own properties. |
| mentor | Attribute of SOCIETY |
A single name here. (See the note below.) |
| enrolment | Relationship type ENROLS_IN |
A verb connecting STUDENT and SOCIETY. |
| date of enrolment | Attribute of that relationship | A fact about the pairing. |
Result: three entity types — STUDENT, COURSE, SOCIETY — all strong.
A judgement call worth discussing
Is TeacherInCharge an attribute of COURSE, or should there be a TEACHER entity type?
- If all we ever store is the teacher's name, and we never need the teacher's phone number, department or list of courses, then attribute is correct and simpler.
- If we need the teacher's own properties, or need to ask "which courses does Dr Gupta teach?", then
TEACHERshould be an entity type related toCOURSE.
The requirements decide. In our running college schema, the course outline stores only a name, so we keep it as an attribute — and we note the limitation. The same reasoning applies to Mentor in SOCIETY.
The four classic beginner mistakes¶
Mistake 1 — Making an attribute into an entity type
Creating a NAME entity type, or a DATEOFBIRTH entity type. A single-valued fact about something else is an attribute. The test: does it have properties of its own that we must store?
Mistake 2 — Making an entity type into an attribute
Storing Societies as a single text attribute of STUDENT containing "Debating, Music". This makes it impossible to store the society's mentor or total seats, and impossible to query "who is in the Debating society?" without string surgery. If the thing has properties, it is an entity type.
Mistake 3 — Modelling the output rather than the data
Creating a REPORT or MARKSHEET entity type. A report is a view computed from stored data, not a thing in the mini-world. Model what is stored, not what is printed.
Mistake 4 — Confusing type with instance
Drawing three rectangles labelled BSC_CS, BSC_CHEM, BA_ENG. These are three entities — three rows — of one entity type COURSE. If you find yourself drawing a rectangle per data value, you have confused the type with the instance. A useful signal: if adding new data would require redrawing the diagram, the diagram is wrong.
5.5 Solved Questions¶
Solved Question 1
Q. Define entity, entity type and entity set. Illustrate with a bank database.
A. An entity is a thing or object in the mini-world with independent existence that can be distinguished from other objects — for example, the particular customer holding account number SB-4471.
An entity type is the definition shared by a collection of such entities: a name together with a set of attributes. CUSTOMER (CustID, Name, Address, Phone) is an entity type. It is the intension, and it is what appears in the ER diagram as a rectangle.
An entity set is the collection of all entities of that type present in the database at a particular moment. It is the extension, and it changes with every insert or delete.
| Entity type | Entity set | Entity | |
|---|---|---|---|
| Bank example | CUSTOMER(CustID, Name, Address) |
The 12,430 customer records held today | ('C1001','Meera Nair','Kochi') |
| Nature | Definition / schema | Collection / state | One instance |
| Changes | Rarely | Constantly | — |
By convention both the type and the set carry the same name, and context resolves the ambiguity.
Solved Question 2
Q. What is a weak entity type? State its four characteristics and give two examples with their partial keys.
A. A weak entity type is one that has no key attribute of its own; its entities can be identified only in combination with a related entity of an identifying (owner) entity type, through an identifying relationship.
Four characteristics:
- No key of its own. It has instead a partial key (discriminator) that distinguishes weak entities belonging to the same owner. Its full key is the owner's key together with the partial key.
- Total participation in the identifying relationship, always. Every weak entity must be related to exactly one owner.
- Existence dependency. If the owner entity is deleted, all its weak entities must be deleted too.
- Notation: double rectangle for the entity type, double diamond for the identifying relationship, dashed underline for the partial key, double line for total participation. The identifying relationship is 1:N from owner to weak.
Examples:
| Weak entity type | Owner | Identifying relationship | Partial key | Full key |
|---|---|---|---|---|
DEPENDENT |
EMPLOYEE |
DEPENDENT_OF |
Name |
(Ssn, Name) |
SECTION |
COURSE |
HAS_SECTION |
SectionNo |
(SID, SectionNo) |
In the second example, "Section 1" is meaningless on its own; only "Section 1 of course CS01" identifies a section.
Solved Question 3
Q. Distinguish between a strong and a weak entity type. Is an entity weak merely because it depends on another? Justify.
A.
| Strong (regular) | Weak | |
|---|---|---|
| Key | Has its own key attribute | Has only a partial key |
| Identification | Self-identifying | Needs the owner's key too |
| Existence | Independent | Existence-dependent on the owner |
| Participation in identifying relationship | Not applicable | Always total |
| Notation | Single rectangle | Double rectangle |
No — dependence alone does not make an entity weak. The test is identification, not existence or ownership.
Counter-example: an INVOICE cannot exist without a CUSTOMER, and is deleted when the customer's account is purged. Yet if InvoiceNo is allocated uniquely company-wide, INVOICE identifies itself perfectly well and is therefore a strong entity type that merely participates totally in a relationship with CUSTOMER.
Contrast: a hotel ROOM numbered 101 is weak with respect to HOTEL, because room number 101 exists in every hotel and only (HotelID, RoomNo) identifies a room.
So the correct question is always: "Does this entity type have an attribute, or set of attributes, whose value is unique across the whole entity set?" If yes, strong; if no, weak.
Solved Question 4
Q. From the following requirements, identify the entity types, state which are weak, and give the partial key of each weak type.
"A hospital has several wards, each with a ward number, name and capacity. Each ward contains beds, numbered 1 upward within the ward. Patients are admitted, each with a unique hospital registration number, name and age. Doctors have an employee code, name and specialisation."
A.
| Candidate noun | Verdict | Justification |
|---|---|---|
| hospital | Not an entity type here | It is the mini-world itself; there is only one, with no properties we store. (If the system covered a chain of hospitals, it would become an entity type.) |
| ward | Strong entity type WARD |
Has properties (WardNo, Name, Capacity); WardNo is unique. |
| bed | Weak entity type BED |
Beds are numbered within a ward, so bed 1 exists in every ward. Owner = WARD; identifying relationship CONTAINS; partial key = BedNo; full key = (WardNo, BedNo). |
| patient | Strong entity type PATIENT |
RegNo is stated to be unique. |
| doctor | Strong entity type DOCTOR |
EmpCode is unique. |
| ward number, name, capacity, registration number, age, employee code, specialisation | Attributes | Single facts about the entity types above. |
BED is drawn as a double rectangle, CONTAINS as a double diamond, BedNo with a dashed underline, and the WARD–CONTAINS line is doubled to show total participation.
Solved Question 5
Q. Why perform conceptual design at all, rather than going straight to CREATE TABLE? Give three reasons.
A.
-
Communication with users. An ER diagram is comprehensible to the registrar, the doctor or the accountant who actually knows the business. They can confirm that a student may take several courses, or correct you when you assume a dependant can belong to two employees. SQL DDL cannot be validated by a non-technical user, so errors of meaning survive into the implementation, where they are orders of magnitude more expensive to fix.
-
Implementation independence. The conceptual schema captures the meaning of the enterprise's data without committing to MySQL, to tables, or even to the relational model. The choice of DBMS can be deferred, changed, or made differently in different deployments, while the conceptual schema — which reflects the business, not the technology — survives.
-
It forces the hard questions early, and yields a demonstrably better design. Drawing the diagram compels explicit answers to cardinality and participation questions that
CREATE TABLElets you leave vague. Furthermore, a systematic mapping algorithm (Unit 5) converts a correct ER diagram into a set of relations that is already largely normalised, whereas hand-written tables typically need to be repaired by normalisation afterwards.
A fourth reason worth stating: the diagram is documentation that remains readable years later, when the original designers have left.
Solved Question 6
Q. A student proposes this design: an entity type STUDENT(Rollno, Name, Societies) where Societies holds the text "Debating, Music". Criticise it and give the correct model.
A. The design commits Mistake 2 — turning an entity type into an attribute — and produces four concrete failures.
- It violates atomicity.
"Debating, Music"packs several values into one field. When mapped to a relation this breaks 1NF (Unit 5). - No properties of a society can be stored. The society's mentor, total seats and identifier have nowhere to live, because a society is not represented as a thing.
- Querying becomes string manipulation. "Who is in the Debating society?" requires
LIKE '%Debating%', which cannot use an index and wrongly matches a society called "Debating and Elocution". - No integrity. Nothing prevents a typo —
"Debatting"— from creating a phantom society, because there is no list of valid societies to reference.
Correct model: SOCIETY is a thing with its own properties, so it is an entity type, and "enrols in" is a many-to-many relationship type carrying its own attribute.
+-----------+ /-------------\ +-----------+
| STUDENT |-------< ENROLS_IN >-------| SOCIETY |
+-----------+ M \-------------/ N +-----------+
Rollno (key) | SocID (key)
Name Dateofenrolment Socname
Dateofbirth Mentor
TotalSeats
Dateofenrolment is an attribute of the relationship, because it is a fact about the pairing of a student with a society, not about either one alone. On mapping (Unit 5) this becomes the table ENROLLMENT(Rollno, SocID, Dateofenrolment) with both columns as foreign keys and the pair as the primary key.
5.6 Exercises¶
A. Short answer
- Who introduced the ER model, and in which year?
- Define entity. Give one example each of physical and conceptual existence.
- Distinguish entity type from entity set.
- What is a partial key? How is it drawn?
- State the four characteristics of a weak entity type.
- Why is a weak entity type's participation in its identifying relationship always total?
- Name the six phases of the database design life-cycle.
B. Long answer
- Explain the database design life-cycle and state precisely where conceptual design fits and what it produces.
- Give three arguments for producing an ER diagram before writing DDL.
- Distinguish strong and weak entity types with full notation and two examples each. Explain why existence dependency is not the test for weakness.
- Describe a systematic procedure for extracting entity types from a requirements document, and apply it to a paragraph of your own choosing.
C. Application and analytical
- For each of the following, state whether it should be an entity type, an attribute, or a relationship type, and justify: a. The colour of a car, in a car-showroom database. b. The colour of a car, in a paint-shop database that stocks colours by code and price. c. A student's marks in a subject. d. A book's publisher, where we store the publisher's address. e. A book's number of pages. f. The supervision of one employee by another.
- From this description, list all entity types, mark the weak ones with their owners and partial keys: > "An airline operates flights, each with a unique flight number, origin, destination and departure time. A flight has legs numbered 1, 2, 3 within that flight. Passengers, each with a unique passenger ID, book seats. Aircraft have registration numbers and models."
- Identify what is wrong with each design and correct it:
a. Entity types
MONDAY,TUESDAY,WEDNESDAY. b. An entity typePHONE_NUMBER(Number)related toSTUDENT, where nothing else about a phone number is stored. c. An entity typeFEE_RECEIPT_PRINTOUT. d.EMPLOYEE(Ssn, Name, Child1, Child2, Child3). - A library wants to record, for each copy of a book, a copy number that restarts at 1 for each title. Model
BOOKandCOPY. Which is weak? What is the full key of a copy? What happens to the copies when a title is withdrawn? - Take the requirements of your college's examination system and extract at least five entity types, marking strong and weak, with justification for each decision.
D. Objective — True or False with reason
- Every entity type must have a key attribute.
- A weak entity type can have total participation in a non-identifying relationship as well.
- The entity set changes when a new row is inserted.
- A conceptual schema mentions the chosen DBMS.
DEPENDENTis weak because a dependant cannot exist without an employee.- An entity type is drawn as a diamond.
Answers to Section D
- False. A weak entity type has no key of its own — only a partial key. Strong entity types must have a key.
- True. A weak entity type may participate in other relationships as well, with any constraints; the total participation requirement applies specifically to its identifying relationship.
- True. The entity set is the extension — the set of entities currently present — so an insert changes it. The entity type does not change.
- False. The conceptual schema is deliberately implementation-independent; the DBMS is chosen at the next phase.
- False, as stated. It is weak because
Namealone does not identify a dependant across the whole set — it has no key of its own. Existence dependency is a consequence, not the test. - False. A rectangle. A diamond denotes a relationship type.
Lesson Summary
- Conceptual design produces a technology-free schema that users can validate; it is the second phase of the design life-cycle.
- Entity = distinguishable thing; entity type = its definition (intension, the rectangle); entity set = all current entities (extension).
- Strong entity types have a key of their own; weak entity types have only a partial key and are identified through an owner via an identifying relationship, with total participation and existence dependency.
- The test for weakness is identification, not dependence.
- Extract entity types by underlining nouns and asking whether each has properties of its own; avoid the four classic mistakes.