Data Collection and DBMS Study Notes

File Systems, DBMS Foundations and Codd’s Relational Rules

What This Chapter Covers

Every later topic in this subject — SQL, normalization, data warehouses, NoSQL — assumes you know why databases exist and what a relational database promises. This chapter starts with how data was kept in ordinary files, shows the problems that approach creates, and then introduces the database management system (DBMS) that solves them. It ends with Codd’s 12 rules, the yardstick used to judge whether a product is truly relational.

Prerequisites: basic familiarity with files and folders, and the idea of a program that reads and writes data. No SQL knowledge is assumed; the few SQL statements shown are explained line by line and are taught fully in the next topics.

Data, Information and Databases

Data consists of recorded facts such as customer identifiers, product prices, dates and measurements. Information is data interpreted in context. The value 1250 becomes meaningful only when identified as an invoice total in rupees for a particular customer and date.

A database is an organized, persistent collection of related data, together with the rules and descriptions (metadata) that give the data meaning and allow controlled storage, retrieval and change. A library database, for example, distinguishes books, physical copies, members and loans so that the same member details do not have to be repeated for every loan.

A database is more than a set of unrelated files. Relationships, constraints, shared definitions and controlled access allow many applications to treat the data as one coherent system.

The File System Approach and Why It Was Needed

Before database software became common, and still today for simple tasks, organizations stored data in files managed by the operating system: text files, CSV files, spreadsheets or binary record files. The operating system’s file system organizes these files into directories, tracks where their bytes sit on disk and controls who may open them. It knows nothing about the meaning of the bytes; that knowledge lives inside each application program.

A file-based data system is a set of application-managed files whose formats and access logic are tightly coupled to the programs that use them, with no central control over transactions, integrity or sharing.

File systems were, and remain, useful because they are:

• simple and cheap — no extra software, server or administrator is required;
• fast for one program reading a file from start to end;
• adequate for small, single-user data such as a configuration file or a one-off export.

Example: A College Using Separate Files

Suppose the admissions office and the examination office each wrote their own program and file:

admissions.csv                          exam_results.csv
roll_no,name,phone,city                 roll_no,name,phone,subject,marks
101,Asha Patil,9876500001,Pune          101,Asha Patil,9876500001,DBMS,78
102,Ravi Kumar,9876500002,Nagpur        101,Asha Patil,9876500001,Java,81
                                        102,Ravi Kumar,9822200002,DBMS,69

Asha’s name and phone appear three times. Ravi changed his phone number and only the examination office recorded it, so the two files now disagree. Nothing stopped someone typing a marks value of 780.

Limitations of the File-Based Approach

A DBMS does not remove every design problem, but it centralizes structure and services so these concerns are handled consistently instead of program by program.

The Database Management System (DBMS)

A database management system (DBMS) is software that defines, stores, queries, updates, secures, coordinates and recovers databases on behalf of many users and applications. Applications send requests to the DBMS instead of manipulating storage files directly. MySQL, Oracle Database, PostgreSQL, Microsoft SQL Server and MongoDB are DBMS products.

Core DBMS responsibilities:

• defining schemas and constraints (data definition);
• processing queries and updates (data manipulation);
• managing persistent storage and indexes;
• coordinating simultaneous transactions (concurrency control);
• enforcing authorization (security);
• recording changes needed for recovery after failures;
• maintaining metadata about database objects in a catalog;
• providing backup, restore and administrative facilities.

The term database system often means the database, the DBMS software, applications, users, procedures and computing infrastructure together.

File System Versus DBMS

A DBMS is not always required. A small script that writes one log file, or a single-user program with simple data, may be better served by a file. A DBMS is justified when data is shared, long-lived, valuable, concurrently updated or governed by rules.

Components of a DBMS Environment

A database environment has five broad components: hardware (servers, disks, network), software (the DBMS, operating system and applications), data (stored data plus metadata), procedures (documented rules for operation, backup and recovery) and people (the users described below).

Inside the DBMS software, textbooks (for example Silberschatz, Korth and Sudarshan) describe these main modules:

Database Users and Roles

Accounts should receive only the privileges required for their role (the principle of least privilege). Shared administrator credentials prevent accountability and expose more capability than ordinary applications need.

Database Vocabulary

An entity is a distinguishable real-world thing about which data is stored, such as a Student, Course or Order. An attribute is a property of an entity, such as studentName or orderDate. A relationship associates entities, such as a Student enrolling in a Course.

In the relational model, a relation is represented as a table, a tuple is a row and an attribute is a column. Each column draws values from a domain, the set of permitted values (for example, dates, or integers from 0 to 100). The number of columns is the degree of a relation and the number of rows is its cardinality.

A row represents one occurrence of the fact modelled by a table. In CUSTOMER, one row represents one customer; in ORDER_ITEM, one row may represent one product line within one order. A column’s declared type limits representation, while constraints refine meaning: a DATE column prevents arbitrary text but does not by itself ensure that an end date follows a start date.

Column order has no conceptual meaning, and row order is not guaranteed unless a query asks for it with ORDER BY. Applications should refer to columns by name and request ordering explicitly.

Schema and Instance

A schema is the relatively stable definition of structure and rules: tables, columns, data types, relationships, constraints, views and other objects. An instance (or database state) is the actual set of values stored at a particular moment. Inserting a row changes the instance without changing the schema; adding a column changes the schema.

The word instance is also used operationally for a running database server installation (for example, “an Oracle instance”). Context distinguishes a snapshot of database contents from a running server.

Metadata and the Data Dictionary

Metadata is data that describes other data. Column names, data types, nullability, keys, constraints, indexes, privileges and object ownership are metadata. The DBMS stores metadata in a catalog or data dictionary. Query processors use it to check that tables and columns exist; administrators and users can query it instead of relying on undocumented assumptions.

The catalog is itself managed data: running a schema command such as ALTER TABLE updates the catalog as well as the stored rows. In MySQL the catalog is exposed through the information_schema database:

SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = 'library' AND TABLE_NAME = 'member'
ORDER BY ORDINAL_POSITION;

If the member table from the worked example at the end of this chapter is created in a database named library, the expected result is:

In enterprise data platforms, a broader metadata catalog also records owners, lineage, classifications, quality and locations so datasets can be discovered and governed; that use is covered in the data-preparation topic.

Data Models

A data model is a collection of concepts for describing data structure, relationships, constraints and operations. It determines how designers and users think about stored data.

Major data-model families:

NoSQL is an umbrella term for several non-relational approaches rather than one model. Relational systems are strong when structured facts, constraints, joins and transactional consistency are central; a document store may fit aggregate-shaped data usually read together; a graph database may fit relationship traversal; a key-value store may fit direct lookup by a known key. Modern products overlap, so selection should follow data relationships, access patterns, consistency needs, scale and team experience rather than a label.

Conceptual, Logical and Physical Models

Database design moves through three levels of modelling:

1. Conceptual model — describes the business domain independently of any DBMS: entities, attributes, relationships, cardinalities and rules, in business language. For a library: a Member can create many Loans; each Loan belongs to one Member; a Book title can have several physical Copies; each Loan concerns one Copy; a Copy cannot have two active loans at the same time.
2. Logical model — translates the conceptual design into the structures of a chosen model. A relational logical design names relations, columns, primary keys, foreign keys and domains: MEMBER, BOOK, BOOK_COPY and LOAN, with LOAN.member_id referencing MEMBER.member_id. Many-to-many relationships are resolved through associative tables.
3. Physical model — decides how a specific product implements the logical design: vendor data types, indexes, partitioning, storage engines and tablespaces (covered in the next topic).

A good design starts with business facts and rules, asks which facts change independently, places each fact in one appropriate table, and only then tunes physical access from real workloads.

Three-Schema Architecture and Data Independence

The ANSI/SPARC three-schema (three-level) architecture separates the user’s view of data from how it is stored:

Mappings connect the levels: the external/conceptual mapping translates a view into base tables, and the conceptual/internal mapping translates tables into storage structures.

A view is a named query that implements an external schema. It can hide sensitive columns, simplify repeated joins and restrict users to relevant rows. Using the member table created in the worked example at the end of this chapter:

CREATE VIEW active_members AS
    SELECT member_id, full_name, email
    FROM member
    WHERE status = 'ACTIVE';

SELECT * FROM active_members ORDER BY member_id;

An ordinary view stores the query definition, not a separate copy of the result rows; the DBMS evaluates it from the underlying tables when used.

Data independence is the ability to change the schema at one level without changing the schema at the next higher level:

• Physical data independence — changing the internal level (adding an index, moving a table to different storage, changing file organization) without changing the conceptual schema or application queries. It is relatively easy to achieve.
• Logical data independence — changing the conceptual schema (adding a column, splitting a table) without changing external views or applications. It is harder, because reorganizing business structures can change what programs mean; views and compatibility layers can insulate clients from some changes.

Data independence reduces coupling; it does not mean that every change is invisible or has no performance effect.

The Relational Model

E. F. Codd proposed the relational model in 1970 in the paper “A Relational Model of Data for Large Shared Data Banks”. It represents data as relations — sets of tuples over named attributes — and derives results through operations (select, project, join and others) whose outputs are again relations.

In formal theory a relation is a set, so duplicate tuples cannot exist and row order has no meaning. Practical SQL tables differ: SQL permits duplicate rows unless a key, unique constraint or DISTINCT prevents them, supports NULL, and returns bag-like results in many operations. Relational theory is the foundation; SQL defines the actual behaviour of a product.

A relational DBMS (RDBMS) is a DBMS that represents data as relations and enforces typed attributes, keys, constraints, transactions and relational operations.

Keys and Identity

• A superkey is any set of attributes that uniquely identifies a row.
• A candidate key is a minimal superkey: removing any attribute destroys uniqueness.
• The primary key is the candidate key chosen as the main identifier; it may not be NULL. The other candidate keys are alternate keys.
• A foreign key is a column (or set of columns) whose values must match a candidate key in another (or the same) table.
• A natural key uses meaningful business data, such as a PAN number; a surrogate key is an artificial identifier such as an auto-generated number.

Key design must reflect stable identity. A person’s name is rarely a sound key because it is neither unique nor unchanging. A surrogate key simplifies references, but natural uniqueness still needs a UNIQUE constraint when the business rule requires it. Keys are developed further in the ER-modelling and normalization topic.

Integrity Constraints

Constraints keep invalid states out of the database:

• domain integrity — values must belong to the column’s domain (type, range, CHECK);
• entity integrity — primary-key values must be unique and not NULL;
• referential integrity — a foreign-key value must match an existing referenced key, or be NULL where allowed;
• business rules — additional conditions such as a positive quantity or a unique email.

Application validation improves user messages, but database constraints remain essential because several applications, scripts and users may modify the same data.

Transactions, Concurrency and Recovery

A transaction groups operations into one logical unit that either completes fully or has no effect. Concurrency control coordinates simultaneous transactions so users do not corrupt shared data or see half-finished changes. Recovery uses logs to restore a valid state after a statement error, process crash or system failure: committed changes survive, incomplete ones are undone. These services are what distinguish a DBMS from ad hoc file editing; they are covered in detail in the SQL transactions topic.

Codd’s 12 Rules

In 1985, Codd published twelve rules (in two Computerworld articles) to test whether a product deserves to be called a relational DBMS. Because they are numbered 0 to 12, they are sometimes called the 13 rules; Rule 0 is the foundation for the others.

How the Rules Appear in MySQL

No commercial product satisfies all twelve rules completely; the rules are a standard of comparison, not a certification. Some practical connections in MySQL 8, again using the worked-example member table:

-- Rule 2: any value is reached by table name + primary-key value + column name
SELECT email FROM member WHERE member_id = 102;

-- Rule 3: NULL is not a value that "= NULL" can match
SELECT member_id, full_name FROM member WHERE phone = NULL;   -- returns no rows
SELECT member_id, full_name FROM member WHERE phone IS NULL
ORDER BY member_id;                                            -- correct test

phone = NULL evaluates to UNKNOWN for every row, so the first query returns nothing; IS NULL is the correct test.

MySQL Client Tools

The MySQL command-line client (the “monitor”, started with mysql -u user -p) accepts SQL interactively and is valuable for administration and reproducible scripts. MySQL Shell (mysqlsh) adds JavaScript, Python and SQL modes plus administration utilities. MySQL Workbench is a graphical client for query editing, schema browsing, modelling, administration and result inspection.

These programs are clients, not the database itself. Closing Workbench does not delete the database, and several clients can connect to the same server at once, subject to accounts and concurrency controls.

Common Misconceptions

Worked Example: From Files to a Database

The college and library examples show the same pattern. The database version stores each member once and links loans to members through a foreign key:

CREATE TABLE member (
    member_id  INT PRIMARY KEY,
    full_name  VARCHAR(80)  NOT NULL,
    email      VARCHAR(120) NOT NULL UNIQUE,
    phone      VARCHAR(15),
    status     VARCHAR(10)  NOT NULL DEFAULT 'ACTIVE'
);

CREATE TABLE loan (
    loan_id    INT PRIMARY KEY,
    member_id  INT NOT NULL,
    book_title VARCHAR(100) NOT NULL,
    loaned_on  DATE NOT NULL,
    FOREIGN KEY (member_id) REFERENCES member(member_id)
);

INSERT INTO member VALUES
    (101, 'Asha Patil', 'asha@example.com',  '9876500001', 'ACTIVE'),
    (102, 'Ravi Kumar', 'ravi@example.com',  NULL,         'ACTIVE'),
    (103, 'Meera Iyer', 'meera@example.com', NULL,         'INACTIVE');
INSERT INTO loan VALUES
    (1, 101, 'Database System Concepts', '2026-02-10'),
    (2, 102, 'RDBMS in Depth',           '2026-02-11');

Step 1 — change a fact once. Updating Asha’s email in one place is immediately visible to every query that joins loans with members; there is no second copy to forget.

UPDATE member SET email = 'asha.patil@example.com' WHERE member_id = 101;

SELECT m.full_name, m.email, l.book_title
FROM loan l JOIN member m ON m.member_id = l.member_id
ORDER BY l.loan_id;

Step 2 — integrity is enforced centrally. Both statements below are rejected by the DBMS, whichever program sends them:

INSERT INTO loan VALUES (3, 999, 'Unknown Member Book', '2026-02-12');
-- rejected: member 999 does not exist (referential integrity)

DELETE FROM member WHERE member_id = 101;
-- rejected: loan 1 still refers to member 101

In MySQL (InnoDB) these fail with error 1452 (“Cannot add or update a child row: a foreign key constraint fails”) and error 1451 (“Cannot delete or update a parent row”). The loan table still holds exactly two rows afterwards. Compare this with the CSV files, where nothing prevented an orphan or inconsistent record.

Key Points

• File systems store bytes for programs; they do not understand or protect the meaning of the data. Separate application files cause redundancy, inconsistency, isolation, program–data dependence, integrity, atomicity, concurrency and security problems.
• A DBMS centralizes definition, querying, integrity, concurrency, security, recovery and metadata; it costs more and adds complexity, so very small single-user data may still suit plain files.
• DBMS internals: query processor (DDL interpreter, DML compiler/optimizer, evaluation engine), storage manager (authorization/integrity, transaction, file and buffer managers) and the data dictionary.
• Users include DBAs, designers, application programmers, sophisticated users and naive end users.
• The schema is the structure; the instance is the data at a moment. Metadata lives in the catalog (information_schema in MySQL).
• Three-schema architecture: external, conceptual and internal levels joined by mappings. Physical data independence is easier to achieve than logical data independence.
• Data models range from hierarchical and network to relational, object-relational and NoSQL families; design proceeds from conceptual to logical to physical models.
• Codd’s rules are numbered 0–12: foundation, information, guaranteed access, systematic NULLs, online relational catalog, comprehensive sublanguage, view updating, set-level insert/update/delete, physical and logical data independence, integrity independence, distribution independence and nonsubversion. No product satisfies all of them fully.