A shop called Wonderful Garments that sells school uniforms maintain a database SCHOOL_UNIFORM as shown below.

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

 

Table: UNIFORM

UCode	UName	UColor
1	Shirt	White
2	Pant	Grey
3	Skirt	Grey
4	Tie	Blue
5	Socks	Blue
6	Belt	Blue

 

Table: PRICE

UCode	Size	Price
1	M	500
1	L	580
1	XL	620
2	M	810
2	L	890
2	XL	940
3	M	770
3	L	830
3	XL	910
4	S	150
4	L	170
5	S	180
5	L	210
6	M	110
6	L	140
6	XL	160

 

 

a) The PRICE relation has an attribute named Price. In order to avoid confusion, write SQL query to change the name of the relation PRICE to COST.

 

b) M/S Wonderful Garments also keeps handkerchiefs of red color, medium size of `100 each. Insert this record in COST table.

 

c) When you used the above query to insert data, you were able to enter the values for handkerchief without entering its details in the UNIFORM relation. Make a provision so that the data can be entered in COST table only if it is already there in UNIFROM table.

 

d) Further, you should be able to assign a new UCode to an item only if it has a valid UName. Write a query to add appropriate constraint to the SCHOOL_UNIFORM database.

 

e) ALTER table to add the constraint that price of an item is always greater than zero.

 

Answer :-

 

a = ALTER TABLE PRICE

  RENAME TO COST ;

 

b = Insert into COST values ( 7 , “M” , 100 ) ;

 

c = We have to make foreign key  UCode in COST table ;

So, we have to write this query :-

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

 

d = Alter table UNIFORM modify column UName char(50) Not Null ;

 

e = Alter table COST modify PRICE int check ( PRICE > 0 ) ;