D426: Data Management Foundations

Database Application

Software that helps users interact with database systems

Database Administrator

Role that secures databases and manages access and availability

Authorization

Restricts user access to specific tables, columns, or rows

Rules

Ensure data follows structural and business constraints

Query Processor

Interprets SQL queries and returns results

Query Optimization

Finds the most efficient execution plan for queries

Storage Manager

Converts queries into low-level storage operations

Indexes

Structures that speed up data retrieval

Transaction Manager

Ensures correct execution, concurrency control, and recovery

Metadata

Data about database structure and information about data

MongoDB

Open-source NoSQL database system

NoSQL

Non-relational database type

INSERT

Adds new rows into a table

SELECT

Retrieves data from a table

UPDATE

Modifies existing rows

DELETE

Removes rows from a table

CREATE TABLE

Creates a new table

INT

Integer numeric type

INTEGER

Whole number type

DECIMAL

Fractional numeric type

DECIMAL(M,D)

Numeric type with precision and scale

VARCHAR

Variable-length text type

VARCHAR(N)

Text up to N characters

DATE

Stores date values

Data Type

Defines format of values in a column

Analysis

First phase of database design (requirements)

Logical Design

Converts ER model into relational tables

Physical Design

Defines storage and indexing

Entity

Real-world object or concept

Relationship

Association between entities

Attribute

Property of an entity

Entity Type

Collection of similar entities

Relationship Type

Collection of relationships

Attribute Type

Set of possible values

Entity Instance

Single entity object

Relationship Instance

Single relationship record

Attribute Instance

Single value

ER Model

High-level data model of entities and relationships

ER Diagram

Visual representation of ER model

Reflexive Relationship

Entity relates to itself

IsA Relationship

Relationship between supertype and subtype

Analysis Step 1

Identify entities, relationships, attributes

Analysis Step 2

Determine cardinality

Analysis Step 3

Identify strong and weak entities

Analysis Step 4

Create supertype and subtype entities

Logical Step 5

Implement entities

Logical Step 6

Implement relationships

Logical Step 7

Implement attributes

Logical Step 8

Apply normalization

Cardinality

Minimum and maximum relationship constraints

Relationship Maximum

Maximum number of related entities

Relationship Minimum

Minimum number of related entities

Primary Key

Uniquely identifies a row

Simple Primary Key

Single-column primary key

Composite Primary Key

Multiple-column primary key

Candidate Key

Minimal unique identifier

Foreign Key

References primary key in another table

Stable Key

Primary key does not change

Simple Key

Easy to use and store

Meaningless Key

Contains no business meaning

Artificial Key

System-generated primary key

Auto-Increment Column

Automatically increases value per row

Functional Dependency

One column depends on another

Redundancy

Repeated data in a table

Normalization

Process of removing redundancy

Denormalization

Adding redundancy for performance

Normal Forms

Rules for database design quality

Third Normal Form

Non-key attributes depend only on key

Boyce-Codd Normal Form

Stronger than 3NF

Trivial Dependency

Left side is subset of right side

Non-key Column

Column not in a candidate key

Business Rules

Policies that govern data

Constraints

Rules restricting allowed values

DDL

Defines database structure

DML

Manipulates data

DQL

Queries data

DCL

Controls access

DTL

Manages transactions

Literals

Fixed values in SQL

Keywords

Reserved SQL words