Data Preparation for Exploration - Database Systems & SQL Vocabulary

Comprehensive vocabulary flashcards generated from Session 2 of the Data Preparation for Exploration lecture, covering core concepts in DBMS, file-based system limitations, ER modeling, SQL categories, database constraints, transaction properties, and data cleaning best practices.

File-Based System

A collection of programs that perform services for the end user, where each program defines and manages its own data.

Separation & Isolation of Data

A limitation of file-based systems where data is stored in multiple files, leading to silos and difficulty in accessing related data across different files.

Duplication of Data

A limitation of file-based systems where redundant data entries across files increase storage costs, increase potential for inconsistencies, and create challenges in maintaining data integrity.

Program Data Dependence

A limitation of file-based systems where applications are tightly coupled with data structures, making it difficult to modify data formats without affecting the program and requiring constant updates.

Incompatible File Formats

A limitation of file-based systems where various applications use different file formats, hindering data sharing and collaboration while increasing data integration complexity.

Database

A collection of related data organized in a structured way to enable efficient storage, retrieval, and management.

Database Management System (DBMS)

A collection of programs that manages the database structure and controls access to the data stored in the database.

Database System

The combination of DBMS software together with the data itself (Software + Database).

Data Models

Concepts that provide ways close to how users perceive data, entities, attributes, and relationships, describing how data is stored and the access path needed.

Relational Databases

Databases that organize data into tables with rows and columns, where relationships between different tables are defined using keys.

Non-Relational (NoSQL) Databases

Databases that use flexible data models, such as document, key-value, wide-column, or graph structures, instead of traditional tables.

Distributed Databases

Databases that distribute data across multiple nodes in a network, allowing for horizontal scaling and fault tolerance.

Entity-Relationship Diagram (ERD)

A visual diagram that identifies information required by a business by displaying relevant entities and the relationships between them.

Crow's Foot Notation

A specific ERD notation style used to represent entities, primary keys, foreign keys, and relationships between tables in a database schema.

Entity

A thing in the real world with an independent existence, which can have a physical existence (e.g., a person or car) or a conceptual existence (e.g., a job or university course).

Attribute

Particular properties that describe an entity, such as name, age, address, or salary.

Entity Instance

A specific occurrence of an entity, such as an individual person or a specific car.

Key Attribute

An attribute whose values are distinct (unique) for each entity and can be used to uniquely identify a record.

Multi-Valued Attribute

An attribute that has a set of values for the same entity instance, such as an employee having multiple phone numbers.

Derived Attribute

An attribute that can be calculated from another attribute or entity, such as deriving age from a date of birth attribute.

Single/Simple Attribute

An attribute that is not divisible and has a single value for a particular entity instance.

One-to-One Relationship

A relationship where a single record in Table A is related to only one record in Table B, and vice versa.

One-to-Many Relationship

A relationship where a single record in Table A can be related to one or more records in Table B, but a single record in Table B is related to only one record in Table A.

Many-to-Many Relationship

A relationship where a single record in Table A can be related to one or more records in Table B, and vice versa.

Primary Key

A column or combination of columns used to ensure data in a specific column is unique, cannot contain NULL values, and uniquely identifies each row in a table.

Foreign Key

A column in a relational database table that provides a link between data in two tables by referencing a column (most often the primary key) of another table.

Database Schema

A group of related objects in a database associated with a specific owner or user who has access to manipulate their structure.

Structured Query Language (SQL)

A standard programming language used for interacting with DBMS to create, read, update, and delete data, manage databases, and design tables.

Data Definition Language (DDL)

A subset of SQL commands used to define and modify database structures, including CREATE, ALTER, DROP, and TRUNCATE.

Data Manipulation Language (DML)

A subset of SQL commands used for managing and manipulating data within existing schema objects, including SELECT, INSERT, UPDATE, and DELETE.

CHAR(n)

An alphanumeric SQL data type used for string data with a fixed length of $n$ characters.

VARCHAR(n)

An alphanumeric SQL data type used for variable-length character strings up to a length of $n$.

TEXT

An alphanumeric SQL data type used for variable-length character strings with unlimited length.

NOT NULL Constraint

A database constraint ensuring that values in a specified column cannot be NULL.

UNIQUE Constraint

A database constraint ensuring that values in a column are distinct across all rows within the same table.

CHECK Constraint

A database constraint that enforces specific rules by ensuring data in a column satisfies a specified Boolean expression.

Database Transaction

An executing program forming a logical unit of database actions that includes one or more database operations such as insert, delete, or update.

ACID Properties

The key set of transaction properties consisting of Atomicity, Consistency, Isolation, and Durability.

Data Structuring

The practice of organizing data into tables, matrices, or hierarchical structures based on data nature and analysis requirements.

Indexing

Creating indexes on database tables using techniques like B-trees, hash indexes, or bitmap indexes to optimize and speed up data retrieval performance.

Imputation

The process of replacing missing values in a dataset with estimated values based on statistical techniques or domain knowledge.