After this chapter you will be able to
- distinguish data, database, DBMS and RDBMS
- compare a DBMS with a traditional file system
- explain relational terms: table, tuple, attribute, domain, degree, cardinality
- identify primary, candidate, alternate, super, foreign and composite keys
- name data models, normal forms and SQL command groups
- use MS Access objects and data types
- explain data warehouse, data mining and big data
32.1 Basic Concepts
| Term | Meaning |
|---|---|
| Data | Raw facts |
| Database | An organised collection of related data stored for easy access and management |
| DBMS (Database Management System) | Software to create, store, retrieve, update and manage databases — e.g., MS Access, MySQL, Oracle |
| RDBMS (Relational DBMS) | DBMS that stores data in related tables — e.g., MySQL, Oracle, SQL Server, PostgreSQL, MS Access, IBM DB2 |
| Database system | Database + DBMS + users + applications |
| DBA (Database Administrator) | Person who manages the database, security and backups |
| Metadata / data dictionary | “Data about data” — structure, types, constraints |
| Schema | Overall design/structure of the database |
| Instance | Data in the database at a particular moment |
| Index | Extra structure that helps find rows quickly, like a catalogue |
★ EXAM POINT — The relational model was proposed by E. F. (Edgar) Codd of IBM in 1970; he is called the father of the relational database. Codd’s 12 rules define a true RDBMS.
File System vs DBMS
| Problem in file system | How DBMS solves it |
|---|---|
| Data redundancy (same data in many files) | Central storage, controlled redundancy |
| Data inconsistency (copies disagree) | Single updated copy |
| Difficult data access | Query languages (SQL) |
| Poor security | User permissions |
| No concurrent access control | Many users at once, safely |
| Integrity problems | Constraints (keys, rules) |
| No backup/recovery | Built-in backup and recovery |
Disadvantages of DBMS: higher cost, complexity, needs trained staff, bigger hardware.
A simple file holds one kind of data. A database holds many related tables and the relationships between them, and the DBMS controls access, security and consistency. A database is therefore the more advanced, organised form of file-based storage.
Three-Level Architecture (ANSI/SPARC)
| Level | Also called | What it shows |
|---|---|---|
| External | View level | What each user sees |
| Conceptual | Logical level | Whole database structure (tables, relationships) |
| Internal | Physical level | How data is actually stored on disk |
- Data independence: changing one level without affecting the level above — logical and physical data independence.
32.2 Relational Model Terms
Table
Column
Row
No. of columns
No. of rows
Allowed values
Unique row ID
Link to another table
| Relational term | Common term | Meaning |
|---|---|---|
| Relation | Table | Data arranged in rows and columns |
| Tuple | Row / record | One complete entry |
| Attribute | Column / field | One property (Name, Age) |
| Domain | — | Set of allowed values for an attribute |
| Degree | — | Number of attributes (columns) |
| Cardinality | — | Number of tuples (rows) |
| Null | — | Missing/unknown value (not zero, not blank space) |
🧠 REMEMBER — “Degree is wide, cardinality is tall.” Degree counts the columns across the table; cardinality counts the rows down the table.
Relational Algebra
Relational algebra is the set of operations performed on relations. SQL queries are based on it.
| Operation | Symbol | What it does |
|---|---|---|
| Select | σ | Picks the rows that satisfy a condition |
| Project | π | Picks chosen columns (duplicate rows removed) |
| Union | ∪ | Rows present in either relation |
| Intersection | ∩ | Rows present in both relations |
| Set difference | − | Rows in the first relation but not in the second |
| Cartesian product | × | Pairs every row of one relation with every row of the other |
| Join | ⋈ | Combines rows of two relations that match on a common attribute |
- Not relational algebra: INSERT, UPDATE and DELETE are SQL data-manipulation commands, not relational algebra operations.