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
   180   181   182   183   184   185   186   187   188   189   190