home
0%
Progress
StudentPortal › Class XII - CS › Unit 3
CBSE Code 083 | Unit 3: Database Management & Python Connectivity

01. Relational Model & SQL Operations

Candidate/Primary/Foreign Keys, DDL (CREATE/ALTER/DROP) vs DML (SELECT/INSERT/UPDATE/DELETE).

Unit 3: Database Management & Python Connectivity

Chapter 1: Relational Model & SQL Operations

1. Recap: Relational Data Model Terms

A relation (table) organizes data into rows and columns.

Term Meaning Example
Relation A table Student table
Tuple A single row of a table One student's complete record
Attribute A column of a table Name, Marks, RollNo
Degree The number of attributes (columns) in a relation 4 columns → Degree = 4
Cardinality The number of tuples (rows) in a relation 30 students → Cardinality = 30
Domain The set of all permissible/valid values for an attribute Marks domain = 0 to 100

2. Keys in a Relational Model

Key Definition
Candidate Key An attribute (or minimal set of attributes) that can uniquely identify each tuple
Primary Key The candidate key chosen by the database designer to uniquely identify tuples; cannot contain NULL values
Alternate Key A candidate key that was not chosen as the primary key
Foreign Key An attribute in one table that refers to the Primary Key of another table, used to establish a relationship between the two tables

Example: In a Student table, both RollNo and AadharNo can uniquely identify a student (both are candidate keys). If RollNo is chosen as the Primary Key, then AadharNo becomes an Alternate Key.


3. SQL Command Categories

Category Full Form Commands Purpose
DDL Data Definition Language CREATE, ALTER, DROP Defines/modifies the structure (schema) of database objects
DML Data Manipulation Language INSERT, UPDATE, DELETE, SELECT Manipulates the actual data stored within tables

4. DDL: Creating and Altering Tables

CREATE TABLE

CREATE TABLE Student (
    RollNo    INT PRIMARY KEY,
    Name      VARCHAR(30) NOT NULL,
    Marks     DECIMAL(5,2) DEFAULT 0,
    Grade     CHAR(1)
);

Common Constraints

Constraint Purpose
PRIMARY KEY Uniquely identifies each row; implies NOT NULL + UNIQUE
NOT NULL Column cannot store a NULL (empty) value
UNIQUE All values in the column must be distinct
DEFAULT Specifies a default value when none is provided
CHECK Restricts values to satisfy a specific condition

ALTER TABLE

ALTER TABLE Student ADD Email VARCHAR(50);          -- add a new column
ALTER TABLE Student MODIFY Marks DECIMAL(6,2);       -- change a column's data type/size
ALTER TABLE Student DROP COLUMN Email;                -- remove a column

DROP TABLE vs DELETE

  • DROP TABLE Student; → Removes the entire table structure and all its data permanently (a DDL command).
  • DELETE FROM Student; → Removes only the data (rows); the empty table structure still remains (a DML command).

5. DML: Manipulating Data

-- INSERT a new row
INSERT INTO Student VALUES (1, 'Aman', 85.5, 'A');

-- UPDATE existing data
UPDATE Student SET Marks = 90 WHERE RollNo = 1;

-- DELETE specific rows
DELETE FROM Student WHERE Marks < 33;

-- SELECT (basic retrieval)
SELECT Name, Marks FROM Student WHERE Grade = 'A';

6. Board Exam Focus Questions

Q1. Differentiate between a Candidate Key and a Primary Key. Answer: Every candidate key is capable of uniquely identifying a row on its own, and a table can have multiple candidate keys. A primary key is the one candidate key specifically chosen by the designer as the main unique identifier for the table - a table can have only one primary key.

Q2. Write SQL statements for the following, based on a table Employee(EmpId, Name, Dept, Salary): - (i) Add a new column JoinDate of type DATE. - (ii) Increase the salary of all employees in the 'Sales' department by 10%. - (iii) Remove the record of the employee whose EmpId is 105.

Answer:

ALTER TABLE Employee ADD JoinDate DATE;

UPDATE Employee SET Salary = Salary * 1.10 WHERE Dept = 'Sales';

DELETE FROM Employee WHERE EmpId = 105;

Q3. State whether each of the following is a DDL or DML command: UPDATE, ALTER, INSERT, DROP. Answer: UPDATE → DML, ALTER → DDL, INSERT → DML, DROP → DDL.

Entity Details

Create New Item

help

Submit Technical Query

Have a question or run into an issue? Describe it below, upload an optional screenshot, and our engineering team will answer it!

image Attach image (optional)

Submit Feedback

build Free Developer Utility Free Tool
gavel

Privacy & Legal Disclaimer

1. Client-Side Browser Processing

All utility tools on DeepEngineerHub (including Image to PDF, Text Formatters, JSON Converters, and Encryptors) execute 100% locally within your client browser using WebAssembly and JavaScript. No uploaded images, text, or documents are transmitted, collected, or stored on remote servers.

2. Limitation of Liability ("As-Is" Provision)

Tools and services are provided free of charge for convenience and educational purposes "as-is" without warranties of any kind. DeepEngineerHub shall not be held liable for any data loss, formatting inconsistencies, or indirect damages resulting from tool usage.

3. Open Source & Third-Party Software

Certain utilities utilize open-source client libraries (such as jsPDF, Mermaid.js, Pyodide) licensed under MIT, Apache, or BSD open licenses. All intellectual property remains with their respective copyright holders.