CBSE Class 12 Informatics Practices Table And Integrity Constraints Worksheet

CBSE Class 12 Informatics Practices Table and Integrity Constraints. CBSE issues sample papers every year for students for class 12 board exams. Students should solve the CBSE issued sample papers to understand the pattern of the question paper which will come in class 12 board exams this year. The sample papers have been provided with marking scheme. It’s always recommended to practice as many CBSE sample papers as possible before the board examinations. Sample papers should be always practiced in examination condition at home or school and the student should show the answers to teachers for checking or compare with the answers provided. Students can download the sample papers in pdf format free and score better marks in examinations. Refer to other links too for latest sample papers.

Lesson 16

Integrity Constraints are the rules that a database must follow at all times. Various Integrity constraints are as follows:-

1. Not Null: It ensures that we cannot leave a column as null i.e. a value has to be supplied for that column.

2. Unique: Ensures that each row for a column must have a unique value. A column(s) can have null value but the values cannot be duplicated.

3. Primary key: - Primary key is used to identify the record uniquely in the table. A combination of a NOT NULL and UNIQUE means that a column cannot have duplicated values and not even a null value.

4. Default: Specifies a default value for a column. If no value is specified while inserting records then default value will be inserted.

5. Check: Ensures that the value in a column meets a specific condition. It is used to limit the range of values that can be inputted in to a column 

6. Foreign Key: Ensure the referential integrity of the data in one table to match values in another

Database constraints are rules implemented to ensure data validity and correctness. They must be adhered to at all times. Below are the standard SQL integrity constraints:

• Not Null: Guarantees that a field cannot be left blank. A valid value must be provided for the designated column.
• Unique: Guarantees that all values in a column are distinct across rows. Multiple NULL values are generally permitted, but duplicates of actual values are restricted.
• Primary Key: Uniquely identifies each record in a table. It is a logical combination of NOT NULL and UNIQUE constraints, meaning the column cannot contain duplicate values or NULLs.
• Default: Assigns a predefined value to a column when no specific value is provided during a new record insertion.
• Check: Verifies that the entered value satisfies a defined logical expression. It is frequently employed to restrict data to a specific range.
• Foreign Key: Establishes a link between tables, ensuring referential integrity by matching values in one table to the primary key values in another.

Creating a Table with Constraints

Constraints can be declared either directly during table creation or added afterward using the alter table command. Here is an example:

CREATE TABLE Student 
( 
    RollNo Integer Primary Key, 
    Regno Integer Unique, 
    NAME Char(20) Not Null, 
    Gender Char(1), 
    Age Integer Check (Age >= 5), 
    Admfee integer Default 5000 
);

Viewing Constraints and Table Schema

To inspect the structure and constraints of a table:

Syntax:
DESC <TABLENAME>;

Example:
DESC STUDENT; (Displays the detailed design and attributes of the STUDENT table)

Modifying Tables (Alter Table)

The ALTER TABLE statement serves several purposes in database schema modification:

1. Adding a Column

To insert a new column into an existing table:

Syntax:
ALTER TABLE <Table Name> ADD [COLUMN] <Column data Type>;

Example:
ALTER TABLE EMPLOYEE ADD MOBILE_NO INTEGER;
(This adds a new column named MOBILE_NO to the EMPLOYEE table)

2. Deleting a Column

To drop a column from a table:

Syntax:
ALTER TABLE <Table Name> DROP [COLUMN] <Column Name>;

Example:
ALTER TABLE EMPLOYEE DROP COLUMN MOBILE_NO;
(Removes the MOBILE_NO column from the EMPLOYEE table)

3. Modifying Column Data Types and Constraints

To change the data type or properties of a column:

Syntax:
ALTER TABLE <Table Name> MODIFY <Column Name> <Column Definition>;

Example:
ALTER TABLE EMPLOYEE MODIFY SALARY INTEGER(8); (Changes the column width to 8 digits)

Syntax to modify a column with constraints:
ALTER TABLE <Table Name> MODIFY <Column Name> <Column Definition> <Constraints>;

Example:
ALTER TABLE EMPLOYEE MODIFY EMP_NO INTEGER(10) PRIMARY KEY; (Sets EMP_NO as the primary key)

4. Adding Constraints

To apply a constraint to an active table:

Syntax:
ALTER TABLE <Table Name> ADD <Constraint Name>(column name);

Example:
ALTER TABLE EMPLOYEE ADD PRIMARY KEY (EMP_ID); (Specifies EMP_ID as the primary key)

5. Deleting Constraints

To remove a constraint from a table:

Syntax:
ALTER TABLE <Table Name> DROP <Constraint Name>;

Example:
ALTER TABLE EMPLOYEE DROP PRIMARY KEY; (Deletes the primary key constraint)

Enabling or Disabling Constraints

SQL allows turning certain constraints on or off temporarily (except for Primary Keys, which must be dropped rather than disabled). Other rules like Foreign Keys and Not Null can be toggled:

Syntax to enable:
SET FOREIGN_KEY_CHECKS=1;

Syntax to disable:
SET FOREIGN_KEY_CHECKS=0;

Dropping Tables

To completely delete a table and all associated definitions and constraints from the database, use the DROP TABLE statement:

Syntax:
DROP TABLE <TABLE_NAME>;

Example:
DROP TABLE EMPLOYEE; (Completely removes the employee table from the database)

Very Short Answer Type question (1 Marks)

Question 1. What is the use of ALTER TABLE Command?
Answer: The `ALTER TABLE` statement is used to modify the structural design of an existing database table. It enables operations such as introducing new columns, removing obsolete columns, changing data types, or adding and dropping table constraints.
In simple words: The `ALTER TABLE` command is like a remodeling tool for your database table. It lets you change the table's structure - like adding or removing columns - even after the table has been created and filled with data.

Exam Tip: Mention at least two specific operations like ADD, DROP, or MODIFY when explaining the use of this command to score full marks.

Question 2. What are integrity constraints? Write the names of the integrity constraints.
Answer: Integrity constraints are logical rules implemented on database columns to maintain data accuracy, consistency, and reliability. The primary integrity constraints in SQL include:
1. NOT NULL
2. UNIQUE
3. PRIMARY KEY
4. DEFAULT
5. CHECK
6. FOREIGN KEY
In simple words: Integrity constraints are like rules or guardrails for a database. They make sure people don't enter wrong, duplicate, or blank information where it is not supposed to be.

Exam Tip: Always list all six major constraints (NOT NULL, UNIQUE, PRIMARY KEY, DEFAULT, CHECK, and FOREIGN KEY) when asked for their names.

Question 3. What is Primary key Constraint?
Answer: A Primary Key constraint is used to uniquely identify each row or record in a table. It acts as a combination of NOT NULL and UNIQUE constraints, ensuring that the column does not contain duplicate values or blank entries.
In simple words: A Primary Key is like a student ID number or a roll number. It ensures that every single row has a unique identifier and that nobody can leave this identifier blank.

Exam Tip: Remember that a table can have only one Primary Key constraint, although it can contain multiple UNIQUE columns.

Question 4. What is Foreign key constraint?
Answer: A Foreign Key constraint is used to link data across multiple tables. It ensures referential integrity by requiring that values in a column in a child table must match a valid value in the primary key column of the parent table.
In simple words: A Foreign Key is a column in one table that points to the Primary Key of another table. It acts like a connector, making sure you cannot link a record to something that does not exist.

Exam Tip: Always mention "referential integrity" when explaining the purpose of a Foreign Key constraint.

Question 5. What is difference between Unique and Primary Key?
Answer: The primary differences between UNIQUE and PRIMARY KEY are:
- A table can contain multiple UNIQUE columns, but it can only have one PRIMARY KEY column.
- UNIQUE constraints permit the insertion of NULL (blank) values, whereas a PRIMARY KEY strictly forbids any NULL entries.
In simple words: A UNIQUE column ensures no duplicates but allows blanks, and you can have many of them. A PRIMARY KEY also prevents duplicates but forbids blanks, and you can only have one per table.

Exam Tip: Present the comparison in a tabular or bulleted format to make it easy for examiners to grade.

Question 6. Write SQL command to view the constraints of emp table.
Answer: To display the schema, columns, and constraints of the `emp` table, run the following statement:
DESC emp;
In simple words: You can run the `DESC emp;` command to look at the columns, data types, and constraints of the `emp` table.

Exam Tip: The abbreviation `DESC` is equivalent to the full keyword `DESCRIBE`. Both are acceptable in SQL.

Question 7. What is NULL?
Answer: In databases, NULL represents an empty, missing, or unknown data value. It is different from zero or blank text; it simply indicates that no information has been entered for that specific field.
In simple words: NULL means "no value" or "missing data". It is completely different from a number 0 or an empty blank text.

Exam Tip: Emphasize that NULL is a placeholder for unknown data, not a zero value, to show a clear conceptual understanding.

Question 8. What is the significance of NOT NULL constraint?
Answer: The NOT NULL constraint is significant because it prevents a column from storing empty or missing values. It guarantees that a valid entry must be provided whenever data is inserted or updated in that column.
In simple words: The NOT NULL rule makes sure you cannot leave a column blank. For example, you must enter a student's name, or else the system won't let you save the record.

Exam Tip: Explain how it enforces data completeness on critical fields like names, IDs, or contact details.

Question 9. Write a query to add new column aadharno in a table student.
Answer: To insert a new column named `aadharno` into the `student` table, execute:
ALTER TABLE student ADD aadharno VARCHAR(12);
In simple words: Use the `ALTER TABLE` command with `ADD` to put the new `aadharno` column into your `student` table.

Exam Tip: Make sure to specify an appropriate data type (like `VARCHAR(12)` or `BIGINT`) when adding a column in your query.

Question 10. Write a query to modify data type (char to int) of the existing column emp_id of emp table.
Answer: To modify the data type of `emp_id` to integer:
ALTER TABLE emp MODIFY emp_id INT;
In simple words: You can use `ALTER TABLE` along with the `MODIFY` command to change the data type of `emp_id` to an integer.

Exam Tip: Use correct keyword sequences: `ALTER TABLE table_name MODIFY column_name new_data_type;`.

Question 11. Write a query to delete a column pincode form a table employee.
Answer: To drop the `pincode` column from the `employee` table, run:
ALTER TABLE employee DROP COLUMN pincode;
In simple words: You can delete the `pincode` column by using the `ALTER TABLE` command with `DROP COLUMN`.

Exam Tip: In MySQL, the `COLUMN` keyword after `DROP` is optional, but including it is good practice.

Question 12. Can constraints be added in an existing table? How?
Answer: Yes, constraints can be added to an existing table using the `ALTER TABLE` command paired with the `ADD` clause.
Syntax:
ALTER TABLE table_name ADD CONSTRAINT constraint_name CONSTRAINT_TYPE (column_name);
Example:
ALTER TABLE employee ADD PRIMARY KEY (emp_id);
In simple words: Yes, you can add constraints to a table that already exists. You do this by running an `ALTER TABLE` query with an `ADD` instruction specifying the constraint.

Exam Tip: Provide a clear, simple syntax and an example to make your answer complete and easy to evaluate.

Question 13. Write a statement to enable and disable the constraints of table.
Answer: In MySQL, you can disable and enable referential constraints using the global system checks:
To disable constraints:
SET FOREIGN_KEY_CHECKS=0;
To enable constraints:
SET FOREIGN_KEY_CHECKS=1;
In simple words: You can temporarily turn off constraints by setting `FOREIGN_KEY_CHECKS` to 0, and turn them back on by setting it to 1.

Exam Tip: Remember that primary key constraints cannot be disabled; they must be dropped if you need to remove their checks.

Question 14. When a Primary key constraint is included in a table, what other constraints does this imply?
Answer: Including a Primary Key in a table automatically implies both the `UNIQUE` and `NOT NULL` constraints. This ensures every entry is completely unique and no field is left blank.
In simple words: A Primary Key constraint is basically a combination of two rules: "no blank spaces allowed" (NOT NULL) and "no duplicate values allowed" (UNIQUE).

Exam Tip: Clearly write "NOT NULL" and "UNIQUE" to show which underlying constraints are automatically inherited.

Short Answer Type questions (2 Marks)

Question 1. Write SQL command to create table Coach the following table structure.

Answer: To create the `Coach` table according to the specified schema:
CREATE TABLE Coach (
    PCode INT PRIMARY KEY,
    Name VARCHAR(20) NOT NULL,
    ACode INT,
    City VARCHAR(20) DEFAULT 'delhi',
    FOREIGN KEY (ACode) REFERENCES Activity(ACode)
);
In simple words: This command creates a new table named `Coach` with columns for code, name, activity reference, and city, applying all the required constraints like default values and foreign key links.

Exam Tip: Ensure that the table name referenced in the `FOREIGN KEY` clause matches exactly as specified (e.g., `Activity(ACode)`).

Question 2. Write a command to create following table with P_ID as foreign key from person table. The "Orders" table is:

Answer: To create the `Orders` table with `P_Id` acting as a foreign key:
CREATE TABLE Orders (
    O_Id INT PRIMARY KEY,
    OrderNo INT NOT NULL,
    P_Id INT,
    FOREIGN KEY (P_Id) REFERENCES person(P_Id)
);
In simple words: This creates the `Orders` table with a column `P_Id` that refers back to the `P_Id` column in the `Person` table.

Exam Tip: Always define the data type of the foreign key (`P_Id`) exactly as it is declared in the referenced table (`Person`).

Question 3. What are different constraints? Explain any two with example.
Answer: Constraints are logical rules defined on columns in a database table to maintain data accuracy and consistency. Two key constraints include:
1. **PRIMARY KEY Constraint:** Ensures that every row in the column is completely unique and cannot contain NULL values.
Example:
CREATE TABLE Employee (
    Emp_Id INT PRIMARY KEY,
    Name VARCHAR(30)
);
2. **DEFAULT Constraint:** Assigns a specified value to a column when no value is provided during database insertion.
Example:
CREATE TABLE Employee (
    Emp_Id INT,
    Country VARCHAR(30) DEFAULT 'India'
);
In simple words: Constraints are rules that keep data clean and accurate. For example, a Primary Key makes sure everyone gets a unique ID, and a Default constraint automatically fills in a value if you forget to enter one.

Exam Tip: Write simple, clean, syntax-compliant SQL examples to secure maximum marks on descriptive questions.

Question 4. Ms. Shilpa created two tables with Deptno as Primary key in Table1 and Foreign Key in Table2, while inserting a row in Table2, Ms. Shilpa is not able to enter a value in the column Deptno. What could be the possible reason there for it?
Answer: This issue occurs due to **Referential Integrity Constraints**. Since `Deptno` in `Table2` is defined as a Foreign Key referencing `Table1`, any value entered into `Table2(Deptno)` must already exist in the primary key column `Table1(Deptno)`. If Ms. Shilpa is trying to insert a value in `Table2` that does not exist in `Table1`, the database will reject the insertion to prevent inconsistent data. To solve this, she must first insert the corresponding `Deptno` value into the parent table (`Table1`).
In simple words: Because the `Deptno` in Table2 is linked to Table1, you cannot enter a department number in Table2 unless it already exists in Table1. She needs to add the department in Table1 first.

Exam Tip: Explain this scenario using terms like "Parent Table", "Child Table", and "Referential Integrity".

Question 5. Write a MySQL command for creating a table "CLUB" whose structure is given below:

Answer: To create the `CLUB` table with the requested constraints:
CREATE TABLE CLUB (
    MEMBER_No INT PRIMARY KEY,
    Member_Name VARCHAR(20),
    Join_Date DATE,
    Member_Type CHAR(1) NOT NULL,
    Charges DECIMAL(10,2)
);
In simple words: This command creates a table named `CLUB` with all the specified columns, data types, sizes, and constraints like NOT NULL and PRIMARY KEY.

Exam Tip: Do not specify any size for the `DATE` data type, as it is a built-in format in MySQL.

Question 6. Answer the question based on the table VOTER given below:

(i) Write the SQL query to create the table VOTER with the specified constraints.
(ii) Write a query to change the size of the Address column from 30 to 50.
Answer:
(i) To create the `VOTER` table with all specified constraints: CREATE TABLE VOTER (
    V_id INT PRIMARY KEY,
    Vname VARCHAR(25) NOT NULL,
    Age INT CHECK (Age > 17),
    Address VARCHAR(30),
    Phone VARCHAR(10)
);
(ii) To modify the size of the `Address` column: ALTER TABLE VOTER MODIFY Address VARCHAR(50);
In simple words: (i) The `CREATE TABLE` query builds the voter table with restrictions on age (must be older than 17) and voter name (cannot be blank). (ii) The `ALTER TABLE` command modifies the existing column to allow longer addresses.

Exam Tip: Make sure the CHECK constraint is written inside parenthesis: `CHECK (Age > 17)`.

Revision Crossword Clues and Answers

Question. Logical unit of work that must succeed or fail entirely. (Across)
Answer: TRANSACTION
In simple words: A transaction is a group of database actions that are treated as a single block. Either all of them must be completed successfully, or none of them are saved at all.

Exam Tip: Examples of transaction control commands in MySQL are COMMIT and ROLLBACK.

Question. Number of attributes in the table. (Down)
Answer: DEGREE
In simple words: Degree refers to the total count of columns (attributes) in a database table.

Exam Tip: Do not confuse 'Degree' (number of columns) with 'Cardinality' (number of rows).

Question. Special features that specify rules for the data in a table (Down)
Answer: CONSTRAINT
In simple words: Constraints are validation rules applied to columns to ensure the correctness and reliability of the data inside a table.

Exam Tip: Constraints can be declared at the column level or the table level.

Question. Statement to undo work done in the current transaction(Down)
Answer: ROLLBACK
In simple words: Rollback is like an "undo" command that cancels all modifications made during the current unsaved transaction.

Exam Tip: Rollback can only undo changes that have not yet been permanently saved with a COMMIT command.

Question. Statement that adds one or more records to any single table in a relational database.(Across)
Answer: INSERT
In simple words: The `INSERT` command is used to add new rows of data into a table.

Exam Tip: Use the `INSERT INTO` syntax followed by the `VALUES` clause to supply data.

Question. Combines records from two tables (Down)
Answer: JOIN
In simple words: A JOIN is used to combine columns and rows from two or more tables based on a related column between them.

Exam Tip: The most common type of join is the INNER JOIN, which matches common records from both tables.

Question. Returns the number of rows returned by the query. (Down)
Answer: COUNT
In simple words: The `COUNT` function is an aggregate function that returns the total number of records matching the query.

Exam Tip: Use `COUNT(*)` to count all rows, including those with NULL values.

Question. Statement to save changes made by a transaction.(Across)
Answer: COMMIT
In simple words: Commit permanently saves all database changes made during the current transaction.

Exam Tip: Once a COMMIT is executed, you cannot undo the changes using a ROLLBACK.

Question. Statement which is used to pull information from a table.(Across)
Answer: SELECT
In simple words: The `SELECT` command is used to retrieve and query data from one or more database tables.

Exam Tip: The `SELECT` statement is the most frequently used Data Query Language (DQL) command in SQL.

Question. Operator to define the range of values. (Down)
Answer: BETWEEN
In simple words: The `BETWEEN` operator is used to filter query results within a specific range of values, including both the starting and ending values.

Exam Tip: Remember that the `BETWEEN` operator is inclusive of both boundary values.