The school has asked their estate manager Mr. Rahul to maintain the data of all the labs in a table LAB. Rahul

Question 26 of 29

Q. The school has asked their estate manager Mr. Rahul to maintain the data of all the labs in a table LAB. Rahul has created a table and entered data of 5 labs. LABNO LAB_NAME INCHARGE CAPACITY FLOOR L001 CHEMISTRY Daisy 20 I L002 BIOLOGY Venky 20 II L003 MATH Preeti 15 I L004 LANGUAGE Daisy 36 III L005 COMPUTER Mary Kom 37 II Based on the data given above answer the following questions:

(i) Identify the columns which can be considered as Candidate keys.

(ii) Write the degree and cardinality of the table.

(iii) Write the statements to:

(a) Insert a new row with appropriate data.

(b) Increase the capacity of all the labs by 10 students which are on 'I' Floor.

(OR)

(Option for part

(iii) only)

(iii) Write the statements to:

(a) Add a constraint PRIMARY KEY to the column LABNO in the table.

(b) Delete the table LAB.

Sikkim CbseCBSE Class XII Board 2023Subjective· 4mImportance★★★★★

90% · 26/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 →

Part (a)Concept understanding — Primary Key Identification

Primary Key Identification

Think about how you tell two students apart in a classroom. If you call out "Rahul," there might be three Rahul's. But if you say "Rahul Sharma, roll number 12," you've pointed to exactly one person. That unique identifier — the roll number — is doing the job of a primary key.

The Everyday Intuition

Every collection of things needs a way to refer to each item without confusion. In a library, every book has a unique accession number. In a bank, every account has an account number. In a school, every student has a unique admission number. These are not random — they are deliberately chosen so that no two items share the same identifier.

This is the core idea: a primary key is a single attribute (or a combination of attributes) that uniquely identifies each record in a table. No two rows can have the same primary key value, and no primary key can be empty (null).

The Precise Meaning

In database terms, a primary key serves two non-negotiable rules:

Uniqueness — every value in the primary key column must be different from every other value. If you have 100 students, you need 100 distinct roll numbers.
Non-null — every record must have a primary key value. You cannot have a student with "no roll number."

A primary key can be a single column, like a student ID. Or it can be a composite key — two or more columns taken together to create uniqueness. For example, in a table recording which books students have borrowed, neither "Student ID" alone nor "Book ID" alone is unique (a student borrows many books; a book is borrowed by many students over time). But the combination of "Student ID + Book ID + Date Borrowed" together can be unique.

Important

A primary key is not just any unique column. It is the official, chosen identifier for that table. A table can have many columns that are unique (like Aadhaar number, PAN number, and email), but only one of them is designated as the primary key. The others are called candidate keys — they could have been the primary key, but weren't chosen.

Why It Matters

Without a primary key, you cannot reliably update or delete a specific record. Imagine a school database where two students have the same name "Ananya Sharma." If you try to delete "Ananya Sharma," the database has no way to know which one you mean. With a primary key, you say "delete student with roll number 45" — and it's unambiguous. …

Part (b)Concept understanding — Primary Key Identification

Primary Key Identification

The Everyday Intuition

The Precise Meaning

In database terms, a primary key serves two non-negotiable rules:

Uniqueness — every value in the primary key column must be different from every other value. If you have 100 students, you need 100 distinct roll numbers.
Non-null — every record must have a primary key value. You cannot have a student with "no roll number."

Important

Why It Matters

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

Part (a)

(i) Candidate keys. LABNO (L001–L005, all distinct) and LAB_NAME (all five names distinct) are candidate keys. INCHARGE (Daisy repeats), CAPACITY (20 repeats) and FLOOR (I/II repeat) are not unique, so cannot be keys.

(ii) Degree = number of columns = 5; Cardinality = number of rows = 5.

(iii)

-- (a) insert a new row
INSERT INTO LAB VALUES ('L006', 'PHYSICS', 'Ravi', 25, 'I');

-- (b) increase capacity by 10 for labs on floor 'I'
UPDATE LAB SET CAPACITY = CAPACITY + 10 WHERE FLOOR = 'I'; …

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

Part (a): candidate keys are LABNO and LAB_NAME; degree 5 and cardinality 5; INSERT a new lab and UPDATE capacities on floor 'I'.

Part (b): the (iii) alternative — ALTER TABLE to add PRIMARY KEY(LABNO), and DROP TABLE LAB.

Part (a)

(i) Candidate keys. A candidate key uniquely identifies each row and is minimal. Checking the five rows:

LABNO — L001, L002, L003, L004, L005 are all distinct -> candidate key.
LAB_NAME — CHEMISTRY, BIOLOGY, MATH, LANGUAGE, COMPUTER are all distinct -> candidate key.
INCHARGE — Daisy appears twice (L001, L004), not unique.
CAPACITY — 20 appears twice, not unique.
FLOOR — I and II repeat, not unique.

So the candidate keys are LABNO and LAB_NAME.

(ii) Degree and cardinality. Degree = number of columns = 5 (LABNO, LAB_NAME, INCHARGE, CAPACITY, FLOOR). Cardinality = number of rows = 5.

(iii) SQL statements.

-- (a) insert a new row with appropriate data
INSERT INTO LAB VALUES ('L006', 'PHYSICS', 'Ravi', 25, 'I');

-- (b) increase capacity of all labs on floor 'I' by 10
UPDATE LAB SET CAPACITY = CAPACITY + 10 WHERE FLOOR = 'I';
``` …

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

←Previous questionQ11 — (a) Consider the following tables - LOAN and BORROWER: Table: LOAN LOA…→Next questionQ13 — fetchone() method fetches only one row in a ResultSet and returns a __…

Part (a)

🎓Unlock everything free for 14 days

Unlock everything free for 14 days