Last reviewed 16 Sept 2026 · 11 min read
Data and databases
- Data — raw facts; information — processed data; database — organised collection of related data.
- Database Management System (DBMS) — software to create, store, retrieve, update and manage databases.
File system vs DBMS
| Traditional file system | DBMS |
|---|---|
| Data redundancy (duplicate data in files) | Minimised redundancy |
| Inconsistency | Consistency through integrity constraints |
| Difficult data access and sharing | Easy querying (SQL), concurrent multi-user access |
| Poor security | Access control, authorisation |
| No standard backup/recovery | Backup and recovery mechanisms |
| Data dependence on programs | Data independence |
Data models
| Model | Structure | Example |
|---|---|---|
| Hierarchical | Tree (parent–child, one-to-many) | IBM IMS |
| Network | Graph (many-to-many via links) | IDMS |
| Relational | Tables (relations) with rows and columns | MySQL, Oracle, PostgreSQL, SQL Server |
| Object-oriented | Objects with attributes and methods | Object databases |
| NoSQL | Document, key–value, column, graph stores | MongoDB, Cassandra, Neo4j |
The relational model was proposed by E. F. Codd (1970).
Relational database concepts
| Term | Meaning |
|---|---|
| Relation (table) | Set of rows and columns |
| Tuple (row/record) | One entry |
| Attribute (column/field) | Property of an entity |
| Domain | Allowed values of an attribute |
| Degree | Number of attributes (columns) |
| Cardinality | Number of tuples (rows) |
| Schema | Structure/design of the database |
| Instance | Data at a particular moment |
Keys
| Key | Meaning |
|---|---|
| Super key | Any set of attributes that uniquely identifies rows |
| Candidate key | Minimal super key |
| Primary key | Candidate key chosen to uniquely identify each row; cannot be NULL or duplicate |
| Alternate key | Candidate keys not chosen as primary |
| Foreign key | Attribute in one table referring to the primary key of another — links tables (referential integrity) |
| Composite key | Primary key made of two or more attributes |
Integrity constraints
Domain constraints, entity integrity (primary key not null), referential integrity (foreign key must match an existing primary key or be null), unique, not null, check constraints.
ER model
Entity–Relationship diagrams — entities (rectangles), attributes (ovals), relationships (diamonds); cardinalities one-to-one, one-to-many, many-to-many.
Normalisation
Organising tables to reduce redundancy and anomalies (insertion, update, deletion anomalies).
| Normal form | Requirement |
|---|---|
| 1NF | Atomic (indivisible) values; no repeating groups |
| 2NF | 1NF + no partial dependency (non-key attributes depend on the whole composite key) |
| 3NF | 2NF + no transitive dependency (non-key attributes depend only on the key) |
| BCNF | Every determinant is a candidate key |
SQL (Structured Query Language)
| Category | Commands | Purpose |
|---|---|---|
| DDL (Data Definition Language) | CREATE, ALTER, DROP, TRUNCATE, RENAME | Define/modify structure |
| DML (Data Manipulation Language) | SELECT, INSERT, UPDATE, DELETE | Query and change data |
| DCL (Data Control Language) | GRANT, REVOKE | Permissions |
| TCL (Transaction Control Language) | COMMIT, ROLLBACK, SAVEPOINT | Manage transactions |
Example queries (materials table)
CREATE TABLE Materials (
ItemCode VARCHAR(10) PRIMARY KEY,
Name VARCHAR(50),
Unit VARCHAR(10),
Rate DECIMAL(10,2),
Stock INT
);
INSERT INTO Materials VALUES ('CEM01', 'Cement OPC 53', 'bag', 420.00, 800);
SELECT Name, Rate FROM Materials WHERE Rate > 1000 ORDER BY Rate DESC;
UPDATE Materials SET Stock = Stock - 50 WHERE ItemCode = 'CEM01';
SELECT Unit, COUNT(*) AS Items, AVG(Rate) FROM Materials GROUP BY Unit;
DELETE FROM Materials WHERE Stock = 0;
- Aggregate functions: COUNT, SUM, AVG, MIN, MAX; GROUP BY, HAVING (filters groups), ORDER BY, JOIN (combining tables on matching keys), DISTINCT, LIKE (pattern matching), BETWEEN, IN.
- DELETE removes selected rows (can be rolled back in transactions); TRUNCATE removes all rows quickly; DROP removes the entire table structure.
ACID properties of transactions
Atomicity (all or nothing), Consistency, Isolation, Durability.
Popular DBMS
MySQL, PostgreSQL (open source), Oracle Database, Microsoft SQL Server, IBM Db2, SQLite (embedded), MS Access (desktop), MongoDB (NoSQL).
Data warehousing, data mining and big data
- Data warehouse — integrated historical data from many sources for analysis and reporting (OLAP); OLTP handles day-to-day transactions.
- Data mining — discovering patterns (classification, clustering, association rules).
- Big data — datasets too large/complex for traditional tools, characterised by Volume, Velocity, Variety (plus Veracity and Value).
- Tools: Hadoop, Spark; data lakes.
- Civil examples: traffic sensor data, structural health monitoring streams, smart meter data, project cost histories.