Introduction to Database

A database is a collection of related data organized to serve multiple applications efficiently. Data represents recordable facts with implicit meaning, such as names, addresses, and balances. A DBMS serves as an interface between the database and end users or application programs.

Data vs Information (Quick Comparison)

Key Characteristics of the Database Approach

1. Self-describing nature: Database contains not only data but also metadata (data about data) in the system catalog

2. Insulation between programs and data: Program-data independence allows changing data structure without modifying programs

3. Support for multiple views: Different users can have different perspectives of the same data

4. Sharing of data and multiuser transaction processing: Multiple users can access database simultaneously while maintaining consistency

Advantages of Using a DBMS

• Controlling redundancy: Eliminates duplicate data storage
• Restricting unauthorized access: Security and authorization mechanisms
• Providing persistent storage: Data outlives programs that create it
• Providing backup and recovery: Protection against hardware/software failures
• Enforcing integrity constraints: Rules to maintain data accuracy
• Permitting inference and actions using rules: Database triggers and stored procedures

Disadvantages of File-Based Systems

The file-based approach suffers from data redundancy, inconsistency, difficulty in accessing data, data isolation, integrity problems, atomicity issues, concurrent access anomalies, and security problems — all of which are addressed by a DBMS.

EXAMPLE 1: HIERARCHICAL DATABASE

University Structure (Tree):
University
|
-------------------------
| |
Department Department
(Computer Science) (Mathematics)
| |
---------- ----------
| | | |
Faculty Students Faculty Students

Limitation: A student double-majoring requires duplication or complex workarounds.

EXAMPLE 2: NETWORK DATABASE

Student-Course Registration (Graph):
Students <-------- Enrollment --------> Courses
| | |
| (Properties: Grade, |
| Semester) |
| |
Departments Instructors

Network allows M:N relationships directly but requires complex pointer navigation.

EXAMPLE 3: RELATIONAL DATABASE

University Schema (Tables):

STUDENTS Table:

COURSES Table:

ENROLLMENT Table (Junction):

SQL Query Examples:

-- Find all students enrolled in Database Systems
SELECT S.Name, S.Email
FROM STUDENTS S
JOIN ENROLLMENT E ON S.StudentID = E.StudentID
JOIN COURSES C ON E.CourseID = C.CourseID
WHERE C.CourseName = 'Database Systems';

-- Count enrollments per course
SELECT C.CourseName, COUNT(E.StudentID) as Enrollment_Count
FROM COURSES C
LEFT JOIN ENROLLMENT E ON C.CourseID = E.CourseID
GROUP BY C.CourseName;

REAL-WORLD APPLICATIONS:

1. Banking Systems: Account management, transactions, customer data
   • Relational model ensures ACID properties for financial transactions
   • Complex queries for fraud detection and reporting

2. Airline Reservation Systems: Flight schedules, bookings, passenger info
   • High concurrency handling thousands of simultaneous bookings
   • Complex relationships between flights, passengers, seats

3. E-Commerce Platforms: Products, orders, customers, inventory
   • Flexible schema for diverse product catalogs
   • Transaction support for payment processing

4. Healthcare Systems: Patient records, appointments, prescriptions
   • Data integrity critical for patient safety
   • HIPAA compliance through access control

PRACTICAL USAGE AND IMPLEMENTATION:

1. Choosing the Right Model:
   • Use Relational for: General-purpose applications, ACID requirements, complex queries
   • Consider NoSQL (modern evolution) for: Massive scale, flexible schema, specific access patterns

2. Database Design Process:
   Step 1: Requirements Analysis - Understand what data to store and how it will be used
   Step 2: Conceptual Design - Create ER diagram showing entities and relationships
   Step 3: Logical Design - Convert ER to relational schema with normalization
   Step 4: Physical Design - Optimize with indexes, partitioning, clustering

3. Real Implementation Example (MySQL):

-- Create database
CREATE DATABASE UniversityDB;
USE UniversityDB;

-- Create tables with constraints
CREATE TABLE Students (
StudentID INT PRIMARY KEY AUTO_INCREMENT,
Name VARCHAR(100) NOT NULL,
Email VARCHAR(100) UNIQUE,
Major VARCHAR(50),
EnrollmentDate DATE DEFAULT CURRENT_DATE
);

CREATE TABLE Courses (
CourseID VARCHAR(10) PRIMARY KEY,
CourseName VARCHAR(100) NOT NULL,
Credits INT CHECK (Credits > 0),
Department VARCHAR(50)
);

CREATE TABLE Enrollment (
StudentID INT,
CourseID VARCHAR(10),
Semester VARCHAR(20),
Grade CHAR(2),
PRIMARY KEY (StudentID, CourseID, Semester),
FOREIGN KEY (StudentID) REFERENCES Students(StudentID)
ON DELETE CASCADE,
FOREIGN KEY (CourseID) REFERENCES Courses(CourseID)
ON UPDATE CASCADE
);

4. Industry Best Practices:
   • Always use foreign keys to maintain referential integrity
   • Create indexes on frequently queried columns
   • Regular backups and disaster recovery plans
   • Monitor performance and optimize queries
   • Implement proper access control and authentication

5. Career Relevance:
   • Database Administrator (DBA): Manages database systems, ensures performance and security
   • Data Engineer: Designs and builds data pipelines
   • Backend Developer: Implements database interactions in applications
   • Data Analyst: Writes complex SQL queries for business insights