Entity–Relationship Modeling

This session moves from the file environment and the three-schema architecture into the heart of database design: entities, attributes, keys, relationships, and weak entity sets. The running theme is the full journey from a user's spoken requirement to a diagram anyone can verify, and from that diagram to a schema that can actually store data.

2.1 The Database Design Process

2.1.1 From User Requirements to a Schema

Before this session we were expected to have a basic understanding of the file environment, its advantages and disadvantages, the three-schema architecture, and what queries look like. This session builds the next step: ER models, cardinality, and how design steps turn a requirement into something storable.

The user requirement is where everything starts. The user can be anyone who owns a problem: a dean, a director, a head of department, or a vice chancellor of a university who walks in with requirements. Your job is to understand the requirements, encapsulate them in one form, and then convert them into a schema — a table with columns, where each value goes into a particular attribute. ER modeling is the set of principles, practices, and concepts used to convert a user requirement into diagrammatic form. Those diagrams are then used to build the schema.

The process stays the same whether you end up storing in a relational system or a non-relational one. Focus on the syntax in this course: once you understand the syntax of one modeling language — entity relationship — you can understand any other modeling process and any other schema.

The textbook design process confirms this journey and adds the standard milestones around it:

1. Requirements collection and analysis — interview the users, document what data they need and which operations (queries and updates) they will run. The result is a written set of requirements.
2. Conceptual design — build a high-level conceptual schema (an ER diagram) from those requirements, without worrying about storage details. This is the step this session teaches.
3. Logical design (data model mapping) — convert the conceptual schema into the data model of the chosen DBMS (for this course, the relational model: tables with columns and keys).
4. Physical design — choose storage structures, file organizations, and indexes on the logical schema.
5. Schema refinement — inspect the resulting relations for problems (redundancy, anomalies) and restructure them.
6. Application and security design — decide which user groups can access which parts of the database.

Steps 2 and 3 are the heart of this session and the next: the ER diagram (conceptual) becomes a relational schema (logical), and only then do SQL queries make sense.

2.1.2 The "Jay" Story

The motivating story starts with Jay, a person in Minnesota, USA, born in the 1960s. America had come out of the second world war and was in a capitalistic rush; the great American dream was showing everywhere. Veterans who had come back from the war wanted to enjoy their lives with their families after suffering so much. They were very skillful people who had given a lot.

Jay's requirement, when it finally comes, is small and personal. Jay wants to store everything for a person — male or female — and store the details of that person: name, gender, address, earning profile, and psychological profiling (DISC analysis, ESTJ, or similar).

The requirement can be fashionable, like Jay's, or traditional, like a banking system or a university system. You may be satisfying a supply chain customer, an automobile customer, or somebody in the healthcare sector. Most requirements you will meet in your career will be new to you, so try to understand the process rather than memorizing cases.

2.1.3 Why an Intermediate Diagram?

Instead of handing your team 40 pages of user requirement, the ER diagram puts everything on one page. The user can confirm that all requirements and constraints are satisfied, and your team can build from the same picture. It also works as an audit record: if the user later raises a ticket claiming a requirement was missed, the ER diagram is the agreed statement — "you specified this requirement, we created this ER diagram, and you confirmed it satisfies all the constraints." Anything beyond what was agreed costs more dollars, and you can encapsulate that cost and ask whether to proceed.

Jumping directly from the user requirement to a relational schema is risky. You may miss constraints, miss requirements, and miss scalability aspects; the design comes out flawed, and you cannot justify that you captured everything. The intermediate diagram is what makes the capture verifiable.

Constraints must be captured in the diagram along with the attributes. In America, for instance, a law might allow one person to marry two women, or one woman to marry two men — a constraint on the person entity. A course must have an instructor, and the instructor must exist in the database. A student must have a mentor, and a mentor may mentor six mentees on campus. Every constraint like these has to be represented.

Real-world: in industry the ER diagram is a shared contract between the business user and the engineering team, and it doubles as the audit trail when requirement disputes arise. Banks, hospitals, and supply-chain systems run on this same discipline: the conceptual picture is agreed before a single table is created, because reworking a schema after data has been loaded is far more expensive than fixing a diagram.

2.1.4 Student Questions

Exam note: focus on the syntax of ER modeling. Once you understand the syntax of one modeling language, you can understand any other modeling process and any other schema; the process is what carries across companies and projects.

2.2 Entities and Entity Sets

2.2.1 What Is an Entity?

An entity is the unit of the world we want to model. Remember the good old days in school when we discussed nouns — proper nouns, common nouns, abstract nouns. Entity relationship works the same way: in a university, an entity can be an instructor, a student, a course, or a section — anything that signifies a particular value as a single entity. We denote the relationships between these entities.

The ER model is a language. Language does not only mean words: gestures, eye movement, tonality, and pitch all convey information. The diagrammatic format is the language we use to communicate a requirement to the user and to the team. Just as English or Hindi has a grammar for sentence construction, the ER model has its own grammar — entities, attributes, and relationships are its parts of speech.

The noun-to-entity mapping is a practical working rule, not a coincidence: when a designer reads a requirement narrative, the nouns tend to become entity types, the verbs tend to become relationship types, and the describing nouns (adjectives, in spirit) become attributes. "A student takes a course" gives the entities student and course, the relationship takes, and any descriptive phrase such as "and receives a grade" supplies an attribute of that relationship.

2.2.2 Entity Sets

An entity set is a set of entities of the same kind: a set of different instructors, a set of different students. In a mathematics-style diagram with dots, each dot denotes one entity in the entity set. A relationship links one entity from one set to an entity in another set — one student from the student set is related to one instructor from the instructor set through the advisor relationship.

Visual intuition: picture the entity sets as two clusters of dots, each cluster labelled with its type — one cluster labelled Student, one labelled Instructor. The relationship advisor draws one line from a dot in the student cluster to a dot in the instructor cluster. Reading the picture is immediate: every line connects exactly one student dot to exactly one instructor dot, and the pattern of lines (many lines leaving one instructor dot, none or one line leaving a student dot) is exactly the constraint information that cardinality notation will encode later.

2.2.3 Deciding Entities and Attributes

The user says, "I want to store everything for a person." It is you who decides that person becomes an entity, and that the person's name, gender, address, earning profile, and psychological profiling are attributes of that entity. You decide what forms an entity and what its attributes are — the requirement gives you the content, but the modeling choices are yours.

Real-world: the entity-versus-attribute decision is exactly where requirement disputes are born in industry. Healthcare systems argue about whether medication is an attribute of a patient record or an entity with its own structure (dosage, prescriber, interactions); e-commerce systems argue about whether address is an attribute or an entity. The designer who can justify the choice with the structure-and-query test settles these arguments quickly.

2.3 Attributes and Their Types

2.3.1 What Is an Attribute?

An attribute is something that describes an entity — a property of that particular entity. Take the instructor in this session as an example. The institution stores everything about the instructor: name, email ID, street address, zip code, and phone number. But a single application, such as the eLearn portal or a Teams-style app, might use only the name, an email ID, and one or two other details.

This connects to the three-tier architecture from the earlier session. At the physical level, data is stored in some way. At the logical level, everything that needs storing is stored. At the view level, the user and the application use only a portion of the data. So when a requirement comes to you, plan to store everything with a proper design; later, an application may use a portion, or a derived portion.

Real-world: your college portal shows your name and email but not your street address, because the view layer exposes only part of the stored data — the same pattern as every production system, from banks showing balances but not card numbers to hospitals showing allergies but not full histories.

2.3.2 Simple vs Composite Attributes

A simple attribute cannot be broken down further — ID number is a simple attribute. A composite attribute is made of smaller attributes. Name is composite: it includes first name, middle name, and last name. Address is composite: it includes street, city, state, and zip code. Street itself is composite again, including street number and street name.

            Address
             /    |    |    \
          Street  City  State  Zip
           /   \
       Number  Street_name

The rule of thumb from the textbooks: subdivide an attribute only when someone actually refers to the components. The Name attribute is subdivided because the university sends letters beginning "Dear Ms. Krishnan" (needs the last name) and builds rosters alphabetically (needs the first name). The Vehicle_id of a car, by contrast, is never subdivided — it stays atomic.

2.3.3 Single-Valued vs Multi-Valued Attributes

A single-valued attribute holds exactly one value per entity: a person has one ID number, one name, one address. A multi-valued attribute holds several: a person is allowed more than one phone number. In the diagram, multi-valued attributes are written inside curly braces; everything else is single-valued.

Every entity in a set has the same set of attributes, but not the same number of values for a multi-valued attribute: one person has zero degrees, another has one, a third has three. That is the whole point of the multi-valued type — the count varies per entity without changing the design.

2.3.4 Stored vs Derived Attributes

A stored attribute is written to the database, like date of birth. A derived attribute is not stored; it is computed at run time from stored data, like age derived from date of birth. Age is used heavily in views and reports, so the design keeps it available — but only date of birth is stored, and age is derived whenever it is needed.

2.3.5 Null Entries and Candidate Keys

While storing records you will meet null entries. A value can be not applicable — a person from an earlier era may have no phone number — or it can be unknown because we do not have it yet. Null is acceptable in ordinary attributes.

But a candidate key, and the primary key once chosen, can never be null: the value cannot be missing, cannot be unknown, and cannot be not applicable. In the diagram, a dash under the attribute marks it as the candidate key.

2.3.6 Student Questions

Real-world: identity systems live or die by these choices. Aadhaar-style national ID numbers are simple, single-valued, non-null candidate keys; names are composite (first, middle, last), and mobile numbers are multi-valued. Financial regulators require "know your customer" systems to record the same person's multiple addresses and aliases — exactly the multi-valued attributes discussed above.

2.4 Keys: Super Key, Candidate Key, Primary Key, Foreign Key

2.4.1 Key

When records are stored in a table, a key is a set of one or more attributes that uniquely distinguishes different records. Take a student table with the attributes ID number, name, email ID, phone number, and address. If ID number and name together uniquely distinguish every record, they form a key.

2.4.2 Super Key and Candidate Key

A super key is any set of attributes that uniquely distinguishes two different records. It may carry redundant attributes. A candidate key is a minimal set: remove one attribute and it no longer uniquely distinguishes records. Any table can have more than one candidate key.

In the student table, $\{IDnumber,name\}$ is a key. A subset of it — just ID number — is also unique, so ID number is a candidate key. If email ID is also unique, as a student in the session pointed out, email ID is a second candidate key. If names happen to be unique too, name is a third candidate key.

2.4.3 Primary Key

The database administrator chooses any one of the candidate keys as the primary key of the table. For the student table, that choice is ID number. The distinction between super key, candidate key, and primary key: a super key may be non-minimal; a candidate key is always minimal; the primary key is the candidate key the administrator picks.

2.4.4 Foreign Key

Consider two tables: the student table, holding ID number, name, email ID, phone number, and address, and a course table listing the students registered in one particular course, say Database Systems. Every student listed in the course table must exist in the student table. The course table's ID number is a foreign key: it references the student table's ID number.

The rule to remember: an entry can exist in the course table only if the student exists in the student table. If the student drops out of the degree, that entry should not remain. An attribute in one table references the primary key attribute of another table — that is the basic concept of a foreign key.

2.4.5 Worked Example: The Intern Story

A junior designer starts with the student table and says, "name, ID number, and email ID together are unique — that is a key." A senior designer corrects the label: call it a super key, because we do not yet know whether the set is minimal.

A second intern proposes another set: name, ID number, and phone number — also unique. A third intern proposes name, ID number, and address — also unique. The senior designer praises all three sets, then asks: is anything inside them individually unique? The interns realize that ID number is unique by itself, email ID is unique by itself, and in this hypothetical university, even name is unique.

So all three single attributes are minimal unique sets: three separate candidate keys. The team decides to use ID number as the primary key. The lesson: the candidates were super keys (three attributes each); the minimal ones, individually, were candidate keys; and the primary key is the one chosen from among them.

2.4.6 Student Questions

The four key terms side by side:

When to use which: identify candidate keys first (the minimal unique sets), choose one as the primary key, and treat any other set that merely identifies records as a super key. Foreign keys appear wherever one table must point at another.

Real-world: every table in a production database — student records, payroll, bank accounts, airline bookings — is keyed exactly this way. National ID or passport numbers are candidate keys of persons; account numbers are primary keys of accounts; and every transaction table (withdrawals, bookings, orders) carries a foreign key pointing back to its account or customer, which is why a customer cannot simply be deleted while transactions remain.

2.5 Relationships, Cardinality Ratios, and Participation

2.5.1 Relationships Between Entities

A relationship captures how entities from different entity sets connect. The advisor example: there is an entity set of instructors and an entity set of students. A student can have an advisor, and an instructor can be the advisor. We store that relationship because the user said it must exist.

The university sets the rules. One university says a student has at most one advisor. Another allows many advisors: some advise on careers, some on health, some on well-being, some are career coaches, some are sports coaches. The requirements can even contradict one another — a student may have at most one advisor, or a student may have many — and whichever rule the user states must be captured exactly.

2.5.2 Cardinality Ratios

The cardinality ratio of a relationship says how many of one side pair with how many of the other. The spoken description in the session: "one is to one, one is to many, many is to many."

$$1:11:NM:N$$

In $1:1$, a student has at most one advisor and an instructor advises at most one student. In $1:N$, a student has at most one advisor while an instructor can advise many students. In $M:N$, a student can have many advisors and an instructor can advise many students. Teams may use different words for the same idea, but the ratios themselves are fixed vocabulary.

2.5.3 Arrow Notation and Reading a Diagram

The notation uses arrows on the relationship lines. A pointed arrow means one; a line without a pointed arrow means many.

Reading the $1:N$ diagram: the instructor side carries one, the student side carries many. So an instructor can have multiple students to advise, while a student has at most one instructor as an advisor. The $1:1$ diagram reads as at most one on each side. The $M:N$ diagram reads as at most many on each side.

Visual intuition: picture a table of (student, advisor) pairs from the real world — rows like (S1001, I203), (S1002, I203), (S1003, I107). In the $1:N$ reading, an instructor ID may repeat down many rows (I203 advises S1001 and S1002), but a student ID may appear in only one row. The arrow sits on the side whose ID can never repeat — that is the "at most one" side. One sentence takeaway: the arrow marks the column that cannot contain duplicates.

2.5.4 Total vs Partial Participation

Participation is separate from cardinality. Total participation is drawn with a double line and means that every entity in that entity set must take part in the relationship. Partial participation means at most — some entities may not participate at all.

Take the rule "every student must have an advisor." Every student in the student entity set must participate, so the student side gets a double line — total participation. The instructor side stays partial: some instructors advise many students, and some advise no student.

Combined reading for $M:N$ with total participation on the student side: a student can have at most $M$ advisors, but every student must have at least one; an instructor can advise at most many students, with no minimum. Cardinality ratios talk about at most; total participation talks about at least.

Exam note: expect to read cardinality and participation straight off a diagram — "one to one, one to many, or many to many" plus "at least" on the double-line side. Practice the at-most versus at-least reading: it is the most likely question area and the most confusing point in this session.

2.5.5 Student Questions

2.5.6 Writing Exact Limits in a Diagram

When the user gives exact numbers, cardinality notation cannot express "20." The bounds notation can. The session described it as follows: if one side writes 20, it means an instructor can advise zero or 20 students; "one dot dot one" means a student has at least one and at most one advisor.

$$\mathrm{0..20}\mathrm{1..1}$$

A requirement like "a student can have at most one instructor, but an instructor can advise at most 20 students" is captured through this notation. Anything non-binary — zero, one, many, or a specific count like five or six — can be written this way.

Real-world: team communication runs on this vocabulary. "The cardinality ratio is N:M with total participation on the student side" is a complete specification — every team member draws the same picture from that one sentence. Production systems use exact bounds constantly: a bank caps daily transfer links at $\mathrm{0..5}$, an airline allows a passenger $\mathrm{0..4}$ checked bags, and a university allows a student $\mathrm{0..2}$ majors — all drawn with the same $min..max$ notation.

2.6 Weak Entity Sets and Strong Entity Sets

2.6.1 Worked Example: Dependents

The organization stores employees and their dependents. A dependent can be a parent residing at home, a spouse, or a child. Dependents are not part of the organization, but the organization needs to store their details: the name of the dependent, the date of birth of the dependent, and the age of the dependent.

Now imagine two dependents both named Dipanshu, born on the same date, both male, because both sets of parents decided to name them Dipanshu. The difference is that their parents are different people — they belong to different employees. Storing name, date of birth, and age for both produces two records that all attributes together cannot tell apart. No subset works either. This signals a weak entity.

2.6.2 Worked Example: Section

The same problem appears for a section. A section has a section ID, a year, and a semester. Everything for a particular semester is noted down in a section. Yet even all three attributes taken together cannot uniquely distinguish every section.

A section cannot stand alone; it needs its course. The section becomes identifiable only through the course's key.

2.6.3 Weak and Strong Entities

A strong entity is an entity whose attributes — or some subset of the attributes — can uniquely distinguish different entities in the entity set, or different records in the table. A weak entity is one where all the attributes in the entity set taken together cannot distinguish two different entities.

The weak entity is drawn as a double rectangle. It needs an owner entity: a strong entity whose key, combined with the weak entity's own attributes, uniquely identifies every record when it is stored in the relation.

2.6.4 The Owner Entity and the Identifying Relationship

The relationship between a weak entity and its owner is called the identifying relationship and is drawn as a double rhombus. The weak entity's own distinguishing attributes form its partial key, drawn with a dashed underline.

The candidate key of the weak entity relation is the owner's key plus the partial key:

$$K_{\text{weak}}=K_{\text{owner}}\cup P$$

where $K_{\text{weak}}$ is the candidate key of the weak entity relation, $K_{\text{owner}}$ is the owner entity's candidate key, and $P$ is the partial key, the dashed-underlined attributes. For the section example, the course's key plus section ID, year, and semester together make the candidate key of the section relation.

2.6.5 Weak Entities in Other Relationships

Not every relationship of a weak entity is identifying. In the university diagram, Takes is a normal single rhombus: a student takes a section and the student gets a grade. Nothing special about it — it is a normal relationship, exactly like the instructor–student relationship.

Once a section is uniquely identified together with its owner course, the section can play any role in any other relationship as a normal entity. Only the relationship between the weak entity and its owner carries the double rhombus; every other relationship of that entity is drawn normally.

An entity can also have a relationship with itself: a course can be a prerequisite of another course.

2.6.6 Student Questions

Real-world: weak entities are everywhere in industry. Order items live only under their parent order; hotel room bookings exist only under their hotel and date; insurance policies list dependents who exist only under the policyholder. In every case the same pattern holds: the child's identity borrows the parent's key, the child is drawn as a double rectangle, and the borrowing relationship is the double rhombus.

2.7 ER Diagram Notation and Reading Conventions

2.7.1 Textbook Nomenclatures

A question from the session: is drawing the key attribute with an underscore, and enclosing multiple attributes in brackets, a standard way to describe ER diagrams? The answer: it is one of the ways. Textbooks use different nomenclatures. One convention comes from Silberschatz and Korth; the Elmasri Navathe textbook uses a similar one with small differences. Both are fine. It is like writing the capital letter A — somebody writes it one way, somebody writes it another, and both are the letter A. Once you understand the ER diagram, you can make sense of any notation, including the one used in this session.

Real-world: the two conventions named in the session are the two most widely used in industry — Silberschatz and Korth's arrow notation and Elmasri and Navathe's 1/M/N-edge notation — and UML class diagrams (2.7.5) are the everyday notation of Java and .NET design teams. A designer who can move between them communicates with every team on a project.

2.7.2 The Mind Map Tool

The diagrams in this session were organized with a mind map tool — software that arranges ideas properly and systematically so that people remember them. It helped the presenter during doctoral research as well. It is not being sponsored or recommended for everyone; it is simply one way to keep a design organized.

The point of such a tool is not the tool itself: it is that a requirement has many scattered clauses, and a structure — any structure — that keeps them arranged while the diagram grows prevents forgotten constraints. Notebook sketches, whiteboards, and formal design tools all serve the same purpose.

2.7.3 Reading a Complete Diagram

The complete university diagram ties everything together. The entities: instructor, student, course, section. The relationships: teaches (instructor to course), takes (student to section, with a grade), prerequisite (course to course — an entity relating to itself), and the identifying relationship from section to course, drawn as a double rhombus. Some relationships in the diagram are weak; most are not.

Reading an entire diagram — every entity, every cardinality, every participation — is exactly the skill this session builds toward.

Real-world: teams talk in these terms. "The cardinality ratio is N:M with total participation on the student side" — and the diagram is clear without further words, even across teams. This sentence-level shorthand is how design reviews, handovers, and change requests are discussed in industry every day.

2.7.4 Practice Exercises

The homework after this session has two parts. First, take a written requirement and create the ER diagram for it. Second, take a given ER diagram and read it across. If you can do both, you have really mastered this stage — a winning shot that sets you free for a long time. The next session adds the conversion of the ER diagram into a relational schema, and then SQL queries.

2.7.5 UML Notation for EER Diagrams

The session mentioned that textbooks use different nomenclatures — Silberschatz-Korth on one side, Elmasri-Navathe on the other. The UML notation is the other major convention a designer will meet in industry, because UML (the Unified Modeling Language) is the standard notation of software engineering, and the extended ER (EER) constructs of specialization and generalization have a direct, widely used UML rendering. The mapping is mechanical, and this supplement records it in full.

In UML, every entity type is drawn as a class — a rectangle divided into three compartments: the class name at the top, the attributes in the middle, and the operations in the bottom compartment. An attribute can be marked with a multiplicity in square brackets — [0..*] for multi-valued attributes — and underlined attributes denote keys. A relationship between entity types is drawn as an association: a plain line between the two classes, labelled with the relationship name, and a multiplicity label at each end. Multiplicity is exactly the (min, max) notation of the ER world, written in the form $m..n$: 0..1 means zero or one (the "at most one" side), 1..1 or 1 means exactly one, 0..* or * means many (zero or more), and 1..* means at least one (the total-participation side). So the advisor relationship, read in the earlier sessions as "a student has at most one advisor, an instructor advises many students," is drawn in UML as a line labelled advises with 0..1 at the student class and * at the instructor class.

The construct the syllabus calls out is the UML rendering of specialization and generalization — the EER concepts of subclasses. UML draws an is-a hierarchy with a hollow triangle (a generalization arrow) pointing from the subclass to the superclass: a superclass class at the top, a line descending to the triangle, and lines fanning out to each subclass class. EMPLOYEE with subclasses SECRETARY and ENGINEER becomes:

          [ EMPLOYEE ]
                    |
                 ( / \ )      <- hollow triangle pointing up
                 /     \
        [ SECRETARY ]  [ ENGINEER ]

The triangle is the UML way of saying what the EER diagram says with a subset-symbol circle: every instance of the subclass is an instance of the superclass (is-a), and the subclass inherits all attributes and relationships of the superclass. A subclass may add its own attributes — ENGINEER adds engineer_rank — and may participate in relationships that the superclass does not have. Multi-level hierarchies and multiple inheritance are drawn by stacking triangles. The EER distinction between specialization (partitioning one superclass into subclasses) and generalization (factoring common attributes of several classes into a superclass) has the same UML picture — only the reading direction changes. UML also supports an {abstract} marker on the superclass for the case where instances exist only in subclasses — the analog of a total specialization that is not covering.

The concrete conversion recipe for a designer moving between the two notations: ER entity → UML class (name + attribute compartment, keys underlined, multivalued attributes in [..]); ER relationship → UML association (line with multiplicity m..n at each end, relationship attributes written in an association class box attached to the line by a dashed line); ER weak entity → UML class with a {partial key} marker and an association to the owner with a filled diamond (the UML composition symbol, the analog of the double rhombus); EER subclass/superclass → UML generalization triangle; EER category (union) → UML generalization with the hollow triangle pointing at the category class.

Real-world: UML class diagrams are the everyday working notation of Java and .NET design teams, so a database designer who can read an EER diagram and redraw it in UML communicates directly with the application team — the ER diagram is the contract with the business user, and the UML class diagram is the contract with the programmers.

Real-world: modern design tools — ERwin, Rational Rose, draw.io, the modeling components of cloud database services — each ship their own symbol set, and every one of them is a dialect of the same ER grammar taught here. Tool fluency is notation fluency, and notation fluency is meaning fluency.

2.8 Managing Change: Agile Development and Database Scalability

2.8.1 The ER Model Is Storage-Agnostic

Real-world: MongoDB and Cassandra are common NoSQL targets for teams migrating away from SQL databases; the conceptual ER work still drives those target designs. Migration projects routinely keep the original ER diagram as the master document and derive both the legacy and the new schema from it — the diagram is the single source of truth across engines.

2.8.2 Agile Development and Changing Requirements

2.8.3 Scaling a 1:1 Design to N:M

Real-world: a work-learning program unit — an example, not an official case — starts with one-to-one mentorship because there are hardly 20 students and 50 teachers. Every student has at least one and at most one mentor; the numbers allow it. Then the organization grows to 60,000 students with different requirements. The same mentorship becomes N:M: any student can have any instructor, and any instructor can have any number of students, with no total participation. The change is aligned and stored properly in the schema — the same discipline you will apply when converting an ER diagram to a relational schema.

2.8.4 Where the Database Lives

Any application queries the database to satisfy the client. The whole system may be hosted on a central server in the organization, on a cloud platform such as AWS or Azure, or even as a blockchain application running on Ethereum, Solana, or Polkadot. The building blocks are the same; you apply your judgment to the requirement at hand.

Real-world: this is the modern deployment landscape — central servers, cloud platforms, and distributed ledger platforms all sit behind the same conceptual design work. A startup's ordering system on AWS, a bank's core ledger on central servers, and a supply-chain tracker on a blockchain can share one ER design because the diagram describes the miniworld, not the hardware.

Exam Guidance Summary

• Focus on the syntax of ER modeling. Once you understand the syntax of one modeling language, you can understand any other modeling process and any other schema; the process is what carries across companies and projects.
• After this session you should be able to do two things: take a written requirement and create the ER diagram for it, and take a given ER diagram and read it across — every entity, relationship, cardinality, and participation.
• Converting the ER diagram into a relational schema is the next session's topic, and the application of foreign keys comes with it. Hold the key concepts until then.
• Practice the cardinality and total participation reading — at most versus at least — because this is a very likely question area and the most confusing point in this session. Drill the combined reading: arrows state the maximum, the double line states the minimum.
• The university exercise from the textbook — a dean, director, HOD, or vice chancellor bringing requirements — is the running example for the whole design process. Practice both directions: requirement to diagram, and diagram back to requirement.
• The key vocabulary to state precisely: super key versus candidate key versus primary key (minimality), foreign key (references another table's primary key), weak entity (double rectangle), partial key (dashed underline), identifying relationship (double rhombus), and the bounds notation $min..max$ for exact limits.
• The official session materials are part of the examination; the extra practice videos are not official.
• SQL queries and lab sheets come in the next session.

Key Industry Applications

• Real-world: ER diagrams serve as the shared contract between business users and engineering teams, and as an audit record when a requirement dispute or a ticket arises. The diagram is the agreed statement of what was requested, designed, and confirmed — anything beyond it is costed separately.
• Real-world: teams migrating away from legacy SQL databases target NoSQL systems such as MongoDB and Cassandra; the conceptual ER design still drives the target schema. The diagram is the master document from which both the legacy and the new schema are derived.
• Real-world: applications expose only part of their data through APIs — application programming interfaces — and large systems split into microservices; the database sits behind both, and the view/API layer enforces what each consumer may see.
• Real-world: production systems run on central organization servers, cloud platforms (AWS, Azure), or blockchain platforms (Ethereum, Solana, Polkadot) — the same conceptual design serves all three.
• Real-world: a college portal (an eLearn-style application) uses only a view of your stored profile — name and email, not your full street address. This is the view level of the three-tier architecture in action.
• Real-world: organizations store psychological profiling data (DISC, ESTJ-style profiles) alongside personal records, as in the Jay example — the designer must capture such fields as attributes without judging the requirement.
• Real-world: mind map tools help design teams organize requirements systematically; any structured tool that keeps every clause visible while the diagram grows serves the same purpose.
• Real-world: the two widely used ER notation conventions come from the Silberschatz and Korth textbook and the Elmasri Navathe textbook, and UML class diagrams add the third convention designers meet in industry; fluency across them is standard practice.