Introduction to Databases and Oracle

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

1.1 Databases and DBMS Basics

Data, information, database, DBMS, RDBMS.

File systems vs DBMS: a DBMS reduces redundancy, enforces integrity, controls concurrency, and handles security and recovery.

Database types: relational, document, key-value, columnar, graph, vector, and converged (multi-model, which is Oracle's approach).

1.2 The Relational Model

E.F. Codd: relations (tables), tuples (rows), attributes (columns), domains.

Keys: super, candidate, primary, alternate, foreign, composite, surrogate, natural.

Integrity rules: entity integrity, referential integrity, domain integrity.

ACID: Atomicity, Consistency, Isolation, Durability.

1.3 SQL and Its Sub-languages

CREATE, ALTER, DROP, TRUNCATE, RENAME, COMMENT, PURGE, FLASHBACK INSERT, UPDATE, DELETE, MERGE GRANT, REVOKE

COMMIT, ROLLBACK, SAVEPOINT, SET TRANSACTION Session/System control ALTER SESSION, ALTER SYSTEM

In Oracle, every DDL statement issues an implicit COMMIT before and after it runs.

1.4 Oracle Corporation and Database History

First commercial SQL RDBMS PL/SQL stored procedures, triggers

Objects, partitioning, Java in the database, "i" = internet RAC, Flashback Query, XML DB

Grid, ASM, AWR/ADDM, Data Pump, Scheduler Result cache, SQL Plan Management, Active Data Guard, invisible indexes

Multitenant (CDB/PDB), identity columns, In-Memory (12.1.0.2), JSON Oracle 18c / 19c

Annual release model. 19c = long-term release, auto indexing, SQL quarantine

Innovation release: blockchain tables, native JSON type, SQL macros

Long-term release: AI Vector Search, JSON Relational Duality, BOOLEAN, SQL domains, 300+ features Oracle AI Database 26ai

Successor long-term release to 23ai, with agentic AI and further vector/AI features

1.5 Oracle Editions

Enterprise Edition (EE): full features. Many features are paid options (Partitioning, RAC, Advanced Security, In-Memory, Advanced Compression, Diagnostics and Tuning packs, Active Data Guard).

Standard Edition 2 (SE2): limited to 2 sockets and 16 threads, with no EE options.

Oracle Database Free: free, with resource limits (2 CPUs, 2 GB RAM, 12 GB user data). Replaces Express Edition (XE).

Personal Edition (legacy). Cloud: Autonomous Database, Base Database Service, Exadata Database Service.

⚠️ Licensing: Using AWR, ASH, ADDM, or the SQL Tuning Advisor requires the Diagnostics and Tuning Packs licenses. CONTROL_MANAGEMENT_PACK_ACCESS controls access. Always check licensing before using an option or pack.

1.6 Oracle Terminology Primer

The physical files (datafiles, control files, redo logs) Memory (SGA) plus background processes

A collection of objects owned by a user. In Oracle, schema = user.

A logical storage container mapped to datafiles Container database / pluggable database (multitenant) System identifier of an instance Logical name that clients connect to

A one-row, one-column dummy table (optional in 23ai: SELECT 1;)

Lab 1: Draw an ER diagram for a library system. Identify entities, attributes, relationships, and keys.