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 COSTrelation. By analysing the database schema and database state, specify SQL queries to rectify the following anomalies. M/S Wonderful Garments also keeps handkerchiefs of red colour, medium size of Rs. 100 each.
To insert the handkerchief data properly into the database, we need to insert records in both tables sequentially since UCode is the primary key in UNIFORM table and acts as a foreign key reference for COST table.
Query 1: sql INSERT INTO UNIFORM (UCode, UName, UColor) VALUES (4, 'Handkerchief', 'Red');
Query 2: sql INSERT INTO COST (UCode, Size, Price) VALUES (4, 'M', 100);
Explanation
The question asks to rectify the anomaly of missing handkerchief data. Since the database has two related tables - UNIFORM (with UCode as primary key) and COST (with UCode and Size as composite key), data must be inserted following referential integrity. The handkerchief details (UName='Handkerchief', UColor='Red', Size='M', Price=100) require insertion into UNIFORM table first, then into COST table. The UCode value 4 is chosen as the next available code after the existing codes (1, 2, 3) shown in the context.
Solution Steps
Step 1: Identify that handkerchief is a new uniform item requiring insertion in UNIFORM table first
Step 2: Insert into UNIFORM table with UCode=4, UName='Handkerchief', UColor='Red'
Step 3: Insert into COST table with UCode=4 (matching the UNIFORM entry), Size='M', Price=100