Samacheer Kalvi 12th Computer Science Chapter 11 Database Concepts Exercise Solutions | KalviGavani

How to Use This Page

1. Attempt every exercise on your own before reading the solution. 2. Compare your approach with the solution — focus on the logic, not just the final answer. 3. Review the Common Mistakes section to avoid errors that cost marks in exams.

Key Concepts

Database and DBMS

Types of Keys

Relational Algebra

Normalisation Forms

Multiple Choice Questions (MCQs)

Q1. A DBMS is:

• (a) A hardware device for storing data
• (b) Software for managing databases
• (c) A programming language
• (d) A type of network

Q2. Which key uniquely identifies each record in a table and cannot be null?

• (a) Foreign key
• (b) Candidate key
• (c) Primary key
• (d) Super key

Q3. In relational algebra, the symbol σ (sigma) represents:

• (a) Project operation
• (b) Select operation
• (c) Join operation
• (d) Union operation

Q4. A table is in 2NF if it is in 1NF and:

• (a) It has no foreign keys
• (b) It has no partial dependencies
• (c) It has no transitive dependencies
• (d) It has no duplicate rows

Q5. Which of the following is a transitive dependency?

• (a) RollNo → Name
• (b) RollNo → DeptID → DeptName
• (c) (RollNo, Subject) → Marks
• (d) EmpID → Salary

Fill in the Blanks

Q1. A ______ is a minimal super key.

Q2. The ______ operation in relational algebra selects specific columns from a table.

Q3. In 1NF, every attribute must contain ______ values.

Q4. A ______ key in one table refers to the primary key of another table.

Q5. The process of organising data to reduce redundancy is called ______.

True or False

Q1. A super key can contain redundant attributes.

Q2. A foreign key can have null values.

Q3. A table in 3NF is automatically in 2NF.

Q4. The Project operation in relational algebra can produce duplicate rows.

Q5. In a relational model, data is stored as objects.

Match the Following

Short Answer Questions

Q1. What is a DBMS? Give two examples.

A Database Management System (DBMS) is software that enables users to create, manage, and manipulate databases. It provides tools for storing, retrieving, updating, and deleting data efficiently while ensuring data integrity and security.

Examples: MySQL, PostgreSQL, Oracle, Microsoft SQL Server, SQLite.

Advantages of DBMS:

• Reduces data redundancy
• Ensures data consistency
• Provides data security and access control
• Supports concurrent access by multiple users
• Enables backup and recovery

Q2. Differentiate between primary key and candidate key.

Example: In a Student table, both RollNo and Email can uniquely identify a student. Both are candidate keys. The designer chooses RollNo as the primary key, making Email an alternate key.

Q3. What is a foreign key? Give an example.

A foreign key is an attribute (or set of attributes) in one table that refers to the primary key of another table. It establishes a relationship between two tables.

Example:

Department Table:

Employee Table:

The DeptID in Employee is a foreign key referencing DeptID in Department. This ensures every employee belongs to a valid department.

Q4. Explain the Select (σ) operation with an example.

The Select operation filters rows from a table based on a condition. It returns all tuples that satisfy the given condition.

Symbol: σ (sigma)

Syntax: σ_condition(Relation)

Example: Given Student table:

Query: σ_(Marks>75)(Student)

Q5. Explain the Project (π) operation with an example.

The Project operation selects specific columns from a table, eliminating duplicates in the result.

Symbol: π (pi)

Syntax: π_column_list(Relation)

Example: Using the Student table above:

Query: π_(Name, Marks)(Student)

Detailed Answers

Q1. Explain all types of keys in a database with examples.

Consider a Student table:

(a) Super Key

Any combination of attributes that uniquely identifies a tuple.

• {RollNo}
• {RollNo, Name}
• {RollNo, RegNo}
• {Email}
• {RegNo, Email}

All of these are super keys because each can uniquely identify a row.

(b) Candidate Key

A minimal super key — removing any attribute would make it non-unique.

• {RollNo} — minimal
• {RegNo} — minimal
• {Email} — minimal

These are candidate keys because they are the smallest sets that still uniquely identify a tuple.

(c) Primary Key

The candidate key chosen by the designer as the main identifier.

• RollNo (chosen as primary key)

(d) Alternate Key

Candidate keys not selected as the primary key.

• RegNo, Email (these are alternate keys)

(e) Foreign Key

An attribute in one table that references the primary key of another table.

• DeptID in Student references DeptID in Department table.

Q2. Explain normalisation with examples (1NF, 2NF, 3NF).

1NF — First Normal Form

Rule: Every attribute must have atomic (indivisible) values. No repeating groups or multi-valued attributes.

Not in 1NF:

In 1NF (each subject in a separate row):

2NF — Second Normal Form

Rule: Must be in 1NF + no partial dependency (every non-key attribute must depend on the entire primary key, not just part of it).

Not in 2NF (composite key: RollNo + Subject):

Problem: StudentName depends only on RollNo (partial dependency), not on the full key (RollNo + Subject).

In 2NF (split into two tables):

Student table:

Marks table:

3NF — Third Normal Form

Rule: Must be in 2NF + no transitive dependency (non-key attributes must not depend on other non-key attributes).

Not in 3NF:

Problem: DeptName depends on DeptID (not directly on RollNo). RollNo → DeptID → DeptName is a transitive dependency.

In 3NF (split into two tables):

Student table:

Department table:

Q3. Explain the Join operation with an example.

The Join operation combines rows from two tables based on a related column between them.

Student table:

Department table:

Natural Join: Student ⋈ Department (on DeptID)

The join matches rows where DeptID is the same in both tables.

Q4. What are database constraints? Explain different types.

Database constraints are rules enforced on data to maintain accuracy and integrity.

CREATE TABLE Student (
    RollNo INT PRIMARY KEY,
    Name VARCHAR(50) NOT NULL,
    Email VARCHAR(100) UNIQUE,
    DeptID VARCHAR(5),
    Marks INT CHECK (Marks >= 0 AND Marks <= 100),
    Status VARCHAR(10) DEFAULT 'Active',
    FOREIGN KEY (DeptID) REFERENCES Department(DeptID)
);

Q5. Differentiate between file system and DBMS.

Q6. Explain the relational model with an example.

The relational model represents data as a collection of relations (tables). Each relation has:

• Relation name (table name)
• Attributes (column names)
• Tuples (rows/records)
• Domain (set of allowed values for each attribute)

Example: Student Relation

Schema: Student(RollNo, Name, Age, DeptID)

Instance (data):

Properties:

• Each column has a unique name (attribute name)
• Each cell contains a single atomic value
• No duplicate rows (tuples are unique)
• Order of rows and columns is not significant
• Each row is unique, identifiable by primary key

Q7. Write SQL queries to create tables with primary and foreign keys.

-- Department table
CREATE TABLE Department (
    DeptID VARCHAR(5) PRIMARY KEY,
    DeptName VARCHAR(50) NOT NULL,
    HOD VARCHAR(50)
);

-- Student table with foreign key
CREATE TABLE Student (
    RollNo INT PRIMARY KEY,
    Name VARCHAR(50) NOT NULL,
    Age INT CHECK (Age > 0),
    Email VARCHAR(100) UNIQUE,
    DeptID VARCHAR(5),
    FOREIGN KEY (DeptID) REFERENCES Department(DeptID)
);

-- Insert data
INSERT INTO Department VALUES ('D01', 'Computer Science', 'Dr. Kumar');
INSERT INTO Department VALUES ('D02', 'Physics', 'Dr. Lakshmi');

INSERT INTO Student VALUES (101, 'Arun', 17, '[email protected]', 'D01');
INSERT INTO Student VALUES (102, 'Bala', 18, '[email protected]', 'D02');
INSERT INTO Student VALUES (103, 'Chitra', 17, '[email protected]', 'D01');

Common Mistakes

Exam Checklist

• [ ] I can define database, DBMS, and relational model
• [ ] I can differentiate between super key, candidate key, primary key, alternate key, and foreign key
• [ ] I can perform Select (σ) and Project (π) operations on a given table
• [ ] I can perform Join operations between two tables
• [ ] I can explain 1NF, 2NF, and 3NF with examples
• [ ] I can identify partial and transitive dependencies
• [ ] I know the six database constraints: NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK, DEFAULT
• [ ] I can write SQL CREATE TABLE statements with constraints
• [ ] I can convert unnormalised data to 1NF, 2NF, and 3NF