DBS101 Unit 3

Unit 3: SQL Fundamentals and Database Operations

After completeing the Entity-Relationship Model and Relational Schema in Unit 2, I was more excited to dive into the practical side of databases. Unit 3 introduced me to SQL (Structured Query Language) and database operations, which are the backbone of interacting with relational databases. This unit transformed my theoretical knowledge into hands-on skills, and here’s how it happened.

---

Previous Understanding of SQL and Database Operations

Before this unit, I had no idea that SQL was used to query databases, and I didn’t understand its full scope or how powerful it could be. Unaware of its capabilities for creating, modifying, and managing databases. Terms like ACID properties, transactions, and constraints were entirely new to me.

---

Key Learnings from Unit 3

ACID Properties in Databases

One of the foundational concepts I learned was the ACID properties, which ensure reliable transaction processing in databases:

1. Atomicity: Transactions are treated as a single unit—they either complete fully or not at all. For example, transferring money between accounts either succeeds completely or fails without partial updates.

2. Consistency: The database remains in a valid state before and after a transaction. Constraints like primary keys and foreign keys help maintain this.

3. Isolation: Concurrent transactions don’t interfere with each other. For example, two users booking the same flight seat won’t cause conflicts if isolation is properly enforced.

4. Durability: Once a transaction is committed, its effects are permanent, even in case of system failures.

SQL Basics and Structure

SQL is the standard language for interacting with relational databases. Here’s what I learned about its structure and components:

1. Data Definition Language (DDL): Used to define and modify database structures.
   • Example: CREATE TABLE, ALTER TABLE, DROP TABLE.
2. Data Manipulation Language (DML): Used to manipulate data within tables.
   • Example: INSERT, UPDATE, DELETE.

3. Data Query Language (DQL): Primarily the SELECT statement for querying data.

4. Constraints: Rules applied to columns to ensure data integrity.
   • Example: PRIMARY KEY, FOREIGN KEY, NOT NULL, UNIQUE.

Practical SQL Operations

I gained hands-on experience with SQL through practical exercises:

1. Creating Databases and Tables:

   CREATE DATABASE AirlineTicketingDB;
   CREATE TABLE Users (
   Username VARCHAR(255) PRIMARY KEY,
   DOB DATE,
   F_Name VARCHAR(255)
   );
   

2. Inserting Data

   INSERT INTO Users (Username, DOB, F_Name)
   VALUES ('user1', '1990-05-15', 'John');
   

3. Querying Data

   SELECT * FROM Users WHERE Gender = 'Male';
   

4. Modifying Tables

   ALTER TABLE Users ADD COLUMN Email VARCHAR(255);
   

5. Deleting Data and Tables

   DELETE FROM Users WHERE Username = 'user1';
   

Advanced SQL Concepts

-- Aggregates
SELECT deptname, AVG(salary) 
FROM instructor 
GROUP BY deptname 
HAVING AVG(salary) > 42000;

-- Subqueries
SELECT LastName FROM Employees 
WHERE OfficeCode IN (SELECT OfficeCode FROM Offices WHERE Country = 'USA');

-- Window functions
SELECT ROW_NUMBER() OVER (PARTITION BY deptname ORDER BY salary DESC) 
FROM instructor;

Conclusion

This unit transformed my understanding of SQL from simple queries to comprehensive database management. The hands-on exercises with the Airline Ticketing Database made learning engaging and practical.

---

Reflection

Through this unit, I’ve:

• Developed practical skills in writing complex SQL queries
• Gained confidence in database design and management
• Learned to implement ACID properties in real scenarios
• Understood how to optimize queries using advanced features

These directly applicable skills will be valuable for my future projects and career in database management.