ER to Relational Mapping

Converting ER to tables (memorize by cardinality): 1:1 — add the primary key of one side as a foreign key on the other (prefer the side with total participation); can merge into one table. 1:N — put the primary key of the '1' side as a foreign key in the table of the 'N' side; no separate relationship table needed. M:N — ALWAYS create a separate relationship table whose primary key is the combination of the primary keys of both participating entities (plus relationship attributes). Shortcut: M:N forces a new table; 1:N never needs one; 1:1 optionally merges. Total participation on the N-side in a 1:N lets you add a NOT NULL constraint on that foreign key.

Weak entity set W with owner E: create a table for W including all of W's attributes PLUS the primary key of E as a foreign key. The primary key of W's table = {primary key of E} UNION {discriminator of W}. The identifying relationship needs no separate table. Multivalued attribute A of entity E: create a SEPARATE table with columns = {primary key of E, A}; its primary key is the full combination (PK of E, A) since one entity has many values. Composite attributes are flattened into their simple components (the composite name itself is dropped). Derived attributes are typically not stored. Memory aid: every multivalued attribute = its own new table.

An ecosystem is not just a collection of plants, animals, and soil — it is a working machine that captures sunlight, moves it through living tissues, recycles dead matter, and topples and rebuilds itself across seasons. UPSC Prelims has consistently asked about the four functions that keep this machine running, and PESS — Productivity, Energy flow, Decomposition, Succession/cycling — is the shortcut that has helped thousands of aspirants score this question.

Definition: An ecosystem is a self-sustaining unit of nature where biotic (living) and abiotic (non-living) components interact through flows of energy and matter.
Definition: A function of an ecosystem is a process — productivity, energy flow, decomposition, or nutrient cycling — that the system performs continually to maintain itself.

The PESS shortcut — the four functions

NCERT lists four key functions of an ecosystem. Remember them with PESS:

1. Productivity
2. Energy flow
3. Synthesis breakdown — i.e., decomposition
4. Spiral / cycle of nutrients

In the standard NCERT wording these are Productivity, Energy flow, Decomposition, and Nutrient cycling. They are not independent; productivity feeds the food chain, the food chain drives energy flow, decomposition releases nutrients, and nutrient cycling fuels new productivity. The four functions are a closed loop.

Productivity — gross, net, and secondary

Productivity is the rate at which biomass is generated per unit area per unit time. There are three flavours.

• Gross Primary Productivity (GPP): the total rate at which producers (autotrophs) fix solar energy through photosynthesis. Units: kg/m²/yr or g/m²/day.
• Net Primary Productivity (NPP): the energy left over after producers have spent some on their own respiration. NPP = GPP − R, where R is plant respiration. NPP is the energy that is actually available to consumers (the herbivores and beyond).
• Secondary Productivity: the rate at which consumers assimilate energy and store it as their own biomass. The same GPP − R logic applies, but at the next trophic level.

Why it matters: GPP and NPP are favourite Prelims targets because they look similar but mean opposite things — GPP is total fixed, NPP is what's left. Confuse the two and you lose marks.

Question: A tropical rainforest fixes 2200 g/m²/yr (GPP) and respires 700 g/m²/yr. Find NPP.
Solution:
Step 1: Write the formula: NPP = GPP − R.
Step 2: NPP = 2200 − 700 = 1500 g/m²/yr.
Conclusion: NPP = 1500 g/m²/yr — this is the energy available to herbivores.

Where the most productive ecosystems live

By NPP per unit area, the most productive ecosystems on Earth are:

• Tropical rainforests
• Estuaries
• Coral reefs
• Swamps and marshes (wetlands)

The least productive include open oceans, deserts, and tundras. Counter-intuitive but exam-favourite fact: oceans cover ~71% of Earth's surface yet contribute only about ~55% of global NPP because their per-area productivity is low (sunlight only penetrates the top few metres, and nutrients are scarce away from upwelling zones). Tropical rainforests cover a tiny percentage of land but contribute disproportionately to NPP.

Energy flow — unidirectional, one-way street

Energy enters an ecosystem as sunlight, is captured by producers, and is then transferred through a food chain: producer → herbivore → carnivore → top carnivore. At every transfer, roughly 90% of the energy is lost — as heat (respiration), in undigested food, and so on — leaving only about 10% to the next trophic level. This is Lindeman's 10% law.

Energy flow is unidirectional — it does not cycle. Once lost as heat, it cannot be recaptured by the ecosystem. This is why food chains are short (rarely more than 4–5 trophic levels) and why top carnivores are so few.

Decomposition — five neat steps

Decomposition is the breakdown of complex dead organic matter (detritus) into simple inorganic substances (CO₂, water, nutrients) by detritivores and decomposers. NCERT lists five steps in fixed order — memorise the sequence.

1. Fragmentation: detritivores like earthworms break detritus into smaller pieces, increasing surface area.
2. Leaching: water-soluble inorganic nutrients dissolve and percolate down into the soil.
3. Catabolism: bacteria and fungi secrete enzymes that degrade detritus into simpler inorganic compounds.
4. Humification: a dark-coloured, amorphous, colloidal substance called humus is formed — highly resistant to microbial action and acts as a nutrient reservoir.
5. Mineralization: humus is further broken down (slowly) to release inorganic nutrients (NH₄⁺, NO₃⁻, PO₄³⁻, etc.) into the soil.

Properties of humus to remember: dark-coloured, amorphous, colloidal, resistant to decomposition. It is the slow-release fertiliser of nature.

What makes decomposition fast or slow?

Decomposition rate depends on three factors — exam loves these.

• Climate: warm and moist → fast; cold or dry → slow.
• Chemistry of detritus: rich in nitrogen and water-soluble substances (sugars) → fast. Rich in lignin and chitin → slow (lignin in wood, chitin in insect exoskeletons and fungal cell walls).
• Aeration: well-aerated soils favour aerobic decomposers; waterlogged anaerobic conditions slow it down.

Why it matters: this is why a fallen mango leaf disappears in a few weeks but a fallen teak log can take years. UPSC has framed MCQs around exactly this contrast.

Nutrient cycling — the closed loop

Unlike energy, nutrients cycle. Carbon, nitrogen, phosphorus, water — each follows its own biogeochemical cycle, moving from soil/atmosphere into living things and back. Cycles are classified as:

• Gaseous cycles — reservoir in atmosphere or hydrosphere: carbon, nitrogen, water.
• Sedimentary cycles — reservoir in Earth's crust: phosphorus, sulphur, calcium.

Real-world example: The Sundarbans mangrove forest is a textbook ecosystem. Mangroves (producers) fix solar energy under tidal conditions. Detritus from fallen leaves is fragmented by crabs and worms, leached by tidal water, decomposed by anaerobic bacteria, and the released nitrogen feeds new growth. Energy flows one-way; nutrients cycle.

Common misconception: Students often assume oceans are the most productive ecosystems because they cover most of Earth's surface. They are not. Per unit area, oceans are quite unproductive — coral reefs and estuaries within oceans are highly productive, but the open sea is closer to a desert. UPSC routinely tests this distinction.

Cover the answer, recall, then check. 12 cards on converting an ER schema to relational tables (GATE).

Q1. How is a strong (regular) entity set mapped?
A1. To one relation containing all its simple attributes; the entity's key becomes the relation's primary key. Composite attributes contribute their leaf components; multivalued and derived attributes are handled separately.

Q2. How is a weak entity set mapped?
A2. To a relation that includes its own attributes plus the owner's primary key as a foreign key. Primary key = owner's PK + the weak entity's partial key.

Q3. How is a multivalued attribute represented?
A3. As a separate relation containing the owning entity's primary key plus the attribute; the primary key of this new relation is the combination of both columns.

Q4. How is a composite attribute stored?
A4. Only its atomic (leaf) components become columns; the composite attribute itself is not stored as a single column.

Q5. How is a derived attribute stored?
A5. It is not stored — it is computed on demand from other stored attributes.

Q6. Mapping a 1:N relationship — where does the foreign key go?
A6. On the "N" (many) side: the relation of the entity on the many side gets the primary key of the one side as a foreign key. No separate relation is required.

Q7. Mapping an M:N relationship?
A7. Always a separate relation containing the primary keys of both entities (as foreign keys) plus any relationship attributes. Its primary key is the combination of the two foreign keys.

Q8. Mapping a 1:1 relationship — best placement of the foreign key?
A8. Add one side's PK as a foreign key to the other; prefer putting it on the side with total participation to avoid NULLs. Alternatively merge both entities into a single relation.

Q9. How is an n-ary (e.g. ternary) relationship mapped?
A9. To a separate relation holding the primary keys of all participating entities plus relationship attributes; the PK is generally the combination of the participating entities' keys (adjusted for cardinality).

Q10. Three schemes for mapping a generalization/specialization (ISA)?
A10. (1) One relation per subclass, each inheriting the superclass attributes; (2) a single relation for the superclass with all subclass attributes plus a type discriminator; (3) one relation for the superclass and one per subclass linked by the shared key.

Q11. For a binary 1:N relationship, why merge rather than create a third table?
A11. Because the foreign key on the many side already records the association exactly once per tuple; a separate relationship table would be redundant and add join cost.

Q12. For a 1:1 relationship where BOTH sides are total, what is the minimal design?
A12. Merge both entity sets and the relationship into a single relation, since every entity of each side pairs with exactly one of the other.

Worked Example

Problem: Map to relational tables (a) a 1:N relationship "Department (1) works_for Employee (N)", and (b) an M:N relationship "Student enrolls_in Course". Indicate where foreign keys go and identify the primary keys.

Solution:
(a) One-to-many mapping. The foreign key is placed on the "many" (N) side, referencing the "one" side's primary key:
Department(dept_id PK, dname)
Employee(emp_id PK, name, dept_id FK → Department.dept_id)
Each employee row carries its single department, correctly capturing 1:N without a separate table.

(b) Many-to-many mapping. A separate relationship (junction) table is required, since neither side can hold multiple foreign keys:
Student(student_id PK, sname)
Course(course_id PK, cname)
Enrolls(student_id FK → Student, course_id FK → Course, PRIMARY KEY(student_id, course_id))
The junction table's primary key is the combination of both foreign keys.

Answer: (a) 1:N → put the foreign key (dept_id) on the Employee (N) side; (b) M:N → create a junction table Enrolls with a composite primary key of both foreign keys.