Lecture 1: Introduction to Databases & Relational Model

1. Data, File Systems, and Storage

To truly grasp the necessity of databases, we must first understand the fundamental building blocks of information management and how they evolved.

• Data: Data represents the absolute raw facts, numbers, text, symbols, or observations that describe real-world entities. By themselves, these facts are unprocessed, unorganized, and often unstructured. Without context, data carries very little meaning. For instance, the number "25", the name "Ahmed", or the decimal "3.5" are mere data points until they are contextualized (e.g., Student Age, Student Name, Student GPA).
• File System: Before modern databases, information was primarily handled via traditional file systems managed by the operating system. In this setup, each application accesses its own files independently (like storing employee records in Excel and customer records in Word). This method is plagued by severe disadvantages:
  • It is unstructured.
  • There are no inherent relationships linking the data across different files.
  • It suffers from massive data redundancy (the same data must be typed repeatedly).
  • It lacks centralized administrative control and advanced security features.
• Data Storage: This is the physical or logical medium where data is saved (e.g., Hard Disks (HDD, SSD), USB drives, Cloud storage like AWS S3, or flat files like CSV, TXT, JSON, XML). Storage simply saves data as files or blocks; it possesses absolutely no built-in intelligence regarding relationships between the data, nor does it support querying or rule enforcement.

2. Databases and Database Management Systems (DBMS)

• Database: A database is an organized, electronically stored collection of data. Unlike a file system, a database strictly structures data into tables, rows, and columns, allowing for lightning-fast retrieval, seamless management, and easy updating. It is designed to minimize redundancy and maximize data integrity. Examples include student portals, hospital management systems, or e-commerce product inventories.
• DBMS: A Database Management System is the sophisticated software layer that acts as an interface between the user, the applications, and the database itself. It handles all backend operations, including storage, retrieval, updates, security, and concurrency control. Famous examples include MySQL, PostgreSQL, Oracle, and Microsoft SQL Server.

3. Main Types of Databases

The tech industry relies on various database models tailored to specific needs:

• Relational/SQL Database: Stores structured data in highly organized tables, enforcing strict relationships and referential integrity using SQL. They support ACID properties (Atomicity, Consistency, Isolation, Durability).
• Time-Series Database: Optimized specifically for high-write and read performance of time-stamped data (e.g., InfluxDB, Prometheus). They utilize advanced data compression and time-based retention policies.
• NoSQL Database: Highly scalable databases built for unstructured or semi-structured data. They include Document-based, Column-based, Key-value, and Graph databases.

4. Relational Database Management System (RDBMS)

An RDBMS manages structured data organized into tables (often called relations) consisting of rows (records) and columns (attributes). It guarantees consistency using SQL.

• Keys:
  • Primary Key (PK): Uniquely identifies each row in a table. It cannot be NULL and must always be unique.
  • Foreign Key (FK): A field in one table that uniquely identifies a row of another table, enforcing referential integrity.
• Relationships:
  • One-to-One (1:1): A record in Table A matches exactly one record in Table B.
  • One-to-Many (1:M): A record in Table A matches multiple records in Table B.
  • Many-to-Many (M:N): Multiple records in Table A map to multiple records in Table B, requiring a junction table.
• Popular RDBMS Software:
  • MySQL: Open-source, extremely fast, primarily used for web applications.
  • PostgreSQL: Open-source, highly extensible, used for enterprise systems and analytics.
  • Oracle Database: Proprietary, robust, utilized by large enterprises and banking sectors.
  • SQLite: Lightweight, serverless, file-based, used heavily in mobile and embedded applications.
  • Microsoft SQL Server: Proprietary, deeply integrated with Windows, heavily used in corporate applications and business intelligence.

5. Microsoft SQL Server Architecture

Microsoft SQL Server is an enterprise-grade RDBMS composed of two main component categories:

• Server Components (The Core):
  • Database Engine: The central service responsible for storing, processing, and securing relational data. It handles all queries, storage, indexing, and replication.
  • SQL Server Analysis Services (SSAS): Used for OLAP (Online Analytical Processing), deep data mining, and Business Intelligence.
  • SQL Server Reporting Services (SSRS): Used to create, manage, and deploy interactive reports.
  • SQL Server Integration Services (SSIS): Handles data integration and vital ETL (Extract, Transform, Load) operations.
  • SQL Server Agent: A background service that automates administrative tasks, such as scheduled backups and alerts.
• Client / Management Tools:
  • SQL Server Management Studio (SSMS): The primary GUI tool for developers and administrators to interact with SQL Server.
  • Connectivity Components: Drivers and libraries (ODBC, OLE DB) enabling external applications to connect.
  • SQL Server Data Tools (SSDT): A development environment within Visual Studio for building robust database solutions.