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.