Skip to content

Ch 8 · Database Concepts

Exercises

Database Concepts

Exercises · Class 12 Computer Science · NCERT 2026-27

Question 1
Give the terms for each of the following:
  1. (a) Collection of logically related records.
  2. (b) DBMS creates a file that contains description about the data stored in the database.
  3. (c) Attribute that can uniquely identify the tuples in a relation.
  4. (d) Special value stored when actual data value is unknown for an attribute.
  5. (e) An attribute which can uniquely identify tuples of the table but is not defined as the primary key.
  6. (f) Software used to create, manipulate and maintain a relational database.
Answer

(a) Database (or Table/Relation, when referring to a single collection of records). (b) Data Dictionary (metadata/system catalog). (c) Candidate Key. (d) NULL. (e) Alternate Key. (f) RDBMS (Relational Database Management System).

Question 2
Why are foreign keys allowed to have NULL values? Explain with an example.
Answer

Foreign keys are allowed to have NULL values because the referencing entity may not always have a corresponding related record at that time — for example, in an EMPLOYEE table with a DepartmentID foreign key referencing a DEPARTMENT table, a newly hired employee not yet assigned to any department can have DepartmentID = NULL, since there is genuinely no department to reference yet.

Question 3MCQ
Differentiate between:
  1. (a) Database state and database schema
  2. (b) Primary key and foreign key
  3. (c) Degree and cardinality of a relation.
Answer

(a) Database schema is the overall structural design of the database (table definitions, attributes, constraints) that remains relatively stable; database state (instance) is the actual data stored at a specific point in time, which changes frequently as records are inserted, updated, or deleted. (b) A primary key uniquely identifies each tuple within its own table; a foreign key is an attribute in one table that references the primary key of another (or the same) table, establishing a relationship between the two. (c) Degree is the number of attributes (columns) in a relation; cardinality is the number of tuples (rows) currently in the relation.

Question 4
Compared to a file system, how does a database management system avoid redundancy in data?
Answer

A DBMS avoids redundancy by centralising data storage in shared tables that multiple applications and users access together, rather than each application maintaining its own separate copy of the data (as in a file system); it uses relationships (foreign keys) to link related data across tables instead of duplicating the same information in multiple files, and applies normalisation techniques to minimise repeated data.

Question 5
What are the limitations of file systems that can be overcome by a relational DBMS?
Answer

Limitations of file systems include: data redundancy and inconsistency (same data duplicated across files, becoming inconsistent when updated in only one place); difficulty in accessing data (requires custom programs for each new query); data isolation (data scattered across differently-formatted files); lack of built-in integrity/constraint enforcement; concurrent access problems; and weak security control. A relational DBMS overcomes these through centralised data management, a query language (SQL) for flexible retrieval, built-in constraints (primary/foreign keys, NOT NULL, etc.), transaction management for concurrent access, and access-control mechanisms.

Question 6
A school requires each student to give one sports preference. For 5 students: (9,Cricket), (13,Football), (17,Badminton), (17,Football), (21,Hockey), (24,NULL), (NULL,Kabaddi).
  1. (a) Can NULL be assigned to roll no 24's preference?
  2. (b) Roll no 17 has given two preferences — which property is violated, and can a constraint prevent this?
  3. (c) Can the (NULL, Kabaddi) tuple exist in this relation?
Answer

(a) Yes — since Preference is not the primary key (assuming Roll_no is), NULL can be assigned to it, indicating the student has not yet specified a preference. (b) This violates the uniqueness/primary key constraint — if Roll_no is declared as the PRIMARY KEY, each roll number should appear only once, so two rows for roll 17 is not allowed. Declaring Roll_no as the PRIMARY KEY would enforce this and prevent such duplication. (c) No — since Roll_no is the primary key, and primary key values cannot be NULL (Entity Integrity constraint), this tuple cannot be permitted.

Question 7
Two class sections prepared separate Sports Preferences tables with different row and column orders but the same underlying (Roll_no, Sports) pairs. Are the states of both relations equivalent? Justify.
Answer

Yes, the two relation states are equivalent. In the relational model, a relation is formally defined as a set of tuples — neither the order of rows nor the order of columns has any significance to the relation's identity. Since both tables contain exactly the same set of (Roll_no, Sports) pairs, merely arranged in different row order and with columns swapped, they represent the same underlying data and are therefore equivalent relations.

Question 8
A school canteen wants a database to track items and generate bills.
  1. (a) What relation should store each item and its price (with appropriate attributes, data types, and a restriction ensuring each item/price is stored only once)?
  2. (b) Should purchase quantity be a new relation or part of the previous one — with an appropriate primary/foreign key satisfying:
    1. (i) the same bill cannot be generated for different orders,
    2. (ii) bills can only be generated for available canteen items?
  3. (c) In which relation should "calories" be stored?
Answer

(a) Create a relation ITEM(ItemName VARCHAR, Price DECIMAL), with ItemName declared as the PRIMARY KEY (or UNIQUE), ensuring each item and its price is stored only once. (b) Purchase quantity requires a new relation, e.g. BILL(BillNo INT, ItemName VARCHAR, Quantity INT), with BillNo declared as the PRIMARY KEY (satisfying restriction (i), since a primary key must be unique — no duplicate bill numbers for different orders), and ItemName declared as a FOREIGN KEY referencing ITEM(ItemName) (satisfying restriction (ii), since referential integrity ensures bills can only reference items that actually exist in the ITEM relation). (c) "Calories" should be stored in the ITEM relation, since it is an inherent property of the item itself, not of a specific purchase transaction.

Question 9
An organisation creates EMP-DEPENDENT with EMPLOYEE(AadharNumber, Name, Address, Department, EmployeeID) and DEPENDENT(EmployeeID, DependentName, Relationship).
  1. (a) Name the candidate keys of EMPLOYEE.
  2. (b) Which tables/key are needed to retrieve a particular employee's dependents?
  3. (c) What is the degree of EMPLOYEE and DEPENDENT?
Answer

(a) The candidate keys of EMPLOYEE are AadharNumber and EmployeeID, since both can uniquely identify each employee record. (b) Both the EMPLOYEE and DEPENDENT tables are needed, joined on the common EmployeeID key — filtering DEPENDENT rows where EmployeeID matches the specific employee's EmployeeID from EMPLOYEE. (c) The degree of EMPLOYEE is 5 (five attributes: AadharNumber, Name, Address, Department, EmployeeID); the degree of DEPENDENT is 3 (EmployeeID, DependentName, Relationship).

Question 10
M/s Sheetal Private Limited maintains SCHOOL_UNIFORM database with UNIFORM(UCode [Primary Key], UName [Not Null], UColor) and COST(UCode, Size [Composite Primary Key], Price [>0]). Existing UNIFORM codes are 1-6 (Shirt, Pant, Skirt, Tie, Socks, Belt).
  1. (a) Can they insert into UNIFORM:
    1. (i) (7, Handkerchief, NULL)
    2. (ii) (4, Ribbon, Red)
    3. (iii) (8, NULL, White)?
  2. (b) Can they insert into COST:
    1. (i) (7, S, 0)
    2. (ii) (9, XL, 100)?
Answer

(a)(i) Yes — UCode 7 is new (not yet used), UName is provided (satisfying NOT NULL), and UColor has no constraint so NULL is permitted. (a)(ii) No — UCode 4 already exists (assigned to "Tie"), so inserting another row with UCode 4 would violate the Primary Key uniqueness constraint. (a)(iii) No — UName is NULL, which violates the NOT NULL constraint declared on UName. (b)(i) No — UCode 7 does not exist in the UNIFORM table (only codes 1-6 exist), violating referential integrity (UCode in COST must reference an existing UNIFORM entry); additionally, Price = 0 violates the Price > 0 constraint. (b)(ii) No — UCode 9 does not exist in the UNIFORM table, again violating referential integrity, even though Price = 100 satisfies the >0 constraint.

Question 11
A multiplex maintains MOVIE(Movie_ID, MovieName, ReleaseDate) and AUDI(AudiNo, Movie_ID, Seats, ScreenType, TicketPrice), where one movie can be shown in more than one auditorium.
  1. (a) Is Movie_ID correct as the primary key of MOVIE?
  2. (b) Is AudiNo correct as the primary key of AUDI? If not, suggest an appropriate one.
  3. (c) Is there a foreign key in either relation?
Answer

(a) Yes — each distinct movie has exactly one row in the MOVIE table, regardless of how many auditoriums screen it, so Movie_ID correctly and uniquely identifies each movie. (b) No — since one movie can be shown in more than one auditorium, and (implicitly) one auditorium could also screen different movies over time, AudiNo alone cannot uniquely identify each row if multiple movies are associated with the same auditorium; the appropriate primary key should be the composite key (AudiNo, Movie_ID). (c) Yes — Movie_ID in the AUDI relation is a foreign key referencing Movie_ID in the MOVIE relation.

Question 12
For the STUDENT-PROJECT database (STUDENT: RollNo, Name, Class, Section, Registration_ID; PROJECT: ProjectNo, PName, SubmissionDate; PROJECT-ASSIGNED: Registration_ID, ProjectNo):
  1. (a) Name the primary key of each table.
  2. (b) Find the foreign key(s) in PROJECT-ASSIGNED.
  3. (c) Is there an alternate key in STUDENT? Justify.
  4. (d) Can a user assign a duplicate value to RollNo in STUDENT?
Answer

(a) Primary keys: STUDENT → RollNo; PROJECT → ProjectNo; PROJECT-ASSIGNED → Registration_ID (each registration is uniquely assigned to one project in the given data). (b) PROJECT-ASSIGNED has two foreign keys: Registration_ID (referencing STUDENT.Registration_ID) and ProjectNo (referencing PROJECT.ProjectNo). (c) Yes — Registration_ID is an alternate key in STUDENT, since it can also uniquely identify each student record (each student has a distinct Registration_ID), but RollNo has already been designated as the primary key. (d) No — since RollNo is the primary key of STUDENT, it must contain unique values for every record; duplicate RollNo values are not permitted.

Question 13
For the STUDENT-PROJECT database, can we perform the following operations?
  1. (a) Insert a student record with a missing roll number value.
  2. (b) Insert a student record with a missing registration number value.
  3. (c) Insert a project detail without a submission date.
  4. (d) Insert a record with Registration_ID "IP-101-19" and ProjectNo 206 in table PROJECT-ASSIGNED.
Answer

(a) No — RollNo is the primary key of STUDENT, and a primary key value cannot be NULL/missing (Entity Integrity constraint). (b) Yes — since Registration_ID is only an alternate key (not the declared primary key) with no explicit NOT NULL constraint mentioned, a missing Registration_ID could be permitted (though this would prevent that student from being linked via PROJECT-ASSIGNED until a Registration_ID is assigned). (c) Yes — SubmissionDate is not part of the primary key (only ProjectNo is) and no NOT NULL constraint is stated for it, so a project detail can be inserted without a submission date. (d) No — Registration_ID "IP-101-19" does not exist in the STUDENT table's Registration_ID values, and ProjectNo 206 does not exist in the PROJECT table (only 101-106 exist); inserting this row would violate referential integrity on both foreign key references.