Introduction to Databases, DBMS, and Entity-Relationship Diagrams

0.0(0)
Studied by 0 people
call kaiCall Kai
learnLearn
examPractice Test
spaced repetitionSpaced Repetition
heart puzzleMatch
flashcardsFlashcards
GameKnowt Play
Card Sorting

1/20

flashcard set

Earn XP

Description and Tags

Comprehensive practice flashcards covering core database concepts, relational vs. non-relational database architectures, transaction models, and entity-relationship diagram (ERD) design principles.

21 Terms

1
New cards

What is the technical definition of a database?

A structured collection of data stored electronically, purpose-built for rapid search, retrieval, and management to replace scattered physical records.

2
New cards

What is the distinction between data and information?

Data represents raw, unprocessed material (such as unranked test scores), whereas information is the engineered output created by processing data into a meaningful structure (such as a valedictorian ranking list).

3
New cards

What is a Database Management System (DBMS)?

A specialized software application that sits between end users or applications and the physical database itself to capture, store, organize, and analyze data.

4
New cards

What are the four pillars of database management?

Data Persistence, Data Integrity, Concurrency Control, and Backup & Security.

5
New cards

What are the three abstraction layers of the 3-Schema Architecture?

  1. External View: The user's reality consisting of highly customized, simplified interfaces.
  2. Conceptual View: The architect's blueprint defining what data is stored and the relationships between them.
  3. Internal View: The machine's reality describing physical storage structure and access paths.
6
New cards

What were the primary limitations of early flat files and hierarchical data systems?

They were rigid, difficult to navigate, and prone to extreme data redundancy.

7
New cards

What are the core components of a relational database table?

  1. Schema: The blueprint and overarching rules defining table structure and allowed data types.
  2. Attributes/Fields: The columns representing specific traits or categories (e.g., GPA).
  3. Tuples/Records: The rows representing a single, complete, distinct entity (e.g., one student's record).
8
New cards

What are primary keys and foreign keys, and how do they function together?

A Primary Key (PK) is a unique identifier for an entity that acts as the absolute source of truth. A Foreign Key (FK) is an attribute referencing the primary key of another table to establish structural relationships and enforce referential integrity.

9
New cards

What catalyst prompted the development of Non-Relational (NoSQL) databases?

The internet explosion generated massive volumes of unstructured data (such as social media posts, sensor readings, and streaming video) that did not fit into rigid, predefined tables.

10
New cards

What are the four primary NoSQL architectures and their prominent use cases or examples?

  1. Document Stores: Store data as JSON-like documents with dynamic profiles (e.g., MongoDB).
  2. Wide-Column Stores: Store data in dynamic columns built for massive global scale (e.g., Cassandra).
  3. Key-Value Stores: Store an attribute name (key) and value for extreme speed (e.g., Redis).
  4. Graph Databases: Focus entirely on mapping complex relationships between data points (e.g., Neo4j).
11
New cards

How do RDBMS and NoSQL systems differ in scaling and schema flexibility?

RDBMS uses locked, predefined schemas (requiring ALTER TABLE) and scales vertically by adding RAM/CPU to a single server. NoSQL uses unlocked, dynamic schemas (schema-on-read) and scales horizontally by distributing workloads across clusters of smaller servers.

12
New cards

What do the ACID and BASE transaction models stand for?

ACID stands for Atomicity, Consistency, Isolation, Durability (guaranteeing 100% immediate data accuracy). BASE stands for Basically Available, Soft state, Eventual consistency (prioritizing extreme speed and high availability over immediate synchronization).

13
New cards

Why is an RDBMS architecture selected for banking and financial transactions?

Only RDBMS provides strict ACID compliance; if a network crash occurs during a transfer between accounts, the transaction is locked and rolled back completely to prevent money from being lost.

14
New cards

Why do social media and streaming platforms utilize NoSQL architectures?

They prioritize high availability (keeping video streaming smoothly) over immediate synchronization. Eventual consistency allows non-critical data, like 'Like' counts, to take a couple of seconds to update globally without impacting service.

15
New cards

Which specific database models are best suited for real-time multiplayer leaderboards and logistics GPS routing?

High-speed Key-Value NoSQL databases power real-time gaming leaderboards, while Graph NoSQL databases excel at calculating and recalculating billions of dynamic GPS routing relationships for cargo tracking.

16
New cards

What is an Entity-Relationship Diagram (ERD) and what are its key use cases?

A visual representation of a database structure used to design and understand how data entities relate before implementation. Key use cases include database design, data modeling, and system architecture planning.

17
New cards

What are the standard visual shapes used to represent ERD components?

Entities (nouns) are drawn as rectangles, Relationships (verbs) are drawn as diamonds or connecting lines, and Attributes (properties) are drawn as ovals.

18
New cards

What are the three mathematical relationship types (cardinality) in an ERD?

  1. One-to-One (1:11:1): Entity A relates to at most one Entity B, and vice versa (e.g., User to User_Profile).
  2. One-to-Many (1:M1:M): Entity A relates to multiple Entity Bs, but Entity B relates to only one Entity A (e.g., Customer to Orders).
  3. Many-to-Many (M:NM:N): Multiple Entity As relate to multiple Entity Bs, and vice versa (e.g., Students to Courses).
19
New cards

What is the 5-step blueprint methodology for building an ERD?

  1. Purpose & Scope: Define boundaries and what is being analyzed.
  2. Entities: Identify involved nouns and draw them as rectangles.
  3. Relationships: Determine connections and draw lines or diamonds.
  4. Attributes: Layer in properties as ovals and define keys.
  5. Cardinality: Define the mathematical rules (1:11:1, 1:M1:M, or M:NM:N).
20
New cards

In the E-Commerce ERD practice workshop, what was defined as in-scope versus out-of-scope?

In-scope included the core purchasing flow: users buying items and recording transactions. Out-of-scope included shipping logistics, payment gateway routing, and inventory restock alerts.

21
New cards

In the final E-Commerce workshop ERD, how are Customer, Order, and Product related?

Customer connects to Order via a 'Places' relationship with a 1:M1:M cardinality (one customer can place many orders). Order connects to Product via a 'Contains' relationship with an M:NM:N cardinality (many orders can contain many products).