Structure of a Database and RDBMS

Learning Objectives

Understand core concepts of Database Management Systems (DBMS) and Relational DBMS (RDBMS).
Become familiar with the Structured Query Language (SQL) and MySQL working environment.
Recognise and apply MySQL data-types, keys, and constraints.
Perform Data Definition Language (DDL) operations: create, modify, update, and delete databases/tables.
Execute Data Manipulation Language (DML) queries, chiefly the SELECT statement, to retrieve information.

Data vs. Information

Data
- Individual units with no inherent meaning until processed.
- Characterised as unorganised, raw facts.
- “…never suited to specific needs” until converted.
Information
- Data that has been processed and organised.
- Specific to expectations, requirements, and decision-making.
- Depends on underlying data.
Classroom exercise (Let’s Check-In): learners match statements A-H to “Data” or “Information.”

Understanding DBMS

DBMS = software for creating, organising, and managing databases.
Core capabilities:
- Reduction of data redundancy → eliminates duplicacy, preserves integrity.
- Data security → access-control mechanisms protect against unauthorised use.
- Query optimisation → improved retrieval performance.
- Backup & recovery → fault tolerance.
- Data manipulation → insert, retrieve, update, delete.
Real-world systems: ATMs, flight reservation, library catalogues, etc.
Historical note: Oracle (1977) among first popular RDBMS; IBM’s IMS used hierarchical model for NASA’s lunar lander diagrams.

Database Structure

Tables: primary storage entities (relations).
Columns (Attributes): fields describing properties.
Rows (Tuples/Records): individual instances.
Primary Key (PK): unique identifier for every row; enforces entity integrity.
Foreign Key (FK): column that references PK of another table; enforces referential integrity.

Types of Keys

Primary Key: uniquely identifies a record; no duplicates; cannot be NULL; basis for relationships.
Foreign Key: attribute that references another table’s PK; links tables; values must match or be NULL.
Candidate Key: any attribute (or combination) that can uniquely identify tuples; all PK candidates.
Composite Key: PK composed of two or more attributes, e.g. \text{Class} + \text{Section} + \text{Roll_no}.

RDBMS Conceptual Vocabulary

Relation ≡ Table.
Attribute ≡ Column.
Tuple/Record ≡ Row.
Schema: overall structural design of DB – tables, attributes, relationships.
Constraints: rules limiting permissible data (NOT NULL, UNIQUE, etc.).
Data Dictionary / Metadata: repository of schema & constraint info.
Instance: snapshot/state of DB at a particular moment.
Query: user request to perform an operation.
Data Manipulation: organising/updating data.
Index: data structure to speed look-ups.

Introduction to Relational Databases (Example)

Separate tables joined by common columns.
Student table & Library table linked on Student_ID.
- In Student → Student_ID is PK.
- In Library → Student_ID is FK.
Enables efficient storage, reduced redundancy, and meaningful joins.

Advantages of DBMS / RDBMS

Organised storage (tables, rows, columns).
Easier data retrieval.
Controlled, accurate updates.
Enhanced security & integrity.
Scalability for large datasets.

Real-Life Applications of Relational Databases

Customer Relationship Management (CRM).
Online retail & e-commerce catalogues.
Inventory & supply-chain tracking.
Electronic Health Records (EHR).
Banking & finance transactional systems.
Human-Resources (HR) payroll/attendance.
Library catalogues.
Government public records (birth, property, tax, voting).
Social-media user data storage.
Ethical/Practical implication: safeguarding personal data, upholding privacy regulations (GDPR, HIPAA, etc.).

Structured Query Language (SQL)

Domain-specific language for relational databases.
Supported by MySQL, Oracle, PostgreSQL, MS-SQL Server, etc.
Case-insensitivity: statements, object names generally not case-sensitive (Windows); in Linux, database/table names ARE case-sensitive.
Statements terminate with semicolon ;.
Multi-line entry → MySQL prompt changes from mysql> to -> until final ;.

Installing MySQL (Community Server)

Visit $https://dev.mysql.com/downloads/$ .
Choose “MySQL Community Server.”
Select version & OS > Download.
Run installer from download location.
Prerequisite: Visual Studio 2019 runtime; download if missing.
MySQL Configurator wizard: choose destination, server type, networking (default TCP $3306$ ), optional X-protocol $33060$ .
Set root password; follow prompts.
Execute configuration → Finish.
Launch “MySQL Command Line Client,” enter password to reach prompt.

MySQL Data Types (Common)

CHAR(n): fixed-length string $0\le n\le255$ ; unused bytes padded with spaces.
VARCHAR(n): variable-length string $0\le n\le65{,}535$ ; storage = bytes of actual data + length metadata.
INT: 4-byte integer; unsigned range $0\text{ to }4\,294\,967\,295$ .
BIGINT for larger integers.
FLOAT: 4-byte single-precision floating-point.
DATE: YYYY-MM-DD; valid range $1000\text{-}01\text{-}01\text{ to }9999\text{-}12\text{-}31$ .

MySQL Constraints

NOT NULL: column must contain a value.
UNIQUE: values must be distinct.
DEFAULT: supplies value when none provided.
PRIMARY KEY: combines NOT NULL + UNIQUE; designates table’s PK.
FOREIGN KEY: enforces referential integrity by linking to another table’s PK.

Core SQL Operations (DDL)

Create Database: CREATE DATABASE db_name;
Show Databases: SHOW DATABASES;
Select/Change Database: USE db_name;
Show Tables: SHOW TABLES;
Create Table:

  CREATE TABLE Student (
      Student_ID INT,
      Name VARCHAR(20),
      Class INT
  );

Alter Table
- Add column: ALTER TABLE Student ADD Age INT;
- Drop column: ALTER TABLE Student DROP Age;
- Change datatype: ALTER TABLE Student MODIFY Name VARCHAR(30);
- Set NOT NULL (or other) constraint: ALTER TABLE Student MODIFY Name VARCHAR(30) NOT NULL;
- Add PK: ALTER TABLE Student ADD PRIMARY KEY (Student_ID);
- Drop PK: ALTER TABLE Student DROP PRIMARY KEY;
Delete Table / Database
- Table: DROP TABLE Student;
- DB: DROP DATABASE Students;

Core SQL Operations (DML)

Insert row:

  INSERT INTO Student VALUES (20021,'Shivam',8);

Character data enclosed in single quotes.
- Select
Specific cols: SELECT Name FROM Student;
With condition: SELECT Name,Percentage FROM Student WHERE Percentage>70;
All rows/cols: SELECT * FROM Student;
- Update

  UPDATE Student SET Class=9 WHERE Student_ID=20021;

Delete

  DELETE FROM Student WHERE Student_ID=20023;

Omitting WHERE would delete every row (dangerous).

Closing MySQL

Click window close (X) or keyboard shortcut Alt + F4.

Good-to-Know / Trivia

MySQL originally stylised “mySQL” (1999), logo evolved through 2002-2010 to current dolphin icon.
Linux distinguishes case in DB & table names; Windows does not.
MySQL is free/open-source under GNU GPL; “Enterprise Edition for Developers” also free for learning & prototyping.

Ethical, Philosophical & Practical Implications

Data integrity and security are not mere technicalities—they uphold trust, privacy, and legal compliance.
Relational models simplify enforcement of ACID principles (Atomicity, Consistency, Isolation, Durability) → critical for finance, healthcare.
Efficient query optimisation reduces energy consumption (sustainability) and improves user experience.

Numerical & Reference Summary

INT unsigned range: $0 \to 4{,}294{,}967{,}295$ (4 bytes).
FLOAT storage: $4$ bytes.
DATE range: $1000\text{-}01\text{-}01\to9999\text{-}12\text{-}31$ .
Default MySQL ports: $3306$ (TCP/IP), $33060$ (X-Protocol).
VARCHAR maximum length: $65{,}535$ characters.

Milestone / Quick-Check Questions

Full form of MySQL: My Structured Query Language.
Constraints: rules that limit what data can be stored in a column (e.g.
NOT NULL, UNIQUE, DEFAULT, PK, FK) to maintain integrity & accuracy.
Two advantages of databases:
- Faster, organised data retrieval.
- Enhanced security & controlled access.