Skip to content
Exercises · Q10
Q.

School uniform is available at M/s Sheetal Private Limited. They have maintained SCHOOL_UNIFORM Database with two relations viz. UNIFORM and COST. The following figure shows database schema and its state.

School Uniform Database — Attributes and Constraints

Table: UNIFORM

AttributeUCodeUNameUColor
ConstraintsPrimary KeyNot Null-

Table: COST

AttributeUCodeSizePrice
ConstraintsComposite Primary KeyComposite Primary Key>0

(In the printed schema, "Composite Primary Key" is one merged cell spanning the UCode and Size columns.)

Table: UNIFORM

UCodeUNameUColor
1ShirtWhite
2PantGrey
3SkirtGrey
4TieBlue
5SocksBlue
6BeltBlue

Table:

UCodeSizeCOST Price
1M500
1L580
1XL620
2M810
2L890
2XL940
3M770
3L830
3XL910
4S150
4L170
5S180
5L210
6M110
6L140
6XL160

a) Can they insert the following tuples to the UNIFORM Relation? Give reasons in support of your answer.

i) 7, Handkerchief, NULL

ii) 4, Ribbon, Red

iii) 8, NULL, White

b) Can they insert the following tuples to the COST Relation? Give reasons in support of your answer.

i) 7, S, 0

ii) 9, XL, 100

Dnh Dd CbseNCERTSubjective· 4mImportance★★★★★est
79% · 11/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 →

Check every tuple against the declared constraints. UNIFORM: UCode = primary key (unique, not NULL), UName = NOT NULL, UColor = unconstrained. COST: (UCode, Size) = composite primary key, Price must be > 0. That gives: a-i Yes, a-ii No (duplicate key 4), a-iii No (NULL name); b-i No (price 0 fails > 0), b-ii Yes under the printed constraints — with the honest note that nothing in the printed schema declares COST.UCode a foreign key, so the "uniform 9 doesn't exist" problem is not caught by any stated constraint.

The declared constraints, as a CREATE TABLE (this is exactly what the printed schema states — note there is no foreign key declared on COST.UCode):

CREATE TABLE UNIFORM (
  UCode  INT PRIMARY KEY,       -- unique + NOT NULL
  UName  VARCHAR(20) NOT NULL,
  UColor VARCHAR(15)            -- no constraint: NULL allowed
);

CREATE TABLE COST (
  UCode INT,
  Size  VARCHAR(3),
  Price INT CHECK (Price > 0),
  PRIMARY KEY (UCode, Size)     -- composite key: the PAIR must be unique, neither part NULL
);

a) Inserting into UNIFORM

i) (7, 'Handkerchief', NULL) — YES, can be inserted.

UCode 7 does not occur among the existing codes 1–6 → primary key satisfied. UName is given ('Handkerchief') → NOT NULL satisfied. UColor is NULL — but UColor carries no constraint, and NULL simply records that the colour is unknown/unspecified. Every constraint passes.

ii) (4, 'Ribbon', 'Red') — NO.

UCode 4 already exists (Tie). The primary key must be unique; inserting a second tuple with key 4 violates entity integrity, and the DBMS rejects it with a duplicate-key error. (Ribbon would need a fresh code, e.g. 7 or 8.)

iii) (8, NULL, 'White') — NO.

UName has the NOT NULL constraint, and this tuple offers NULL for it. Rejected — every uniform must have a name.

b) Inserting into COST

i) (7, 'S', 0) — NO.

The Price constraint requires Price > 0, and 0 is not greater than 0 → the CHECK fails and the tuple is rejected. (The composite key (7, 'S') itself would have been fine — it's new. Also worth noting: no uniform with UCode 7 exists yet, so even if the row were priced correctly it would describe a non-existent product — see the note below.)

ii) (9, 'XL', 100) — YES, under the printed constraints.

Composite primary key (9, 'XL') does not occur in COST → unique, neither part NULL. Price 100 > 0 → CHECK satisfied. So all the constraints stated in the schema are met and the insertion is allowed. …

Unlock everything free for 14 days

  • Full step-by-step solutions
  • Concept-first explanations
  • Methods, shortcuts & mistakes
  • PYQ mapping + timed mock tests

Full access for 14 days. No credit card required.