Database Concepts

A school must keep students' enrolment details, guardian contacts and daily attendance. If every employee keeps an independent copy of the same facts, names may be misspelled, corrections may reach only one copy, and a student can appear under incompatible identities. Database design addresses this problem by deciding what facts belong together, what should be kept only once, which identifiers distinguish records, and how related records are connected.

NCERT coverage. This chapter follows Chapter 8, pp. 111–130, of the supplied NCERT Class XII Computer Science textbook. It covers the textbook's §8.1–§8.5, all 13 numbered exercises with every subpart, Activity 8.1, and all five extended scenario groups (sports preferences, canteen, employee dependents, uniform prices, movies and student projects; some are parts of the numbered exercises). Answers explain the actual tables and constraints shown in NCERT. Where a question omits a crucial constraint, the classroom assumption is stated explicitly instead of inventing a declared rule.

Curriculum correspondence. Permanent lessons: C12-DB-01 (database concepts/need), C12-DB-02 (relation, attribute, tuple, domain, degree and cardinality), and C12-DB-03 (candidate, primary, alternate and foreign keys). The seven chapter section IDs below are stable reading anchors; SQL examples are only a practical preview, not a replacement for the separate Chapter 9 SQL curriculum.

Learning outcomes#

After studying this resource, you can: explain problems with manual and file-based record keeping; distinguish a database from the software managing it; describe schema, instance, catalog, metadata and constraints; recognise relations, attributes, tuples and domains; calculate degree and cardinality; explain row/column order independence and atomic values; choose candidate/primary/alternate/composite/foreign keys; design a simple multi-table database; distinguish missing values from invalid ones; solve all 13 NCERT exercises; and check a proposed insert against key and value constraints.

Reading guide#

Section C12-CH08-S01 — From Manual Records to Organised Data#

Curriculum: C12-DB-01 · NCERT: §8.1.

1. Why a school needs systematic data management#

A teacher maintaining attendance for 50 students over 26 days makes 50 × 26 = 1,300 daily attendance marks each month. The office separately records student enrolment, birth dates and guardian contacts. Under manual record keeping, the same details may be rewritten each month; pages may be lost or damaged; totals may be miscalculated; and searching or correcting a record requires considerable effort.

In a computer, a file is a stored collection of data, such as a CSV of student details or a text document. Computerised files support copying, searching, addition, change and deletion. Yet moving manual registers into independent files does not automatically eliminate repeated or inconsistent data. A coordinated database is a further step.

2. Think in entities and facts#

An entity is something about which we record facts: STUDENT, GUARDIAN or an ATTENDANCE event. Its attributes are facts such as RollNumber, SName, GUID or AttendanceDate. A good database connects related entities using stable identifiers rather than depending on names that may repeat or change.

Worked mental example: Two children share a guardian. The guardian's address should not be copied into each child's student record. Keep the guardian's address once under a guardian identifier and refer to that identifier from both students. A correction to the guardian's address then updates one record, not two.

Section C12-CH08-S02 — File Systems: Benefits and Limitations#

Curriculum: C12-DB-01 · NCERT: §8.2 and §8.2.1.

In the original NCERT example, the office's STUDENT file keeps RollNumber, SName, SDateofBirth, GName, GPhone, GAddress. The teacher's ATTENDANCE file separately keeps AttendanceDate, RollNumber, SName, AttendanceStatus. The repeated student name illustrates an avoidable duplication; separately maintaining data makes reliable association and corrections harder.

1. The six file-system limitations in NCERT#

Redundancy versus inconsistency: Two records containing the same name are redundant even when they match. They become inconsistent when an update changes only one copy. A DBMS helps centralise facts, but a poor database design can still repeat them; using DBMS software alone does not guarantee a sound schema.

2. File system versus DBMS: fair comparison#

Caution: A DBMS has costs: hardware/software resources, maintenance, staff training, backup/recovery and system complexity. Central storage can also concentrate operational risks without resilient architecture and backups.

Section C12-CH08-S03 — DBMS and Its Essential Concepts#

Curriculum: C12-DB-01 · NCERT: §8.3–§8.3.2.

1. Database, DBMS and database system#

• Database: an organised collection of logically related data (usually tables in the relational approach).
• DBMS: software used to define, create, store, retrieve, modify, secure and manage a database.
• Database system: the broader working environment including the database, DBMS, user programs and users.
• RDBMS: a DBMS based on the relational model, such as MySQL, PostgreSQL or SQLite. The NCERT examples of DBMS include both relational and other database products; MongoDB is not an RDBMS.

Typical real-world uses from NCERT include banking, crop-loan records, inventory and order management, employee/resource management and online shopping.

2. Schema versus instance#

A database schema is the design: the names of tables, attributes, data types, relationships and constraints. It changes when designers alter the structure. A database instance is the actual content/state at a particular moment; it changes when users insert, update or delete records.

Example schema:

STUDENT(RollNumber, SName, SDateofBirth, GUID)
GUARDIAN(GUID, GName, GPhone, GAddress)
ATTENDANCE(AttendanceDate, RollNumber, AttendanceStatus)

The corresponding instance could contain six students, five guardian records and twelve attendance records. Different instances can share the same schema even if student counts or contact numbers change.

3. Metadata, catalog and data dictionary#

Metadata means data about data. The database catalog/data dictionary contains schema descriptions, constraints and other structural information used by the DBMS. For example, saying that RollNumber is an integer primary key and AttendanceStatus permits P or A describes the data rather than giving a student's attendance.

4. Constraints and valid states#

A constraint is a rule restricting stored values or relationships so that invalid data cannot enter or remain in a table. Illustrative rules:

• Primary key: a roll number uniquely identifies one student; it cannot be NULL.
• NOT NULL: a required student name must have a value.
• UNIQUE: a unique registration ID cannot be used for two students.
• CHECK: attendance must be P or A; a price must be positive.
• Foreign key: an attendance roll number must reference an existing student.

A column may have more than one relevant constraint. Constraints do not imply that every non-key attribute must have a value: a guardian may decline to provide a phone number, so GPhone can be nullable if the application permits it.

5. Queries, manipulation and database engine#

A query requests data or performs an operation against data through the DBMS. Data manipulation covers INSERT, UPDATE and DELETE (and retrieval is normally performed with SELECT). A database engine is the underlying component managing storage and operations. SQL is introduced in detail in Chapter 9; here is a small preview:

-- Read attendance for an existing student.
SELECT AttendanceDate, AttendanceStatus
FROM ATTENDANCE
WHERE RollNumber = 3;

-- Change an allowed guardian detail in its one authoritative table.
UPDATE GUARDIAN
SET GPhone = '9990000000'
WHERE GUID = 'G003';

These are illustrative statements against the example design, not literal outputs of the NCERT book. In a real system, telephone numbers are often stored as strings to preserve formatting and leading zeros; the update requires authorisation and appropriate privacy safeguards.

Section C12-CH08-S04 — The Relational Data Model#

Curriculum: C12-DB-02 · NCERT: §8.4.

A relation is a table-like structure with named attributes (columns) and tuples (rows). An RDBMS stores related facts across such tables, with foreign keys expressing permitted references. The relational approach separates the logical model (what data mean) from the details of physical storage.

1. Core relational vocabulary#

The NCERT school database contains STUDENT with four attributes, GUARDIAN with four and ATTENDANCE with three. Given the depicted snapshot: STUDENT cardinality is 6, GUARDIAN 5, and ATTENDANCE 12.

2. Degree is not cardinality#

For a hypothetical BOOK(BookID,Title,Author,Price) table with 20 books:

• Degree = 4 because there are four attributes.
• Cardinality = 20 because there are twenty rows.

Adding one new book changes the instance cardinality to 21 without altering its degree or schema. Adding a new column such as Publisher changes the schema degree to 5, even if the table still has twenty rows.

3. Meaning of a domain#

A domain is the set of admissible atomic values under the attribute definition and its constraints. An integer column may have an integer data type, but a CHECK (Age BETWEEN 5 AND 18) narrows its permitted values to the intended school ages. The value NULL requires separate treatment: it represents unavailable or inapplicable information, not a number belonging to the normal age domain.

Section C12-CH08-S05 — Properties of Relations and NULL#

Curriculum: C12-DB-02 · NCERT: §8.4.1.

The textbook groups a relation's properties into rules about attributes, tuples and individual values:

1. Attributes: each attribute name is distinct; rearranging attribute columns does not alter the underlying relationship when you keep their labels/meaning.
2. Tuples: identical full tuples are disallowed by the pure relational model; the order in which rows are displayed is not semantically significant.
3. Values: values follow their attribute's domain; each field is atomic at the chosen level of modelling; a field is not a list of independent values; NULL represents unknown/not-applicable information where permitted.

1. Row/column order is not identity#

If section 1 prints (Roll_no, Sports) ordered by roll number, while section 2 prints (Sports, Roll_no) in alphabetical order, the relations may still represent the same set of facts. Match attributes by name and rows by their attribute–value pairs, not display position.

In SQL results, presentation order is not guaranteed without ORDER BY. Practical SQL tables can contain duplicate rows in the absence of key constraints; the textbook's distinct-tuples property describes the formal relational model, while SQL implementations may use bag-like query results. For school-level answers, adopt the relational rule and identify suitable keys.

2. Atomicity versus everyday strings#

A phone number in one field is treated as one contact value in this model. Storing "12345, 67890" as two independent phone numbers in the same field would violate the intended one-value-per-attribute design. If multiple phone numbers are required, create a related PHONE table with one number per row, not many values packed into one cell.

3. NULL is not zero, empty string or the word 'NULL'#

NULL means not known or not applicable, subject to column constraints. Examples: guardian contact not supplied, optional submission date not yet determined. 0 is a known numeric value; '' is an empty string; 'NULL' is ordinary text. A primary key must never be NULL, because each record requires an identity. A foreign key may be NULL if the relationship is optional and the FK column is not otherwise NOT NULL (or part of a primary key).

Example: STUDENT.GUID might be nullable when a guardian has not yet been identified under a deliberately permissive enrolment process. However, STUDENT.RollNumber must be populated when it is the primary key. These are policy choices; neither NULL nor optional linkage should be interpreted as a recommendation to omit required contact or safety information.

Section C12-CH08-S06 — Keys and Relationships#

Curriculum: C12-DB-03 · NCERT: §8.5–§8.5.4.

1. Choosing a key#

Minimality matters: {RollNumber,SName} can identify students but is not a candidate key if RollNumber alone is sufficient. Candidate keys may comprise multiple columns; composite does not mean two arbitrary columns concatenated into a string.

2. The school database links#

GUARDIAN [GUID PK]
      ^
      | STUDENT.GUID FK (guardian reference)
STUDENT [RollNumber PK]
      ^
      | ATTENDANCE.RollNumber FK
ATTENDANCE [AttendanceDate + RollNumber composite PK]

Each student may have multiple attendance rows, one for each date. Therefore, RollNumber alone cannot identify an attendance record; AttendanceDate alone cannot either. The pair identifies the daily attendance record under the rule at most one status per student per date.

The GUARDIAN reference may be shared by siblings. Keeping one guardian record avoids repeated addresses and preserves linkage. A foreign key prevents references to nonexistent parent values when enforced. It does not automatically decide what should happen to child records when a parent is deleted; policies such as RESTRICT, SET NULL and CASCADE must be designed explicitly.

3. A runnable miniature schema (SQL preview)#

The following demonstration is written using broadly standard SQL supported by SQLite; the full SQL curriculum is taught in Chapter 9.

PRAGMA foreign_keys = ON;

CREATE TABLE GUARDIAN (
    GUID TEXT PRIMARY KEY,
    GName TEXT NOT NULL,
    GPhone TEXT,
    GAddress TEXT
);

CREATE TABLE STUDENT (
    RollNumber INTEGER PRIMARY KEY,
    SName TEXT NOT NULL,
    SDateofBirth TEXT,
    GUID TEXT,
    FOREIGN KEY (GUID) REFERENCES GUARDIAN(GUID)
);

CREATE TABLE ATTENDANCE (
    AttendanceDate TEXT NOT NULL,
    RollNumber INTEGER NOT NULL,
    AttendanceStatus TEXT NOT NULL
        CHECK (AttendanceStatus IN ('P', 'A')),
    PRIMARY KEY (AttendanceDate, RollNumber),
    FOREIGN KEY (RollNumber) REFERENCES STUDENT(RollNumber)
);

4. Valid and invalid data changes#

INSERT INTO GUARDIAN VALUES ('G1', 'Example Guardian', NULL, NULL);
INSERT INTO STUDENT VALUES (1, 'Example Student', '2010-05-15', 'G1');
INSERT INTO ATTENDANCE VALUES ('2026-08-01', 1, 'P');

-- Any of these would fail with the appropriate constraints enforced:
-- duplicate attendance for ('2026-08-01', 1)
-- ATTENDANCE row for RollNumber 999 when STUDENT 999 does not exist
-- ATTENDANCE status 'X' rather than P or A
-- STUDENT row with a NULL primary-key value (engine-specific caveat below)

Engine nuance: In SQLite, INTEGER PRIMARY KEY is also an automatically assigned row identifier and inserting NULL may generate a new integer; this is not a general rule for all relational DBMS products. To demonstrate unequivocal "missing primary-key is invalid" behaviour across engines, supply the key explicitly and/or use a composite or non-generated required key. The NCERT relational rule remains: every existing tuple must have an identifiable non-NULL primary key.

5. Stable keys are usually better keys#

A name may be shared, corrected or changed; a phone number may change or be absent. A designed guardian ID (GUID) remains a more robust primary key than either. For the textbook's GUARDIAN example, NCERT treats GPhone as another candidate key under an assumption of unique, present phone numbers; if a real system permits missing/shared phone numbers, it is not a valid candidate key unless further constraints ensure uniqueness and non-NULL values.

Section C12-CH08-S07 — Designing and Checking Databases#

Curriculum: C12-DB-01–03 · NCERT: school case plus application exercises.

1. Four questions to ask before creating a table#

1. What does one row represent? One student, one product, one receipt, one purchased item, or one daily attendance event?
2. What identifies each row? Choose a minimal, stable, enforceable key.
3. Which facts can change independently? Separate them into appropriately related tables to avoid update anomalies.
4. Which rows are allowed? Specify not-null, uniqueness, positive-price, valid-status and referential constraints.

2. Example: orders should not duplicate product prices#

An item catalogue stores one price per product. An order-line table stores which item and how many units, not a second current catalogue price. If historic billing is needed, an immutable PriceAtSale value can be stored per order line so old invoices remain correct after catalogue prices change; it is a deliberately distinct historical fact, not accidental redundancy.

ITEM(ItemID PK, ItemName UNIQUE NOT NULL, CurrentPrice > 0, Calories)
BILL(BillID PK, BillDate)
BILL_LINE(BillID FK, LineNo, ItemID FK, Quantity > 0, PriceAtSale >= 0,
          PRIMARY KEY(BillID, LineNo))

BILL_LINE lets a single bill contain several items. BillID identifies a bill, while (BillID,LineNo) identifies an individual line. If each bill may contain an item at most once, (BillID,ItemID) could instead be the composite primary key; that extra rule must be stated. This is a robust three-table solution to NCERT Exercise 8, not a claim that NCERT mandates a particular bill format.

3. Example: one project per student versus many projects#

If each student has exactly one project, a PROJECT_ASSIGNED(Registration_ID PRIMARY KEY, ProjectNo FOREIGN KEY) table suffices. If students may undertake several projects, choose a composite primary key (Registration_ID,ProjectNo) so each student–project pair occurs at most once. Either design needs foreign keys to STUDENT and PROJECT; the question's sample rows do not by themselves establish which business rule should apply to every future row.

4. A short constraint-testing checklist#

When asked whether a tuple can be inserted, examine all applicable rules in order: (a) attribute count/types; (b) non-null requirements; (c) uniqueness and primary keys; (d) positive-price or other checks; (e) foreign-key presence in the parent; and (f) any business rule such as exactly one preference per student. An attempted insert may fail for more than one reason.

NCERT Activity 8.1 — Complete Worked Response#

Textbook task: Visit shops that keep manual records and identify difficulties in their record-keeping methods.

This is an observational activity; the following is a model answer based on hypothetical shop observations, not a claim that actual shops were visited.

Conclusion: The main obstacles are slow search, repeated entry, incomplete updates, human error, physical damage and difficult access management. Any database conversion should be supported by backups, correct permissions and carefully checked legacy-data migration. For a real submission, replace the hypothetical examples with a shop's consented, non-sensitive observations rather than inventing an interview.

NCERT Chapter 8 — Complete Exercise Solutions (Questions 1–13)#

The solutions below retain NCERT's question numbers and subpart structure. Explanations distinguish the relational-model rule from any additional database constraints that must actually be defined. An example table's current rows alone do not prove that an attribute will remain unique, non-null or referenced in all future states.

NCERT Exercise 1 — Identify the database terms (a–f)#

(a) Collection of logically related records. File. In file-based terminology, a file contains logically related records (a database, by comparison, organises related collections/tables). An individual relational table/relation also holds related tuples; here the expected chapter term is file.

(b) A DBMS-created file holding descriptions of stored data. Database catalog or data dictionary; its descriptions are called metadata. The catalog contains schema, data types and constraints, not the student marks themselves.

(c) Attribute that can uniquely identify tuples. Candidate key, provided the identifying attribute is minimal and non-NULL; if chosen as the main identifier, it becomes the primary key. A unique identifier alone may form a candidate key.

(d) Special value when an attribute's actual value is unknown. NULL. This is not zero, '' or the text 'NULL'.

(e) Identifying attribute not chosen as primary key. Alternate key, if it is another candidate key.

(f) Software for creating, manipulating and maintaining a relational database. Relational Database Management System (RDBMS); examples include MySQL, PostgreSQL and SQLite.

Self-check: Do not answer “foreign key” for (e): a foreign key creates a reference, whereas an alternate key is still an alternative identifying key of the same table.

NCERT Exercise 2 — Why can foreign keys be NULL?#

A foreign key may be NULL when its relationship is optional and the column is not declared NOT NULL and is not a component of the primary key. NULL then represents no known/assigned parent reference, not a reference to some fictitious parent row.

Example: A STUDENT(RollNumber PRIMARY KEY, SName, GUID FOREIGN KEY) entry could temporarily have GUID = NULL if the school's data-collection rules allow enrolment before guardian information is confirmed. A non-NULL GUID must match an existing guardian's primary/candidate key when referential integrity is enforced. Conversely, ATTENDANCE.RollNumber in a design where every attendance event must belong to a student should be declared NOT NULL even though it is a foreign key.

Important qualification: It is incorrect to say that every foreign key must allow NULL; a database designer may explicitly require it using NOT NULL. Foreign keys participating in primary keys cannot be NULL.

NCERT Exercise 3 — Differentiate (a–c)#

3(a). Database state/instance versus database schema#

3(b). Primary key versus foreign key#

3(c). Degree versus cardinality#

Degree is the number of attributes/columns. Cardinality is the number of tuples/rows in the current relation state. NCERT's GUARDIAN sample has four attributes and five records, so degree 4; cardinality 5. Adding another guardian changes cardinality to six without changing degree.

NCERT Exercise 4 — How does a DBMS avoid redundancy compared with files?#

Consider separate file-system records:

STUDENT file:    RollNumber, SName, DateOfBirth, GName, GPhone, GAddress
ATTENDANCE file: AttendanceDate, RollNumber, SName, Status

Student names and shared guardian details are written repeatedly. A better relational design separates facts:

GUARDIAN(GUID PK, GName, GPhone, GAddress)
STUDENT(RollNumber PK, SName, DateOfBirth, GUID FK)
ATTENDANCE(AttendanceDate, RollNumber FK, Status,
           PRIMARY KEY(AttendanceDate, RollNumber))

Reasoning: SName is stored only with the student's RollNumber, not in every attendance record. One guardian's name/address is stored once under GUID; siblings reference that same guardian. Querying across linked tables retrieves the combined view when needed. If a guardian's phone changes, one GUARDIAN record is updated. This reduces redundancy, update anomalies, potential inconsistency and unnecessary repeated storage. It does not claim that using a DBMS automatically makes every possible schema redundancy-free; proper relational design is required.

NCERT Exercise 5 — File-system limitations overcome by an RDBMS#

Six textbook limitations and the corresponding database mechanisms:

1. Difficult data access: a query language can filter and combine related data instead of requiring new custom parsers for each report.
2. Data redundancy: decomposition into STUDENT, GUARDIAN and ATTENDANCE tables stores a fact in its appropriate relation.
3. Data inconsistency: a single authoritative record and validation constraints prevent divergent duplicated facts.
4. Data isolation: primary–foreign key relationships let data from related sources be retrieved together.
5. Data dependence: the database catalog and abstraction reduce exposure to physical file layout; schema changes may still require application changes.
6. Controlled sharing: users and applications can receive limited permissions, such as reading attendance without permission to edit it.

Balanced conclusion: An RDBMS improves integrity and multi-user management but introduces costs and administrative complexity. Backups, access policy and sound design remain necessary.

NCERT Exercise 6 — Sports Preferences: NULL and duplicate choices (a–c)#

Given NCERT table:

Stated school rule: Every student must participate and give exactly one sports preference. A student is identified by unique Roll_no.

(a) May Roll_no 24 have NULL preference? No, under the stated business rule. A missing preference contradicts mandatory participation/selection. Define Preference NOT NULL, and if a specific accepted list exists, add a suitable CHECK or reference to a SPORTS table. General SQL fact: NULL could be stored in a nullable non-key column if this rule were not enforced; the rule is why it is invalid here.

(b) May Roll_no 17 have two preferences? What is violated, and how to prevent it? No. Two rows share Roll_no = 17 although each student is allowed one choice. This violates the intended uniqueness/one-student-one-preference rule (and the identifying-key property when Roll_no is primary). Declare Roll_no PRIMARY KEY (which also prohibits NULL); its uniqueness rejects the second row. Preference need not be unique because several students may choose Cricket.

(c) May (NULL, Kabaddi) be a tuple in the Sports Preferences relation? No, when Roll_no is the required primary key. The row has no student identity and cannot satisfy the rule that each preference belongs to one enrolled student. This does not mean Kabaddi itself is forbidden: (25, Kabaddi) is valid if student 25 exists and has no previous selection. One could list Kabaddi without a student selection in a separate SPORTS(SportName PRIMARY KEY) catalogue.

A suitable design:

CREATE TABLE SPORTS_PREFERENCE (
    Roll_no INTEGER PRIMARY KEY,
    Preference TEXT NOT NULL
);

If there is a master STUDENT table, Roll_no should also reference STUDENT(RollNumber), and the school should validate that every enrolled student has a preference; a primary key alone does not enforce that every STUDENT has a corresponding preference row.

NCERT Exercise 7 — Are the two Sports Preferences relation states equivalent?#

Section 1 lists (Roll_no, Sports):

9 Cricket; 13 Football; 17 Badminton; 21 Hockey; 24 Cricket

Section 2 lists (Sports, Roll_no) in alphabetical order:

Badminton 17; Cricket 9; Cricket 24; Football 13; Hockey 21

Yes, they are equivalent as relational states when the columns are matched by their names. Each consists of the same five {Roll_no, Sports} assignments. The table with roll numbers first and the table with sports first differ only in attribute order; the tuple order also differs. In the relational model, neither column order nor row order changes the underlying set of attribute–value pairs. The degree is 2, cardinality 5, and Roll_no is unique in these snapshots.

Exam trap: Two sports values are both Cricket; that is allowed because Sports is not a unique identifier. Row duplication means an entire tuple repeats, not merely one attribute value.

NCERT Exercise 8 — Design the school-canteen database (a–c)#

(a) Store each item and its price exactly once. Define an ITEM relation. A clear design is:

ItemID is preferable to a changing name as a primary key. The UNIQUE rule on ItemName is a design choice appropriate if names must not repeat; it should be adapted if variants legitimately share a label. Why: the ITEM table is the single catalogue source of item price.

(b) Store purchased quantity in a new relation, not ITEM. Quantity depends on a purchase, not on the item definition. To guarantee one bill per distinct order and allow multiple line items, use:

BILL(BillID PRIMARY KEY, BillDate)
BILL_LINE(BillID, LineNo, ItemID, Quantity, PriceAtSale,
          PRIMARY KEY(BillID, LineNo),
          FOREIGN KEY(BillID) REFERENCES BILL(BillID),
          FOREIGN KEY(ItemID) REFERENCES ITEM(ItemID))

Quantity > 0 is enforced by CHECK. BillID uniquely identifies the bill header, so different orders cannot accidentally share a bill identifier (assuming each order has exactly one bill and unique BillID generation). The line-table ItemID foreign key ensures that billing lines reference existing catalogue items, not arbitrary names. If each item can occur only once per bill, an alternate composite key (BillID,ItemID) may be used. The bill header and lines separate one purchase transaction from its multiple purchased products.

Why not a single ITEM table with Quantity? It would confuse stock quantity or purchase quantity with static item details. A single item appears on many bills with different purchased quantities.

Illustrative SQL schema (SQLite-compatible):

CREATE TABLE ITEM (
    ItemID INTEGER PRIMARY KEY,
    ItemName TEXT NOT NULL UNIQUE,
    Price NUMERIC NOT NULL CHECK (Price > 0),
    Calories INTEGER CHECK (Calories IS NULL OR Calories >= 0)
);
CREATE TABLE BILL (
    BillID INTEGER PRIMARY KEY,
    BillDate TEXT NOT NULL
);
CREATE TABLE BILL_LINE (
    BillID INTEGER NOT NULL,
    LineNo INTEGER NOT NULL,
    ItemID INTEGER NOT NULL,
    Quantity INTEGER NOT NULL CHECK (Quantity > 0),
    PriceAtSale NUMERIC NOT NULL CHECK (PriceAtSale >= 0),
    PRIMARY KEY (BillID, LineNo),
    FOREIGN KEY (BillID) REFERENCES BILL(BillID),
    FOREIGN KEY (ItemID) REFERENCES ITEM(ItemID)
);

For money in a production system, prefer fixed-precision monetary types where supported (or integer minor units) with documented rounding; SQLite NUMERIC alone is not a guarantee of exact decimal arithmetic.

(c) Where should Calories be stored? In ITEM, because calories per standard serving are a property of the catalogue item, not of the bill. To find calories for a purchased quantity, multiply per-item calories by the count. If an item has several serving sizes, model those as separate variants or servings; the calorie value is relative to the defined serving.

NCERT Exercise 9 — EMPLOYEE and DEPENDENT relations (a–c)#

Given schemas:

EMPLOYEE(AadharNumber, Name, Address, Department, EmployeeID)
DEPENDENT(EmployeeID, DependentName, Relationship)

(a) Candidate keys of EMPLOYEE. EmployeeID and AadharNumber are plausible candidate keys if each is guaranteed unique and present. Typically EmployeeID is selected as primary key and AadharNumber may serve as alternate key if collected and constrained. The textbook question expects both. Name, Address and Department cannot identify employees uniquely.

(b) Which tables/key retrieve a given employee's dependents? Use EMPLOYEE and DEPENDENT, linked through the common EmployeeID attribute: it is the primary key of EMPLOYEE and a foreign key in DEPENDENT. First identify the employee by EmployeeID (or find EmployeeID via another candidate key), then match all dependent rows whose EmployeeID equals it. A separate dependent key or composite key should also be designed if needed to distinguish dependents; EmployeeID alone is not unique in DEPENDENT because an employee can have multiple dependents.

(c) Degree of each relation. EMPLOYEE has 5 attributes, so degree 5. DEPENDENT has 3 attributes, so degree 3. Cardinality cannot be inferred because no employee/dependent rows are supplied.

Privacy note: Aadhaar is highly sensitive personal information. The school exercise tests candidate keys, not a recommendation to collect or display Aadhaar in every database. For practical applications, use the least necessary identifying information and protect any legally required identifier.

NCERT Exercise 10 — UNIFORM and COST insert constraints (a–b)#

The figure supplies these properties:

• UNIFORM(UCode PRIMARY KEY, UName NOT NULL, UColor nullable) with existing UCode 1–6.
• COST(UCode, Size, Price) with composite primary key (UCode,Size) and Price > 0.
• NCERT's COST sample contains sizes/prices for UCode 1–6. The printed constraint list does not explicitly label COST.UCode as a FOREIGN KEY, though a complete valid uniform database should declare that relationship.

(a)(i) Insert (7, Handkerchief, NULL) into UNIFORM? — Yes under the shown constraints. UCode 7 is new, UName is non-NULL, and no NOT NULL restriction is specified for UColor. Hence UColor may be NULL. A real catalogue may adopt extra rules, but they are not printed here.

(a)(ii) Insert (4, Ribbon, Red)? — No. UCode 4 already identifies Tie. Reusing UCode 4 violates the primary-key uniqueness constraint even though the other fields differ.

(a)(iii) Insert (8, NULL, White)? — No. Although UCode 8 is available, UName is declared NOT NULL.

(b)(i) Insert (7, S, 0) into COST? — No. Price = 0 violates the explicitly shown Price > 0 rule. Under a recommended foreign key to UNIFORM it would additionally require that UCode 7 existed in the parent table at insertion time; that requirement is not explicitly printed in COST's constraints. Do not rely only on the possible parent reference to reject a zero-priced row.

(b)(ii) Insert (9, XL, 100) into COST? Under only the printed COST rules, the tuple passes: (9,XL) is not in the supplied COST state and Price = 100 > 0. Under the logically intended relational design with COST.UCode REFERENCES UNIFORM(UCode), reject it because UNIFORM has no UCode 9. This is a source specification gap, not a reason to silently assume an undeclared foreign key. A complete database should add the foreign-key constraint.

Extra note: If the question assumes the intended COST→UNIFORM relationship without explicitly naming it, the expected classroom answer for (b)(ii) is “not allowed because uniform item 9 is not defined.” Present both cases to distinguish conceptual referential integrity from what a schema actually enforces.

NCERT Exercise 11 — MOVIE and AUDI keys (a–c)#

Given:

MOVIE(Movie_ID, MovieName, ReleaseDate)
AUDI(AudiNo, Movie_ID, Seats, ScreenType, TicketPrice)

(a) Can Movie_ID be primary key in MOVIE? — Yes. Each Movie_ID can identify one movie, while a movie can be shown in several auditoriums. MovieName and ReleaseDate need not be unique.

(b) Can AudiNo be primary key in AUDI? — Yes under the textbook's current-auditorium model. Each auditorium has a distinct number within the multiplex, so one row can describe each auditorium. Movie_ID cannot be a key in AUDI because the same movie may be assigned to multiple auditoriums. Extension: For a database of many showtimes per auditorium, AudiNo by itself would no longer identify each screening; add a SHOW table keyed by ShowID or (AudiNo,StartTime) with suitable uniqueness rules.

(c) Is there a foreign key? — Yes. AUDI.Movie_ID references MOVIE.Movie_ID, expressing which movie is being shown in that auditorium. The reference may be made nullable if an auditorium can temporarily have no assigned movie, subject to the chosen design.

NCERT Exercise 12 — STUDENT-PROJECT database (a–d)#

Given sample:

STUDENT(RollNo, Name, Class, Section, Registration_ID)
PROJECT(ProjectNo, PName, SubmissionDate)
PROJECT_ASSIGNED(Registration_ID, ProjectNo)

Five STUDENT rows have distinct RollNo and Registration_ID; six PROJECT rows have distinct ProjectNo; five PROJECT_ASSIGNED rows each pair a known student registration with an existing project number.

(a) Primary key of each table. STUDENT: RollNo is a valid choice (unique roll numbers in the given school schema). PROJECT: ProjectNo. PROJECT_ASSIGNED: Registration_ID if the business rule is one project per student, as the given rows suggest. If multiple projects per student are permitted, use the composite primary key (Registration_ID,ProjectNo) to prevent duplicate assignments. The book does not explicitly state this future-cardinality rule; select the intended one and declare it rather than treating current sample uniqueness as a permanent guarantee.

(b) Foreign keys in PROJECT_ASSIGNED. Registration_ID should reference STUDENT.Registration_ID (which must be UNIQUE/candidate to serve as a valid target); ProjectNo should reference PROJECT.ProjectNo. The two links enforce that each assignment names an existing student and an existing project.

(c) Alternate key in STUDENT? Registration_ID can be an alternate key if RollNo is primary and registration IDs are guaranteed to remain unique and non-NULL. The sample's five Registration_ID values are distinct, so this is a sensible intended design. If instead Registration_ID were chosen as primary, RollNo could be alternate, assuming uniqueness. The present sample alone is insufficient to enforce uniqueness for future rows; specify UNIQUE NOT NULL.

(d) Can RollNo be duplicated? No when it is defined as the primary key or another unique candidate key. Two students cannot share the same chosen identifying roll number in the intended schema. A table with no declared uniqueness could physically accept duplicates, but would not satisfy this intended relational design.

NCERT Exercise 13 — Are these STUDENT-PROJECT operations allowed? (a–d)#

The answer depends on which constraints from Question 12 are declared. Here is a consistent school design: STUDENT.RollNo PRIMARY KEY; STUDENT.Registration_ID UNIQUE NOT NULL; PROJECT.ProjectNo PRIMARY KEY; PROJECT.SubmissionDate nullable unless due date is mandatory; and both PROJECT_ASSIGNED foreign keys enforced.

(a) Insert a student without RollNo? — No under RollNo PRIMARY KEY. A student must have an identifying primary-key value. (A DBMS with auto-generated integer identifiers may generate an ID, but then the stored row still receives a valid key; the classroom question assumes missing/non-generated RollNo.)

(b) Insert a student without Registration_ID? — No in the intended student-project design where Registration_ID is required to identify and link assignments (UNIQUE NOT NULL). Strict schema qualification: If the only declared primary key were RollNo and Registration_ID were an unconstrained optional field, SQL could permit NULL. The textbook does not print a full CREATE TABLE statement, so NOT NULL is an explicit design assumption in the expected no-answer.

(c) Insert a PROJECT without SubmissionDate? — Yes if SubmissionDate is nullable, since the primary key is ProjectNo and a missing date can mean not yet assigned/known. If the organisation defines SubmissionDate NOT NULL, then reject the omission. NCERT supplies no such mandatory-date constraint.

(d) Insert (IP-101-19, 206) into PROJECT_ASSIGNED? — No under referential integrity. The sample STUDENT rows do not contain Registration_ID IP-101-19, and the sample PROJECT rows do not contain ProjectNo 206; this attempted assignment refers to two nonexistent parent records. First create the corresponding valid student and project records (or use existing IDs), then add a non-duplicate assignment.

Exercise-completion cross-check: All 13 numbered exercises, with Q1(a–f), Q3(a–c), Q6(a–c), Q8(a–c), Q9(a–c), Q10(a–b with all five proposed tuples), Q11(a–c), Q12(a–d), and Q13(a–d), are answered above.

Original Practice — 30 Questions with Explained Answers#

These are original questions, separate from the NCERT exercises. Each answer includes a reason, calculation or schema rule so that students can check why a choice is valid.

Part A — Conceptual MCQs (1–10)#

Practice 1 — A database schema describes which of these?#

A. Current students' marks · B. Table names, data types and constraints · C. Yesterday's attendance records · D. One student's phone number

Answer: B. A schema is the database's logical design. Choices A, C and D are values belonging to an instance.

Practice 2 — Which is the best example of metadata?#

A. John · B. SName TEXT NOT NULL · C. Present · D. 89 marks

Answer: B. The type and restriction for SName describe how a field is stored. The other choices are actual data values.

Practice 3 — Which item changes when a row is inserted without a schema alteration?#

A. Degree · B. Attribute names · C. Cardinality · D. Primary-key definition

Answer: C. Insertion normally increases the number of rows (cardinality) by one; degree and schema remain fixed.

Practice 4 — A stable but unused candidate key is called what?#

A. Foreign key · B. Alternate key · C. Tuple · D. Domain

Answer: B. An alternate key is a candidate key not chosen as the primary key. An FK instead references an identifier in another relation.

Practice 5 — A table stores one row per student per attendance date. Which key is suitable?#

A. Date alone · B. RollNumber alone · C. (Date,RollNumber) · D. Status

Answer: C. Neither student nor date alone is unique; the pair is unique if each student has at most one record per date.

Practice 6 — A guardian can have several children. What may repeat in STUDENT?#

A. Its primary RollNumber · B. The guardian foreign key GUID · C. An alternate candidate key · D. The complete chosen composite primary key

Answer: B. Several students can legitimately reference one guardian. That is a one-to-many relationship, not a key violation.

Practice 7 — What does an optional foreign-key NULL mean?#

A. The parent row has key 0 · B. It is a text value · C. No known/applicable reference yet · D. The row is necessarily invalid

Answer: C. It indicates an absent reference where permitted. A non-NULL value must refer to an existing parent if the FK is enforced.

Practice 8 — Which formal relational property remains unchanged after shuffling displayed rows?#

A. The stored tuples' meaning · B. The first row as an identifier · C. The column data types · D. Only the table name

Answer: A. Order of tuples has no inherent relational meaning. Column data types also stay the same, but the main tested property is unchanged relation state.

Practice 9 — Which rule prevents a negative or zero unit price?#

A. FOREIGN KEY alone · B. CHECK Price > 0 · C. DEFAULT · D. ORDER BY

Answer: B. CHECK imposes an allowed-value condition. Foreign keys check references, not positivity.

Practice 10 — A SQL table allows duplicate rows without a declared key. Which conclusion is sound?#

A. SQL always enforces formal relation uniqueness · B. The schema may allow duplicates despite textbook relational ideals · C. Every column is primary · D. Two rows with the same name are always duplicates

Answer: B. The pure model uses sets of distinct tuples, while practical SQL tables need suitable key/uniqueness constraints. Same-name rows are not identical tuples when IDs differ.

Part B — Trace Relations and Constraints (11–18)#

Practice 11 — Degree and cardinality of a three-column timetable#

TIMETABLE(Day,Period,Subject) contains rows (Mon,1,Math), (Mon,2,English), (Tue,1,Physics), (Tue,2,Math).

Answer: Degree 3 (Day, Period, Subject) and cardinality 4 (four rows). Subject appearing twice does not make the tuples duplicate.

Practice 12 — What changes after an insertion and an extra attribute?#

A table starts with degree 4 and cardinality 9. One row is inserted and an extra nullable attribute is added. Find the new values.

Answer: Degree 5 and cardinality 10. The insertion adds a row; adding a column changes the structure. Both actions affect different dimensions.

Practice 13 — Check employee primary-key duplicates#

EMPLOYEE(EID PRIMARY KEY,Name) has (1,Ravi) and (2,Ravi). Can (2,Asha) and (3,Ravi) be inserted?

Answer: (2,Asha) fails because EID 2 is already in use. (3,Ravi) passes because EID 3 is new and the Name column is not marked UNIQUE. A person's name need not be unique.

Practice 14 — Required field versus optional field#

BOOK(BookID PRIMARY KEY,Title NOT NULL,Subtitle) contains no row for BookID 8. Is (8,Title=NULL,Subtitle='Intro') valid? Is (8,'Physics',NULL) valid?

Answer: The first is invalid because Title cannot be NULL. The second is valid on the given rules because BookID 8 is new and Subtitle may be NULL.

Practice 15 — Foreign-key reference check#

CATEGORY(CategoryID PRIMARY KEY) has keys 10 and 20. PRODUCT(ProductID PK,CategoryID FK nullable) is empty. Consider (1,10), (2,99) and (3,NULL).

Answer: (1,10) is valid, because 10 exists. (2,99) is invalid, because parent 99 is absent. (3,NULL) is valid if the FK is genuinely nullable. Insertion order does not make an absent parent automatically valid.

Practice 16 — Attendance composite-key collision#

ATTENDANCE(Date,RollNo,Status) uses (Date,RollNo) as PK. Rows (01-08,4,P) and (02-08,4,A) already exist. May (01-08,5,P) and (01-08,4,A) be added?

Answer: (01-08,5,P) is valid: new pair (01-08,5). (01-08,4,A) is invalid: existing key pair (01-08,4) repeats, despite changed status. Update the existing status if that change is legitimate.

Practice 17 — Equivalent relation order#

Two lists contain {(1,Math),(2,English)} and {(English,2),(Math,1)} but the second uses columns (Subject,RollNo). Are the states the same?

Answer: Yes, when paired by named attributes. (1,Math) is the same fact as (Math,1) displayed under reversed headings. Both contain two tuples, regardless of listing order.

Practice 18 — Atomicity versus combined contact values#

CONTACT(PersonID,Phone) stores (7,'99901,99902'). A policy requires each Phone field to hold one distinct telephone number. Suggest a design and key.

Answer: Keep PERSON(PersonID PRIMARY KEY,...) and make PHONE(PersonID,Phone, PRIMARY KEY(PersonID,Phone), FOREIGN KEY(PersonID) REFERENCES PERSON(PersonID)). Store (7,'99901') and (7,'99902') as two rows. This enforces one number per value and avoids packed multi-valued fields.

Part C — Short-Answer Reasoning (19–24)#

Practice 19 — Why is a phone number a risky permanent primary key?#

Answer: Phone numbers can change, can be missing, can be shared, and can be reused. A generated stable GuardianID is easier to keep unique and persistent. A phone may still have a conditional UNIQUE rule where appropriate, but should not be treated as universally unique or mandatory without a valid policy.

Practice 20 — Why does a database not automatically eliminate redundancy?#

Answer: The software manages whatever schema designers provide. If the designer copies the entire guardian address into every attendance row, the DBMS will store the repetition. Proper separation of entities, relationship keys and suitable constraints is what reduces unnecessary redundancy.

Practice 21 — How are data isolation and data dependence different?#

Answer: Isolation means logically related facts reside in disconnected or differently structured sources, making combined access difficult. Dependence means program code relies on file layout so a format change forces code changes. A multi-file report illustrates isolation; a parser breaking when an extra column is added illustrates dependence.

Practice 22 — Can a foreign key also be part of a primary key?#

Answer: Yes. In BILL_LINE(BillID,LineNo,...), BillID can be part of the composite primary key and a foreign key to BILL. Because it is part of the primary key, that column is non-NULL, even though unrelated optional foreign keys might allow NULL.

Practice 23 — Why are candidate keys minimal?#

Answer: A candidate key must uniquely identify rows without unnecessary attributes. If StudentID alone works, (StudentID,Name) is a superkey but not a candidate key. Minimality prevents treating arbitrary oversized sets as distinct meaningful identifiers.

Practice 24 — Why does an example with unique phone numbers not prove they are a candidate key?#

Answer: A snapshot could happen to have no repeated or missing numbers. Candidate-key suitability is a schema/business-rule requirement for all valid states, not merely an observed accident in five rows. Confirm uniqueness and presence or choose a more reliable identifier.

Part D — Applied Database Design (25–30)#

Practice 25 — Design a simple library lending database#

A library has many books, borrowers and loans. A borrower may borrow several books at different times. Name tables, keys and relationships.

Model answer:

BOOK(BookID PK, Title, Author)
BORROWER(BorrowerID PK, Name)
LOAN(LoanID PK, BookID FK, BorrowerID FK, BorrowedOn, ReturnedOn)

LOAN.BookID references BOOK and LOAN.BorrowerID references BORROWER. LoanID identifies each lending event; using BookID alone as the LOAN PK would incorrectly allow only one historic loan of a book. ReturnedOn may be nullable while a loan remains open. A real library may also need a COPY table to distinguish multiple physical copies of one title.

Practice 26 — Build a student-club membership relation#

Students can join several clubs; clubs have many students. Define a junction relation preventing duplicate membership.

Model answer:

STUDENT(StudentID PK, SName)
CLUB(ClubID PK, ClubName)
MEMBERSHIP(StudentID FK, ClubID FK, JoinedOn,
           PRIMARY KEY(StudentID, ClubID))

The composite key prevents a student from joining the same club twice under this model while permitting membership in multiple different clubs.

Practice 27 — Identify insert and update anomalies in a flat invoice table#

A one-table invoice repeats CustomerName, Address, ItemName and CurrentPrice for every purchased item. What goes wrong, and how would you improve it?

Model answer: A customer address change requires updating many invoice rows; one missed change causes inconsistency. Adding a catalogue item without a purchase may require an artificial invoice row (insert anomaly). Split into CUSTOMER, ITEM, BILL and BILL_LINE, joined by IDs. Store PriceAtSale per purchased line if invoices must preserve historical price instead of incorrectly recalculating old bills from today's catalogue price.

Practice 28 — SQL constraint preview: inventory catalogue#

Write an illustrative definition for INVENTORY(ItemID,ItemName,Quantity) enforcing unique ID, required name and nonnegative quantity.

Model answer:

CREATE TABLE INVENTORY (
    ItemID INTEGER PRIMARY KEY,
    ItemName TEXT NOT NULL,
    Quantity INTEGER NOT NULL CHECK (Quantity >= 0)
);

The PK enforces identity; NOT NULL prevents missing names; and CHECK rejects negative stock. A production inventory may need transactions rather than editing a number without an audit trail.

Practice 29 — Find a missing relationship in a school design#

The school stores STUDENT(StudentID,Name), EXAM(ExamID,Subject) and RESULT(StudentID,ExamID,Marks). Which keys are needed and why?

Model answer: STUDENT.StudentID is primary; EXAM.ExamID is primary. RESULT should reference both; if a student has one mark per exam, use (StudentID,ExamID) as the composite primary key. The foreign keys prevent results for nonexistent students or exams, and the composite key rejects duplicate results for the same pair.

Practice 30 — A constraint-based admission test#

Given ADMISSION(AdmissionNo PK, ApplicantName NOT NULL, CourseID FK nullable) and COURSE contains ID 5 only, decide if (101,'Asha',5), (102,'Ravi',9), (103,'Jai',NULL) and (101,'Maya',5) may be inserted in this order.

Model answer: (101,'Asha',5) succeeds because 101 is new and course 5 exists. (102,'Ravi',9) fails because course 9 does not exist. (103,'Jai',NULL) succeeds because CourseID is declared nullable. (101,'Maya',5) fails because AdmissionNo 101 already exists. The failed insertion of 102 does not prevent a later independent valid insertion into another key (assuming no enclosing transaction rollback).

Quick Revision — Database Concepts at a Glance#

Essential definitions#

Six recurring NCERT exam traps#

1. A foreign key can be NULL only if permitted. It is not automatically nullable merely because it references another table.
2. Observed unique values are not a declared key. The design must guarantee uniqueness across future states.
3. Different row and column orders may represent the same relation. Compare labelled attribute–value assignments.
4. Degree is not cardinality. Five rows with four attributes means cardinality five and degree four.
5. A composite primary key protects the full combination. Individual components may repeat when the pair remains unique.
6. An undeclared constraint cannot be silently assumed. Especially in NCERT Q10 and Q13, state the printed rules and the additional relational design assumption separately.

Five-step practical exam strategy#

1. Write what each row represents and identify all attributes.
2. Choose the minimal key and explicitly declare PRIMARY KEY / UNIQUE.
3. Draw the child-to-parent foreign-key arrows.
4. Check each attempted row against NOT NULL, uniqueness, CHECK and referential integrity.
5. If the question does not tell you whether an attribute is required or referenced, state the assumption and explain how the answer changes.

Viva / oral review — model answers#

Q. What is the difference between database and DBMS? — Database is organised data; DBMS is the software used to define, access and manage it.

Q. Does adding a tuple change degree? — No; degree counts columns, while adding a tuple changes cardinality.

Q. Can two students have the same guardian foreign key? — Yes; several students can reference one guardian.

Q. Why use a composite attendance key? — Roll number repeats across dates and date repeats across students; the pair can identify one daily student record.

Q. When may a missing phone number be represented as NULL? — When contact is unknown/inapplicable and the column's constraints allow NULL.

Q. Why does an alternate key have to be unique? — It is another candidate key; it must identify tuples independently of the chosen primary key.

Q. Is MongoDB an RDBMS? — No; it is a non-relational, document-oriented DBMS, though the NCERT list includes it among DBMS products.

Q. Is row order part of a relational table's meaning? — No; row order is a display choice unless deliberately requested in an output query.

Q. Can a foreign key point to an absent row? — Not when referential integrity is enforced and the value is non-NULL; the reference must match a parent key.

Q. What is a database instance? — The actual contents at a point in time, not the table structure.

Student self-check#

• I can explain all six file-system limitations with examples.
• I can tell schema, instance and metadata apart.
• I can calculate degree and cardinality on an unseen table.
• I can check a relation for repeated full tuples and non-atomic fields.
• I can choose minimal candidate keys and distinguish PK from FK.
• I can explain why ATTENDANCE needs a composite key.
• I can solve all NCERT exercises, including the constraint-dependent cases.
• I can design ITEM, BILL and BILL_LINE without duplicating purchase quantities in the catalogue.