File Systems vs. DBMS
Traditional file systems have several critical drawbacks for data management. Database Management Systems (DBMS) were developed specifically to solve these problems. Click on each drawback below to see how a DBMS provides a solution.
Drawbacks of File Systems
The DBMS Solution
Select a drawback to see the solution.
Schemas vs. Instances
A database's design (schema) is distinct from the data it holds at any moment (instance). The schema is the blueprint, while the instance is the actual house built from it. Click the button to simulate adding data and see the instance change.
Schema (The Blueprint)
The overall design of the database. It defines the structure, data types, and relationships. It rarely changes.
TABLE: Users
COLUMN 1: UserID (Integer, Primary Key)
COLUMN 2: Name (String)
COLUMN 3: Email (String, Unique)
Instance (The Data)
A snapshot of the data in the database at a specific moment. It changes frequently.
| UserID | Name | |
|---|---|---|
| 1 | Alice | alice@web.com |
| 2 | Bob | bob@web.com |
Levels of Data Abstraction
A major goal of a DBMS is to provide an abstract view of the data, hiding complex storage details. This is achieved through three levels of abstraction. Hover over each level to learn more.
View Level
How users see the data.
Logical Level
What data is stored.
Physical Level
How data is stored.
ANSI/SPARC 3-Tier Architecture
This standard architecture model provides data independence, meaning the schema at one level can be changed without affecting the schemas at higher levels. Hover over each tier.
External Level (Views)
Conceptual/Logical Level
Internal/Physical Level
Core Data Models
Data models provide the tools to describe data, relationships, and constraints. The two most fundamental models in database design are the Entity-Relationship model for conceptual design and the Relational model for implementation.
Entity-Relationship (E-R) Model
A high-level, conceptual model that describes the real world in terms of **entities** (objects) and the **relationships** between them. It's used during initial design to map out the structure.
Example:
Relational Model
This model organizes data into two-dimensional **tables** (relations). E-R diagrams are typically converted into a relational schema for implementation.
Example Tables:
STUDENTS(StudentID, Name)
COURSES(CourseID, Title)
Database Languages
Databases use specialized languages to define structure, manipulate data, and control access.
DDL
Data Definition Language
Defines the database schema (structure).
CREATE, ALTER, DROP
DML
Data Manipulation Language
Accesses and manipulates the data itself.
SELECT, INSERT, UPDATE
DCL
Data Control Language
Manages access rights and permissions.
GRANT, REVOKE
Database Users
Different people interact with a database system in various ways, from end-users who see a simple interface to programmers who build complex applications. They can be classified by their level of technical interaction.
Naïve Users
Interact through user-friendly interfaces of pre-written applications (e.g., bank tellers, web shoppers).
Application Programmers
Computer professionals who write programs that access the database using DML calls via an API.
Sophisticated Users
Analysts or scientists who formulate their own queries directly using a query language like SQL to explore data.
Specialized Users
Develop specialized, non-traditional applications like CAD, expert systems, or knowledge bases.
DBMS System Architecture
A DBMS is a complex software system with several key components that work together to manage data efficiently and reliably. This diagram shows a more detailed view of its internal structure.
Query Processor
Storage Manager
Application Architectures
Applications that use a database typically follow one of two main architectural patterns.
Two-Tier Architecture
The client application communicates directly with the database server. This is common for traditional desktop applications.
Three-Tier Architecture
An intermediary application server sits between the client and the database. This is the standard for web applications.
Test Your Knowledge
Check your understanding of the fundamental concepts with this short quiz. Click on each question to reveal its answer.
Glossary of Key Terms
Use the search bar below to quickly find definitions for important database terminology.