Page 304 - Information_Practice_Fliipbook_Class11
P. 304

Table: PROJECT ASSIGNED
               Registration_ID     ProjectNo
               IP-101-15           101
               IP-104-15           103
               CS-103-14           102
               CS-101-14           105
               CS-101-10           104
               a. Name primary key of each table.
               b. Find foreign key(s) in table PROJECT-ASSIGNED.
               c.  Is there any alternate key in table STUDENT? Give justification for your answer.
               d. Can a user assign duplicate value to the field RollNo of STUDENT table? Jusify.
         Ans.  a. The name of the primary key of each table is as follows:
               Table                    Primary Key
               Student                  RollNo
               Project                  ProjectNo
               ProjectAssigned          {Registration_ID, ProjectNo}
              Note that: Registration_ID of STUDENT is an alternate key.
              b. The ProjectNo is the foreign key for PROJECT-ASSIGNED table.
              c. Yes, Name and Registration_ID can be an alternate keys in the STUDENT table as it sores unique values for each rows.
              d. No, as we have already defined RollNo as a primary key. So user cannot assign duplicate value to the field RollNo.

          13.  For the above given database STUDENT-PROJECT, can we perform the following operations?
               a. Insert a student record with missing roll number value.
               b. Insert a student record with missing registration number value.
               c. Insert a project detail without submission-date.
               d. Insert a record with registration ID IP-101-19 and ProjectNo 206 in table PROJECT-ASSIGNED.
         Ans.  a. No, because RollNo is a primary key, so it can not be NULL.
              b.  Yes, because Registration_ID is a foreign key in the STUDENT table, so the record with missing Registration_ID can be
                inserted.
              c. Yes, detail of project can be inserted provided a null constraint is not set for SubmissionDate.
              d.  No, because Registration_ID with value IP-101-19  does not exist in STUDENT. Also, ProjectNo with value 206 does
                not exist in PROJECT.
          14.  Match the following clauses with their respective functions.
               ALTER               Insert the value in a table
               UPDATE              Restrictions on columns
               DELETE              Table definition
               INSERT INTO         Change the name of a column
               CONSTRAINTS         Update existing information in a table
               DESC                Delete an existing row from a table
               CREATE              Create a database
         Ans.
               ALTER               Change the name of a column
               UPDATE              Update existing information in a table
               DELETE              Delete an existing row from a table
               INSERT INTO         Insert the values in a table
               CONSTRAINTS         Restrictions on columns
               DESC                Table definition
               CREATE              Create a database
          15.  Choose appropriate answer with respect to the following code snippet.
              CREATE TABLE student (
                          name CHAR(30),
                          student_id INT,
          290  Touchpad Informatics Practices-XI
   299   300   301   302   303   304   305   306   307   308   309