Mastering SQL: Data Definition, Complex Filtering, and Join Operations

This comprehensive study note explores core computer science database fundamentals, transforming spoken lecture concepts into structured engineering notes. It covers the philosophical divide between relational and non-relational storage, Entity-Relationship (ER) modeling, relational algebra terminologies, the anatomy of database keys, and Structured Query Language (SQL) architecture categories (DDL, DML, DCL, TCL, DQL).

1. Relational vs. Non-Relational Storage: The Notebook Analogy

To understand database models, it helps to examine how data is organized structurally. The architectural choice between SQL and NoSQL mirrors how different notebooks are structured for writing.

Real-Life Analogy: Math Copy vs. Hindi Copy

• Relational Databases (SQL / MySQL / PostgreSQL): Imagine a math copybook featuring strict horizontal and vertical grid lines. Every entry must fit inside predetermined cells, and the schema is completely fixed.
• Non-Relational Databases (NoSQL / MongoDB): Imagine a Hindi copybook containing only horizontal lines. You can write as long a line as you want, and text is not bound by rigid tabular constraints.

2. Entity-Relationship (ER) Modeling Fundamentals

The Entity-Relationship (ER) Model is a structural blueprint used to visualize data using entities, attributes, and relationships. When drawn out visually using boxes and connecting lines, it is referred to as an ER Diagram.

Core Components of an ER Model

1. Entity: A real-world, independent object that holds data and possesses its own primary key.
   • Examples: Student, Employee, Product, User.
   • Graphical Representation: Rectangle.
2. Attribute: A characteristic or property describing an entity.
   • Examples: Name, Mobile Number, Address.
   • Composite Attributes: Can be sub-divided (e.g., Full Name split into First Name and Last Name).
   • Single-Attribute: Cannot be easily sub-divided (e.g., Age, Salary).
3. Relationship: The logical connection linking two or more entities.
   • Examples: A Student enrolled in a Course, or an Employee belonging to a Department.

3. Cardinality and Mapping Constraints

Cardinality defines the numerical mapping constraints between entities in a relational system, borrowing principles from Venn diagrams.

Participation Constraints

• Total Participation: Every single entity in the entity set must participate in at least one relationship. If an entity is created, it must serve a purpose to avoid wasting storage.
• Partial Participation: Not every entity in the set is required to participate in a relationship (e.g., an employee might not manage a project).

4. Extended ER Diagram Concepts

Advanced database design uses three specialized operations to handle complex structural hierarchies:

• Generalization (Bottom-Up Approach): Combines multiple lower-level, distinct entities into a single higher-level generalized entity.
• Specialization (Top-Down Approach): Breaks down a larger, generalized entity into multiple specialized lower-level entities based on distinct characteristics.
• Aggregation: Treats an entire relationship as a higher-level entity so it can participate in another relationship (e.g., an Employee works on a Project, and that whole assignment is Managed_By a Manager).

5. Relational Model Terminology

Proposed in 1970, the Relational Model organizes data into two-dimensional tables consisting of rows and columns.

Key Database Terminologies

• Relation: The table itself.
• Tuple: A single record or row inside a relation.
• Attribute: A column representing a specific property.
• Domain: The set of permissible, valid values allowed for an attribute (e.g., Age cannot be negative, and human age cannot exceed 100).
• Degree: The number of attributes (columns) in a table.
• Cardinality: The number of tuples (rows) in a table.

Core Properties of a Relation

1. Every relation must possess a unique name within the database schema.
2. All attribute (column) names within a given relation must be distinct.
3. Values stored within a specific column must belong to the same domain.
4. The order of tuples (rows) and order of attributes (columns) do not matter.
5. Every individual data cell contains a single, atomic, independent value.

6. The Anatomy of Database Keys

Keys are essential constraints used to enforce data integrity, establish relationships, and uniquely identify rows.

1. Super Key: Any combination of attributes that can uniquely identify a tuple in a relation (e.g., [EmployeeID], [EmployeeID, Email], [MobileNumber, Name]). It may contain extra, unneeded attributes.
2. Candidate Key: A minimal super key. It is a super key with no redundant attributes (e.g., [EmployeeID] or [Email] independently).
3. Primary Key: A specific candidate key chosen by the database designer to uniquely identify tuples in a table. It cannot contain NULL values and must remain unique.
4. Alternate Key: Any candidate key that was not chosen to serve as the primary key.
5. Foreign Key: An attribute (or set of attributes) in one table that references the Primary Key of another table, creating a relational bridge.
6. Comporsite Key: A primary key consisting of two or more attributes combined to achieve uniqueness.
7. Surrogate Key: An artificially generated key (typically an auto-incrementing integer, e.g., 1, 2, 3...) created strictly for database indexing and row identification.

7. SQL Command Categories (DDL, DML, DCL, TCL, DQL)

Structured Query Language (SQL) serves as the communication interface for relational databases (such as PostgreSQL and MySQL). SQL commands are partitioned into five functional categories:

1. DDL (Data Definition Language)

Used to define, structure, and modify the database schema itself.

• Commands: CREATE, ALTER, DROP, TRUNCATE.
• Caution: These are high-impact structural operations that alter database architecture.

2. DML (Data Manipulation Language)

Used to manage and manipulate the actual data records stored inside database tables.

• Commands: INSERT, UPDATE, DELETE.

3. DCL (Data Control Language)

Manages user access permissions and security privileges across the database.

• Commands: GRANT, REVOKE.

4. TCL (Transaction Control Language)

Manages database transactions to guarantee data consistency and ACID compliance.

• Commands: COMMIT, ROLLBACK, SAVEPOINT.

5. DQL (Data Query Language)

Used exclusively to query and retrieve data from database tables.

• Commands: SELECT.

Summary and Key Takeaways

• Relational vs Non-Relational: Relational databases (SQL) use fixed schemas like math notebooks, whereas non-relational databases (NoSQL) offer flexible, document-based storage.
• ER Modeling: Converts real-world objects into structured diagrams using Entities (rectangles), Attributes (properties), and Relationships (connections).
• Cardinality Mapping: Establishes numerical associations (1:1, 1:N, N:1, M:N) between connected entities.
• Relational Terminology: Tables are relations, rows are tuples, columns are attributes, column count is degree, and row count is cardinality.
• Key Hierarchy: Super keys are broad identifiers, candidate keys are minimal super keys, primary keys are unique non-null identifiers, and foreign keys link tables together.
• Surrogate Keys: Artificial auto-incrementing integers used for clean row identification and indexing.
• SQL Language Categories: Divided into DDL (schema definition), DML (data manipulation), DCL (permissions), TCL (transactions), and DQL (data retrieval).