Databases Flashcards

Flashcards about Relational Model & Introduction to SQL.

Relation

Two-dimensional table.

Tuples

The rows of a relation.

Attributes

The column headers of a relation.

Keys

Identifiers for a row of data

Schema for the relation

The name of a relation (table) and its set of attributes (column headers)

Database schema

The set of schemas for all relations (tables) in a design

Data Dictionary (Metadata)

Describes schema details and other database objects

Entity Integrity

Ensures each row has a unique identity, allowing foreign keys to reference primary keys correctly.

Referential Integrity

Prevents invalid foreign key values and ensures consistency between related tables.

Domain Integrity

Ensures data accuracy by restricting entries to a valid format.

Header-Details pattern

Avoids repeated order-level data by separating it from item-level data.

Data Definition Language (DDL)

Defines and modifies database structures.

Data Manipulation Language (DML)

Used to retrieve, insert, update, and delete data.

Transaction Control Language (TCL)

Manages transaction consistency within the database.

Data Control Language (DCL)

Controls user access to the database, including granting and revoking permissions.

What makes a relation valid?

A relation (table) is valid if it adheres to certain rules, namely, it must have a unique name, each attribute (column) must have a unique name within the relation, and each tuple (row) must be unique.

What are the rules for keys?

Keys must uniquely identify each tuple in the relation. A primary key cannot contain NULL values, ensuring entity integrity.

What is enforced by referential integrity?

Referential integrity ensures that relationships between tables remain consistent. Foreign key values must either match a primary key value in the related table or be NULL (if allowed), preventing orphaned records.

What is ensured by domain integrity?

Domain integrity ensures that each attribute contains only valid values from its defined domain, maintaining data accuracy and consistency.