Based on the data given above answer the following questions:
(i) Identify the most appropriate column, which can be considered as Primary key.
(ii) What is the product of degree and cardinality of the above table?
(iii) Write the statements to:
Update a record present in the table with data for Qtr2 – 200, Qtr3 = 600, total – sum of all Qtrs where the Client_ID is C660
OR (option for part iii only)
(iii)
(a) Delete all records where total is between 500 to 900
(b) Add a column RATINGS with datatype integer whose value must lie between 1 to 5
CLIENT
| ClientName | Client_ID | Qtr1 | Qtr2 | Qtr3 | Total |
|---|---|---|---|---|---|
| Suraj | C120 | 200 | 300 | 400 | 900 |
| Radha | C650 | 190 | 356 | 220 | 766 |
| Estha | C430 | 200 | 100 | 400 | 700 |
| Karuna | C790 | 130 | 540 | 380 | 1050 |
| Naresh | C660 | 200 | 400 | 800 | 1400 |
| Varun | C233 | 400 | 300 | 220 | 920 |
| Kritika | C540 | 500 | 100 | 400 | 1000 |
Answer (2)
(i) Identify the most appropriate column, which can be considered as Primary key.
A primary key is a column (or a combination of columns) in a table that uniquely identifies each row in that table. In the given table, the Client_ID column is the most appropriate choice for the primary key because it uniquely identifies each client.
(ii) What is the product of degree and cardinality of the above table?
The degree of a table refers to the number of columns it has. In the given table, there are six columns: Client, ClientName, Client_ID, Qtr1, Qtr2, Qtr3, and Total, making the degree = 6.
The cardinality of a table refers to the number of rows. The table has seven rows of data.
The product of degree and cardinality is calculated as follows:
Product of degree and cardinality = Degree × Cardinality = 6 × 7 = 42
(iii) Write the statements to:
(a) Option for part iii only (update):
To update a record in the table for the client with Client_ID C660, set Qtr2 to 200, Qtr3 to 600, Total to the sum of all quarters (Qtr1 + Qtr2 + Qtr3), the SQL statement would be:
UPDATE CLIENT SET Qtr2 = 200, Qtr3 = 600, Total = Qtr1 + Qtr2 + Qtr3 WHERE Client_ID = 'C660';
(b) Option for part iii only (delete and add column):
To delete records where Total is between 500 and 900, the SQL statement would be:
DELETE FROM CLIENT WHERE Total BETWEEN 500 AND 900;
To add a column RATINGS with datatype integer whose value must lie between 1 to 5, the SQL statement to add the column would be:
ALTER TABLE CLIENT ADD RATINGS INTEGER CHECK (RATINGS BETWEEN 1 AND 5);
The primary key for the table is the Client_ID column, which uniquely identifies each client. The product of the degree (6) and cardinality (7) of the table is 42. SQL commands for updating a record, deleting certain records, and adding a new column are provided in the response.
;
