Database Systems: SQL Introduction & Relationships Summary

1. Introduction to Databases & SQL

• Database: A structured collection of related data stored in a computer system (not just unrelated files).
• DBMS (Database Management System): Software used to create, manage, and access databases (e.g., MySQL).
• SQL (Structured Query Language): The standard language used to communicate with the DBMS.
• Basic Structure:
  • Table: Stores structured data.
  • Record (Row): A single entry in a table.
  • Attribute (Column): A specific field containing data (e.g., Name, ID).

2. SQL Commands Categories

• DDL (Data Definition Language): Defines structure (CREATE, DROP, ALTER).
• DML (Data Manipulation Language): Manages data (INSERT, UPDATE, DELETE).
• DQL (Data Query Language): Retrieves data (SELECT).

3. Key SQL Commands

Database Operations

• Create: CREATE DATABASE database_name;
• Select/Activate: USE database_name; (Crucial step before creating tables).
• Verify: SHOW DATABASES;
• Delete: DROP DATABASE database_name; (Permanently removes the DB and all its contents).

Table Operations

• Create: CREATE TABLE table_name (column datatype constraint, ...);
• Verify: SHOW TABLES;
• Insert Data: INSERT INTO table_name (col1, col2) VALUES (val1, val2);
• Retrieve Data: SELECT * FROM table_name;
  • Filter: WHERE condition (e.g., WHERE age > 18).
  • Sort: ORDER BY column (Default is Ascending).
  • Unique: SELECT DISTINCT column (Removes duplicates).

4. Data Types

• INT: Used for integer values (whole numbers).
• VARCHAR(n): Variable-length text (saves space, stores only what is used).
• CHAR(n): Fixed-length text (always uses the defined length).
• DATE: Stores dates (YYYY-MM-DD).

5. Constraints

Constraints enforce rules on data columns:

• PRIMARY KEY: Uniquely identifies each record. Must be UNIQUE and NOT NULL. Only one per table.
• FOREIGN KEY: Links to a Primary Key in another table to establish relationships.
• NOT NULL: Ensures the column cannot be empty.
• UNIQUE: Ensures all values in the column are different.
• AUTO_INCREMENT: Automatically generates a unique number for new records (often used for IDs).

6. Relationships & Referential Integrity

• Referential Integrity: Ensures consistency between tables using Foreign Keys.
  • ON DELETE CASCADE: If a parent record is deleted, the related child record is automatically deleted.
  • ON UPDATE CASCADE: If a parent ID changes, the child Foreign Key updates automatically.

Relationship Types & Modeling

1. One-to-One (1:1):
   • Example: Person and Passport.
   • Structure: Each person has one passport. The Passport table contains a Foreign Key (person_id) referencing the Person table.
2. One-to-Many (1:M):
   • Example: Author and Books.
   • Structure: One author writes multiple books. The Book table (Child) contains a Foreign Key (author_id) referencing the Author table (Parent).