English:Database Systems
Database Systems
Introduction
A database system combines organized data, a database management system, and the applications and users that interact with it. Database systems support tasks such as reliable storage, querying, updates, concurrent access, security, recovery, and large-scale data processing. At university level, you should understand both the logical abstractions presented to users and the physical mechanisms that make those abstractions efficient and dependable.
In this aiMOOC, you will move from conceptual modeling and the relational model to SQL, normalization, indexing, query processing, transactions, recovery, security, and distributed database design. You will also examine why database engineering is full of trade-offs: a design that improves read speed may increase write cost, stronger isolation may reduce concurrency, and distribution may improve scale while making coordination harder.
Learning Objectives
After working through this course, you should be able to explain the role of a DBMS, design a relational schema from requirements, express queries in SQL and relational algebra, reason about normalization and dependencies, explain how indexes and query optimizers improve performance, analyze transaction schedules, connect ACID properties to concurrency and recovery mechanisms, and evaluate centralized, distributed, relational, and non-relational designs for different workloads.
Foundations of Database Systems
A DBMS provides controlled access to persistent data. Typical services include schema definition, query processing, update processing, integrity enforcement, transaction management, authorization, backup, recovery, and performance monitoring. The DBMS creates data independence: application programs can often work with logical structures without knowing exactly where bytes are stored on disk or which physical access path the optimizer will choose.
A useful way to reason about a database system is to separate levels of abstraction. The external level describes user-specific views, the conceptual level describes the global logical schema, and the internal level describes physical representation. This separation helps teams change storage structures without forcing every application to change.
In a client/server architecture, clients send requests to a database service. In multi-tier systems, application servers often sit between user interfaces and the database. This middle tier can enforce business rules, pool connections, control access, and expose an API.
Data Models
A data model defines concepts for representing data, relationships, and constraints. Important families include relational, key-value, document, graph, column-family, and object-oriented models. The relational model remains central because it combines a simple table-based abstraction with a strong mathematical foundation and declarative query languages.
Model choice should follow requirements rather than fashion. You should ask about data shape, integrity rules, query patterns, update frequency, latency, scale, consistency needs, and operational constraints before selecting a technology.
Conceptual Modeling and Schema Design
Conceptual modeling translates real-world requirements into a structured description before implementation details dominate the discussion. In entity–relationship modeling, entities represent distinguishable objects or concepts, attributes describe them, and relationships connect them.
Cardinality expresses how many instances can participate in a relationship. One-to-one, one-to-many, and many-to-many relationships lead to different relational implementations. A many-to-many relationship is commonly mapped to a junction table whose rows reference the related entities.
A good conceptual model also captures participation constraints, candidate identifiers, optionality, and domain rules. The purpose is not merely to draw boxes and lines; it is to make assumptions explicit enough that they can later become keys, foreign keys, checks, and application rules.
Keys and Integrity Constraints
A superkey uniquely identifies tuples. A candidate key is a minimal superkey, and one candidate key can be chosen as the primary key. A foreign key connects rows across relations by requiring referenced values to correspond to an allowed key in another relation, subject to the chosen nullability and referential actions.
Integrity constraints protect meaning. Domain constraints limit permitted values, key constraints prevent duplicate identifiers, referential integrity protects relationships, and application-specific checks can encode additional rules. Constraints are valuable because they make correctness requirements explicit and allow the DBMS to enforce them consistently.
The Relational Model
The relational model represents data using relations. Informally, a relation resembles a table, a tuple resembles a row, and an attribute resembles a column. Formally, a relation is a set of tuples over defined attribute domains, so row order is not part of the logical model.
A schema describes the structure of a relation, while an instance is the current set of tuples. Because relational queries describe desired results rather than step-by-step navigation through physical records, a DBMS has freedom to choose efficient execution plans.
Relational Algebra
Relational algebra provides operators that transform relations into relations. Important operations include selection, projection, Cartesian product, union, difference, rename, and several forms of join. These operators matter because they provide a formal basis for reasoning about query equivalence and optimization.
For example, pushing a selective filter toward the leaves of a query plan can reduce the number of tuples processed by later joins. However, valid rewrites must preserve the semantics of the query, including the effects of duplicates and null values when translating between pure relational algebra and practical SQL.
SQL and Declarative Querying
SQL is the dominant language for relational database systems. It includes data definition, data manipulation, querying, integrity constraints, authorization features, and transaction control. SQL is declarative: you specify the result you want, and the DBMS chooses an execution strategy.
A simple query might be written as SELECT title FROM Film WHERE rating = 'PG';. More advanced SQL uses joins, grouping, aggregation, subqueries, common table expressions, window functions, views, and recursive queries.
You should distinguish logical meaning from physical execution. Two syntactically different SQL queries can sometimes express the same result, while a single SQL statement can be executed by many different plans depending on statistics, indexes, memory, and optimizer estimates.
Null Values and Three-Valued Logic
SQL uses NULL to represent missing or inapplicable information. Comparisons involving null generally produce an unknown truth value rather than ordinary true or false. This is why IS NULL is different from equality comparison.
Three-valued logic can surprise programmers. Conditions in a WHERE clause retain rows for which the predicate evaluates to true, not unknown. Careful schema design and explicit null handling reduce ambiguity.
Functional Dependencies and Normalization
A functional dependency X → Y means that whenever two tuples agree on X, they must also agree on Y. Functional dependencies help you reason about keys and redundancy.
Normalization decomposes schemas to reduce anomalies caused by unnecessary repetition. First normal form establishes an appropriate relational structure; second normal form addresses certain dependencies on parts of composite keys; third normal form addresses certain transitive dependency problems; and Boyce–Codd normal form applies a stricter determinant condition.
Normalization is not a ritual of splitting tables as far as possible. The design goal is a clear, enforceable schema that avoids update, insertion, and deletion anomalies while supporting required workloads. In performance-sensitive contexts, carefully justified denormalization may be appropriate, but it creates additional consistency responsibilities.
Physical Storage and Indexing
A DBMS stores data in files organized into pages or blocks. Records must be laid out within those pages, and buffer management moves pages between persistent storage and memory. Storage engines balance sequential access, random access, write amplification, cache behavior, and durability.
An index is an auxiliary data structure that accelerates selected lookups while consuming space and adding maintenance work during updates. Common index families include B+ trees and hash indexes. B+ trees support ordered traversal and range predicates, while hash indexes are naturally suited to equality-oriented access patterns.
A B-tree family index keeps search paths short by maintaining a high branching factor. Database implementations commonly use B+ tree variants in which internal nodes guide navigation and leaf nodes contain entries for indexed keys.
Index Design Trade-offs
Indexes should reflect workload. An index on columns that are rarely filtered, joined, grouped, or ordered may add write overhead without much benefit. Composite indexes introduce ordering decisions because the usefulness of later key columns depends on query predicates and the access strategy.
Covering indexes can allow a query to obtain needed values directly from an index, but larger indexes consume more memory and storage. Database tuning therefore requires measurement with representative data and execution plans rather than guesses.
Query Processing and Optimization
A query processor parses and validates SQL, converts it into an internal representation, explores alternatives, and selects an execution plan. Physical operators include scans, index lookups, sorting, aggregation, and join algorithms such as nested-loop, hash, and sort-merge joins.
The optimizer estimates costs using metadata and statistics. Cardinality estimation is especially important because an error early in a plan can influence join order, memory decisions, and operator choice. Cost-based optimization is powerful but not omniscient: stale statistics, correlated attributes, parameter sensitivity, and complex predicates can produce poor estimates.
When diagnosing performance, compare logical query meaning, actual execution plans, estimated versus observed row counts, index availability, data distribution, and I/O behavior.
Transactions and ACID
A transaction is a sequence of operations treated as one logical unit of work. The ACID properties summarize important guarantees:
- Atomicity: A transaction's changes take effect as a unit rather than partially.
- Consistency: Transactions preserve declared integrity rules and valid application invariants when those rules are correctly specified.
- Isolation: Concurrent execution behaves according to the system's isolation guarantees and aims to prevent unacceptable interference.
- Durability: Once a transaction is committed, its effects survive failures covered by the recovery model.
Concurrency improves throughput and resource utilization, but interleaving operations can cause anomalies. Database systems therefore use protocols such as locking, timestamp ordering, optimistic validation, and multi-version concurrency control.
Serializability and Isolation Levels
A schedule is serial when transactions run one after another without interleaving. A concurrent schedule is serializable when its outcome is equivalent, under the relevant definition, to some serial execution.
Practical SQL systems expose isolation levels that trade stronger guarantees against concurrency and implementation cost. You should not assume that every product implements every level identically. Instead, read the DBMS documentation, identify which anomalies are possible, and test the behavior relevant to your application.
Deadlocks
Locking can create a deadlock when transactions wait in a cycle for resources held by one another. A DBMS may detect deadlocks using a wait-for graph and abort a victim transaction, or it may use prevention policies. Application code must therefore be prepared to retry transactions that fail because of concurrency conflicts.
Logging and Recovery
Failures can occur after some in-memory changes have been made but before all corresponding data pages reach stable storage. Recovery mechanisms restore a database to a correct state.
Write-ahead logging records information needed for recovery before dependent data pages are written. Combined with transaction metadata and recovery algorithms, logging allows the system to redo committed work when necessary and undo incomplete work when required by the recovery design.
Backups and logs solve related but different problems. Recovery from a crash, restoration after accidental deletion, disaster recovery, and point-in-time restoration may require different mechanisms. A professional database strategy defines recovery objectives and regularly tests restoration procedures.
Security and Administration
Database security applies the principle of least privilege. Users and services should receive only the permissions required for their responsibilities. Roles, views, row-level policies, stored procedures, and application-layer controls can reduce unnecessary access.
Sensitive data may require encryption in transit and at rest, auditing, secrets management, masking, retention rules, and legal or organizational controls. Security also includes protecting availability: denial-of-service risks, runaway queries, compromised credentials, and unsafe administrative interfaces can all threaten a database system.
Database administration also includes capacity planning, monitoring, schema migration, index maintenance, statistics management, backup verification, and performance diagnosis.
Distributed and Non-Relational Database Systems
A distributed database stores or processes data across multiple nodes. Distribution can improve scale, geographic reach, and fault tolerance, but it makes coordination, consistency, partition handling, and recovery more complex.
Common techniques include partitioning, replication, distributed consensus for selected coordination problems, and distributed transaction protocols. Network failures are normal design conditions rather than exceptional events, so systems must define what clients observe during partitions and recovery.
Non-relational systems use models such as key-value, document, graph, or wide-column storage. They are not automatically faster or more scalable than relational systems; advantages depend on workload, data model, implementation, and operational context. Modern systems also blur categories by adding transactions, SQL-like query languages, JSON support, vector search, and multiple storage models.
Choosing a Database Architecture
When evaluating an architecture, ask what must remain correct under concurrency and failure, what latency and throughput are required, how data grows, where users are located, which queries dominate, and what operational expertise is available.
A small, well-designed relational database may outperform a complex distributed architecture for modest workloads because it avoids network coordination and operational overhead. Conversely, very large or geographically distributed workloads may justify partitioning, replication, and specialized storage engines.
Interactive Tasks
Quiz: Test Your Knowledge
What is the primary purpose of a foreign key? (To enforce a relationship between referenced rows) (!To sort every table automatically) (!To encrypt a database page) (!To replace all candidate keys)
Which relational operation keeps selected columns? (Projection) (!Selection) (!Difference) (!Cartesian product)
What does a cost-based query optimizer choose? (An estimated efficient execution plan) (!A new database password) (!A fixed row order for every table) (!A replacement for transaction logging)
Which index structure naturally supports ordered range scans? (B plus tree) (!Hash table only) (!Heap file only) (!Write ahead log)
Which ACID property concerns all-or-nothing transaction effects? (Atomicity) (!Durability) (!Isolation) (!Replication)
What does write-ahead logging require before dependent data pages are written? (Relevant log records must be made durable) (!All indexes must be deleted) (!Every query must become serial) (!All users must disconnect)
What is a candidate key? (A minimal set of attributes that uniquely identifies a tuple) (!Any attribute used in an ORDER BY clause) (!A temporary buffer page) (!A database backup file)
What problem can normalization help reduce? (Redundancy-driven update anomalies) (!Network latency between continents) (!CPU instruction decoding) (!Lossless image compression)
What is serializability intended to preserve? (Equivalent behavior to some serial transaction order) (!Alphabetical ordering of table names) (!A single physical disk layout) (!Permanent caching of every query)
Why can an index slow down some workloads? (Updates may need additional index maintenance) (!Indexes disable all joins) (!Indexes remove transaction isolation) (!Indexes prevent data from being stored)
Memory Game
| Primary key | Chosen candidate key used to identify tuples uniquely |
| Foreign key | Constraint that references an allowed key in another relation |
| Projection | Relational operation that keeps selected attributes |
| Join | Operation that combines related tuples from different relations |
| Normalization | Schema refinement intended to reduce problematic redundancy |
| Index | Auxiliary access structure that can accelerate selected queries |
| Transaction | Logical unit of database work |
| Durability | Guarantee that committed effects survive covered failures |
Drag and Drop
| Match the correct terms. | Topic |
|---|---|
| Conceptual schema | Global logical structure of the database |
| Query optimizer | Component that compares execution alternatives |
| Buffer manager | Component that manages database pages in memory |
| Concurrency control | Mechanism that coordinates overlapping transactions |
| Recovery manager | Mechanism that restores a correct state after failures |
Match each database-system component to its main responsibility. Then explain where two of the responsibilities interact during query execution or transaction processing.
Crossword Puzzle
| Schema | What word describes the formal structure of a database? |
| Tuple | What relational term is commonly compared with a row? |
| Index | What access structure can accelerate selected lookups? |
| Commit | What operation makes a successful transaction final? |
| Join | What operation combines related tuples from relations? |
| Deadlock | What condition occurs when transactions wait in a cycle? |
LearningApps
Cloze Text
Open-Ended Tasks
Easy
- Schema Sketch: Choose a familiar university activity such as course registration and draw a small schema with at least four entities, their attributes, and key relationships.
- SQL Query Set: Create a tiny sample database and write five SQL queries that demonstrate filtering, ordering, joining, grouping, and aggregation.
- Constraint Hunt: Inspect a public application or website and identify five data rules that could become keys, foreign keys, checks, or uniqueness constraints.
- Index Observation: Use a DBMS that can display execution plans and compare one query before and after adding an appropriate index; record what changed.
Standard
- Normalization Case Study: Start with a deliberately redundant table, identify functional dependencies and anomalies, then decompose it into a better schema and justify each step.
- Query Plan Analysis: Build two equivalent SQL queries, compare their execution plans, and explain why the optimizer may choose similar or different physical operators.
- Transaction Experiment: Run two concurrent sessions against the same test database, reproduce a concurrency effect permitted by your chosen isolation level, and document the schedule.
- Database Professional Interview: Interview a database administrator, data engineer, or backend developer about backups, migrations, incidents, and performance tuning; summarize the engineering lessons.
Advanced
- Mini DBMS Component: Implement a small in-memory relational operator such as selection, projection, hash join, or aggregation and evaluate its time and space behavior.
- Recovery Demonstration: Create a controlled test environment, perform committed and uncommitted updates, simulate an application or server failure, and explain what the DBMS recovers and why.
- Architecture Decision Record: Compare a relational DBMS with one non-relational alternative for a realistic application and produce an evidence-based architecture decision covering consistency, queries, scaling, operations, and cost.
- Distributed Database Investigation: Deploy or study a multi-node database in a safe lab, observe replication or partition behavior, and produce a short technical video explaining one failure scenario and the system response.
Learning Assessment
- Relational Design Assessment: Given a narrative specification for a university research project system, produce an ER model, relational schema, keys, foreign keys, and integrity constraints, then defend your choices.
- Query Reasoning Assessment: Translate a multi-table information need into relational algebra and SQL, then explain how equivalent query transformations can affect execution cost without changing meaning.
- Normalization Assessment: Analyze a schema with stated functional dependencies, identify keys and redundancy problems, decompose it where appropriate, and discuss dependency preservation and lossless joining.
- Performance Assessment: Interpret an execution plan for a slow query, identify likely bottlenecks, propose indexes or query changes, and predict both benefits and write-side costs.
- Concurrency Assessment: Analyze an interleaved transaction schedule, determine whether problematic conflicts or anomalies occur, and justify an isolation or concurrency-control strategy.
- Recovery Assessment: Explain how logging, checkpoints, commit state, and backups contribute to recovery after a crash and how this differs from recovery after accidental data deletion.
- Architecture Assessment: Recommend a database architecture for a globally used application with stated latency, consistency, growth, and availability requirements and justify the trade-offs.
Evidence of Learning
Strong evidence of learning includes accurate use of database terminology, correct relational and ER models, executable and well-reasoned SQL, justified normalization decisions, interpretation of query plans, evidence-based index choices, correct analysis of transaction schedules, clear explanations of recovery behavior, and architecture decisions that connect workload requirements to system trade-offs.
Useful products include schema diagrams, SQL files, test datasets, query-plan reports, transaction experiments, benchmark notes, interview summaries, technical videos, architecture decision records, and small database components.
Transfer is demonstrated when you can apply the same reasoning to unfamiliar domains: identify entities and constraints, choose suitable data models, predict concurrency risks, diagnose performance, and evaluate how failure, distribution, and security requirements change a design.
OERs on the Topic
The English Wikipedia article on databases provides a broad overview of terminology, history, database models, DBMS functions, storage, transactions, security, design, and administration.
Linked Learning Areas
Database systems connect strongly with Computer science, Software engineering, Data engineering, Information systems, Distributed computing, Cybersecurity, Algorithms, Data structures, and Cloud computing. At higher-education level, these links help you connect formal data models with implementation, performance, reliability, security, and professional system design.
aiMOOC Projects
MOOCwiki · Deutsch
Nach dem Lernen ist vor dem Lernen
Entdecke direkt den nächsten Lernkurs. Weitere Inhalte erscheinen, wenn Du weiter nach unten scrollst.
Zur MOOCwiki-HauptseiteMediathek
Mediathek
Mediathek wird aus dem Wiki geladen ...
Keine passenden Inhalte gefunden. Bitte ändere Suche oder Filter.