Database Fundamentals

Data and Information

Why a database is not a container of data but a tool that produces information, and which problems appear as soon as data are stored without a design.

Beginner

Learning objectives

  • Distinguish data, information and knowledge with precise criteria
  • Recognise the properties that make information useful for a decision
  • Explain redundancy, inconsistency and integrity with concrete examples
  • Define database and DBMS and list the functions of a DBMS
  • Distinguish schema from instance
  • Place conceptual, logical and physical design in the design process

Prerequisites

  • No previous database knowledge: this is the first lesson of the course

Introduction

Every database design decision you will take in this course depends on a single distinction: a database does not store data, it produces information.

That sentence sounds like a slogan, so let us make it operational. A company collects millions of rows: an identifier, a date, an amount, a code. Those rows are data. Nobody can take a decision by looking at them. A decision needs a different object: "the customers who spent more than 5 000 euros in the last quarter", "the two products whose stock will run out before Friday". Those are information. A database is well designed when it makes the second object cheap to obtain and the first object impossible to corrupt.

This lesson establishes the vocabulary and the problems that the remaining 100+ lessons will solve. Nothing here is about SQL or diagrams yet: we are defining what we are building and why.

Data, information and knowledge

Data

Data is a set of symbols recorded on a support, obtained by measuring or observing facts, and registered without any interpretation. Data have no meaning by themselves: 38 is data, and so is 2024-03-12 or Torino.

Information

Information is the result of interpreting data inside a context, in order to answer a question or support a decision. Information reduces the uncertainty of who receives it. The same data can produce different information in different contexts.

The distinction is not philosophical: it has an immediate practical consequence. If two applications interpret the same data differently, the data are not wrong — the design is. Our job as designers is to make the interpretation explicit and unique.

A third term is useful:

Knowledge

Knowledge is the set of rules that allow information to be produced from data: "a customer is inactive when they have not bought anything for 12 months". Knowledge lives in people, in procedures, or in the application code — and it is exactly what we must discover during requirements analysis (Module 2).

The same data, two interpretations

Stored data Context A Information produced
38 column temperature_celsius "The patient has a fever"
38 column shoe_size "This shoe does not fit me"
2024-03-12 column hire_date "The employee has 3 months of seniority"
2024-03-12 column expiry_date "The product has expired"
Torino column birth_city "The customer was born in Turin"
Torino column warehouse_city "The order ships from Turin"

The value is identical; the meaning is given by the column name, by the domain and by the rules of the application. This is why, throughout the course, we will insist on domains, on naming and on constraints: they are the mechanisms that keep the interpretation attached to the data.

Properties of useful information

Information is useful when it satisfies a small set of properties. Each of them, when missing, corresponds to a defect that a good schema must prevent.

Property Meaning What happens when it is missing
Accuracy The data reflect reality at the moment of recording Decisions based on wrong facts
Completeness All the data needed for the decision are present Partial answers, wrong aggregates
Timeliness Data are available when the decision is taken Stale information
Relevance Data concern the question being asked Noise, slow queries, useless reports
Unambiguity A value has exactly one interpretation Two users read the same row differently
Consistency Values that must agree actually agree Contradictory answers from the same database
Availability Authorised users can access data when needed Work blocked, shadow copies created outside the system
Security Only authorised users can access data Legal and economic damage

Why traditional file archives are not enough

Before DBMSs became widespread, each application wrote its own files. This organisation works for one program and collapses as soon as data are shared. Six problems appear, and they are the historical justification of the database approach.

1. Redundancy

The same fact is stored in several places. The customer address is written in the invoicing file, in the shipping file and in the marketing file. Redundancy is not free: it costs storage, but above all it costs maintenance.

2. Inconsistency

Redundancy plus independent updates produce contradiction. The customer moves; the invoicing application is updated, the shipping application is not. The database now contains two different addresses for the same customer, and no rule says which one is correct.

3. Weak integrity

A file-based application can only check the rules that its own code implements. If three programs write the same file, each of them implements a different subset of the rules. Nothing prevents an order from referencing a non-existent customer, or an employee from having a negative salary.

4. Concurrency

Two users update the same record at the same time. Without a centralised control mechanism, the last write silently wins and the first update is lost. With files, coordinating concurrent access requires locks implemented by each application — a solution that does not scale.

5. Security

Access control on files is coarse: either you can read the file or you cannot. Granting a clerk the right to see the orders of their own office requires either a copy of the data or an application that filters them — and any other program can bypass the filter.

6. Backup and recovery

If the machine crashes in the middle of a write, the file may be left in a state that is neither the old nor the new one. Reconstructing a consistent state requires the application to know the semantics of its own files. There is no general mechanism.

Problem Symptom perceived by the user Mechanism that solves it
Redundancy "We update the address in three places" Centralised, normalised schema
Inconsistency "Two reports give different numbers" Constraints + single source of truth
Weak integrity "The order has no customer" Declarative integrity constraints
Concurrency "My change disappeared" Transactions with isolation
Security "Everyone can read the salaries" Privileges managed by the DBMS
Recovery "After the crash the file was corrupted" Transactions + backup and restore

What a database is

Database

A database is a collection of data, organised according to a schema, managed by a software system (the DBMS) that guarantees its integrity, its security, its concurrent use and its durability.

Three words carry the whole definition.

  • Organised: the data follow a structure that was designed. A folder full of CSV files is a collection of data, not a database.
  • Managed: access does not happen by opening files, but by asking the DBMS. The DBMS is the only component that touches the physical storage.
  • Guaranteed: integrity, security, concurrency and durability are properties of the system, not of the goodwill of the programs that use it.

A database is therefore characterised by being large, shared (many users, many applications) and persistent (it survives the processes that use it). These three properties are precisely what a file archive cannot provide.

Schema and instance

Two concepts are constantly confused, and confusing them makes every later lesson incomprehensible.

Schema

The schema (or intension) is the description of the structure: which entities exist, which attributes they have, which constraints hold. It changes rarely, and only when the requirements change.

Instance

The instance (or extension, or state) is the set of values currently stored. It changes continuously, at every insert, update and delete, without touching the schema.

Schema Instance
Question it answers "How are data organised?" "Which data are stored now?"
Changes when Requirements change Every transaction
Size A few dozen definitions Millions of rows
Example (relational) Student(id, name, birth_date) (1, 'Anna', 2004-05-17)
Example (E-R) Entity Student with attributes id, name The set of students enrolled today

The schema is what we design. The instance is what the users fill. A design error is a defect of the schema, and it cannot be repaired by correcting data: it will reappear at the next insert.

What a DBMS is and what it guarantees

DBMS

A DBMS (Database Management System) is the software layer that manages a database. It accepts requests expressed in a data model (for example SQL), translates them into operations on the physical storage, and enforces the constraints declared in the schema.

The functions of a DBMS map one to one onto the problems listed above.

DBMS function Problem it solves
Data definition (DDL) Declaring the schema once, for all applications
Data manipulation (DML) Reading and writing without knowing the physical layout
Integrity control Rejecting data that violate the declared constraints
Access control Granting privileges per user and per object
Concurrency control Isolating simultaneous transactions
Recovery Restoring a consistent state after a failure
Physical data independence Changing the storage without changing the applications
Query optimisation Choosing an efficient execution strategy automatically

Transactions: a first look

A transaction is a sequence of operations that the DBMS treats as a single, indivisible unit. Transferring 100 euros from account A to account B is two updates; if the system stops between them, the money disappears or doubles. The DBMS prevents both outcomes by guaranteeing four properties, known as ACID.

Property Guarantee
Atomicity Either all operations of the transaction happen, or none
Consistency A transaction moves the database from one valid state to another
Isolation Concurrent transactions do not observe each other's intermediate states
Durability Once committed, the effects survive a crash

Transactions are the reason why the recovery and concurrency problems of file archives disappear. We will study them operationally in Module 7 and use them in Module 8.

Where design fits: the seven phases

The course follows a precise pipeline. Each phase produces an artefact, and each artefact is the input of the next one.

Phase Question answered Artefact produced Module
1. Requirements analysis What must the system do? Requirements, glossary, constraints 2
2. Conceptual design Which facts and rules exist in the domain? E-R schema, independent from any DBMS 3
3. Schema restructuring Is the schema simple and complete? Restructured E-R schema 4
4. Logical design How do we represent it with relations? Relational schema with keys 5
5. Normalization Are the relations free of redundancy? Normalised relational schema 6
6. Physical design How do we store it efficiently in MySQL? Tables, indexes, constraints 7
7. Implementation and use How do we create and query it? SQL scripts, queries 8

The running case study of the whole course is a Library Management System. You will meet the same requirements in Module 2, the same entities in Module 3, the same schema in Module 5, and the same tables in Module 7. Nothing will change silently between one lesson and the next.

Common mistakes

  1. Treating data and information as synonyms. If a report is wrong, the first question is not "which data are wrong" but "which interpretation is applied". Very often the data are correct and the schema allows two readings.
  2. Believing that a spreadsheet is a database. A spreadsheet has no schema, no constraints, no concurrency control and no notion of transaction. It is an excellent calculation tool and a dangerous shared archive.
  3. Starting from tables. Tables are the result of a design, not its starting point. If you begin with CREATE TABLE, you will encode the current report layout, not the structure of the domain.
  4. Thinking that a DBMS removes the need for design. A DBMS enforces the constraints you declare. If you declare none, it will happily store contradictory data forever.
  5. Confusing schema and instance. "The database is wrong" usually means "this row is wrong"; "the design is wrong" means "this row can exist". The two diagnoses lead to completely different interventions.
  6. Adding redundancy without measuring it. Duplicating a value can be a legitimate performance decision, but it must be a decision, documented, with a mechanism that keeps the copies aligned.

Summary

  • Data are recorded symbols without interpretation; information is data interpreted in a context to support a decision; knowledge is the set of rules that produces information from data.
  • Useful information is accurate, complete, timely, relevant, unambiguous, consistent, available and secure.
  • Traditional file archives suffer from redundancy, inconsistency, weak integrity, concurrency conflicts, coarse security and unreliable recovery.
  • A database is a collection of data organised according to a schema and managed by a DBMS.
  • A DBMS declares, manipulates, protects and optimises data, and provides transactions with the ACID properties.
  • The schema describes the structure and changes rarely; the instance contains the current values and changes constantly.
  • Database design is a pipeline of seven phases; the conceptual schema comes before any table and must not be expressed in terms of tables.

Check your understanding

  1. Give an example, different from the ones in the lesson, in which the same data produce two different pieces of information.
  2. Why does redundancy enable inconsistency, but is not identical to it?
  3. A colleague says: "we do not need a DBMS, our application already checks that the customer exists". List at least three reasons why this is fragile.
  4. Explain, with your own words, the difference between schema and instance, using a concrete example from a school.
  5. Which of the seven design phases produces an artefact that is independent from the DBMS? Why is that independence valuable?
Beginner

Classify, then find the constraints

Read the following description of a small cinema.

The cinema has three rooms. Each room has a number and a number of seats. Films are shown in sessions; each session has a date, a start time, a room and a film. Each film has a title, a director and a duration in minutes. Tickets are sold for a session; each ticket has a progressive number, a price and the seat number. A customer buys a ticket by giving their name and their e-mail address.

Then:

  1. For each of the following items, state whether it is data, information, or knowledge: a. 19:30 b. "Room 2 is sold out for tonight's 19:30 session" c. "A session is sold out when the number of tickets sold equals the number of seats of its room" d. 12.50
  2. List three constraints that the description implies but does not state explicitly.
  3. Identify one redundancy that a naive implementation of this description would produce, and explain which inconsistency it could cause.
Show the commented solution

1. Classification.

a. 19:30 is data: a symbol recorded on a support, with no interpretation. On its own it does not say whether it is a start time, a closing time or a duration.

b. "Room 2 is sold out for tonight's 19:30 session" is information: it is the result of interpreting several data (the room of the session, the number of seats, the number of tickets sold) inside a context, and it supports a decision (choosing another session).

c. "A session is sold out when the number of tickets sold equals the number of seats of its room" is knowledge: it is a rule that allows information to be produced from data. Notice that it is exactly the kind of sentence that, in Module 2, will be recorded as a business rule, and that in Module 7 will become a constraint or a query.

d. 12.50 is data. Only the column name (price) and the domain (a non-negative amount in euros) give it meaning.

2. Implied constraints. The text is a typical informal requirement, and several rules must be made explicit:

  • Referential integrity: a ticket must refer to an existing session; a session must refer to an existing film and an existing room.
  • Domain of the seat number: the seat number of a ticket must be between 1 and the number of seats of the room of that session.
  • Uniqueness: in the same session, two tickets cannot have the same seat number; the ticket number is unique.
  • Multiplicity of the room: a room can host many sessions, but a session takes place in exactly one room.
  • Non-negative price and positive duration: a ticket cannot have a negative price, a film cannot have a duration of zero.

3. Redundancy. A naive implementation stores the number of seats of the room inside every ticket, or the title of the film inside every session. Both choices duplicate a fact that already exists elsewhere:

  • If the number of seats is copied into the ticket, and the cinema adds two seats to room 2, some tickets will carry the old value. The database then answers "is this session sold out?" with two different results depending on which row is read: that is exactly inconsistency produced by redundancy.
  • If the title of the film is copied into every session, correcting a typo in the title requires updating every session of that film. Any session left behind shows a title that no longer exists in the catalogue.

The correct design stores each fact once, in the entity that owns it, and links the other entities to it. Module 3 will express this with entities and associations; Module 5 will translate it into foreign keys; Module 6 will prove formally that the resulting relations are normalised.

The three questions correspond to three different activities: classify (understand the vocabulary), find the constraints (discover the knowledge), find the redundancy (prepare the design). In Module 2 we will perform them systematically on a real requirement document, and in Module 3 we will turn the result into a diagram.

Where to go next

You now have the vocabulary. The next lessons of this module examine the context in which databases live and the alternatives that were used before them.

Here is a first, very small E-R diagram: two entities and one many-to-many association. You will be able to read it completely at the end of Module 3, and to open it in the editor to modify it.

(0,N)(0,N)StudentEnrollsCourseidfirst_namelast_namebirth_dateemailenrollment_datecodetitlecreditssemester
Student and Course · beginner

Related lessons