ITM 210: Database Concepts and Design

Vocabulary flashcards covering key terms and definitions from ITM 210 lecture notes on database concepts, relational database design, ERDs, and Sakila database relationships.

Database

An organized collection of structured data stored electronically, designed to efficiently store, retrieve, and manage large amounts of information.

Key advantages of databases over Excel spreadsheets

1. Higher data volume: Capable of storing billions of records.

2. Higher concurrent users: Supports hundreds of simultaneous users.

3. Reduced data duplication: Data is stored once and linked everywhere (e.g., updating a customer address in one record updates it across the system).

4. Data integrity: Automatic enforcement of integrity rules.

5. Query performance: Optimized for fast data retrieval.

Relational Database

A database that organizes data into tables and connects them together through carefully designed relationships.

Table

A collection of related data organized in rows and columns, where each table typically represents one type of entity (e.g., customers, products, orders, or employees).

Record (Row)

A single entry in a table representing one instance of the entity (e.g., a single row in a customers table representing one specific customer).

Field (Column)

A single piece of information about each record in a table (e.g., Customer ID, First Name, Last Name, Email, or Phone).

Data Type

A classification that specifies what kind of data can be stored in a field and what operations can be performed on it.

Recommended data type for storing customer phone numbers and reason

VARCHAR / TEXT, because phone numbers may contain symbols (e.g., dashes, parentheses) or leading zeros, and they do not undergo arithmetic operations.

Integer (INT)

A data type representing whole numbers without decimals.

Decimal / Float

A data type representing numbers with decimal places.

VARCHAR / TEXT

A data type representing text strings containing letters, numbers, and symbols.

DATETIME / TIMESTAMP

A data type representing calendar dates and time combined.

BOOLEAN

A data type representing True/False values.

Primary Key

A field (or combination of fields) that uniquely identifies each record in a table, which cannot be duplicate or NULL (e.g., SSN or Customer ID).

Four key characteristics of a Primary Key

1. Unique: No duplicate values allowed in the table (e.g., distinguishing two customers both named 'Sarah' using unique IDs).

2. Not NULL: Every record must have a primary key value.

3. Stable: Rarely or never changes.

4. Simple: Ideally a single field.

Foreign Key

A field in one table that references the primary key of another table, creating links between related tables without repeating customer or entity details.

One-to-One Relationship (1:1)

A relationship where each record in Table A relates to exactly one record in Table B, and vice versa (e.g., each employee having exactly one parking permit/badge).

One-to-Many Relationship (1:M)

The most common relationship type, where one record in Table A relates to many records in Table B, but each record in Table B relates to only one record in Table A (e.g., one customer placing multiple orders, one department containing many employees, or one author writing many books).

Many-to-Many Relationship (N:M)

A relationship where records in Table A relate to multiple records in Table B, and vice versa (e.g., students enrolling in multiple courses and courses having multiple students, or books having multiple authors).

Implementation requirement for a Many-to-Many relationship

A junction (or bridge) table containing foreign keys that reference the primary keys of both participating tables.

Referential Integrity

A database rule that ensures foreign key values always reference existing primary key values, preventing orphan records and data corruption.

Example scenario demonstrating referential integrity enforcement

• Without referential integrity: An order could reference Customer ID 9999 even if no customer with that ID exists (creating an orphan record).

• With referential integrity: The database automatically rejects order creation with an invalid Customer ID.

Entity Relationship Diagrams (ERDs)

Visual representations and blueprints of a database structure showing entities (tables), attributes (columns), and relationships.

Cardinality

Symbols on relationship lines in an ERD that show how many records can participate in a relationship (e.g., '1' for exactly one, crow's foot for many, '0' for optional).

Sakila

A sample database modeling a DVD rental store, widely used for learning SQL due to its realistic business data and multiple related tables.

Examples of relationships within the Sakila sample database

• Customer to Rental: 1:M (one customer can have many rentals)

• Film to Inventory: 1:M (one film title can have many physical copies)

• Inventory to Rental: 1:M (one DVD copy can be rented many times)

• Film to Actor: N:M (films can feature multiple actors, and actors appear in multiple films)