Class 12Computer Science · Database ManagementFull chapter

Database Concepts and the Relational Model

The whole chapter in one place — read it, then test yourself. Clear notes, a reference sheet, a practice quiz, and worked NCERT solutions & PYQs.

In this chapter
Why Databases Exist: What Goes Wrong Without OneRelation, Attribute, Tuple and DomainDegree and Cardinality — Count Them, Do Not GuessCandidate, Primary and Alternate KeysForeign Keys and Referential Integrity

Why Databases Exist: What Goes Wrong Without One

Quick answer Keeping everything in one wide table repeats the same facts over and over, and a half-finished update then leaves one member stored with two different cities — the exact mess a DBMS is built to prevent.

A school office once ran entirely on registers: one for admissions, one for library issues, one for fees. The same student's name and city were copied by hand into all three. That is the file-based approach, and computerising it without a database only makes the copying faster, not safer.

Let us build that mess for real and watch it break. Everything below was run on MySQL 8.0.41.

CREATE DATABASE cs12_database_concepts;
USE cs12_database_concepts;

CREATE TABLE LIBRARY_FLAT (
  MemberId   CHAR(4),
  MemberName VARCHAR(25),
  MemberCity VARCHAR(20),
  BookCode   CHAR(5),
  BookTitle  VARCHAR(30),
  IssueDate  DATE
);

INSERT INTO LIBRARY_FLAT VALUES
('M01','Aarav Sharma','Delhi','B101','Wings of Fire','2026-07-02'),
('M01','Aarav Sharma','Delhi','B107','Train to Pakistan','2026-07-14'),
('M02','Diya Nair','Kochi','B101','Wings of Fire','2026-07-05'),
('M02','Diya Nair','Kochi','B120','The God of Small Things','2026-07-19'),
('M03','Rohan Verma','Jaipur','B107','Train to Pakistan','2026-07-21');

SELECT * FROM LIBRARY_FLAT;

The output MySQL returned:

+----------+--------------+------------+----------+-------------------------+------------+
| MemberId | MemberName   | MemberCity | BookCode | BookTitle               | IssueDate  |
+----------+--------------+------------+----------+-------------------------+------------+
| M01      | Aarav Sharma | Delhi      | B101     | Wings of Fire           | 2026-07-02 |
| M01      | Aarav Sharma | Delhi      | B107     | Train to Pakistan       | 2026-07-14 |
| M02      | Diya Nair    | Kochi      | B101     | Wings of Fire           | 2026-07-05 |
| M02      | Diya Nair    | Kochi      | B120     | The God of Small Things | 2026-07-19 |
| M03      | Rohan Verma  | Jaipur     | B107     | Train to Pakistan       | 2026-07-21 |
+----------+--------------+------------+----------+-------------------------+------------+

Something is already wrong. Data redundancy: the pair Aarav Sharma / Delhi is stored twice, and the title Wings of Fire is stored twice, only because two loans happened. Wasted space is the least of it. Redundancy is what makes the next three problems possible.

Update anomaly. Diya Nair shifts from Kochi to Thrissur. A clerk updates the row in front of him and moves on:

UPDATE LIBRARY_FLAT SET MemberCity='Thrissur'
WHERE MemberId='M02' AND BookCode='B101';

SELECT MemberId, MemberName, MemberCity FROM LIBRARY_FLAT WHERE MemberId='M02';
+----------+------------+------------+
| MemberId | MemberName | MemberCity |
+----------+------------+------------+
| M02      | Diya Nair  | Thrissur   |
| M02      | Diya Nair  | Kochi      |
+----------+------------+------------+

One member, two cities, and nothing in the system says which is correct. This is data inconsistency. Notice that MySQL did not complain — a flat table has no way of knowing those two rows were supposed to agree. An audit query makes the damage countable:

SELECT MemberId, COUNT(DISTINCT MemberCity) AS CitiesOnRecord
FROM LIBRARY_FLAT
GROUP BY MemberId;
+----------+----------------+
| MemberId | CitiesOnRecord |
+----------+----------------+
| M01      |              1 |
| M02      |              2 |
| M03      |              1 |
+----------+----------------+

Deletion anomaly. Rohan Verma returns his only book, so the loan record is deleted:

DELETE FROM LIBRARY_FLAT WHERE MemberId='M03' AND BookCode='B107';
SELECT COUNT(*) AS RowsForM03 FROM LIBRARY_FLAT WHERE MemberId='M03';
+------------+
| RowsForM03 |
+------------+
|          0 |
+------------+

Rohan Verma has vanished from the library's records altogether. He is still a member; we deleted a loan and lost a person, because there was nowhere else to keep the fact that he exists.

Insertion anomaly. A new member enrols today but has not borrowed anything yet. There is no row we can create for him without leaving BookCode, BookTitle and IssueDate empty. We cannot record a member independently of a loan.

Those four problems are the whole justification for a database. A database is an organised collection of related data. A DBMS (Database Management System) is the software that stores that data, enforces rules on it and answers queries about it. MySQL is a DBMS. A DBMS that keeps its data as relations and links them through common attributes — the model this chapter is about — is called an RDBMS, a Relational DBMS; MySQL, Oracle, PostgreSQL and SQLite are all RDBMS products.

What a DBMS gives you that loose files cannot:

  • Controlled redundancy — each fact is stored once, in the table where it belongs.
  • Consistency — because a fact is stored once, it cannot disagree with itself.
  • Data integrity — rules such as marks lie between 0 and 100, or this hostel code must exist, are enforced by the software rather than by a clerk's memory.
  • Sharing with concurrency control — many users read and write at the same time without corrupting each other's work.
  • Security — different users get different privileges on different tables.
  • Backup and recovery — the DBMS can restore a consistent state after a crash.
  • Data independence — how data is physically stored can change without rewriting every program that uses it.

The cure for all four anomalies is the same: store each fact once, in a relation of its own, and link the relations together. Member details in one table, loan details in another, joined by MemberId. How those relations are described, and what keeps the link honest, is the rest of this chapter.

Create and select a database CREATE DATABASE db_name; USE db_name; Without USE, every table must be written as db_name.table_name. Forgetting USE is the most common first error of the practical exam.
Create a relation CREATE TABLE t (col1 TYPE, col2 TYPE, ...); The column list fixes the degree. You cannot add an attribute later with INSERT — that needs ALTER TABLE.
Insert several tuples at once INSERT INTO t VALUES (...),(...),(...); Values must follow the order the columns were declared in, otherwise list the column names explicitly after the table name.
Detect an inconsistency SELECT key_col, COUNT(DISTINCT fact_col) FROM t GROUP BY key_col; Any count above 1 means one entity is stored with two different values — a live update anomaly you can point at.
Remove your practice database DROP DATABASE IF EXISTS db_name; IF EXISTS stops it erroring when the database is already gone. There is no undo and no confirmation prompt.
Remember
  • The file-based approach fails through data redundancy, which then causes update, deletion and insertion anomalies.
  • Redundancy plus a partial update produces inconsistency with no warning from the system — we saw one member stored with two different cities at once.
  • A deletion anomaly destroys facts you never meant to delete: removing Rohan Verma's only loan removed every trace of him as a member.
  • A database stores each fact once; a DBMS is the software that enforces the rules on it, and a DBMS built on the relational model is called an RDBMS.
  • Key benefits of a DBMS: controlled redundancy, consistency, integrity, sharing, security, backup and recovery, and data independence.

Relation, Attribute, Tuple and Domain

Quick answer A relation is a table of named attributes and unordered tuples in which every attribute is confined to one domain — and MySQL enforces that domain by refusing the whole tuple, not by quietly correcting it.

The relational model was proposed by E. F. Codd in 1970. Its central idea is simple: keep all data in tables, and let one general-purpose language ask questions about them. Every term the board asks you to define comes from that model, and each has an everyday equivalent.

Relational termEveryday termMeaning
RelationTableA named two-dimensional grid of data
TupleRow or recordOne complete set of facts about one entity
AttributeColumn or fieldOne named property, the same kind of fact in every tuple
Domain—The set of permitted values for one attribute
Degree—The number of attributes
Cardinality—The number of tuples

Here is a real relation. Notice that CREATE TABLE names the attributes and declares each one's domain in the same breath.

CREATE TABLE STUDENT (
  AdmNo      INT,
  Name       VARCHAR(25),
  City       VARCHAR(20),
  Stream     VARCHAR(12),
  Marks      DECIMAL(5,2),
  Email      VARCHAR(30),
  HostelCode CHAR(2)
);

INSERT INTO STUDENT VALUES
(1001,'Aarav Sharma','Delhi','Science',88.00,'aarav.sharma@example.in','H1'),
(1002,'Diya Nair','Kochi','Commerce',91.50,'diya.nair@example.in','H2'),
(1003,'Rohan Verma','Jaipur','Science',76.25,'rohan.verma@example.in','H1'),
(1004,'Ananya Iyer','Chennai','Humanities',84.00,'ananya.iyer@example.in','H3'),
(1005,'Kabir Singh','Ludhiana','Science',69.75,'kabir.singh@example.in','H2'),
(1006,'Diya Nair','Pune','Science',79.00,'diya.n2@example.in','H1'),
(1007,'Meera Joshi','Nagpur','Commerce',93.25,'meera.joshi@example.in','H3'),
(1008,'Arjun Reddy','Hyderabad','Science',58.50,'arjun.reddy@example.in','H2'),
(1009,'Sneha Das','Kolkata','Humanities',87.00,'sneha.das@example.in','H1'),
(1010,'Vihaan Patel','Ahmedabad','Commerce',72.00,'vihaan.patel@example.in','H3');

SELECT * FROM STUDENT;
+-------+--------------+-----------+------------+-------+-------------------------+------------+
| AdmNo | Name         | City      | Stream     | Marks | Email                   | HostelCode |
+-------+--------------+-----------+------------+-------+-------------------------+------------+
|  1001 | Aarav Sharma | Delhi     | Science    | 88.00 | aarav.sharma@example.in | H1         |
|  1002 | Diya Nair    | Kochi     | Commerce   | 91.50 | diya.nair@example.in    | H2         |
|  1003 | Rohan Verma  | Jaipur    | Science    | 76.25 | rohan.verma@example.in  | H1         |
|  1004 | Ananya Iyer  | Chennai   | Humanities | 84.00 | ananya.iyer@example.in  | H3         |
|  1005 | Kabir Singh  | Ludhiana  | Science    | 69.75 | kabir.singh@example.in  | H2         |
|  1006 | Diya Nair    | Pune      | Science    | 79.00 | diya.n2@example.in      | H1         |
|  1007 | Meera Joshi  | Nagpur    | Commerce   | 93.25 | meera.joshi@example.in  | H3         |
|  1008 | Arjun Reddy  | Hyderabad | Science    | 58.50 | arjun.reddy@example.in  | H2         |
|  1009 | Sneha Das    | Kolkata   | Humanities | 87.00 | sneha.das@example.in    | H1         |
|  1010 | Vihaan Patel | Ahmedabad | Commerce   | 72.00 | vihaan.patel@example.in | H3         |
+-------+--------------+-----------+------------+-------+-------------------------+------------+

Read the vocabulary straight off that output. The whole grid is the relation STUDENT. Name is an attribute. The line beginning 1004 is a tuple — one student, completely described. The single value Chennai is one attribute value.

The domain is the pool each attribute may draw from, and MySQL keeps it as part of the table's definition:

DESCRIBE STUDENT;
+------------+--------------+------+-----+---------+-------+
| Field      | Type         | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| AdmNo      | int          | YES  |     | NULL    |       |
| Name       | varchar(25)  | YES  |     | NULL    |       |
| City       | varchar(20)  | YES  |     | NULL    |       |
| Stream     | varchar(12)  | YES  |     | NULL    |       |
| Marks      | decimal(5,2) | YES  |     | NULL    |       |
| Email      | varchar(30)  | YES  |     | NULL    |       |
| HostelCode | char(2)      | YES  |     | NULL    |       |
+------------+--------------+------+-----+---------+-------+

Read that as: AdmNo may hold whole numbers; Name at most 25 characters; Marks a decimal of at most 5 digits with 2 after the point, so 999.99 is its ceiling; HostelCode exactly 2 characters.

A domain is not a suggestion. Push a value outside it and the tuple is refused, not silently repaired:

INSERT INTO STUDENT VALUES
(1099,'Test Kumar','Bhopal','Science','Ninety','t.k@example.in','H1');
ERROR 1366 (HY000): Incorrect decimal value: 'Ninety' for column 'Marks' at row 1
INSERT INTO STUDENT VALUES
(1098,'Test Kumar','Bhopal','Science',81.00,'t2@example.in','HOSTEL-1');
ERROR 1406 (22001): Data too long for column 'HostelCode' at row 1

After both attempts SELECT COUNT(*) FROM STUDENT; still returned 10. Nothing was half-written. That refusal is what the phrase the DBMS enforces integrity actually looks like in practice. The numeric ceiling behaves the same way: DECIMAL(5,2) accepted 999.99 but refused 1000.00 with ERROR 1264 (22003): Out of range value.

The domain declared by a data type is usually far wider than the values really in use. To see the values actually present, project the attribute:

SELECT DISTINCT Stream FROM STUDENT;
+------------+
| Stream     |
+------------+
| Science    |
| Commerce   |
| Humanities |
+------------+

Three values are in use; the declared VARCHAR(12) domain would accept a great many more. If only these three are ever to be legal, that has to be stated as a rule, for example CHECK (Stream IN ('Science','Commerce','Humanities')).

Properties every relation must satisfy:

  • Every attribute value is atomic — one indivisible value per cell, never a list.
  • All values of an attribute come from the same domain.
  • Attribute names are unique within the relation.
  • The order of tuples is immaterial — a relation is a set of tuples, not a sequence.
  • The order of attributes is immaterial too, since you refer to them by name.
  • In theory no two tuples are identical — which is precisely why a relation needs a key.

One idea to carry into the SQL chapter: the result of a query is itself a relation. This query returned a smaller table with its own attributes and its own tuples:

SELECT Name, City, Marks FROM STUDENT WHERE Stream='Science';
+--------------+-----------+-------+
| Name         | City      | Marks |
+--------------+-----------+-------+
| Aarav Sharma | Delhi     | 88.00 |
| Rohan Verma  | Jaipur    | 76.25 |
| Kabir Singh  | Ludhiana  | 69.75 |
| Diya Nair    | Pune      | 79.00 |
| Arjun Reddy  | Hyderabad | 58.50 |
+--------------+-----------+-------+

Being a relation, it has a degree and a cardinality of its own — which is exactly where the next section begins.

Declare an attribute's domain col_name DATATYPE(size) VARCHAR(25) means at most 25 characters. DECIMAL(5,2) means 5 digits in total with 2 after the point, so the largest value is 999.99, not 99999.99 — verified, 1000.00 raises ERROR 1264.
Inspect structure and domains DESCRIBE table_name; Can be shortened to DESC table_name; The Key column shows PRI, UNI or MUL once keys have been declared.
See the values actually in use SELECT DISTINCT col FROM t; This gives the values present, not the declared domain. The declared domain is almost always much larger.
Narrow a domain beyond the data type col DECIMAL(5,2) CHECK (col >= 0 AND col <= 100) CHECK is actually enforced from MySQL 8.0.16 onwards; a violation raises ERROR 3819. In older versions it was parsed and ignored.
Change an attribute's domain ALTER TABLE t MODIFY col NEW_TYPE; Fails if data already in the table will not fit the new, narrower domain — narrowing Name to VARCHAR(5) here gave ERROR 1265, data truncated, and the table was left untouched.
List the relations in a database SHOW TABLES; Shows tables of the currently selected database only — so run USE first.
Remember
  • Relation = table, tuple = row, attribute = column, domain = the set of values an attribute is allowed to hold.
  • A domain is enforced: MySQL rejected 'Ninety' for a DECIMAL column (ERROR 1366) and 'HOSTEL-1' for CHAR(2) (ERROR 1406), and the row count stayed at 10.
  • DECIMAL(5,2) really does stop at 999.99 — 1000.00 was refused with ERROR 1264, out of range.
  • The declared domain is normally wider than the values in use; SELECT DISTINCT shows what is present, not what is permitted.
  • A relation requires atomic values, one domain per attribute, unique attribute names, and no significance to row or column order.
  • The result of a SELECT is itself a relation, with its own attributes and tuples.

Degree and Cardinality — Count Them, Do Not Guess

Quick answer Degree is the number of attributes and cardinality the number of tuples; STUDENT has degree 7 and cardinality 10, two numbers that cannot be swapped without the error showing.

Two numbers describe the size of any relation, and the board asks for them almost every year:

  • Degree = the number of attributes (columns).
  • Cardinality = the number of tuples (rows).

Students swap these under exam pressure more often than they get any other definition wrong. Fix it with the word itself. Cardinality comes from cardinal number, which is a plain count of things — and the things being counted are records. Degree describes the shape of one record: how many fields it carries. A deck of playing cards has cardinality 52; its degree is however many properties each card has (suit, rank, colour).

Now count them off something visible rather than trusting a definition. Our STUDENT relation, printed in full in the last section, was built with 7 columns and 10 rows — deliberately different numbers, so a swap cannot hide behind a coincidence.

SELECT COUNT(*) AS Degree FROM information_schema.columns
WHERE table_schema='cs12_database_concepts' AND table_name='STUDENT';

SELECT COUNT(*) AS Cardinality FROM STUDENT;
+--------+
| Degree |
+--------+
|      7 |
+--------+

+-------------+
| Cardinality |
+-------------+
|          10 |
+-------------+

Degree 7, cardinality 10. Check it by hand against the DESCRIBE output: AdmNo, Name, City, Stream, Marks, Email, HostelCode — seven attributes. Rows 1001 to 1010 — ten tuples. In the exam you will simply count the columns and rows of the printed table; this is how you verify yourself on a machine.

Be careful with a relation where the two numbers happen to agree. The HOSTEL relation used later in this chapter has 3 attributes and 3 tuples. Equal numbers are an accident of that table, never a rule, and never a reason to stop distinguishing the terms.

Which operation changes which number. This is the second-favourite exam question, usually phrased what will the degree and cardinality be after...

OperationDegreeCardinality
INSERT one tupleunchangedincreases by 1
DELETE one tupleunchangeddecreases by 1
ALTER TABLE ... ADD COLUMNincreases by 1unchanged
ALTER TABLE ... DROP COLUMNdecreases by 1unchanged
UPDATE existing valuesunchangedunchanged

Worked example. A second relation, COACH, starts with 5 attributes and 6 tuples. Build it so you can follow along:

CREATE TABLE COACH (
  CoachId   INT,
  CoachName VARCHAR(20),
  Sport     VARCHAR(12),
  Pay       INT,
  City      VARCHAR(15)
);

INSERT INTO COACH VALUES
(11,'Ravi Kumbhar','Cricket',48000,'Pune'),
(12,'Sunita Rane','Badminton',52000,'Nagpur'),
(13,'Imran Khan','Football',45000,'Kolkata'),
(14,'Leela Menon','Athletics',61000,'Kochi'),
(15,'Harpreet Kaur','Hockey',57000,'Jalandhar'),
(16,'Ravi Kumbhar','Swimming',50000,'Pune');

SELECT * FROM COACH;
+---------+---------------+-----------+-------+-----------+
| CoachId | CoachName     | Sport     | Pay   | City      |
+---------+---------------+-----------+-------+-----------+
|      11 | Ravi Kumbhar  | Cricket   | 48000 | Pune      |
|      12 | Sunita Rane   | Badminton | 52000 | Nagpur    |
|      13 | Imran Khan    | Football  | 45000 | Kolkata   |
|      14 | Leela Menon   | Athletics | 61000 | Kochi     |
|      15 | Harpreet Kaur | Hockey    | 57000 | Jalandhar |
|      16 | Ravi Kumbhar  | Swimming  | 50000 | Pune      |
+---------+---------------+-----------+-------+-----------+

MySQL confirmed cardinality 6 and degree 5. Now add one attribute and two tuples:

ALTER TABLE COACH ADD COLUMN Experience INT;
INSERT INTO COACH VALUES (17,'Deepak Yadav','Kabaddi',43000,'Lucknow',6);
INSERT INTO COACH VALUES (18,'Fatima Sheikh','Chess',46000,'Hyderabad',9);

SELECT COUNT(*) AS New_Cardinality FROM COACH;
SELECT COUNT(*) AS New_Degree FROM information_schema.columns
WHERE table_schema='cs12_database_concepts' AND table_name='COACH';
+-----------------+
| New_Cardinality |
+-----------------+
|               8 |
+-----------------+

+------------+
| New_Degree |
+------------+
|          6 |
+------------+

Degree went 5 to 6, cardinality 6 to 8 — exactly what the table above predicts. And look at what ALTER did to the rows that already existed:

SELECT * FROM COACH;
+---------+---------------+-----------+-------+-----------+------------+
| CoachId | CoachName     | Sport     | Pay   | City      | Experience |
+---------+---------------+-----------+-------+-----------+------------+
|      11 | Ravi Kumbhar  | Cricket   | 48000 | Pune      |       NULL |
|      12 | Sunita Rane   | Badminton | 52000 | Nagpur    |       NULL |
|      13 | Imran Khan    | Football  | 45000 | Kolkata   |       NULL |
|      14 | Leela Menon   | Athletics | 61000 | Kochi     |       NULL |
|      15 | Harpreet Kaur | Hockey    | 57000 | Jalandhar |       NULL |
|      16 | Ravi Kumbhar  | Swimming  | 50000 | Pune      |       NULL |
|      17 | Deepak Yadav  | Kabaddi   | 43000 | Lucknow   |          6 |
|      18 | Fatima Sheikh | Chess     | 46000 | Hyderabad |          9 |
+---------+---------------+-----------+-------+-----------+------------+

The six original coaches received NULL for Experience. Adding an attribute never adds or removes tuples — it widens every tuple that already exists, and it will not invent a value such as 0 to fill the gap.

Derived relations have their own two numbers. A query result is a relation, so it carries a degree and a cardinality that are usually not those of the base table. From the 10-tuple STUDENT:

SELECT Name, City, Marks FROM STUDENT WHERE Stream='Science';
+--------------+-----------+-------+
| Name         | City      | Marks |
+--------------+-----------+-------+
| Aarav Sharma | Delhi     | 88.00 |
| Rohan Verma  | Jaipur    | 76.25 |
| Kabir Singh  | Ludhiana  | 69.75 |
| Diya Nair    | Pune      | 79.00 |
| Arjun Reddy  | Hyderabad | 58.50 |
+--------------+-----------+-------+

Degree 3, because three attributes were projected. Cardinality 5, because five students matched. The base relation is untouched and still 7 by 10 — a SELECT never changes the table it reads.

A trap worth knowing. Cardinality is COUNT(*), not COUNT(column). COUNT(*) counts tuples; COUNT(col) counts only the non-NULL values in that column. Run both on the COACH table as it now stands:

SELECT COUNT(*) AS c_star, COUNT(Experience) AS c_col FROM COACH;
+--------+-------+
| c_star | c_col |
+--------+-------+
|      8 |     2 |
+--------+-------+

Same relation, same cardinality, two different answers — because only one of those expressions is measuring cardinality at all. Six coaches hold NULL in Experience, and COUNT(Experience) simply skips them.

A Cartesian product. When two relations are combined with no matching condition, degrees add while cardinalities multiply. The second relation needed here, HOSTEL, is built only in the last section of this chapter, so the run below was made at the very end — by which point STUDENT holds 11 tuples (one more student is admitted in the foreign key section) and HOSTEL holds 3:

SELECT COUNT(*) AS r FROM STUDENT, HOSTEL;
+----+
| r  |
+----+
| 33 |
+----+

11 times 3 is 33 — every student paired with every hostel. The degree behaves the other way round: STUDENT has 7 attributes and HOSTEL has 3, and a column count of the result gave 7 + 3 = 10. Degrees add, cardinalities multiply.

Cardinality (number of tuples) SELECT COUNT(*) FROM table_name; COUNT(*) counts rows. COUNT(col) skips NULLs in that column and can be smaller — it is not cardinality.
Degree (number of attributes) SELECT COUNT(*) FROM information_schema.columns WHERE table_schema='db' AND table_name='t'; In the exam you simply count the columns of the printed table; this is the machine check. Write the table name as you created it.
Change the degree ALTER TABLE t ADD COLUMN c TYPE; ALTER TABLE t DROP COLUMN c; Affects degree only. Cardinality is untouched, and existing tuples receive NULL in the new attribute.
Change the cardinality INSERT INTO t VALUES (...); DELETE FROM t WHERE condition; Affects cardinality only. Degree is untouched. UPDATE changes neither number.
Cartesian product SELECT * FROM A, B; Degree = degree(A) + degree(B); cardinality = cardinality(A) x cardinality(B). Degrees add, cardinalities multiply — do not mix the two operations up.
Remember
  • Degree = number of attributes; cardinality = number of tuples. STUDENT gave degree 7 and cardinality 10, verified against the server.
  • Cardinality is a cardinal number, a count of records; degree describes how many fields one record carries.
  • INSERT and DELETE change only cardinality; ALTER TABLE ADD or DROP COLUMN changes only degree. COACH went from 5 by 6 to 6 by 8.
  • ADD COLUMN fills existing tuples with NULL — it never fabricates a zero and never changes the row count.
  • Cardinality is COUNT(*), never COUNT(column): on the same 8-tuple COACH, COUNT(*) returned 8 while COUNT(Experience) returned 2.
  • A query result is a relation with its own degree and cardinality: projecting 3 attributes from 10 tuples gave a 3 by 5 result while the base table stayed 7 by 10.
  • In a Cartesian product degrees add and cardinalities multiply: 11 students by 3 hostels gave 33 tuples of degree 7 + 3 = 10.

Candidate, Primary and Alternate Keys

Quick answer Every minimal set of attributes that uniquely identifies a tuple is a candidate key; the designer promotes exactly one to primary key, and whatever is left over is an alternate key.

A relation is a set of tuples, so no two tuples should be identical, and we need a dependable way to point at exactly one of them. That is what keys are for. The three names the board asks about form one short chain:

  1. Find every minimal set of attributes that can uniquely identify a tuple. Each one is a candidate key.
  2. The designer picks exactly one of them to be the primary key.
  3. The candidate keys left over become the alternate keys.

So every primary key and every alternate key was a candidate key first. Three candidate keys means one primary key and two alternate keys. One candidate key means it becomes the primary key and there are no alternate keys at all. Getting this chain right earns most of the marks on the topic.

Testing a candidate against real data. An attribute can only be a key if its values never repeat, so compare the number of distinct values with the number of rows:

SELECT COUNT(*)                   AS Rows_,
       COUNT(DISTINCT AdmNo)      AS D_AdmNo,
       COUNT(DISTINCT Email)      AS D_Email,
       COUNT(DISTINCT Name)       AS D_Name,
       COUNT(DISTINCT City)       AS D_City,
       COUNT(DISTINCT Name, City) AS D_Name_City
FROM STUDENT;
+-------+---------+---------+--------+--------+-------------+
| Rows_ | D_AdmNo | D_Email | D_Name | D_City | D_Name_City |
+-------+---------+---------+--------+--------+-------------+
|    10 |      10 |      10 |      9 |     10 |          10 |
+-------+---------+---------+--------+--------+-------------+

AdmNo and Email both match the row count, so neither repeats. Name does not — 9 distinct names across 10 students. Find the culprit:

SELECT Name, COUNT(*) AS Repeats FROM STUDENT
GROUP BY Name HAVING COUNT(*) > 1;
+-----------+---------+
| Name      | Repeats |
+-----------+---------+
| Diya Nair |       2 |
+-----------+---------+

Two different girls named Diya Nair, one in Kochi and one in Pune. Name is disqualified, and that is exactly why real systems never key on a person's name.

Now the trap. D_City also came out as 10, matching the row count perfectly. Does that make City a candidate key? No. It happens that these ten students come from ten different cities, but nothing stops the eleventh from also being from Delhi. A candidate key is a guarantee about every possible tuple, not an observation about the tuples you have today. The query above can only disprove a candidate key; it can never prove one. Proof comes from the real-world rule. AdmNo qualifies because the school issues each admission number exactly once, by policy. Email qualifies because one address belongs to one person. City qualifies for nothing.

Minimality matters. The combination {AdmNo, Name} also never repeats, but AdmNo alone was already enough, so Name is dead weight. A set of attributes that identifies a tuple uniquely but may carry such extras is a superkey. A candidate key is a superkey from which nothing can be removed. Every candidate key is a superkey; most superkeys are not candidate keys.

Declaring the choice. STUDENT has two candidate keys, {AdmNo} and {Email}. We make AdmNo the primary key — it is short, numeric, and never changes, whereas a student's email address might. Email therefore becomes the alternate key, declared with UNIQUE:

ALTER TABLE STUDENT
  MODIFY AdmNo INT NOT NULL,
  ADD PRIMARY KEY (AdmNo),
  ADD UNIQUE (Email);

DESCRIBE STUDENT;
+------------+--------------+------+-----+---------+-------+
| Field      | Type         | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| AdmNo      | int          | NO   | PRI | NULL    |       |
| Name       | varchar(25)  | YES  |     | NULL    |       |
| City       | varchar(20)  | YES  |     | NULL    |       |
| Stream     | varchar(12)  | YES  |     | NULL    |       |
| Marks      | decimal(5,2) | YES  |     | NULL    |       |
| Email      | varchar(30)  | YES  | UNI | NULL    |       |
| HostelCode | char(2)      | YES  |     | NULL    |       |
+------------+--------------+------+-----+---------+-------+

The Key column now records the design decision: PRI against AdmNo, UNI against Email.

Watching the constraints work. Three inserts, three refusals, each demonstrating a different property.

INSERT INTO STUDENT VALUES
(1003,'Nikhil Rao','Mysuru','Commerce',80.00,'nikhil.rao@example.in','H2');
ERROR 1062 (23000): Duplicate entry '1003' for key 'student.PRIMARY'

A primary key value cannot repeat.

INSERT INTO STUDENT VALUES
(NULL,'Nikhil Rao','Mysuru','Commerce',80.00,'nikhil.rao@example.in','H2');
ERROR 1048 (23000): Column 'AdmNo' cannot be null

A primary key value cannot be NULL. This rule has a name — entity integrity. If the identifier is missing, the tuple cannot be identified at all, so it is not permitted to exist.

INSERT INTO STUDENT VALUES
(1011,'Nikhil Rao','Mysuru','Commerce',80.00,'sneha.das@example.in','H2');
ERROR 1062 (23000): Duplicate entry 'sneha.das@example.in' for key 'student.Email'

The alternate key is enforced every bit as strictly as the primary key. Being merely the alternate costs it nothing in uniqueness. After all three refusals the row count was still 10.

So what is the real difference? NULL. A primary key column is made NOT NULL automatically; a UNIQUE column is not, and may even hold several NULLs. Verified on a scratch table:

CREATE TABLE T1 (id INT PRIMARY KEY, email VARCHAR(20) UNIQUE);
INSERT INTO T1 VALUES (1,NULL),(2,NULL),(3,'a@b.in');
DESCRIBE T1;
SELECT * FROM T1;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | NO   | PRI | NULL    |       |
| email | varchar(20) | YES  | UNI | NULL    |       |
+-------+-------------+------+-----+---------+-------+

+----+--------+
| id | email  |
+----+--------+
|  1 | NULL   |
|  2 | NULL   |
|  3 | a@b.in |
+----+--------+

Look at the Null column: NO for the primary key even though NOT NULL was never written, YES for the unique one — and two NULL emails were accepted without complaint.

Composite keys. Sometimes no single attribute is enough. In a marksheet a student appears once per subject, so neither AdmNo nor Subject is unique on its own, but the pair is:

CREATE TABLE MARKSHEET (
  AdmNo   INT,
  Subject VARCHAR(15),
  Marks   DECIMAL(5,2) CHECK (Marks >= 0 AND Marks <= 100),
  PRIMARY KEY (AdmNo, Subject)
);

INSERT INTO MARKSHEET VALUES (1001,'CS',88.00),(1001,'Maths',79.50),(1002,'CS',91.00);
INSERT INTO MARKSHEET VALUES (1001,'Physics',83.00);
SELECT * FROM MARKSHEET;
+-------+---------+-------+
| AdmNo | Subject | Marks |
+-------+---------+-------+
|  1001 | CS      | 88.00 |
|  1001 | Maths   | 79.50 |
|  1001 | Physics | 83.00 |
|  1002 | CS      | 91.00 |
+-------+---------+-------+

Student 1001 appears three times, which is perfectly legal. Repeat the same student with the same subject and the pair collides:

INSERT INTO MARKSHEET VALUES (1001,'CS',70.00);
ERROR 1062 (23000): Duplicate entry '1001-CS' for key 'marksheet.PRIMARY'

MySQL prints the combined value 1001-CS, which shows plainly that the pair is what must be unique. The CHECK on Marks is a domain rule and is enforced quite separately:

INSERT INTO MARKSHEET VALUES (1003,'CS',105.00);
ERROR 3819 (HY000): Check constraint 'marksheet_chk_1' is violated.

The name marksheet_chk_1 was invented by MySQL because we did not name the constraint ourselves — it is simply the table name followed by _chk_ and a serial number.

Test whether an attribute could be a key SELECT COUNT(*), COUNT(DISTINCT col) FROM t; Equal numbers mean unique in this instance only. A genuine candidate key needs a real-world rule guaranteeing it stays unique for all future tuples.
Find the duplicates that disqualify it SELECT col, COUNT(*) FROM t GROUP BY col HAVING COUNT(*) > 1; An empty result means no duplicates right now. It is still not a proof of a key.
Primary key at creation CREATE TABLE t (id INT PRIMARY KEY, ...); Makes the column NOT NULL automatically. A relation may have only one primary key, though that key may span several attributes.
Alternate key UNIQUE (col) Enforces uniqueness just as strictly as a primary key, but still permits NULL — and MySQL permits several NULLs. That is the only real difference.
Composite primary key PRIMARY KEY (col1, col2) The combination must be unique; either attribute alone may repeat. A clash reports as Duplicate entry 'val1-val2'.
Add keys to an existing relation ALTER TABLE t MODIFY id INT NOT NULL, ADD PRIMARY KEY (id), ADD UNIQUE (email); Written this way the intent is explicit. Leave the MODIFY out and ADD PRIMARY KEY still succeeds — MySQL quietly rewrites the column to NOT NULL for you, which is easy to miss when you next read the table definition.
Remember
  • Candidate key -> one is chosen as the primary key -> the rest are alternate keys. Every primary and alternate key was a candidate key first.
  • A candidate key must be minimal: {AdmNo, Name} is unique but Name is removable, so it is only a superkey, not a candidate key.
  • COUNT(DISTINCT col) = COUNT(*) can disprove a key but never prove one — City passed the test on 10 rows yet is obviously not a key.
  • A primary key is automatically NOT NULL (entity integrity); a UNIQUE alternate key allows NULL, and MySQL accepted two NULLs in one UNIQUE column.
  • A composite primary key such as PRIMARY KEY (AdmNo, Subject) requires the combination to be unique; either attribute alone may repeat, and a clash is reported as Duplicate entry '1001-CS'.

Foreign Keys and Referential Integrity

Quick answer A foreign key is an attribute whose values must already exist as a primary key in another relation, and MySQL enforces it by refusing the child INSERT with error 1452 and the parent DELETE with error 1451.

Splitting one wide table into two cures the anomalies from the first section, but it introduces a fresh risk: the link between the two tables can be wrong. A foreign key is the rule that stops that.

Definition. A foreign key is an attribute, or set of attributes, in one relation whose values must appear as the primary key of another relation. The relation holding the foreign key is the referencing (child) relation; the one it points at is the referenced (parent) relation. The property being enforced is referential integrity: no tuple may refer to a parent that does not exist.

First, life without one. STUDENT already carries a HostelCode attribute, but so far nothing checks it. Here is the parent relation:

CREATE TABLE HOSTEL (
  HostelCode CHAR(2) PRIMARY KEY,
  HostelName VARCHAR(20),
  Warden     VARCHAR(25)
);

INSERT INTO HOSTEL VALUES
('H1','Tagore House','Mr R Balan'),
('H2','Nehru House','Ms S Kulkarni'),
('H3','Bose House','Mr A Qureshi');

SELECT * FROM HOSTEL;
+------------+--------------+---------------+
| HostelCode | HostelName   | Warden        |
+------------+--------------+---------------+
| H1         | Tagore House | Mr R Balan    |
| H2         | Nehru House  | Ms S Kulkarni |
| H3         | Bose House   | Mr A Qureshi  |
+------------+--------------+---------------+

Only H1, H2 and H3 exist. Now watch MySQL cheerfully accept a student allotted to a hostel that does not:

INSERT INTO STUDENT VALUES
(1011,'Nikhil Rao','Mysuru','Commerce',80.00,'nikhil.rao@example.in','H9');

SELECT AdmNo, Name, HostelCode FROM STUDENT WHERE AdmNo=1011;
+-------+------------+------------+
| AdmNo | Name       | HostelCode |
+-------+------------+------------+
|  1011 | Nikhil Rao | H9         |
+-------+------------+------------+

No error at all. H9 is a perfectly valid CHAR(2) value, so the domain rule is satisfied — but the hostel does not exist. This is a dangling reference, and it stays invisible until somebody runs a report and finds a student with no warden.

Now declare the foreign key — and watch it refuse to be created, because the data is already dirty:

ALTER TABLE STUDENT ADD FOREIGN KEY (HostelCode) REFERENCES HOSTEL(HostelCode);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails
(`cs12_database_concepts`.`#sql-788_308b`, CONSTRAINT `student_ibfk_1`
FOREIGN KEY (`HostelCode`) REFERENCES `hostel` (`HostelCode`))

Two details in that message are worth decoding. The odd name #sql-788_308b is the temporary copy MySQL builds while rebuilding a table, so the digits will differ on your machine — do not expect to see the same ones. And student_ibfk_1 is the name MySQL invented for the constraint, because we did not supply one.

The refusal itself is valuable: a foreign key cannot be added until every existing value already matches a parent. Repair the bad tuple, then add the constraint under a name of our own choosing:

UPDATE STUDENT SET HostelCode='H2' WHERE AdmNo=1011;

ALTER TABLE STUDENT
  ADD CONSTRAINT fk_hostel
  FOREIGN KEY (HostelCode) REFERENCES HOSTEL(HostelCode);

DESCRIBE STUDENT;
+------------+--------------+------+-----+---------+-------+
| Field      | Type         | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| AdmNo      | int          | NO   | PRI | NULL    |       |
| Name       | varchar(25)  | YES  |     | NULL    |       |
| City       | varchar(20)  | YES  |     | NULL    |       |
| Stream     | varchar(12)  | YES  |     | NULL    |       |
| Marks      | decimal(5,2) | YES  |     | NULL    |       |
| Email      | varchar(30)  | YES  | UNI | NULL    |       |
| HostelCode | char(2)      | YES  | MUL | NULL    |       |
+------------+--------------+------+-----+---------+-------+

HostelCode now shows MUL in the Key column — it is indexed and constrained, but its values are allowed to repeat, because many students share one hostel. That is normal and important: a foreign key is not unique on the child side.

The two errors the board expects you to recognise.

First, inserting a child that points at a parent which does not exist:

INSERT INTO STUDENT VALUES
(1012,'Ishita Bose','Patna','Science',77.00,'ishita.bose@example.in','H9');
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails
(`cs12_database_concepts`.`student`, CONSTRAINT `fk_hostel`
FOREIGN KEY (`HostelCode`) REFERENCES `hostel` (`HostelCode`))

The very same kind of insert succeeded a few minutes earlier. The data type has not changed; the rule has.

Second, deleting a parent that still has children:

DELETE FROM HOSTEL WHERE HostelCode='H1';
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails
(`cs12_database_concepts`.`student`, CONSTRAINT `fk_hostel`
FOREIGN KEY (`HostelCode`) REFERENCES `hostel` (`HostelCode`))

Four students live in Tagore House. Deleting that hostel row would leave them pointing at nothing, so MySQL refuses. Memorise the pair: 1452 is a bad INSERT into the child; 1451 is a blocked DELETE from the parent.

A foreign key may be NULL. A primary key may never be, but a foreign key can — it simply means this tuple refers to no parent yet, which is a legitimate state of affairs. A student admitted but not yet allotted a hostel:

INSERT INTO STUDENT VALUES
(1013,'Ishita Bose','Patna','Science',77.00,'ishita.bose@example.in',NULL);

SELECT AdmNo, Name, HostelCode FROM STUDENT WHERE AdmNo=1013;
+-------+-------------+------------+
| AdmNo | Name        | HostelCode |
+-------+-------------+------------+
|  1013 | Ishita Bose | NULL       |
+-------+-------------+------------+

Accepted. This is the cleanest way to hold the distinction: a primary key answers which tuple is this? and must always have an answer, while a foreign key answers which parent does it belong to? and is allowed to answer none yet.

Rules a foreign key must satisfy:

  • The referenced attribute must be the primary key of the parent relation, or at least declared UNIQUE. Point a foreign key at an attribute with no index at all and MySQL will not create the constraint — it answers ERROR 1822 ... Missing index for constraint.
  • The two attributes must have compatible data types — CHAR(2) on both sides here. Compatible does not mean identical: string lengths may differ, and MySQL 8.0.41 also accepted a VARCHAR(2) child pointing at a CHAR(2) parent. What it refuses is a real mismatch such as an INT child pointing at a CHAR(2) parent, which gives ERROR 3780 ... are incompatible.
  • Every non-NULL value in the child must exist in the parent, at all times.

The payoff. With the link guaranteed, joining the two relations is safe, because every student is certain to find a hostel. (Student 1013 was removed before this query, leaving 11 students.)

DELETE FROM STUDENT WHERE AdmNo=1013;

SELECT S.AdmNo, S.Name, S.HostelCode, H.HostelName, H.Warden
FROM STUDENT S JOIN HOSTEL H ON S.HostelCode = H.HostelCode
ORDER BY S.AdmNo;
+-------+--------------+------------+--------------+---------------+
| AdmNo | Name         | HostelCode | HostelName   | Warden        |
+-------+--------------+------------+--------------+---------------+
|  1001 | Aarav Sharma | H1         | Tagore House | Mr R Balan    |
|  1002 | Diya Nair    | H2         | Nehru House  | Ms S Kulkarni |
|  1003 | Rohan Verma  | H1         | Tagore House | Mr R Balan    |
|  1004 | Ananya Iyer  | H3         | Bose House   | Mr A Qureshi  |
|  1005 | Kabir Singh  | H2         | Nehru House  | Ms S Kulkarni |
|  1006 | Diya Nair    | H1         | Tagore House | Mr R Balan    |
|  1007 | Meera Joshi  | H3         | Bose House   | Mr A Qureshi  |
|  1008 | Arjun Reddy  | H2         | Nehru House  | Ms S Kulkarni |
|  1009 | Sneha Das    | H1         | Tagore House | Mr R Balan    |
|  1010 | Vihaan Patel | H3         | Bose House   | Mr A Qureshi  |
|  1011 | Nikhil Rao   | H2         | Nehru House  | Ms S Kulkarni |
+-------+--------------+------------+--------------+---------------+

Compare this with the flat library table of the first section. Each warden's name is stored exactly once, in HOSTEL. Change it there and every student's report changes with it. No update anomaly is possible here, because there is nothing to update twice.

Finally, all three kinds of key on one relation, read straight out of the server's own catalogue:

SELECT COLUMN_NAME, CONSTRAINT_NAME, REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME
FROM information_schema.KEY_COLUMN_USAGE
WHERE TABLE_SCHEMA='cs12_database_concepts' AND TABLE_NAME='STUDENT';
+-------------+-----------------+-----------------------+------------------------+
| COLUMN_NAME | CONSTRAINT_NAME | REFERENCED_TABLE_NAME | REFERENCED_COLUMN_NAME |
+-------------+-----------------+-----------------------+------------------------+
| Email       | Email           | NULL                  | NULL                   |
| AdmNo       | PRIMARY         | NULL                  | NULL                   |
| HostelCode  | fk_hostel       | hostel                | HostelCode             |
+-------------+-----------------+-----------------------+------------------------+

Email is the alternate key, AdmNo the primary key, HostelCode the foreign key — and only the foreign key names a referenced relation. One relation, three key roles, exactly as this chapter has described them.

Foreign key at creation FOREIGN KEY (col) REFERENCES parent(pk_col) parent(pk_col) must already be a PRIMARY KEY or UNIQUE, and the two columns must have compatible types. Lengths need not match: MySQL 8.0.41 accepted CHAR(5) and even VARCHAR(2) referencing a CHAR(2) parent. Only a genuine mismatch such as INT referencing CHAR(2) is refused, with ERROR 3780.
Add a foreign key later ALTER TABLE child ADD CONSTRAINT fk_name FOREIGN KEY (col) REFERENCES parent(pk_col); Fails with ERROR 1452 if the child already holds a value with no match in the parent. Clean the data first.
Child insert violation ERROR 1452 (23000): Cannot add or update a child row You pointed at a parent that does not exist. Insert the parent tuple first, then the child.
Parent delete violation ERROR 1451 (23000): Cannot delete or update a parent row Child tuples still refer to it. Delete or re-point the children first. ON DELETE CASCADE automates that, but deletes the children too.
See every key on a relation SELECT COLUMN_NAME, CONSTRAINT_NAME, REFERENCED_TABLE_NAME FROM information_schema.KEY_COLUMN_USAGE WHERE TABLE_NAME='t'; REFERENCED_TABLE_NAME is NULL for primary and alternate keys, and filled in only for foreign keys — a quick way to tell them apart.
Remove a foreign key ALTER TABLE child DROP FOREIGN KEY fk_name; Needs the constraint name, which is why naming it yourself (CONSTRAINT fk_hostel) beats letting MySQL invent student_ibfk_1.
Remember
  • A foreign key is an attribute in the referencing (child) relation whose values must exist as the primary key of the referenced (parent) relation; this is referential integrity.
  • Without the constraint MySQL accepted HostelCode 'H9' although no such hostel existed — a dangling reference that no data type can catch.
  • ERROR 1452 = you tried to INSERT or UPDATE a child row with no matching parent. ERROR 1451 = you tried to DELETE a parent row that still has children.
  • A foreign key may be NULL (meaning no parent yet) and may repeat, so it shows as MUL, not PRI or UNI. A primary key may do neither.
  • The referenced attribute must be a primary key or UNIQUE in the parent, and the two attributes must have compatible types — compatible, not identical: CHAR(2) referencing CHAR(2) and VARCHAR(2) referencing CHAR(2) were both accepted, while INT referencing CHAR(2) was refused with ERROR 3780.

The formula sheet

Every formula in this chapter, in one place — screenshot it before your exam.

CREATE DATABASE db_name; USE db_name;
Create and select a database
CREATE TABLE t (col1 TYPE, col2 TYPE, ...);
Create a relation
INSERT INTO t VALUES (...),(...),(...);
Insert several tuples at once
SELECT key_col, COUNT(DISTINCT fact_col) FROM t GROUP BY key_col;
Detect an inconsistency
DROP DATABASE IF EXISTS db_name;
Remove your practice database
col_name DATATYPE(size)
Declare an attribute's domain
DESCRIBE table_name;
Inspect structure and domains
SELECT DISTINCT col FROM t;
See the values actually in use
col DECIMAL(5,2) CHECK (col >= 0 AND col <= 100)
Narrow a domain beyond the data type
ALTER TABLE t MODIFY col NEW_TYPE;
Change an attribute's domain
SHOW TABLES;
List the relations in a database
SELECT COUNT(*) FROM table_name;
Cardinality (number of tuples)
SELECT COUNT(*) FROM information_schema.columns WHERE table_schema='db' AND table_name='t';
Degree (number of attributes)
ALTER TABLE t ADD COLUMN c TYPE; ALTER TABLE t DROP COLUMN c;
Change the degree
INSERT INTO t VALUES (...); DELETE FROM t WHERE condition;
Change the cardinality
SELECT * FROM A, B;
Cartesian product
SELECT COUNT(*), COUNT(DISTINCT col) FROM t;
Test whether an attribute could be a key
SELECT col, COUNT(*) FROM t GROUP BY col HAVING COUNT(*) > 1;
Find the duplicates that disqualify it
CREATE TABLE t (id INT PRIMARY KEY, ...);
Primary key at creation
UNIQUE (col)
Alternate key
PRIMARY KEY (col1, col2)
Composite primary key
ALTER TABLE t MODIFY id INT NOT NULL, ADD PRIMARY KEY (id), ADD UNIQUE (email);
Add keys to an existing relation
FOREIGN KEY (col) REFERENCES parent(pk_col)
Foreign key at creation
ALTER TABLE child ADD CONSTRAINT fk_name FOREIGN KEY (col) REFERENCES parent(pk_col);
Add a foreign key later
ERROR 1452 (23000): Cannot add or update a child row
Child insert violation
ERROR 1451 (23000): Cannot delete or update a parent row
Parent delete violation
SELECT COLUMN_NAME, CONSTRAINT_NAME, REFERENCED_TABLE_NAME FROM information_schema.KEY_COLUMN_USAGE WHERE TABLE_NAME='t';
See every key on a relation
ALTER TABLE child DROP FOREIGN KEY fk_name;
Remove a foreign key

Test yourself

Tap an answer to check it instantly — you'll see why it's right, and what to revise if it isn't.

0 correct · 0/12 answered
Q1

In the relational data model, the number of attributes in a relation is called its:

Q2

A relation STUDENT has 7 attributes and 10 tuples. Two more tuples are inserted and one new column is added with ALTER TABLE. Its new degree and cardinality are:

Q3

On the 10-tuple STUDENT table, what does SELECT COUNT(DISTINCT Stream) FROM STUDENT; return?

Q4

STUDENT holds marks 88.00, 91.50, 76.25, 84.00, 69.75, 79.00, 93.25, 58.50, 87.00 and 72.00. What does SELECT COUNT(*) FROM STUDENT WHERE Marks > 85; return?

Q5

SELECT Name, City, Marks FROM STUDENT WHERE Stream='Science'; returned 5 rows from the 10-tuple STUDENT. The degree and cardinality of that result are:

Q6

STUDENT has 11 tuples and HOSTEL has 3. What does SELECT COUNT(*) FROM STUDENT, HOSTEL; return?

Q7

COACH has 5 attributes and 6 tuples. After ALTER TABLE COACH ADD COLUMN Experience INT; the six existing rows will hold:

Q8

Which statement about a primary key in MySQL is TRUE?

Q9

In COACH(CoachId, CoachName, Sport, Pay, City), CoachId is unique and each sport has exactly one coach. If CoachId is chosen as the primary key, then Sport is best described as the:

Q10

HostelCode in STUDENT is a foreign key referencing HOSTEL, which contains only H1, H2 and H3. Inserting a student with HostelCode 'H9' causes MySQL to:

Q11

{AdmNo, Name} uniquely identifies every tuple of STUDENT, but AdmNo on its own is already unique. {AdmNo, Name} is therefore:

Q12

HostelCode is declared CHAR(2). An INSERT supplying 'HOSTEL-1' for it results in:

NCERT solutions & previous-year questions

Step-by-step model answers — tap a question to reveal the full solution.

NCERT questions 6

1 What is data redundancy? Explain the problems it can lead to, with an example.Need for a database

Data redundancy means storing the same fact more than once in a database. It usually arises when unrelated kinds of information are forced into a single wide table.

Consider a library that keeps member details and issue details together:

+----------+--------------+------------+----------+-------------------------+------------+
| MemberId | MemberName   | MemberCity | BookCode | BookTitle               | IssueDate  |
+----------+--------------+------------+----------+-------------------------+------------+
| M01      | Aarav Sharma | Delhi      | B101     | Wings of Fire           | 2026-07-02 |
| M01      | Aarav Sharma | Delhi      | B107     | Train to Pakistan       | 2026-07-14 |
| M02      | Diya Nair    | Kochi      | B101     | Wings of Fire           | 2026-07-05 |
| M02      | Diya Nair    | Kochi      | B120     | The God of Small Things | 2026-07-19 |
| M03      | Rohan Verma  | Jaipur     | B107     | Train to Pakistan       | 2026-07-21 |
+----------+--------------+------------+----------+-------------------------+------------+

Aarav Sharma's name and city are stored twice, purely because he borrowed two books. Redundancy wastes storage, but its serious cost is the three anomalies it makes possible.

1. Update anomaly. When Diya Nair moves to Thrissur, updating only one of her two rows produces:

+----------+------------+------------+
| MemberId | MemberName | MemberCity |
+----------+------------+------------+
| M02      | Diya Nair  | Thrissur   |
| M02      | Diya Nair  | Kochi      |
+----------+------------+------------+

One member now has two cities on record and nothing indicates which is correct. This is data inconsistency, and the system raised no error at all.

2. Deletion anomaly. Deleting Rohan Verma's only issue record removed every trace of him as a member; a query for his rows afterwards returned a count of 0. We deleted a loan and lost a person.

3. Insertion anomaly. A newly enrolled member who has not yet borrowed anything cannot be recorded at all without leaving BookCode, BookTitle and IssueDate empty, because there is no row to put him in.

Remedy: store each fact exactly once. Keep member details in a MEMBER relation and loan details in an ISSUE relation, linked by MemberId. With the city stored in one place only, it cannot disagree with itself, a member survives the deletion of his loans, and a member can be recorded before he borrows anything.

2 Differentiate between the degree and the cardinality of a relation. Illustrate with an example.Degree and cardinality
DegreeCardinality
Number of attributes (columns) in the relationNumber of tuples (rows) in the relation
Fixed by the structure of the relationDepends on how much data is currently stored
Changed by ALTER TABLE ADD or DROP COLUMNChanged by INSERT and DELETE
Found by counting the columnsFound by SELECT COUNT(*) FROM table_name;

Example. For the relation STUDENT(AdmNo, Name, City, Stream, Marks, Email, HostelCode) holding records for admission numbers 1001 to 1010, MySQL reported:

+--------+
| Degree |
+--------+
|      7 |
+--------+

+-------------+
| Cardinality |
+-------------+
|          10 |
+-------------+

Degree = 7 (seven attributes) and cardinality = 10 (ten tuples).

Note three things. First, an UPDATE changes neither number — it only alters values inside existing tuples. Second, cardinality is COUNT(*) and never COUNT(column): on a table where six of eight rows held NULL in one column, COUNT(*) returned 8 while COUNT of that column returned 2. Third, do not be misled when the two numbers coincide: a relation HOSTEL(HostelCode, HostelName, Warden) holding three hostels has degree 3 and cardinality 3, but that is a coincidence of that table, not a rule.

3 Define candidate key, primary key and alternate key. Identify each of them in a suitable relation.Keys

Candidate key: a minimal set of attributes that can uniquely identify every tuple of a relation. Minimal means no attribute can be removed from it without destroying uniqueness. A relation may have several candidate keys.

Primary key: the one candidate key the designer selects to identify tuples. It can never be NULL and never repeat — this rule is called entity integrity. A relation has exactly one primary key.

Alternate key: any candidate key that was not selected as the primary key.

Example. In STUDENT(AdmNo, Name, City, Stream, Marks, Email, HostelCode):

  • Candidate keys: {AdmNo} and {Email}. The school issues each admission number once, and one email address belongs to one person.
  • Primary key: AdmNo — short, numeric, and it never changes, whereas an email address might.
  • Alternate key: Email.

Attributes that do not qualify. Name fails outright, as this query showed:

SELECT Name, COUNT(*) AS Repeats FROM STUDENT
GROUP BY Name HAVING COUNT(*) > 1;
+-----------+---------+
| Name      | Repeats |
+-----------+---------+
| Diya Nair |       2 |
+-----------+---------+

Two students share that name. City is subtler: in this particular set of ten students all ten cities happened to be different, yet City is still not a candidate key, because nothing prevents two students from Delhi. A candidate key is a guarantee about all possible data, not an observation about the data currently present.

Superkey contrast. {AdmNo, Name} also identifies tuples uniquely, but Name is removable, so it is a superkey and not a candidate key.

Declared in SQL, the choice looks like this, and DESCRIBE then shows PRI against AdmNo and UNI against Email:

ALTER TABLE STUDENT
  MODIFY AdmNo INT NOT NULL,
  ADD PRIMARY KEY (AdmNo),
  ADD UNIQUE (Email);
4 What is a domain? Show how the domain of an attribute restricts the values it can store.Domain

A domain is the set of permitted values for an attribute. Every value appearing under an attribute, in every tuple, must be drawn from that attribute's domain. In SQL the domain is declared mainly by the data type, and can be narrowed further with constraints such as CHECK.

In STUDENT the domains were declared as:

AdmNo      INT            -- whole numbers
Name       VARCHAR(25)    -- text, at most 25 characters
Marks      DECIMAL(5,2)   -- 5 digits, 2 after the point: maximum 999.99
HostelCode CHAR(2)        -- exactly 2 characters

A domain is enforced, not merely documented. Two attempts to break it:

INSERT INTO STUDENT VALUES
(1099,'Test Kumar','Bhopal','Science','Ninety','t.k@example.in','H1');
ERROR 1366 (HY000): Incorrect decimal value: 'Ninety' for column 'Marks' at row 1
INSERT INTO STUDENT VALUES
(1098,'Test Kumar','Bhopal','Science',81.00,'t2@example.in','HOSTEL-1');
ERROR 1406 (22001): Data too long for column 'HostelCode' at row 1

Both tuples were rejected outright — the cardinality stayed at 10 afterwards. Nothing was partially written and no value was silently altered. The numeric ceiling behaves the same way: DECIMAL(5,2) took 999.99 but refused 1000.00 with ERROR 1264 (22003): Out of range value.

A data type is often a wider domain than the application really wants. Marks declared DECIMAL(5,2) would accept 250.00, which is meaningless for a percentage. Narrow it with CHECK:

Marks DECIMAL(5,2) CHECK (Marks >= 0 AND Marks <= 100)

With that in place, an out-of-range value is refused too:

ERROR 3819 (HY000): Check constraint 'marksheet_chk_1' is violated.

Note the difference between the declared domain and the values in use. SELECT DISTINCT Stream FROM STUDENT; returned only Science, Commerce and Humanities, but the declared VARCHAR(12) domain would accept a great many other strings. If only those three are ever legal, that rule must be declared explicitly.

5 What is a foreign key? How does it help maintain referential integrity? Explain with an example.Foreign key

A foreign key is an attribute (or set of attributes) in one relation whose values must appear as the primary key of another relation. The relation containing the foreign key is the referencing or child relation; the relation it points to is the referenced or parent relation.

Referential integrity is the property that no tuple ever refers to a parent tuple that does not exist. The foreign key is the constraint that enforces it.

Example. HOSTEL is the parent, with HostelCode as its primary key:

+------------+--------------+---------------+
| HostelCode | HostelName   | Warden        |
+------------+--------------+---------------+
| H1         | Tagore House | Mr R Balan    |
| H2         | Nehru House  | Ms S Kulkarni |
| H3         | Bose House   | Mr A Qureshi  |
+------------+--------------+---------------+

STUDENT is the child, and HostelCode there is declared as a foreign key:

ALTER TABLE STUDENT
  ADD CONSTRAINT fk_hostel
  FOREIGN KEY (HostelCode) REFERENCES HOSTEL(HostelCode);

What it prevents. Before the constraint existed, a student was inserted with HostelCode 'H9' and MySQL accepted it without complaint, even though no hostel H9 exists — the value fitted the CHAR(2) domain, so nothing objected. After the constraint is added, the same insert fails:

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails
(`cs12_database_concepts`.`student`, CONSTRAINT `fk_hostel`
FOREIGN KEY (`HostelCode`) REFERENCES `hostel` (`HostelCode`))

It also protects the parent. Deleting hostel H1, which four students still occupy, is refused:

ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails
(`cs12_database_concepts`.`student`, CONSTRAINT `fk_hostel`
FOREIGN KEY (`HostelCode`) REFERENCES `hostel` (`HostelCode`))

So the constraint works in both directions: 1452 blocks a child that points nowhere, 1451 blocks the removal of a parent that is still being pointed at.

Points to remember: the referenced attribute must be a primary key (or UNIQUE) in the parent; the two attributes must have compatible data types (compatible, not identical — a VARCHAR(2) child pointing at a CHAR(2) parent was accepted, while an INT child pointing at a CHAR(2) parent was refused with ERROR 3780); a foreign key value may repeat in the child, since many students share one hostel; and a foreign key may be NULL, meaning the tuple has no parent yet — a student admitted but not yet allotted a hostel was accepted with HostelCode NULL.

6 Consider the relation COACH given below. (a) Name its attributes. (b) State its degree and cardinality. (c) Identify all candidate keys, given that each sport has exactly one coach. (d) Suggest a suitable primary key and name the alternate key. (e) Explain why CoachName cannot be a key.Relational model — applied 
+---------+---------------+-----------+-------+-----------+
| CoachId | CoachName     | Sport     | Pay   | City      |
+---------+---------------+-----------+-------+-----------+
|      11 | Ravi Kumbhar  | Cricket   | 48000 | Pune      |
|      12 | Sunita Rane   | Badminton | 52000 | Nagpur    |
|      13 | Imran Khan    | Football  | 45000 | Kolkata   |
|      14 | Leela Menon   | Athletics | 61000 | Kochi     |
|      15 | Harpreet Kaur | Hockey    | 57000 | Jalandhar |
|      16 | Ravi Kumbhar  | Swimming  | 50000 | Pune      |
+---------+---------------+-----------+-------+-----------+

(a) Attributes: CoachId, CoachName, Sport, Pay, City — five in all.

(b) Degree = 5, Cardinality = 6. Five attributes, six tuples. Verified on the server, which returned 5 for the degree and 6 for the cardinality.

(c) Candidate keys. Counting distinct values across these six tuples:

SELECT COUNT(DISTINCT CoachName) AS D_Name,
       COUNT(DISTINCT Sport)     AS D_Sport,
       COUNT(DISTINCT City)      AS D_City,
       COUNT(DISTINCT CoachId)   AS D_Id
FROM COACH;
+--------+---------+--------+------+
| D_Name | D_Sport | D_City | D_Id |
+--------+---------+--------+------+
|      5 |       6 |      5 |    6 |
+--------+---------+--------+------+

Only CoachId and Sport reach 6, matching the row count. Combined with the stated rule that each sport has exactly one coach, the candidate keys are {CoachId} and {Sport}.

(d) Primary key: CoachId. It is a short numeric code, it never changes, and it stays valid even if the school later assigns a second coach to a sport. Alternate key: Sport, being the candidate key not chosen.

(e) Why CoachName cannot be a key. Its distinct count is 5 against 6 tuples — Ravi Kumbhar appears twice, at CoachId 11 and 16. A repeated value cannot identify a tuple uniquely, so CoachName fails the basic test for a key. More generally, personal names are never used as keys, because duplicates are always possible however large the table grows.

Also note: City also shows 5 distinct values and so fails as well. Even had all six cities been different, City still could not be a candidate key, since nothing stops two coaches from living in the same city.

Previous-year board questions 4

Q1 Ms. Kavita created the table COACH shown below to store the details of coaches in a sports academy. Write the degree and the cardinality of the table COACH. (1 mark) COACH CoachId | CoachName | Sport | Pay | City 11 | Ravi Kumbhar | Cricket | 48000 | Pune 12 | Sunita Rane | Badminton | 52000 | Nagpur 13 | Imran Khan | Football | 45000 | Kolkata 14 | Leela Menon | Athletics | 61000 | Kochi 15 | Harpreet Kaur | Hockey | 57000 | Jalandhar 16 | Ravi Kumbhar | Swimming | 50000 | Pune 2023 · board pattern

Degree = 5 and Cardinality = 6.

Working. Degree is the number of attributes: CoachId, CoachName, Sport, Pay, City — five columns. Cardinality is the number of tuples: the rows for CoachId 11 to 16 — six rows.

Confirmed on MySQL:

SELECT COUNT(*) AS Cardinality FROM COACH;
SELECT COUNT(*) AS Degree FROM information_schema.columns
WHERE table_schema='cs12_database_concepts' AND table_name='COACH';
+-------------+
| Cardinality |
+-------------+
|           6 |
+-------------+

+--------+
| Degree |
+--------+
|      5 |
+--------+

Examiner's note. This single mark is lost every year by candidates who write degree 6 and cardinality 5. Before writing the answer, say the definition to yourself once: degree counts columns, cardinality counts rows. Then count the column headings, which is the quicker of the two, and the other number follows.

Q2 (i) Define the terms candidate key and alternate key. (ii) In the table COACH given above, each sport has exactly one coach. Identify all the candidate keys and name the alternate key if CoachId is chosen as the primary key. (2 marks) 2024 · board pattern

(i) Definitions.

Candidate key: a minimal set of attributes that can uniquely identify each tuple of a relation. Minimal means that no attribute can be dropped from the set without losing uniqueness. A relation may have more than one candidate key.

Alternate key: a candidate key that has not been chosen as the primary key.

(ii) Applied to COACH.

Candidate keys: {CoachId} and {Sport}.

  • CoachId is issued once per coach, so its values never repeat.
  • Sport qualifies because the question states that each sport has exactly one coach, so no sport can appear twice.

If CoachId is the primary key, then Sport is the alternate key.

Supporting evidence. Counting distinct values against the 6 rows:

SELECT COUNT(DISTINCT CoachName) AS D_Name,
       COUNT(DISTINCT Sport)     AS D_Sport,
       COUNT(DISTINCT City)      AS D_City,
       COUNT(DISTINCT CoachId)   AS D_Id
FROM COACH;
+--------+---------+--------+------+
| D_Name | D_Sport | D_City | D_Id |
+--------+---------+--------+------+
|      5 |       6 |      5 |    6 |
+--------+---------+--------+------+

CoachName is disqualified because Ravi Kumbhar appears twice (CoachId 11 and 16), and City because Pune appears twice.

Marking tip. Do not stop at naming CoachId. The question says all the candidate keys, and the mark for Sport depends on quoting the rule given in the question — one coach per sport — rather than merely observing that the six sports listed happen to differ.

Q3 A school stores hostel details in HOSTEL(HostelCode, HostelName, Warden), where HostelCode is the primary key, and student details in STUDENT(AdmNo, Name, ..., HostelCode), where HostelCode is a foreign key referencing HOSTEL. HOSTEL contains only H1, H2 and H3. (i) Sunil tries to insert a student record with HostelCode 'H9'. State what will happen and justify your answer. (ii) The administrator then tries to delete the row for H1 from HOSTEL, although four students are allotted to H1. State what will happen and why. (2 marks) 2022 Term-1 · board pattern

(i) The insert will be rejected.

HostelCode in STUDENT is a foreign key, so every value stored in it must already exist as a primary key value in HOSTEL. Since 'H9' is not present in HOSTEL, accepting the row would create a dangling reference — a student pointing at a hostel that does not exist. This would break referential integrity, so the DBMS refuses the whole tuple.

The actual error returned by MySQL:

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails
(`cs12_database_concepts`.`student`, CONSTRAINT `fk_hostel`
FOREIGN KEY (`HostelCode`) REFERENCES `hostel` (`HostelCode`))

Note that 'H9' is a perfectly valid CHAR(2) value, so the domain rule is satisfied. It is the foreign key constraint alone that stops it. Before the constraint was added, the identical insert succeeded silently.

(ii) The delete will also be rejected.

Four student tuples still refer to H1. Removing that parent row would leave those four students referring to a hostel that no longer exists, once again breaking referential integrity. The DBMS therefore blocks the deletion:

ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails
(`cs12_database_concepts`.`student`, CONSTRAINT `fk_hostel`
FOREIGN KEY (`HostelCode`) REFERENCES `hostel` (`HostelCode`))

To actually delete H1, the administrator must first deal with the children — either re-allot those four students to another hostel with an UPDATE, or delete their rows — and only then delete the parent. (Declaring the foreign key with ON DELETE CASCADE would make the DBMS remove the child rows automatically, but that destroys the student records too, so it is rarely appropriate here.)

Remember the pair: 1452 = bad INSERT into the child; 1451 = blocked DELETE from the parent.

Q4 Answer the following with reference to the relation COACH(CoachId, CoachName, Sport, Pay, City), which currently has 5 attributes and 6 tuples. (i) A new column Experience is added using ALTER TABLE, and then two more records are inserted. What will the degree and cardinality of COACH be now? Justify. (ii) Give one reason why CoachName should not be made the primary key. (iii) Differentiate between a primary key and a foreign key with respect to NULL values. (3 marks) 2025 Sample Paper · board pattern

(i) New degree = 6, new cardinality = 8.

Justification: ALTER TABLE ... ADD COLUMN adds one attribute, so the degree rises from 5 to 6, while leaving the number of tuples untouched. Each INSERT adds one tuple, so two inserts raise the cardinality from 6 to 8, leaving the number of attributes untouched. The two operations affect one number each.

Verified on MySQL:

ALTER TABLE COACH ADD COLUMN Experience INT;
INSERT INTO COACH VALUES (17,'Deepak Yadav','Kabaddi',43000,'Lucknow',6);
INSERT INTO COACH VALUES (18,'Fatima Sheikh','Chess',46000,'Hyderabad',9);

SELECT COUNT(*) AS New_Cardinality FROM COACH;
SELECT COUNT(*) AS New_Degree FROM information_schema.columns
WHERE table_schema='cs12_database_concepts' AND table_name='COACH';
+-----------------+
| New_Cardinality |
+-----------------+
|               8 |
+-----------------+

+------------+
| New_Degree |
+------------+
|          6 |
+------------+

A further point worth stating: the six pre-existing coaches received NULL in the new Experience column. Adding an attribute widens the tuples that already exist; it neither creates nor deletes tuples. A useful consequence: on this table COUNT(*) returns 8 but COUNT(Experience) returns only 2, so never use COUNT(column) to report cardinality.

(ii) Why CoachName cannot be the primary key.

Its values are not unique — Ravi Kumbhar appears twice, at CoachId 11 and 16. A primary key value must never repeat, since it has to identify one tuple and one tuple only. More generally, personal names should never be chosen as keys, because two people can always share a name however carefully the data is collected.

(iii) Primary key versus foreign key, with respect to NULL.

Primary keyForeign key
Can never be NULL; declaring a column as PRIMARY KEY makes it NOT NULL automaticallyMay be NULL, meaning the tuple has no parent yet
Values must be uniqueValues may repeat, since many children can share one parent
Enforces entity integrity — every tuple is identifiableEnforces referential integrity — every reference points at a real parent

Both facts were confirmed on the server. A column declared merely as INT PRIMARY KEY showed Null = NO in DESCRIBE without NOT NULL ever being written, and an INSERT of NULL into it failed with ERROR 1048 (23000): Column 'AdmNo' cannot be null. In contrast, a student admitted but not yet allotted a hostel was accepted with HostelCode NULL.

Why the difference makes sense: a primary key answers which tuple is this?, a question that must always have an answer. A foreign key answers which parent does it belong to?, and none yet is a legitimate answer.

Stuck? Ask about this chapter. Type your question in your own words — it searches straight through Database Concepts and the Relational Model for the answer. The Ask AI button (bottom-right) stays scoped to this chapter while you're here, too.

Part of Priodemy for School

Interactive CBSE lessons, Class 8–12 — free with every school on Priodemy EduSuite. Explore more chapters and labs on the Priodemy for School hub.

Ask AI