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
- 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.
- 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.
- 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. - 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.
- 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.
- 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
- Give an example, different from the ones in the lesson, in which the same data produce two different pieces of information.
- Why does redundancy enable inconsistency, but is not identical to it?
- 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.
- Explain, with your own words, the difference between schema and instance, using a concrete example from a school.
- Which of the seven design phases produces an artefact that is independent from the DBMS? Why is that independence valuable?
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:
- For each of the following items, state whether it is data, information, or knowledge:
a.
19:30b. "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 - List three constraints that the description implies but does not state explicitly.
- 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.
- Information systems and computer systems: where the database sits inside an organisation.
- Traditional archives and their limits: the six problems of this lesson, examined in detail.
- Introduction to databases: the database approach and its advantages.
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.