Page 285 - Information_Practice_Fliipbook_Class11
P. 285
6. A ______________ constraint allows you to specify a set of values that a particular attribute may take.
7. Data Manipulation language (DML) includes statements that modify the ______________ of a database.
8. The data type indicates the ______________ of values for an attribute.
9. If the length of a field of type CHAR is not specified, the default length is ______________.
10. Each SQL statement terminates with a ______________.
11. ______________ command is used to delete a table from a database.
12. The ______________ command is used to add, delete, or modify columns in an existing table.
D. Answer the following questions:
1. Observe the following table Transaction and write the names of the most appropriate columns, which can be considered
as (i) candidate keys and (ii) primary key: (Based on CBSE, 2015)
Table: Transaction
+----------+------------------------+------+-------+------------------+
| ItemCode | Item | Qty | Price | Transaction_Date |
+----------+------------------------+------+-------+------------------+
| 1001 | Plastic Folder 14= | 100 | 3400 | 2014-12-14 |
| 1004 | Pen Stand Standard | 200 | 4500 | 2015-01-31 |
| 1005 | Stapler Mini | 250 | 1200 | 2015-02-28 |
| 1009 | Punching Machine Small | 200 | 1400 | 2015-03-12 |
| 1003 | Stapler Big | 100 | 1500 | 2015-02-02 |
+----------+------------------------+------+-------+------------------+
Ans. We see from the table that there are two occurrences of Qty, obviously Qty does not qualify to be a candidate key.
We see from the table that each item has a unique price. However, this is not a permanent property of the table. In the
future, two different items may have the same price. Therefore, Price does not qualify to be a candidate key.
It appears from the table that each item is unique, so it qualifies to be a candidate key.
As there is a unique ItemCode for each item, ItemCode also identifies a tuple uniquely. Therefore, ItemCode also seems
to qualify to be a candidate key.
Subtle thing to note is that although there is a unique transaction for each item. However, as more transactions get included,
it is likely that there will be multiple transactions for the same item. So, Item alone does not qualify to be a candidate key.
Instead, a combination of Item and TransactionDate will form a candidate key.
Similarly, ItemCode and TransactionDate will also form a candidate key.
ItemCode being more concise than Item, it would be better to choose the combination (ItemCode, TransactionDate)
as the primary key. Further, writing the name of an item is error prone as it involves space and special characters.
General observation: As more and more transactions take place, the information about an item (Item and Price) will appear
several times. But this does not go well with the spirit of DBMS. So, it would be better to split the above table into two tables:
ITEM: ItemCode, Item, Price,
TRANSACTION: ItemCode, Transaction Date, Qty
2. Consider the following table and answer the questions that follow:
Table: MEMBER
+-----+-------+------+--------------+
| Mno | Name | Qty | PurchaseDate |
+-----+-------+------+--------------+
| 201 | Shirt | 67 | 14-07-2019 |
| 202 | Skirt | 15 | 12-12-2020 |
| 203 | Shoes | 102 | 10-09-2020 |
| 204 | Saree | 59 | 31-08-2021 |
| 201 | Shoes | 50 | 31-09-2021 |
+-----+-------+------+--------------+
(i) In the above table, can we take Mno as Primary Key only? Justify your answer with a valid reason.
(ii) What is the degree and the cardinality of the above table?
Ans. (i) No: If we assume many transactions can be performed on day by the same member, even though it is not clearly
evident from the table because the table shows only an instance of the relation and more rows may be added to it.
(ii) degree: 4, cardinality: 5
Database Concepts and the Structured Query Language 271

