Question

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

1. M/S Wonderful Garments also keeps handkerchiefs of red colour, medium size of Rs. 100 each.
2. INSERT INTO COST (UCode, Size, Price) values (7, 'M', 100);
   When the above query is used to insert data, the values for the handkerchief without entering its details in the UNIFORM relation are entered. Make a provision so that the data can be entered in the COST table only if it is already there in the UNIFORM table.
3. Further, they should be able to assign a new UCode to an item only if it has a valid UName. Write a query to add appropriate constraints to the SCHOOLUNIFORM database.
4. Add the constraint so that the price of an item is always greater than zero.

Answer 1

1. INSERT INTO UNIFORM (UCode, UName, Color) VALUES(7, 'HANDKERCHIEF', 'RED'); This query will insert a new row in the UNIFORM table for Red HANDKERCHIEF.
2. ALTER TABLE COST ADD FOREIGN KEY(UCode) REFERENCES UNIFORM(UCode);
3. ALTER TABLE UNIFORM ADD UName VARCHAR (20) NOT NULL;
4. ALTER TABLE COST ADD Cost INT CHECK( COST > 0);