A school has a rule that each student must participate in a sports activity. So each one should give only one preference for sports activity. Suppose there are five students in a class, each having a unique roll number. The class representative has prepared a list of sports preferences as shown below. Answer the following: Table: Sports Preferences | Roll_no | Preference | |---|---| | 9 | Cricket | | 13 | Football | | 17 | Badminton | | 17 | Football | | 21 | Hockey | | 24 | NULL | | NULL | Kabaddi | a) Roll no 24 may not be interested in sports. Can a NULL value be assigned to that student’s preference field? b) Roll no 17 has given two preferences sports. Which property of relational DBMC is violated here? Can we use any constraint or key in the relational DBMS to check against such violation, if any? c) Kabaddi was not chosen by any student. Is it possible to have this tuple in the Sports Preferences relation?

A school has a rule that each student must participate in a sports activity. So each one should give only one

Exercises · Q6

A school has a rule that each student must participate in a sports activity. So each one should give only one preference for sports activity. Suppose there are five students in a class, each having a unique roll number. The class representative has prepared a list of sports preferences as shown below. Answer the following:

Table: Sports Preferences

Roll_no	Preference
9	Cricket
13	Football
17	Badminton
17	Football
21	Hockey
24	NULL
NULL	Kabaddi

a) Roll no 24 may not be interested in sports. Can a NULL value be assigned to that student’s preference field?

b) Roll no 17 has given two preferences sports. Which property of relational DBMC is violated here? Can we use any constraint or key in the relational DBMS to check against such violation, if any?

c) Kabaddi was not chosen by any student. Is it possible to have this tuple in the Sports Preferences relation?

Dnh Dd CbseNCERTSubjective· 4mImportance★★★★★

50% · 7/14 Questions

🔒 Locked · start free trial →

You're viewing a preview — the full solution, concept, methods & PYQ mapping are locked.

Start your 14-day free trial to unlock the full solution →

Concept understanding — Foreign Key Nullability

Imagine you are the registrar at a college. Every student belongs to a department — say, Physics or Economics. You keep a list of students, and next to each student’s name you write their department code. That department code is a foreign key: it points to the official list of departments. Now, a question arises: what happens if a student has not yet been assigned to any department? Do you leave that slot blank, or do you force them to pick one?

That blankness is the heart of foreign key nullability.

Foreign key nullability simply means: can a foreign key column contain a NULL value? In database terms, NULL stands for “unknown” or “not applicable.” It is not the same as zero or an empty string — it is the absence of a value. When you design a table, you decide whether the foreign key column is allowed to hold NULL or not.

If the foreign key allows NULL, then a record in the child table can exist without being linked to any parent record. In our college example, a student can be enrolled without a department assignment — the department code is left blank.
If the foreign key does not allow NULL (it is “NOT NULL”), then every record in the child table must point to a valid, existing parent record. Every student must belong to a department, no exceptions.

Why does this matter? Because it directly controls the referential integrity of your data — the rule that every foreign key value must match a primary key value in the parent table. A NULL is not a violation of that rule, because NULL simply means “no reference.” But a non-null value that does not match any parent key is a violation.

Important

A NULL foreign key does not break referential integrity. The rule only applies to non-null values. This is a common point of confusion in exams: a foreign key can be NULL unless you explicitly forbid it.

Here are the practical consequences:

When NULL is allowed: You can insert a child record before you know its parent. For example, a new employee can be added to the payroll before their manager is assigned. This gives flexibility, but it also means some records may be “orphaned” in the sense that they have no parent link. …

Start your 14-day free trial to unlock the full solution →

One student = one tuple, identified by Roll_no. So: a NULL Preference is fine (NULL means "unknown/not given"), a repeated Roll_no breaks the uniqueness property of the key and is stopped by a PRIMARY KEY constraint, and a NULL Roll_no is flatly impossible because entity integrity forbids NULL in a primary key.

In this relation the natural design is: Roll_no identifies the student (each roll number is unique), and Preference records the single sport chosen. In SQL:

CREATE TABLE SPORTS_PREFERENCES (
  Roll_no    INT PRIMARY KEY,      -- one tuple per student, never NULL
  Preference VARCHAR(20)           -- may be NULL: preference not yet given
);

a) Can Roll_no 24's Preference be NULL? — Yes.

NULL is the special marker for unknown / not applicable / not yet provided. Preference is an ordinary (non-key) attribute with no NOT NULL constraint, so the tuple (24, NULL) is perfectly valid: it records honestly that student 24 has not indicated any preference. (If the school wanted to force everyone to choose — per its rule — the designer could add NOT NULL to Preference; but as the relation stands, NULL is allowed.)

b) Roll_no 17 appears twice — what is violated, and what prevents it?

The rule broken is the key/uniqueness property of a relation: every tuple must be uniquely identifiable, and here Roll_no is meant to be the key ("each one should give only one preference"). Two tuples with Roll_no 17 mean the key value repeats, so the relation can no longer tell the two apart by its key — a violation of entity integrity/uniqueness.

Yes, a constraint prevents this: declare Roll_no the PRIMARY KEY (or at least UNIQUE). Then the DBMS itself rejects the second entry:

INSERT INTO SPORTS_PREFERENCES VALUES (17, 'Badminton');  -- accepted
INSERT INTO SPORTS_PREFERENCES VALUES (17, 'Football');   -- REJECTED: duplicate primary key 17

The second INSERT fails with a duplicate-key error — the constraint enforces "one student, one preference" automatically.

c) Can the tuple (NULL, 'Kabaddi') exist? — No. …

Start your 14-day free trial to unlock the full solution →

←Previous questionQ5 — What are the limitations of file system that can be overcome by a rela…→Next questionQ7 — In another class having 2 sections, the two respective class represent…

🎓Unlock everything free for 14 days

Unlock everything free for 14 days