OPIM 3204 knowledge check 3

Database model

Conceptual framework describing how data is organized, manipulated, and governed in a database.

Three parts of a database model

Data structures, operations, and rules.

Relational model

Database model that organizes data in tables consisting of rows and columns.

Who developed the relational model

E. F. Codd (1970).

Table (relation)

Structure that stores data in rows and columns.

Column (attribute)

A named field in a table that stores a specific type of data.

Row (tuple)

A record in a table containing values for each column.

Data type (domain)

The set of values allowed in a column.

Cell

A single value at the intersection of a row and column.

Key relational rule about rows

Rows in a table have no inherent order.

Relational algebra

Set of operations used to manipulate relational tables.

SELECT operation

Chooses rows from a table.

PROJECT operation

Chooses columns from a table.

JOIN operation

Combines rows from two tables based on related columns.

UNION operation

Combines rows from two tables.

INTERSECT operation

Returns rows common to two tables.

DIFFERENCE operation

Returns rows in one table but not the other.

SQL

Structured Query Language used to interact with relational databases.

SQL pronunciation

“Sequel” or “S-Q-L”.

DDL

Data Definition Language used to define database structures.

DQL

Data Query Language used to retrieve data.

DML

Data Manipulation Language used to insert, update, and delete data.

DTL

Data Transaction Language used to manage transactions.

DCL

Data Control Language used to control user access.

Literal

A fixed value written directly in SQL.

Keyword

Reserved word with special meaning in SQL.

Identifier

Name of a database object like a table or column.

Expression

Combination of values and operators that evaluates to a result.

Comment

Text ignored by the SQL processor.

CREATE DATABASE

SQL statement used to create a new database.

DROP DATABASE

SQL statement used to delete a database.

USE statement

Selects the active database for SQL queries.

SHOW DATABASES

Displays all databases in the system.

SHOW TABLES

Lists all tables in the current database.

SHOW COLUMNS

Displays columns in a table.

SHOW CREATE TABLE

Displays the SQL statement used to create a table.

CREATE TABLE

SQL statement used to create a table.

DROP TABLE

SQL statement used to delete a table.

ALTER TABLE

SQL statement used to modify an existing table.

Data type

Defines the kind of values a column can store.

Integer data type

Stores whole numbers.

Decimal data type

Stores numbers with fractional values.

Character data type

Stores text.

Date/time data type

Stores dates and times.

Binary data type

Stores data as binary values (0s and 1s).

Spatial data type

Stores geographic data like coordinates.

Document data type

Stores structured documents like JSON or XML.

CHAR

Fixed-length text data type.

VARCHAR

Variable-length text data type.

INT

Integer data type.

DECIMAL

Exact numeric data type with decimal precision.

SELECT statement

Retrieves rows from a table.

Result table

The output returned by a SELECT query.

WHERE clause

Filters rows based on a condition.

Arithmetic operators

Perform mathematical calculations (+, -, *, /, %).

Comparison operators

Compare values (=,

Logical operators

Combine conditions (AND, OR, NOT).

Expression

Combination of operators and operands that produces a value.

NULL

Special value representing unknown or missing data.

NULL is NOT

Not zero and not blank text.

NOT NULL constraint

Prevents a column from containing NULL values.

IS NULL

Operator used to check if a value is NULL.

IS NOT NULL

Operator used to check if a value is not NULL.

INSERT statement

Adds rows to a table.

UPDATE statement

Modifies existing rows in a table.

DELETE statement

Removes rows from a table.

TRUNCATE statement

Deletes all rows from a table quickly.

DEFAULT constraint

Assigns a default value when none is provided.

Primary key

Column or set of columns that uniquely identifies each row.

Primary key requirements

Must be unique and not NULL.

Simple primary key

Primary key consisting of one column.

Composite primary key

Primary key consisting of multiple columns.

AUTO_INCREMENT

Automatically generates increasing numbers for a column.

Foreign key

Column that references the primary key of another table.

Foreign key rules

Must match a primary key value or be NULL.

Composite foreign key

Multiple columns referencing a composite primary key.

Referential integrity

Rule ensuring foreign keys reference valid primary keys.

Fully NULL foreign key

All columns in the key are NULL.

Partially NULL foreign key

Some columns NULL and others not (bad practice).

Referential integrity violation

Occurs when foreign key does not match a primary key.

RESTRICT

Prevents updates or deletes that break referential integrity.

SET NULL

Sets foreign key values to NULL when referenced row is deleted.

SET DEFAULT

Sets foreign key to its default value.

CASCADE

Automatically updates or deletes related rows.

ON DELETE clause

Defines action when referenced primary key is deleted.

ON UPDATE clause

Defines action when referenced primary key is updated.

Constraint

Rule that restricts allowable values in a database.

Column constraint

Applies to a single column.

Table constraint

Applies to multiple columns.

UNIQUE constraint

Ensures values in a column are unique.

CHECK constraint

Enforces logical conditions on column values.

Constraint name

Optional identifier given to a constraint.

ALTER TABLE ADD

Used to add constraints to a table.

ALTER TABLE DROP

Used to remove constraints.