Database Concepts and SQL - L3

Flashcards covering key vocabulary and concepts related to database design, the relational model, and SQL, based on lecture notes.

Data vs. Information

Data is raw, unorganized facts that need to be processed. Information is data that has been processed and organized in a meaningful way.

Database Management System (DBMS)

Software for managing and organizing databases.

Entity-Relationship Modeling (ERM)

A technique for visually representing the structure of a database.

Entity Type

A category of objects or concepts with similar properties.

Entity Instance

A specific occurrence of an entity type.

Attribute

A characteristic or property of an entity.

Value

A specific data point for an attribute.

Logical Model

A detailed representation of the database structure, converted from a conceptual model, ready for database implementation.

Relational Model

A database model that organizes data into tables (relations) with rows (tuples) and columns (attributes).

Normalization

The process of organizing data in a database to reduce redundancy and improve data integrity.

SQL (Structured Query Language)

A standard language for accessing and manipulating databases.

Data Definition Language (DDL)

The subset of SQL commands used to define database schemas, such as creating, altering, and dropping tables.

Data Manipulation Language (DML)

The subset of SQL commands used to manipulate data, such as selecting, inserting, updating, and deleting data.

Entity Clustering

Grouping related entities together in an ER diagram to simplify the model.

Relation

A two-dimensional table in a relational database.

Tuple

A row in a relation, representing a single instance of the entity.

Attribute

A column in a relation, representing a characteristic of the entity.

Key

An identifier for a row of data in a relation.

Schema

The structure of a relation, including the name of the relation and its attributes.

Database Schema

The set of schemas for all relations in a database.

Data Dictionary (Metadata)

A repository of information about the database, including table purposes, column data types, constraints, and indexes.

Primary Key

A unique identifier for each row in a table.

Candidate Key

Any set of one or more columns whose combined values are unique among all occurrences in a table.

Alternate (Secondary) Key

A candidate key that is not chosen as the primary key.

Foreign Key

A set of one or more columns in a table that refers to the primary key of another table.

Entity Integrity

A rule ensuring that all primary key entries are unique and no part of a primary key may be NULL.

Referential Integrity

A rule ensuring that a foreign key value either matches an existing primary key value in the referenced table or is NULL.

Domain Integrity

A rule ensuring that data values conform to the column's predefined data type and constraints.

DDL (Data Definition Language)

SQL commands that define and modify database structures (e.g., CREATE, ALTER, DROP).

DML (Data Manipulation Language)

SQL commands used to retrieve, insert, update, and delete data (e.g., SELECT, INSERT, UPDATE, DELETE).

TCL (Transaction Control Language)

SQL commands that manage transaction consistency within the database (e.g., COMMIT, ROLLBACK, SAVEPOINT).

Transaction

A logical unit of work that consists of one or more SQL statements.

CREATE SCHEMA

SQL command to create a new database schema.

CREATE TABLE

SQL command to create a new table in a database.

SELECT

SQL command to retrieve data from one or more tables.

FROM

Specifies the table(s) from which to retrieve data in a SELECT statement.

WHERE

Filters the rows of data based on provided criteria in a SELECT statement.

ORDER BY

Sorts the final query result rows in ascending or descending order based on the values of one or more columns in a SELECT statement.

GROUP BY

Groups the rows of data into collections based on sharing the same values in one or more columns in a SELECT statement.

HAVING

Filters the groups formed in the GROUP BY clause based on provided criteria in a SELECT statement.

Relationship Degree

The number of entities involved in an association.

Relationship Connectivity

The types of association between entities.

Relationship Cardinality

The numerical constraints of the relationship between entities.

BETWEEN

Defines a range of values for a WHERE clause condition.

IS NULL

Checks for NULL values in a column for a WHERE clause condition.

LIKE

Matches a specified pattern in a WHERE clause condition.

IN

Specifies multiple possible values for a column in a WHERE clause.

EXISTS

Checks for the existence of rows in a subquery.