Notes - DBMS & SQL

Database Definition – Organized collection of interrelated data.

DBMS Advantages over File Systems:

Data independence, reduced redundancy, consistency, security, concurrency, integrity.
Core Components of DBMS
3-Tier Architecture
Data Abstraction Levels
Data Models
ER Model

The Relational Model (Core of DBMS)

Data is stored in tables (relations).

Each row (tuple) = one record,

Each column (attribute) = one field.

Example: STUDENT Table
Keys uniquely identify records and define relationships.

Key Type	Description	Example
Super Key	Any set of attributes that uniquely identifies a row	{Student_ID}, {Student_ID, Name}
Candidate Key	Minimal super key (no redundancy)	{Student_ID}
Primary Key	Chosen candidate key	Student_ID
Alternate Key	Remaining candidate keys	None here
Foreign Key	Links two tables	Dept_ID in Student → Dept table
Composite Key	Key with multiple attributes	(Student_ID, Course_ID) in Enrollment

Keys guarantee each record is unique.
Foreign keys maintain referential integrity between tables.

## Data Types in SQL
# Numeric Types
TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT
FLOAT(M,D), DOUBLE(M,D), DECIMAL(M,D)
-- For instance, DECIMAL(5,2) can store numbers like 123.45

# Date/Time Types
DATE        -- YYYY-MM-DD
DATETIME    -- YYYY-MM-DD HH:MM:SS
TIME        -- HH:MM:SS
YEAR        -- YYYY

# String/Text Types
CHAR(M)        -- Fixed-length string
VARCHAR(M)     -- Variable-length string
TEXT           -- Large text
BLOB           -- Binary large object (for files/images)
ENUM('x','y')  -- One value from a defined set 
-- Example: A column named status might use ENUM('pending', 'approved', 'rejected'). A row could have 'pending', but not 'pending, approved'. 
SET('x','y')   -- Multiple values from a defined set
-- Example: A column named features might use SET('wifi', 'pool', 'gym'). A row could have 'wifi', or 'wifi,gym', or 'pool,gym', or 'wifi,pool,gym', or nothing.

# Create table with constraints & properties
CREATE TABLE Department (
    Dept_ID INT PRIMARY KEY,
    Dept_Name VARCHAR(30) UNIQUE
);

CREATE TABLE Student (
    Student_ID INT PRIMARY KEY,
    Name VARCHAR(50) NOT NULL,
    Age INT CHECK (Age > 17),
    Dept_ID INT,
    FOREIGN KEY (Dept_ID) REFERENCES Department(Dept_ID)
);

<aside> 👉🏻

https://www.codewithharry.com/blogpost/mysql-cheatsheet

</aside>