Introduction to Database Management Systems

What this lesson covers

Almost every application you will build or support stores data somewhere: user accounts, orders, payments, exam results, chat messages. The software that stores that data safely, lets many people read and change it at once, and answers questions about it quickly is a database management system, or DBMS. This lesson builds the vocabulary that the rest of the DBMS track depends on.

Interviewers use the basics as a warm-up and as a filter. Typical opening questions are "What is the difference between a database and a DBMS?", "Why not just store data in files?", "Explain the three-schema architecture", and "What is data independence?". Freshers often answer with memorized one-liners. A strong candidate answers with a concrete example, such as a college that keeps student records in spreadsheets and runs into trouble. That is the approach this lesson takes.

By the end you will be able to:

• Define data, database, DBMS and database system, and tell them apart.
• List the concrete problems of file-based storage and say how a DBMS solves each one.
• Draw the three-schema architecture and explain physical and logical data independence.
• Compare the hierarchical, network, relational, document and graph data models.
• Name the SQL sub-languages and the people who work with a database.
• Explain two-tier and three-tier architecture, and OLTP versus OLAP.

Data, database, DBMS and database system

These four terms are often mixed up, so pin them down first.

• Data is a set of raw recorded facts: 101, "Asha", 8.7. On its own a fact has little meaning.
• Information is data placed in context: "Student 101, Asha, has a CGPA of 8.7."
• A database is an organized, related collection of data, stored so that it can be found and updated efficiently. A college database might hold students, courses, enrolments and fees.
• A database management system (DBMS) is the software that creates, stores, protects and queries a database. MySQL, PostgreSQL, Oracle Database, Microsoft SQL Server, SQLite and MongoDB are DBMSs.
• A database system is the whole package: the database, the DBMS, the applications that use it and, in some definitions, the people who run it.

A useful analogy: the database is the books in a library, and the DBMS is the librarian plus the catalog plus the lending rules. You never walk into the stacks and rearrange shelves yourself. You ask the librarian, and the librarian makes sure two people do not borrow the same copy.

+-----------+   SQL query    +---------+   reads/writes   +--------+
| App / user| -------------> |  DBMS   | ---------------> |  Data  |
|           | <------------- | engine  | <--------------- | files  |
+-----------+   result rows  +---------+                  +--------+

The application never opens the data files directly. It sends requests, usually written in SQL (Structured Query Language), and the DBMS decides how to carry them out.

What a DBMS provides

A DBMS bundles many services that you would otherwise write by hand:

The last row deserves a note. Metadata means "data about data": table names, column types, constraints, indexes. The DBMS keeps it in a system catalog (also called the data dictionary). In PostgreSQL you can query information_schema.tables; in SQLite you can query sqlite_schema. Because the description of the data lives with the data, every program sees the same definition.

Why not just use files?

Before database systems became common, organizations stored data in ordinary files, and each application program had its own files in its own format. This is the file processing system approach. It still appears today whenever a team keeps critical data in a set of spreadsheets or CSV files shared on a drive.

Take a college. The admissions office keeps students.csv. The hostel office keeps hostel.csv, which also stores each student's name, phone and branch. The exam cell keeps results.csv, which again stores name and branch. Each office wrote its own small program to read and update its own file.

 Admissions app ---> students.csv  (roll, name, phone, branch)
 Hostel app     ---> hostel.csv    (roll, name, phone, room)
 Exam app       ---> results.csv   (roll, name, branch, marks)

Now look at what goes wrong.

1. Data redundancy

Redundancy means storing the same fact in more than one place. Asha's name and phone number are stored three times. That wastes space, but the bigger cost is the next problem.

2. Data inconsistency

Inconsistency means two copies of the same fact disagree. Asha changes her phone number and tells admissions. students.csv is updated; hostel.csv is not. When the hostel warden calls her in an emergency, the call goes to a dead number. Which copy is correct? Nothing in the system can tell you.

A DBMS reduces redundancy by storing each fact once (the subject of the normalization lesson) and lets every application read that single copy.

3. Difficulty in accessing data

The principal asks: "List all CSE students in the hostel with marks below 40." The answer needs data from three files in three formats. Someone must write a new program to join them. Every new question means new code. A DBMS answers ad hoc questions with a declarative query language: you describe what you want, not how to fetch it.

4. Data isolation

Data is scattered across files with different formats (one uses commas, one uses tabs, one stores dates as DD/MM/YYYY, another as YYYY-MM-DD). Writing programs that combine them is tedious and fragile.

5. Integrity problems

Integrity constraints are rules the data must always satisfy, such as "marks are between 0 and 100" or "every hostel resident must be an admitted student". In a file system these rules are buried inside each program's code. Add a new program and you must remember to re-implement every rule. Forget one, and bad data gets in. A DBMS stores the rules once, in the schema, and checks them on every write, whichever program does the writing.

6. Atomicity problems

Atomicity means a group of changes happens completely or not at all. Moving a student from hostel room 12 to room 30 means two writes: free room 12, occupy room 30. If the program crashes between them, room 12 is freed but the student has no room. Files give you no easy way to undo the first write. A DBMS groups the writes into a transaction and guarantees all-or-nothing behaviour. The transactions and ACID lesson covers this properly.

7. Concurrent-access anomalies

Concurrency means several users acting at the same time. Two clerks each read "seats left in the elective: 1", each enrol a student, and each write back "seats left: 0". Two students got the last seat. This is a lost update. With files, preventing it needs hand-written locking that is easy to get wrong. A DBMS has built-in concurrency control, covered in the concurrency control lesson.

8. Security problems

The exam cell should see marks but not hostel fee dues. With files, access is usually all-or-nothing at the file level. A DBMS lets you grant permissions per table, per column and per operation, for example GRANT SELECT ON results TO exam_cell;.

Summary table

When files are still fine

A DBMS is not free. It costs money or operational effort, uses memory and CPU, and needs someone to run backups and upgrades. For a single-user tool, a small configuration file or a write-once log, plain files can be the right choice. Embedded databases such as SQLite exist precisely to give you DBMS guarantees without running a server.

The three-schema architecture

A schema is the description of a database's structure: which tables exist, what columns they have, what types and rules apply. The data stored at a given moment is called an instance (or state) of the database. The schema changes rarely; the instance changes on every insert.

The three-schema architecture, proposed by the ANSI/SPARC committee in the 1970s, separates the description of a database into three levels. The goal is to keep users' view of data separate from how the data is physically stored.

   User A        User B        User C
     |             |             |
+----------+  +----------+  +----------+
| External |  | External |  | External |   EXTERNAL LEVEL
|  view 1  |  |  view 2  |  |  view 3  |   (what each user sees)
+----------+  +----------+  +----------+
      \            |            /
       \  external/conceptual  /
        \      mapping        /
      +-------------------------+
      |    Conceptual schema    |         CONCEPTUAL LEVEL
      | tables, columns, rules  |         (whole logical design)
      +-------------------------+
                   |
        conceptual/internal mapping
                   |
      +-------------------------+
      |     Internal schema     |         INTERNAL LEVEL
      | files, pages, indexes   |         (how it is stored)
      +-------------------------+
                   |
             [ disk storage ]

Internal level

The internal level (or physical level) describes how data is actually stored: file layout, record format, page size, which columns are indexed and with what kind of index, compression, and where on disk things live. Only the DBMS and the database administrator usually care about this level.

Example: "The student table is stored as a B+ tree clustered on roll_no, with a secondary index on branch, in 8 KB pages."

Conceptual level

The conceptual level (or logical level) describes the whole database for the whole organization: every table, every column and type, every relationship and constraint. It hides storage details. Database designers and developers work here.

Example: student(roll_no INT PRIMARY KEY, name TEXT, branch TEXT, cgpa REAL) and enrolment(roll_no, course_id, grade) with a foreign key from enrolment.roll_no to student.roll_no.

External level

The external level (or view level) describes the part of the database that one group of users needs, in the shape they need it. There can be many external schemas. In SQL they are usually implemented as views, which are stored queries that look like tables.

Example: the hostel office sees a view hostel_residents(roll_no, name, room_no) that hides CGPA and fees. The exam cell sees results_view(roll_no, name, course, grade).

Mappings

Between levels sit mappings. The external/conceptual mapping says how each view is built from the conceptual tables. The conceptual/internal mapping says how each logical table is laid out in physical storage. When a query arrives against a view, the DBMS uses both mappings to translate it into operations on stored records.

Data independence

Data independence is the ability to change the schema at one level without having to change the schema at the next higher level, or the application programs. It is the practical payoff of the three-level split.

Physical data independence

Physical data independence means you can change the internal schema without changing the conceptual schema or the applications.

Examples of physical changes that should not break any query:

• Adding an index on student.branch to speed up searches.
• Moving the table to a faster disk or a different tablespace.
• Changing the page size, compression or file organization.
• Partitioning a large table by year.

Your SELECT * FROM student WHERE branch = 'CSE' keeps working and returns the same rows. Only its speed changes. Relational databases achieve physical independence well, which is why you can tune performance without touching application code.

Logical data independence

Logical data independence means you can change the conceptual schema without changing the external schemas or the applications.

Examples:

• Adding a new column email to student. Existing views and queries that name their columns keep working.
• Splitting student into student_core and student_contact, then redefining the old view student as a join of the two, so old programs still see the old shape.

Logical independence is harder to achieve than physical independence. Applications depend directly on table and column names and meanings. If you remove a column that a report uses, or change its meaning, no mapping can save that report. Views help, but they cannot hide every change, and some views over joins cannot be updated.

Data models

A data model is a set of concepts for describing data: the structures, the operations allowed on them, and the constraints. The data model decides how you think about your data. Here are the five you should be able to compare.

Hierarchical model

Data is arranged as a tree. Each record has one parent and possibly many children, like folders on a disk. IBM's IMS, built in the 1960s, is the classic example, and it still runs in some banks and insurers. The Windows registry and XML documents are tree-shaped too.

            College
           /       \
       Dept CSE    Dept ECE
       /     \          \
   Asha     Meera       Ravi
    |
 Courses: DBMS, OS

Strengths: very fast when you walk from parent to child along the tree's paths. Weaknesses: a many-to-many relationship (a student takes many courses, a course has many students) does not fit a tree. You must duplicate records, which brings back redundancy. Queries that go "against the grain" of the tree are slow.

Network model

The network model (standardized by CODASYL around 1969–1971) generalizes the tree: a record can have several parents, so data forms a graph of records linked by pointers. It handles many-to-many relationships through link records.

Weakness: programs navigate pointer by pointer ("find the first course of this student, then the next"). The program must know the access paths, so any change to the structure breaks programs. That is the opposite of data independence.

Relational model

The relational model, proposed by E. F. Codd in 1970, stores all data as relations, which you can picture as tables of rows and columns. Relationships are expressed by matching values (a foreign key), not by pointers. You query with a declarative language and the DBMS chooses the access path.

student                       enrolment
+-------+-------+--------+    +-------+--------+
| roll  | name  | branch |    | roll  | course |
+-------+-------+--------+    +-------+--------+
| 101   | Asha  | CSE    |    | 101   | DBMS   |
| 102   | Ravi  | ECE    |    | 101   | OS     |
+-------+-------+--------+    | 102   | DBMS   |
                              +-------+--------+

Strengths: simple mental model, strong theory (relational algebra, normalization), declarative SQL, excellent data independence, mature tooling. It is the default choice for most business systems. Examples: PostgreSQL, MySQL, Oracle, SQL Server, SQLite. The relational model and keys lesson goes deep on it.

Document model

A document database stores self-contained documents, usually JSON or a binary form of it, grouped into collections. A document can nest arrays and sub-objects, and two documents in one collection can have different fields. MongoDB and Couchbase are examples.

{
  "roll": 101,
  "name": "Asha",
  "branch": "CSE",
  "courses": [ {"id": "DBMS", "grade": "A"},
               {"id": "OS",   "grade": "B"} ]
}

Strengths: one read fetches a whole aggregate (a student with all courses), the shape matches application objects, and the flexible schema suits fast-changing data. Weaknesses: data shared between documents gets duplicated, joins across collections are limited or slower, and multi-document transactions arrived late and are more restricted than in relational systems. Note that "schemaless" really means "schema enforced by the application" unless you add validation.

Graph model

A graph database stores nodes (entities) and edges (relationships), each with properties. Neo4j and Amazon Neptune are examples. Queries follow edges, so "friends of friends who live in Pune and like cricket" is natural and fast, because each hop follows stored links instead of joining large tables.

 (Asha) -[:FRIEND]-> (Ravi) -[:FRIEND]-> (Meera)
    |                                       |
 [:TAKES]                                [:TAKES]
    v                                       v
 (DBMS) <------------[:TAKES]---------- (Kiran)

Use it for social networks, recommendations, fraud rings and route finding, where the relationships are the main thing you query.

Comparison

There are other models worth one sentence each: key-value stores (Redis, DynamoDB in its simplest use) map a key to an opaque value; wide-column stores (Cassandra) group columns into families per row key; and the object-oriented model stores programming-language objects directly. The NoSQL and distributed databases lesson compares the non-relational ones.

Database languages: DDL, DML and friends

A relational DBMS is driven by SQL, which is split into sub-languages by purpose. You will meet them in detail in SQL fundamentals; here is the overview.

Some textbooks put SELECT in its own group called DQL (Data Query Language). Either classification is acceptable in an interview if you state it.

Here is a small runnable example (tested in SQLite):

-- DDL: define the structure, including an integrity rule
CREATE TABLE student (
  roll_no   INTEGER PRIMARY KEY,
  name      TEXT NOT NULL,
  branch    TEXT NOT NULL,
  cgpa      REAL CHECK (cgpa BETWEEN 0 AND 10)
);
ALTER TABLE student ADD COLUMN email TEXT;

-- DML: change and read rows
INSERT INTO student (roll_no, name, branch, cgpa)
VALUES (101, 'Asha', 'CSE', 8.7),
       (102, 'Ravi', 'ECE', 7.9),
       (103, 'Meera', 'CSE', 9.1);
UPDATE student SET cgpa = 8.0 WHERE roll_no = 102;
DELETE FROM student WHERE roll_no = 103;

SELECT roll_no, name, cgpa FROM student WHERE branch = 'CSE';

roll_no | name | cgpa
--------+------+-----
101     | Asha | 8.7

If you then try INSERT ... VALUES (104, 'X', 'CSE', 11), SQLite rejects it with CHECK constraint failed. The rule lives in the schema, so every application is protected, which is exactly the integrity advantage over files.

DML languages come in two styles. Procedural (navigational) DML makes you say how to get the data, record by record, as in the network model. Declarative (non-procedural) DML, such as SQL, lets you say what you want; the DBMS's query optimizer chooses how. That separation is what makes physical data independence possible.

Inside a DBMS: main components

You do not need to know every module, but interviewers sometimes ask "what happens when you run a query?". A simplified picture:

SQL text
   |
   v
+--------------------+
| Parser             |  checks syntax, builds a parse tree
+--------------------+
   |
   v
+--------------------+
| Optimizer          |  picks a cheap plan using statistics
+--------------------+
   |
   v
+--------------------+
| Execution engine   |  runs scans, joins, sorts
+--------------------+
   |
   v
+--------------------+   +---------------------------+
| Buffer manager     |<->| Transaction & lock manager|
| (pages in memory)  |   | Log / recovery manager    |
+--------------------+   +---------------------------+
   |
   v
 Disk: data files, index files, log files, catalog

• The parser validates the SQL and checks names against the catalog.
• The optimizer considers different plans (which index, which join order) and estimates their cost. See query processing and optimization.
• The buffer manager keeps frequently used disk pages in memory, because reading from memory is far faster than reading from disk.
• The transaction manager and lock manager keep concurrent work correct.
• The recovery manager writes a log so it can redo or undo work after a crash. See recovery.

The database administrator (DBA)

The database administrator is the person or team responsible for the database system as a whole. Typical duties:

1. Schema definition: creating the conceptual schema, often together with developers.
2. Storage structure and access methods: choosing indexes, partitions and storage layout.
3. Security and authorization: creating users and roles and granting permissions.
4. Backup and recovery: scheduling backups, testing restores, planning for disasters.
5. Performance monitoring and tuning: finding slow queries, adding indexes, adjusting memory.
6. Integrity: making sure constraints reflect business rules.
7. Upgrades and maintenance: patching the DBMS, managing capacity and space.

In many modern teams, especially those using managed cloud databases, these jobs are shared between developers, a platform or SRE team, and the cloud provider. The responsibilities still exist even when no one has the job title.

Database users

Different people interact with a database in different ways:

An ORM (object-relational mapper) is a library, such as Hibernate in Java or Django ORM in Python, that maps programming-language objects to table rows so developers write less raw SQL.

Client-server, two-tier and three-tier architecture

Architecture here means how the pieces of a database application are split across machines.

One-tier

Everything, the user interface, the application logic and the database, runs on one machine. A desktop app using an embedded SQLite file is one-tier. Simple, but no sharing between users.

Two-tier (client-server)

The client machine runs the user interface and the application logic. It talks directly to a database server over the network using an API such as ODBC (Open Database Connectivity) or JDBC (Java Database Connectivity).

+------------------+          +------------------+
| Client PC        |  SQL via | Database server  |
| UI + app logic   | -------> | DBMS + data      |
+------------------+  JDBC    +------------------+

Fine for a small office application with a few dozen users. Problems at scale: every client holds database credentials, every client opens its own connections, and changing the business logic means updating every client.

Three-tier

A middle application server sits between the client and the database. The client (often a browser or mobile app) talks to the application server over HTTP. Only the application server talks to the database.

+-----------+  HTTP   +-------------------+  SQL   +-----------+
| Browser / | ------> | Application server| -----> | Database  |
| mobile app| <------ | business logic,   | <----- | server    |
+-----------+  JSON   | auth, validation  |        +-----------+
 presentation         +-------------------+         data tier
   tier                  application tier

Benefits:

• Security: clients never get database credentials; the app server enforces authorization.
• Scalability: you can run many app servers behind a load balancer, and they can share a pool of database connections.
• Maintainability: business logic lives in one place; deploy it once.
• Flexibility: web, mobile and partner APIs can all reuse the same middle tier.

Almost every web application is three-tier (or more, with caches and queues in between). If you want to see how this grows, read how systems grow.

OLTP versus OLAP

Databases serve two very different workloads, and the difference explains why companies run separate systems for them.

OLTP (Online Transaction Processing) handles the day-to-day operations of a business: place an order, transfer money, book a seat, update a profile. Each transaction is short, touches a few rows found by key, and must be correct and fast even with thousands running at once.

OLAP (Online Analytical Processing) handles analysis: "total revenue by region by month for the last three years", "which products are bought together". Each query reads millions of rows, aggregates them and returns a summary. There are fewer queries, but each is heavy. OLAP usually runs on a data warehouse, a separate database loaded from the OLTP systems by a process called ETL (extract, transform, load).

A star schema has a central fact table (one row per sale, with numeric measures like amount and quantity) surrounded by dimension tables (date, product, store, customer) that describe the facts. A snowflake schema normalizes the dimensions further into sub-tables.

              +-----------+
              | dim_date  |
              +-----------+
                    |
+-------------+  +------------+  +-------------+
| dim_product |--| fact_sales |--| dim_store   |
+-------------+  +------------+  +-------------+
                    |
              +--------------+
              | dim_customer |
              +--------------+

Why column-oriented storage for OLAP? A row store keeps all columns of one row together, which is ideal when you fetch a whole order. A column store keeps all values of one column together, so a query summing amount over 100 million rows reads only the amount column and compresses it well. For OLTP, where you read and write whole rows by key, row stores win.

Advantages and disadvantages of a DBMS

Advantages

• Controlled redundancy and better consistency.
• Data sharing between many users and applications.
• Integrity constraints enforced centrally.
• Security through fine-grained permissions.
• Backup and crash recovery built in.
• Concurrency control for many simultaneous users.
• Data independence, so storage can be tuned without rewriting apps.
• Declarative queries and a query optimizer.

Disadvantages

• Cost: licences for commercial systems, hardware, and skilled staff.
• Complexity: setup, tuning, upgrades and monitoring take expertise.
• Overhead: for very simple workloads, a DBMS can be slower than a tuned flat file because of logging, locking and parsing.
• A central database can become a single point of failure unless it is replicated.

Interview questions

Q1. What is the difference between a database and a DBMS?

A database is the organized collection of related data itself, such as all the student, course and enrolment records of a college. A DBMS is the software that stores, protects, queries and manages that data, such as PostgreSQL or MySQL. The database is the content; the DBMS is the system that controls access to it. Together with the applications, they form a database system.

Q2. What problems of file systems does a DBMS solve?

Redundancy and the inconsistency it causes, difficulty answering new questions without writing new programs, data scattered in different formats, integrity rules buried in application code, no atomicity when a crash happens mid-update, lost updates under concurrent access, and coarse security. A DBMS stores facts once, enforces constraints centrally, offers declarative SQL, and provides transactions, concurrency control, recovery and fine-grained permissions.

Q3. Explain the three-schema architecture.

It separates the database description into three levels. The internal level describes physical storage, such as files, pages and indexes. The conceptual level describes all tables, columns, relationships and constraints for the whole organization. The external level describes the views each user group sees. Mappings between levels let the DBMS translate a query on a view into operations on stored data, and they are what give us data independence.

Q4. What is data independence? Which kind is harder to achieve?

Data independence is the ability to change the schema at one level without changing the level above or the application programs. Physical independence means changing storage, such as adding an index, without changing the logical schema. Logical independence means changing the logical schema, such as adding a column or splitting a table, without changing views or programs. Logical independence is harder because applications depend directly on table and column names and meanings.

Q5. What is the difference between a schema and an instance?

The schema is the structure of the database: table definitions, types and constraints. It changes rarely. The instance is the actual data in the database at a particular moment, and it changes with every insert, update or delete. An analogy is a class definition (schema) versus the objects that exist at runtime (instance).

Q6. What is metadata and where is it stored?

Metadata is data about data: table names, column names and types, constraints, indexes, users and permissions. The DBMS stores it in the system catalog, also called the data dictionary, which is itself a set of tables you can query. Examples are information_schema in PostgreSQL and MySQL, and sqlite_schema in SQLite.

Q7. Compare the hierarchical, network and relational models.

The hierarchical model organizes records as a tree with one parent per record, so many-to-many relationships need duplicated records. The network model allows multiple parents through pointers, handling many-to-many relationships, but programs must navigate pointers, so structural changes break them. The relational model stores data in tables and links them by matching values, queried declaratively; this gave much better data independence and is why it became dominant.

Q8. When would you choose a document database over a relational one?

When the data naturally forms self-contained aggregates that are usually read and written together, such as a product with its variants and attributes, and when the shape changes often. It suits workloads that fetch the whole aggregate by key. If the data has many relationships queried in different ways, or needs multi-entity transactions and strong constraints, a relational database is usually the better default.

Q9. What are DDL, DML, DCL and TCL? Give examples.

DDL defines structure: CREATE, ALTER, DROP, TRUNCATE. DML reads and changes data: SELECT, INSERT, UPDATE, DELETE. DCL manages permissions: GRANT, REVOKE. TCL controls transactions: COMMIT, ROLLBACK, SAVEPOINT. Some texts separate SELECT into DQL.

Q10. What does a DBA do?

A DBA defines and maintains the schema with designers, chooses storage structures and indexes, manages users and permissions, plans and tests backups and recovery, monitors and tunes performance, and handles upgrades and capacity. In cloud setups some of this is shared with the provider, but backup testing, access control and query tuning remain the team's job.

Q11. Explain two-tier versus three-tier architecture.

In two-tier, the client runs the UI and business logic and connects directly to the database server. In three-tier, an application server sits in the middle: clients talk to it over HTTP, and only it talks to the database. Three-tier is more secure because clients never hold database credentials, scales better because app servers can be added and share connection pools, and is easier to maintain because logic is deployed in one place.

Q12. What is the difference between OLTP and OLAP?

OLTP runs the business with many short transactions that read and write a few rows by key, need low latency and strict correctness, and use a normalized schema. OLAP analyses the business with fewer, heavy read queries that scan and aggregate millions of historical rows, typically on a data warehouse with a denormalized star schema and often columnar storage. They are usually kept on separate systems so that analysis does not slow down customer-facing transactions.

Q13. Why do data warehouses often use column-oriented storage?

Analytical queries usually read a few columns across a huge number of rows, such as summing amount by region. A column store reads only the needed columns from disk instead of whole rows, and values from one column are similar, so they compress very well. A row store is better for OLTP, where you fetch or update a full row by key.

Q14. What are the disadvantages of using a DBMS?

Cost of software, hardware and skilled people; complexity of setup, tuning and upgrades; and overhead from logging, locking and query parsing that can make a DBMS slower than a simple file for trivial workloads. A single central database can also be a single point of failure unless replicated. For most multi-user applications the benefits clearly outweigh these costs.

Key takeaways

• A database is the data; a DBMS is the software that manages it; a database system is the whole package.
• File-based storage suffers from redundancy, inconsistency, scattered formats, hidden integrity rules, partial updates on crash, lost updates and coarse security.
• The three-schema architecture separates external views, the conceptual schema and the internal storage, joined by mappings.
• Physical data independence (change storage, keep logic) is easier than logical data independence (change logic, keep views and apps).
• The relational model won because declarative queries over tables give strong data independence; document and graph models suit aggregates and relationship-heavy data.
• SQL splits into DDL, DML, DCL and TCL.
• Three-tier architecture keeps database credentials and business logic on the server side.
• OLTP is many small, correct, fast transactions; OLAP is few large analytical scans, usually on a separate warehouse.

Next lesson

Continue with The ER model.