RDBMS Lab (ME-606) - Unit 1 Short Notes

UNIT 1: INTRODUCTION TO RELATIONAL DATABASES & BASIC SQL

1.0 Relational Model Fundamentals

1.1 Core Concepts

The relational model organizes data into relations (tables).

• Cardinality: Number of tuples (rows) in a relation.

• Degree: Number of attributes (columns) in a relation.

  \begin{equation*}

  \text{Degree} = \text{Count of Columns}, \quad \text{Cardinality} = \text{Count of Rows}

  \end{equation*}

1.2 Keys

• Super Key: Any set of attributes that uniquely identifies a tuple. (Can have extra attributes).

• Candidate Key: A minimal super key (no subset is a super key). There can be multiple.

• Primary Key (PK): The candidate key chosen to uniquely identify tuples. NOT NULL and UNIQUE.

• Alternate Key: A candidate key not chosen as the primary key.

• Foreign Key (FK): An attribute (or set) in one table that references the Primary Key of another table. It creates a link and enforces referential integrity.

[!TIP] Key Hierarchy: Super Key ⊃ Candidate Key ⊃ Primary Key (selected). All PKs are Candidate Keys, but not all Candidate Keys become PKs.

1.3 Integrity Rules

1. Entity Integrity: Primary Key cannot have NULL values. Ensures each entity is uniquely identifiable.

2. Referential Integrity: A Foreign Key value must either:

   • Match a Primary Key value in the referenced table, OR

   • Be NULL (if the FK column allows NULLs).

   This rule maintains consistency between linked tables.

2.0 SQL: Data Definition Language (DDL)

2.1 Data Types (Common)

2.2 CREATE TABLE Statement

CREATE TABLE table_name (

    column1 datatype [constraint],

    column2 datatype [constraint],

    ...

    [table_constraint]

);

Example with constraints:

CREATE TABLE Student (

    Roll_No INT PRIMARY KEY,

    Name VARCHAR(50) NOT NULL,

    Dept_ID INT,

    CONSTRAINT fk_dept FOREIGN KEY (Dept_ID) REFERENCES Department(Dept_ID)

);

2.3 ALTER TABLE Statement

• ADD column: ALTER TABLE table_name ADD column_name datatype;

• DROP column: ALTER TABLE table_name DROP COLUMN column_name;

• MODIFY column: ALTER TABLE table_name MODIFY column_name new_datatype;

• ADD CONSTRAINT: ALTER TABLE table_name ADD CONSTRAINT constraint_name constraint_definition;

• DROP CONSTRAINT: ALTER TABLE table_name DROP CONSTRAINT constraint_name;

2.4 DROP TABLE Statement

• DROP TABLE table_name; → Deletes the table structure and all data permanently.

2.5 TRUNCATE TABLE Statement

• TRUNCATE TABLE table_name; → Removes all rows quickly, resets storage, but cannot be rolled back (in most RDBMS). Does not fire DELETE triggers. Faster than DELETE.

[!TIP] DROP vs TRUNCATE vs DELETE:

• DROP: Removes table structure + data.

• TRUNCATE: Removes all data only, resets identity, fast, DDL command.

• DELETE: Removes specific/all rows, slow (row-by-row), can ROLLBACK, DML command, fires triggers.

3.0 SQL: Data Manipulation Language (DML)

3.1 INSERT Statement

-- Single row

INSERT INTO table_name (col1, col2) VALUES (val1, val2);

-- Multiple rows (MySQL, PostgreSQL)

INSERT INTO table_name (col1, col2) VALUES (v1a, v2a), (v1b, v2b);

-- From another table

INSERT INTO table1 (col1, col2)

SELECT colA, colB FROM table2 WHERE condition;

3.2 UPDATE Statement

UPDATE table_name

SET column1 = value1, column2 = value2

WHERE condition; -- CRITICAL: Without WHERE, updates ALL rows!

Update from another table:

UPDATE t1

SET t1.col = t2.col

FROM table1 t1

JOIN table2 t2 ON t1.id = t2.id

WHERE ...;

3.3 DELETE Statement

DELETE FROM table_name WHERE condition; -- Specific rows

DELETE FROM table_name; -- All rows (use with extreme caution!)

[!TIP] DELETE vs TRUNCATE (Exam Favorite):

| Feature | DELETE | TRUNCATE |

| :--- | :--- | :--- |

| Operation | DML (Row-by-row) | DDL (Deallocates pages) |

| WHERE | Allowed | Not allowed |

| Rollback | Possible (if in transaction) | Usually NOT |

| Triggers | Fires DELETE triggers | Does NOT fire triggers |

| Speed | Slower | Faster |

| Reset Identity | No | Yes |

4.0 Integrity Constraints (In-Depth)

4.1 Column-Level Constraints

Defined immediately after the column datatype.

col_name datatype CONSTRAINT constraint_name constraint_type,
-- e.g., Age INT CHECK (Age >= 18),
-- e.g., Email VARCHAR(100) UNIQUE,
-- e.g., Salary DECIMAL DEFAULT 50000

• NOT NULL: Ensures column has a value.

• UNIQUE: Ensures all values are distinct (allows one NULL in some DBs).

• DEFAULT value: Sets a default if no value provided.

• CHECK (condition): Validates data (e.g., CHECK (Gender IN ('M','F'))).

4.2 Table-Level Constraints

Defined at the end of the CREATE TABLE statement. Necessary for composite keys or FKs referencing columns not yet defined.

CREATE TABLE ... (

    ...

    CONSTRAINT pk_name PRIMARY KEY (col1, col2), -- Composite PK

    CONSTRAINT fk_name FOREIGN KEY (col) REFERENCES other_table(other_col)

        ON DELETE CASCADE

        ON UPDATE SET NULL

);

Foreign Key Actions (ON DELETE / ON UPDATE):

4.3 Naming Constraints

Always name constraints for easier management.

CONSTRAINT pk_student PRIMARY KEY (Roll_No),

CONSTRAINT fk_student_dept FOREIGN KEY (Dept_ID) ...

5.0 Basic Querying Techniques (Single Table)

5.1 SELECT Statement Fundamentals

SELECT [ALL|DISTINCT] column_list

FROM table_name;

• SELECT *: All columns.

• Arithmetic expressions: SELECT Salary * 1.1 AS New_Salary FROM Emp;

• Column aliasing: SELECT column_name AS alias FROM ...;

5.2 WHERE Clause

Filters rows based on a condition.

5.3 ORDER BY Clause

SELECT ... FROM ... ORDER BY col1 [ASC|DESC], col2 ...;
-- Default is ASC.

5.4 DISTINCT Keyword

Removes duplicate rows from the result set.

SELECT DISTINCT department FROM Employees;

5.5 Logical Precedence in WHERE clause

Order of evaluation: NOT > AND > OR.

[!TIP] Use parentheses to control logic and avoid errors. E.g., WHERE (A OR B) AND C is different from WHERE A OR (B AND C).

[[END OF UNIT 1 NOTES]]