Question

In a database there are two tables'CD' and 'TYPE' as shown below :

                    Table : CD

Code	Title	Duration	Singer	Category
101	Sufi Songs	50 min	Zakir Faiz	12
102	Eureka	45 min	Shyama Mukherjee	12
103	Nagmey	23 min	Sonvi kumar	77
104	Dosti	35 min	Bobby	1

                        Table : Type

Category	Description
1	Jazz
12	Classical
40	Country Side
78	Pop

(i) Name the Primary key in "CD" table.

(ii) Name the foreign key in "CD" table.

(iii) Write the Cardinality and Degree of "TYPE" table.

(iv) Check every value in CATEGORY column of both the tables. Do you find any discrepancy ? State the discrepancy.

(v) Write SQL statement to change the name of Singer "Sonvi Kumar" to "Sonvi Mehra" in all the places wherever it occurs in CD table.

(vi) Write MySQL statement to add a column "Music_Director" which datetype Varchar and size as 30 in the table "CD".

(vii) Write command in SQL to display code, Title and corresponding description of all the CDs.

Answer 1

(i) Code

(ii) Category

(iii) Cardinality : 4 Degree : 2

(iv) Category 77 is not present in Category table. R-eferential integrity doesn't exist.

(v) UPDATE CD SET Singer='Sonvi Mehra' where Singer like 'Sonvi kumar';

(vi) ALTER TABLE CD ADD COLUMN Music _Director Varchar(30);

(vii) SELECT CODE, TITLE, DESCRIPTION FROM CD, TYPE WHERE CD.CATEGORY = TYPE CATEGORY;