Skip to content
Tutorials Center
My learning

Topics

Lesson 7 of 79 ยท Introduction to Databases

DBMS Interfaces and a Short History of Databases

Tour the user interfaces a DBMS offers (menus, forms, GUIs, parametric, natural language) and six decades of database history, from IMS and CODASYL to SQL and NoSQL.

Most people who use a database never type SQL. A cashier scans a barcode, a traveller picks a seat on a map, a manager opens a dashboard: each of them is using an interface to a DBMS, and behind the screen a program turns clicks into queries. A DBMS therefore ships with several kinds of interface, each designed for a different kind of user from the ones in People Around a Database.

The same lesson looks backward as well. Every idea in this course, from the relational model to transactions to NoSQL, was an answer to a problem the previous generation of systems had. Knowing the order in which those problems appeared makes the whole subject easier to remember.

Interfaces for different users

Menu-based Pick options from lists.
Forms-based Fill in fields, press search.
Graphical Point at a diagram of the schema.
Natural language Ask in plain words.

Same database, different doors.

1 / 2

DBMS interfaces

An interface is any way for a person or program to give the DBMS a request and read the answer. The main kinds are:

Interface Who uses it How it works Strength Weakness
Command-line / SQL Developers, DBAs Type statements into a shell such as psql or sqlite3 Complete power Needs skill
Menu-based Occasional users The system lists the possible requests; you pick one No syntax to learn Cannot express new questions
Forms-based Naive users, data-entry staff A form with blank fields; the program fills in a query template Fast for routine work One form per task
Graphical (GUI) Analysts, designers A diagram of tables you click and drag, often generating SQL (query-by-example style) Visual, discoverable Hard to fit complex queries
Parametric Bank tellers, call-centre staff A button per canned transaction, taking a few parameters Very fast and safe Fixed set of operations
Natural language Casual users, and increasingly everyone Questions in plain words, translated to SQL by a parser or a language model Easiest to start Ambiguity; results need checking
Application programming interface Programs A driver library (JDBC, ODBC, a Python sqlite3 module) sending SQL The basis of everything else Programmers must handle errors

Two ideas run under this table. First, the more an interface limits the user, the safer and easier it is. A parametric screen cannot be tricked into deleting a table, because it only knows how to run a handful of prepared statements. Second, every interface below the developer level is ultimately a generator of SQL. If you understand the SQL, you can understand what any tool is doing on your behalf.

A forms-based and parametric interface in code

The following program plays both roles on a tiny SQLite database. search_form is the forms-based interface: blank fields are simply ignored, and the rest become a WHERE clause. transfer is the parametric interface: one fixed transaction that takes three parameters. Notice that user values always travel as parameters (?), never glued into the SQL text, which is also how you avoid SQL injection (see Database Security Threats).

Python A forms-based search and a parametric transaction

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The three form searches produce three different WHERE clauses from the same function, and the last one has none. The refused transfer shows why a parametric interface is trustworthy: the with db: block rolled both UPDATEs back, so Cleo and Ben are exactly as they were.

A short history of databases

Before databases: file systems (1950s-60s)

Programs kept their own data files, as you saw in File Systems versus DBMS. Every program had its own file format, so data was duplicated and inconsistent, and changing a record layout meant rewriting programs.

The first true DBMSs appeared with the space program and big business. IBM's IMS (1966, built to track the parts list of the Apollo rocket) organized data as a hierarchy, and the CODASYL committee standardized a network model in which records are linked by pointers. A programmer wrote code that navigated: go to this customer, follow the pointer to the first order, then to the next. It was fast for the access paths the designers foresaw and painful for any new question. Data Models shows both shapes side by side.

The relational revolution (1970s-80s)

In 1970 Edgar Codd proposed storing data as relations and asking for results declaratively: say what you want, not how to find it (see The Relational Model). IBM's System R and Berkeley's Ingres turned the idea into working systems, and the query language that came out of System R, SQL, became an ANSI standard in 1986. The query optimizer became the clever part: the user states the goal and the engine chooses the path. Commercial products (Oracle, DB2, and later SQL Server) took over business computing in the 1980s, and transaction processing with ACID guarantees became the standard for banking and booking systems.

Objects, warehouses and the web (1990s)

Object-oriented programming prompted object-oriented and object-relational databases (Object-Oriented and Object-Relational Databases). Businesses started collecting history for analysis, which led to data warehouses and OLAP (OLAP and Data Warehousing). The web created huge numbers of simultaneous users, and open-source systems (MySQL, PostgreSQL) made relational databases free to use.

Scale, NoSQL and the cloud (2000s onwards)

Web companies hit the limits of a single server. Google published designs for Bigtable and MapReduce, Amazon published Dynamo, and a wave of NoSQL systems traded some relational guarantees for horizontal scale and flexible schemas (NoSQL Databases, CAP and Consistency Models). The 2010s answered with NewSQL and distributed SQL systems that keep SQL and transactions but spread data across machines, plus managed cloud databases that take administration off your hands. Today specialized engines (time-series, graph, vector) sit alongside relational ones, and PostgreSQL absorbs many of their features.

Why navigation lost to declarative queries

The difference between the first and second eras fits into a few lines. The first function below walks a hierarchy the way an IMS program would; the second states the same question to SQL.

Python Navigating a hierarchy versus asking a question

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Both approaches answer the first question. The difference shows when the question changes: the navigational program needs new code for each new access path, while SQL needs one new sentence. That flexibility, together with the optimizer, is the reason the relational model won.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. A bank teller presses a button that always runs the same transfer, asking only for two account numbers and an amount. Which interface is this?

    A parametric interface offers a fixed set of canned transactions that take a few parameters.
  2. 2. What is the safest way for a forms-based program to include a value typed by the user in a query?

    Bound parameters are sent separately from the SQL text, so the input can never change the structure of the statement.
  3. 3. What defined the navigational systems of the 1960s?

    IMS and CODASYL programs navigated record by record, which tied programs to the physical structure.
  4. 4. Which pair is correct?

    Codd published the relational model in 1970. SQL, born in System R, became an ANSI standard in 1986.
  5. 5. Why did NoSQL systems appear in the 2000s?

    Giant web workloads outgrew single servers, so designs like Bigtable and Dynamo traded some relational guarantees for scale.

Summary

  • A DBMS offers several interfaces: command line, menu, forms, graphical, parametric, natural-language and programming APIs; most of them generate SQL for the user.
  • Restricted interfaces (forms, parametric) are easier and safer; always pass user input as parameters.
  • History in short: file systems, then navigational IMS and CODASYL (1960s), the relational model and SQL (1970s-80s), objects and warehouses (1990s), NoSQL, cloud and distributed SQL (2000s on).
  • Declarative queries replaced navigation because they let the optimizer choose the access path and make new questions cheap to ask.

Next: Data Models, where the chapter on data models and ER modelling begins.