Introduction to Databases and SQL Server

Lab 1: Draw an ER diagram for a library system.

1.1 What is Data, a Database, and a DBMS?

Data is raw facts. Information is data that has been processed into something meaningful.

A database is an organized collection of related data.

A DBMS is the software that stores, retrieves, and manages that data.

File system vs DBMS: a DBMS reduces redundancy and inconsistency, enforces integrity, controls concurrent access, and handles security and recovery.

1.2 Types of Databases

Relational (RDBMS) SQL Server, Oracle, PostgreSQL, MySQL Structured, transactional data MongoDB, Cosmos DB Caching, sessions Wide, distributed data Neo4j, SQL Server Graph Pinecone, SQL Server 2025 vectors AI similarity search

1.3 The Relational Model

E.F. Codd (1970). Data is organized into relations (tables).

Terminology: Table = Relation, Row = Tuple/Record, Column = Attribute/Field, Domain = set of allowed values.

Keys: Super key, Candidate key, Primary key, Alternate key, Foreign key, Composite key, Surrogate key, Natural key.

Codd's 12 rules (overview).

ACID properties: Atomicity, Consistency, Isolation, Durability.

1.4 What is SQL?

SQL (Structured Query Language) is the ANSI/ISO standard language for relational databases.

T-SQL (Transact-SQL) is Microsoft's extension. It adds variables, control-of-flow, error handling, and more.

SQL sub-languages: CREATE, ALTER, DROP, TRUNCATE INSERT, UPDATE, DELETE, MERGE GRANT, REVOKE, DENY

BEGIN TRAN, COMMIT, ROLLBACK, SAVE TRAN

1.5 SQL Server History and Versions

SQL Server 2005 SSMS, CLR, TRY/CATCH, ranking functions, XML SQL Server 2008/R2 MERGE, spatial, compression, TDE SQL Server 2012

AlwaysOn AGs, columnstore, SEQUENCE, OFFSET/FETCH SQL Server 2014 In-Memory OLTP (Hekaton) SQL Server 2016

Query Store, JSON, temporal tables, RLS, Always Encrypted SQL Server 2017

Linux support, graph, adaptive query processing SQL Server 2019

Big data clusters, IQP, ADR, UTF-8, scalar UDF inlining SQL Server 2022

Azure-connected features, Ledger, contained AG, IQP NextGen, new T-SQL functions SQL Server 2025

AI-ready: VECTOR type, AI functions, native JSON type, RegEx, optimized locking

1.6 SQL Server Editions

Enterprise: all features, mission-critical workloads.

Standard: core features with resource limits.

Express: free, with a 10 GB database size limit (50 GB in 2025).

Developer: free, full Enterprise features, non-production use only (2025 adds Standard and Enterprise Developer variants).

Azure SQL: cloud PaaS and IaaS (see Module 52).

1.7 SQL Server Architecture (High Level)

Client App ──(TDS protocol)──► SQL Server Network Interface (SNI)
                                    │
                    ┌───────────────▼────────────────┐
                    │  RELATIONAL ENGINE (Query Proc)│
                    │  Parser → Algebrizer →         │
                    │  Optimizer → Executor          │
                    └───────────────┬────────────────┘
                    ┌───────────────▼────────────────┐
                    │  STORAGE ENGINE                │
                    │  Access Methods, Buffer Mgr,   │
                    │  Transaction Mgr, Lock Mgr     │
                    └───────────────┬────────────────┘
                    ┌───────────────▼────────────────┐
                    │  SQLOS (scheduling, memory, IO)│
                    └────────────────────────────────┘
                         Data files (.mdf/.ndf), Log (.ldf)

1.8 System Databases

Instance-level metadata: logins, configuration, database list Template for every new database

SQL Agent jobs, backup history, Database Mail, SSIS packages

Temporary objects, version store, sorts. Recreated at every startup.

Hidden, read-only. Holds the system objects.

Created only when replication is configured Lab 1: Draw an ER diagram for a library system. Identify entities, attributes, and keys.