Basics
A database is an organised collection of related data. A DBMS (Database Management System) is software to create, store, retrieve and manage it – e.g. MySQL, Oracle Database, PostgreSQL, Microsoft SQL Server, MS Access. An RDBMS stores data in tables (relations); the relational model was proposed by E. F. Codd in 1970. NoSQL databases such as MongoDB store documents or key-value pairs instead of tables.
Advantages of a DBMS: less redundancy, consistency, data sharing, security, backup and recovery, concurrent access. Metadata (data about data) is kept in the data dictionary.
Relational terms
| Formal term | Common term |
|---|---|
| Relation | Table |
| Tuple | Row / record |
| Attribute | Column / field |
| Degree | Number of attributes (columns) |
| Cardinality | Number of tuples (rows) |
| Domain | Set of allowed values for an attribute |
| Schema | Logical design / structure of the database |
| Instance | Data in the database at a given moment |
Keys
| Key | Meaning |
|---|---|
| Super key | Any set of attributes that uniquely identifies a row |
| Candidate key | Minimal super key; a table may have several |
| Primary key | Candidate key chosen to identify rows; unique and NOT NULL; one per table |
| Alternate key | Candidate keys not chosen as primary key |
| Foreign key | Attribute that refers to the primary key of another table; enforces referential integrity |
| Composite key | Key made of two or more columns |
| Unique key | Values must be unique but (in most systems) NULL is allowed |
Example: in a bank, Account_No is the primary key of the ACCOUNTS table and appears as a foreign key in the TRANSACTIONS table.
SQL command groups
| Group | Full form | Commands |
|---|---|---|
| DDL | Data Definition Language | CREATE, ALTER, DROP, TRUNCATE, RENAME |
| DML | Data Manipulation Language | INSERT, UPDATE, DELETE (SELECT is often listed here or as DQL) |
| DCL | Data Control Language | GRANT, REVOKE |
| TCL | Transaction Control Language | COMMIT, ROLLBACK, SAVEPOINT |
DELETE vs TRUNCATE vs DROP
DELETE FROM t WHERE ...– removes selected rows; can be rolled back.TRUNCATE TABLE t– removes all rows, keeps the structure.DROP TABLE t– removes the table structure and all its data.
SELECT query order
SELECT columns FROM table WHERE row-condition GROUP BY column HAVING group-condition ORDER BY column ASC|DESC;
- WHERE filters rows; HAVING filters groups made by GROUP BY.
- ORDER BY sorts; ascending is the default.
- DISTINCT removes duplicate rows from the result.
- Aggregate functions: COUNT, SUM, AVG, MAX, MIN.
- LIKE patterns:
%= any number of characters,_= exactly one character.'S%'starts with S;'%S'ends with S. IS NULLtests for missing values (not= NULL).- Joins: INNER, LEFT, RIGHT, FULL OUTER combine rows from tables.
Normalisation
Organising tables to reduce redundancy and avoid insertion, update and deletion anomalies.
| Normal form | Rule |
|---|---|
| 1NF | Every column holds atomic (indivisible) values; no repeating groups |
| 2NF | 1NF + no partial dependency (non-key column depends on the whole composite key) |
| 3NF | 2NF + no transitive dependency (non-key column depends only on the key) |
| BCNF | Stricter 3NF: every determinant is a candidate key |
Transactions: ACID
- Atomicity – all or nothing (a fund transfer debits and credits together or not at all).
- Consistency – database moves from one valid state to another.
- Isolation – concurrent transactions do not interfere.
- Durability – committed changes survive crashes.
Data models and E-R diagrams
Models: hierarchical (tree), network (graph, Charles Bachman), relational (tables, Codd), object-oriented. The E-R model was proposed by Peter Chen (1976). Symbols: rectangle = entity, ellipse = attribute, diamond = relationship, double ellipse = multivalued attribute, underlined attribute = key.
Data warehouse – large store of historical data for analysis; data mining – finding patterns in it; data mart – subset for one department.