The Back-End Database

4. The Back-End Database

What the back-end holds. The back-end of a database application is a relational database managed by a relational database management system (RDBMS). A relational database stores data in tables (also called relations). Each table holds data about one kind of thing (for example, a Customer table or a Product table). Within a table, each row (record / tuple) describes one item (one customer), and each column (field / attribute) holds one piece of information about it (name, city, phone).

Keys — how rows are identified and linked.

• A primary key is a column (or set of columns) whose value is unique for every row and is never left blank; it identifies each record exactly (for example, CustID in a Customer table). No two rows may share the same primary-key value.
• A foreign key is a column in one table that refers to the primary key of another table, linking the two. For example, an Orders table may hold a CustID foreign key that points to the customer who placed each order. Foreign keys are what make the database relational — they connect related data across tables.

Data types and constraints. Each column is given a data type that fixes what kind of value it may hold — for example, a whole number, a decimal number, a piece of text of a stated length, or a date. Constraints are rules the RDBMS enforces to keep data correct: NOT NULL (the column must have a value), UNIQUE (no duplicates), PRIMARY KEY, FOREIGN KEY and CHECK (the value must satisfy a condition, such as marks between 0 and 100). Because these rules live in the back-end, the data stays correct no matter which front-end changes it.

The Structured Query Language (SQL). The front-end talks to the back-end using SQL (Structured Query Language), the standard language for relational databases. Its statements fall into familiar groups:

• Defining structure (DDL): CREATE TABLE, ALTER TABLE, DROP TABLE.
• Changing data (DML): INSERT (add a row), UPDATE (change rows), DELETE (remove rows).
• Reading data (DQL): SELECT, usually with a WHERE condition to pick particular rows.

A small example — creating a table and adding a row:

CREATE TABLE Customer (
    CustID   INTEGER      PRIMARY KEY,
    Name     VARCHAR(50)  NOT NULL,
    City     VARCHAR(30),
    Phone    VARCHAR(15)
);

INSERT INTO Customer (CustID, Name, City, Phone)
VALUES (101, 'Rakesh Sahoo', 'Cuttack', '9876500000');

And reading data back — the most common thing a front-end does:

SELECT Name, City, Phone
FROM Customer
WHERE City = 'Cuttack';
``` …