Anmol maintains a database of Medicines for his pharmacy using SQL to store the data. The structure of the table PHARMA is as follows:
Name of the table – PHARMA
Attributes:
MID - numeric
MNAME - character of size 20
PRICE - numeric
UNITS - numeric
EXPIRY - date
Table: PHARMA
| MID | MNAME | PRICE | UNITS | EXPIRY |
|-----|------------|-------|-------|-----------|
| M1 | PARACETAMOL| 12 | 120 | 2022-12-25|
| M2 | CETRIZINE | 6 | 125 | 2022-10-12|
| M3 | METFORMIN | 14 | 150 | 2022-05-23|
| M4 | VITAMIN B-6| 12 | 120 | 2022-07-01|
| M5 | VITAMIN D3 | 25 | 150 | 2022-06-30|
| M6 | TELMISARTAN| 22 | 115 | 2022-02-25|
(a) Write the degree and cardinality of the table PHARMA.
(b) Identify the attribute best suitable to be declared as a primary key.
(c) Anmol has received a new medicine to be added into his stock, but he does not know the number of UNITS. The rest of the values are:
| MID | MNAME | PRICE | UNITS | EXPIRY |
|-----|-----------|-------|-------|-----------|
| M7 | SUCRALFATE| 17 | | 2022-03-20|
Write the SQL command to add this medicine without the UNITS value.
(d) Anmol wants to change the name of the attribute UNITS to QUANTITY in the table PHARMA. Which command will he use?
(i) UPDATE
(ii) DROP TABLE
(iii) CREATE TABLE
(iv) ALTER TABLE
(e) Anmol wants to increase the PRICE of all medicines by 5. Which command will he use?
(i) UPDATE SET
(ii) INCREASE BY
(iii) ALTER TABLE
(iv) INSERT INTO
Answer (1)
(a) To determine the degree and cardinality of the table PHARMA, we need to understand these concepts:
Degree refers to the number of attributes (columns) in a table. In this case, the table PHARMA has 5 columns: MID, MNAME, PRICE, UNITS, and EXPIRY. Therefore, the degree of the table is 5.
Cardinality refers to the number of rows (records) in the table. Looking at the provided table, there are 6 medicines listed, so the cardinality is 6.
(b) The attribute best suitable to be declared as a primary key is MID. A primary key uniquely identifies each record in a table. MID is unique for each medicine, hence it can serve as an appropriate primary key.
(c) To add a new medicine into the PHARMA table without a value for UNITS, we should use the following SQL command:
INSERT INTO PHARMA (MID, MNAME, PRICE, EXPIRY) VALUES ('M7', 'SUCRALFATE', 17, '2022-03-20');
This command inserts a new row with the given MID, MNAME, PRICE, and EXPIRY, omitting the UNITS column.
(d) To change the name of the attribute UNITS to QUANTITY, Anmol will use the ALTER TABLE command. The correct choice is:
(iv) ALTER TABLE
(e) To increase the PRICE of all medicines by 5, Anmol will use the UPDATE command. The correct command and choice is:
(i) UPDATE SET
The SQL command would be:
UPDATE PHARMA SET PRICE = PRICE + 5;
This command updates the table PHARMA by increasing the PRICE of each medicine by 5.
