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 in 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

Roll no 24 may not be interested in sports. Can a NULL value be assigned to that student's preference field?
Roll no 17 has given two preferences in 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?
Kabaddi was not chosen by any student. Is it possible to have this tuple in the Sports Preferences relation?

Odisha ChseTextbookSubjective· 3mImportance★★★★★

24% · 7/29 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 →

Here are the answers to your questions:

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

No, a NULL value cannot be assigned to the Preference field for Roll no 24. The school rule states that "each student must participate in a sports activity" and "each one should give only one preference". A NULL value in the Preference field would indicate that the student has not given any preference, which directly violates this mandatory participation rule.

(b) Roll no 17 has given two preferences in 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?

The property of uniqueness is violated here. The rule states that "each one should give only one preference for sports activity". Having two entries for Roll_no 17 means that student has given two preferences, which contradicts this rule.

To prevent such violations, a PRIMARY KEY constraint or a UNIQUE constraint can be applied to the Roll_no field. If Roll_no is declared as a PRIMARY KEY, the database management system will automatically enforce both uniqueness (no duplicate roll numbers) and non-nullability (no NULL roll numbers), ensuring that each student has only one entry in this table. A UNIQUE constraint on Roll_no would also prevent duplicate roll numbers.

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

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

This question explores fundamental relational database concepts: the meaning of NULL values, the Entity Integrity Constraint, and the role of Primary Keys in enforcing uniqueness and non-nullability to maintain data consistency and adhere to business rules.

Let's break down each part of the question, focusing on the underlying database principles.

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

A NULL value in a database signifies the absence of a value, meaning it is unknown, not applicable, or not yet provided.

From a purely database schema perspective, if the Preference column is not explicitly defined with a NOT NULL constraint, then assigning a NULL value to it is syntactically permissible. The database management system (DBMS) would allow the entry (24, NULL).

However, the question also states a school rule: "each student must participate in a sports activity." If a student's Preference field is NULL, it implies that their preference is unknown or they have not yet chosen an activity. This directly contradicts the school's business rule that every student must participate.

Therefore, while technically possible to store NULL if no NOT NULL constraint is applied to the Preference column, it would violate the school's operational rule. A well-designed database, intended to enforce such rules, would typically have a NOT NULL constraint on the Preference column to prevent such entries, or it would require a default value indicating "undecided" rather than NULL.

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

The table shows two entries for Roll_no 17: (17, Badminton) and (17, Football). This means that Roll_no 17 appears twice in the table.

The problem statement specifies two crucial rules: "each student must participate in a sports activity" and "each one should give only one preference for sports activity." The second rule, "each one should give only one preference," implies that for any given student (Roll_no), there should be only one associated Preference.

The property of relational DBMS violated here is uniqueness, specifically the Entity Integrity Constraint if Roll_no is intended to be the primary key. A primary key is designed to uniquely identify each record (tuple) in a table. If Roll_no is meant to identify a student's single preference, then Roll_no must be unique.

To prevent such a violation, we can use a Primary Key constraint on the Roll_no column.

PRIMARY KEY (column_name)

A Primary Key constraint ensures two things:

Uniqueness: Every value in the primary key column(s) must be unique. No two rows can have the same primary key value.
Non-nullability: No value in the primary key column(s) can be NULL.

If Roll_no were defined as the primary key, the DBMS would automatically check for uniqueness. When an attempt is made to insert the second entry for Roll_no 17 (e.g., (17, Football)), the DBMS would detect that 17 already exists as a primary key value and would reject the insertion, thereby enforcing the "only one preference" rule.

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

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…

(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 in sports. Which property of relational DBMS 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? …

🎓Unlock everything free for 14 days

Unlock everything free for 14 days