A department store MyStore is considering to maintain their inventory using SQL to store the data. As a database administrator, Abhay has decided that:

• Name of the database — mystore
• Name of the table — STORE

The attributes of STORE are as follows:

ItemNo — numeric
ItemName — character of size 20
Scode — numeric
Quantity — numeric

Table: STORE

ItemNo	ItemName	Scode	Quantity
2005	Sharpener Classic	23	60
2003	Ball Pen 0.25	22	50
2002	Gel Pen Premium	21	150
2006	Gel Pen Classic	21	250
2001	Eraser Small	22	220
2004	Eraser Big	22	110
2009	Ball Pen 0.5	21	180

(a) Identify the attribute best suitable to be declared as a primary key.

(b) Write the degree and cardinality of the table STORE.

(c) Insert the following data into the attributes ItemNo, ItemName and SCode respectively in the given table

STORE.ItemNo = 2010, ItemName = "Note Book" and Scode = 25.

(d) Abhay wants to remove the table STORE from the database MyStore. Which command will he use from the following?

1. DELETE FROM STORE;
2. DROP TABLE STORE;
3. DROP DATABASE MYSTORE;
4. DELETE STORE FROM MYSTORE;

(e) Now Abhay wants to display the structure of the table STORE, i.e., name of the attributes and their respective data types that he has used in the table. Write the query to display the same.

(a) ItemNo attribute is best suitable to be declared as a primary key as it uniquely identifies each item in the inventory.

(b) The degree of the table STORE is 4, and the cardinality of the table STORE is 7.

(c)

INSERT INTO STORE(ItemNo, ItemName, Scode)
VALUES(2010, 'Note Book', 25);

(d) DROP TABLE STORE;
Reason — DROP TABLE command is used to remove/delete a table permanently. The syntax is : DROP TABLE <table_name>;. Hence, according to this DROP TABLE STORE; is the correct command to remove the STORE table from the database MyStore.

(e)

DESCRIBE STORE;

Output

+----------+----------+------+-----+---------+-------+
| Field    | Type     | Null | Key | Default | Extra |
+----------+----------+------+-----+---------+-------+
| ItemNo   | int      | NO   | PRI | NULL    |       |
| ItemName | char(20) | YES  |     | NULL    |       |
| Scode    | int      | YES  |     | NULL    |       |
| Quantity | int      | YES  |     | NULL    |       |
+----------+----------+------+-----+---------+-------+