Database Systems Programming - Oracle Overview and SQL Fundamentals

Vocabulary flashcards covering core database systems concepts, Oracle objects, ER modeling, SQL statement types, ACID transaction characteristics, and Oracle data types.

Software Development Life Cycle (SDLC)

A six-phase process consisting of Planning, Analysis, Design, Implementation, Testing & Integration, and Maintenance.

Data

Pieces of information.

Database

An organized collection of information.

DBMS

Database Management System; a system used to store, retrieve, and modify data in the database.

RDBMS

Relational Database Management System.

ORDBMS

Object-Relational Database Management System.

Table

The basic unit of storage in an RDBMS; composed of rows.

View

A database object that logically represents subsets of data from one or more tables.

Sequence

A database object that generates numeric values.

Index

A database object that improves the performance of some queries.

Synonym

A database object that gives alternative names to objects.

Data Models

Conceptual tools used to describe data.

Entity

A real-world object that can be easily identified (for example, in a school database: students, teachers, classes, and courses).

Attribute

A characteristic or property that describes an entity (for example, student name, age, or birthday in a student entity).

Relationship

A connection between two or more entities (for example, an employee works_at a department, or a student enrolls in a course).

Primary Key

A column or set of columns that uniquely identifies each row of data in a table; duplicate values cannot be used, and values generally cannot be changed.

Foreign Key

A purely logical pointer based on data values that relates data across tables by referencing an existing primary key or unique key column, or contains a null value.

SQL

Structured Query Language; a standard programming language specifically designed for managing and manipulating relational databases.

Data Manipulation Language (DML)

A category of SQL statements (SELECT, INSERT, UPDATE, DELETE, MERGE) used to manipulate data within tables without altering the database schema; commands are not automatically committed.

Data Definition Language (DDL)

A category of SQL statements (CREATE, ALTER, DROP, RENAME, TRUNCATE, COMMENT) that alter the schema or structure of the database and are automatically committed.

Data Control Language (DCL)

A category of SQL statements (GRANT, REVOKE) used for security management by controlling access to and modification of data and database objects; changes are usually committed automatically.

Transaction Control

Commands (COMMIT, ROLLBACK, SAVEPOINT) used to manage database transactions and enforce the ACID properties.

Atomicity

A transaction characteristic ensuring that a set of SQL operations either all succeed or all fail, maintaining data consistency.

Consistency

A transaction characteristic ensuring that the database remains in a valid and consistent state both before and after a transaction executes.

Isolation

A transaction characteristic that prevents unintended interactions by keeping concurrent transactions isolated from each other.

Durability

A transaction characteristic ensuring that once a transaction is committed, its modifications are permanent even in the event of system failure.

VARCHAR2(size)

An Oracle data type used to store variable-length character data.

CHAR(size)

An Oracle data type used to store fixed-length character data.

NUMBER(p, s)

An Oracle data type used to store variable-length numeric data specified by precision $p$ and scale $s$.

DATE

An Oracle data type used to store date and time values.

LONG

An Oracle data type used to store variable-length character data up to $2\,\text{GB}$.

CLOB

Character Large Object; an Oracle data type used to store character data up to $4\,\text{GB}$.

RAW and LONG RAW

Oracle data types used to store raw binary data.

BLOB

Binary Large Object; an Oracle data type used to store binary data up to $4\,\text{GB}$.

BFILE

An Oracle data type used to store binary data in an external operating system file up to $4\,\text{GB}$.

ROWID

A base-64 number system representing the unique physical address of a row in its table.