Q.A shop called Wonderful Garments who sells school uniforms maintains a database SCHOOLUNIFORM as shown below. It consisted of two relations - UNIFORM and COST. They made UniformCode as the primary key for UNIFORM relations. Further, they used UniformCode and Size to be composite keys for COST relation. By analysing the database schema and database state, specify SQL queries to rectify the following anomalies.

Shape of question

This is a plain theory question with four sub-parts (a), (b), (c), (d) asking for SQL queries to enforce constraints and insert data into a database schema. Each sub-part must be answered with the appropriate SQL statement and a brief explanation.

(a) Insert handkerchief data

The shop needs to add a red handkerchief (medium size, ₹100). Since UniformCode is the primary key in UNIFORM and (UniformCode, Size) is the composite key in COST, we must first insert the item into UNIFORM, then into COST.

INSERT INTO UNIFORM (UniformCode, UName, Colour)
VALUES (7, 'Handkerchief', 'Red');

INSERT INTO COST (UCode, Size, Price)
VALUES (7, 'M', 100);

The first statement creates the uniform item with code 7. The second adds its size-specific pricing. Without the UNIFORM entry, the COST insertion would violate referential integrity (once the constraint in part (b) is applied).

(b) Prevent orphan entries in COST

The anomaly described is that a row can be inserted into COST even if the corresponding UniformCode does not exist in UNIFORM. This is a referential integrity violation. We enforce it by declaring UCode in COST as a foreign key referencing UniformCode in UNIFORM.

ALTER TABLE COST
ADD CONSTRAINT fk_uniform
FOREIGN KEY (UCode) REFERENCES UNIFORM(UniformCode);

Now any INSERT into COST will fail if the UCode does not already exist in UNIFORM. The database rejects orphan cost records.

(c) Ensure UName is not null in UNIFORM

The requirement is that a new UniformCode can be assigned only if it has a valid UName. "Valid" here means non-null (and typically non-empty). We add a NOT NULL constraint on the UName column.

ALTER TABLE UNIFORM
MODIFY UName VARCHAR(50) NOT NULL;

(Adjust the data type and length to match the existing schema.) This prevents any INSERT or UPDATE that leaves UName as NULL. …

Understanding Database Constraints and Integrity

The question presents a classic scenario of database anomalies that arise when a schema lacks proper constraints. The shop's database allows inconsistent data — rows in COST that reference non-existent uniforms, missing uniform names, and invalid prices. Each sub-part asks you to write an ALTER TABLE statement that enforces a specific integrity rule.

The three constraint types you need are:

1. Foreign Key — ensures every UCode in COST exists in UNIFORM (referential integrity).
2. NOT NULL — prevents inserting a row without a required field (domain integrity).
3. CHECK — enforces a condition on column values (domain integrity).

Let's tackle each anomaly in turn.

(a) Insert handkerchiefs into the database

The shop wants to add a new item: red handkerchiefs, medium size, ₹100 each. Because UNIFORM holds item details and COST holds size-price combinations, you must insert into both tables.

Step 1: Insert the item into UNIFORM (assign a new UniformCode, say 7, and describe the item).

INSERT INTO UNIFORM (UniformCode, UName, Colour)
VALUES (7, 'Handkerchief', 'Red');

Step 2: Insert the size-price pair into COST.

INSERT INTO COST (UCode, Size, Price)
VALUES (7, 'M', 100);

(b) Prevent orphan records in COST — add a Foreign Key constraint

The problem states that the query

INSERT INTO COST (UCode, Size, Price) VALUES (7, 'M', 100);

succeeds even when UniformCode = 7 does not exist in UNIFORM. This creates an orphan record — a cost entry for a non-existent item.

The solution is a foreign key from COST.UCode to UNIFORM.UniformCode. This constraint rejects any insert/update in COST if the referenced UCode is absent from UNIFORM.

ALTER TABLE COST
ADD CONSTRAINT fk_cost_uniform
FOREIGN KEY (UCode) REFERENCES UNIFORM(UniformCode);

Why this works: Before inserting (7, 'M', 100) into COST, the DBMS checks whether UniformCode = 7 exists in UNIFORM. If not, the insert is rejected with a foreign-key violation error.

(c) Enforce that every uniform has a valid UName — add a NOT NULL constraint

The requirement is: "assign a new UCode only if it has a valid UName." In SQL, "valid" here means not null — every uniform must have a name.

ALTER TABLE UNIFORM
MODIFY UName VARCHAR(50) NOT NULL;

(Syntax varies slightly by DBMS; MySQL uses MODIFY, PostgreSQL uses ALTER COLUMN … SET NOT NULL, Oracle uses MODIFY.)

Why this works: After this constraint, any INSERT or UPDATE that leaves UName as NULL is rejected. You cannot create a uniform without naming it.

UPDATE UNIFORM SET UName = 'Unknown' WHERE UName IS NULL;

(d) Ensure price is always positive — add a CHECK constraint

Prices cannot be zero or negative. A CHECK constraint enforces this business rule at the database level.

ALTER TABLE COST
ADD CONSTRAINT chk_price_positive
CHECK (Price > 0);

Why this works: Before every insert or update on COST, the DBMS evaluates Price > 0. If false, the operation is rejected. …