Page 185 - Informatics_Practices_Fliipbook_Class12
P. 185
Similarly, ItemCode and TransactionDate will also form a candidate key.
Item Code 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-9-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? Justify your answer with a valid reason.
(ii) What is the degree and the cardinality of the above table?
Ans. (i) Yes: If we assume only one transaction is performed on a day, i.e. in future also multiple transactions by the same
member will not be allowed on the same day.
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
3. List two advantages and two disadvantages of DBMS.
Ans. The advantages of DBMS are:
• Sharing of Data
• Reduction in Data Redundancy
The disadvantages of DBMS are:
• High initial cost
• Complex software
4. Define an entity and an attribute with respect to RDBMS.
Ans. (i) An entity is a real-world object whose data is stored in the database.
(ii) An attribute defines a characteristics of an entity.
5. Describe Referential Integrity Constraint.
Ans. If a table R1 has a foreign key (i.e. a non-key attribute), say, A, that refers to a UNIQUE attribute, say, B (typically primary
key) of another table R2, then
(i) The value of foreign key A maybe be NULL.
(ii) The value of foreign key A in table R1 maybe be non-NULL (say, val), then there must be a tuple in table R2 having
val as the value of attribute B. For example, suppose attribute Mgr_Id of relation DEPARTMENT refers to the
attribute ID of relation EMPLOYEE. As ID is a primary key of relation EMPLOYEE, Mgr_Id becomes a foreign key
for relation DEPARTMENT. So, in the DEPARTMENT table, if the department having Dept_No value 1 has Mgr_Id
value E0005, then the referential integrity constraint requires that the EMPLOYEE table must have an employee
having Id value E0005. Alternately, Mgr_ID value should be NULL for the DEPARTMENT having Dept_No
value 1.
Database Query using SQL 171

