(SQL) Advanced Database 1 (Prelim)

Vocabulary flashcards covering key terms and concepts from the lecture notes on database systems, SQL extensions, and data types.

Database

An organized collection of structured information stored electronically, usually controlled by a DBMS; together with the DBMS and related applications, forms a database system.

DBMS

An interface between the database and end-users/programs; enables retrieval, updates, organization, optimization; provides administrative operations like performance monitoring, tuning, backup and recovery.

Database Application

One or more programs serving as intermediary between the user and the DBMS; reads or modifies data via database query language.

Naive user

An unsophisticated user who interacts with predefined interfaces such as web or mobile applications.

Application programmer

A computer professional who writes application programs that access the database.

Sophisticated user

A user who interacts with the system without writing programs, often using a database query language.

Centralized database system

A database that stores data located at a single site.

Distributed database system

A collection of multiple interconnected databases spread across various locations.

Structured Data

Data stored in relational databases with a predefined schema.

Unstructured Data

Data in its original raw state, existing in various shapes and sizes and without a predefined schema.

Semi-structured Data

Data items of the same type that may have different attributes; contains semantic tags and has no predefined schema.

SQL

Structured Query Language; a language for storing, manipulating, and retrieving data in a relational database.

NoSQL

Not only SQL; databases not based on the traditional relational model, often with dynamic schemas for unstructured data and document storage.

NewSQL

A generation of DBMS that combines SQL with the speed and scalability of NoSQL, aiming to provide improved functionality.

Object-Oriented database

A database that stores objects rather than rows in tables; uses languages like C++, Java, C# and supports inheritance for complex data with high performance.

XML

Extensible Markup Language; designed to facilitate exchange of structured documents; platform-independent and suitable for small datasets.

PL-SQL

Procedural Language extension of SQL developed by Oracle.

T-SQL

Transact-SQL; Microsoft/Sybase extension of SQL adding variables, transaction control, error handling, and row processing.

PostgreSQL

Open-source object-relational database system that extends SQL.

MySQL

Open-source SQL relational database management system developed and supported by Oracle.

SQLite

Relational database management system contained in a C programming library.

BEGIN/END

SQL constructs that define a block of statements to execute together.

DECLARE

Declare a variable in SQL Server; variable names start with @.

SET

Assign a value to a variable (SET or SELECT can be used for assignment).

CAST

Converts an expression from one data type to another.

PRINT

Outputs a predefined message or value.

TRY-CATCH

Exception handling structure with a TRY block and one or more CATCH clauses to handle errors.

IF-ELSE

Conditional statement: executes code if condition is true, otherwise executes an alternative block.

ELSE-IF

ELSE IF tests a new condition if the previous condition is false.

WHILE

Loop that repeats a block of code as long as a specified condition is true.

CASE

Case statement; extension of IF-ELSE allowing multiple conditions for different actions.