ER Model and Relational Model

An entity is a real-world object; an entity set is a collection of similar entities. Attribute types to memorize (mnemonic SCAM-D): Simple vs Composite (Name -> First, Last), Single-valued vs Multivalued (phone numbers, shown by double ellipse), Stored vs Derived (Age derived from DOB, shown by dashed ellipse), Key attribute (underlined). A weak entity has no key of its own and depends on an owner (identifying) entity; it is drawn with a double rectangle and connected via a double-diamond identifying relationship. Its discriminator (partial key) is underlined with a dashed line. NULL handling: attributes can be NULL when not applicable or unknown. Remember: composite = breakable into parts; multivalued = many values for one entity; derived = computable, never stored.

ER diagram symbols (memorize for quick recall): Rectangle = entity set; Double rectangle = weak entity; Ellipse = attribute; Double ellipse = multivalued attribute; Dashed ellipse = derived attribute; Underlined attribute = primary key; Diamond = relationship; Double diamond = identifying relationship (for weak entity); Line connecting = participation. Cardinality is written on lines as 1, N, M. Participation: single line = partial, double line = total. Min-max (look-across vs look-here) notation gives precise (min,max) constraints. Shortcut: a weak entity ALWAYS has total participation in its identifying relationship, so that connecting edge is always a double line.

Specialization is top-down (split a superclass into subclasses, e.g., Employee -> Engineer, Secretary). Generalization is bottom-up (combine entities into a superclass). Both use the ISA triangle and support attribute inheritance. Constraints: Disjoint (d) vs Overlapping (o) tells whether an entity can belong to multiple subclasses; Total vs Partial tells whether every superclass entity must belong to some subclass. Aggregation treats a relationship (with its participating entities) as a higher-level entity so it can participate in another relationship (modeling a relationship between an entity and a relationship). Memory aid: Specialization = Subclass creation (both start with S); Aggregation = treating a relationship as one Abstract block.

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.

:::compare

:::compare

:::keypoints

• Four functions: Productivity, Energy flow, Synthesis breakdown (decomposition), Spiral of nutrients — PESS.
• NPP = GPP − R; NPP is available to consumers.
• Most productive: tropical rainforests, estuaries, coral reefs, swamps.
• Oceans cover ~71% of Earth but have low NPP per unit area.
• Decomposition order: Fragmentation → Leaching → Catabolism → Humification → Mineralization.
• Humus is dark, amorphous, colloidal, resistant to decomposition.
• Decomposition is fast in warm-moist, nitrogen-rich detritus; slow in lignin/chitin-rich, cold, or waterlogged conditions.
• Energy flow is unidirectional; nutrients cycle.
  :::

:::memory
PESS for the four functions. For decomposition, "Friends Like Catching Hums Mostly" — Fragmentation, Leaching, Catabolism, Humification, Mineralization. For productivity, "NPP = GPP minus Plant breathing".
:::

:::recap

• An ecosystem performs four interlocking functions: productivity, energy flow, decomposition, nutrient cycling.
• NPP is the GPP that survives plant respiration — the food budget of all consumers.
• Decomposition follows five fixed steps and produces humus, a resistant nutrient reservoir.
• Energy flows one way; nutrients cycle.
  :::

A relation is a set of tuples; its schema is R(A1,...,An) with degree = number of attributes and cardinality = number of tuples. Key terms: domain (allowed values), tuple (row), attribute (column). A relation is a SET, so duplicate tuples are not allowed and tuple ordering is immaterial. Integrity constraints: (1) Domain constraint — values from the attribute's domain. (2) Key constraint — candidate key values are unique. (3) Entity integrity — no primary key attribute may be NULL. (4) Referential integrity — a foreign key value must either match some primary key value in the referenced relation or be entirely NULL. Memory aid: PK can't be NULL (entity integrity); FK can be NULL (referential integrity allows it).

Superkey: any attribute set that uniquely identifies a tuple. Candidate key: a MINIMAL superkey (no proper subset is a superkey). Primary key: one chosen candidate key (cannot be NULL). Alternate keys: candidate keys not chosen as primary. Foreign key: attribute(s) referencing another relation's primary key. Prime attribute: an attribute that is part of SOME candidate key; non-prime otherwise. Counting superkeys: if a relation with n attributes has a single candidate key of one attribute, the number of superkeys = 2^(n-1) (the key must be present, the other n-1 attributes are optional). Shortcut: every candidate key is a superkey, but not every superkey is a candidate key (minimality fails).

Counting superkeys looks like a 30-second brain teaser, but it's also one of the most common GATE CSE multiple-mark questions in DBMS. The trick is to translate the words "candidate key" and "superkey" into a clean set-counting problem and then apply inclusion–exclusion wherever keys overlap. This lesson builds the recipe from the ground up.

Definition: Superkey — any set of attributes that uniquely identifies every tuple in a relation. Equivalently, a superkey is a candidate key together with any (possibly empty) subset of the remaining attributes.

Definition: Candidate key — a minimal superkey: removing any attribute from it would destroy uniqueness.

Counting from First Principles

Suppose a relation has n total attributes and one candidate key with k attributes. Every superkey must contain all k attributes of the candidate key (otherwise it wouldn't be a superkey). The remaining n – k attributes are independent — each one can be either present or absent. Therefore the number of superkeys is exactly 2^(n – k).

This is the master formula behind every counting problem in this topic. The candidate-key attributes are forced; the non-key attributes are free.

Why it matters: GATE often asks "How many superkeys exist?" or "How many superkeys contain attribute X?" — both are answered by counting free attributes.

Example 1 — One Candidate Key, AB in R(A, B, C, D)

Given R(A, B, C, D) with the candidate key AB:

• A and B are forced into every superkey.
• C and D are each free (include or not).
• Number of superkeys = 2^2 = 4, namely {AB, ABC, ABD, ABCD}.

Listing them keeps you honest, and the formula gives the count instantly.

Example 2 — Two Candidate Keys, Inclusion–Exclusion in Action

Now take R(A, B, C, D, E) with two candidate keys: A (single attribute) and BC (two attributes). Now any superkey must contain at least one candidate key — so we need:

|superkeys containing A| ∪ |superkeys containing BC|

A naive sum double-counts the superkeys that contain both A and BC. The correct formula is the principle of inclusion–exclusion:

|A ∪ BC| = |A| + |BC| − |A ∩ BC|

Step-by-step:

• Superkeys containing A: A is forced; B, C, D, E are free → 2^4 = 16.
• Superkeys containing BC: B and C forced; A, D, E free → 2^3 = 8.
• Superkeys containing both A and BC: A, B, C all forced; D and E free → 2^2 = 4.
• Total = 16 + 8 − 4 = 20.

Notice how the overlapping set is subtracted exactly once. If we forgot this step we'd report 24 — a very common mistake.

Why Inclusion–Exclusion?

The intuition: every "containing A" superkey and every "containing BC" superkey is a valid superkey, but a relation like ABCDE is counted twice — once because it contains A, once because it contains BC. Subtracting the intersection corrects exactly that double-count. For three candidate keys you'd extend to:

|X ∪ Y ∪ Z| = |X| + |Y| + |Z| − |X ∩ Y| − |Y ∩ Z| − |Z ∩ X| + |X ∩ Y ∩ Z|

Common misconception: Some students try to "fix" the issue by averaging or by counting only the minimal candidate keys. Both are wrong. The only safe approach is inclusion–exclusion across the candidate keys.

A Quick Recipe You Can Memorise

1. Identify all candidate keys.
2. For each candidate key, count superkeys that contain it = 2^(n − size of that key).
3. For each pair of candidate keys, count superkeys containing the union of their attributes = 2^(n − |union|).
4. Apply inclusion–exclusion.

Worked example with the recipe — R(A, B, C, D) with candidate keys A and B:

• |A| = 2^3 = 8.
• |B| = 2^3 = 8.
• |A ∪ B|-forced attributes = {A, B}, so |A ∩ B as superkey-sets| = 2^2 = 4.
• Total = 8 + 8 − 4 = 12.

Real-world example: In a real database for, say, a college's STUDENT(roll_no, aadhaar, name, branch, year) table, both roll_no and aadhaar are candidate keys. Asking "how many distinct sets of columns would still uniquely identify a student?" is exactly the superkey-counting question, and the count tells the DBA how many alternative unique indexes are theoretically possible.

Disjoint vs Overlapping Candidate Keys

If two candidate keys are completely disjoint (share no attributes), their union still gets the same |X ∩ Y| treatment — the size of the union is just |X| + |Y|. If they overlap (share some attributes), the union is smaller, so the intersection count gets bigger and the subtraction matters more. Either way, the formula is identical; only the numbers change.

Question: R(A, B, C, D, E) has candidate keys AB and CD. How many superkeys exist?

Solution:
Step 1: |AB| = forced A, B; free C, D, E → 2^3 = 8.
Step 2: |CD| = forced C, D; free A, B, E → 2^3 = 8.
Step 3: |AB ∪ CD| has forced A, B, C, D; free E → 2^1 = 2.
Step 4: Total = 8 + 8 − 2 = 14.
Conclusion: 14 superkeys in R.

:::compare

:::keypoints

• A superkey contains a candidate key; non-key attributes are free.
• For a single candidate key of size k in an n-attribute relation, #superkeys = 2^(n − k).
• With multiple candidate keys, use inclusion–exclusion to avoid double counting.
• Pair-wise term: 2^(n − |union of the two keys|).
• Disjoint keys lower |union|? No — they raise |union|, lowering the intersection count.
• The intersection set is always subtracted exactly once for two-key problems.
• Always list a few superkeys for tiny problems as a sanity check.
• Extend to three keys with the standard three-set inclusion–exclusion.
  :::

:::memory
"Force the key, free the rest; subtract the overlap, that's the test."
:::

:::recap

• Superkeys = candidate key forced + non-key attributes free.
• One candidate key of size k → 2^(n − k) superkeys.
• Two candidate keys → sum the individual counts, subtract the overlap.
• Inclusion–exclusion is non-negotiable when keys overlap or are disjoint.
  :::

When a referenced (parent) tuple is deleted or its key updated, referential integrity must be maintained via one of: CASCADE (propagate the delete/update to child tuples), SET NULL (set the child's foreign key to NULL — requires FK be nullable), SET DEFAULT (set FK to a predefined default), and RESTRICT/NO ACTION (reject the operation if dependent child tuples exist). Default in most systems is NO ACTION/RESTRICT. Memory aid: CASCADE = follow the parent; SET NULL = orphan but flagged; RESTRICT = block. ON DELETE CASCADE can chain through multiple levels. Choosing CASCADE carelessly risks unintended mass deletions; RESTRICT is the safest conservative choice.

NULL means 'unknown' or 'not applicable', NOT zero or empty string. Key pitfalls for GATE: (1) NULL = NULL evaluates to UNKNOWN, not TRUE — use IS NULL. (2) Aggregate functions ignore NULLs (except COUNT(*) which counts all rows). (3) Three-valued logic: AND/OR/NOT over {TRUE, FALSE, UNKNOWN}. TRUE OR UNKNOWN = TRUE; FALSE AND UNKNOWN = FALSE; TRUE AND UNKNOWN = UNKNOWN. (4) A WHERE clause passes a row only if the condition is TRUE (UNKNOWN rows are dropped). (5) UNIQUE constraints generally allow multiple NULLs, but PRIMARY KEY allows none. Shortcut: any arithmetic with NULL yields NULL; any comparison with NULL yields UNKNOWN.

With T=TRUE, F=FALSE, U=UNKNOWN: AND -> T AND U = U, F AND U = F, U AND U = U. OR -> T OR U = T, F OR U = U, U OR U = U. NOT U = U. Quick rule: in AND, F dominates (F with anything = F); in OR, T dominates (T with anything = T); U appears only when no dominating value is present. For WHERE/HAVING/JOIN-ON, only rows evaluating to TRUE qualify; both FALSE and UNKNOWN rows are excluded. For CHECK constraints the opposite leniency applies: a CHECK passes unless it evaluates to FALSE (UNKNOWN is accepted). Remember this asymmetry: WHERE needs TRUE; CHECK only rejects FALSE.