Databases

• A file-based approach suffers from data redundancy, inconsistency, data dependence and poor security.
• A relational database stores data in linked tables, which removes these problems.
• Terminology: entity, table, record/tuple, field/attribute, primary, candidate, secondary and foreign keys, relationships, referential integrity, indexing.
• E-R diagrams show entities and the one-to-one, one-to-many and many-to-many relationships between them.
• Normalisation to 1NF, 2NF and 3NF removes repeating groups, partial dependencies and non-key dependencies.
• A DBMS provides a data dictionary, data modelling, a logical schema, integrity and security, a developer interface and a query processor.
• SQL is the standard DDL (CREATE DATABASE, CREATE TABLE, ALTER TABLE, keys) and DML (SELECT, INSERT, UPDATE, DELETE).

Why databases replaced separate files

In a file-based approach each department or program keeps its own files. Limitations:

• Data redundancy: the same data (a customer’s address) is stored in several files, wasting storage.
• Data inconsistency: when one copy is updated the others are not, so different files disagree.
• Data dependence: programs are written for a specific file structure, so changing the structure means rewriting the programs.
• Data is hard to share or combine between departments, and security cannot be managed centrally: anyone with the file sees all of it.

A relational database stores data in tables linked by keys. Each item is stored once (less redundancy, so no inconsistency); the structure is separate from the programs (data independence); data can be queried across tables; and the DBMS controls access and integrity centrally.

Use these words precisely

• Entity: a thing about which data is stored (Customer, Product). Table (relation): data about one entity, in rows and columns.
• Record / tuple: one row, data about one instance. Field / attribute: one column, one item of data.
• Primary key: an attribute (or combination) that uniquely identifies each record. Candidate key: any attribute that could be the primary key; one is chosen. Secondary key: a non-key attribute that is indexed for fast searching or sorting (LastName). Foreign key: an attribute in one table that is the primary key of another table, creating the link.
• Relationship: one-to-one, one-to-many or many-to-many. A many-to-many relationship cannot be implemented directly; it is replaced by a link table with two one-to-many relationships.
• Referential integrity: every foreign key value must match an existing primary key value in the linked table. The DBMS will not let you add a sale for a customer who does not exist, or delete a customer who still has sales.
• Indexing: a separate structure listing key values and where their records are, so searches and sorts are faster.